02.4 - EXPLAIN 与查询优化

定位：EXPLAIN 是 MySQL 查询优化的核心工具——理解执行计划是 DBA 和后端开发的核心技能 面试高频度：⭐⭐⭐⭐⭐ 考查方式：分析 EXPLAIN 输出判断慢查询原因、type 列各种类型的含义及优劣、如何通过执行计划优化 SQL

一、这是什么？为什么需要它？

是什么

EXPLAIN 是 MySQL 提供的查询执行计划分析工具。它展示优化器选择用什么方式执行一条 SQL——用哪个索引、扫描多少行、做哪些连接。

为什么需要 EXPLAIN？

没有 EXPLAIN，优化 SQL 就像蒙眼开车：

你以为走了索引，实际是全表扫描
你以为用了联合索引全部列，实际只用了前缀
你以为只查 10 行，实际扫描了 10 万行
你以为 JOIN 顺序是高效的那个，实际是低效的

核心 insight：EXPLAIN 不是"调优秘籍"，它是"debug 工具"——告诉你优化器的实际决策，让你能验证假设。

二、原理拆解

2.1 EXPLAIN 输出字段详解

sql

EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 18\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u            ← 表名/别名
   partitions: NULL
         type: ALL          ← 访问类型（最关键字段）
possible_keys: PRIMARY       ← 可能使用的索引
          key: NULL          ← 实际使用的索引（NULL = 没有）
      key_len: NULL          ← 使用索引的长度（越精确越好）
          ref: NULL
         rows: 10000         ← 预估需要扫描的行数
     filtered: 33.33         ← 经过条件过滤后的百分比
        Extra: Using where   ← 额外信息

2.2 type 列：访问类型（最重要的字段）

从最优到最差排序：

type	含义	说明	速查
system	系统表，只有 1 行	极少见	🏆 最快
const	主键/唯一索引等值查询	`WHERE id = 1`	🥇
eq_ref	JOIN 时，被驱动表用主键/唯一索引匹配	`JOIN ... ON t.id = ...`	🥇
ref	非唯一索引等值查询	`WHERE name = 'Alice'`	✅
fulltext	全文索引匹配	MATCH AGAINST	⚠️
ref_or_null	ref + 额外查找 NULL	`WHERE name = 'Alice' OR name IS NULL`	✅
index_merge	合并多个索引结果	两个 WHERE 条件各用不同索引	⚠️
unique_subquery	IN 子查询唯一索引	`WHERE id IN (SELECT ...)`	✅
index_subquery	IN 子查询非唯一索引	同上但非唯一	⚠️
range	索引范围扫描	`>`, `<`, `BETWEEN`, `IN`, `LIKE 'abc%'`	✅
index	全索引扫描	扫描整个索引树（比 ALL 好）	⚠️
ALL	全表扫描	没有使用索引	❌ 最慢

优化目标：至少达到 range 级别，最好达到 ref 或 const。

2.3 Extra 列：额外信息

Extra 信息	含义	是否需要优化
Using index	覆盖索引，不需要回表	✅ 最好
Using index condition	ICP 索引下推生效	✅ 好
Using where	在 Server 层过滤数据	⚠️ 正常，但可能表示需要索引
Using MRR	Multi-Range Read 优化	✅ 好
Using filesort	需要额外排序操作	⚠️ 尽量优化
Using temporary	使用了临时表（如 GROUP BY）	⚠️ 尽量优化
Using index for group-by	松散索引扫描用于 GROUP BY	✅ 高效

2.4 EXPLAIN FORMAT = TREE（8.0.16+）

sql

EXPLAIN FORMAT=TREE 
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 18\G

*************************** 1. row ***************************
EXPLAIN: 
-> Nested loop inner join  (cost=2250 rows=3333)
    -> Filter: (u.age > 18)  (cost=1000 rows=3333)
        -> Table scan on u  (cost=1000 rows=10000)
    -> Index lookup on o using idx_user_id (user_id=u.id)  
       (cost=0.25 rows=1)

优势：树形结构直接展示执行路径，比传统 EXPLAIN 更直观。

2.5 EXPLAIN ANALYZE（8.0.18+）

sql

EXPLAIN ANALYZE 
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 18\G

