MySQL: MySQL索引进阶与EXPLAIN执行计划
最后更新：2026-08-26
理解索引的高级特性，才能真正写出高性能查询。
本课深入讲解索引类型和 EXPLAIN 分析。
1. 你将学到
- 聚簇索引 vs 非聚簇索引
- 覆盖索引
- 联合索引与最左前缀
- 索引失效场景
- EXPLAIN 执行计划解读
2. 聚簇索引与非聚簇索引
| 维度 | 聚簇索引 | 非聚簇索引 |
|---|---|---|
| 存储方式 | 数据和索引在一起 | 索引指向数据地址 |
| 数量 | 只能一个 | 可以多个 |
| 主键 | InnoDB 自动用主键 | 二级索引 |
| 查询 | 直接获取数据 | 需要回表 |
(1) 回表查询
graph LR
A[二级索引<br/>idx_email] -->|主键值| B[聚簇索引<br/>PRIMARY]
B -->|完整数据| C[数据行]
SQL
-- 回表过程
SELECT * FROM users WHERE email = 'alice@email.com';
-- 1. 在 idx_email 找到 id=1
-- 2. 在 PRIMARY 找到 id=1 的完整数据
3. 覆盖索引
查询的字段都在索引中，无需回表。
▶ 示例：覆盖索引
SQL
-- 创建联合索引
CREATE INDEX idx_name_email ON users(username, email);
-- 覆盖索引（无需回表）
SELECT username, email FROM users WHERE username = 'alice';
-- 非覆盖索引（需要回表）
SELECT * FROM users WHERE username = 'alice';
| 类型 | 是否回表 | 性能 |
|---|---|---|
| 覆盖索引 | 否 | 快 |
| 非覆盖索引 | 是 | 慢 |
4. 联合索引与最左前缀
(1) 最左前缀原则
联合索引 (a, b, c) 可以被以下查询使用：
SQL
WHERE a = 1 -- ✅ 使用索引
WHERE a = 1 AND b = 2 -- ✅ 使用索引
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ 使用索引
WHERE b = 2 -- ❌ 未使用索引
WHERE b = 2 AND c = 3 -- ❌ 未使用索引
▶ 示例：索引使用验证
SQL
CREATE INDEX idx_abc ON orders(customer_id, status, order_date);
-- ✅ 使用索引
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid' AND order_date > '2026-01-01';
-- ❌ 未使用索引（违反最左前缀）
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
5. 索引失效场景
| 场景 | 示例 | 原因 |
|---|---|---|
| 函数操作 | WHERE YEAR(date) = 2026 |
索引被函数包裹 |
| 隐式转换 | WHERE phone = 13800001111 |
字符串列用数字查询 |
| LIKE 左模糊 | WHERE name LIKE '%john' |
前缀不确定 |
| OR 条件 | WHERE a = 1 OR b = 2 |
部分字段无索引 |
| NOT IN/NOT EXISTS | WHERE id NOT IN (1,2) |
优化器选择全表扫描 |
| IS NULL/IS NOT NULL | WHERE col IS NULL |
取决于 NULL 比例 |
▶ 示例：索引失效与修复
SQL
-- ❌ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- ✅ 修复：改为范围查询
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- ❌ 索引失效
SELECT * FROM users WHERE phone = 13800001111;
-- ✅ 修复：加引号
SELECT * FROM users WHERE phone = '13800001111';
6. EXPLAIN 执行计划
▶ 示例：EXPLAIN 用法
SQL
EXPLAIN SELECT * FROM users WHERE email = 'alice@email.com';
输出：
TEXT
📖 仅展示
+----+-------+------+---------+------+----------+-------+
| id | type | key | key_len | ref | rows | Extra |
+----+-------+------+---------+------+----------+-------+
| 1 | const | idx_email | 402 | const | 1 | |
+----+-------+------+---------+------+----------+-------+
(1) 关键字段说明
| 字段 | 说明 | 好值 |
|---|---|---|
| type | 访问类型 | const > eq_ref > ref > range > index > ALL |
| key | 使用的索引 | 不为 NULL |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index（覆盖索引） |
(2) type 访问类型
| 类型 | 说明 | 优劣 |
|---|---|---|
| const | 主键/唯一索引等值查询 | ⭐⭐⭐ |
| eq_ref | 连接时使用主键/唯一索引 | ⭐⭐⭐ |
| ref | 非唯一索引等值查询 | ⭐⭐ |
| range | 索引范围查询 | ⭐⭐ |
| index | 全索引扫描 | ⭐ |
| ALL | 全表扫描 | ❌ |
❓ 常见问题
Q 联合索引的字段顺序怎么选？
A 选择性高的放前面，查询频率高的放前面。
Q EXPLAIN 的 rows 准确吗？
A 是估算值，不是精确值。但可以用来判断查询效率。
Q 索引失效怎么办？
A 改写 SQL 避免索引失效场景，或创建函数索引（MySQL 8.0+）。
Q EXPLAIN 的 rows 准确吗？
A 是估算值，不是精确值，但可对比相对变化判断优化效果。用 ANALYZE TABLE 更新统计信息。
Q 什么时候用前缀索引？
A VARCHAR 字段很长且前缀区分度够高时。如
INDEX(email(10))，前 10 字符区分度>95%即可。📖 小节
- 聚簇索引数据和索引在一起，非聚簇索引需要回表
- 覆盖索引查询字段都在索引中，无需回表
- 最左前缀联合索引从左到右匹配
- 索引失效场景：函数、隐式转换、左模糊、OR
- EXPLAIN 分析查询计划，关注 type/key/rows
📝 作业
-
基础题（难度⭐）：用 EXPLAIN 分析一个查询，确认是否使用了索引。
-
进阶题（难度⭐⭐）：创建联合索引，验证最左前缀原则。
-
挑战题（难度⭐⭐⭐）：找出 3 个索引失效的场景并给出修复方案。
