摘要网上大量教程只讲最左匹配口诀很少讲底层 B 树、索引下推 ICP、回表、顺序/随机 IO、BufferPool 之间的完整链路。本文结合 Explain 执行计划、底层存储原理把面试高频坑一次性讲透。前置准备建表与复合索引CREATETABLEtest(idINTPRIMARYKEYAUTO_INCREMENT,aINT,bINT,cINT);-- 创建联合索引 idx(a,b,c)CREATEINDEXidx_abcONtest(a,b,c);复合索引idx(a,b,c)的 B 树叶子节点排序规则是优先按 a 排序a 相等按 b 排序b 相等按 c 排序叶子行末尾附带主键 id。上图演示了复合索引叶子页的排序逻辑。从图中可以看到a 相同的行被分到同一组a 相同后按 b 排序只有 (a,b) 都相同时c 才有序。行尾的主键 id 是后续回表的“钥匙”。因果链因为 InnoDB 的 B 树叶子行按 (a,b,c) 全局有序 → 所以索引定位时可以从 a 开始二分查找 → 但因为排序优先级是 a→b→c 依次递减 → 所以一旦 a 或 b 出现范围查询c 在叶子内部就不再有序。一、什么是最左匹配最左前缀原则核心复合索引要从索引定义的最左侧字段开始匹配连续前缀SQL 的 where 条件书写顺序不影响索引命中优化器会自动调整条件顺序。✅ 可以有效使用索引前缀wherea1wherea1andb2wherea1andb2andc3whereb2anda1-- where 条件顺序打乱优化器重排依旧命中索引❌ 无法使用索引缺失最左前缀whereb2whereb2andc3wherec5误区不是 where 子句写了 a/b/c 字段就一定能用索引必须要有索引定义的最左起始列。重点遇到范围查询后面字段无法做索引 seek between属于范围条件。一旦复合索引匹配中遇到范围查询范围之后的字段不能再利用索引有序性做快速定位seek。wherea10andb20andc5a等值匹配索引 seekB 树二分定位b范围条件索引 range 扫描c不能走索引 seek。因为 b 范围之后c 在 B 树叶子节点内部是无序的上图完整演示了where a10 and b20 and c5的执行过程a 先等值 seek 定位b 范围扫描命中连续 6 行但这 6 行里的 c 值5,12,5,3,40,5完全无序无法 seek 定位只能逐行判断。但是c 不是完全失效它可以交给索引下推 ICP 在二级索引页内过滤这点是很多博客遗漏的关键点。补充in 算不算范围in(1,2,3)不属于破坏索引的范围条件MySQL 内部等价多个 or 等值不会打断后面索引字段匹配后续字段依旧可以 seek。补充MySQL 8.0 索引跳跃扫描Skip ScanMySQL 8.0.13 引入 Skip Scan 优化。当缺失最左前缀时如where b2 and c3优化器可能通过扫描 a 的所有不同值在每个 a 值下分别做 b,c 的 seek来利用索引代价是扫描次数 a 的 distinct 数量。Explain 中typerangeUsing index for skip scan。二、索引下推 ICPIndex Condition PushdownICP 全称索引条件下推MySQL 5.6 之后支持Explain Extra 字段显示Using index condition。没有 ICP 时代执行流程BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层根据 a、b 条件扫描二级索引拿到主键 id 集合1逐个主键回表查聚簇索引完整行2返回整行数据3返回全部行数据4在 Server 层过滤 c5 条件丢弃不满足的数据5问题很多主键对应的行本来就不满足 c 条件白白执行大量回表随机 IO。开启 ICP 后流程BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层根据 a seekb 做 range 扫描1在二级索引页内直接利用 c 列过滤不满足直接丢弃2只把过滤后剩余的主键 id 回表查整行3返回整行数据4返回过滤后的行5✔ ICP 本质在二级索引页过滤数据减少需要回表的主键数量从而减少回表 IO。⚠ 注意ICP 只是过滤不能对 c 做索引 seek不能利用 c 的有序性快速定位区间。上图对比了有无 ICP 的核心差异无 ICP 时 6 行全部回表到 Server 层才发现 3 行不满足 c5回表白做开启 ICP 后存储引擎在索引页内先过滤 c5只剩 3 次回表随机 IO 直接减半。覆盖索引与 ICP 区分Explain ExtraUsing index覆盖索引直接从二级索引拿到全部查询字段完全不需要回表。Using index conditionICP部分条件索引层过滤仍然需要回表。三、回表是什么聚簇索引 vs 二级索引InnoDB 中聚簇索引主键索引叶子节点存储完整整行数据表数据本身就是主键 B 树。二级索引普通/复合索引叶子节点存储索引列 主键 id没有完整行。回表拿到二级索引叶子的主键 id再去主键 B 树查找完整行数据的过程。如果查询需要的全部字段都在二级索引内不需要读取完整行就是覆盖索引避免回表。-- 覆盖索引Extra: Using index无需回表selecta,b,cfromtestwherea10andb20;key_len判断复合索引用到多少字段key_len表示实际用到索引的字节长度。以a INT, b INT, c INT为例查询条件key_len含义where a105只用 aINT 4 字节 nullable 1 字节where a10 and b2010用到 a、bb 范围仍计入 key_lenwhere a10 and b2 and c515用到 a、b、c 全部面试技巧看到key_len10就知道只用到前两个字段key_len5说明只走了 ab 没参与索引定位。四、表空间与数据页组织页从哪里来在讲 IO 类型之前必须先回答一个问题回表时访问的页在磁盘上是怎么存的InnoDB 的数据最终持久化在表空间tablespace中。开启innodb_file_per_tableON时每张表对应一个独立的.ibd文件这就是该表的独立表空间。表空间内部是层级组织层级大小作用页Page16KB最小读写单位存实际数据行区Extent1MB 64 页空间分配的最小单位保证区内页物理连续段Segment变长逻辑概念如叶子节点段、非叶子节点段因果链因为 表空间按 extent 成片分配一次分 64 页物理连续 → 所以 同一段时期内分配的页物理位置大概率相邻 → 但因为 增删改导致页分裂、合并、回收再分配 → 所以 逻辑上页号相邻的两页磁盘位置可能已经分散 → 因此 判定顺序/随机 IO 不能看物理位置只能看页号访问次序关键认知B 树叶子节点的逻辑有序≠物理有序。表空间决定了页的物理落脚处而页号只是表空间内的逻辑编号。理解这一点才能真正理解下一节的顺序/随机 IO。五、顺序 IO、随机 IO不要再记死口诀网上流传二级索引扫描 顺序 IO回表 随机 IO。这句话只是绝大多数场景的经验总结不是铁律定义。InnoDB 判定顺序/随机访问模式InnoDB 看不到磁盘物理扇区看的是表空间页号的访问序列顺序访问模式顺序 IO页号持续递增向后访问触发 InnoDB 线性预读 read-ahead。哪怕磁盘物理页不连续只要访问次序连续向后就视为顺序访问。随机访问模式随机 IO页号跳跃无序访问无法触发预读机械磁盘会产生昂贵寻道开销。关键点顺序 IO、随机 IO 是访问模式不是索引自带属性。示例 1聚簇索引主键-- 主键连续读取页号递增顺序 IOselect*fromtestwhereidbetween1000and2000;-- 主键乱序跳跃读取页号到处跳随机 IOselect*fromtestwhereidin(1001,7,3900,56);普通回表为什么大多是随机 IO二级索引筛选出来的主键 id 集合排序规则跟随(a,b,c)主键 id 是乱序打散的拿着一堆无序 id 访问聚簇索引页号来回跳产生随机 IO。MRR 优化把回表随机 IO 转为顺序 IOMRRMulti-Range Read多范围读优化MySQL 官方专门解决回表大量随机 IO 的方案先从二级索引拿到一批待回表主键 id在内存缓冲区把主键 id 从小到大排序按主键升序访问聚簇索引页号递增随机 IO 变成顺序 IO。Explain 会看到 ExtraUsing MRR。MRR 充分证明回表本身不等于随机 IO访问主键的次序决定 IO 类型。上图演示了 MRR 的核心机制二级索引按 (a,b,c) 序吐出主键 57,3,812,21,406乱序直接回表时页号来回跳 随机 IOMRR 先在缓冲区排成升序 3,21,57,406,812再按页号递增顺序访问 → 顺序 IO 触发预读。重要结论如果需要访问的数据页全部命中 Buffer Pool 内存不存在磁盘 IO顺序 IO、随机 IO 没有性能差异。顺序/随机 IO 概念只针对磁盘访问场景。六、延伸理解BufferPool、脏页帮你看懂 SQL 底层 IO 行为1. BufferPool LRU 冷热分区BufferPool否是young 热区最近频繁访问old 冷区新页默认进来新页加载1s 内再次访问停留 1s 再访问淘汰冷页淘汰热页全表扫描大量新页InnoDB 使用改良 LRU 链表分为 young 热区、old 冷区。新页默认进入 old 区头部只有在 old 区停留超过innodb_old_blocks_time默认 1000ms后再次被访问才会移到 young 区头部。这避免了全表扫描一次性冲掉全部热点缓存。2. 脏页生命周期从磁盘加载到 BufferPoolUPDATE/INSERT/DELETE 修改数据Page Cleaner 异步刷脏成功LRU 淘汰直接丢弃LRU 淘汰先刷脏再释放干净页脏页3. 脏页是否可读脏页完全可以对外查询。脏页定义Buffer Pool 内存页被修改内存版本 磁盘持久化版本磁盘存旧数据。查询优先读取 Buffer Pool 内存中的页不管它是不是脏页后台 Page Cleaner 线程异步刷脏页刷脏不会阻塞读写刷盘成功脏页变成干净页该页依旧留在 BufferPool。4. 脏页刷盘成功后为什么不直接删除要用 LRU 淘汰很多人误区脏页落盘完毕就没用了直接清掉。Redo Log 只负责崩溃恢复业务运行时 select不会读取 redo log 拿业务数据redo log 只是操作流水没有完整数据页结构。BufferPool 是缓存遵循局部性原理刚访问过的页大概率还会再次访问。刷脏完成变成干净页仍然是热点数据留在内存可以避免重复从磁盘加载。LRU 淘汰触发时机BufferPool 内存用尽要加载新的数据页时才淘汰最久未访问的冷页。淘汰脏页先刷脏页落盘再释放内存淘汰干净页直接丢弃磁盘已有副本。七、Explain 关键字段回顾做索引分析必看字段含义面试关注点type访问类型ref等值索引查找range范围索引扫描ALL全表扫描key实际使用索引确认是否走了预期索引key_len实际用到索引字节长度判断复合索引用到多少字段INT nullable 5 字节Extra额外信息Using index覆盖索引无回表Using index conditionICPUsing MRR多范围读优化Using filesort需额外排序八、高频踩坑总结面试速记最左匹配要求索引定义的连续最左前缀where 条件书写顺序无关优化器自动调整。遇到 between范围查询后面字段不能索引 seek但可被 ICP 过滤in 不会打断索引匹配。ICP 减少回表数量但不能替代索引 seek覆盖索引直接消除回表。顺序 IO / 随机 IO 看页面访问次序不是索引类型MRR 可以把回表随机 IO 转为顺序 IO。全部页命中 BufferPool磁盘 IO 消失顺序随机 IO 性能无差别。脏页可读刷脏不等于驱逐页面LRU 只有内存不足才淘汰冷页redo log 只管崩溃恢复业务查询不会读取 redo log。复合索引设计原则等值条件放前面范围条件尽量放在索引最后。九、因果链总收束一条链串起所有概念因为 复合索引叶子按 (a,b,c) 全局有序 → 所以 查询可以从 a 开始二分 seek → 因为 排序优先级 a→b→c 依次递减 → 所以 b 范围后 c 在叶子内部无序 → 因为 c 无序无法 seek只能逐行判断 → 所以 引入 ICP 在引擎层过滤减少回表 → 因为 二级索引吐出主键 id 跟随 (a,b,c) 排序主键乱序 → 所以 回表需要访问聚簇索引页 → 因为 聚簇索引页存储在表空间中页号由表空间分配 → 所以 回表默认是随机 IO页号跳跃 → 因为 MRR 把主键排序后再访问 → 所以 回表变成顺序 IO 触发预读 → 因为 页全部命中 BufferPool 时不存在磁盘 IO → 所以 顺序/随机 IO 概念只在磁盘层有意义这条链上的每一个环节都是前一个环节的必然推论。拿掉任何一节后面的结论都不成立。参考资料《高性能 MySQL》第 5 章 — 索引设计MySQL 官方文档 — Index Condition PushdownMySQL 官方文档 — Multi-Range Read OptimizationMySQL 官方文档 — InnoDB Buffer PoolMySQL 官方文档 — InnoDB Read-AheadMySQL 索引下推 ICP 详解 — 博客园MySQL MRR 优化详解 — 博客园InnoDB Buffer Pool LRU 冷热分区 — CSDN
摘要网上大量教程只讲最左匹配口诀很少讲底层 B 树、索引下推 ICP、回表、顺序/随机 IO、BufferPool 之间的完整链路。本文结合 Explain 执行计划、底层存储原理把面试高频坑一次性讲透。前置准备建表与复合索引CREATETABLEtest(idINTPRIMARYKEYAUTO_INCREMENT,aINT,bINT,cINT);-- 创建联合索引 idx(a,b,c)CREATEINDEXidx_abcONtest(a,b,c);复合索引idx(a,b,c)的 B 树叶子节点排序规则是优先按 a 排序a 相等按 b 排序b 相等按 c 排序叶子行末尾附带主键 id。上图演示了复合索引叶子页的排序逻辑。从图中可以看到a 相同的行被分到同一组a 相同后按 b 排序只有 (a,b) 都相同时c 才有序。行尾的主键 id 是后续回表的“钥匙”。因果链因为 InnoDB 的 B 树叶子行按 (a,b,c) 全局有序 → 所以索引定位时可以从 a 开始二分查找 → 但因为排序优先级是 a→b→c 依次递减 → 所以一旦 a 或 b 出现范围查询c 在叶子内部就不再有序。一、什么是最左匹配最左前缀原则核心复合索引要从索引定义的最左侧字段开始匹配连续前缀SQL 的 where 条件书写顺序不影响索引命中优化器会自动调整条件顺序。✅ 可以有效使用索引前缀wherea1wherea1andb2wherea1andb2andc3whereb2anda1-- where 条件顺序打乱优化器重排依旧命中索引❌ 无法使用索引缺失最左前缀whereb2whereb2andc3wherec5误区不是 where 子句写了 a/b/c 字段就一定能用索引必须要有索引定义的最左起始列。重点遇到范围查询后面字段无法做索引 seek between属于范围条件。一旦复合索引匹配中遇到范围查询范围之后的字段不能再利用索引有序性做快速定位seek。wherea10andb20andc5a等值匹配索引 seekB 树二分定位b范围条件索引 range 扫描c不能走索引 seek。因为 b 范围之后c 在 B 树叶子节点内部是无序的上图完整演示了where a10 and b20 and c5的执行过程a 先等值 seek 定位b 范围扫描命中连续 6 行但这 6 行里的 c 值5,12,5,3,40,5完全无序无法 seek 定位只能逐行判断。但是c 不是完全失效它可以交给索引下推 ICP 在二级索引页内过滤这点是很多博客遗漏的关键点。补充in 算不算范围in(1,2,3)不属于破坏索引的范围条件MySQL 内部等价多个 or 等值不会打断后面索引字段匹配后续字段依旧可以 seek。补充MySQL 8.0 索引跳跃扫描Skip ScanMySQL 8.0.13 引入 Skip Scan 优化。当缺失最左前缀时如where b2 and c3优化器可能通过扫描 a 的所有不同值在每个 a 值下分别做 b,c 的 seek来利用索引代价是扫描次数 a 的 distinct 数量。Explain 中typerangeUsing index for skip scan。二、索引下推 ICPIndex Condition PushdownICP 全称索引条件下推MySQL 5.6 之后支持Explain Extra 字段显示Using index condition。没有 ICP 时代执行流程BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层根据 a、b 条件扫描二级索引拿到主键 id 集合1逐个主键回表查聚簇索引完整行2返回整行数据3返回全部行数据4在 Server 层过滤 c5 条件丢弃不满足的数据5问题很多主键对应的行本来就不满足 c 条件白白执行大量回表随机 IO。开启 ICP 后流程BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层BufferPool / 磁盘存储引擎 InnoDBMySQL Server 层根据 a seekb 做 range 扫描1在二级索引页内直接利用 c 列过滤不满足直接丢弃2只把过滤后剩余的主键 id 回表查整行3返回整行数据4返回过滤后的行5✔ ICP 本质在二级索引页过滤数据减少需要回表的主键数量从而减少回表 IO。⚠ 注意ICP 只是过滤不能对 c 做索引 seek不能利用 c 的有序性快速定位区间。上图对比了有无 ICP 的核心差异无 ICP 时 6 行全部回表到 Server 层才发现 3 行不满足 c5回表白做开启 ICP 后存储引擎在索引页内先过滤 c5只剩 3 次回表随机 IO 直接减半。覆盖索引与 ICP 区分Explain ExtraUsing index覆盖索引直接从二级索引拿到全部查询字段完全不需要回表。Using index conditionICP部分条件索引层过滤仍然需要回表。三、回表是什么聚簇索引 vs 二级索引InnoDB 中聚簇索引主键索引叶子节点存储完整整行数据表数据本身就是主键 B 树。二级索引普通/复合索引叶子节点存储索引列 主键 id没有完整行。回表拿到二级索引叶子的主键 id再去主键 B 树查找完整行数据的过程。如果查询需要的全部字段都在二级索引内不需要读取完整行就是覆盖索引避免回表。-- 覆盖索引Extra: Using index无需回表selecta,b,cfromtestwherea10andb20;key_len判断复合索引用到多少字段key_len表示实际用到索引的字节长度。以a INT, b INT, c INT为例查询条件key_len含义where a105只用 aINT 4 字节 nullable 1 字节where a10 and b2010用到 a、bb 范围仍计入 key_lenwhere a10 and b2 and c515用到 a、b、c 全部面试技巧看到key_len10就知道只用到前两个字段key_len5说明只走了 ab 没参与索引定位。四、表空间与数据页组织页从哪里来在讲 IO 类型之前必须先回答一个问题回表时访问的页在磁盘上是怎么存的InnoDB 的数据最终持久化在表空间tablespace中。开启innodb_file_per_tableON时每张表对应一个独立的.ibd文件这就是该表的独立表空间。表空间内部是层级组织层级大小作用页Page16KB最小读写单位存实际数据行区Extent1MB 64 页空间分配的最小单位保证区内页物理连续段Segment变长逻辑概念如叶子节点段、非叶子节点段因果链因为 表空间按 extent 成片分配一次分 64 页物理连续 → 所以 同一段时期内分配的页物理位置大概率相邻 → 但因为 增删改导致页分裂、合并、回收再分配 → 所以 逻辑上页号相邻的两页磁盘位置可能已经分散 → 因此 判定顺序/随机 IO 不能看物理位置只能看页号访问次序关键认知B 树叶子节点的逻辑有序≠物理有序。表空间决定了页的物理落脚处而页号只是表空间内的逻辑编号。理解这一点才能真正理解下一节的顺序/随机 IO。五、顺序 IO、随机 IO不要再记死口诀网上流传二级索引扫描 顺序 IO回表 随机 IO。这句话只是绝大多数场景的经验总结不是铁律定义。InnoDB 判定顺序/随机访问模式InnoDB 看不到磁盘物理扇区看的是表空间页号的访问序列顺序访问模式顺序 IO页号持续递增向后访问触发 InnoDB 线性预读 read-ahead。哪怕磁盘物理页不连续只要访问次序连续向后就视为顺序访问。随机访问模式随机 IO页号跳跃无序访问无法触发预读机械磁盘会产生昂贵寻道开销。关键点顺序 IO、随机 IO 是访问模式不是索引自带属性。示例 1聚簇索引主键-- 主键连续读取页号递增顺序 IOselect*fromtestwhereidbetween1000and2000;-- 主键乱序跳跃读取页号到处跳随机 IOselect*fromtestwhereidin(1001,7,3900,56);普通回表为什么大多是随机 IO二级索引筛选出来的主键 id 集合排序规则跟随(a,b,c)主键 id 是乱序打散的拿着一堆无序 id 访问聚簇索引页号来回跳产生随机 IO。MRR 优化把回表随机 IO 转为顺序 IOMRRMulti-Range Read多范围读优化MySQL 官方专门解决回表大量随机 IO 的方案先从二级索引拿到一批待回表主键 id在内存缓冲区把主键 id 从小到大排序按主键升序访问聚簇索引页号递增随机 IO 变成顺序 IO。Explain 会看到 ExtraUsing MRR。MRR 充分证明回表本身不等于随机 IO访问主键的次序决定 IO 类型。上图演示了 MRR 的核心机制二级索引按 (a,b,c) 序吐出主键 57,3,812,21,406乱序直接回表时页号来回跳 随机 IOMRR 先在缓冲区排成升序 3,21,57,406,812再按页号递增顺序访问 → 顺序 IO 触发预读。重要结论如果需要访问的数据页全部命中 Buffer Pool 内存不存在磁盘 IO顺序 IO、随机 IO 没有性能差异。顺序/随机 IO 概念只针对磁盘访问场景。六、延伸理解BufferPool、脏页帮你看懂 SQL 底层 IO 行为1. BufferPool LRU 冷热分区BufferPool否是young 热区最近频繁访问old 冷区新页默认进来新页加载1s 内再次访问停留 1s 再访问淘汰冷页淘汰热页全表扫描大量新页InnoDB 使用改良 LRU 链表分为 young 热区、old 冷区。新页默认进入 old 区头部只有在 old 区停留超过innodb_old_blocks_time默认 1000ms后再次被访问才会移到 young 区头部。这避免了全表扫描一次性冲掉全部热点缓存。2. 脏页生命周期从磁盘加载到 BufferPoolUPDATE/INSERT/DELETE 修改数据Page Cleaner 异步刷脏成功LRU 淘汰直接丢弃LRU 淘汰先刷脏再释放干净页脏页3. 脏页是否可读脏页完全可以对外查询。脏页定义Buffer Pool 内存页被修改内存版本 磁盘持久化版本磁盘存旧数据。查询优先读取 Buffer Pool 内存中的页不管它是不是脏页后台 Page Cleaner 线程异步刷脏页刷脏不会阻塞读写刷盘成功脏页变成干净页该页依旧留在 BufferPool。4. 脏页刷盘成功后为什么不直接删除要用 LRU 淘汰很多人误区脏页落盘完毕就没用了直接清掉。Redo Log 只负责崩溃恢复业务运行时 select不会读取 redo log 拿业务数据redo log 只是操作流水没有完整数据页结构。BufferPool 是缓存遵循局部性原理刚访问过的页大概率还会再次访问。刷脏完成变成干净页仍然是热点数据留在内存可以避免重复从磁盘加载。LRU 淘汰触发时机BufferPool 内存用尽要加载新的数据页时才淘汰最久未访问的冷页。淘汰脏页先刷脏页落盘再释放内存淘汰干净页直接丢弃磁盘已有副本。七、Explain 关键字段回顾做索引分析必看字段含义面试关注点type访问类型ref等值索引查找range范围索引扫描ALL全表扫描key实际使用索引确认是否走了预期索引key_len实际用到索引字节长度判断复合索引用到多少字段INT nullable 5 字节Extra额外信息Using index覆盖索引无回表Using index conditionICPUsing MRR多范围读优化Using filesort需额外排序八、高频踩坑总结面试速记最左匹配要求索引定义的连续最左前缀where 条件书写顺序无关优化器自动调整。遇到 between范围查询后面字段不能索引 seek但可被 ICP 过滤in 不会打断索引匹配。ICP 减少回表数量但不能替代索引 seek覆盖索引直接消除回表。顺序 IO / 随机 IO 看页面访问次序不是索引类型MRR 可以把回表随机 IO 转为顺序 IO。全部页命中 BufferPool磁盘 IO 消失顺序随机 IO 性能无差别。脏页可读刷脏不等于驱逐页面LRU 只有内存不足才淘汰冷页redo log 只管崩溃恢复业务查询不会读取 redo log。复合索引设计原则等值条件放前面范围条件尽量放在索引最后。九、因果链总收束一条链串起所有概念因为 复合索引叶子按 (a,b,c) 全局有序 → 所以 查询可以从 a 开始二分 seek → 因为 排序优先级 a→b→c 依次递减 → 所以 b 范围后 c 在叶子内部无序 → 因为 c 无序无法 seek只能逐行判断 → 所以 引入 ICP 在引擎层过滤减少回表 → 因为 二级索引吐出主键 id 跟随 (a,b,c) 排序主键乱序 → 所以 回表需要访问聚簇索引页 → 因为 聚簇索引页存储在表空间中页号由表空间分配 → 所以 回表默认是随机 IO页号跳跃 → 因为 MRR 把主键排序后再访问 → 所以 回表变成顺序 IO 触发预读 → 因为 页全部命中 BufferPool 时不存在磁盘 IO → 所以 顺序/随机 IO 概念只在磁盘层有意义这条链上的每一个环节都是前一个环节的必然推论。拿掉任何一节后面的结论都不成立。参考资料《高性能 MySQL》第 5 章 — 索引设计MySQL 官方文档 — Index Condition PushdownMySQL 官方文档 — Multi-Range Read OptimizationMySQL 官方文档 — InnoDB Buffer PoolMySQL 官方文档 — InnoDB Read-AheadMySQL 索引下推 ICP 详解 — 博客园MySQL MRR 优化详解 — 博客园InnoDB Buffer Pool LRU 冷热分区 — CSDN
