MySQL索引优化指南
在数据库性能优化的领域中，有一句流传甚广的话：“如果数据库优化是一栋大厦，那么索引就是地基。” 一个慢查询可能让网页加载延迟几秒，而在高并发场景下，这“几秒”就足以引发系统雪崩。MySQL索引作为加速查询的核心工具，用得好是利器，用不好反而成为负担。本文将深入浅出地介绍MySQL索引的核心原理，并为你总结一套拿来即用的索引优化规则。
一、索引的核心回顾：为什么它能这么快？
在讨论规则之前，我们必须先理解索引的底层逻辑。MySQL默认使用 B+树 作为索引的数据结构。
B+树的特性：数据仅存储在叶子节点，且叶子节点之间通过指针形成有序链表。这使得它不仅支持快速的等值查询，也非常适合范围查询（如 > 、 < 、 BETWEEN ）和排序 。
聚簇索引 vs 辅助索引：
聚簇索引（主键索引） ：叶子节点存储了整行数据。一张表只有一个。
辅助索引（二级索引） ：叶子节点存储的是主键值。这意味着通过辅助索引查找数据时，通常需要先找到主键，再回到聚簇索引中查找完整数据，这个过程称为回表 。
理解了这两个基本概念，我们就能更好地把握下面这些优化规则。
二、索引设计的黄金法则
- 最左前缀原则
这是联合索引中最重要、也最容易犯错的原则。MySQL创建联合索引 (a, b, c) 时，实际上是按 a、b、c 的顺序建立了一棵B+树。
生效规则：查询条件必须从索引的最左侧列开始，且不能跳过中间的列。
支持以下查询：
WHERE a=1
WHERE a=1 AND b=2
WHERE a=1 AND b=2 AND c=3
WHERE a=1 AND c=3 ——（注意：仅 a 列能用到索引，c 列用不到，但索引会用来过滤 a）
不支持/部分支持：
WHERE b=2 ——（索引失效）
WHERE c=3 ——（索引失效）
WHERE a=1 AND c=3 ——（a 生效，c 不生效）
技巧：在创建联合索引时，将最常用于查询条件的列放在最左边。
- 高选择性优先
索引的选择性 = 列中不同值的数量 / 总行数。选择性越接近1，索引价值越大。
反例：对性别字段（仅有男/女）建立索引。由于需要回表扫描大量数据，优化器可能认为还不如全表扫描快，导致索引失效 。
正解：将低选择性的列与高选择性的列组成联合索引。例如 INDEX(city, gender)，先通过城市过滤掉大部分数据 。
- 覆盖索引
如果一个索引包含（或覆盖）了所有需要查询的字段，那就不需要回表了。这是减少磁盘I/O的杀手锏。
优化前：SELECT name, age FROM users WHERE age=20; （假设仅有 age 索引，查到主键后需要回表获取 name 和 age）
优化后：创建索引 INDEX age_name(age, name)。
此时查询分析器显示 Extra: Using index，表示使用了覆盖索引，性能提升可达数倍 。
三、索引失效的常见场景（避坑指南）
建立了索引并不代表一定会被使用。以下场景会导致索引失效，让查询退化为全表扫描：
- 对索引列进行了计算或函数操作
错误：WHERE YEAR(create_time) = 2025
正确：WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
- 隐式类型转换
场景：phone 字段类型为 varchar，查询时 WHERE phone = 13800001111（用数字比较）。
结果：MySQL会将字符串转为数字，相当于在索引列上用了函数，导致索引失效 。
- 使用 != 或 <> 或 IS NOT NULL
对于非主键索引，不等于操作通常无法利用索引进行高效查找。如果NULL值过多，IS NULL 也可能失效 。 - 左模糊查询
LIKE '%关键字' 无法使用索引，而 LIKE '关键字%' 则可以 。 - OR条件
如果 OR 连接的两个条件中，有一个字段没有索引，那么整个查询将无法使用索引（除非使用 UNION 替代） 。
四、实战优化策略
- 索引下推
这是MySQL 5.6引入的优化。
无ICP（索引下推） ：根据索引定位到数据行，回表，再过滤其他条件。
有ICP：在存储引擎层遍历索引时，直接对索引中包含的字段进行过滤，减少回表次数。
示例：索引 (city, age)，查询 WHERE city=xxx AND age=30。ICP允许在索引内部就判断 age=30，无需将所有满足 city 条件的记录都回表 。
- 联合索引顺序设计
通用公式：等值查询列 → 范围查询列 → 排序/分组列。
案例：查询 WHERE city=北京 AND age>20 ORDER BY create_time。
索引建议：(city, create_time)。因为 age 是范围查询，放在后面可能会导致 create_time 无法用于排序 。
- 控制索引数量
索引不是越多越好！每张表建议控制在5个以内。
代价：每个索引都会占用磁盘空间，且每次 INSERT、UPDATE、DELETE 操作都需要同步维护所有索引，写性能会下降30%甚至更多 。
- 利用 EXPLAIN 分析
写完SQL后养成好习惯，用 EXPLAIN 看一下执行计划，重点关注：
type：至少达到 range 或 ref，尽量避免 ALL（全表扫描）。
key：实际使用的索引是哪个。
Extra：出现 Using filesort（文件排序）或 Using temporary（临时表）通常意味着需要优化 。
五、总结
MySQL索引优化并非一朝一夕之功，它是一门关于权衡的艺术。我们通过B+树原理理解其运作方式，通过最左前缀原则设计联合索引，通过覆盖索引减少回表，同时时刻警惕那些导致索引失效的操作。
最后送你一条优化的“心法口诀”：
“全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不回表。”