Theory · MySQL · InnoDB

B+ 树索引

16KB 页上的有序结构 —— 树高 3 层撑起 2000 万行，索引的代价与收益全在"页"里

结构即结论

非叶只存键+指针、叶子存记录并双向成链——范围扫描、最左前缀、免排序，全部从结构直接推出

聚簇 × 二级

聚簇叶存整行即"表本体"；二级叶存索引列+主键，查整行要回表——覆盖索引与 ICP 都是省回表的手段

EXPLAIN 落地

type 从 const 到 ALL 的阶梯、key_len 判断用到哪一列、Extra 三兄弟定位回表/下推/排序

索引是 MySQL 面试三大件之首，比 MVCC 更常考，因为它能从原理一路追问到 EXPLAIN 落地。这份 deck 的主线是"结构决定行为"：先讲清 B+ 树长什么样、为什么选它，再推 3 层树 2000 万行，然后是聚簇与二级索引的分工、自增主键的原理性优势，最后落到最左前缀、失效场景和 EXPLAIN 识读。所有结论都对照 8.0 手册验证过，追问链都准备好了。

Why Index Matters

先看现象：同一条 SQL，加索引前 40 秒，加索引后 3 毫秒

-- orders 表 800 万行，每行约 1KB，没建任何索引
SELECT * FROM orders
WHERE user_id = 10086
  AND created_at > '2026-08-01';

-- 无索引：从第 1 行开始，一行一行比对 800 万次
--   全表 ≈ 800万 × 1KB      = 8 GB
--   按 16KB 一页读          ≈ 53 万次页访问
--   顺序读 200MB/s          ≈ 40 秒
--   而且：并发 10 个这样的查询 → 整库被拖垮

-- 加一个 INDEX(user_id, created_at) 之后：
--   沿树下降 3 层定位到 user_id=10086 的起点   = 3 次页访问
--   沿叶子链表向右扫，扫到 created_at 越界为止 = M 次页访问
--   命中 200 行 → 总共约 3 + 14 ≈ 17 次页访问
--   耗时 ≈ 3 毫秒

关键观察：慢的不是"比较 800 万次"这个 CPU 动作，而是把 8GB 数据从磁盘搬进内存。索引的全部价值，就是把"读 53 万页"压缩成"读 17 页"。

没有索引时，数据库只能这么干

把整张表的页从头到尾读一遍，每读一行就比对一次 user_id=10086。这叫全表扫描（Full Table Scan）。表多大就要读多少，跟你要几行完全无关——查 1 行和查 100 万行，耗时一样。

那么"索引"换掉了什么？

它换掉的是IO 次数，代价是空间与写入速度：数据库额外维护一份"按 user_id 排好序的目录"，这份目录自己也要占磁盘、每次 INSERT/UPDATE 都要同步更新。所以索引是典型的用空间和写性能换读性能——这也是"索引不是越多越好"的根源（第 16 页）。

这个 deck 要回答的三件事

① 为什么这个目录恰好是 B+ 树——哈希、B 树、红黑树为什么不行（第 4 页）；
② 目录里到底存了什么——决定了哪些查询能用上、哪些用不上（第 7–11 页）；
③ 怎么验证用没用上——EXPLAIN 怎么读（第 14–15 页）。

先建立一个直觉：索引本质上就是教科书最后的"术语索引页"——按拼音排好序，告诉你每个词在第几页。你要找"游标"这个词，不用从第一页翻到最后一页，查索引页直接跳。B+ 树只是让这个"索引页"本身也能被快速检索的分层结构。

动机页：给一条能算出具体数字的 SQL（800 万行 / 8GB / 53 万次页访问 / 40 秒 vs 17 次页访问 / 3 毫秒），把索引的价值锚定在"压缩 IO 次数"上，而不是"加快比较"。右侧三问：没有它怎么办、它换掉了什么、本 deck 讲哪三件事。末尾用"教科书索引页"的类比建立最小直觉，避免读者一上来就面对 B+ 树结构图。

Prerequisites & Glossary

先把词认全：下面每一页都会用到它们

术语	一句话理解（先记住这个，细节后面展开）
页（Page）	InnoDB 读写磁盘的最小单位，默认 16KB；一次 IO 就是搬一页，不是搬一行
B+ 树	一种"矮而宽"的多叉搜索树：只有叶子层存数据，非叶层纯粹指路；叶子之间还串成双向链表
扇出（Fanout）	一个节点能指向多少个孩子。扇出越大树越矮 → 需要的 IO 越少
聚簇索引	按主键建的 B+ 树，叶子存整行数据——它就是表本身
二级索引	按别的列建的 B+ 树，叶子只存索引列 + 主键值
回表	二级索引只告诉你主键是多少，要拿整行得再查一次聚簇索引——这第二次查找就叫回表
覆盖索引	要查的列恰好全在索引里，不用回表，Extra 显示 `Using index`
最左前缀	联合索引 `(a,b,c)` 是按字典序排的一棵树，必须从 a 开始才能用它定位
选择性	一列能区分多少不同的值：`count(distinct col)/count(*)` 越接近 1，索引越值钱
EXPLAIN	在 SQL 前加这个词，MySQL 会打印执行计划而不是执行它——看索引用没用上的工具

需要但不在这份 deck 里的前置

平衡树（数据结构系列）→　B+ 树作为"平衡多叉搜索树"的通用原理、与红黑树/AVL 的分工
LRU 缓存（手撕）→　buffer pool 用什么策略淘汰页（理解"根页常驻内存"为什么成立）

本 deck 怎么用这些词

为了不纠结名词，后面统一把索引说成一棵按索引键排好序的树。你只需要盯住一个量：这次查询要读多少页。能用上索引 = 从根下降 3 层直奔目标；用不上 = 把整张表的页过一遍。

最小心智模型（一句话统摄全文）：
索引做的唯一一件事，就是把「逐页翻完整张表」变成「沿树下降 3 层定位 + 沿叶子链表横扫一段」。