*************************** 1. row ***************************
EXPLAIN: 
-> Nested loop inner join  
   (actual time=0.12..12.3 rows=3333 loops=1)
    -> Filter: (u.age > 18)  
       (actual time=0.05..5.2 rows=3333 loops=1)
        -> Table scan on u  
           (actual time=0.02..3.1 rows=10000 loops=1)
    -> Index lookup on o using idx_user_id (user_id=u.id)  
       (actual time=0.01..0.02 rows=1 loops=3333)

核心优势：显示实际执行的耗时和行数（不仅仅是优化器的预估），可以精确定位慢在哪里。

三、图解全景

               EXPLAIN 使用流程图

         ┌─────────────────────────┐
         │ 识别慢查询 (slow query)  │
         │ SHOW PROCESSLIST;       │
         │ slow_query_log          │
         └───────────┬─────────────┘
                     │
                     ▼
         ┌─────────────────────────┐
         │ 第一步: EXPLAIN 分析      │
         │ 看 type/key/rows/Extra   │
         └───────────┬─────────────┘
                     │
         ┌───────────┴───────────┐
         │                       │
         ▼                       ▼
   ┌─────────────────┐   ┌─────────────────┐
   │ type=ALL?        │   │ type=range/ref  │
   │ key=NULL?        │   │ key 有值         │
   │ rows 很大?       │   │ Extra 理想?      │
   └────────┬────────┘   └────────┬────────┘
            │                     │
            ▼                     ▼
   ┌─────────────────┐   ┌─────────────────┐
   │ 需要优化          │   │ 第二步: 看细节    │
   │ ① 检查索引        │   │ Extra 有什么?    │
   │ ② 改写 SQL       │   │ 需要进一步优化？  │
   │ ③ ANALYZE TABLE  │   └────────┬────────┘
   └────────┬────────┘            │
            │                     │
            └──────────┬──────────┘
                       ▼
         ┌─────────────────────────┐
         │ 第三步: 优化确认         │
         │ 再次 EXPLAIN 确认效果    │
         │ 实际查询测试耗时         │
         └─────────────────────────┘

四、实战验证

4.1 常见慢查询场景分析

场景 1：没有索引的全表扫描

sql

EXPLAIN SELECT * FROM users WHERE age = 30;
-- type: ALL, rows: 100000, Extra: Using where

诊断：全表扫描 10 万行。age 列没有索引。

优化：

sql

ALTER TABLE users ADD INDEX idx_age (age);

EXPLAIN SELECT * FROM users WHERE age = 30;
-- type: ref, rows: 100, key: idx_age

场景 2：索引失效——函数操作

sql

EXPLAIN SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- type: ALL, rows: 500000

诊断：虽然 create_time 有索引，但函数包装导致无法使用。

优化：

sql

-- 改写为范围查询，索引可以生效
EXPLAIN SELECT * FROM orders 
WHERE create_time >= '2024-01-01 00:00:00' 
  AND create_time < '2024-01-02 00:00:00';
-- type: range, rows: 200

场景 3：最左前缀不满足

sql

EXPLAIN SELECT * FROM users WHERE b = 1 AND c = 2;
-- 假设联合索引 (a,b,c)
-- type: ALL, 或 type: index (如果走全索引扫描)

诊断：跳过了 a 列，无法使用联合索引前缀。

优化：添加单独索引 INDEX(b,c) 或改写查询（如果可能带上 a 条件）。

场景 4：ORDER BY 导致 filesort

sql

EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY create_time;
-- Extra: Using where; Using filesort

诊断：排序没有用到索引。

优化：

sql

-- 让排序也走索引
ALTER TABLE users ADD INDEX idx_age_create_time (age, create_time);

EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY create_time;
-- Extra: Using where; Using index condition

场景 5：JOIN 被驱动表没有索引

sql

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- orders 表 type: ALL, rows: 100000（被驱动表全表扫描）

优化：

sql

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- orders 表 type: ref, rows: 10

4.2 慢查询排查完整流程

sql

-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

-- 2. 找到慢查询
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- 3. 分析执行计划
EXPLAIN FORMAT=TREE [慢查询 SQL];

-- 4. 查看优化器追踪（更详细）
SET optimizer_trace='enabled=on';
[执行慢查询 SQL];
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

