MySQL 索引原理与 B+ 树应用详解

InnoDB 有多种类型的页，最常用的是存储数据和索引的'索引页'（也叫'数据页'）。无论哪种类型，都包含页头（File Header）和页尾（File Trailer），主体由'行'填充。

页文件头和页文件尾

页头尾包含上一页页号和下一页页号等信息，通过这两个属性可将页与页链接成双向链表。

页主体

页主体保存真实数据。创建新页时会自动分配两个特殊行：Infimum（最小行）和 Supremum（最大行），它们不存真实信息，而是作为数据行链表的头和尾。第一个数据行有一个 next_record 区域指向下一行，形成单向链表。

插入数据时，Infimum 连接第一行，最后一行连接 Supremum，数据按主键从小到大链接。

页目录

直接遍历链表效率太低。InnoDB 引入页目录（Page Directory），将行分组，头行单独一组，其他组最多 8 条数据。每个组最后一行的地址记录在槽（Slot）中，支持二分查找。找到对应槽后，只需在组内最多 8 行中遍历即可。

数据页头

记录了当前页保存数据的元信息。

B+ 树在 MySQL 索引中的应用

非叶子节点保存索引数据，叶子节点保存真实数据。

查找 id=5 的过程：

1. 根节点判断 5 < 7，访问左孩子（索引页 2）。
2. 索引页 2 中找到 id=5，命中，加载对应数据页。 IO 过程：索引页 1 → 索引页 2 → 数据页 3。

三层树高的容量估算

假设用户数据 1KB，一页存 16 条数据。索引页一条记录约 14Byte（主键 BIGINT 8Byte + 地址 6Byte），一页可存 1170 条索引记录。 三层树高（根 + 二级 + 叶子）可保存：1170 × 1170 × 16 ≈ 2190 万条记录。这意味着两千多万数据的表，三次 IO 即可完成检索。

索引分类

主键索引

定义 PRIMARY KEY 时，InnoDB 使用其作为聚集索引。建议每张表都定义主键，若无合适列则添加自增列。

普通索引

最基本的索引类型，无唯一性限制。多列组合称为复合索引。

唯一索引

定义 UNIQUE 键时自动创建，列值不允许重复。

全文索引

基于文本列创建，加快全文搜索。MyISAM 和 InnoDB 均支持。

聚集索引

与主键索引同义。若无 PRIMARY KEY，InnoDB 使用第一个 UNIQUE NOT NULL 列；若都没有，生成 ROW_ID 作为索引。

非聚集索引

即二级索引。每条记录包含主键列及索引列，需回表查询。

索引覆盖

查询列刚好是索引列时，直接返回数据无需回表。

使用索引

自动创建

添加主键、外键、唯一约束时，MySQL 会自动创建索引。

手动创建

主键索引

-- 方式一：建表时指定
CREATE TABLE t_test_pk ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) );

-- 方式二：建表后单独指定
CREATE TABLE t_test_pk1 ( id BIGINT AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id) );

-- 方式三：修改表
ALTER TABLE t_test_pk2 ADD PRIMARY KEY (id);

唯一索引

-- 方式一：建表时指定
CREATE TABLE t_test_uk ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) UNIQUE );

-- 方式二：建表后指定
CREATE TABLE t_test_uk1 ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), UNIQUE (name) );

-- 方式三：修改表
ALTER TABLE t_test_uk2 ADD UNIQUE (name);

普通索引

-- 方式一：建表时指定
CREATE TABLE t_test_index ( id BIGINT PRIMARY KEY AUTO_INCREMENT, sno VARCHAR(10), INDEX(sno) );

-- 方式二：修改表
ALTER TABLE t_test_index1 ADD INDEX (sno);

-- 方式三：单独创建
CREATE INDEX index_name ON t_test_index2(sno);

创建复合索引

语法与普通索引类似，指定多列，逗号分隔。

-- 方式一：建表时指定
CREATE TABLE t_test_index4 ( id BIGINT PRIMARY KEY AUTO_INCREMENT, sno VARCHAR(10), class_id BIGINT, INDEX (sno, class_id) );

-- 方式二：修改表
ALTER TABLE t_test_index5 ADD INDEX (sno, class_id);

-- 方式三：单独创建
CREATE INDEX index_name ON t_test_index6 (sno, class_id);

查看索引

SHOW KEYS FROM 表名;
SHOW INDEX FROM 表名;
DESC 表名;

删除索引

主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

注意：若自增列关联主键，需先修改自增属性再删除主键。

其他索引

ALTER TABLE 表名 DROP INDEX 索引名;

注意事项

• 索引应创建在高频查询列上。
• 索引占用额外存储空间。
• 增删改操作会维护索引，可能影响写入性能。
• 过多或不合理索引会导致性能下降，需谨慎规划。