美しいミズキ

（一）从 .ibd 文件看到页#

MySQL 8.0 默认开启独立表空间。一个 InnoDB 表在这种模式下会有自己的 .ibd 文件，表的数据和索引都放在里面。系统表空间、撤销表空间等采用其他文件形式，所以不能把所有 InnoDB 文件都概括成 .ibd。

InnoDB 不会把这个文件当成一整块数据随意读写，它会把表空间划分成一个个页。页是 InnoDB 组织数据、把数据读入缓冲池以及刷回磁盘的基本单位，默认大小为 16 KB：

1
SHOW VARIABLES LIKE 'innodb_page_size';

Linux 常见文件系统块是 4 KB，但是数据库觉得 4 KB 太小，于是定义了 16 KB 页。这里把两个层次接到了一起。文件系统块由操作系统和文件系统管理，innodb_page_size 是 InnoDB 初始化实例时确定的配置；它的默认值恰好是 16 KB，还可以选择 4 KB、8 KB、32 KB 或 64 KB。

为什么一次读一页？因为页的内部地址是连续的，根据局部性原理，刚访问过的数据附近，往往还放着接下来可能访问的数据。把相邻记录一起读入缓冲池，后续查询就有机会直接从内存中取得，从而减少磁盘I/O开销，增加性能。

（二）页内查找#

树先帮我们定位到叶子页，进入页以后仍要寻找具体记录。原笔记把这一步记成“目录 + 主键”，这个抓法很直观。

页目录像一组稀疏路标。查找时先通过目录槽缩小范围，再到对应的记录分组中比较主键或索引键。这样一次定位就分成了两步：先沿索引树找到页，再在页内找到记录。

（三）残缺页#

如果一页还没完整写入磁盘就发生断电或进程退出，磁盘上可能只留下部分新内容，这叫不完整页写入。单靠“记录日志”四个字还说不清恢复过程。

InnoDB 会先把准备刷盘的页写入 doublewrite 区域，再写到表空间中的正式位置。崩溃恢复时，如果正式位置的页不完整，可以从 doublewrite 中找到完好的副本；redo log 再负责重放尚未写入数据文件的修改。两者处理的问题互相衔接。

（一）页的连接#

一页只能容纳有限记录，数据继续增加时，就需要更多页。问题随之出现：我们怎样从许多页中快速找到目标页？

哈希适合等值查找，却不保留键的顺序；普通二叉搜索树能保持顺序，树高又可能随着数据量增长得太快。数据库更希望一个节点能指向很多子节点，让一层就排除大批无关页。多路平衡树正好符合这个要求。

MySQL 手册把 InnoDB 的常规索引结构称为 B-tree，课程中通常用 B+ 树来理解它：

• 非叶子页保存分隔键和子页引用，用来指路；
• 叶子页保存完整的索引记录；
• 叶子页按照键的顺序相互连接，范围扫描可以继续向后走。

“只有叶子节点存储数据”这句话需要把“数据”说清楚。非叶子页也保存索引键和页引用，只是完整的索引记录位于叶子层。聚簇索引和二级索引的叶子记录还不一样，下一节继续区分。

（二）查找路径#

从根页开始，我们根据索引键选择子页，一层一层走到叶子页，再借助页目录找到具体索引记录。树的每个节点就是一个页，一个页能容纳许多导航项，所以树通常不需要长得很高。

这样看，B+ 树的作用就清楚了：它把许多页组织成一条层次稳定的查找路径，同时保留了键的顺序。

（一）几种常见索引#

主键索引、唯一索引描述的是约束和用途，聚簇索引描述的是行数据怎样和索引组织在一起。它们属于不同的观察角度。

（二）InnoDB 怎样选择聚簇索引#

每个 InnoDB 表都有一个聚簇索引，它的叶子记录保存行数据。选择顺序如下：

1. 表中定义了 PRIMARY KEY，主键就是聚簇索引。
2. 没有主键，InnoDB 选择第一个“所有键列都定义为 NOT NULL”的 UNIQUE 索引。
3. 前两项都不存在，InnoDB 创建隐藏的 GEN_CLUST_INDEX，用内部生成的 6 字节行 ID 组织记录。

原笔记写的是“第一个 UNIQUE 和 NOT NULL 字段”，容易让人理解成随便找一个唯一字段。准确条件落在整个唯一索引上：它的每一个键列都必须是 NOT NULL。

隐藏行 ID 也不会变成应用程序可以直接查询的业务列。实际建表时，主动设计简短、稳定的主键，会让表结构更清楚；主键值还会进入二级索引，主键过长会跟着放大多个索引。

（一）叶子记录#

聚簇索引以外的 InnoDB 索引都叫二级索引。假设 score 表以 id 为主键，并建立下面的联合索引：

1
CREATE INDEX idx_score_student_course
2
ON score (student_id, course_id);

这个二级索引的叶子记录可以理解成：

1
student_id + course_id + 主键 id

二级索引不重复保存整行，它保存自己的索引键和对应行的主键。这样既能按照 student_id、course_id 查找，也能在需要完整记录时继续找到聚簇索引。

（二）回表#

现在查询索引之外的 score 列：

1
SELECT student_id, course_id, score
2
FROM score
3
WHERE student_id = 1
4
  AND course_id = 1;

按照这条二级索引访问路径，查找过程是：

1. 先在 idx_score_student_course 中找到符合条件的记录；
2. 从二级索引记录中取得主键 id；
3. 再用 id 查询聚簇索引，取出包含 score 的完整行。

原笔记里的“先通过二级索引找到主键，再通过聚簇索引获得完整记录”，说的就是回表。可以把它理解成先查到一张门牌号，再按照门牌号去主表中取完整信息。

（一）创建并查看索引#

在 score(student_id, course_id) 上创建普通联合索引：

1
CREATE INDEX idx_score_student_course
2
ON score (student_id, course_id);

创建后，用 SHOW INDEX 查看索引定义：

1
SHOW INDEX FROM score
2
WHERE Key_name = 'idx_score_student_course';

实际结果返回两行：Seq_in_index = 1 对应 student_id，Seq_in_index = 2 对应 course_id，Non_unique = 1 表示这是普通的非唯一索引。两列共同组成一个索引，列顺序也是索引定义的一部分。

SHOW INDEX 只能证明索引已经创建，并展示它的元数据。某条查询最终有没有使用它，还要查看执行计划。本次练习没有运行 EXPLAIN，所以这里只记录已经验证过的创建、查看和删除结果。

（二）删除并确认结果#

1
DROP INDEX idx_score_student_course ON score;

删除后再次执行带 Key_name 条件的 SHOW INDEX，实际结果为 0 行，说明这条索引已经不存在。

索引适合放在经常参与 WHERE、JOIN 或排序的列上，也要结合区分度、表规模和读写比例判断。索引能缩小检索范围，覆盖查询还能少走一次回表；代价是额外空间，以及每次写入时的维护工作。