LIMIT 100000, 20慢是因为MySQL需扫描跳过前100020行，每行回表导致约10万次随机IO；优化方案包括覆盖索引子查询和游标分页。
为什么LIMIT 100000, 20会慢得离谱
MySQL执行LIMIT offset, size时，必须从头扫描并跳过前offset + size行，哪怕只返回最后20条。如果SELECT *且排序字段没走覆盖索引，每跳过一行都可能触发一次回表——也就是根据二级索引里的主键再去聚簇索引里随机读取整行。偏移量到十万级，实际产生的随机IO次数接近10万次，磁盘扛不住，CPU也卡在等待IO上。
常见错误现象：EXPLAIN显示type=ref但Extra列出现Using filesort或Using temporary；或者key用了索引，但rows值远大于limit指定的条数。
- ORDER BY字段没索引 → 必然
Using filesort - WHERE条件用了
IN、OR或函数 → 索引可能部分失效，排序无法复用索引 - 查询字段超出索引覆盖范围 → 即使索引能定位，仍要回表，放大IO压力
覆盖索引子查询+JOIN写法的关键细节
核心是把“找ID”和“取数据”拆开：子查询只走索引捞出主键，外层用主键精准回查。这要求子查询本身必须能完全走索引，不回表、不排序临时化。
实操要点：
- 复合索引顺序必须匹配查询逻辑：例如
WHERE status = 1 ORDER BY created_at DESC，索引就得是INDEX(status, created_at, id)——id放最后，确保子查询能直接覆盖输出 - 子查询只能
SELECT id，不能SELECT *或多余字段，否则优化器大概率放弃覆盖索引 - 外层必须用
INNER JOIN ... ON t1.id = t2.id，不能改用WHERE t1.id IN (SELECT ...)，后者在MySQL 5.7+仍可能退化为循环嵌套 - 确认执行计划中子查询的
Extra是Using index，不是Using where; Using index（后者说明WHERE条件没被索引完全覆盖）
示例：
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 status = 1 ORDER BY created_at，MySQL无法跳过status做范围扫描，排序仍要Using filesort - WHERE条件含
status IN (1,2)→created_at无法用于索引排序，子查询变成全索引扫描+内存排序 - 业务字段太多，强行塞进覆盖索引导致B+树层级变深：比如加了10个VARCHAR(255)，索引页变大，定位单个
id反而更慢 - MySQL版本低于5.6：旧版本对子查询优化不足，
EXPLAIN里可能出现DEPENDENT SUBQUERY，性能波动极大
比覆盖索引更稳的替代：游标分页怎么写才不翻车
当偏移量持续增长、前端只提供“下一页”按钮时，游标分页（键集分页）比任何LIMIT offset都可靠。它不依赖行号，而是用上一页末尾记录的排序键做边界。
关键约束必须满足：
- 排序字段必须单调且唯一：首选自增
id；若用时间字段（如created_at），必须补id二级排序：ORDER BY created_at DESC, id DESC - WHERE条件要严格对应：上一页最后一条是
(created_at = '2024-01-01', id = 999)，下一页就得写WHERE (created_at, id) < ('2024-01-01', 999)，不能漏括号，也不能混用>/< - 不能在游标条件里加额外WHERE：比如
WHERE status = 1 AND (created_at, id) < (...)，必须确保status已包含在复合索引最左前缀中，否则索引无法生效
正确写法示例：
SELECT *
FROM orders
WHERE status = 1
AND (created_at, id) < ('2024-01-01 10:20:30', 999)
ORDER BY created_at DESC, id DESC
LIMIT 20;真正容易被忽略的，是游标值本身的精度和一致性——比如前端传来的created_at如果只精确到秒，而数据库存的是微秒，就可能漏掉同一秒内的多条记录。生产环境务必用id兜底，且所有排序字段类型、时区、NULL处理方式必须前后端完全对齐。