后面所有"索引失效"场景，本质都是同一句话：这一步做不到了。

阅读提示：术语不用背，遇到忘了的回来查这一页。真正要背的只有两个数字（16KB 页、扇出约 1170）和最后那张速查表。

前置页：术语挑的是本 deck 真正会用到的十个（页/扇出/聚簇/二级/回表/覆盖/最左前缀/选择性/EXPLAIN/B+ 树），每个给一句话白话定义。右侧给两条外链前置 + 最小心智模型（"逐页翻全表"变成"下降 3 层 + 横扫一段"），这个模型在第 13 页失效清单会被回指复用。

Why B+ Tree

索引 = 额外维护的一份"有序冗余"，选型看读写分布

开场那条 SQL 之所以能从 53 万次页访问降到 17 次，前提是这份"目录"本身也能被快速查找。所以问题变成：用什么数据结构组织它？

结构	等值查询	范围 / 排序	磁盘友好度	典型场景
哈希表	O(1)，最快	不支持（无序，前缀匹配也不行）	一次随机 IO 即定位，但冲突链坏局部性	MEMORY 引擎；InnoDB 自适应哈希（AHI）只是热点索引页上的缓存加速层
B 树	O(logN)	可中序遍历，但跨层往返	每个节点都存整行 → 节点少、扇出小、树更高	少用；是理解 B+ 树的对照品
B+ 树	O(logN)，稳定（都走到叶）	原生支持：叶子有序 + 双向链表，O(logN + M)	非叶只存键+指针 → 扇出大、树矮、IO 少	InnoDB / MyISAM 索引的统一形态
跳表	O(logN)，期望	支持（底层有序链表）	节点高度随机、指针散，对磁盘页局部性差	内存态为主：Redis ZSet、LSM 的 memtable

手册的哈希 vs B-Tree 对比结论

§10.3.9：哈希索引只在 = / <=> / IN() 这类等值比较中占优；优化器无法用哈希索引加速 ORDER BY，也查不了两值之间的范围。MySQL 默认索引用 B-tree——哈希只在显式 ENGINE=MEMORY 或 AHI 里出现。

为什么不是红黑树 / 二叉树？

二叉树扇出=2，2000 万行高约 24 层 = 24 次页 IO；B+ 树把扇出做到上千，3 层就够。数据库要的是让树高 ≈ IO 次数，这决定了"宽而矮"是唯一方向。

这页回答"为什么是 B+ 树"。核心是磁盘 IO 模型：内存里哈希最快，但数据库数据在盘上，IO 按页进行，所以索引结构的目标是减少页访问次数。B 树每个节点都存数据，同样 16KB 放不了几项，树就高；B+ 树非叶只导航，扇出上千，叶子再串成有序链表，等值和范围都兼顾。跳表和 B+ 树功能等价但更适合内存，Redis 用它是合理的。AHI 要说清：它不是另一种索引，是热点索引页上的查询加速缓存。

Structure · §17.6.2.2

B+ 树长什么样：一切行为都从这张图推出

这张图是整个 deck 的锚点，后面每个结论都能指回它。三个要点：非叶节点只存键加子页指针，是纯导航层；叶子页存键和记录，页内有序、页间双向链表。双向链表是范围查询的基础——定位到起点后沿链右扫，不用回上层。手册原文说 InnoDB 索引就是 B-tree 数据结构、索引页默认 16KB；"叶子双向链表"这个物理细节在 Internals Manual 的 FIL Header 部分，页头有 prev 和 next 指针。记住右下那行：等值查询 3 次页访问，而根页和非叶页基本常驻内存。

Back-of-envelope

推演：为什么 3 层 B+ 树 ≈ 2000 万行

// 数量级推演（bigint 主键 · 单行约 1KB）
// 非叶页：每项 = 8B 键 + 6B 页指针 ≈ 14B
扇出 fanout ≈ 16KB / 14B   ≈ 1170

// 叶子页：手册 17.6.2.2 —— 顺序插入约 15/16 满
每页行数 ≈ 16KB × 15/16 / 1KB ≈ 15 行

树高 1：1170 行
树高 2：1170 × 15          ≈ 1.8 万
树高 3：1170 × 1170 × 15   ≈ 2053 万
树高 4：再乘 1170          ≈ 240 亿

追问点	口径
这是精确值吗？	数量级推演，非精确：行宽、键长、填充率都影响结果；面试先给公式再给结论
扇出取决于什么？	索引键长度——键越长每页放的项越少、扇出越小、树越高（长主键连带抬升所有二级索引，见第 7 页）
2000 万行几次 IO？	逻辑上 3 次页访问；根页 + 非叶页常驻 buffer pool，冷查询真实磁盘 IO 常只有 1 次（叶子页）→ 缓存淘汰的底层结构见 LRU 缓存 deck（手撕）
为什么按 15/16 算？	手册：InnoDB 留 1/16 空闲，顺序插入的页约 15/16 满；随机插入只有 1/2~15/16 满

答题模板："16KB 页、8+6≈14B 每项、扇出约 1170；叶子 1KB 行约 15 行；1170² × 15 ≈ 2000 万。所以千万级表树高 3，亿级也就 4 层——索引高度几乎不随数据量线性恶化，这是 B+ 树的核心优势。"

2000 万这个数是索引面试第一道手推题，必须现场推而不是背。三步：非叶 16KB 除以 14 字节每项得扇出 1170；叶子按 15/16 填充放 15 行；1170 的平方乘 15 约两千万。推完主动补两句：这是数量级不是精确值，扇出对键长敏感；以及根页常驻内存，真实磁盘 IO 常只有一次。树高 3 到 4 意味着千万到亿级行的等值查询成本几乎恒定，这句话收尾很加分。

Clustered vs Secondary · §17.6.2.1

聚簇索引 = 表本体；二级索引 = 索引列 + 主键

短主键原则：每个二级索引叶子都存一遍主键列——手册原话 "If the primary key is long, secondary indexes use more space, so it is advantageous to have a short primary key." 主键长 → 二级索引全部变胖 → 扇出变小、树变高（呼应第 6 页）。

