MySQL 索引原理：为什么你的 SQL 越跑越慢？

MySQL 索引原理：为什么你的 SQL 越跑越慢？

写业务代码时，最头疼的往往不是逻辑复杂，而是数据量上来后 SQL 越来越慢。加索引是最常见的优化手段，但很多人只是"听说索引能加速"，并不清楚索引到底是怎么工作的，甚至加了索引也没效果。今天就来拆解一下 MySQL 索引的核心原理。

索引的本质

索引是一种数据结构，用来快速定位数据。你可以把它理解为书的目录——不用逐页翻，直接根据目录找到目标页码。

MySQL 最常用的索引结构是 B+ 树。为什么不是哈希表、二叉树或者 B 树？

• 哈希表：等值查询 O(1) 很快，但范围查询（>、<、BETWEEN）完全用不上。
• 二叉搜索树：数据有序插入时会退化成链表，查询退化为 O(n)。
• B 树：每个节点都存数据，树更高，磁盘 I/O 次数更多。
• B+ 树：非叶子节点只存索引键，叶子节点存完整数据且用链表串联，既适合范围查询，又减少了树高。

B+ 树的几个关键特性

1. 多叉平衡树：每个节点可以有多个子节点（InnoDB 默认页大小 16KB，一个节点大约能存上千个键），树非常"矮胖"。通常 3 层 B+ 树就能支撑千万级数据，意味着一次查询最多 3 次磁盘 I/O。
2. 叶子节点有序链表：所有叶子节点通过双向链表连接，范围查询时找到起点后直接沿链表遍历即可，不需要回树查找。
3. 聚簇索引 vs 非聚簇索引：
   • 聚簇索引（主键索引）：叶子节点直接存整行数据。InnoDB 表必须有且只有一个聚簇索引，通常就是主键。
   • 非聚簇索引（二级索引）：叶子节点只存索引列 + 主键值。查询时先通过二级索引找到主键，再回聚簇索引查完整数据，这个过程叫 回表。

回表与覆盖索引

-- 假设 idx_name 是 (name) 的二级索引
SELECT id, name FROM user WHERE name = '张三';  -- 覆盖索引，无需回表
SELECT id, name, age FROM user WHERE name = '张三';  -- 需要回表查 age

覆盖索引：查询的列全部包含在索引中，不需要回表，性能最好。所以设计索引时可以考虑把常用查询列一起放进去（联合索引）。

最左前缀原则

联合索引 (a, b, c) 相当于建立了 (a)、(a, b)、(a, b, c) 三个索引。查询时必须从最左列开始匹配，跳过中间列会导致索引失效：

WHERE a = 1 AND b = 2 AND c = 3   --  用索引
WHERE a = 1 AND b = 2              --  用索引
WHERE a = 1 AND c = 3              -- ️ 只能用 a，c 用不上
WHERE b = 2 AND c = 3              --  索引失效

索引失效的常见场景

• 对索引列做函数运算：WHERE YEAR(create_time) = 2024 → 改成范围查询 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'。
• 隐式类型转换：字符串字段不加引号 WHERE phone = 13800000000，MySQL 会做类型转换导致索引失效。
• **LIKE '%xxx'**** 前缀模糊**：LIKE 'xxx%' 可以用索引，LIKE '%xxx' 不行。
• **OR**** 连接非索引列**：WHERE a = 1 OR b = 2，如果 b 没有索引，整个索引失效。
• **!=**、**NOT IN**、**IS NOT NULL**：通常不走索引（优化器认为全表扫描更快）。

如何分析 SQL 是否用了索引？

用 EXPLAIN 命令：

EXPLAIN SELECT * FROM user WHERE name = '张三';

重点关注：

• **type**：system > const > eq_ref > ref > range > index > ALL，至少要到 ref 或 range，出现 ALL 就是全表扫描。
• **key**：实际使用的索引。
• **Extra**：出现 Using filesort 或 Using temporary 说明需要额外排序或临时表，性能较差；出现 Using index 说明命中覆盖索引，很好。

索引不是越多越好

• 索引会占用额外磁盘空间。
• 每次 INSERT、UPDATE、DELETE 都要维护索引，写操作会变慢。
• 一般单表索引不超过 5 个，联合索引字段不超过 5 个。

一句话总结

• InnoDB 用 B+ 树，3 层树就能支撑千万级数据。
• 二级索引查询需要回表，尽量用覆盖索引优化。
• 联合索引遵循最左前缀原则。
• 对索引列做运算、隐式类型转换、前缀模糊匹配都会导致索引失效。
• 用 EXPLAIN 分析执行计划，别凭感觉加索引。