EXPLAIN：SQL优化大法
SQL 优化这件事，说难很难——索引怎么建、SQL 怎么写、JOIN 顺序怎么排，每个点都能展开讲三天三夜。
但说简单也简单：第一步永远是先看执行计划。很多工程师优化 SQL 上来就加索引、改写法，结果改完发现没效果，甚至更慢了——就是因为没看执行计划，不知道数据库实际上是怎么跑这条 SQL 的。
EXPLAIN 就是那个让你“看见”数据库想法的工具。这篇文章讲清楚 EXPLAIN 的各个字段，以及常见的优化思路。
EXPLAIN 执行流程概览
为了更好地理解 EXPLAIN 的工作原理，我们先来看一下 MySQL 执行 SQL 查询的整体流程：
EXPLAIN 各字段关系图
EXPLAIN 输出的各个字段之间存在紧密的关联关系，理解这些关系有助于全面分析执行计划：
基础用法
EXPLAIN SELECT * FROM orders WHERE user_id = 10086;
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 10086; -- JSON 格式，详细信息
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 10086; -- MySQL 8.0+，实际执行并计时
推荐：MySQL 8.0 以上一定要用 EXPLAIN ANALYZE，它会实际执行 SQL 并告诉你预估行数和实际行数的差距。
字段详解
下面是 EXPLAIN 各字段的详细解释和优化思路：
1. id：执行顺序
EXPLAIN
SELECT * FROM orders WHERE user_id = (
SELECT id FROM users WHERE name = '张三'
);
- id 表示这条记录对应 SQL 中的哪个 SELECT。
- id 越大越先执行，id 相同则由上到下执行。
优化思路：子查询尽量转为 JOIN（有时候 MySQL 优化器会先执行子查询，但手动调整 SQL 结构可能效果更好）。
2. select_type：查询类型
| 值 | 含义 |
|---|---|
| SIMPLE | 简单 SELECT，不含子查询或 UNION |
| PRIMARY | 主查询（最外层的 SELECT） |
| SUBQUERY | 子查询（位于 WHERE 后的子查询） |
| DERIVED | 派生表（FROM 后的子查询） |
| UNION | UNION 的第二个及之后的 SELECT |
| DEPENDENT SUBQUERY | 依赖外层的子查询（性能差，尽量避免） |
典型问题：看到 DEPENDENT SUBQUERY 要警惕——子查询依赖外层每行都要重新执行一次，O(n²) 复杂度。
-- 差：DEPENDENT SUBQUERY
SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE name = '张三'
);
-- 好：改写为 JOIN
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.name = '张三';
3. type：访问类型（重要）
type 访问类型性能对比
为了更直观地展示不同 type 的性能差异，可以参考下面的性能对比图：
优化建议：
- 尽量让查询达到 ref 级别以上
- range 是性能的及格线
- 出现 index 或 ALL 时必须进行优化
这是最核心的字段，表示 MySQL 查找数据的方式。从好到差排序：
const > eq_ref > ref > range > index > ALL
| type | 含义 | 性能 |
|---|---|---|
| const | 主键或唯一索引等值查询，最多返回1条 | 极致 |
| eq_ref | JOIN 时，被驱动表使用主键或唯一索引等值访问 | 极好 |
| ref | 使用普通索引等值查询，返回匹配的多条记录 | 好 |
| range | 索引范围扫描（>, <, BETWEEN, LIKE 等） | 中等 |
| index | 全索引扫描，只扫描索引树，不扫描数据 | 差 |
| ALL | 全表扫描，读整个数据文件 | 极差 |
优化目标：ref 以上是好的，range 是及格线，index 和 ALL 要优化。
一个反直觉的例子：
-- 查询50万条订单，type=ALL（全表扫描）
EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- 加了索引后，type=range，但扫描行数可能还是很多
ALTER TABLE orders ADD INDEX idx_amount (amount);
EXPLAIN SELECT * FROM orders WHERE amount > 100; -- type=range, rows=350000
-- 改成覆盖索引，Using index condition（索引条件下推）
EXPLAIN SELECT order_no, amount FROM orders WHERE amount > 100;
-- Extra: Using index condition，type=ref
4. possible_keys 和 key：索引选择
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND amount > 100;
- possible_keys：MySQL 认为可能用到的索引（候选）
- key：MySQL 实际选中的索引
常见问题：possible_keys 有值，但 key 是 NULL —— 说明优化器认为全表扫描更快。可能是：
- 数据量太小（全表扫描更快）
- 索引列区分度低（优化器觉得不划算）
- 统计信息不准（执行
ANALYZE TABLE更新统计信息）
5. key_len：索引覆盖长度
key_len 表示使用了索引的前多少字节。可以用来判断复合索引用到了哪几列：
-- 复合索引 idx_user_status(user_id, status)
EXPLAIN SELECT * FROM orders WHERE user_id = 10086;
-- key_len = 8（user_id 是 BIGINT，8字节）
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 1;
-- key_len = 9（再加1字节 TINYINT）
优化思路：通过 key_len 判断复合索引被利用的程度，调整查询条件顺序。
6. rows：扫描行数（预估）
MySQL 优化器估算这条查询需要扫描的行数。越小越好。
但这是估算值，不一定准。用 EXPLAIN ANALYZE 可以看到实际扫描行数：
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 10086;
-- 输出：
-- -> Filter: (orders.user_id = 10086) (cost=1234 rows=5678 actual=5678)
-- -> Table scan on orders (cost=1234 rows=100000)
如果估算 rows 和实际 rows 差距巨大，说明统计信息不准，执行 ANALYZE TABLE 可能有效。
7. Extra：额外信息（关键）
Extra 字段优化流程图
Extra 字段中的各种提示信息对应不同的优化策略，可以参考下面的优化流程图：
这个字段最复杂，也是最能暴露问题的地方。
Using filesort（要优化）
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC;
-- Extra: Using filesort
filesort 表示无法用索引完成排序，需要在内存或磁盘中额外排序。数据量大时非常慢。
解决方案：加一个 (user_id, created_at) 的复合索引，让查询和排序都在索引中完成。
-- 覆盖了查询条件和排序字段的复合索引
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
-- 再次 EXPLAIN，Extra: Using index condition（Using filesort 消失）
Using temporary（要优化）
EXPLAIN SELECT order_no FROM orders GROUP BY order_no;
-- Extra: Using temporary; Using filesort
使用了临时表分组，通常意味着性能差。
解决方案：如果 order_no 有索引，可以直接利用索引有序特性分组，避免临时表。
Using index condition（ICP，索引下推）
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND amount > 100;
-- Extra: Using index condition
ICP 是 MySQL 5.6+ 的优化：在索引树遍历过程中，先用索引条件过滤数据，减少回表次数。
这是好的，说明索引被有效利用了。
Using index（覆盖索引）
EXPLAIN SELECT user_id, amount FROM orders WHERE user_id = 10086;
-- Extra: Using index
查询的列全部在索引里，不需要回表，性能极好。
优化思路：有时候加一个“冗余列”到索引里，可以把查询变成覆盖索引，避免回表。
常见 SQL 的 EXPLAIN 案例
案例1：JOIN 顺序问题
EXPLAIN
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = 10086;
看两行的 id，id 相同则由上到下执行。上面是 o（orders）先执行，然后是 u（users）。被驱动表（出现在 JOIN 右边的表）应该用索引列去关联。
优化：
- 确保
users.id有主键索引 - 确保
orders.user_id有索引 - 如果数据量大，考虑
STRAIGHT_JOIN强制顺序（慎用）
案例2：OR 导致索引失效
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 OR status = 1;
-- type: ALL（全表扫描）
OR 是索引杀手。只要 OR 连接的列有一个没索引，整条查询就退化为全表扫描。
优化方案：
- 加上缺失列的索引
- 或者拆成两条 SQL + UNION：
EXPLAIN SELECT * FROM orders WHERE user_id = 10086
UNION
SELECT * FROM orders WHERE status = 1 AND user_id != 10086;
案例3：LIKE 前缀通配符
EXPLAIN SELECT * FROM orders WHERE order_no LIKE '%2024%';
-- type: ALL（全表扫描）
LIKE '%xxx' 无法使用索引，因为前缀不确定。
优化方案：
- 如果业务允许，用 Elasticsearch
- 如果一定要 MySQL，考虑全文索引（FULLTEXT）
- 如果是固定前缀，改为后缀通配符：
-- 差：全表扫描
SELECT * FROM orders WHERE order_no LIKE '%2024%';
-- 好：可以走索引（但只能后缀）
SELECT * FROM orders WHERE order_no LIKE '2024%';
EXPLAIN ANALYZE：MySQL 8.0+ 的神器
EXPLAIN ANALYZE
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = 10086;
输出示例：
-> Nested loop inner join (cost=1234 rows=567)
-> Index lookup on o using idx_user_id (user_id=10086) (cost=234 rows=567 actual=567ms)
-> Index lookup on u using PRIMARY (id=o.user_id) (cost=0.25 rows=1 actual=0.1ms)
注意 actual 列——这是实际执行时间，和 cost 估算对比，能发现优化器判断失误的地方。
SQL 优化决策流程图
基于 EXPLAIN 结果的优化决策可以遵循以下流程：
深入理解索引覆盖与索引下推
在 EXPLAIN 的 Extra 字段中，Using index 和 Using index condition 是两个重要的优化提示，分别对应索引覆盖和索引下推技术。理解这两个概念对于 SQL 优化至关重要。
索引覆盖（Covering Index）
索引覆盖是指查询所需的所有列都包含在索引中，不需要回表查询数据行。
工作原理
-
普通索引查询流程：
- 在索引树中找到符合条件的索引记录
- 根据索引记录中的主键值回表查询完整数据行
- 返回查询结果
-
覆盖索引查询流程：
- 在索引树中找到符合条件的索引记录
- 直接从索引记录中获取所需列的值
- 返回查询结果（无需回表）
示例对比
-- 表结构
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10,2),
status TINYINT,
created_at DATETIME,
INDEX idx_user_id (user_id)
);
-- 普通索引查询（需要回表）
EXPLAIN SELECT * FROM orders WHERE user_id = 10086;
-- Extra: NULL（需要回表查询数据行）
-- 覆盖索引查询（无需回表）
EXPLAIN SELECT user_id FROM orders WHERE user_id = 10086;
-- Extra: Using index（直接从索引获取user_id）
-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, amount);
-- 使用覆盖索引
EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 10086 AND status = 1;
-- Extra: Using index（三列都在索引中）
优化技巧
- 包含查询列：将 SELECT 列表中的列都包含在索引中
- 包含 WHERE 条件列：将 WHERE 条件中的列作为索引前缀
- 权衡索引大小：覆盖索引可能较大，需要权衡空间和性能
- 使用最左前缀原则：确保查询条件使用索引的最左前缀
索引下推（Index Condition Pushdown, ICP）
索引下推是 MySQL 5.6+ 引入的优化技术，允许在存储引擎层过滤 WHERE 条件，减少回表次数。
工作原理对比
没有 ICP 的查询流程：
-- 查询：SELECT * FROM orders WHERE user_id = 10086 AND amount > 100
-- 索引：idx_user_id (user_id)
1. 存储引擎使用索引找到所有 user_id = 10086 的记录
2. 将所有找到的记录（主键）返回给 Server 层
3. Server 层根据主键回表获取完整数据行
4. Server 层应用 amount > 100 的条件过滤
有 ICP 的查询流程：
-- 查询：SELECT * FROM orders WHERE user_id = 10086 AND amount > 100
-- 索引：idx_user_id_amount (user_id, amount)
1. 存储引擎使用索引找到 user_id = 10086 的记录
2. 在存储引擎层直接应用 amount > 100 的条件过滤
3. 只将满足条件的记录（主键）返回给 Server 层
4. Server 层回表获取完整数据行
适用条件
- 范围查询：WHERE 条件包含范围查询（>, <, BETWEEN, LIKE）
- 复合索引：查询条件使用复合索引的前缀列
- 非主键索引：仅适用于二级索引
- InnoDB 引擎：需要 InnoDB 存储引擎
示例演示
-- 创建测试表
CREATE TABLE products (
id INT PRIMARY KEY,
category_id INT,
price DECIMAL(10,2),
name VARCHAR(100),
INDEX idx_category_price (category_id, price)
);
-- 插入测试数据
INSERT INTO products VALUES
(1, 1, 100.00, 'Product A'),
(2, 1, 200.00, 'Product B'),
(3, 2, 150.00, 'Product C'),
(4, 1, 50.00, 'Product D');
-- 开启 ICP（默认开启）
SET optimizer_switch = 'index_condition_pushdown=on';
-- 使用 ICP 的查询
EXPLAIN SELECT * FROM products
WHERE category_id = 1 AND price > 100;
-- Extra: Using index condition
-- 关闭 ICP 对比
SET optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM products
WHERE category_id = 1 AND price > 100;
-- Extra: Using where（需要回表后过滤）
性能影响
- 减少回表次数：在存储引擎层过滤掉不符合条件的记录
- 减少 IO 操作：减少从磁盘读取数据页的次数
- 减少 Server 层负载：减少数据传输和处理开销
索引覆盖 vs 索引下推
| 特性 | 索引覆盖 | 索引下推 |
|---|---|---|
| 核心思想 | 避免回表 | 提前过滤 |
| Extra 显示 | Using index | Using index condition |
| 索引要求 | 包含所有查询列 | 包含过滤条件列 |
| 性能提升 | 避免回表IO | 减少回表数据量 |
| 适用场景 | 查询列少 | 范围查询过滤 |
实战优化建议
-
优先创建覆盖索引
-- 常见查询 SELECT user_id, order_count, last_login FROM users WHERE status = 1; -- 优化索引 ALTER TABLE users ADD INDEX idx_status_user_info (status, user_id, order_count, last_login); -
利用 ICP 优化范围查询
-- 优化前：全索引扫描 + 回表过滤 SELECT * FROM logs WHERE date >= '2024-01-01' AND user_id = 10086; -- 优化后：索引下推 ALTER TABLE logs ADD INDEX idx_user_date (user_id, date); -- Extra: Using index condition -
监控 ICP 效果
-- 查看 ICP 使用情况 SHOW STATUS LIKE '%handler_read%'; -- handler_read_next: 索引扫描次数 -- handler_read_rnd_next: 全表扫描次数 -- 比较开启/关闭 ICP 的性能 SET optimizer_switch = 'index_condition_pushdown=on'; EXPLAIN ANALYZE SELECT ...; SET optimizer_switch = 'index_condition_pushdown=off'; EXPLAIN ANALYZE SELECT ...; -
避免过度索引
- 覆盖索引会增加索引大小
- 定期分析索引使用率：
SHOW INDEX FROM table_name - 删除未使用的索引：
DROP INDEX index_name ON table_name
总结对比
- 索引覆盖是"空间换时间"，通过增加索引列避免回表
- 索引下推是"提前过滤"，在存储引擎层减少不必要的数据传输
- 两者可以结合使用：创建合适的复合索引，既实现覆盖查询，又支持索引下推
在实际优化中，应该：
- 先通过 EXPLAIN 识别是否缺少覆盖索引或未使用 ICP
- 根据查询模式设计合适的复合索引
- 使用 EXPLAIN ANALYZE 验证优化效果
- 监控长期性能变化，适时调整索引策略
总结
EXPLAIN 是 SQL 优化的起点。看懂它，这几件事是核心：
- type 列：目标是 ref 以上，range 是底线，ALL 必须改
- Extra 列：关注 Using filesort 和 Using temporary，这两个是性能杀手
- rows 列：扫描行数越少越好，和实际行数对比判断统计信息准确性
- key 列：看看优化器有没有选中你期望的索引
拿到一个慢 SQL，先 EXPLAIN，看执行计划，再决定怎么改。不要凭感觉改 SQL，改完再 EXPLAIN，对比前后差距。
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
8
0- 0
已为社区贡献3条内容
所有评论(0)