mysql如何优化LIMIT深分页_mysql延迟关联优化技巧

limit 10000,20 越来越慢是因为mysql需扫描并丢弃前10000行，而非直接定位；延迟关联和游标分页可有效优化。

为什么 LIMIT 10000, 20 会越来越慢

MySQL 在执行深分页时，并不是跳过前 10000 行再取 20 行，而是先扫描并暂存前 10000 行（哪怕你不要），再丢弃它们——这个过程全在内存或临时表里做，索引也救不了。尤其当 ORDER BY 字段没走覆盖索引、或涉及 JOIN 时，性能断崖式下跌。

常见错误现象：EXPLAIN 显示 rows 高达几十万，Extra 出现 using filesort 或 Using temporary；线上接口响应从 50ms 涨到 2s+，且越往后翻越卡。

• 真正耗时的是“定位第 10000 条记录的位置”，不是“取 20 条”
• 如果排序字段有重复值（比如多个记录 created_at 相同），MySQL 还可能因不确定顺序而额外排序
• OFFSET 越大，InnoDB 越频繁回表，Buffer Pool 压力也越大

用延迟关联（deferred join）绕过 OFFSET 扫描

核心思路：先用覆盖索引快速定位“第 N 条记录的主键”，再用主键回查完整数据。这能避免扫描无关行，把 LIMIT 压缩到主键索引上执行。

假设你要分页查用户列表，按 id 排序：

SELECT u.* FROM users u INNER JOIN (   SELECT id FROM users ORDER BY id LIMIT 10000, 20 ) AS tmp ON u.id = tmp.id;

这个 tmp 子查询只走 PRIMARY 索引，不回表、不读其他字段，快得多。

• 必须确保 ORDER BY 字段是索引前缀（最好是主键或唯一索引），否则子查询仍会慢
• 如果排序依据是 updated_at，得建联合索引 (updated_at, id)，否则无法稳定排序 + 快速定位
• JOIN 后不能加 WHERE 过滤非索引字段，否则优化器可能放弃延迟关联策略

游标分页（cursor-based pagination）替代 LIMIT/OFFSET

适用于无限滚动、Feed 流等场景，本质是“记住上一页最后一条的排序值”，下一页直接查“比它大的第一条起”。彻底规避 OFFSET。

例如，上一页最后一条是 id = 10050，下一页就写：

SELECT * FROM users WHERE id > 10050 ORDER BY id LIMIT 20;

注意：必须用 >（或 ）配合严格单调字段（如自增 <code>id），不能用 updated_at 单独做游标——重复值会导致漏数据或重复。

• 如果业务要求按 updated_at 排序，游标必须是复合的：WHERE (updated_at, id) > ('2024-05-01', 9999)
• 前端需保存上一页末尾的完整游标值，不能只传时间戳
• 删除中间数据不影响游标逻辑，但新插入数据可能出现在“已翻过”的页里（这是业务可接受的权衡）

哪些情况延迟关联也救不了

延迟关联不是银弹。当排序字段本身无法高效定位，或者业务强依赖动态过滤时，它会失效甚至更慢。

典型翻车点：

• 排序字段是表达式，比如 ORDER BY Length(name) —— 没法走索引，子查询照样全表扫
• 分页前要 WHERE status = 'active'，但 status 没进排序索引 —— 子查询选出的 id 可能大量被外层 WHERE 过滤掉，实际回查远超 20 行
• 使用 union 或复杂视图，优化器无法下推 LIMIT 到子查询
• MySQL 5.6 以下版本对子查询优化差，延迟关联效果打折扣

这时候得换思路：预生成分页映射表、改用 ES 分页、或者干脆限制前端最多翻到第 200 页——很多所谓“深分页需求”，其实是产品没想清要不要真支持。