SQL 执行计划解析：从 EXPLAIN 到性能优化的完整指南 - 指南

SQL 执行计划解析：从 EXPLAIN 到性能优化的完整指南

文章目录

• SQL 执行计划解析：从 EXPLAIN 到性能优化的完整指南
• 一、为什么要理解执行计划
• • SQL 性能瓶颈的常见原因
  • 性能问题统计
• ⚡ 二、EXPLAIN 命令详解
• • 基本用法与输出格式
  • 执行计划解读框架
  • 三、关键字段深度解析
  • select_type - 查询类型
  • ️ type - 访问类型（性能关键）
  • key - 实际使用的索引
  • rows - 预估扫描行数
  • Extra - 额外信息（关键提示）
• ️ 四、实战案例分析
• • 案例一：单表查询优化
  • 案例二：多表 JOIN 优化
  • 案例三：索引覆盖优化
• 五、总结与优化指南
• • 执行计划分析 checklist
  • ⚡ 优化黄金法则
  • 高级优化工具
  • 持续学习资源

一、为什么要理解执行计划

SQL 性能瓶颈的常见原因

执行计划的价值​​：

• ​​提前发现问题​​：在SQL执行前预测性能

• ​​​​优化依据​​：提供具体的优化方向

​​- ​​学习工具​​：理解数据库工作原理

性能问题统计

根据对生产环境SQL问题的分析：

• ​​​​65%​​ 的慢查询源于索引使用不当

​​- ​​20%​​ 由于JOIN操作效率低下

​​- ​​10%​​ 因为数据量过大缺乏分页

​​- ​​5%​​ 其他原因（锁竞争、配置问题等）

⚡ 二、EXPLAIN 命令详解

基本用法与输出格式

​​EXPLAIN 基本命令​​：

-- 基本用法
EXPLAIN SELECT * FROM users WHERE age >
25;
-- 详细格式（MySQL 8.0+）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age >
25;
-- 实际执行计划（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age >
25;

​​典型EXPLAIN输出​​：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

执行计划解读框架

三、关键字段深度解析

id - 查询标识符
​​id 字段的含义​​：

• ​​相同id​​：同一查询层级

• ​​递增id​​：子查询或UNION操作

• ​​NULL id​​：派生表或临时结果

​​示例分析​​：

-- 复杂查询示例
EXPLAIN
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u
WHERE u.status = 'active';

​​输出解读​​：

+----+--------------------+-------+------------+------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | u | NULL | ref | idx_status | idx_status | 102 | const| 500 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | o | NULL | ref | idx_user_id | idx_user_id | 5 | func | 10 | 100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+---------+---------+------+------+----------+--------------------------+

select_type - 查询类型

​​常见 select_type 值​​：

类型	描述	出现场景
SIMPLE	简单查询	不含子查询或UNION
PRIMARY	最外层查询	复杂查询的主查询
SUBQUERY	子查询	WHERE子句中的子查询
DERIVED	派生表	FROM子句中的子查询
UNION	UNION查询	UNION第二个及以后的查询
UNION RESULT	UNION结果	UNION结果的临时表

️ type - 访问类型（性能关键）

​​type 性能等级​​（从优到劣）：

​​详细解释​​：

-- 最佳情况：const（主键或唯一索引等值查询）
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const
-- 良好情况：ref（非唯一索引等值查询）
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- type: ref
-- 最差情况：ALL（全表扫描）
EXPLAIN SELECT * FROM users WHERE name LIKE '%john%';
-- type: ALL

key - 实际使用的索引

​​索引使用分析​​：

-- 创建测试索引
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
-- 查看索引选择
EXPLAIN SELECT * FROM users WHERE age >
25 AND city = 'Beijing';

​​可能输出​​：

+----+-------------+-------+------------+------+-------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ref | idx_age,idx_city | idx_city| 102 | const | 100 | 50.00 | Using where |
+----+-------------+-------+------------+------+-------------------+---------+---------+-------+------+----------+-------------+

rows - 预估扫描行数

​​rows 字段的重要性​​：

• 数值越小越好​​：表示需要处理的数据量少

• ​​基于统计信息​​：可能不准确，需要定期分析表

• ​​优化参考​​：帮助识别全表扫描

-- 优化前：全表扫描
EXPLAIN SELECT * FROM users WHERE name LIKE '%john%';
-- rows: 10000（扫描所有行）
-- 优化后：索引扫描
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
-- rows: 1（只扫描1行）

Extra - 额外信息（关键提示）

​​重要 Extra 值解读​​：

Extra 值	含义	优化建议
Using where	使用WHERE过滤	正常，无需优化
Using index	覆盖索引	优秀，无需回表
Using temporary	使用临时表	考虑优化查询或索引
Using filesort	文件排序	添加合适的索引
Range checked for each record	索引选择困难	优化查询条件

️ 四、实战案例分析

案例一：单表查询优化

​​问题查询​​：

SELECT * FROM products
WHERE category = 'electronics'
AND price BETWEEN 1000 AND 2000
ORDER BY created_at DESC;

​​初始执行计划​​：

