MySQL优化之 EXPLAIN 详解：全面解析执行计划

EXPLAIN 是 MySQL 性能优化的核心工具，通过分析查询执行计划，可以精准定位性能瓶颈。以下从基础到高级全面解析 EXPLAIN 的每个细节。

一、EXPLAIN 基础用法
1. 基本语法
EXPLAIN [FORMAT=JSON|TREE|TRADITIONAL] SELECT * FROM table WHERE...;

eg:

EXPLAIN SELECT * FROM products 
WHERE category = 'electronics' 
ORDER BY price DESC 
LIMIT 10;
FORMAT选项：
TRADITIONAL：默认表格格式
JSON：详细信息（MySQL 5.6+）
TREE：树形结构（MySQL 8.0+）
2. 关键执行计划列
列名

说明

id

查询标识符

select_type

查询类型

table

访问的表

partitions

匹配的分区

type

访问类型

possible_keys

可能使用的索引

key

实际使用的索引

key_len

使用的索引长度

ref

索引比较的列

rows

预估检查行数

filtered

条件过滤百分比

Extra

额外信息

二、核心列深度解析
1. type 访问类型（性能关键）
按性能从优到劣排序：

类型

描述

示例场景

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 时需考虑添加索引
2. 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
3. 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 |
+----+...+--------+---------------------+---------+------+----------+-------------+
四、EXPLAIN 高级技巧
1. EXPLAIN ANALYZE (MySQL 8.0+)
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)
2. JSON格式输出
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"
    }
  }
}
3. 可视化工具推荐
MySQL Workbench：图形化执行计划展示
Percona PMM：监控查询性能
pt-visual-explain：将EXPLAIN转为可视化图表
五、执行计划优化策略
1. 索引优化
缺失索引：possible_keys 为 NULL 时考虑添加
索引选择不当：key 列显示非最优索引时使用 FORCE INDEX
索引失效：检查 type 为 ALL 的查询
2. 查询重写
拆分复杂查询：将大查询拆分为多个简单查询
**避免 SELECT ***：只查询必要字段
优化子查询：转为 JOIN 操作
3. 配置调优
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 审查的标准步骤，持续优化数据库性能。
————————————————