聚簇索引和二级索引的本质差异一句话：聚簇叶存整行，是表数据本体；二级叶只存索引列加主键，所以查整行必须拿主键再走一次聚簇索引，这就是回表。图上两条路径都要会讲：实线是 SELECT 星号的回表路径，虚线是覆盖索引——查询列全在索引里时二级索引就地结束。底部短主键原则要背手册原话，主键长二级索引全都变胖。另外注意二级索引记录没有隐藏列，这带来的 MVCC 特殊性在 mvcc deck 里专门讲过。

Primary Key Design

为什么自增主键好：把插入变成"永远写在最右一页"

自增主键：顺序追加

新行总落在叶子链表最右侧的页：手册 §17.6.2.2——顺序插入的页"about 15/16 full"，且只在末端偶发分裂，整树几乎无碎空间；主键紧凑（bigint 8B），二级索引也瘦。

UUID / 随机键：到处插

随机键落点不可预测：手册同页——随机插入的页只有"from 1/2 to 15/16 full"。目标页常不在缓存 → 每次插入都可能产生随机读 + 页分裂 + 写回；缓存命中率与填充率双双下降，空间浪费可达 1/3 以上。

维度	自增（AUTO_INCREMENT / 雪花）	UUID v4 等随机键
页填充率	≈ 15/16，紧凑	1/2 ~ 15/16，碎片多
页分裂	仅末端，代价小	任意位置，分裂 + 上层指针更新频繁
buffer pool 命中	热点集中在最右页	写点分散，冷页挤占缓存
索引体积	8B（bigint）× 每个二级索引 × 每行	36 字符（或 16B 二进制）主键膨胀所有二级索引
代价	自增锁（8.0 默认 mode=2 基本无碍，见 lock-internals）；单机序列，分布式需改雪花/号段	换来全局唯一、客户端可预生成——分布式场景的正当代价

手册口径：§17.6.2.1——没有逻辑主键时"add an auto-increment column"；§10.3.2 Primary Key Optimization 同方向。若必须用 UUID，至少选单调递增变体（UUIDv7 / 雪花）或重排为"时间前缀"，把随机写变顺序写。

自增主键题的答题骨架是结构决定写法：自增让插入永远发生在叶子链表最右一页，页填到 15/16，分裂只在末端；UUID 随机落点，手册明说页只有一半到 15/16 满，还伴随随机读和频繁分裂。体积账也要算：主键被每个二级索引复制一份，8 字节和 36 字符的差距乘上行数和索引数。别把话说死：分布式场景客户端要预生成 ID，UUID 有其正当性，但至少换成单调递增变体，把随机写变顺序写。

Split & Merge · §17.6.2.2

页分裂与合并：索引的"涨肚"与"瘦身"

分裂（Split）

插入的目标页已满 → 新建一页，原页记录按中间点分成两页，上层加一项指针；极端时逐层向上连锁分裂。顺序插入触发"近似顺序分裂"（新页只承接新记录，原页几乎不搬）；随机插入触发"中间分裂"，原页约一半数据搬家——代价最高的形态。

合并（Merge）

删除使页占用低于 MERGE_THRESHOLD（默认 50%）时，InnoDB 尝试把它与相邻页合并，合并不动就尽量做数据搬运腾空。随机删除会留下"半空页"，直到合并或 purge 后空间被新插入复用。

场景	机制与影响
乱序插入批量写入	分裂频繁 + 半满页 → 表"变胖"、空间比数据多；重建表（OPTIMIZE TABLE / 空表 ALTER）可重整到紧凑状态
二级索引大量 UPDATE/DELETE	二级索引更新 = delete-mark + 插入新记录（不原地更新）→ 页内先膨胀后留垃圾，等 purge 物理清理；机制见 mvcc · 二级索引的 MVCC
自增主键 + 批量删除	最右页持续分裂/合并，左侧老页稳定不受扰——所以顺序键对分裂合并都友好
监控抓手	`SHOW TABLE STATUS` 的 Data_length/Index_length 与 Data_free、`information_schema.INNODB_SYS_INDEXES`；碎片高时考虑重建

面试串联："删除不是真删（delete-mark），purge 才物理回收，回收腾出的空间要等新插入复用，占用低于 50% 才合并"——把 B+ 树页机制与 mvcc 的 purge 链条连起来讲，是二级索引追问链的完整闭环。

分裂和合并是一对逆操作。分裂的要点是区分两种形态：顺序插入的近似顺序分裂只搬新记录，代价小；随机插入的中间分裂要搬走约半页数据，代价大，这就是上一页 UUID 结论的微观机制。合并的门槛是 MERGE_THRESHOLD 默认 50%。这页最重要的一环是把 delete-mark 串进来：二级索引更新不是原地改，是打标加插入，所以大量更新删除会让页先膨胀再留垃圾，等 purge 清理。这正好把本 deck 和 mvcc deck 连成一个闭环。

Composite Index · §10.3.6

联合索引 (a, b, c)：按字典序排一棵树，最左前缀是唯一打开方式

WHERE / ORDER BY 条件	索引使用情况	原因（回到树的排列）
`a=1`	a ✓	树按 a 有序
`a=1 AND b=2`	a ✓ b ✓	a 等值把 b "钉"进有序段
`a=1 AND b=2 AND c=3`	a ✓ b ✓ c ✓	逐级钉住
`b=2` / `c=3`	✗（无最左）	树按 a 排，跳过 a 无序可循（8.0.13+ 少量场景可 Index Skip Scan 兜底，见 §10.2.1.3）
`a=1 AND c=3`	a ✓，c ✗	中间缺 b，c 在页内无序；c 条件可走 ICP（见第 11 页）
`a>1 AND b=2`	a ✓（range），b ✗	范围列截断：a>1 之后 b 又乱了——"等值续、范围断"
`a=1 ORDER BY b, c`	免排序	等值条件钉住 a 后，(b,c) 在页内天然有序
`ORDER BY a, b`（无 WHERE）	免排序	整体字典序直接可用；`ORDER BY b` 则 filesort

