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

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的分表分库与多租户功能，这对于构建大型分布式系统至关重要。

znlgis 博客