合理设计复合索引并使用游标分页可显著提升 mysql 分页效率，避免大偏移带来的性能问题，同时通过索引优化、近似统计和缓存机制改善 count 查询性能。
在 MySQL 中实现高效分页统计，关键在于合理设计索引以支持分页查询和聚合操作。如果索引设计不当，随着数据量增长，分页查询尤其是 OFFSET 较大的请求会变得非常慢。以下是实用的设计思路和优化建议。
理解分页场景与 性能瓶颈
常见的分页语句如下：
select * FROM orders WHERE status = 'completed' ORDER BY created_at DESC LIMIT 10 OFFSET 50000;
问题在于，MySQL 需要先扫描前 50000 + 10 条记录，再丢弃前 50000 条。当偏移量大时，即使有索引，也会导致大量无效扫描。
同时，若还需执行统计（如总行数）：
SELECT COUNT(*) FROM orders WHERE status = 'completed';
这个统计查询在没有合适索引时会触发全表扫描，严重影响性能。
为分页查询创建复合索引
针对带条件和排序的分页，应建立覆盖查询条件、排序字段和主键的复合索引。
例如，对上面的查询：
CREATE INDEX idx_status_created ON orders (status, created_at DESC, id);
这个索引的作用：
- 直接过滤
status = 'completed' - 按
created_at倒序 排列，避免额外排序 - 包含
id可支持回表或作为游标使用
注意：将等值条件字段（如status）放在复合索引最前面，然后是范围或排序字段。
避免大偏移，使用游标分页
用 OFFSET 进行深度分页效率低，推荐改用基于游标的分页（也叫“键集分页”）。
假设上一页最后一条记录的 created_at 是'2024-01-01 10:00:00'，且id=1000，下一页查询为：
SELECT * FROM orders WHERE status = ‘completed’
AND (created_at < ‘2024-01-01 10:00:00’ OR (created_at = ‘2024-01-01 10:00:00’ AND id < 1000))
ORDER BY created_at DESC, id DESC
LIMIT 10;
这种写法能利用索引快速定位起始位置，跳过所有前置数据，性能稳定。
优化统计查询性能
COUNT(*) 在大表上代价高，特别是带条件时。优化方式包括：
- 确保统计条件字段有索引。例如
status字段建索引后，COUNT(*)可走索引扫描而非全表 - 使用近似值：执行
EXPLAIN SELECT ……获取行数估算，适用于不要求精确的场景 - 维护计数缓存：通过触发器或应用层在redis 等缓存中维护符合条件的记录数
- 对于复杂统计，考虑使用物化视图或汇总表定期更新结果
基本上就这些。核心是根据查询模式设计复合索引，避免大偏移，用游标替代 OFFSET，并对统计做针对性优化。索引不是越多越好，要结合实际查询频率和数据分布来权衡。
