第九章：分页查询与性能优化

9.1 分页查询基础

9.1.1 ToPageList方法

SqlSugar提供了强大的分页功能，ToPageList是最常用的分页方法：

// 基础分页查询
int pageIndex = 1;  // 当前页码
int pageSize = 20;  // 每页数量
int totalCount = 0; // 总记录数

var pageData = db.Queryable<Student>()
    .Where(s => s.Status == 1)
    .OrderBy(s => s.CreateTime, OrderByType.Desc)
    .ToPageList(pageIndex, pageSize, ref totalCount);

Console.WriteLine($"总记录数: {totalCount}");
Console.WriteLine($"总页数: {Math.Ceiling(totalCount / (double)pageSize)}");

高级分页示例：

// 多条件分页查询
public class PageRequest
{
    public int PageIndex { get; set; } = 1;
    public int PageSize { get; set; } = 20;
    public string Keyword { get; set; }
    public int? ClassId { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
}

public PageResult<Student> GetStudentsByPage(PageRequest request)
{
    int totalCount = 0;
    
    var query = db.Queryable<Student>()
        .WhereIF(!string.IsNullOrEmpty(request.Keyword), 
            s => s.Name.Contains(request.Keyword) || s.Email.Contains(request.Keyword))
        .WhereIF(request.ClassId.HasValue, s => s.ClassId == request.ClassId)
        .WhereIF(request.StartDate.HasValue, s => s.CreateTime >= request.StartDate)
        .WhereIF(request.EndDate.HasValue, s => s.CreateTime <= request.EndDate)
        .OrderBy(s => s.CreateTime, OrderByType.Desc);
    
    var list = query.ToPageList(request.PageIndex, request.PageSize, ref totalCount);
    
    return new PageResult<Student>
    {
        PageIndex = request.PageIndex,
        PageSize = request.PageSize,
        TotalCount = totalCount,
        TotalPages = (int)Math.Ceiling(totalCount / (double)request.PageSize),
        Items = list
    };
}

9.1.2 Skip和Take方法

除了ToPageList，还可以使用Skip和Take进行分页：

// Skip/Take分页
int pageIndex = 1;
int pageSize = 20;

// 计算跳过的记录数
int skipCount = (pageIndex - 1) * pageSize;

var list = db.Queryable<Student>()
    .OrderBy(s => s.Id)
    .Skip(skipCount)
    .Take(pageSize)
    .ToList();

// 获取总数（需要单独查询）
var totalCount = db.Queryable<Student>().Count();

性能对比：

// ToPageList：一次查询获取数据和总数（推荐）
int total = 0;
var data1 = db.Queryable<Student>()
    .ToPageList(1, 20, ref total);

// Skip/Take：需要两次查询
var data2 = db.Queryable<Student>()
    .Skip(0).Take(20).ToList();
var total2 = db.Queryable<Student>().Count();

9.1.3 分页参数封装

创建通用的分页结果类：

public class PageResult<T>
{
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
    public int TotalCount { get; set; }
    public int TotalPages { get; set; }
    public bool HasPreviousPage => PageIndex > 1;
    public bool HasNextPage => PageIndex < TotalPages;
    public List<T> Items { get; set; }
}

// 扩展方法
public static class QueryableExtensions
{
    public static PageResult<T> ToPageResult<T>(
        this ISugarQueryable<T> query,
        int pageIndex,
        int pageSize)
    {
        int totalCount = 0;
        var items = query.ToPageList(pageIndex, pageSize, ref totalCount);
        
        return new PageResult<T>
        {
            PageIndex = pageIndex,
            PageSize = pageSize,
            TotalCount = totalCount,
            TotalPages = (int)Math.Ceiling(totalCount / (double)pageSize),
            Items = items
        };
    }
}

// 使用
var result = db.Queryable<Student>()
    .Where(s => s.Status == 1)
    .ToPageResult(1, 20);

9.2 性能优化技术

9.2.1 查询缓存

SqlSugar支持多种缓存策略：

// 简单缓存（缓存2小时）
var list = db.Queryable<Student>()
    .WithCache(7200) // 缓存时间（秒）
    .ToList();

// 带缓存键的缓存
var data = db.Queryable<Student>()
    .Where(s => s.ClassId == 1)
    .WithCache("student_class_1", 3600)
    .ToList();

// 移除缓存
db.RemoveDataCache("student_class_1");

// 移除所有缓存
db.RemoveDataCache();

自定义缓存服务：

// 使用Redis缓存
public class RedisCacheService : ICacheService
{
    private readonly IConnectionMultiplexer _redis;
    
    public RedisCacheService(IConnectionMultiplexer redis)
    {
        _redis = redis;
    }
    
