LIMIT 100000, 20会慢是因为MySQL必须扫描并丢弃前100000行,而非跳转;本质是线性扫描offset+size行,触发大量回表和随机I/O,性能随偏移量线性下降。

直接用 LIMIT offset, size 查大偏移量,本质是让 MySQL 扫描并丢弃大量数据,不是“跳到第 N 条”,而是“数够 N 条再取”。覆盖索引能绕过回表、减少 I/O,但必须满足严格条件,否则优化器会直接放弃。
为什么 LIMIT 100000, 20 会触发大量随机 I/O
MySQL 执行这类语句时,并不会跳过前 10 万行索引节点,而是逐行遍历二级索引叶节点,每遇到一个匹配的主键,就回表查整行(即“回表”)。如果 SELECT * 或查询字段超出索引范围,哪怕索引能定位,也得额外走聚簇索引——10 万次随机读,在 HDD 上可能耗尽 IOPS;在 SSD 上也会因高延迟拖慢整体响应。
常见错误现象包括:
-
EXPLAIN显示type = index或ref,但Extra列出现Using filesort或Using temporary - 索引命中了,
rows值却远大于size(比如rows=987654,limit=20) - 执行计划里子查询的
Extra是Using where而非Using index——说明 WHERE 条件没被索引完全覆盖
覆盖索引子查询必须满足的三个硬性条件
延迟关联(JOIN 子查询)不是加个索引就能生效,以下任一不满足,就会退化为全索引扫描或文件排序:
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 复合索引字段顺序必须严格匹配查询逻辑:例如
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+ 仍可能触发嵌套循环,失去覆盖优势
正确写法示例:
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条件含IN、OR或函数:如status IN (1,2),会导致created_at无法用于排序,子查询变成全索引扫描 + 内存排序 - 强行把太多业务字段塞进覆盖索引:比如加了 10 个
VARCHAR(255),B+ 树页变大,单次定位id的深度增加,反而比简单主键扫描更慢
替代方案:书签分页比覆盖索引更稳,但不能跳页
如果业务允许滚动加载(如 feed 流、日志列表),WHERE (sort_col, id) > (?, ?) ORDER BY sort_col, id LIMIT 20 是更可靠的选择。它不依赖 OFFSET,每次只查下一页起点之后的数据,性能恒定。
但它要求客户端记住上一页末尾的排序值组合,且无法实现“跳转到第 100 页”这种操作。真正需要跳页的场景,覆盖索引 + 延迟关联仍是目前最通用的解法,只是对索引设计和 SQL 写法极其敏感——稍有偏差,性能就打回原形。

















