MySQL索引优化：从理论到实战

一、索引优化的重要性

索引是数据库性能优化的核心手段之一。在高并发、大数据量的场景下，合理的索引设计可以将查询性能提升数十倍甚至上百倍。阿里巴巴开发手册明确指出：“SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。”

二、MySQL 索引基础

1. 索引类型

MySQL 支持多种索引类型，主要包括：

• ​主键索引​：表的主键自动创建的索引，唯一且非空
• ​唯一索引​：确保索引列的值唯一
• ​普通索引​：最基本的索引类型，没有唯一性限制
• ​覆盖索引​：查询的字段都包含在索引中，无需回表查询

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。

2. 索引的底层数据结构

MySQL 主要使用 B+Tree 作为索引的底层数据结构，其优势在于：

• 叶节点具有相同的深度
• 非叶子节点不存储数据，只存储索引
• 叶子节点用指针连接，提高区间访问性能
• 适合范围查询和排序

三、索引设计原则

1. 代码先行，索引后上

"代码先行，索引后上"是阿里巴巴手册推荐的最佳实践。不要在建表后立即创建索引，而应等到主体业务功能开发完毕，把涉及该表的相关 SQL 都拿出来分析之后再建立索引。

2. 联合索引尽量覆盖条件

"联合索引尽量覆盖条件"是索引设计的核心原则。设计一个或两三个联合索引（尽量少建单值索引），让每一个联合索引都尽量包含 SQL 语句里的条件。

3. 区分度高的字段放在最左边

"建组合索引的时候，区分度最高的在最左边。"这是索引设计的黄金法则。

举例：如果用户表有 province（省）、city（市）、sex（性别）字段，且 province 区分度最高（如全国有 34 个省），则应创建联合索引(province, city, sex)。

4. 最左前缀原则

MySQL 索引遵循最左前缀匹配原则。对于联合索引 (a, b, c)：

• WHERE a = 1：可以使用索引
• WHERE a = 1 AND b = 2：可以使用索引
• WHERE b = 2：无法使用索引
• WHERE a = 1 AND c = 3：无法使用索引（b 字段缺失）

重要提示：MySQL 8.0 引入了索引跳跃扫描（Index Skip Scan）特性，可以在某些情况下绕过最左前缀限制，但不能依赖此特性，仍应优先将区分度高的字段放在联合索引的左边。

四、常见索引失效场景及优化

1. 在索引列上做函数操作

— 无效：索引失效
EXPLAIN SELECT * FROM employees WHERE YEAR(hire_time) = 2023;

— 有效：索引可使用
EXPLAIN SELECT * FROM employees WHERE hire_time >= '2023-01-01' AND hire_time < '2024-01-01';

2. 类型转换

— 无效：索引失效
EXPLAIN SELECT * FROM employees WHERE name = 1000;

— 有效：索引可使用
EXPLAIN SELECT * FROM employees WHERE name = '1000';

3. 使用 OR 连接条件

— 可能失效：MySQL优化器可能选择不走索引
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' OR name = 'HanMeimei';

— 优化方案：拆分成两个查询
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name = 'HanMeimei';

4. 不使用最左前缀

— 无效：无法使用索引
EXPLAIN SELECT * FROM employees WHERE age = 22 AND position = 'dev';

— 有效：使用最左前缀
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22;

5. 范围查询后跟其他条件

— 无效：age为范围条件，position无法使用索引
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age > 22 AND position = 'dev';

— 有效：拆分范围查询
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age > 22 AND age <= 30;
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age > 30 AND age <= 40;

五、查询优化实践

1. 分页查询优化

MySQL 的 LIMIT offset, rows 在 offset 很大时效率极低，因为需要先读取 offset+rows 条记录，然后丢弃前 offset 条。

​优化方案​：利用主键范围查询

— 原始低效查询
SELECT * FROM employees LIMIT 90000, 5;

— 优化后查询（主键连续且自增）
SELECT * FROM employees WHERE id > 90000 LIMIT 5;

​延迟关联优化​：

— 低效查询
SELECT a.* FROM employees a, (SELECT id FROM employees WHERE condition LIMIT 100000, 20) b WHERE a.id = b.id;

— 优化后查询
SELECT a.* FROM employees a INNER JOIN (SELECT id FROM employees WHERE condition LIMIT 100000, 20) b ON a.id = b.id;

2. JOIN 关联查询优化

​问题​：当被驱动表的关联字段没有索引时，MySQL 会使用 BNL（Block Nested Loop）算法，性能较差。

​优化方案​：为关联字段添加索引，并确保小表驱动大表。

— 优化前（被驱动表关联字段无索引）
SELECT * FROM t2 JOIN t1 ON t2.a = t1.a;

— 优化后（添加索引并小表驱动大表）
ALTER TABLE t1 ADD INDEX idx_a(a);
SELECT * FROM t2 STRAIGHT_JOIN t1 ON t2.a = t1.a;

说明：MySQL 对于被驱动表的关联字段没索引的关联查询，一般使用 BNL 算法。如果有索引，一般选择 NLJ（Nested Loop Join）算法，性能更高。

3. 排序优化

​优化目标​：避免 Using filesort，使用 Using index。

— 无效：导致Using filesort
EXPLAIN SELECT * FROM employees ORDER BY position DESC;

— 有效：使用覆盖索引
EXPLAIN SELECT name, age, position FROM employees ORDER BY position;

MySQL 8.0 引入了降序索引，可以支持 ORDER BY position DESC 直接使用索引，避免 Using filesort。

4. 范围查询优化

— 低效：MySQL可能不使用索引
EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 2000;

— 优化：拆分成多个小范围查询
EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 1000;
EXPLAIN SELECT * FROM employees WHERE age >= 1001 AND age <= 2000;

六、索引优化最佳实践

1. 索引使用率监控

• 定期分析慢查询日志，针对慢查询创建索引
• 使用 SHOW INDEX FROM table_name 查看索引使用情况
• 通过 EXPLAIN 分析查询是否使用了索引

2. 索引大小控制

• 避免创建过长的联合索引
• 索引列应尽量选择小数据类型（如 INT 代替 VARCHAR）
• 避免在索引中包含大文本字段

3. 索引维护

• 定期检查索引碎片
• 删除不使用的索引
• 避免在频繁更新的表上创建过多索引

4. 阿里巴巴手册推荐实践

七、MySQL 5.7 与 8.0 的索引优化差异

八、总结

索引优化是一项需要持续迭代的工作，而非一劳永逸的解决方案。根据阿里巴巴手册和实战经验，我们总结了以下关键点：

“索引是数据库性能的加速器，但不当的索引是性能的拖累。”——阿里巴巴开发手册

记住，索引优化不是一蹴而就的，需要根据实际业务场景和数据分布不断调整和优化。通过合理使用 EXPLAIN 工具，我们可以清晰地看到索引是否被正确使用，从而进行针对性优化。

在实际工作中，建议遵循"先业务，后索引"的原则，先确保业务逻辑正确，再通过 EXPLAIN 分析查询性能，最后进行针对性的索引优化。这样既能保证业务功能正常运行，又能获得最佳的查询性能。