    public void Add<V>(string key, V value)
    {
        var db = _redis.GetDatabase();
        var json = JsonConvert.SerializeObject(value);
        db.StringSet(key, json);
    }
    
    public void Add<V>(string key, V value, int cacheDurationInSeconds)
    {
        var db = _redis.GetDatabase();
        var json = JsonConvert.SerializeObject(value);
        db.StringSet(key, json, TimeSpan.FromSeconds(cacheDurationInSeconds));
    }
    
    public bool ContainsKey<V>(string key)
    {
        var db = _redis.GetDatabase();
        return db.KeyExists(key);
    }
    
    public V Get<V>(string key)
    {
        var db = _redis.GetDatabase();
        var json = db.StringGet(key);
        return json.HasValue ? JsonConvert.DeserializeObject<V>(json) : default(V);
    }
    
    public IEnumerable<string> GetAllKey<V>()
    {
        var server = _redis.GetServer(_redis.GetEndPoints().First());
        return server.Keys().Select(k => k.ToString());
    }
    
    public V GetOrCreate<V>(string key, Func<V> create, int cacheDurationInSeconds = int.MaxValue)
    {
        if (ContainsKey<V>(key))
        {
            return Get<V>(key);
        }
        else
        {
            var value = create();
            Add(key, value, cacheDurationInSeconds);
            return value;
        }
    }
    
    public void Remove<V>(string key)
    {
        var db = _redis.GetDatabase();
        db.KeyDelete(key);
    }
}

// 配置使用Redis缓存
db.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices
{
    DataInfoCacheService = new RedisCacheService(redis)
};

9.2.2 索引优化

合理使用索引可以大幅提升查询性能：

// 实体类配置索引
[SugarTable("student")]
[SugarIndex("idx_name", nameof(Name), OrderByType.Asc)]
[SugarIndex("idx_class_status", nameof(ClassId) + "," + nameof(Status), OrderByType.Asc)]
public class Student
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    
    [SugarColumn(Length = 50, IsNullable = false)]
    public string Name { get; set; }
    
    public int ClassId { get; set; }
    public int Status { get; set; }
}

// 代码创建索引
db.DbMaintenance.CreateIndex("student", "idx_email", "Email");

// 创建唯一索引
db.DbMaintenance.CreateIndex("student", "uk_student_no", "StudentNo", true);

// 查看索引使用情况
var sql = "EXPLAIN SELECT * FROM student WHERE name = 'test'";
var dt = db.Ado.GetDataTable(sql);

9.2.3 批量操作

批量操作比循环单条操作性能高得多：

// 批量插入（推荐）
var students = new List<Student>();
for (int i = 0; i < 10000; i++)
{
    students.Add(new Student { Name = $"Student{i}" });
}
db.Insertable(students).ExecuteCommand();

// 使用分批插入（大数据量）
db.Insertable(students)
    .PageSize(1000) // 每次插入1000条
    .ExecuteCommand();

// 批量更新
db.Updateable(students).ExecuteCommand();

// 批量删除
var ids = new int[] { 1, 2, 3, 4, 5 };
db.Deleteable<Student>()
    .In(ids)
    .ExecuteCommand();

// 批量更新指定字段
db.Updateable<Student>()
    .SetColumns(s => new Student { Status = 1 })
    .Where(s => ids.Contains(s.Id))
    .ExecuteCommand();

性能对比：

// 方式1：循环插入（慢）
var sw1 = Stopwatch.StartNew();
foreach (var student in students)
{
    db.Insertable(student).ExecuteCommand();
}
sw1.Stop();
Console.WriteLine($"循环插入耗时: {sw1.ElapsedMilliseconds}ms");

// 方式2：批量插入（快）
var sw2 = Stopwatch.StartNew();
db.Insertable(students).ExecuteCommand();
sw2.Stop();
Console.WriteLine($"批量插入耗时: {sw2.ElapsedMilliseconds}ms");

9.3 异步操作

9.3.1 异步查询

使用异步操作可以提高应用程序的响应性：

// 异步查询列表
public async Task<List<Student>> GetStudentsAsync()
{
    return await db.Queryable<Student>()
        .Where(s => s.Status == 1)
        .ToListAsync();
}

// 异步分页查询
public async Task<PageResult<Student>> GetPageAsync(int pageIndex, int pageSize)
{
    RefAsync<int> totalCount = 0;
    var list = await db.Queryable<Student>()
        .ToPageListAsync(pageIndex, pageSize, totalCount);
    
    return new PageResult<Student>
    {
        PageIndex = pageIndex,
        PageSize = pageSize,
        TotalCount = totalCount,
        Items = list
    };
}

