MySQL 索引详解：从原理到实践-优快云博客

本文链接：https://blog.youkuaiyun.com/xiepinan/article/details/149750018

引言

在数据库操作中，查询性能往往是系统瓶颈的关键。想象一下，当你在一本厚厚的字典中查找某个字时，是逐页翻阅还是通过目录定位？答案显而易见 ——目录（索引）能让效率呈几何级提升。MySQL 的索引，正是数据库中的 "目录"，它通过特定的数据结构优化数据检索过程，让海量数据的查询不再成为负担。

本文将从索引的基础概念出发，深入解析其背后的数据结构选择、在 MySQL 中的实现原理，以及索引的分类与实战用法，帮你全面掌握这一数据库性能优化的核心工具。

一、什么是索引？

MySQL 的索引是一种帮助高效查询、更新数据表数据的数据结构。它通过规则排列表中的记录，让查询无需扫描全表，而是通过搜索索引快速定位数据位置。

形象地说，索引就像汉语字典的目录：你可以通过笔画、偏旁部首或拼音索引快速找到目标字，而不必从第一页翻到最后一页。例如，字典中 "三画" 目录下的 "三、千、子" 等字，对应着正文的具体页码 —— 这与索引中 "键值" 对应 "数据行位置" 的逻辑完全一致。

二、为什么需要索引？

索引的核心价值只有一个：提升数据检索效率。

在实际应用中，数据库的查询操作频率远高于增删改。没有索引时，查询可能需要扫描全表（"全表扫描"），当表中数据量达到百万、千万级时，这种操作会变得极其缓慢。而索引通过预排序和快速定位机制，能将查询时间从 "线性增长" 压缩到 "对数级增长"，直接决定系统能否支撑高并发场景。

三、索引的数据结构：为什么是 B + 树？

选择合适的数据结构是索引高效的关键。MySQL 对多种结构进行了权衡，最终选择B + 树作为默认索引结构，我们来看看其中的缘由。

3.1 被排除的选项

HASH 表：查询单条数据时时间复杂度为 O (1)，速度极快，但不支持范围查询（如WHERE id > 100），而范围查询是数据库的高频操作，因此被排除。
二叉搜索树：中序遍历可得到有序数据，但存在明显缺陷：
- 最坏情况下（如数据有序插入）会退化为链表，时间复杂度变为 O (N)；
- 二叉结构导致树高过高，每次查询需要多次磁盘 IO（数据库中 IO 是性能瓶颈）。
- 即使是平衡二叉树（AVL、红黑树），仍因 "二叉" 特性无法降低树高，IO 次数依然较多。
N 叉树：通过增加每个节点的子节点数量（N>2）降低树高，减少 IO 次数。但 N 叉树的叶子节点不连续，范围查询效率仍不理想。

3.2 B + 树：索引的最优解

B + 树是一种平衡查找树，专为数据库设计，其核心特点完美适配索引需求：

非叶子节点仅存索引，不存真实数据：每个节点可存储更多索引项，进一步降低树高，减少 IO。
叶子节点存真实数据，且形成有序链表：所有叶子节点通过指针连接，范围查询时可直接遍历链表，无需回溯上层节点。
查询性能稳定：任意数据的查询路径长度相同（树高一致），时间复杂度稳定为 O (logN)。

例如，一个 4 阶 B + 树的叶子节点会按顺序存储所有数据，非叶子节点仅作为 "导航"，确保查询时能以最少的 IO 次数定位到目标数据。

四、MySQL 中的 "页"：索引的物理载体

B + 树的高效离不开 MySQL 对 "页" 的设计。页是 MySQL 中内存与磁盘交互的最小单元，默认大小为 16KB，每次 IO 至少读取 1 页数据。

4.1 页的设计原理

页的设计基于局部性原理：

时间局部性：刚访问的数据近期可能再次被访问；
空间局部性：刚访问的数据附近，大概率是接下来要访问的数据。

因此，一次读取 16KB 的页到内存，可减少后续 IO 次数 —— 若下次查询的数据仍在该页中，直接从内存读取即可。

4.2 页的结构

每个页由三部分组成：

页头（File Header）：存储页的元信息，如页号、上一页 / 下一页页号（通过双向链表连接页）、修改时间等；
页主体：存储真实数据行，包含最小行（Infimum）和最大行（Supremun）作为链表头尾，数据行按主键排序形成单向链表；
页尾（File Trailer）：存储校验和，确保页数据在传输中未损坏。

