直接结论：用 limit offset, size 查几百万行后的数据性能必然崩，因 mysql 必须扫描并丢弃前 offset 行；优化方案包括延迟关联（先查 id 再 join）和游标分页（where 排序字段 > 上一页末值）。
直接结论：用 LIMIT offset, size 查几百万行后的数据，性能必然崩。这不是索引没加好，而是 MySQL 必须扫描并丢弃前 offset 行——哪怕你只想要 10 条。
为什么 LIMIT 1000000, 20 比 LIMIT 0, 20 慢几十倍
MySQL 不会“跳”到第 1000001 行，它得从头开始数：先按 ORDER BY 排序（走索引也得遍历 B+ 树叶子节点），再逐行读、逐行计数，直到凑够 1000020 行，最后扔掉前 1000000 行。
这个过程带来三重开销：
- 大量二级索引回表（如果
SELECT *或含非索引列） - B+ 树叶子节点跨页遍历，I/O 次数激增
- 排序缓冲区（
sort_buffer_size）可能溢出，触发磁盘临时文件
实测中，570 万行的表，LIMIT 800000, 20 耗时 2.1 秒；优化后降到 0.3 秒——差距不在 SQL 写法“炫技”，而在是否绕开了偏移量扫描。
SELECT * FROM t ORDER BY id LIMIT 100000, 10 怎么改才不回表炸
核心思路是把“查全行 → 舍弃大部分”改成“先精准定位 ID → 再按 ID 拿数据”。前提是：排序字段和过滤条件能走索引，且主键是高效查找锚点（如自增 id）。
典型错误写法（子查询带 LIMIT 直接报错）：
SELECT * FROM t WHERE id IN (SELECT id FROM t ORDER BY id LIMIT 100000, 10);
正确写法（加一层派生表绕过限制）：
SELECT t.* FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 10) AS tmp ON t.id = tmp.id;
- 子查询只走主键索引或覆盖索引，不回表
- 外层
JOIN只回表 10 次，而非 100010 次 - 必须确保
ORDER BY字段有索引，否则子查询本身就会filesort
游标分页（WHERE id > ? ORDER BY id LIMIT ?）怎么落地
这是真正规避 offset 的方案，但要求业务接受“只能下一页/上一页”，不能跳转任意页码。
- 前端需保存上一页最后一条记录的
id（比如last_id = 123456） - 下一页查询写成：
SELECT * FROM t WHERE id > 123456 ORDER BY id LIMIT 20 - 必须有唯一、递增、非空的排序字段（推荐自增主键或
created_at+ 主键组合） - 如果排序字段可能重复（如多个记录同秒创建），要补上主键避免漏数据：
WHERE (created_at, id) > ('2026-06-05 10:00:00', 999999) ORDER BY created_at, id LIMIT 20
注意：游标值不能来自用户输入，必须由服务端校验或生成，防止越权或错位。
容易被忽略的细节和兜底判断
覆盖索引优化和游标分页都依赖索引有效性。以下情况会让优化失效：
-
WHERE条件中用了函数（如WHERE DATE(create_time) = '2026-06-05'），导致索引无法下推 -
ORDER BY和WHERE字段未命中同一复合索引，触发Using filesort - 表有大量
UPDATE，导致 B+ 树分裂严重，叶子节点物理不连续，I/O 效率下降 - 缓存池（
innodb_buffer_pool_size）太小，热点索引页反复进出内存
当数据量突破千万且业务强依赖随机跳页（如后台导出第 5000 页），就该考虑架构级方案：把分页逻辑下沉到 Elasticsearch，或用物化视图预聚合。SQL 层优化有天花板，别硬扛。
