外观
MySQL深分页优化：LIMIT大偏移性能问题及方案
在后台管理系统、数据导出等业务场景中，很多开发者都遇到过「前几页翻页速度很快，翻到第100页之后接口响应直接超时」的问题，这类问题90%以上都源于LIMIT 大偏移导致的深分页性能陷阱。很多人第一反应是加索引，但加完普通索引之后性能提升依然不明显，核心原因是没有摸透MySQL LIMIT的底层执行逻辑。
深分页性能瓶颈根因分析
我们先看最常见的慢分页SQL示例，查询状态为已完成的订单，按创建时间倒序取第100001条开始的20条数据：
sql
SELECT * FROM order_info
WHERE status = 1
ORDER BY create_time DESC
LIMIT 100000, 20;很多人执行explain查看执行计划会发现，哪怕已经给create_time建了普通索引，这条SQL的执行效率依然很差。这是因为MySQL的LIMIT offset, rows执行逻辑是：先扫描offset + rows条数据，然后丢弃前offset条无效数据，最后返回剩下的rows条数据。 当offset达到10万级别时，MySQL需要先访问10万条索引记录，逐条回表取出所有字段，再把前10万条全部丢弃，仅返回最后20条数据。大量随机IO都消耗在了无效的回表操作上，随着偏移量增大，查询耗时会呈线性甚至指数级上升。
方案一：延迟关联改写优化
延迟关联是工业界最常用的深分页优化手段，核心思路是「先通过覆盖索引拿到主键ID，再用ID批量回表取全量数据」，把原本10万次的无效回表操作直接降到20次。 优化后的SQL示例如下：
sql
SELECT a.* FROM order_info a
INNER JOIN (
SELECT id FROM order_info
WHERE status = 1
ORDER BY create_time DESC
LIMIT 100000, 20
) b ON a.id = b.id
ORDER BY a.create_time DESC;我们只需要提前建好联合覆盖索引idx_status_create(status, create_time, id)，子查询部分完全可以在二级索引树上完成扫描，不需要任何回表操作，仅需要扫描100020条索引记录，拿到目标20条数据的主键ID之后，再通过主键索引回表20次拿到全量字段，整体性能相比原始写法可以提升数十倍。 这种方案的优势是不需要改动前端分页逻辑，支持任意跳页操作，适合后台管理系统这类需要直接跳转到指定页码的场景。
方案二：游标分页极致优化
如果你的业务场景支持用户连续翻页（比如APP端的信息流、内容列表），不需要支持直接跳转到任意页码，那么游标分页的性能上限比延迟关联还要高很多，哪怕翻到第1000页性能也不会出现衰减。 核心思路是把上一页最后一条记录的排序字段值作为「游标」，下一页查询直接从游标位置往后扫描，完全跳过前面所有的无效数据，offset直接降为0。 可运行的SQL示例如下：
sql
-- 第一页查询，返回20条记录，同时拿到最后一条的游标值：last_create_time、last_id
SELECT * FROM order_info
WHERE status = 1
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- 第二页及后续分页，传入上一页拿到的游标参数
SELECT * FROM order_info
WHERE status = 1
AND create_time < '2024-05-20 14:30:00'
AND id < 12345678
ORDER BY create_time DESC, id DESC
LIMIT 20;这里需要注意的细节是，排序字段必须加上主键id作为唯一后缀，避免出现多条记录的create_time完全相同的场景，导致分页时出现重复数据或者漏数据的问题。因为联合索引的有序性，MySQL可以直接通过B+树快速定位到游标对应的位置，之后仅需要向后扫描20条记录就可以返回结果，几乎没有任何额外开销。
两种方案选型对比
延迟关联改造成本最低，对业务侵入性小，适合需要支持跳页的后台系统，在offset不超过50万的场景下性能完全可以满足业务需求；游标分页的性能上限最高，适合连续翻页的C端信息流场景，哪怕千万级别的数据量也能做到毫秒级响应。 如果你线上还有大量深分页导致的慢查询不知道怎么定位根因，不妨使用站内的「免费慢查询日志分析工具」，一键解析全量慢日志，自动标记深分页、全表扫描等问题SQL，生成对应的索引优化和SQL改写建议，快速帮你定位并解决分页性能问题。
免责声明
本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。