【面试题】如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

一、EXPLAIN是什么？

比喻：EXPLAIN就像给SQL语句做全面体检

• X光：看执行计划
• 心电图：看执行路径
• 血常规：看性能指标

-- 用法超简单！
EXPLAIN SELECT * FROM users WHERE age > 25;

二、EXPLAIN输出解读：12项体检指标 📋

先看一个完整的EXPLAIN输出：

mysql> EXPLAIN SELECT * FROM users WHERE age > 25;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 1000 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

翻译："你的SQL有全表扫描问题，需要优化！"

三、关键指标详解：重点关注这5项 🎯

1. type（连接类型） - 最重要！

-- type从好到坏排序（就像考试成绩）：
-- 优等生（A+）  system     ← 表只有一行（系统表）
-- 优等生（A）    const      ← 通过主键或唯一索引一次找到
-- 优等生（A-）   eq_ref     ← 唯一索引关联，每行只匹配一条记录
-- 良好（B）      ref        ← 非唯一索引查找
-- 及格（C）      range      ← 范围扫描（BETWEEN、IN、>、<等）
-- 不及格（D）    index      ← 全索引扫描
-- 差生（F）      ALL        ← 全表扫描（要避免！）

-- 检查你的type：
EXPLAIN SELECT * FROM users WHERE id = 1;  -- type: const（好！）
EXPLAIN SELECT * FROM users WHERE age = 25; -- type: ref（不错）
EXPLAIN SELECT * FROM users WHERE age > 25; -- type: range（还可以）
EXPLAIN SELECT * FROM users;                -- type: ALL（差！全表扫描）

2. key（实际使用的索引）

-- 看SQL用了哪个"导航"
EXPLAIN SELECT * FROM users WHERE age = 25;
-- key: idx_age ✅ 用了age索引

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- key: NULL ❌ 没走索引（可能要加索引）

3. rows（扫描行数）

-- 看SQL要"翻多少页"
EXPLAIN SELECT * FROM users WHERE age = 25;
-- rows: 10 ✅ 只扫描10行

EXPLAIN SELECT * FROM users;
-- rows: 1000000 ❌ 扫描100万行（太多了！）

4. Extra（额外信息） - 看"医嘱"

-- 常见的Extra信息：
-- Using index       ✅ 好！用了覆盖索引（直接从索引取数据）
-- Using where       ⚠️  用了WHERE过滤
-- Using temporary   ❌ 差！用了临时表（GROUP BY、DISTINCT没优化好）
-- Using filesort    ❌ 差！用了文件排序（ORDER BY没走索引）
-- Using join buffer ⚠️  用了连接缓冲区（关联表太大）

EXPLAIN SELECT age FROM users WHERE age > 25;
-- Extra: Using where; Using index ✅

EXPLAIN SELECT * FROM users ORDER BY name;
-- Extra: Using filesort ❌

5. possible_keys vs key

-- possible_keys: 可能用的"导航"（GPS推荐路线）
-- key: 实际用的"导航"（你选的路线）
EXPLAIN SELECT * FROM users WHERE age = 25 OR name = '张三';
-- possible_keys: idx_age,idx_name  （有两条路可选）
-- key: idx_age                     （选了age索引这条路）
-- 如果possible_keys有值，key是NULL → 索引失效了！

四、实战案例：看体检报告治病 🏥

病例1：全表扫描（需要加索引）

-- 症状：查询慢
SELECT * FROM orders WHERE user_id = 100;

-- 体检报告
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
+----+-------------+--------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | type | key           | rows | Extra   |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | 50000| Using where |  -- ❌ type: ALL, key: NULL
+----+-------------+--------+------+---------------+------+---------+------+-------+----------+-------------+

-- 诊断：全表扫描5万行
-- 药方：加索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 复查
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
+----+-------------+--------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table  | type | key           | rows        | Extra   |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user_id   | 10          | NULL    |  -- ✅ type: ref, 只扫10行
+----+-------------+--------+------+---------------+-------------+---------+-------+------+----------+-------+

病例2：文件排序（ORDER BY没走索引）

-- 症状：排序慢
SELECT * FROM products ORDER BY price DESC;

-- 体检报告
EXPLAIN SELECT * FROM products ORDER BY price DESC;
+----+-------------+----------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table    | type | key           | rows | Extra                     |
+----+-------------+----------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | products | ALL  | NULL          | 10000| Using filesort |  -- ❌ 文件排序
+----+-------------+----------+------+---------------+------+---------+------+-------+----------+----------------+

-- 药方1：为排序字段加索引
CREATE INDEX idx_price ON products(price);

-- 药方2：使用覆盖索引（只查索引字段）
SELECT id, price FROM products ORDER BY price DESC;  -- 只查id和price

-- 复查
EXPLAIN SELECT id, price FROM products ORDER BY price DESC;
+----+-------------+----------+-------+---------------+------------+---------+------+-------+----------+-------------+
| id | select_type | table    | type  | key           | rows       | Extra            |
+----+-------------+----------+-------+---------------+------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | products | index | idx_price     | 10000      | Using index |  -- ✅ 覆盖索引
+----+-------------+----------+-------+---------------+------------+---------+------+-------+----------+-------------+

病例3：索引失效（写法问题）

-- 症状：有索引但没走
SELECT * FROM users WHERE YEAR(created_at) = 2023;  -- ❌ 函数操作

-- 体检报告
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2023;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | type | key           | rows | Extra   |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | 10000| Using where |  -- ❌ 索引失效
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

