LIMIT 100000, 20 慢是因为MySQL需扫描跳过前100020行，即使有索引也会频繁回表触发随机IO；优化方案包括覆盖索引子查询+JOIN或游标分页。
为什么 LIMIT 100000, 20 会慢得离谱
MySQL 执行 LIMIT offset, size 时，必须先扫描并跳过前 offset + size 行，再返回最后 size 行。哪怕有索引，只要查询字段不在索引里，它就得反复回表——每跳过一行，都可能触发一次随机磁盘 IO。偏移量到十万级，实际读取的行数远超必要，但全白干了。
SELECT * 场景下用覆盖索引 + JOIN 的实操写法
核心是把「找数据」和「取数据」拆开：子查询只走索引捞出主键，外层用主键精准回查。前提是排序字段和过滤条件能被同一联合索引覆盖。
- 必须建复合索引，顺序要匹配查询逻辑：比如
WHERE status = 1 ORDER BY created_at DESC，索引就得是INDEX(status, created_at, id)（id放最后，确保子查询能直接拿到主键） - 子查询不能带
SELECT *，只能选id；否则无法利用覆盖索引，还是会回表 - 外层
JOIN必须用主键等值连接，不能用IN或范围，否则优化器可能放弃索引
示例：
SELECT t1.* FROM orders t1 INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 100000, 20 ) t2 ON t1.id = t2.id;
什么时候覆盖索引方案会失效
这个方案不是万能的，几个关键卡点容易被忽略：
- 子查询中
ORDER BY字段没索引，或索引顺序错（比如建了INDEX(created_at)却没包含status），子查询本身就会变慢 - 如果
WHERE条件用了IN或范围（如status IN (1,2)），那么created_at就无法用于索引排序，整个子查询退化为文件排序（Using filesort） - MySQL 5.6 以下版本对这种写法优化不足，需手动检查执行计划是否出现
Using index - 如果业务要求返回大量字段（比如 20+ 列），而覆盖索引太宽，B+ 树层级变深，反而影响定位效率
比覆盖索引更稳的替代：游标分页（WHERE id ）
当偏移量持续增长、且前端只支持“下一页”时，游标分页比任何 LIMIT offset 方案都可靠。它不依赖行号，而是用上一页末尾的 id 值做边界条件，起点可直接通过索引定位。
- 必须保证排序字段单调唯一：首选自增主键
id；若用时间字段（如created_at），务必补上id二级排序：ORDER BY created_at DESC, id DESC，对应条件写成WHERE (created_at, id) - 不能混用 ASC/DESC：上一页是
ORDER BY id DESC，下一页也必须保持同方向，否则索引无法范围扫描 - 前端无法跳页（比如输入“第 500 页”），这是设计约束，不是 bug
第一次查：
SELECT * FROM orders WHERE status = 1 ORDER BY id DESC LIMIT 20;
拿到最小 id（比如 5002341）后，下一页就是：
SELECT * FROM orders WHERE status = 1 AND id < 5002341 ORDER BY id DESC LIMIT 20;
真正难的不是写对 SQL，而是让业务接受「不能任意跳页」和「排序字段必须严格可控」这两个前提。一旦越界，所有优化都归零。
