mysql执行limit 100000,20需扫描前100020行并丢弃前100000行，每行回表引发随机io，导致i/o与cpu开销剧增。
为什么LIMIT 100000, 20会触发大量随机IO
因为MySQL必须从索引头开始扫描，跳过前100000行再取20行——哪怕只返回20条，也要做约10万次回表。每回一次表，就是一次聚簇索引的随机磁盘读；偏移量越大，无效IO越多，CPU卡在等待上。
常见错误现象：EXPLAIN里rows显示几十万，但Extra出现Using filesort或Using temporary；或者key用了索引，Extra却是Using where（说明WHERE没被索引完全覆盖）。
- ORDER BY字段没索引 → 必然
Using filesort - WHERE含
IN、OR或函数（如WHERE DATE(created_at) = '2024-01-01'）→ 索引可能部分失效 - SELECT *且非主键字段不在索引中 → 每行都得回表，IO压力翻倍
怎么建复合索引让子查询走覆盖索引
核心是让子查询只查索引列、不回表、不排序临时化。索引顺序必须严格匹配查询逻辑：过滤字段在前、排序字段居中、主键（或唯一ID）放最后。
例如查询是WHERE status = 1 ORDER BY created_at DESC，索引就得建为INDEX(status, created_at, id)——id放末尾，子查询SELECT id才能直接从叶子节点拿到全部数据。
- 子查询只能写
SELECT id，不能加其他字段，否则优化器大概率放弃覆盖 - 避免在索引字段上存NULL值，否则MySQL可能跳过该索引做ORDER BY
- 不要把太多业务字段塞进覆盖索引，B+树层级变深反而拖慢范围扫描
子查询+JOIN写法的关键细节
拆成“找ID”和“取数据”两步，外层用INNER JOIN关联，而不是WHERE id IN (SELECT ...)——后者在MySQL 5.7+仍可能退化为循环嵌套，性能不稳。
确认执行计划中子查询那行的Extra是Using index（不是Using 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;
- 外层必须用
ON t1.id = t2.id，不能改用WHERE t1.id IN (t2.id) - 如果
IN列表超长（比如>1000个ID），要考虑分批或改用游标 - JOIN后实际只回表20次，比原语句少99980次随机IO
覆盖索引方案失效的硬性卡点
这个方案看着简单，但只要索引结构和查询语义没咬合上，就立刻退回原形。最容易忽略的是排序字段的位置和条件表达式是否“干净”。
-
ORDER BY created_at DESC，但索引是(id, created_at)→ 无法利用索引排序，仍Using filesort - WHERE用
status IN (1,2)→created_at无法用于索引排序，子查询变成全索引扫描+内存排序 - 隐式类型转换，比如
WHERE user_id = '123'（字段是INT）→ 索引失效
真正起效的前提，是子查询能被优化器识别为“仅扫描索引页、不访问数据页”。一旦出现Using where; Using index condition，就得回头检查索引定义和WHERE写法。