此外，页中还有页目录（Page Directory） 结构：将数据行分组（每组最多 8 行），记录每组最后一行的位置。查询时通过二分查找定位分组，再遍历组内数据，大幅提升页内查询效率。

五、B + 树在 MySQL 索引中的实战应用

在 MySQL 中，B + 树的节点与 "页" 直接对应：非叶子节点是 "索引页"（仅存索引），叶子节点是 "数据页"（存真实数据）。

以查询id=5的记录为例，过程如下：

从根索引页判断：5 < 7，访问左子节点（索引页 2）；
在索引页 2 中匹配到 5，定位到对应数据页；
读取数据页，获取 id=5 的记录。

整个过程仅需 3 次 IO（根索引页→子索引页→数据页），效率远超全表扫描。

按默认 16KB 页计算：

数据页：若每条数据 1KB，1 页可存 16 条数据；
索引页：每条索引（主键 8 字节 + 页地址 6 字节 = 14 字节），1 页可存约 1170 条索引。

因此，3 层 B + 树可存储约 1170×1170×16≈2200 万条记录，且查询仅需 3 次 IO，这就是索引高效的核心原因。

六、MySQL 索引的分类

MySQL 索引可按不同维度分类，掌握分类有助于合理设计索引。

6.1 按功能划分

类型	特点	适用场景
主键索引	由`PRIMARY KEY`创建，唯一且非空，自动成为聚集索引	表的唯一标识，高频查询主键时
普通索引	无唯一性限制，可创建多列组合索引（复合索引）	高频查询的非唯一列（如`name`、`age`）
唯一索引	由`UNIQUE`创建，列值不可重复	需要保证列唯一性的场景（如`phone`）
全文索引	针对文本列（`CHAR`、`VARCHAR`、`TEXT`），支持关键词搜索	文章内容、评论等文本检索

6.2 按存储结构划分

聚集索引：与主键索引同义，叶子节点存储整行数据。若表无主键，MySQL 会用第一个UNIQUE NOT NULL列，或自动生成 6 字节ROW_ID作为聚集索引。
非聚集索引（二级索引）：叶子节点存储主键值，查询时需通过主键回表查询完整数据（称为 "回表查询"）。

6.3 特殊场景：索引覆盖

当查询的列全部包含在索引中时，无需回表，直接从索引获取数据，称为 "索引覆盖"。例如，为(sno, class_id)创建复合索引，查询SELECT sno, class_id FROM table WHERE sno='1001'时，可直接通过索引返回结果，效率极高。

七、索引的创建与管理

7.1 自动创建

MySQL 会为特定约束自动创建索引：

主键约束（PRIMARY KEY）→ 主键索引；
唯一约束（UNIQUE）→ 唯一索引；
外键约束（FOREIGN KEY）→ 普通索引。

7.2 手动创建

主键索引

-- 方式1：创建表时定义
CREATE TABLE t_test (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));

-- 方式2：修改表添加
ALTER TABLE t_test ADD PRIMARY KEY (id);

唯一索引

CREATE TABLE t_test (id BIGINT, name VARCHAR(20), UNIQUE (name));
-- 或 ALTER TABLE t_test ADD UNIQUE (name);

普通索引

CREATE TABLE t_test (id BIGINT, sno VARCHAR(10), INDEX (sno));
-- 或 CREATE INDEX idx_sno ON t_test(sno);

复合索引

CREATE TABLE t_test (id BIGINT, sno VARCHAR(10), class_id BIGINT, INDEX (sno, class_id));

7.3 查看与删除索引

查看索引：

SHOW INDEX FROM 表名;
-- 或 SHOW KEYS FROM 表名;

删除索引：

-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 删除其他索引
ALTER TABLE 表名 DROP INDEX 索引名;

八、创建索引的注意事项

索引不是越多越好：索引会占用存储空间，且增删改操作需同步更新索引，过多索引会降低写性能；
优先在高频查询列创建索引：如WHERE、JOIN、ORDER BY涉及的列；
复合索引遵循 "最左前缀原则"：如(a,b,c)索引，仅支持a、a+b、a+b+c的查询，b、b+c无法命中；
避免在低基数列（如gender，仅男 / 女）创建索引：区分度低，索引效率接近全表扫描。

结语

索引是 MySQL 性能优化的 "利器"，其核心是通过 B + 树和页结构减少磁盘 IO，实现高效查询。理解索引的原理、分类及使用技巧，能帮助我们在实际开发中设计合理的索引策略 —— 既充分发挥索引的查询加速作用，又避免其对存储和写性能的负面影响。

合理使用索引，让你的数据库在海量数据面前依然 "健步如飞"！