从 16KB 页结构到千万级查询：MySQL 索引底层原理深度总结
从 16KB 页结构到千万级查询：MySQL 索引底层原理深度剖析
文章目录
阶段一：磁盘与 16KB 页——图书管理员的“搬书推车”
很多人学索引，第一步就去看树。这是本末倒置的，因为如果计算机内存无限大、速度无限快，根本就不需要任何索引技术！
所有索引的设计，都是被一个现实逼出来的：磁盘太慢了。
1. 现实痛点：机械磁头的“老牛破车”
计算机的内存（RAM）像高铁，唰一下就到了；而机械磁盘（HDD）就像拖拉机。
- 磁盘里面是有真实的机械手臂和磁头在物理旋转的。
- 磁头在盘片上找位置（寻道），一次大概要花 5 到 10 毫秒。
- 别小看这 5 毫秒，CPU 跑一圈只需要几纳秒。这意味着：磁头找一次数据的时间，CPU 已经闲得可以转上百万圈了！
所以，数据库性能的核心矛盾，从来不是 CPU 算得慢，而是：磁头寻道的次数（I/O 次数）能不能少一点、再少一点？
2. 生活比喻：去仓库搬书，你是拿一本跑一趟，还是推小推车？
假设你是一家超级图书馆的管理员，每天都有人找你要书。
- 笨办法（按字节读）：
读者找一本书，你跑进漆黑的大仓库找出来，送回前台；读者又找隔壁一本，你再跑进仓库……你一天腿都要跑断（磁头反复寻道）。 - 聪明办法（推车批发，也就是 16KB 页）：
你弄来了一辆标准容量的小推车。不管是找哪本书，只要进仓库，你就把那一排书架上相邻的整整一整箱书全搬上小推车，推回前台的办公桌（内存）。
在 MySQL 的 InnoDB 引擎里，这辆小推车的大小固定是 16KB，在数据库里它有一个名字叫——页（Page）。
【现实生活】: 读者要看一页纸 ────> 管理员直接推回来 [ 一整箱书 (小推车) ]
│
【MySQL 内部】: 用户只要 1 行数据 ───> 磁盘直接加载整块 [ 16KB 数据页 (Page) ] 到内存
通俗大白话：
哪怕你执行查询只想看 1 个字节的数据，MySQL 也绝对不会只从磁盘读 1 个字节。它必须把这 1 行所在的整整 16KB 数据页一次性搬到内存里！
为什么？ 因为人类有个习惯叫“局部性”——你刚看完第 3 页，下一秒大概率要看第 4 页。先整箱搬过来，你下次再看时，直接在桌子上拿就行，磁头就不用再去跑腿了！
阶段二：单页内部的秘密——字典里的“拼音检字表”
现在，推车已经把 16KB 的书搬到你的桌子上了。
但这 16KB 里面密密麻麻塞了几百条数据，如果你在这一页里面找人，依然要从第一行挨个比对到最后一行，这不还是很慢吗？
1. 为什么插入时必须排好序？（就像新华字典）
如果你买了一本《新华字典》，里面的字是按作者当天的心情随机印的，“啊”在第 100 页，“把”在第 3 页，你肯定会当场疯掉。
正因为字典里的字是严格按照拼音 a-z 升序排好的，你翻开字典才觉得好找。
所以，在 16KB 的数据页内部，所有数据行在逻辑上必须按照主键 ID 从小到大排好队。
2. 什么是页目录（Page Directory）？（字典正文前面的目录页）
就算字典里的字是有序的，让你从第 1 页一页页翻到 500 页找“张”字，也很痛苦。
你会怎么做？看字典最前面的“检字表/目录”！
MySQL 在每个 16KB 页的末尾，也专门留出了一块空间，叫做页目录（Page Directory）：
- 它把这一页里的几百条数据，按顺序每 4 到 8 个人分成一个小组。
- 每个小组推选出那个主键最大的带头大哥。
- 把带头大哥的位置抽出来，排成一个目录索引，每一个目录项叫一个槽（Slot）。
看下面这幅字符画：
【页目录 Slot 槽位区】: [ 槽 1 (管到 4 号) ] [ 槽 2 (管到 8 号) ] [ 槽 3 (管到 12 号) ]
│ │ │
▼ (指向该组最大值) ▼ ▼
【页内真实数据行】 : [1] -> [2] -> [4] [5] -> [6] -> [8] [9] -> [10] -> [12]
\_______________/ \_______________/ \_________________/
第 1 组 第 2 组 第 3 组
极速定位过程：
假设你要找 id = 6 的数据：
- 查目录：MySQL 先看页目录的各个槽，用二分法快速对比：6 大于 4，但小于等于 8。
- 秒锁定：瞬间断定目标在“第 2 组”！
- 小范围走两步：直接跳到第 2 组的起始位置，顺着链表只比对 5 和 6 两个人，找到了！
通俗大白话：
页目录就是“大步跨栏，小步微调”。它把原本在 16KB 里找数据需要比对几百次的笨办法，缩减成了只要二分比对几次！
阶段三：从多页到 B+ 树——图书馆的“楼层索引牌”
如果你的表里只有几百条数据，一个 16KB 的页就装得下了。
但如果你有 1000 万条数据呢？一个页装不下，就会产生几十万个页！几十万辆小推车排成一条望不到头的长龙，你怎么知道你要找的那个人在哪辆推车里？
1. 层的演进：给小推车再建一层“目录推车”
既然一个 16KB 的页可以给“具体的行”当目录，那我们为什么不能拿出一个专门的 16KB 页，来给“其他推车”当目录呢？
看下面这张图，它是一层层长出来的：
第 1 层 (顶层大总管):
[ 顶层目录页：找 1~1000 号去下层 A 页，找 1001~2000 号去下层 B 页 ]
/ \
第 2 层 (中层中队长):
[ 目录页 A (管 1~1000 号) ] [ 目录页 B (管 1001~2000 号) ]
/ \ / \
第 3 层 (最底层，干活的推车):
[ 数据页 1 ] <======> [ 数据页 2 ] [ 数据页 3 ] <======> [ 数据页 4 ]
(真正存张三李四) (真正存王五赵六) (真正存各行记录) (真正存各行记录)
这就是大名鼎鼎的 B+ 树！
- 叶子节点（最底层）：就是那些真正装载了用户数据行的小推车。
- 非叶子节点（上层目录）：不装任何具体的肉（业务数据），只装“导航指针”——专门记录哪辆车管哪个范围。
2. 算一本账：为什么 1000 万条数据只要查 3 次磁盘？
为什么它叫 B+ 树，长得这么“矮胖”？我们来做一道小学乘法题：
- 上层目录页能装多少指针？
- 目录页的一个格子非常小，只记两样东西：主键 ID（8 字节）+ 下一层车厢的指针（6 字节）= 14 字节。
- 一个目录页有 16KB（16384 字节）。
- 16384 ÷ 14 ≈ 16384 \div 14 \approx 16384÷14≈ 1170 个指针！也就是说，一个大总管能管 1170 个手下！
- 底层数据页能装多少条数据？
- 假设你的业务数据一行占用 1KB，那么一个底层数据页就能装： 16 KB ÷ 1 KB = 16\text{KB} \div 1\text{KB} = 16KB÷1KB= 16 行数据。
- 把树盖到 3 层楼，能装多少人？
- 第 1 层（根节点）：1 个大总管页，管着 1170 个中层干部。
- 第 2 层（中层干部）：1170 个目录页，每个又管 1170 个底层车厢，总共管着 1170 × 1170 ≈ 136 1170 \times 1170 \approx 136 1170×1170≈136 万个车厢。
- 第 3 层（底层车厢）：136 万个车厢，每个装 16 行数据：
1170 × 1170 × 16 ≈ 2190 万条数据！ 1170 \times 1170 \times 16 \approx \mathbf{2190 \text{ 万条数据！}} 1170×1170×16≈2190 万条数据！
通俗大白话：
面对 2000 多万行 的海量表，这棵树一共也才只有 3 层楼高！
顶层的总管目录一般常年放在内存里。也就是说，你哪怕查一个全表第 2100 万号的人，计算机也只需要顺着指针跑 3 步，从磁盘读 2 到 3 次小推车（2~3 次 I/O），眨眼之间就能把你想要的数据端出来！
阶段四：聚簇索引与回表——字典正文与通讯录小卡片
搞懂了这棵树之后，很多同学会问：主键 ID 查起来是快了，但我如果查的是“姓名”呢？
这就引出了 MySQL 存储引擎里最核心的一对双胞胎：主键索引 InnoDB（聚簇） 与 二级索引 MyISAM（非聚簇）。
1. 生活比喻：查字典 vs 翻通讯录便签
- 主键索引 InnoDB（聚簇索引） = 正版大字典
字典里的正文，本来就是严格按照主键字母拼音排好的。你按拼音找到了这个字，详细的解释、造句、例句（整行所有字段）就在这个字的正下方！ 索引和真实数据是一体的。 - 二级索引 MyISAM（辅助索引） = 随身带的迷你通讯录
比如你在name字段上建了索引。因为空间有限，你不可能把每个人的身份证号、家庭住址、三代履历都抄在小卡片上。
小卡片上只记了简洁的一句话：“杨过 ────> 去查字典的第 3 页（主键 ID = 3）”。
看下面这幅对比字符画：
【主键索引树 (大字典)】:
[ 叶子节点 ] ────> 里面装着所有东西: [ ID: 3 | 姓名: 杨过 | 年龄: 18 | 地址: 古墓派 ] (应有尽有)
【姓名索引树 (小卡片)】:
[ 叶子节点 ] ────> 里面只记了: [ 姓名: 杨过 | 主键 ID: 3 ] (没存年龄和地址！)
2. 什么是“回表”？（两棵树的接力跑）
假设你写了一条 SQL：
SELECT 年龄 FROM user WHERE 姓名 = '杨过';
数据库的寻宝过程如下：
- 第一步（翻小卡片）：MySQL 顺着“姓名索引树”一路狂奔，很快找到了“杨过”。但翻开一看傻眼了：上面只有
杨过和ID: 3，根本没有年龄！ - 第二步（触发回表）：没办法，MySQL 只能拿着手里刚拿到的
ID = 3，跑回“大字典（主键索引树）”重新查一遍。 - 第三步（拿到结果）：在主键索引树里找到 3 号，终于翻到了“年龄: 18”。
第 1 趟：查【姓名索引树】 ──找到──> (姓名: 杨过, ID: 3)
│
发现没有年龄，拿着 ID 跑回去！
│ (这就是“回表”！)
▼
第 2 趟：查【主键索引树】 ──找到──> (ID: 3, 姓名: 杨过, 年龄: 18) ──> 最终交卷！
通俗大白话：
所谓回表，就是你的二级索引树里没有用户想要的全部字段，MySQL 不得不拿着主键值，“回到”主键树里再去查一次整行数据的过程。多跑了一棵树，就多了几次磁盘 I/O！
3. 什么是“索引覆盖”？（抄近道神技）
如果你的 SQL 改成这样：
SELECT ID FROM user WHERE 姓名 = '杨过';
- MySQL 跑去翻“姓名索引小卡片”，找到了杨过。
- 它定睛一看，小卡片上赫然写着
ID = 3！ - 你要的只要 ID，而我手里刚好有 ID！
- MySQL 拔腿就走，直接交卷，根本不用再去翻大字典了！
这种情况在数据库里就叫索引覆盖（Covering Index）。
少跑了一趟大字典，效率极高！在执行计划里，如果看到 Extra 写着 Using index，就说明你成功抄了这个近道。
阶段五：联合索引与最左匹配——通讯录的排序铁律
如果不是单字段，而是多个字段一起建索引，比如 INDEX idx_name_age(name, age)，它是怎么排的？
1. 生活比喻：按“姓氏 + 年龄”排队
想象操场上站着几百个人，体育委员给他们立了一个排队规矩：
- 第一优先准则：大家先按姓氏首字母排。姓“白”的站前面，姓“张”的站后面。
- 第二优先准则：只有在大家都姓“张”的情况下，再按年龄从小到大排！
看下面排出来的队伍队列：
队伍排布现状:
[ (白展堂, 25岁) ]
[ (郭芙蓉, 22岁) ]
[ (张三, 18岁) ] <─── 都在张家范围内，年龄是有序的！(18岁 在 28岁 前面)
[ (张三, 28岁) ]
[ (赵六, 12岁) ] <─── 突然蹦出一个赵六，12岁！
2. 为什么会有“最左匹配原则”？
看懂了这个排队规律，你立刻就能明白为什么会有所谓的最左匹配原则：
-
情况 A：你找“姓张、且 18 岁的人”
-
WHERE name = '张三' AND age = 18 -
结论：极快！先用姓氏二分法缩小到所有姓张的范围，再在这个小范围里按年龄二分找到 18 岁。完全走索引！
-
情况 B：你只找“姓张的人”
-
WHERE name = '张三' -
结论：极快！因为队伍打头就是按姓氏排的，一眼就能把姓张的切出来。走索引！
-
情况 C：你直接找“全场所有 18 岁的人”（跳过了姓名）
-
WHERE age = 18 -
结论：索引完全失效！全场懵圈！
-
为什么？ 你低头看上面的队列：白展堂 25 岁，郭芙蓉 22 岁，张三 18 岁，张三 28 岁，赵六 12 岁……
-
抛开姓氏单独看年龄，这帮人的年龄根本就是完全乱序的！
-
既然是乱序的，计算机根本无法进行二分查找，只能从头到尾一个挨一个看（全表扫描）。
最左匹配铁律：
联合索引就像上楼梯，必须踩稳了第一个台阶（最左边的列），第二个台阶（后面的列）才会有序！
如果你跳过了最左边的列，后面的列在整张表里就是纯粹的乱码，索引直接报废。
阶段六：常见索引失效——为什么有时候它不按常理出牌？
平时我们加了索引，但一用 EXPLAIN 检查，发现它居然还在全表扫描（type = ALL）。这通常是因为我们踩了下面几个生活常识层面的误区：
【避坑指南：索引失效五大现场】
1. 违背最左匹配 : 队伍直接找年龄，没人知道你在找谁 (跳过联合索引前导列)。
2. 在列上动手术 : WHERE age + 1 = 19，字典里记的是原值，算完的值它不知道在哪。
3. 隐式类型转换 : 手机号明明是字符串，查询时偷懒没加单引号写成数字，MySQL 偷偷加函数转类型导致索引报废。
4. 左模糊查询 : WHERE name LIKE '%三'，查尾巴叫三的人，字典从左往右排，它没法倒着翻。
5. 优化器嫌麻烦 : 表里总共就 5 行数据，或者一查查出来半张表；MySQL 觉得直接看比翻卡片回表还快，索性弃用索引。
总结全景脑图
把所有的零件装回你的脑海里，整个 MySQL 索引的完整骨架其实就是这样一条链条：
- 磁盘太慢 → \to → 必须用 16KB 小推车整页批量搬运（Page）。
- 页内要找得快 → \to → 数据按主键排成链表，底部抽出槽位做页目录（二分查找）。
- 单页装不下 → \to → 上层抽出目录项页，逐层管理，长成矮胖的 B+ 树（千万数据仅需 3 次 I/O）。
- 查非主键字段 → \to → 建立二级索引小卡片，查完拿主键回表找大字典；全包含则触发索引覆盖。
- 多列组合排队 → \to → 只有前项相同后项才有序，铁律遵守最左匹配原则。
阶段七：真实场景模拟——电商千万级用户表
我们用一个非常贴近实际开发的电商千万级用户表，从数据怎么摆放、树长成什么样，到一条真实 SQL 执行时在物理磁盘上怎么一步步翻页，带你全程走一遍。
一、 真实场景设定：2000 万用户的会员表
假设有一张会员表 t_user，拥有 2000 万行数据：
CREATE TABLE t_user (
id BIGINT PRIMARY KEY, -- 用户ID (主键，8 字节)
phone VARCHAR(11) NOT NULL, -- 手机号 (11 字节)
user_name VARCHAR(20), -- 用户名
balance INT, -- 账户余额
KEY idx_phone (phone) -- 普通二级索引：在手机号上建索引
);
数据规模与大小估算：
- 单行记录大小：假设包含各种信息后，平均一行占 1KB。
- 整表物理大小：
2000
万行
×
1
KB
≈
20
GB
2000\text{ 万行} \times 1\text{KB} \approx 20\text{GB}
2000 万行×1KB≈20GB 的数据文件（放在磁盘的
.ibd文件里）。
二、 千万级主键 B+ 树长什么样？
这 20GB 的数据被拆成了大约 125 万个 16KB 数据页。为了管理这 125 万个推车，MySQL 构建了一棵 3 层的聚簇索引 B+ 树：
【第 1 层：根节点 Page 3 (常驻内存，0 次 I/O)】
[ id 范围: 1~18720 指向 Page 100 ] [ id 范围: 18721~37440 指向 Page 101 ] ... [ 指向 Page 1270 ]
│
┌────────────────────┘
▼
【第 2 层：中层目录页 Page 101 (需要从磁盘读入内存，第 1 次物理 I/O)】
[ id 范围: 18721~18736 指向 Page 5000 ] ... [ id: 25000~25016 指向 Page 5678 ] ...
│
┌────────────────────┘
▼
【第 3 层：最底层真实数据页 Page 5678 (需要从磁盘读入内存，第 2 次物理 I/O)】
┌────────────────────────────────────────────────────────────────────────┐
│ 页目录 (Page Directory): [槽 0] [槽 1] [槽 2] ... │
│ 真实数据区 (共存了 16 行完整数据): │
│ [ id=25000 | 手机号: 13800000001 | 用户名: 张三 | 余额: 100 ] │
│ [ id=25001 | 手机号: 13800000002 | 用户名: 李四 | 余额: 200 ] │
│ ... │
│ [ id=25005 | 手机号: 13988888888 | 用户名: 王五 | 余额: 9999 ] (目标！) │
└────────────────────────────────────────────────────────────────────────┘
三、 过程一：主键直接查询（千万级只要 2 次磁盘 I/O）
现在执行查询：
SELECT * FROM t_user WHERE id = 25005;
完整穿透过程：
- 第 1 步（看根节点）：
- MySQL 访问根节点
Page 3。 - 由于根节点访问极度频繁，它从数据库启动就一直常驻在内存（Buffer Pool）中。
- CPU 在内存里二分对比，发现
25005落在18721 ~ 37440之间，导航指向 中层目录页Page 101。 - 磁盘 I/O 次数：0。
- 第 2 步（读中层目录页）：
- MySQL 检查内存，发现
Page 101没在缓存里。 - 磁头动一次，去磁盘把
Page 101（16KB）加载进内存。 - CPU 在这个 16KB 页里继续二分查找，发现
25005落在 底层真实数据页Page 5678。 - 磁盘 I/O 次数：1。
- 第 3 步（读底层数据页）：
- 磁头再动一次，去磁盘把
Page 5678（16KB）抓进内存。 - 磁盘 I/O 次数：2。
- 第 4 步（页内抓取数据）：
- 在这个 16KB 内部，利用底部的“页目录槽位”进行二分查找，瞬间锁定包含
id = 25005的小组。 - 顺着组内单向链表走两步，直接拿到了 王五 的这一整行数据（包含手机号、余额等所有字段）。
整个千万级查询，从磁盘只读了 16 KB + 16 KB = 32 KB 16\text{KB} + 16\text{KB} = 32\text{KB} 16KB+16KB=32KB 的数据，耗时仅 2~4 毫秒！
四、 过程二：非聚簇索引查询 + 回表（两棵树的完整接力）
现在换一个场景：用户用手机号登录，执行查询：
SELECT user_name, balance FROM t_user WHERE phone = '13988888888';
此时条件是 phone，主键树帮不上忙了，MySQL 会使用为 phone 单独建立的二级索引树（idx_phone）。
接力第 1 棒：在手机号二级索引树中找 ID
二级索引树也是一棵 B+ 树，但是它的叶子节点非常轻量，只存 [phone, id]，没有名字和余额：
【手机号二级索引树叶子节点 (某个 16KB 页)】
[ ('13800000001', id=25000) ]
[ ('13800000002', id=25001) ]
...
[ ('13988888888', id=25005) ] <--- 找到了！拿到主键 id = 25005
- MySQL 先去查
idx_phone树（根节点 → \to → 中层 → \to → 叶子节点）。 - 在叶子节点里成功定位到了
'13988888888'，拿到对应的伴生数据：id = 25005。 - 但此时 MySQL 发现用户要的是
user_name和balance，二级索引树给不了！ - 触发回表信号！
接力第 2 棒：拿着 id = 25005 回到主键树（回表）
- MySQL 拿着刚拿到的
id = 25005，重新走一遍刚才过程一的流程：
- 查主键树根节点
→
\to
→ 找到
Page 101→ \to → 找到Page 5678。 - 在
Page 5678里读取出整行记录，挑出user_name = '王五'和balance = 9999返回给用户。
五、 过程三：如果不回表呢？（索引覆盖）
如果 SQL 仅仅是判断这个手机号是否已经注册：
SELECT id FROM t_user WHERE phone = '13988888888';
- 第 1 棒跑完：MySQL 在二级索引树的叶子节点命中
('13988888888', id=25005)。 - 原地交卷：你只要
id，手里刚好就有id=25005。 - 第 2 棒直接取消：绝不发生回表，直接返回结果！磁盘 I/O 减少了一半。
一览对比表
| 查询语句 | 经过的树路径 | 发生的物理 I/O | 是否回表 |
|---|---|---|---|
WHERE id = 25005 | 仅查主键聚簇树 | 约 2 次（中层页 + 底层数据页） | 否（数据就在主键下） |
WHERE phone = '139...' 查询所有字段 | 查二级索引树 → \to → 再查主键聚簇树 | 约 4~5 次（二级索引 2 次 + 回表 2 次） | 是（为了拿名字和余额） |
WHERE phone = '139...' 仅查 id | 仅查二级索引树 | 约 2 次（只查二级索引叶子即止） | 否（触发索引覆盖） |
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
21
0- 0
已为社区贡献4条内容
所有评论(0)