设计口诀：把等值列放前、范围列放最后——如 INDEX(status, created_at) 支撑 WHERE status=? AND created_at>?；若把范围列放中间，后面的列全部截断。范围列截断的本质：B+ 树里只有"前缀固定"的部分才有序。

最左前缀不是规定而是几何事实：联合索引是一棵按 a、b、c 字典序排的树，只有前缀固定时后面的列才有序。对着表过一遍典型组合，重点记两类：中间断列，后面用不上但能走 ICP；范围列截断，等值续、范围断。设计口诀等值列在前、范围列在后要能举例。ORDER BY 的判断和 WHERE 同理：等值条件钉住前缀后，排序列天然有序。8.0.13 的 Skip Scan 可以当彩蛋讲，说明 MySQL 也在给无最左场景兜底。

Covering Index & ICP · §10.2.1.6

减少回表的两件武器：覆盖索引消除它，索引下推减少它

覆盖索引（Covering Index）

SELECT 的列全部包含在某棵索引树里 → 在二级索引就地返回，Extra 显示 Using index。天然条件：二级索引叶子已带主键，所以 SELECT id, name 走 idx_name 即覆盖；SELECT * 几乎永远无法覆盖。代价：把列加进索引 = 多一份冗余 = 写放大。

索引下推 ICP（Index Condition Pushdown）

WHERE 中只用索引列就能判定的部分，server 层下推给 InnoDB 在索引条目上先过滤，不满足的不回表。手册：用于 range/ref/eq_ref/ref_or_null 且需读整行的场景；InnoDB 仅对二级索引生效；EXPLAIN 显示 Using index condition；开关 optimizer_switch='index_condition_pushdown=on'（默认开）。

-- INDEX(zipcode, lastname, firstname)  · 手册 §10.2.1.6 原例
SELECT * FROM people
WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

// 无 ICP：取回所有 zipcode=95054 的整行，server 再过滤 lastname
// 有 ICP：lastname LIKE 条件在索引条目上先判 —— 前导 % 用不了索引定位，但能在索引上过滤！
//         只有"索引判定通过"的行才回表 → 回表次数大幅下降

一句话区分：覆盖索引让"回表"整个消失；ICP 让"回表"只发生在过滤后的行上。交叉陷阱：mvcc 讲过——二级索引记录被 delete-mark 或页被新事务改过时，为判可见性仍要回聚簇索引，覆盖索引临时失效；ICP 的下推过滤照常可用。

覆盖索引和 ICP 都在回答"怎么少回表"，但层次不同：覆盖索引直接消除回表，条件是查询列全在索引树里；ICP 是减少回表，把能用索引列判定的 WHERE 条件下推到 InnoDB 在索引条目上先过滤。手册那个zipcode例子值得记：前导百分号虽然不能用于定位，却可以在索引上过滤，这是 ICP 的点睛之处。注意两个边界：ICP 只对二级索引生效；而 mvcc deck 讲过的 delete-mark 场景会让覆盖索引失效，两个 deck 在这里交叉。

ORDER BY · §10.8.2

ORDER BY：要么沿索引序读，要么付出 filesort

情形（INDEX(a, b)）	结果	说明
`WHERE a=1 ORDER BY b`	✓ 索引序，免排序	a 等值钉住前缀，b 在有序段内
`ORDER BY a, b` / `ORDER BY a`	✓ 索引序	直接沿树字典序读
`ORDER BY b`	`Using filesort`	无最左前缀
`ORDER BY a ASC, b DESC`（8.0 前建索引）	`Using filesort`	5.7 及更早解析并忽略 DESC——索引只按升序存；8.0 起有真降序索引（第 16 页）
`SELECT * ... WHERE a>0 ORDER BY b`	可能仍 filesort	a 是 range，b 无序；优化器也可能弃用索引选全表 + 排序

filesort 是什么、有多糟？

手册 §10.8.2：Using filesort = "MySQL must do an extra pass to find out how to retrieve the rows in sorted order"。不等于落盘：先在会话级排序缓冲里排，放不下才用临时文件做外排归并——小结果集代价有限，大结果集才是真痛点。

顺手的强信号

同一批 Using temporary（多为 GROUP BY 与 ORDER BY 列不一致、或 DISTINCT/子查询物化）——手册建议"你要查询尽可能快"时优先盯住这两个值。两者都提示"结果集被物化/重排了"，往往意味着缺索引或需要改写。

排序页的核心判断：ORDER BY 能不能吃到索引序，判断方法和 WHERE 的最左前缀一模一样，只是"等值条件可以钉住前缀"这个细节容易被漏掉。要记的坑有两个：一是 5.7 及以前 DESC 被忽略，混序排序拿不到索引序，8.0 才有真降序索引；二是 filesort 不等于落盘，它只是额外排序步骤的标记，内存放不下才外排。Using temporary 和 Using filesort 常一起出现，都是"结果被物化重排"的信号。

When Indexes Fail

索引失效场景：7 条高频 + 1 条官方原文级细节