-- 药方：避免对索引列做计算/函数
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- 复查
EXPLAIN SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
+----+-------------+-------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | type  | key           | rows       | Extra   |
+----+-------------+-------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | range | idx_created   | 1000       | Using index condition |  -- ✅
+----+-------------+-------+-------+---------------+------------+---------+------+------+----------+-------------+

五、高级用法：EXPLAIN的多种格式 🎨

1. EXPLAIN FORMAT=JSON（最详细）

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;

输出：JSON格式，包含成本估算、访问类型等详细信息

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "103.75"  -- 查询成本
    },
    "table": {
      "table_name": "users",
      "access_type": "range",  -- 访问类型
      "possible_keys": ["idx_age"],
      "key": "idx_age",
      "used_key_parts": ["age"],
      "rows_examined_per_scan": 500,
      "rows_produced_per_join": 500,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "53.75",
        "eval_cost": "50.00",
        "prefix_cost": "103.75",
        "data_read_per_join": "125K"
      }
    }
  }
}

2. EXPLAIN ANALYZE（MySQL 8.0.18+，真实执行）

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

输出：实际执行时间、循环次数等

-> Filter: (users.age > 25)  (cost=103.75 rows=500) (actual time=0.100..1.500 rows=450 loops=1)
    -> Index range scan on users using idx_age  (cost=103.75 rows=500) (actual time=0.095..1.200 rows=500 loops=1)

翻译："预计扫描500行，实际扫描500行，耗时1.5毫秒"

3. EXPLAIN EXTENDED + SHOW WARNINGS（看优化器重写）

EXPLAIN EXTENDED SELECT * FROM users WHERE age > 25;
SHOW WARNINGS;

输出：优化器重写后的SQL

/* select#1 */ select `test`.`users`.`id` AS `id`, 
`test`.`users`.`name` AS `name`, 
`test`.`users`.`age` AS `age` 
from `test`.`users` 
where (`test`.`users`.`age` > 25)

六、多表关联查询分析 🤝

案例：JOIN查询优化

-- 查询用户及其订单
EXPLAIN 
SELECT u.name, o.order_no 
FROM users u 
JOIN orders o ON u.id = o.user_id
WHERE u.city = '北京';

解读要点：

1. 驱动表选择：MySQL会选择rows少的表作为驱动表
2. 关联类型：看type是eq_ref、ref还是ALL
3. 索引使用：关联字段要有索引

-- 优化前：可能全表扫描
-- 优化后：确保关联字段有索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_city ON users(city);

七、EXPLAIN实战检查清单 ✅

快速诊断SQL的"健康状态"：

-- 1. 有没有走索引？
-- type不能是ALL，key不能是NULL

-- 2. 索引走对了吗？
-- possible_keys有值，key也不能是NULL

-- 3. 扫描行数多吗？
-- rows要尽量小（几百几千可以，几万几十万要优化）

-- 4. 有没有临时表或文件排序？
-- Extra不能有Using temporary、Using filesort

-- 5. 用了覆盖索引吗？
-- Extra有Using index最好

常见问题速查表：

症状	可能原因	解决方案
type: ALL	没索引或索引失效	添加索引、优化WHERE条件
key: NULL	索引失效	检查索引列是否计算、函数、类型转换
Using filesort	ORDER BY没走索引	为排序字段加索引、使用覆盖索引
Using temporary	GROUP BY/DISTINCT没走索引	为分组字段加索引、减少分组字段
rows太大	查询条件过滤性差	加索引、优化查询条件

八、使用工具辅助分析 🔧

1. MySQL Workbench可视化

Visual Explain功能，图形化展示执行计划

2. pt-visual-explain（Percona工具）

# 将EXPLAIN输出转为可视化
mysql -e "EXPLAIN SELECT * FROM users" | pt-visual-explain

3. mysqldumpslow分析慢查询

# 先开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

# 分析慢查询日志
mysqldumpslow -s t /var/log/mysql/slow.log

九、一张图总结EXPLAIN 🗺️

EXPLAIN 使用流程图：

       开始分析SQL
           ↓
     执行EXPLAIN语句
           ↓
 ┌───────────────────┐
 │ 看type列：连接类型 │ ← 最重要！避免ALL
 └─────────┬─────────┘
           ↓
 ┌───────────────────┐
 │ 看key列：用的索引  │ ← 不能是NULL
 └─────────┬─────────┘
           ↓
 ┌───────────────────┐
 │ 看rows列：扫描行数 │ ← 要尽量小
 └─────────┬─────────┘
           ↓
 ┌───────────────────┐
 │ 看Extra列：附加信息│ ← 不能有filesort/temporary
 └─────────┬─────────┘
           ↓
     得出优化建议
           ↓
     创建/调整索引
           ↓
     优化SQL写法
           ↓
     重新EXPLAIN验证

十、总结：EXPLAIN优化口诀 📝

SQL优化不用慌，EXPLAIN来帮大忙。
一看type连接型，ALL扫描要警醒。
二看key用啥索引，NULL值表示没得用。
三看rows扫多少，行数太多效率糟。
四看Extra附加项，filesort要提防。
复合索引顺序对，最左前缀要记牢。
函数计算别乱用，索引失效白忙活。
覆盖索引是法宝，不回表来性能高。
定期分析慢查询，调优之路步步高。

记住：EXPLAIN不是最终答案，而是诊断工具。结合业务实际，多次测试，才能找到最优方案！🔧