EXPLAIN 是 MySQL 性能优化的核心工具，通过分析查询执行计划，可以精准定位性能瓶颈。本文从基础用法到高级技巧，全面解析 EXPLAIN 的每个细节，并提供实战案例与优化策略。
EXPLAIN 基础用法
EXPLAIN 是 MySQL 中用于分析查询执行计划的命令，它可以帮助开发者和数据库管理员了解 MySQL 是如何处理 SQL 查询的。EXPLAIN 有三种格式：TRADITIONAL、JSON 和 TREE，分别适用于不同的需求和版本。
基本语法如下：
EXPLAIN [FORMAT=JSON|TREE|TRADITIONAL] SELECT * FROM table WHERE...;
示例
EXPLAIN SELECT * FROM products
WHERE category = 'electronics'
ORDER BY price DESC
LIMIT 10;
选项说明
- TRADITIONAL：默认格式，输出表格形式。
- JSON：详细信息输出，适用于 MySQL 5.6 及以上版本。
- TREE：树形结构输出，适用于 MySQL 8.0 及以上版本。
关键执行计划列解析
EXPLAIN 输出中包含多个关键列，每个列都有其特定含义和优化建议。
id
查询标识符，用于标识查询中的每个部分。通常用于多表查询时区分不同表的访问顺序。
select_type
查询类型，用于区分查询的复杂程度，如 SIMPLE、JOIN、SUBQUERY 等。
table
访问的表，显示查询涉及的表名。
partitions
匹配的分区，用于显示查询涉及的分区信息。
type
访问类型，表示 MySQL 如何查找表中的行。按性能从优到劣排序，包括 system、const、eq_ref、ref、fulltext、range、index 和 ALL。
possible_keys
可能使用的索引，显示 MySQL 在查找过程中可能使用的索引。
key
实际使用的索引，显示 MySQL 选择的索引。
key_len
使用的索引长度，显示索引使用的字节数。计算规则包括整型、字符串、NULL标记等。
ref
索引比较的列，显示索引列与查询条件的比较方式。
rows
预估检查行数，显示 MySQL 预计需要检查的行数。
filtered
条件过滤百分比，显示 MySQL 预计过滤掉的行数百分比。
Extra
额外信息，提供关于查询执行的额外提示，如 Using index、Using where、Using temporary 等。
type 访问类型深度解析
type 列是 EXPLAIN 中最重要的列之一，它表示 MySQL 如何查找表中的行。按性能从优到劣排序，包括以下类型：
- system：系统表只有一行，性能最优。
- const：通过主键/唯一索引查找单行，如
WHERE id = 1。 - eq_ref：关联查询中主键/唯一索引匹配，如
JOIN ON t1.id = t2.primary_key。 - ref：普通索引等值查询，如
WHERE index_col = 'value'。 - fulltext：全文索引搜索，如
MATCH(content) AGAINST('text')。 - range：索引范围扫描，如
WHERE id > 100 AND id < 200。 - index：全索引扫描，如
SELECT indexed_col FROM table。 - ALL：全表扫描，性能最差。
优化建议
- 确保高频查询至少达到 range 级别。
- 出现 ALL 时需考虑添加索引。
key_len 计算规则
key_len 显示索引使用的字节数，计算方式包括：
- 整型：INT=4字节，BIGINT=8字节
- 字符串：字符集相关（如 utf8mb4=4字节/字符）
- NULL标记：额外1字节
示例
CREATE TABLE demo (
id INT NOT NULL, -- 4
name VARCHAR(20) NOT NULL,-- 20*4=80
age TINYINT NULL -- 1+1(NULL标记)
);
索引 (id, name, age) 的 key_len = 4 + 80 + 2 = 86。
Extra 列关键信息
Extra 列提供关于查询执行的额外信息，包括：
- Using index：覆盖索引，理想情况。
- Using where：服务器层过滤，检查索引使用。
- Using temporary：使用临时表，优化 GROUP BY/ORDER BY。
- Using filesort：文件排序，添加排序索引。
- Select tables optimized away：优化器优化掉表访问，无需优化。
- Impossible WHERE：查询条件永远不成立，检查业务逻辑。
EXPLAIN 实战分析
案例1：简单查询
EXPLAIN SELECT * FROM users WHERE id = 100;
典型输出：
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
分析： - type=const：主键精确查找，最优性能 - rows=1：只需读取1行
案例2：关联查询
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.amount > 100;
典型输出：
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | o | NULL | range | user_id | user_id | 5 | NULL | 200 | 100.00 | Using where |
| 1 | SIMPLE | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db.o.user_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
分析： - 驱动表 orders 使用 range 扫描 - 被驱动表 users 使用 eq_ref 高效关联 - 优化点：确保 amount 字段有索引
案例3：复杂查询优化
问题SQL：
EXPLAIN SELECT * FROM products
WHERE category = 'electronics'
ORDER BY price DESC
LIMIT 10;
优化方案：
-- 添加联合索引
CREATE INDEX idx_category_price ON products(category, price DESC);
再执行 EXPLAIN：
EXPLAIN SELECT * FROM products
WHERE category = 'electronics'
ORDER BY price DESC
LIMIT 10;
优化后输出：
+----+...+--------+---------------------+---------+------+----------+-------------+
| id |...| type | key | key_len | rows | filtered | Extra |
+----+...+--------+---------------------+---------+------+----------+-------------+
| 1 |...| ref | idx_category_price | 102 | 100 | 100.00 | Using index |
+----+...+--------+---------------------+---------+------+----------+-------------+
分析： - 使用联合索引后，type 变为 ref - key_len 显示索引使用的字节数 - Extra 显示 Using index，表示覆盖索引
EXPLAIN 高级技巧
EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE 提供实际执行时间，帮助更准确地评估查询性能。
EXPLAIN ANALYZE SELECT * FROM large_table WHERE id > 1000 LIMIT 10;
输出：
-> Limit: 10 row(s) (actual time=0.1..0.3 rows=10 loops=1)
-> Filter: (large_table.id > 1000) (cost=1.2 rows=10)
-> Index range scan on large_table using PRIMARY (cost=1.2 rows=10)
JSON格式输出
JSON 格式输出提供更详细的执行计划信息，适用于 MySQL 5.6 及以上版本。
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20;
输出：
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "45.67"
},
"table": {
"access_type": "range",
"rows_examined_per_scan": 5000,
"rows_produced_per_join": 2500,
"filtered": "50.00"
}
}
}
可视化工具推荐
- MySQL Workbench：图形化执行计划展示
- Percona PMM：监控查询性能
- pt-visual-explain：将 EXPLAIN 转为可视化图表
执行计划优化策略
索引优化
- 缺失索引：当 possible_keys 为 NULL 时考虑添加索引
- 选择不当：当 key 列显示非最优索引时使用 FORCE INDEX
- 索引失效：检查 type 为 ALL 的查询，确保有合适的索引
查询重写
- 拆分复杂查询：将大查询拆分为多个简单查询
- 避免 SELECT *：只查询必要字段
- 优化子查询：将子查询转为 JOIN 操作
配置调优
- join_buffer_size：优化 Using join buffer 情况
- sort_buffer_size：解决 Using filesort 问题
- tmp_table_size：减少 Using temporary 出现
常见问题解决方案
问题1：全表扫描（type=ALL）
解决方案： - 为 WHERE 条件列添加索引 - 检查是否有索引失效情况（如使用函数）
问题2：文件排序（Using filesort）
解决方案： - 为 ORDER BY 列添加索引 - 使用覆盖索引避免回表
问题3：临时表（Using temporary）
解决方案： - 优化 GROUP BY 子句 - 增加 tmp_table_size - 为分组字段添加索引
EXPLAIN 检查清单
为了确保查询性能优化，建议使用以下检查清单：
- type 列是否达到最优级别（至少 range）
- key 列是否使用了预期索引
- rows 列预估行数是否合理
- Extra 列是否有警告信息
- 联合索引是否满足最左前缀原则
- 是否存在索引扫描（index）可优化为索引查找（ref）
通过系统性地分析 EXPLAIN 结果，可以精准定位查询性能问题。建议将 EXPLAIN 作为日常 SQL 审查的标准步骤，持续优化数据库性能。
关键字列表：MySQL, EXPLAIN, 索引优化, 查询性能, 执行计划, 覆盖索引, 临时表, 文件排序, 分区, 事务, 锁机制