MySQL 索引原理与优化

什么是索引？

索引是帮助 MySQL 高效获取数据的数据结构。就像书的目录一样，可以快速定位到需要的内容。

索引的数据结构

B+ 树

MySQL 的 InnoDB 引擎默认使用 B+ 树作为索引结构。

                    [15 | 25 | 35]
                   /     |     |    \
         [1|3|5|10]  [16|18|20]  [26|28|30]  [36|40|45]
              ↓           ↓           ↓           ↓
            叶子节点通过双向链表连接

B+ 树的特点：

• 非叶子节点只存储索引，不存储数据
• 所有数据都在叶子节点
• 叶子节点通过双向链表连接，支持范围查询

为什么不用 B 树或二叉树？

数据结构	问题
二叉树	树的高度过高，磁盘 IO 次数多
B 树	非叶子节点存储数据，导致每个节点能存的索引数减少
Hash	只支持等值查询，不支持范围查询和排序

索引类型

1. 聚簇索引（Clustered Index）

聚簇索引的叶子节点存储的是完整的数据行。InnoDB 中，主键索引就是聚簇索引。

// 通过主键查询，只需一次索引查找
SELECT * FROM user WHERE id = 1;

2. 二级索引（Secondary Index）

二级索引的叶子节点存储的是主键值，查询时需要回表。

// 通过 name 查询，需要：
// 1. 在 name 索引中找到主键值
// 2. 再通过主键值回表查询完整数据
SELECT * FROM user WHERE name = 'Tom';

3. 覆盖索引（Covering Index）

如果查询的列都在索引中，则无需回表，称为覆盖索引。

-- 假设有联合索引 (name, age)
-- 以下查询可以使用覆盖索引，无需回表
SELECT name, age FROM user WHERE name = 'Tom';

索引优化原则

1. 最左前缀原则

联合索引 (a, b, c) 只能按从左到右的顺序使用：

查询条件	能否使用索引
WHERE a = 1	✅ 使用 a
WHERE a = 1 AND b = 2	✅ 使用 a, b
WHERE a = 1 AND b = 2 AND c = 3	✅ 使用 a, b, c
WHERE b = 2	❌ 无法使用
WHERE a = 1 AND c = 3	⚠️ 只使用 a

2. 避免索引失效

-- ❌ 对索引列使用函数
SELECT * FROM user WHERE YEAR(create_time) = 2024;

-- ✅ 改写为范围查询
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- ❌ 隐式类型转换
SELECT * FROM user WHERE phone = 13800138000; -- phone 是 varchar

-- ✅ 使用正确的类型
SELECT * FROM user WHERE phone = '13800138000';

-- ❌ 使用 != 或 NOT IN
SELECT * FROM user WHERE status != 1;

-- ❌ LIKE 以 % 开头
SELECT * FROM user WHERE name LIKE '%Tom';

3. 索引下推（Index Condition Pushdown）

MySQL 5.6 引入的优化，在索引遍历过程中，先对索引中包含的列进行判断，过滤掉不满足条件的记录，减少回表次数。

如何分析索引使用情况

使用 EXPLAIN 分析执行计划：

EXPLAIN SELECT * FROM user WHERE name = 'Tom';

重点关注字段：

• type：访问类型，从好到差：const > eq_ref > ref > range > index > ALL
• key：实际使用的索引
• rows：预估扫描的行数
• Extra：额外信息，如 Using index（覆盖索引）、Using filesort（需要排序）