MySQL 处理 LIMIT 100000, 10 深分页时，会扫描 100010 行数据并丢弃前 100000 行，导致 IO 和 CPU 开销剧增。优化核心是减少回表次数，最推荐采用“延迟关联”方案保留跳页功能，或“游标法”用于连续翻页场景。
先说结论：深分页慢的本质是 OFFSET 导致的大量无效扫描和回表，必须通过索引优化或改写 SQL 逻辑来解决。
- 先定位：使用 EXPLAIN 查看 rows 扫描行数，确认是否出现 Using filesort 或大量回表。
- 先做：优先尝试“子查询 + 延迟关联”改写 SQL，若业务允许改为“WHERE id > last_id”游标模式。
- 再验证：观察慢查询日志中 Rows_examined 是否从百万级降至十位数级别。
命令速用版
根据业务场景选择以下两种 SQL 改写方案，直接替换原有分页语句。
方案一：延迟关联（保留跳页功能）
SELECT a.* FROM account a INNER JOIN ( SELECT id FROM account WHERE update_time > '2025-01-01' ORDER BY id LIMIT 1000000, 10 ) b ON a.id = b.id;方案二：游标法（仅适用于连续翻页）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;为什么会这样
LIMIT 深分页慢的根本原因是 MySQL 执行逻辑并非“直接跳转”，而是“从头扫描”。当执行 LIMIT 100000, 10 时，数据库必须从索引树或表中逐行扫描，累计够 100010 行后，丢弃前 100000 行，仅返回最后 10 行。如果查询包含 ORDER BY 且未走覆盖索引，每一行扫描都可能触发回表操作，导致磁盘 IO 和 CPU 消耗随 OFFSET 增大呈线性增长。
分步处理
第一步：检查索引覆盖情况
确保 ORDER BY 字段和 WHERE 条件字段建有索引。若使用延迟关联，子查询中只 SELECT 主键 ID，利用覆盖索引避免回表。
第二步：改写 SQL 语句
将原查询拆分为两步：先在索引树上定位目标 ID，再回表获取完整数据。对于连续翻页场景，记录上一页最大 ID，下一页使用 WHERE id > last_id 查询。
第三步：下推查询条件
若原有 WHERE 条件（如 update_time > '...'），必须写在子查询内部，防止外层 JOIN 放大结果集导致性能回退。
怎么验证是否生效
使用 EXPLAIN 命令分析优化后的 SQL，重点关注 rows 字段。优化前 rows 可能显示为 100010 或更高，优化后子查询部分的 rows 应显著降低。同时检查慢查询日志，确认 Rows_examined 从百万级下降，且 Extra 字段中不再出现 Using filesort。
常见坑
1. 索引未命中导致回表：若 ORDER BY 字段无索引，MySQL 仍会进行文件排序，优化效果大打折扣。
2. 外层 JOIN 放大结果：复合条件未下推到子查询时，外层关联可能扫描更多行，需确保 WHERE 条件在子查询内生效。
3. ID 不连续导致游标失效：若使用游标法但 ID 存在删除空洞，可能导致分页数据遗漏，需业务层确认 ID 连续性或改用时间戳锚点。
常见问题
为什么 LIMIT 偏移量越大查询越慢？
因为 MySQL 需要扫描 offset + size 行数据并丢弃前 offset 行，偏移量越大扫描行数越多，回表开销随之增加。
延迟关联和游标法哪个更好？
游标法性能最好但无法跳页，适合瀑布流；延迟关联兼容传统分页组件，适合需要随机跳转页码的场景。
ID 不连续能用游标法吗？
可以，但需确保上一页最后一条记录的 ID 准确，若有数据删除可能导致中间数据跳过，业务允许轻微遗漏时使用。
参考来源
- MySQL 如何优化 LIMIT 深分页查询慢的问题_采用延迟关联与子查询技术
- 你还在用 LIMIT 100000,10?献上分页查询优化技巧 - 腾讯云开发者社区 - 腾讯云
- MySQL 深分页问题与优化思路
- MySQL Limit 深分页优化方案
- MySQL 海量数据深分页优化
- MySQL 深分页性能优化终极指南：告别慢查询的 5 大方案