MySQL和SQLSugar百万条数据查询分页优化

测试结果：

没加任何索引 或者 优化方法--17.89S

优化之后 -- 3.67S

方法如下：

Repository基类：

public class Repository<T> : IRepository<T> where T : class, new()
{
    private readonly ISqlSugarClient _db;

    public Repository(ISqlSugarClient db)
    {
        _db = db;
    }
    // 查询列表，支持条件和分页
    public async Task<List<T>> GetQueryable(Expression<Func<T, bool>> filter = null, int? pageIndex = null, int? pageSize = null)
    {
        try
        {
            var query = _db.Queryable<T>();

            // 应用过滤条件
            if (filter != null)
            {
                query = query.Where(filter);
            }

            // 如果提供了分页参数
            if (pageIndex.HasValue && pageSize.HasValue)
            {
                query = query.Skip(pageSize.Value * (pageIndex.Value - 1)).Take(pageSize.Value);
            }

            return await query.ToListAsync();
        }
        catch (Exception ex)
        {
            // 处理异常或记录日志
            // 可考虑使用日志库如 NLog 或 Serilog
            return new List<T>();  // 返回空列表而不是 null
        }
    }
    public async Task<(int Count, List<T> List)> CountAndToListAsync(Expression<Func<T, bool>> filter = null, int? pageIndex = null, int? pageSize = null)
    {
        try
        {
            var query = _db.Queryable<T>(); // 用合适的方式获取 IQueryable<T>

            // 应用过滤条件
            if (filter != null)
            {
                query = query.Where(filter);
            }

            // 记录总数
            var count = await query.CountAsync();

            // 如果提供了分页参数
            if (pageIndex.HasValue && pageSize.HasValue)
            {
                query = query.Skip(pageSize.Value * (pageIndex.Value - 1))
                             .Take(pageSize.Value);
            }

            // 执行查询
            var list = await query.ToListAsync();

            return (count, list); // 返回记录数和列表
        }
        catch (Exception ex)
        {
            // 处理异常或记录日志
            // 记录日志代码
            return (0, new List<T>()); // 返回空列表和记录数为0
        }
    }
}

IRepository实现：

public interface IRepository<T> where T : class, new()
{
    Task<List<T>> GetQueryable(Expression<Func<T, bool>> filter = null, int? pageIndex = null, int? pageSize = null);
    Task<(int Count, List<T> List)> CountAndToListAsync(Expression<Func<T, bool>> filter = null, int? pageIndex = null, int? pageSize = null); 
}

ExpressionExtensions类：

public static class ExpressionExtensions
{
    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var parameter = Expression.Parameter(typeof(T));

        var combined = new ReplaceParametersVisitor(parameter, expr1.Body).Visit(expr1.Body);
        var second = new ReplaceParametersVisitor(parameter, expr2.Body).Visit(expr2.Body);

        var andExpression = Expression.AndAlso(combined, second);
        return Expression.Lambda<Func<T, bool>>(andExpression, parameter);
    }

    private class ReplaceParametersVisitor : ExpressionVisitor
    {
        private readonly Expression _replacement;
        private readonly Expression _oldValue;

        public ReplaceParametersVisitor(Expression oldValue, Expression replacement)
        {
            _oldValue = oldValue;
            _replacement = replacement;
        }

        public override Expression Visit(Expression node)
        {
            return node == _oldValue ? _replacement : base.Visit(node);
        }
    }
}

服务实现：

public class VIPService : IDynamicWebApi
{
    public readonly SqlSugarContext db;
    private readonly IRepository<SavingsManagement> Savingsrepository;
    private readonly IMapper mapper;
    public VIPService(SqlSugarContext context,  IMapper mapper,  IRepository<SavingsManagement> savingsrepository)
    {
        this.db = context;
        _repository = repository;
        this.mapper = mapper;
        this.Savingsrepository = savingsrepository;
    }
    /// <summary>
    /// SQLSugar创建表
    /// </summary>
    /// <returns></returns>
    [HttpGet]
    public async Task<ResultDTO<bool>> CreateTable()
    {
        db.InitializeTables();
        return new ResultDTO<bool> { Code = CodeType.Success, Message = "执行成功!" };
    }
    /// <summary>
    /// 获取储值+条件查询+分页显示
    /// </summary>
    /// <returns></returns>
    [HttpGet]
    public async Task<ResultDTO<PageDTO<List<SavingsManagement>>>> GetSavingPageDepartments(int PageIndex, int PageSize, int? Member_Grade, string SearchText)
    {
        // 构建查询条件
        Expression<Func<SavingsManagement, bool>> filter = x => true;

        // 添加 Member_Grade 过滤条件
        if (Member_Grade.HasValue)
        {
            filter = filter.And(x => x.Member_Grade == Member_Grade.Value);
        }

        // 处理 SearchText
        if (!string.IsNullOrWhiteSpace(SearchText))
        {
            var searchParts = SearchText.Split('/');
            string namePart = searchParts.ElementAtOrDefault(0) ?? string.Empty; // 姓名
            string cardNumberPart = searchParts.ElementAtOrDefault(1) ?? string.Empty; // 卡号
            string phoneNumberPart = searchParts.ElementAtOrDefault(2) ?? string.Empty; // 电话号码

            if (!string.IsNullOrWhiteSpace(namePart))
            {
                filter = filter.And(x => x.Member_Name.Contains(namePart));
            }
            if (!string.IsNullOrWhiteSpace(cardNumberPart))
            {
                filter = filter.And(x => x.Card_Number.ToString().Contains(cardNumberPart));
            }
            if (!string.IsNullOrWhiteSpace(phoneNumberPart))
            {
                filter = filter.And(x => x.Phone.Contains(phoneNumberPart));
            }
        }

        // 使用 GetQueryable 一次性查询所需数据和总记录数
        var (count, list) = await Savingsrepository.CountAndToListAsync(filter, PageIndex, PageSize);

        return new ResultDTO<PageDTO<List<SavingsManagement>>>
        {
            Code = 0,
            Message = "获取成功",
            Data = new PageDTO<List<SavingsManagement>>
            {
                Total = count,
                Page = (int)Math.Ceiling(count / (double)PageSize),
                List = list
            }
        };
    }
}