场景	示例	根因 / 补救
① 对列做函数 / 运算	`WHERE DATE(t)='2026-01-01'`、`WHERE id+1=10`	对列加工后树序失效；改写成范围条件 `t>=… AND t<…`，或用 8.0.13+ 函数索引
② 隐式类型转换（字符串列 vs 数字）	`str_col = 1`（str_col 是索引字符串列）	手册 §12.3 原话："For comparisons of a string column with a number, MySQL cannot use an index on the column"；等价于对列套 CAST。反例注意：`int_col = '1'` 常量转数字，索引仍可用
③ 隐式字符集/排序规则转换	JOIN 两表列 utf8mb4_general_ci vs utf8mb4_0900_ai_ci（8.0 默认）	对一侧列隐式 CONVERT，索引失效；统一 collation 或显式转换到列上
④ 前导 % 的 LIKE	`LIKE '%abc'`	后缀无序，不能定位；例外：查询列全在索引内时可走全索引扫描（type=index）+ ICP 过滤
⑤ OR 两侧有"无索引侧"	`a=1 OR unindexed_col=2`	任一侧需全表即全表；两索引侧可用 `index_merge`。补救：UNION ALL 拆分
⑥ 不等于 / NOT IN	`a <> 1`	语法上可转两个 range（<1 或 >1），不是必然失效——选择性差时优化器按代价弃用；别说死"不走索引"
⑦ 联合索引非最左前缀	INDEX(a,b) 下 `WHERE b=2`	树的几何结构决定；见第 10 页
⑧ 优化器主动放弃	统计信息过期、选择性差、回表行数过多	代价估算判定全表更快；`ANALYZE TABLE` 刷新统计，或上覆盖索引降低回表成本

加分句式："失效"多数不是引擎做不到，而是树序被破坏或代价不划算。区分"结构性失效"（①②③④⑦，必然）与"代价性放弃"（⑥⑧，取决于数据分布）——这样答题不会以偏概全。

失效清单不要背成顺口溜，要能分层。结构性失效是树序被破坏：函数运算、隐式转换、前导百分号、非最左前缀，这些必然用不上定位。其中隐式转换有官方级细节：字符串列和数字比较，手册明说用不了索引，等价于对列做 CAST；反过来数字列写字符串常量不受影响，这个反例很多人答反。代价性放弃是另一类：不等条件其实能转 range，优化器只是权衡后放弃，说"必然失效"就外行了。最后统一 collation 的坑是 8.0 时代的高频事故。

EXPLAIN 1/2 · §10.8.2

EXPLAIN 识读：type 阶梯与 key_len

type（好→坏）	含义（手册口径）
const / system	最多一行命中：主键/唯一索引全列等值（`WHERE pk=1`），启动时读入
eq_ref	JOIN 时按主键/非空唯一索引逐行精确匹配——"最好的关联类型"
ref	按非唯一索引（或最左前缀）取所有匹配行
range	索引范围：`= <> > >= < <= BETWEEN IN LIKE`
index	"same as ALL, except that the index tree is scanned"——全索引扫描（可能顺手覆盖）
ALL	全表扫描，手册："usually very bad"

完整阶梯含 fulltext / ref_or_null / index_merge / unique_subquery / index_subquery；日常达标线：核心查询 ≥ range，JOIN 被驱动表 ≥ ref

key_len 速算（utf8mb4）
`INT NOT NULL` → 4；`INT NULL` → 4+1=5（NULL 标志 1B）
`BIGINT` → 8
`VARCHAR(64) NOT NULL` → 64×4+2（长度前缀）=258
`VARCHAR(64) NULL` → 259
`CHAR(1)`（utf8mb4）→ 4

key_len 的真正用途：判断联合索引实际用到第几列——INDEX(a int, b int) 下 key_len=4 只用了 a，=9 用满两列（NULL 列再 +1）。手册：key_len 让你"确定多列键的哪几部分被使用"。rows 是估算值，配合 filtered 估算过滤比例。

EXPLAIN 第一优先看 type。这页给的是六级常用阶梯：const 等值唯一命中，eq_ref 是关联的最好形态，ref 普通二级索引匹配，range 范围扫描，index 是全索引扫描——它是 ALL 的索引树版本，务必别说成好东西；ALL 全表扫描是底线警戒。key_len 的核心用途是判断联合索引用到第几列，速算规则三条：NULL 加 1 字节、varchar 加长度前缀、utf8mb4 每字符 4 字节。rows 是估算值，别当精确数。

EXPLAIN 2/2 · §10.8.2

Extra 列：回表 / 下推 / 排序 / 物化的一手信号

-- id select_type table  type  key       key_len rows Extra
 1  SIMPLE      users  ref   idx_name  258     1    Using index condition

-- 例 1：SELECT id,name WHERE name='Alice'
--       → ref + Using index          （覆盖，免回表）

-- 例 2：SELECT * WHERE name='Alice' AND name LIKE 'A%'
--       → ref + Using index condition（ICP：索引上过滤，命中才回表）

-- 例 3：SELECT * FROM users WHERE name LIKE '%li%' ORDER BY age
--       → index/ALL + Using where; Using filesort

-- 例 4：GROUP BY 与 ORDER BY 列不一致
--       → Using temporary; Using filesort

Extra 值	含义（手册口径）
Using index	覆盖索引：只读索引树即得列信息，"without having to do an additional seek"
Using index condition	ICP：先按索引条目判条件，通过才读整行
Using where	server 层 WHERE 过滤（注意：type=index/ALL 而无 Using where 时手册提醒可能有错）
Using filesort	额外排序一遍才能按序返回
Using temporary	建临时表存中间结果（GROUP BY/ORDER BY 不一致、DISTINCT 等）

答题口径："Using index 与 Using index condition 是互斥的两档：前者零回表，后者过滤后回表；filesort 与 temporary 是成本信号，出现即检查索引设计与查询改写。"

Extra 列是最能体现实战经验的一列。五个核心值按"好到坏"记：Using index 是覆盖索引零回表；Using index condition 是 ICP 过滤后回表，两者基本互斥；Using where 是 server 层再过滤，注意手册有个告警，type 是 index 或 ALL 却没有 Using where 时可能有问题；Using filesort 和 Using temporary 是成本信号。左边四条真实 EXPLAIN 样例对着讲一遍，面试就能边说边画，比背定义有说服力。

Trade-offs · 8.0 Features

索引不是免费的：前缀 / 降序 / 隐藏 / 函数索引与写放大

