在mysql的实际使用中，分页查询是非常常见的需求，我们通常会使用limit语句来实现分页功能。但当查询的偏移量越来越大时，limit查询的性能会出现明显的下降，很多开发者都会遇到这个问题，需要找到对应的优化方法。
limit查询性能下降的原因
mysql执行limit offset, size语句时，会先扫描offset + size条记录，然后丢弃前offset条记录，只返回后面的size条记录。当offset数值很大时，需要扫描的记录数非常多，即使有索引，也可能需要遍历大量的索引节点，导致查询耗时增加。
比如我们要查询第1000页的数据，每页10条，执行的语句是select * from user limit 9990, 10，mysql需要扫描前10000条记录，然后丢弃前9990条，只返回最后10条，扫描成本很高。
mysql limit查询优化方法
1. 使用覆盖索引减少回表
如果查询的字段都在索引中，mysql可以直接从索引中获取数据，不需要回表查询聚簇索引，能大幅减少IO消耗。我们可以在查询中只返回索引包含的字段，或者建立合适的联合索引覆盖查询字段。
比如user表有id、name、age字段，我们建立联合索引idx_age_name (age, name)，查询年龄大于20的用户姓名时，使用如下语句：
-- 覆盖索引优化，不需要回表 select age, name from user where age > 20 limit 9990, 10;
2. 基于自增主键的分页优化
如果表的主键是自增的，且没有频繁的删除操作导致主键不连续，我们可以利用主键来进行分页，避免大的偏移量扫描。原理是先找到上一页的最后一个主键值，然后查询大于这个值的记录。
比如上一页最后一条数据的id是9990，那么查询下一页的语句可以写成：
-- 基于自增主键分页，避免大偏移量 select * from user where id > 9990 limit 10;
这种方式只需要扫描10条记录，性能比传统的limit偏移量查询好很多，但是要求主键连续，且排序规则是和主键顺序一致的。
3. 延迟关联优化
如果查询需要返回所有字段，无法使用覆盖索引，也可以使用延迟关联的方式优化。先通过索引查询出需要的主键id，然后再通过主键id关联原表查询所有字段，减少回表的次数。
示例语句如下：
-- 延迟关联优化
select * from user
inner join (
select id from user
where age > 20
order by id
limit 9990, 10
) as tmp on user.id = tmp.id;
子查询中只查询主键id，利用索引快速定位到需要的10个id，然后再通过id关联原表查询完整数据，比直接查询所有字段再偏移的效率高很多。
4. 限制最大分页页码
从业务层面考虑，很多场景下用户不会翻到非常靠后的页码，我们可以限制分页的最大页码，比如最多只允许查询前100页，超过这个范围的查询直接返回空结果或者提示。这样从业务上避免了大偏移量的limit查询，也是很多大型系统的常用做法。
不同优化方案的性能对比
我们在一张有100万条记录的user表上做测试，查询第1000页（每页10条）的数据，不同方案的执行耗时如下：
| 优化方案 | 执行耗时（毫秒） |
|---|---|
| 传统limit 9990,10查询 | 320 |
| 覆盖索引优化 | 45 |
| 基于自增主键分页 | 12 |
| 延迟关联优化 | 28 |
从测试结果可以看出，不同的优化方案都能不同程度的提升分页查询的性能，开发者可以根据实际的业务场景和表结构选择合适的方案。
注意事项
- 使用基于自增主键的分页时，要确保主键是连续的，如果表有频繁的删除操作导致主键断层，可能会出现数据缺失的问题。
- 建立覆盖索引时要权衡索引的维护成本，不要为了单个查询建立过多的联合索引，避免影响写入性能。
- 延迟关联优化适合需要返回所有字段的场景，如果只需要返回部分字段，优先使用覆盖索引优化。
以上就是mysql limit查询优化的几种常用方法，合理运用这些方案可以有效提升分页查询的性能，解决大偏移量分页慢的问题。