// 异步获取单条
public async Task<Student> GetByIdAsync(int id)
{
    return await db.Queryable<Student>()
        .FirstAsync(s => s.Id == id);
}

// 异步聚合查询
public async Task<int> GetCountAsync()
{
    return await db.Queryable<Student>().CountAsync();
}

9.3.2 异步增删改

// 异步插入
public async Task<int> AddAsync(Student student)
{
    return await db.Insertable(student).ExecuteReturnIdentityAsync();
}

// 异步批量插入
public async Task<int> AddRangeAsync(List<Student> students)
{
    return await db.Insertable(students).ExecuteCommandAsync();
}

// 异步更新
public async Task<bool> UpdateAsync(Student student)
{
    return await db.Updateable(student).ExecuteCommandHasChangeAsync();
}

// 异步删除
public async Task<bool> DeleteAsync(int id)
{
    return await db.Deleteable<Student>()
        .Where(s => s.Id == id)
        .ExecuteCommandHasChangeAsync();
}

// 异步事务
public async Task<bool> TransferAsync(int fromId, int toId, decimal amount)
{
    try
    {
        db.BeginTran();
        
        await db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance - amount)
            .Where(a => a.Id == fromId)
            .ExecuteCommandAsync();
        
        await db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance + amount)
            .Where(a => a.Id == toId)
            .ExecuteCommandAsync();
        
        db.CommitTran();
        return true;
    }
    catch
    {
        db.RollbackTran();
        return false;
    }
}

9.4 SQL性能分析

9.4.1 SQL分析器

SqlSugar提供了强大的SQL分析功能：

// 启用SQL日志
db.Aop.OnLogExecuting = (sql, pars) =>
{
    Console.WriteLine($"SQL: {sql}");
    Console.WriteLine($"参数: {string.Join(",", pars?.Select(p => $"{p.ParameterName}={p.Value}"))}");
};

// 记录慢查询
db.Aop.OnLogExecuting = (sql, pars) =>
{
    Console.WriteLine($"开始执行: {DateTime.Now}");
};

db.Aop.OnLogExecuted = (sql, pars) =>
{
    Console.WriteLine($"执行完成: {DateTime.Now}");
};

// 监控慢查询
var slowQueryThreshold = 1000; // 1秒
db.Aop.OnLogExecuting = (sql, pars) =>
{
    db.Ado.SqlExecutionTime = Stopwatch.StartNew();
};

db.Aop.OnLogExecuted = (sql, pars) =>
{
    var sw = db.Ado.SqlExecutionTime;
    sw.Stop();
    if (sw.ElapsedMilliseconds > slowQueryThreshold)
    {
        Console.WriteLine($"慢查询警告: {sw.ElapsedMilliseconds}ms");
        Console.WriteLine($"SQL: {sql}");
    }
};

9.4.2 性能监控

实现完整的性能监控系统：

public class SqlPerformanceMonitor
{
    private static readonly ConcurrentDictionary<string, SqlStatistics> _statistics 
        = new ConcurrentDictionary<string, SqlStatistics>();
    
    public static void Initialize(SqlSugarClient db)
    {
        db.Aop.OnLogExecuting = (sql, pars) =>
        {
            var key = GetSqlKey(sql);
            var stats = _statistics.GetOrAdd(key, new SqlStatistics { Sql = sql });
            stats.StartExecution();
        };
        
        db.Aop.OnLogExecuted = (sql, pars) =>
        {
            var key = GetSqlKey(sql);
            if (_statistics.TryGetValue(key, out var stats))
            {
                stats.EndExecution();
            }
        };
        
        db.Aop.OnError = (exp) =>
        {
            var sql = exp.Sql;
            var key = GetSqlKey(sql);
            if (_statistics.TryGetValue(key, out var stats))
            {
                stats.RecordError();
            }
        };
    }
    
    private static string GetSqlKey(string sql)
    {
        // 提取SQL模板（去除参数值）
        return Regex.Replace(sql, @"'[^']*'|\d+", "?");
    }
    
    public static List<SqlStatistics> GetTopSlowQueries(int top = 10)
    {
        return _statistics.Values
            .OrderByDescending(s => s.AverageExecutionTime)
            .Take(top)
            .ToList();
    }
}

public class SqlStatistics
{
    public string Sql { get; set; }
    public int ExecutionCount { get; set; }
    public int ErrorCount { get; set; }
    public long TotalExecutionTime { get; set; }
    public long MaxExecutionTime { get; set; }
    public long MinExecutionTime { get; set; } = long.MaxValue;
    public double AverageExecutionTime => ExecutionCount > 0 
        ? TotalExecutionTime / (double)ExecutionCount : 0;
    