手段	解决什么	代价 / 边界
前缀索引 `col(N)`	长字符串列太胖 → 只索引前 N 字符；选择性 = `count(distinct left(col,N))/count(*)` 逼近 1 即够用	索引里没有完整列 → 不能覆盖、不能免 ORDER BY；COUNT 列在索引中不存在
降序索引（§10.3.13）	`INDEX(a ASC, b DESC)` 真按降序存键，支撑 `ORDER BY a, b DESC` 混向排序	8.0 起才生效（旧版忽略 DESC）；优化器按扫描方向自动选升/降读
隐藏索引（§10.3.12）	`ALTER TABLE … ALTER INDEX idx INVISIBLE`——优化器无视但索引仍维护，用于安全下线索引 / 灰度验证	仍占空间与写放大；主键不可隐藏；`use_invisible_indexes=on` 可临时试开
函数索引（§13.1.15，8.0.13+）	`INDEX((DATE(t)))` 直接索引表达式——解决"对列做函数必失效"	等价于"隐藏生成列 + 索引"的语法糖；表达式必须确定性的
控制索引数量	每索引一棵独立 B+ 树，DML 要同步维护所有二级索引 + 写 redo	写放大 = 行变更 × 索引数；失效索引先 INVISIBLE 观察再 DROP

写放大的完整链条：一次 UPDATE → 改聚簇记录 → 改每个受影响的二级索引（delete-mark + 插入新记录）→ 全部记 redo → binlog → 从库重放（见 log-redo-undo-binlog / replication）。索引数 = 这个链条的乘数。

这页讲索引的成本管理和 8.0 新武器。前缀索引的坑必须说：索引里没有完整列，所以永远不能覆盖查询也不能免排序，它只适合"定位后必回表"的场景。三个 8.0 新特性各记一个场景：降序索引管混向排序；隐藏索引管安全下线，先 INVISIBLE 观察,不行再开回来；函数索引专治函数失效，本质是隐藏生成列加索引。最后把写放大讲成乘法：索引数乘上每次变更要维护的树，这直接联动 redo 和复制链路。

Cheat Sheet · 1/2

速查上篇：结构推演 · 最左前缀 · 失效分类

① 结构与推演（必背两个数字）

页大小	默认 16KB（innodb_page_size），一次 IO = 搬一页
非叶扇出	16KB ÷（8B 键 + 6B 指针 ≈ 14B）≈ 1170
3 层容量	1170 × 1170 × 15 行 ≈ 2000 万行（叶子按 15/16 填充）
等值查询 IO	逻辑 3 次页访问；根页/非叶页常驻 buffer pool，冷查真实磁盘 IO 常为 1 次
叶子存什么	聚簇 = 整行；二级 = 索引列 + 主键

② 最左前缀：一表判定

`a=1 AND b=2`	✓✓ 等值逐级"钉住"前缀
`b=2`	✗ 无最左，无序可循（8.0.13+ 少量 Skip Scan 兜底）
`a=1 AND c=3`	a ✓，c ✗（但 c 可走 ICP 下推过滤）
`a>1 AND b=2`	a ✓，b ✗ —— 等值续、范围断
`a=1 ORDER BY b`	免排序（a 钉住后 b 天然有序）

③ 索引失效：先分两类再答

结构性失效（必然）	对列做函数/运算、字符串列 vs 数字（隐式 CAST）、collation 不一致、前导 `%` 的 LIKE、非最左前缀
代价性放弃（看数据分布）	`!=` / `NOT IN`（其实可转两个 range）、统计信息过期、选择性差、回表代价过高
隐式转换方向性	字符串列 `= 1` → 失效（列被 CAST）；数字列 `= '1'` → 不失效（常量被转）
filesort 怎么判	= 需额外排序一步，不等于落盘：小结果集内存排完即走，大结果集才外排归并

速查上篇（判定侧）：结构与两个必背数字（16KB 页、扇出约 1170）→ 最左前缀一表判定 → 失效的两分法。失效分类是本 deck 最容易被答偏的地方，必须区分"结构性失效（必然）"与"代价性放弃（取决于数据分布）"。

Cheat Sheet · 2/2

速查下篇：EXPLAIN 读数顺序 · 建索引清单

④ EXPLAIN 读数顺序

type（好→坏）	const → eq_ref → ref → range → index → ALL；达标线：核心查询 ≥ range、JOIN 被驱动表 ≥ ref。index ≠ 好（它是全索引扫描）
key + key_len	验证"用没用、用到第几列"。key_len 速算：`INT`=4、NULL 列 +1、`VARCHAR(n)` utf8mb4 = 4n+2
rows	预估扫描行数（估算值，配合 filtered 看过滤比例）
Extra	`Using index`=覆盖（零回表）｜`Using index condition`=ICP（过滤后回表）｜`filesort`/`temporary`=成本信号

⑤ 设计清单（建索引前自问）

主键	用自增/单调（顺序写 → 15/16 填充、分裂仅末端）；UUIDv4 随机写 → 1/2~15/16 填充 + 频繁中间分裂。分布式用单调变体（雪花 / UUIDv7）
主键要短	主键被每个二级索引复制一份 → 长主键让所有二级索引变胖、扇出变小
联合索引顺序	等值列在前、范围列收尾（如 INDEX(status, created_at)）
能不能覆盖	把高频查询的返回列并进索引 → `Using index`；代价是写放大
索引数量	每索引一棵树，DML 同步维护 + 写 redo + 从库重放 → 写放大 = 行变更 × 索引数；下线先 INVISIBLE 观察

一句话背下来：索引把「逐页翻完整张表」变成「沿树下降 3 层 + 沿叶子链表横扫一段」；所有失效场景，本质都是这一步做不到了。

删/改的隐藏成本：二级索引更新 = delete-mark + 插入新记录（不原地改），页先膨胀后由 purge 物理回收，占用低于 50% 才触发页合并——所以乱序批量写会让表"虚胖"，需要 OPTIMIZE/重建。

速查下篇（验证与设计侧）：EXPLAIN 的固定读数顺序 type→key/key_len→rows→Extra，以及建索引前的五条自问清单。收尾两句话分别呼应第 2 页的最小心智模型和第 9 页的页分裂/purge 链条。

Interview QA · 1/2

