ASP.NET Core 6中用Dapper实现百万级数据高效分页及总行数统计

两种方案的对比与性能分析 #

1. 单独执行COUNT(*)查询 #

• 做法：先执行SELECT COUNT(*) FROM {joinString} WHERE {Condition}获取总行数，再运行你现有的分页查询拿当前页数据。
• 性能表现：
  • 优势：数据库可以针对COUNT查询单独做优化，比如利用覆盖索引（如果WHERE条件、关联表字段有合适索引的话），不会和分页查询的排序、OFFSET操作互相干扰。百万级数据下，只要索引到位，两次查询的总耗时可控。
  • 劣势：需要两次数据库请求，但这个开销在多数场景下可以忽略。
• 适用场景：当WHERE条件复杂、关联表较多时，单独查COUNT的稳定性更好，不容易因为额外计算拖慢分页。

2. 使用COUNT(*) OVER()窗口函数 #

• 做法：在分页查询里加个COUNT(*) OVER() AS TotalCount，一次查询同时返回当前页数据和总行数，示例SQL：

SELECT {fieldsString}, COUNT(*) OVER() AS TotalCount
FROM {joinString} 
WHERE {Condition} 
ORDER BY {orderBy} DESC 
OFFSET @PageOffset ROWS FETCH NEXT @PageNext ROWS ONLY;

• 性能表现：
  • 优势：只需要一次数据库请求，减少网络往返的开销。
  • 劣势：数据库要在执行分页的同时计算全量符合条件的行数，当数据量逼近千万级、或者没有合适索引时，窗口函数的额外计算会拖慢整个查询。另外返回的每一行都带TotalCount，代码里取第一行的这个值就行，别重复处理。
• 适用场景：WHERE条件简单、索引覆盖良好，且数据量在百万级（没到千万）时，这个方案效率更高。

更优的替代方案 #

1. 用数据库统计信息估算行数（适合不需要精确值的场景） #

像SQL Server这类数据库会维护表的统计信息，可以通过系统视图快速拿到近似行数，适合前端分页只需要大概页数范围的场景，示例SQL：

SELECT SUM(rows) AS ApproximateTotalCount
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('YourTableName') 
AND index_id < 2;

注意：这个是估算值，不是精确的，数据更新频繁的话误差会变大。

2. 预计算总行数（适合数据更新不频繁的场景） #

如果你的数据表更新频率低（比如每天批量更新一次），可以定时把符合条件的总行数缓存到Redis或者单独的统计表里，分页时直接读缓存值，完全避免每次计算全量行数。这是性能最优的方式，但要处理数据更新后的缓存同步问题，比如更新数据时触发缓存刷新。

大数据集分页的通用性能优化建议 #

• 别用大OFFSET：当OFFSET值很大（比如第1000页，每页10条，OFFSET=9990），数据库得扫描大量无关数据再跳过，性能会崩。改用键集分页，以上一页最后一条数据的排序字段（比如主键、CreateTime）作为条件，示例：

SELECT {fieldsString}
FROM {joinString}
WHERE {Condition} AND {orderBy} < @LastOrderValue
ORDER BY {orderBy} DESC
FETCH NEXT @PageNext ROWS ONLY;

这种方式能利用索引直接定位起始位置，性能不受页码影响。

• 建覆盖索引：针对WHERE条件、关联字段、ORDER BY字段创建覆盖索引，让数据库直接从索引里拿数据，不用回表查原数据。比如查询条件是Status = 1，排序是CreateTime DESC，就建个包含返回字段的索引IX_YourTable_Status_CreateTime。
• 别用SELECT*：只返回业务需要的字段，减少数据传输和内存占用。
• Dapper用QueryMultiple：用Dapper的QueryMultiple方法同时执行COUNT和分页查询，减少数据库连接开销，示例代码：

using var connection = new SqlConnection(connectionString);
var sql = @"
SELECT COUNT(*) FROM {joinString} WHERE {Condition};
SELECT {fieldsString} FROM {joinString} WHERE {Condition} ORDER BY {orderBy} DESC OFFSET @PageOffset ROWS FETCH NEXT @PageNext ROWS ONLY;
";
using var multi = connection.QueryMultiple(sql, new { PageOffset = pageOffset, PageNext = pageSize });
var totalCount = multi.ReadFirst<int>();
var data = multi.Read<YourModel>().ToList();

内容的提问来源于stack exchange，提问作者AminFarajzadeh