MySQL索引:从原理到实战的终极指南

MySQL索引原理揭秘

MySQL 索引是数据库高效查询的核心机制，其原理基于特定的数据结构（主要是 B+Tree）和数据库引擎（如 InnoDB）的实现策略。索引本质上是一种空间换时间的策略，虽然会占用额外存储空间，但能极大提升查询速度。

B+树的精妙设计

B+树是B树的升级版，具有几个关键特点：所有数据都存储在叶子节点，非叶子节点只存储键值；叶子节点通过指针连接形成链表；树的高度通常维持在3-4层。

这种设计带来巨大优势：范围查询效率极高，因为只需找到起始点就能顺着链表扫描；查询稳定性好，任何查询都需要相同次数的IO；更适合磁盘存储，节点大小通常设置为磁盘页大小(16KB)的整数倍。

InnoDB 引擎索引的关键实现策略

聚集索引决定了表中数据的物理存储顺序，一张表只能有一个聚集索引。其主要结构特点是：

• 叶子节点存储的是完整的数据行（不仅仅是主键值）。因此，通过聚集索引查找数据非常直接高效（一次 B+Tree 查找即可拿到完整行数据）。
• 非叶子节点存储索引键值（主键或其他被选为聚集索引的列值）和指向子节点（页）的指针

InnoDB中主键就是聚集索引。如果没有主键，InnoDB会选择一个唯一非空列，都没有则会隐式创建一个自增列。

非聚集索引(二级索引)存储的是主键值而非数据指针

其主要结构特点:

• 叶子节点存储的不是完整的数据行，而是该索引的键值 + 对应行的聚集索引键值（主键值）。
• 非叶子节点存储索引键值和指向子节点的指针（同 B+Tree 结构）。

查询时需要先查索引再回表查主键。回表过程:

• 在二级索引的 B+Tree 中查找，定位到目标记录的叶子节点。
• 从该叶子节点中读取记录的聚集索引键值（主键值）。
• 拿着这个主键值，回到聚集索引的 B+Tree 中进行一次查找（称为回表 - Bookmark Lookup）。
• 在聚集索引的叶子节点中找到该主键对应的完整数据行

二级索引虽然需要回表操作，但在特定场景下非常高效。例如用户表有(user_id,username)两个字段，如果只为username建索引，查询"SELECT user_id FROM users WHERE username='张三'"时，索引覆盖了查询字段，无需回表。

覆盖索引是指索引包含了查询需要的所有字段，那么 MySQL 无需回表就能直接从二级索引的叶子节点中获取到所需数据，是性能优化的银弹。设计表结构时应考虑常用查询模式，有意识地创建覆盖索引。

EXPLAIN：SQL性能分析的X光机

EXPLAIN命令能显示MySQL如何执行查询，关键列包括：

type：从优到差依次为system > const > eq_ref > ref > range > index > ALL

key：实际使用的索引

key len: 实际使用索引的长度，辅助判断索引是否全部使用上

rows：预估需要检查的行数

Extra：额外信息如"Using index"表示使用了覆盖索引，"Using index condition" 表示使用了索引下推(mysql5.6以后)，即mysql在存储引擎层完成过滤，而不需要在server层二次处理。

分析慢查询时，先EXPLAIN看执行计划，重点关注是否使用了合适索引，避免出现"ALL"(全表扫描)这种性能杀手，最少应该达到range级别。

索引优化实战案例

以mysql官方测试数据(从mysql测试数据官方获取,按官方文档导入数据) 我们设计几个sql优化场景。

使用的是mysql 5.7版本。

创建索引:

ALTER TABLE employees ADD INDEX idx_last_name_hire_date (last_name, hire_date);

• 全值匹配：

EXPLAIN

SELECT * FROM employees WHERE last_name = 'Facello' AND hire_date = STR_TO_DATE('1986-06-26', '%Y-%m-%d')

索引完全匹配是最理想情况，type通常为ref。

• 使用覆盖索引:

EXPLAIN

SELECT last_name,hire_date FROM employees WHERE last_name = 'Facello' AND hire_date = STR_TO_DATE('1986-06-26', '%Y-%m-%d');

仅查询索引字段，避免回表，与全值匹配不同的是Extra 为Using index(覆盖索引)

• 最左前缀原则：联合索引从左至右不能断，跨过联合索引的开头会导致索引失效

EXPLAIN

SELECT * FROM employees WHERE hire_date = STR_TO_DATE('1986-06-26', '%Y-%m-%d')

跨过last_name，导致索引失效，type变为ALL, 全表扫描。

Extra 显示 Using where 说明MySQL在存储引擎检索数据后，还需在服务器层进行二次过滤，需要避免。

• 范围查询会导致之后的索引列失效(5.6之前)：

mysql5.6之后的版本，联合索引的中间列使用范围查询时，确实可通过索引下推技术使之后的索引列仍被高效使用。例如：

ALTER TABLE employees ADD INDEX idx_last_name_hire_date_gen (last_name, hire_date,gender);

EXPLAIN

SELECT * FROM employees WHERE last_name = 'Facello' AND hire_date = STR_TO_DATE('1986-01-01', '%Y-%m-%d') AND gender='M';

EXPLAIN

SELECT * FROM employees WHERE last_name = 'Facello' AND hire_date between STR_TO_DATE('1986-01-01', '%Y-%m-%d') AND STR_TO_DATE('1992-01-01', '%Y-%m-%d') AND gender='M';

后者与前者索引列的长度都是54，后者Extra 显示 Using index condition 说明索引下推技术起了作用。

• 函数操作破坏索引：

EXPLAIN

SELECT * FROM employees WHERE left(last_name,4) = 'Face'

函数的使用导致索引失效，全表扫描,尽量避免对索引列进行计算或者使用函数

可以使用范围查询代替，比如:

EXPLAIN

SELECT * FROM employees WHERE last_name like 'Face%'

使用范围查询则使用了索引

• 不等以及null会使索引失效，or因为索引下推依旧可以有效的使用索引列

EXPLAIN

SELECT * FROM employees WHERE last_name != 'Facello';

EXPLAIN

SELECT * FROM employees WHERE last_name IS NOT NULL;

两者都导致索引失效

可以看到有可能使用的索引(possible keys),可以使用 force index进行优化

EXPLAIN

SELECT * FROM employees force index (idx_last_name_hire_date_gen) WHERE last_name != 'Facello'

OR查询时，索引下推技术的作用

EXPLAIN

SELECT * FROM employees WHERE last_name ='Facello' OR last_name ='Bill';

索引优化黄金法则

不要过度索引：每个索引都会增加写操作成本，通常一张表不超过5个索引为宜。索引不是越多越好，而是要为关键查询路径精心设计。理解业务查询模式，针对性创建组合索引，才能发挥索引的最大威力。