-- 5. 验证优化效果
-- 优化后再次 EXPLAIN 和实际执行

4.3 快速诊断命令

sql

-- 查看当前运行的查询
SHOW PROCESSLIST;

-- 查看全表扫描的查询（5.7+）
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_db';

-- 查看表扫描情况
SELECT * FROM sys.statements_with_full_table_scans;

五、面试视角

追问	答案要点
EXPLAIN 的 type 从好到差排序？	system > const > eq_ref > ref > range > index > ALL
怎么判断有没有用到索引？	type 不是 ALL，key 不是 NULL，key_len 显示实际使用长度
Extra: Using filesort 是什么意思？	无法用索引排序，MySQL 在内存/磁盘创建了排序文件。需要优化 ORDER BY 对应的索引
Extra: Using temporary 是什么意思？	用了临时表（常见于 GROUP BY 无索引）。优化索引让 GROUP BY 用松散索引扫描
EXPLAIN rows 准确吗？	是优化器预估的，不一定准确。用 `EXPLAIN ANALYZE` 看实际行数
怎么让 EXPLAIN 数据更准确？	定期 `ANALYZE TABLE` 更新统计信息
怎么查看实际执行时间？	8.0.18+ 用 `EXPLAIN ANALYZE`；低版本用 `SHOW PROFILES`

📚 相关链接

前置基础：**B+树索引结构**
索引物理：**聚簇索引与二级索引**
优化策略：**索引优化策略**
性能调优：**性能调优**
← 返回 **索引与查询优化索引**

架构与存储引擎

索引与查询优化

SQL与事务

锁机制

日志系统

高可用与架构

性能调优

02.4 - EXPLAIN 与查询优化

一、这是什么？为什么需要它？

是什么

为什么需要 EXPLAIN？

二、原理拆解

2.1 EXPLAIN 输出字段详解

2.2 type 列：访问类型（最重要的字段）

2.3 Extra 列：额外信息

2.4 EXPLAIN FORMAT = TREE（8.0.16+）

2.5 EXPLAIN ANALYZE（8.0.18+）

三、图解全景

四、实战验证

4.1 常见慢查询场景分析

场景 1：没有索引的全表扫描

场景 2：索引失效——函数操作

场景 3：最左前缀不满足

场景 4：ORDER BY 导致 filesort

场景 5：JOIN 被驱动表没有索引

4.2 慢查询排查完整流程

4.3 快速诊断命令

五、面试视角

📚 相关链接

02.4 - EXPLAIN 与查询优化 ​

一、这是什么？为什么需要它？ ​

是什么 ​

为什么需要 EXPLAIN？ ​

二、原理拆解 ​

2.1 EXPLAIN 输出字段详解 ​

2.2 type 列：访问类型（最重要的字段） ​

2.3 Extra 列：额外信息 ​

2.4 EXPLAIN FORMAT = TREE（8.0.16+） ​

2.5 EXPLAIN ANALYZE（8.0.18+） ​

三、图解全景 ​

四、实战验证 ​

4.1 常见慢查询场景分析 ​

场景 1：没有索引的全表扫描 ​

场景 2：索引失效——函数操作 ​

场景 3：最左前缀不满足 ​

场景 4：ORDER BY 导致 filesort ​

场景 5：JOIN 被驱动表没有索引 ​

4.2 慢查询排查完整流程 ​

4.3 快速诊断命令 ​

五、面试视角 ​

📚 相关链接 ​

02.4 - EXPLAIN 与查询优化

一、这是什么？为什么需要它？

是什么

为什么需要 EXPLAIN？

二、原理拆解

2.1 EXPLAIN 输出字段详解

2.2 type 列：访问类型（最重要的字段）

2.3 Extra 列：额外信息

2.4 EXPLAIN FORMAT = TREE（8.0.16+）

2.5 EXPLAIN ANALYZE（8.0.18+）

三、图解全景

四、实战验证

4.1 常见慢查询场景分析

场景 1：没有索引的全表扫描

场景 2：索引失效——函数操作

场景 3：最左前缀不满足

场景 4：ORDER BY 导致 filesort

场景 5：JOIN 被驱动表没有索引

4.2 慢查询排查完整流程

4.3 快速诊断命令

五、面试视角

📚 相关链接