在当今数据驱动的时代，数据库性能直接决定了应用的响应速度和用户体验。而 MySQL 作为流行的关系型数据库之一，其性能优化的核心往往在于  索引优化。可以说，合理的索引是高速查询的保障，而不当的索引则会导致资源浪费和性能下降。

本文将深入浅出，带你全面了解 MySQL 索引优化的 “为什么”、“是什么” 和 “怎么做”。

第一部分：为什么需要索引？—— 数据的目录

想象一下一本没有目录的巨著（比如《辞海》）。要找到某个特定词汇的解释，你只能一页一页地翻，这就是所谓的  全表扫描。效率极其低下。

而索引就像是这本书的  目录。它通过一种数据结构（在 MySQL 中主要是 B+Tree），预先维护了数据到存储位置的映射关系。当你需要查找数据时，数据库引擎会先通过这个“目录”快速定位到数据所在的大致位置，然后直接去那个位置读取，从而避免了低效的全表扫描。

没有索引的查询：  O(n) 的时间复杂度，数据量越大，速度越慢。
有索引的查询： 通常是  O(log n) 的时间复杂度，效率提升几个数量级。

第二部分：MySQL 索引底层核心：B+Tree

要优化索引，必须对其底层数据结构有基本了解。MySQL 的 InnoDB 存储引擎默认使用  B+Tree 结构来存储索引。

B+Tree 的优势：

1. 矮胖的树形结构：层级很少，通常只需要 3-4 次 I/O 就能从亿级数据中定位到一条记录。

2. 有序存储：叶子节点中的数据是有序排列的，这对于范围查询 ( BETWEEN,  >,  <,  ORDER BY,  GROUP BY) 和排序操作非常高效。

3. 叶子节点包含所有数据：在 InnoDB 中，聚簇索引的叶子节点直接存储了整行数据。而非聚簇索引（二级索引）的叶子节点存储的是主键值。这意味着通过二级索引查询时，如果所需的字段不在索引中，还需要根据主键值 回表查询一次，这会带来额外的开销。

理解 B+Tree 是理解最左前缀原则、索引排序等优化策略的基础。

第三部分：如何优化索引？—— 实战策略篇

以下是一些关键且实用的索引优化策略：

1. 为合适的列创建索引

• WHERE 子句中的字段：这是最直接的优化点。频繁作为查询条件的字段必须考虑建立索引。

• 连接（JOIN）的字段：例如  ON t1.user_id = t2.id 中的  user_id 和  id，通常已经是主键或外键，会自动创建索引，但需确认。

• 排序（ORDER BY）和分组（GROUP BY）的字段：为这些字段建立索引，可以让 MySQL 直接利用索引的有序性避免额外的排序操作，大幅提升性能。

2. 避免无效索引

• 选择性低的字段不要建索引：例如“性别”字段只有‘男’、‘女’两个值，建索引的意义不大。数据库优化器甚至可能会放弃使用这个索引。

• 避免过度索引：索引不是越多越好。每个索引都需要占用磁盘空间，并在数据 增、删、改时维护索引结构，这会带来写操作的开销。需要权衡读写比例。

3. 最左前缀原则（Leftmost Prefix Principle）
这是联合索引（复合索引）最重要的原则。

假设有一个联合索引  index (last_name, first_name, age)。

这个索引可以被用于查询：

• WHERE last_name = 'Wang' （使用第一列）

• WHERE last_name = 'Wang' AND first_name = 'Leo' （使用前两列）

• WHERE last_name = 'Wang' AND first_name = 'Leo' AND age = 25 （使用全部三列）

• WHERE last_name = 'Wang' AND age = 25 （ 部分使用：只能使用到  last_name， age 无法用于快速查找，因为  first_name 是中断的）

这个索引不能被用于查询（未从最左列开始）：

• WHERE first_name = 'Leo'

• WHERE age = 25

• WHERE first_name = 'Leo' AND age = 25

最佳实践： 将选择性最高（最常用、唯一值最多）的列放在联合索引的最左边。

4. 覆盖索引（Covering Index）
如果一个索引包含了查询所需要的所有字段，那么数据库就无需回表查询，可以极大地提升性能。这被称为“覆盖索引”。

示例：
表  users 有索引  index (last_name, first_name)

查询A：