结构与设计 8 连问

先盖住答案自己答一遍，再展开对照——想不起来比看得顺眼记得牢；答不出的直接翻回第 17 / 18 页速查表。

1 · 为什么用 B+ 树而不是哈希 / B 树 / 红黑树？

磁盘页 IO范围查询扇出

哈希等值 O(1) 但无序，不支持范围与排序；B 树非叶也存数据，扇出小树高；红黑树二叉太高（2000 万行约 24 层）。B+ 树非叶只导航 → 扇出上千、3 层覆盖千万行，叶子有序链表天然支持范围与排序（§10.3.9）。

2 · 3 层树 ≈ 2000 万行怎么推？

16KBfanout≈1170

非叶每项 8B 键 + 6B 指针 ≈ 14B，16KB/14B ≈ 1170；叶子按 15/16 填充放约 15 行；1170²×15 ≈ 2000 万。强调是数量级推演，扇出对键长敏感。

3 · 主键等值查询要几次磁盘 IO？

3 次页访问根页常驻

逻辑 3 次：根 → 非叶 → 叶。根页与非叶页几乎常驻 buffer pool，冷数据真实磁盘 IO 常为 1 次（叶子页）。追问延伸：缓存与淘汰见 LRU 缓存 deck。

4 · 聚簇与二级索引叶子各存什么？回表是什么？

整行 vs 列+PK

聚簇叶存整行（表即索引组织表）；二级叶存索引列 + 主键值。按二级索引查整行需拿主键再走聚簇索引 = 回表。主键长则所有二级索引变胖——"short primary key"（§17.6.2.1）。

5 · 为什么自增主键好？UUID 呢？

顺序写15/16 满分裂在末端

自增 = 永远写在叶子链最右页：页 15/16 满、分裂仅末端、缓存命中集中。UUID 随机落点：页 1/2~15/16 满、频繁中间分裂、随机 IO。分布式必须预生成时选单调变体（雪花/UUIDv7）。

6 · 什么是覆盖索引？为什么快？

Using index零回表

查询列全在某棵索引树内，二级索引就地返回，省一次 B+ 树查找与随机 IO。二级叶天然带主键，所以 SELECT id,name + idx_name 即覆盖；SELECT * 几乎不可能覆盖。代价是冗余与写放大。

7 · 最左前缀？范围列为什么截断？

字典序等值续·范围断

联合索引按 (a,b,c) 字典序成树，只有前缀固定时后续列才有序；范围列后的列在页内无序 → 截断。设计口诀：等值列在前、范围列收尾，如 INDEX(status, created_at)。

8 · ICP 是什么？和覆盖索引什么关系？

Using index condition少回表

ICP 把"仅用索引列可判定"的 WHERE 下推到 InnoDB 在索引条目上先过滤，通过才回表；仅二级索引生效（§10.2.1.6）。覆盖索引消除回表，ICP 减少回表；方向相同、层次不同。

QA 第一组覆盖结构与设计主干。第二题一定要现场推公式，不能背结论；第三题的根页常驻是体现生产感的点；第五题把 UUID 的辩护场景主动说出来，避免显得偏激；第七题记住"等值续、范围断"四字口诀；第八题用"消除 vs 减少"一句话区分覆盖索引和 ICP。每题都按结论、根因、代价三段式答。

Interview QA · 2/2

失效场景与 EXPLAIN 8 连问

同样建议先自答。这一页每题都要能给出"结论 + 一句边界"——只答结论会被追问（如第 10 题的方向性、第 14 题的分层判断）。

9 · LIKE '%xx' 一定不走索引吗？

不能定位可全索引扫

前导 % 无法用于定位，但若查询列全在索引内，可用全索引扫描（type=index）+ ICP 过滤——比全表扫仍省。LIKE 'abc%' 则正常走 range。

10 · 字符串列 WHERE col=1 为什么失效？数字列加引号呢？

列被 CAST反例不失效

字符串列 vs 数字：每行都把列转数字再比，等价于对列套函数 → 索引失效（手册 §12.3 原话）。反向 int_col='1' 是常量转数字，索引照常可用——方向不同结果不同。

11 · != / NOT IN 一定全表扫吗？

两个 range代价决定

不是。a<>1 可转 (<1) ∪ (>1) 两个 range；选择性好的时候照样走。选择性差时优化器按代价放弃——归入"代价性放弃"而非"结构性失效"。

12 · EXPLAIN 先看哪几列？type 达标线？

type/key/key_len/rows/Extra

先 type 定位访问方式，再 key+key_len 验证"用没用、用到第几列"，rows 估扫描量，Extra 找回表/排序/临时表信号。核心查询 ≥ range、JOIN 被驱动表 ≥ ref 是常用达标线；index 不等于好。

13 · key_len=5 和 9 差在哪？（INDEX(a INT NULL, b INT)）

用到第几列NULL +1B

INDEX(a INT NULL, b INT NOT NULL)：a 占 4+1=5B，b 占 4B。key_len=5 → 只用了 a；key_len=9 → a、b 都用上。答题时把"列宽×字符集×NULL 标志"现场算一遍，比背结论有说服力。

14 · Using filesort 一定是坏事吗？

不一定看结果集

filesort = 需额外排序，不代表落盘：小结果集内存排完即走，大结果集才外排归并。LIMIT 小值 + 索引可部分缓解；若出现在大分页深翻页场景（ORDER BY … LIMIT 100000,10）应改游标分页。

15 · 索引是不是越多越好？写放大算给谁？

写放大乘数delete-mark

每索引一棵树：UPDATE 触发聚簇 + N 个二级索引变更（delete-mark + 插入），全部记 redo/binlog 并在从库重放；还拖慢批量导入与空间利用率。下线用 INVISIBLE 观察，确认无用再 DROP。

16 · 大表加索引会锁表吗？

INPLACE并发 DML

8.0 加/删二级索引默认 ALGORITHM=INPLACE：不重建表、允许并发 DML（细节见 lock-internals 的 Online DDL 表）；MDL 短暂持有，长事务会卡 DDL——这也是"先查长事务再变更"的原因。