    private Stopwatch _currentExecution;
    
    public void StartExecution()
    {
        _currentExecution = Stopwatch.StartNew();
    }
    
    public void EndExecution()
    {
        if (_currentExecution != null)
        {
            _currentExecution.Stop();
            var elapsed = _currentExecution.ElapsedMilliseconds;
            
            ExecutionCount++;
            TotalExecutionTime += elapsed;
            MaxExecutionTime = Math.Max(MaxExecutionTime, elapsed);
            MinExecutionTime = Math.Min(MinExecutionTime, elapsed);
        }
    }
    
    public void RecordError()
    {
        ErrorCount++;
    }
}

9.5 最佳实践

性能优化清单

// 1. 只查询需要的字段
var list = db.Queryable<Student>()
    .Select(s => new { s.Id, s.Name, s.Email })
    .ToList();

// 2. 使用NoLock提示（SQL Server）
var data = db.Queryable<Student>()
    .With(SqlWith.NoLock)
    .ToList();

// 3. 避免N+1查询，使用Include
var students = db.Queryable<Student>()
    .Includes(s => s.Class)
    .ToList();

// 4. 使用分页避免查询大量数据
// ❌ 错误：查询所有数据
var allData = db.Queryable<Student>().ToList();

// ✅ 正确：使用分页
var pageData = db.Queryable<Student>().ToPageList(1, 100, ref total);

// 5. 合理使用缓存
var cachedData = db.Queryable<Dictionary>()
    .Where(d => d.Type == "Country")
    .WithCache(3600) // 字典数据缓存1小时
    .ToList();

// 6. 批量操作代替循环
// ❌ 错误
foreach (var id in ids)
{
    db.Deleteable<Student>().Where(s => s.Id == id).ExecuteCommand();
}

// ✅ 正确
db.Deleteable<Student>().In(ids).ExecuteCommand();

// 7. 使用异步操作
public async Task<List<Student>> GetDataAsync()
{
    return await db.Queryable<Student>().ToListAsync();
}

// 8. 关闭不必要的追踪
var list = db.Queryable<Student>()
    .AsNoTracking() // 只读查询
    .ToList();

常见性能陷阱

// 陷阱1：在循环中执行查询
// ❌ 错误
foreach (var classId in classIds)
{
    var students = db.Queryable<Student>()
        .Where(s => s.ClassId == classId)
        .ToList();
}

// ✅ 正确
var students = db.Queryable<Student>()
    .Where(s => classIds.Contains(s.ClassId))
    .ToList();

// 陷阱2：查询后在内存中过滤
// ❌ 错误
var allStudents = db.Queryable<Student>().ToList();
var filtered = allStudents.Where(s => s.Age > 18).ToList();

// ✅ 正确
var filtered = db.Queryable<Student>()
    .Where(s => s.Age > 18)
    .ToList();

// 陷阱3：不使用参数化查询
// ❌ 错误：SQL注入风险
var name = Request["name"];
var sql = $"SELECT * FROM student WHERE name = '{name}'";

// ✅ 正确
var students = db.Queryable<Student>()
    .Where(s => s.Name == name)
    .ToList();

9.6 本章小结

本章详细介绍了SqlSugar的分页查询和性能优化技术：

分页查询：掌握了ToPageList、Skip/Take等分页方法，以及如何封装通用的分页结果类
缓存优化：学会了使用查询缓存和自定义缓存服务来提升查询性能
索引优化：了解了如何创建和管理索引，以及索引对查询性能的影响
批量操作：掌握了批量插入、更新、删除等高效操作方法
异步编程：学会了使用异步方法提高应用程序的响应性和吞吐量
性能监控：实现了SQL分析和性能监控系统，能够识别和优化慢查询
最佳实践：总结了性能优化的各种技巧和需要避免的常见陷阱

通过本章的学习，您应该能够编写高性能的数据访问代码，并能够对现有系统进行性能分析和优化。在下一章中，我们将学习SqlSugar的分表分库与多租户功能，这对于构建大型分布式系统至关重要。

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 我才是银古

第九章：分页查询与性能优化

目录

9.1 分页查询基础

9.1.1 ToPageList方法

9.1.2 Skip和Take方法

9.1.3 分页参数封装

9.2 性能优化技术

9.2.1 查询缓存

9.2.2 索引优化

9.2.3 批量操作

9.3 异步操作

9.3.1 异步查询

9.3.2 异步增删改

9.4 SQL性能分析

9.4.1 SQL分析器

9.4.2 性能监控

9.5 最佳实践

性能优化清单

常见性能陷阱

9.6 本章小结