SELECT user_id FROM users WHERE last_name = ‘Wang‘ AND first_name = ‘Leo‘; -- 需要回表，因为 user_id 不在索引中https://zq-mobile.zhaopin.com/moment/73399241    
https://zhiq.zhaopin.com/moment/73399241    
https://zq.zhaopin.com/moment/73399239    
https://zq-mobile.zhaopin.com/moment/73399239    
https://zhiq.zhaopin.com/moment/73399239    
https://zq.zhaopin.com/moment/73399221    
https://zq-mobile.zhaopin.com/moment/73399221    
https://zhiq.zhaopin.com/moment/73399221    
https://zq.zhaopin.com/moment/73399216    
https://zq-mobile.zhaopin.com/moment/73399216    
https://zhiq.zhaopin.com/moment/73399216    
https://zq.zhaopin.com/moment/73399214    
https://zq-mobile.zhaopin.com/moment/73399214    
https://zhiq.zhaopin.com/moment/73399214    
https://zq.zhaopin.com/moment/73399204    
https://zq-mobile.zhaopin.com/moment/73399204    
https://zhiq.zhaopin.com/moment/73399204    
https://zq.zhaopin.com/moment/73399173    
https://zq-mobile.zhaopin.com/moment/73399173    
https://zhiq.zhaopin.com/moment/73399173

查询B：

SELECT last_name, first_name FROM users WHERE last_name = ‘Wang‘ AND first_name = ‘Leo‘; -- 覆盖索引！所有需要的数据都在索引里了，无需回表。

优化策略：在高频查询中，尽量使用覆盖索引。可以考虑将查询中需要的字段“冗余”到联合索引中。

5. 索引失效的常见场景
即使创建了索引，错误的 SQL 写法也可能导致索引失效：

• 对索引列进行运算或函数操作：  WHERE YEAR(create_time) = 2023 （失效） vs  WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01' （有效）

• 使用  != 或  <>：通常会导致全表扫描。

• 使用  % 开头的 LIKE 模糊查询：  WHERE last_name LIKE ‘%ang’ （失效） vs  WHERE last_name LIKE ‘Wang%’ （可能有效，索引下推）。

• 类型转换：如果字段是字符串类型，但查询时用了数字，例如  WHERE id = ‘123’（字符串类型字段），会导致类型转换，索引可能失效。

• OR 连接：如果 OR 前后的条件并非都是索引列，索引会失效。例如  WHERE last_name = ‘Wang‘ OR age = 25，如果  age 无索引，则整个查询索引失效。

6. 使用 EXPLAIN 分析执行计划
这是索引优化的 终极神器。在任何一个 SELECT 语句前加上  EXPLAIN，MySQL 会告诉你它打算如何执行这个查询。

关键字段解读：

• type：访问类型。从好到坏： system >  const >  eq_ref >  ref >  range >  index >  ALL。至少要做到  range 级别，最好能达到  ref。

• key：实际使用的索引。如果为  NULL，说明未使用索引。

• rows：预估需要扫描的行数。越小越好。

• Extra：额外信息。如果出现  Using filesort（需要额外排序）或  Using temporary（使用临时表），通常意味着需要优化。如果出现  Using index，恭喜你，用上了覆盖索引！

第四部分：索引优化是一个持续的过程

1. 监控慢查询：开启 MySQL 的慢查询日志 ( slow_query_log)，定期分析哪些 SQL 语句执行缓慢，并针对性地进行优化。

2. 定期审查索引：使用  SHOW INDEX FROM table_name 查看表的索引情况，关注  Cardinality（基数，即唯一值的数量估算）来判断索引的有效性。也可以使用  SELECT * FROM sys.schema_unused_indexes; （MySQL 5.7+）来查看可能未被使用的冗余索引，并考虑删除。

3. 选择性与基数：高选择性的列（基数高）更适合建索引。

总结

MySQL 索引优化是一门艺术，需要结合业务场景、数据特性和数据库原理进行综合判断。其核心思路可以归纳为：

1. 对症下药：在查询最频繁的 WHERE、JOIN、ORDER BY、GROUP BY 子句的字段上建立索引。

2. 理解结构：深刻理解 B+Tree 和 最左前缀原则，是设计高效联合索引的关键。

3. 追求覆盖：尽量让索引覆盖查询所需，避免昂贵的 回表操作。

4. 避免失效：编写 SQL 时，警惕导致索引失效的写法。

5. 善用工具：坚定不移地使用  EXPLAIN 和 慢查询日志来分析和验证优化效果。

记住，没有一劳永逸的优化方案。随着业务发展和数据增长，需要不断地监控、分析和调整你的索引策略，才能始终保持数据库的最佳性能。