QA 第二组偏实战。第十题的隐式转换方向性是最容易答反的题，务必把"列被转换"这个判断标准说出来。第十二题给出自己的 EXPLAIN 习惯顺序，展示有实战流。第十四题不要把 filesort 一棍子打死，分结果集大小讨论才显成熟。第十五题把写放大讲到 redo 和从库重放，说明你理解全链路。第十六题接 Online DDL 和 MDL，正好把话头递给 lock-internals 那份 deck。

Related & References

参考来源：本 deck 全部结论可溯源

dev.mysql.com/doc/refman/8.0/en/innodb-physical-structure.html	§17.6.2.2：B-tree 结构、索引页默认 16KB、留 1/16 空闲、顺序 15/16 满 vs 随机 1/2~15/16、MERGE_THRESHOLD
dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html	§17.6.2.1：聚簇索引定义、二级记录含主键列、短主键原则、无逻辑主键时用自增
dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html	§10.3：How MySQL Uses Indexes / Multiple-Column Indexes(10.3.6) / B-Tree vs Hash(10.3.9) / Invisible(10.3.12) / Descending(10.3.13)
dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html	§10.2.1.6：ICP 定义、InnoDB 仅二级索引、适用访问方式、Using index condition
dev.mysql.com/doc/refman/8.0/en/explain-output.html	§10.8.2：type 值 best→worst 清单、key_len 语义、Using index / index condition / filesort / temporary 定义
dev.mysql.com/doc/refman/8.0/en/type-conversion.html	§12.3：字符串列与数字比较无法使用索引（隐式转换失效的官方出处）
dev.mysql.com/doc/refman/8.0/en/create-index.html	§13.1.15：Functional Key Parts（8.0.13+）
dev.mysql.com/doc/internals/en/innodb-page-structure.html	MySQL Internals Manual：FIL Header 的 prev/next 页指针（叶子双向链表的物理依据）

怎么查：把上表第一段路径拼到 https:// 后面直达官方页面。版本敏感结论务必回到手册核对——本 deck 全部按 MySQL 8.0 撰写，5.7 在降序索引、隐藏索引、函数索引、Skip Scan 上与 8.0 行为不同。

参考页：版本敏感结论都有出处。16KB 页、15/16 与 1/2 填充、MERGE_THRESHOLD 出自 §17.6.2.2；聚簇与二级索引定义出自 §17.6.2.1；隐式转换失效的官方原话在 §12.3；ICP 定义在 §10.2.1.6；EXPLAIN 的 type/key_len/Extra 定义在 §10.8.2；函数索引在 §13.1.15。末尾提示全部结论按 8.0 撰写，5.7 行为不同。

B+ 树索引

结构即结论

聚簇 × 二级

EXPLAIN 落地

先看现象：同一条 SQL，加索引前 40 秒，加索引后 3 毫秒

没有索引时，数据库只能这么干

那么"索引"换掉了什么？

这个 deck 要回答的三件事

先把词认全：下面每一页都会用到它们

需要但不在这份 deck 里的前置

本 deck 怎么用这些词

索引 = 额外维护的一份"有序冗余"，选型看读写分布

手册的哈希 vs B-Tree 对比结论

为什么不是红黑树 / 二叉树？

B+ 树长什么样：一切行为都从这张图推出

推演：为什么 3 层 B+ 树 ≈ 2000 万行

聚簇索引 = 表本体；二级索引 = 索引列 + 主键

为什么自增主键好：把插入变成"永远写在最右一页"

自增主键：顺序追加

UUID / 随机键：到处插

页分裂与合并：索引的"涨肚"与"瘦身"

分裂（Split）

合并（Merge）

联合索引 (a, b, c)：按字典序排一棵树，最左前缀是唯一打开方式

减少回表的两件武器：覆盖索引消除它，索引下推减少它

覆盖索引（Covering Index）

索引下推 ICP（Index Condition Pushdown）

ORDER BY：要么沿索引序读，要么付出 filesort

filesort 是什么、有多糟？

顺手的强信号

索引失效场景：7 条高频 + 1 条官方原文级细节

EXPLAIN 识读：type 阶梯与 key_len

Extra 列：回表 / 下推 / 排序 / 物化的一手信号

索引不是免费的：前缀 / 降序 / 隐藏 / 函数索引 与写放大

速查上篇：结构推演 · 最左前缀 · 失效分类

① 结构与推演（必背两个数字）

② 最左前缀：一表判定

③ 索引失效：先分两类再答

速查下篇：EXPLAIN 读数顺序 · 建索引清单

④ EXPLAIN 读数顺序

⑤ 设计清单（建索引前自问）

结构与设计 8 连问

1 · 为什么用 B+ 树而不是哈希 / B 树 / 红黑树？

2 · 3 层树 ≈ 2000 万行怎么推？

3 · 主键等值查询要几次磁盘 IO？

4 · 聚簇与二级索引叶子各存什么？回表是什么？

5 · 为什么自增主键好？UUID 呢？

6 · 什么是覆盖索引？为什么快？

7 · 最左前缀？范围列为什么截断？

8 · ICP 是什么？和覆盖索引什么关系？

失效场景与 EXPLAIN 8 连问

9 · LIKE '%xx' 一定不走索引吗？

10 · 字符串列 WHERE col=1 为什么失效？数字列加引号呢？

11 · != / NOT IN 一定全表扫吗？

12 · EXPLAIN 先看哪几列？type 达标线？

13 · key_len=5 和 9 差在哪？（INDEX(a INT NULL, b INT)）

14 · Using filesort 一定是坏事吗？

15 · 索引是不是越多越好？写放大算给谁？

16 · 大表加索引会锁表吗？

相关知识点与参考

本领域相关 deck

答题串联 · 一图流

参考来源：本 deck 全部结论可溯源

索引不是免费的：前缀 / 降序 / 隐藏 / 函数索引与写放大