EXPLAIN SELECT * FROM products
WHERE category = 'electronics'
AND price BETWEEN 1000 AND 2000
ORDER BY created_at DESC;

​​输出分析​​：

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | products | NULL | ALL | NULL | NULL | NULL | NULL | 5000 | 5.00 | Using where;
Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

问题诊断​​：

• type: ALL：全表扫描

• Using filesort：文件排序，性能差

• 缺少合适索引

​​优化方案​​：

-- 创建复合索引
CREATE INDEX idx_category_price_created
ON products(category, price, created_at);
-- 再次查看执行计划
EXPLAIN SELECT * FROM products
WHERE category = 'electronics'
AND price BETWEEN 1000 AND 2000
ORDER BY created_at DESC;

​​优化后输出​​：

+----+-------------+----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-------+
| 1 | SIMPLE | products | NULL | range | idx_category_price_created| idx_category_price_created| 108 | NULL | 50 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-------+

案例二：多表 JOIN 优化

​​问题查询​​​​：

SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Beijing'
AND o.status = 'completed'
ORDER BY o.order_date DESC;

​​​​初始执行计划​​​​：

EXPLAIN SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Beijing'
AND o.status = 'completed'
ORDER BY o.order_date DESC;

​​​​输出分析​​​​：

+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+
| 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 10.00 | Using where;
Using filesort |
| 1 | SIMPLE | o | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.u.id | 1 | 10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+

问题诊断​​：

• u表全表扫描

• 连接顺序可能不是最优

• 缺少合适的索引

​​优化方案​​：

-- 为users表添加城市索引
CREATE INDEX idx_city ON users(city);
-- 为orders表添加状态索引
CREATE INDEX idx_status_user ON orders(status, user_id);
-- 使用STRAIGHT_JOIN强制连接顺序
EXPLAIN SELECT STRAIGHT_JOIN u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Beijing'
AND o.status = 'completed'
ORDER BY o.order_date DESC;

​​优化后输出​​：

+----+-------------+-------+------------+------+---------------------+---------------------+---------+--------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+--------------------+------+----------+---------------------------------+
| 1 | SIMPLE | u | NULL | ref | PRIMARY,idx_city | idx_city | 102 | const | 100 | 100.00 | Using temporary;
Using filesort |
| 1 | SIMPLE | o | NULL | ref | idx_status_user | idx_status_user | 104 | test.u.id,const | 5 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+--------------------+------+----------+---------------------------------+

案例三：索引覆盖优化

​​问题查询​​​​：

SELECT COUNT(*) FROM user_actions
WHERE user_id = 1001 AND action_date >
'2023-01-01';

​​​​初始情况​​​​：

-- 现有索引：INDEX (user_id)
EXPLAIN SELECT COUNT(*) FROM user_actions
WHERE user_id = 1001 AND action_date >
'2023-01-01';

​​​​输出分析​​​​：

+----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | user_actions| NULL | ref | idx_user_id | idx_user_id | 5 | const | 500 | 33.33 | Using where |
+----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

优化方案​​：

-- 创建覆盖索引
CREATE INDEX idx_user_id_action_date
ON user_actions(user_id, action_date);
-- 再次执行
EXPLAIN SELECT COUNT(*) FROM user_actions
WHERE user_id = 1001 AND action_date >
'2023-01-01';

​​优化后输出​​：

+----+-------------+-------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | user_actions| NULL | range | idx_user_id_action_date | idx_user_id_action_date | 8 | NULL | 100 | 100.00 | Using where;
Using index |
+----+-------------+-------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+

优化效果​​：

• Using index：实现索引覆盖，无需回表

• 查询性能提升 3-5 倍

五、总结与优化指南

执行计划分析 checklist

​​快速诊断流程​​：

⚡ 优化黄金法则

​​索引优化策略​​：

• 最左前缀原则​​：复合索引的顺序很重要

• ​​覆盖索引​​：尽可能让索引包含查询字段

• ​​索引选择性​​：选择高区分度的列建索引

​​查询优化技巧​​：

-- 避免 SELECT *
SELECT id, name, email FROM users;
-- 只选择需要的字段
-- 优化 WHERE 条件
SELECT * FROM products WHERE price >
1000;
-- 使用范围查询
SELECT * FROM products WHERE id IN (1,2,3);
-- 使用IN而不是OR
-- 合理使用 LIMIT
SELECT * FROM large_table LIMIT 1000;
-- 限制返回行数

高级优化工具

​​MySQL 8.0 新特性​​：

-- 执行计划可视化
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age >
25;
-- 实际执行分析
EXPLAIN ANALYZE SELECT * FROM users WHERE age >
25;
-- 索引建议
SELECT * FROM sys.schema_index_statistics;

​​性能监控脚本​​：

-- 查找全表扫描的查询
SELECT * FROM sys.statements_with_full_table_scans;
-- 查找需要索引的查询
SELECT * FROM sys.statements_with_errors_or_warnings;

持续学习资源

​​推荐分析命令​​：

-- 定期分析表统计信息
ANALYZE TABLE users;
-- 检查索引使用情况
SHOW INDEX FROM users;
-- 监控慢查询
SHOW VARIABLES LIKE 'slow_query_log%';