EXPLAIN执行计划全解析：从type=ALL到type=const的优化之路
2026/4/30大约 11 分钟
EXPLAIN执行计划全解析：从type=ALL到type=const的优化之路
适读人群：Java后端开发、数据库运维、对SQL性能优化感兴趣的工程师 | 阅读时长：约26分钟
开篇故事
2020年底，我加入了一个新团队，接手了一个运行了三年的电商系统。
接手第一天，DBA给了我一份慢查询报告，上面密密麻麻的SQL，每天产生数千条慢查询，告警轰炸个不停。
其中有一条SQL让我印象深刻：
SELECT a.*, b.name AS category_name, c.shop_name
FROM product a
LEFT JOIN category b ON a.category_id = b.id
LEFT JOIN shop c ON a.shop_id = c.id
WHERE a.status = 1
AND a.price BETWEEN 100 AND 500
AND b.parent_id = 10
ORDER BY a.sales_count DESC
LIMIT 20;前任工程师说这条SQL「应该走了索引」，因为status、price、category_id上都有索引。
我EXPLAIN了一下，截图给他：
id select_type table type possible_keys key rows Extra
1 SIMPLE b ALL PRIMARY NULL 2843 Using where
1 SIMPLE a ref idx_category,idx_status,idx_price idx_category 45231 Using where; Using filesort
1 SIMPLE c ref PRIMARY PRIMARY 1问题是：b表全表扫描了2843行，而且a表的filesort说明ORDER BY没有走索引。整个查询扫描了约13万行，耗时2.3秒。
优化完之后，0.04秒。
今天就把EXPLAIN执行计划的每一个字段都讲透，让你看到EXPLAIN输出就能找到问题所在。
一、EXPLAIN输出字段总览
EXPLAIN SELECT ...;
-- 或者
EXPLAIN FORMAT=JSON SELECT ...; -- 更详细的JSON格式（MySQL 5.6+）
-- 或者
EXPLAIN ANALYZE SELECT ...; -- 执行并显示实际行数（MySQL 8.0.18+）标准EXPLAIN输出的12个字段：
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+每个字段的含义和重要程度：
| 字段 | 重要程度 | 说明 |
|---|---|---|
| id | 高 | 查询序号，相同id同级执行，不同id数字大的先执行 |
| select_type | 中 | 查询类型（SIMPLE/SUBQUERY/UNION等） |
| table | 中 | 当前行操作的表 |
| partitions | 低 | 分区表时显示用到的分区 |
| type | 极高 | 访问类型，性能的关键指标 |
| possible_keys | 中 | 可能用到的索引 |
| key | 极高 | 实际用到的索引 |
| key_len | 高 | 索引使用的字节数 |
| ref | 中 | 与索引比较的列或常量 |
| rows | 高 | 预估扫描行数 |
| filtered | 高 | 经过WHERE过滤后保留的行百分比 |
| Extra | 极高 | 附加信息，包含重要优化线索 |
二、底层原理：type字段的完整梯队
type字段是EXPLAIN中最重要的字段，它反映了MySQL访问数据的方式。
从最优到最差的排序：
const > eq_ref > ref > fulltext > ref_or_null > index_merge >
unique_subquery > index_subquery > range > index > ALL实际工作中主要关注这几个：
性能梯队：
优秀 ┌─────────────────────────────────────────────────┐
│ const : 主键或唯一索引等值查询，最多1行 │
│ eq_ref : JOIN时被驱动表走主键/唯一索引 │
├─────────────────────────────────────────────────┤
良好 │ ref : 非唯一索引等值查询，可能多行 │
│ range : 索引范围扫描 │
├─────────────────────────────────────────────────┤
注意 │ index : 全索引扫描（比ALL好，但仍需关注） │
├─────────────────────────────────────────────────┤
危险 │ ALL : 全表扫描，必须优化 │
└─────────────────────────────────────────────────┘三、完整解决方案与代码
3.1 测试环境搭建
CREATE TABLE product (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
category_id INT NOT NULL,
shop_id INT NOT NULL,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL DEFAULT 1,
sales_count INT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL,
INDEX idx_category (category_id),
INDEX idx_shop (shop_id),
INDEX idx_status (status),
INDEX idx_price (price)
) ENGINE=InnoDB;
CREATE TABLE category (
id INT PRIMARY KEY AUTO_INCREMENT,
parent_id INT NOT NULL DEFAULT 0,
name VARCHAR(100),
INDEX idx_parent (parent_id)
) ENGINE=InnoDB;
CREATE TABLE shop (
id INT PRIMARY KEY AUTO_INCREMENT,
shop_name VARCHAR(200)
) ENGINE=InnoDB;3.2 type=ALL：全表扫描，必须消灭
-- 场景：无索引字段的WHERE条件
EXPLAIN SELECT * FROM product WHERE sales_count > 1000;输出：
+----+---...---+---------+------+---------------+------+---------+------+----------+-------------+
| id | ... | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---...---+---------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | product | ALL | NULL | NULL | NULL | NULL | 9876543 | Using where |
+----+---...---+---------+------+---------------+------+---------+------+----------+-------------+
-- 扫描近千万行，耗时：约8秒优化：
ALTER TABLE product ADD INDEX idx_sales (sales_count);
EXPLAIN SELECT * FROM product WHERE sales_count > 1000;优化后：
+----+---...---+---------+-------+---------------+-----------+---------+------+--------+------------------+
| id | ... | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---...---+---------+-------+---------------+-----------+---------+------+--------+------------------+
| 1 | SIMPLE | product | range | idx_sales | idx_sales | 4 | NULL | 123456 | Using index cond |
+----+---...---+---------+-------+---------------+-----------+---------+------+--------+------------------+
-- type=range，耗时：约0.12秒3.3 type=index：全索引扫描
-- 查询索引覆盖的列，但没有WHERE限制
EXPLAIN SELECT category_id FROM product ORDER BY category_id;输出：
+----+---...---+---------+-------+---------------+--------------+---------+------+----------+-------------+
| id | ... | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---...---+---------+-------+---------------+--------------+---------+------+----------+-------------+
| 1 | SIMPLE | product | index | NULL | idx_category | 4 | NULL | 9876543 | Using index |
+----+---...---+---------+-------+---------------+--------------+---------+------+----------+-------------+
-- type=index，全索引扫描，没有回表（Using index），但仍扫描所有行
-- 比ALL快（索引文件比数据文件小），但数据量大时仍然慢3.4 type=range：索引范围扫描
EXPLAIN SELECT * FROM product
WHERE price BETWEEN 100 AND 500;输出：
+----+---...---+---------+-------+---------------+-----------+---------+------+--------+------------------+
| id | ... | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---...---+---------+-------+---------------+-----------+---------+------+--------+------------------+
| 1 | SIMPLE | product | range | idx_price | idx_price | 6 | NULL | 345678 | Using index cond |
+----+---...---+---------+-------+---------------+-----------+---------+------+--------+------------------+
-- type=range，扫描约34万行
-- Using index cond: 索引条件下推（ICP），减少回表次数3.5 type=ref：非唯一索引等值查询
EXPLAIN SELECT * FROM product WHERE category_id = 10;输出：
+----+---...---+---------+------+---------------+--------------+---------+-------+------+-------+
| id | ... | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---...---+---------+------+---------------+--------------+---------+-------+------+-------+
| 1 | SIMPLE | product | ref | idx_category | idx_category | 4 | const | 4523 | NULL |
+----+---...---+---------+------+---------------+--------------+---------+-------+------+-------+
-- type=ref，命中非唯一索引，扫描约4523行
-- ref=const：和常量比较3.6 type=eq_ref：JOIN时走唯一索引
-- 被驱动表走主键或唯一索引
EXPLAIN SELECT p.name, c.name
FROM product p
JOIN category c ON p.category_id = c.id
WHERE p.status = 1 LIMIT 100;输出：
+----+---...---+---------+--------+--------------------+--------------+---------+---------------------------+------+-------------+
| id | ... | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---...---+---------+--------+--------------------+--------------+---------+---------------------------+------+-------------+
| 1 | SIMPLE | p | ref | idx_status,idx_cat | idx_status | 1 | const | 4.9M | NULL |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | test.p.category_id | 1 | NULL |
+----+---...---+---------+--------+--------------------+--------------+---------+---------------------------+------+-------------+
-- c表 type=eq_ref：每次用p.category_id查c的主键，精确命中1行
-- 这是JOIN中被驱动表的最优状态3.7 type=const：主键/唯一索引等值查询，最优
EXPLAIN SELECT * FROM product WHERE id = 12345;输出：
+----+---...---+---------+-------+---------------+---------+---------+-------+------+-------+
| id | ... | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---...---+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | product | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+---...---+---------+-------+---------------+---------+---------+-------+------+-------+
-- type=const，rows=1，MySQL在优化阶段就把这个表当作常量处理
-- 这是单表查询的最优状态3.8 key_len字段：验证联合索引利用情况
key_len是判断联合索引利用程度的关键。
-- 联合索引 idx_cover (category_id INT, status TINYINT, price DECIMAL(10,2))
ALTER TABLE product ADD INDEX idx_cover (category_id, status, price);
-- 只用category_id
EXPLAIN SELECT * FROM product WHERE category_id = 10;
-- key_len = 4（INT=4字节）
-- 用category_id + status
EXPLAIN SELECT * FROM product WHERE category_id = 10 AND status = 1;
-- key_len = 5（INT(4) + TINYINT(1)）
-- 用全部三个字段
EXPLAIN SELECT * FROM product WHERE category_id = 10 AND status = 1 AND price = 199.00;
-- key_len = 11（4 + 1 + 6）注：DECIMAL(10,2) = 6字节key_len的字节计算规则：
数据类型 | 字节数（NOT NULL）| 字节数（可为NULL）
INT | 4 | 5（+1字节NULL标志）
BIGINT | 8 | 9
TINYINT | 1 | 2
VARCHAR(n) | n*字符集字节数+2 | n*字符集字节数+3（变长字段额外2字节存长度）
CHAR(n) | n*字符集字节数 | n*字符集字节数+1
DATETIME | 5 | 6
DATE | 3 | 4
DECIMAL(M,D) | ceil((M-D)/9)*4+ceil(D/9)*4字节3.9 Extra字段详解
Extra字段包含最重要的优化信息：
-- Using index（覆盖索引）：最优，不需要回表
EXPLAIN SELECT category_id, status FROM product WHERE category_id = 10 AND status = 1;
-- 如果联合索引覆盖了查询字段，Extra=Using index
-- Using where：WHERE条件过滤，没用到覆盖索引
EXPLAIN SELECT * FROM product WHERE status = 1;
-- Using index condition（ICP，索引条件下推）
-- MySQL 5.6+，部分WHERE条件在存储引擎层过滤，减少回表
EXPLAIN SELECT * FROM product WHERE category_id > 5 AND status = 1;
-- Using filesort：ORDER BY没有走索引，需要额外排序
EXPLAIN SELECT * FROM product WHERE category_id = 10 ORDER BY price DESC;
-- 如果 idx_category 只有category_id，price没在索引中，就会filesort
-- Using temporary：需要临时表，常见于GROUP BY、UNION
EXPLAIN SELECT status, COUNT(*) FROM product GROUP BY status;
-- 如果status没有单独索引，会用临时表
-- NULL（Extra为空）：通常是最好的状态（直接走索引，无额外操作）
EXPLAIN SELECT * FROM product WHERE id = 1;3.10 Java代码：EXPLAIN结果自动分析工具
import java.sql.*;
import java.util.*;
/**
* SQL执行计划分析工具
* 自动识别性能问题并给出建议
*/
public class ExplainAnalyzer {
private final DataSource dataSource;
public ExplainAnalyzer(DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* 分析SQL的执行计划
* @param sql 待分析的SQL语句
* @return 分析报告
*/
public AnalysisReport analyze(String sql) throws SQLException {
String explainSql = "EXPLAIN " + sql;
AnalysisReport report = new AnalysisReport(sql);
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(explainSql)) {
while (rs.next()) {
ExplainRow row = new ExplainRow();
row.id = rs.getInt("id");
row.selectType = rs.getString("select_type");
row.table = rs.getString("table");
row.type = rs.getString("type");
row.possibleKeys = rs.getString("possible_keys");
row.key = rs.getString("key");
row.keyLen = rs.getString("key_len");
row.ref = rs.getString("ref");
row.rows = rs.getLong("rows");
row.filtered = rs.getDouble("filtered");
row.extra = rs.getString("Extra");
report.rows.add(row);
analyzeRow(row, report);
}
}
return report;
}
private void analyzeRow(ExplainRow row, AnalysisReport report) {
// 检查全表扫描
if ("ALL".equals(row.type)) {
report.addIssue(String.format(
"[严重] 表 %s 全表扫描，预估扫描 %d 行。建议：检查WHERE条件是否有对应索引",
row.table, row.rows
));
if (row.rows > 100000) {
report.addIssue(String.format(
"[严重] 表 %s 扫描行数超过10万，查询可能超时", row.table
));
}
}
// 检查全索引扫描
if ("index".equals(row.type) && row.rows > 500000) {
report.addIssue(String.format(
"[警告] 表 %s 全索引扫描，扫描 %d 行。考虑添加更精确的索引条件",
row.table, row.rows
));
}
// 检查没有用到索引
if (row.key == null || row.key.isEmpty() || "null".equalsIgnoreCase(row.key)) {
if (!"DERIVED".equals(row.selectType) && !"UNION RESULT".equals(row.selectType)) {
report.addIssue(String.format(
"[警告] 表 %s 未使用索引（possible_keys=%s）",
row.table, row.possibleKeys
));
}
}
// 检查Extra中的问题标志
if (row.extra != null) {
if (row.extra.contains("Using filesort")) {
report.addIssue(String.format(
"[警告] 表 %s 需要额外排序（Using filesort）。建议：ORDER BY字段加入索引",
row.table
));
}
if (row.extra.contains("Using temporary")) {
report.addIssue(String.format(
"[警告] 表 %s 使用了临时表（Using temporary）。建议：优化GROUP BY或DISTINCT",
row.table
));
}
if (row.extra.contains("Using join buffer")) {
report.addIssue(String.format(
"[警告] 表 %s JOIN时缺少索引，使用了join buffer。建议：JOIN条件字段加索引",
row.table
));
}
}
// 检查filtered值过低
if (row.filtered < 10.0 && row.rows > 10000) {
report.addIssue(String.format(
"[提示] 表 %s 索引过滤率仅 %.1f%%，实际有效行比例低，考虑改进索引设计",
row.table, row.filtered
));
}
}
// 报告数据类
public static class AnalysisReport {
public final String sql;
public final List<ExplainRow> rows = new ArrayList<>();
public final List<String> issues = new ArrayList<>();
AnalysisReport(String sql) { this.sql = sql; }
void addIssue(String issue) { issues.add(issue); }
public boolean hasIssues() { return !issues.isEmpty(); }
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("SQL: ").append(sql).append("\n\n");
sb.append("执行计划:\n");
sb.append(String.format("%-4s %-15s %-20s %-10s %-8s %-40s %-12s %-8s %-10s %s\n",
"id", "select_type", "table", "type", "key_len", "key", "rows", "filtered", "Extra", ""));
for (ExplainRow r : rows) {
sb.append(String.format("%-4d %-15s %-20s %-10s %-8s %-40s %-12d %-8.1f %s\n",
r.id, r.selectType, r.table, r.type,
r.keyLen == null ? "NULL" : r.keyLen,
r.key == null ? "NULL" : r.key,
r.rows, r.filtered,
r.extra == null ? "" : r.extra));
}
if (hasIssues()) {
sb.append("\n发现的问题：\n");
issues.forEach(i -> sb.append(" - ").append(i).append("\n"));
} else {
sb.append("\n执行计划看起来不错！\n");
}
return sb.toString();
}
}
public static class ExplainRow {
int id;
String selectType, table, type, possibleKeys, key, keyLen, ref, extra;
long rows;
double filtered;
}
}四、踩坑实录
坑1：EXPLAIN显示rows=1，实际执行慢10秒
-- 这条SQL EXPLAIN显示rows=1，但实际很慢
EXPLAIN SELECT * FROM product WHERE id IN (
SELECT product_id FROM order_item WHERE order_id = 12345
);EXPLAIN输出（MySQL 5.7之前）：
+----+--------------------+------------+--------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+---------------+---------+---------+------+----------+-------------+
| 1 | PRIMARY | product | ALL | NULL | NULL | NULL | NULL | 9876543 | Using where |
| 2 | DEPENDENT SUBQUERY | order_item | eq_ref | idx_order | idx_order| 8 | func | 1 | Using where |
+----+--------------------+------------+--------+---------------+---------+---------+------+----------+-------------+
-- DEPENDENT SUBQUERY：子查询依赖外部查询，外部每行都要执行一次子查询
-- product全表扫描了千万行，每行都对子查询执行一次！实际耗时：47秒解决方案（改写为JOIN）：
-- 优化后
EXPLAIN SELECT p.* FROM product p
JOIN order_item oi ON p.id = oi.product_id
WHERE oi.order_id = 12345;优化后输出：
+----+---...---+-------+--------+--------------+----------+---------+--------------------------+------+-------+
| id | ... | table | type | ... | key | key_len | ref | rows | Extra |
+----+---...---+-------+--------+--------------+----------+---------+--------------------------+------+-------+
| 1 | SIMPLE | oi | ref | idx_order | idx_order| 8 | const | 3 | NULL |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 8 | test.oi.product_id | 1 | NULL |
+----+---...---+-------+--------+--------------+----------+---------+--------------------------+------+-------+
-- 优化后：只扫描3行，耗时0.001秒！坑2：EXPLAIN FORMAT=JSON发现隐藏成本
标准EXPLAIN的rows是估算值，JSON格式可以看到更多细节：
EXPLAIN FORMAT=JSON
SELECT * FROM product WHERE category_id = 10 AND price BETWEEN 100 AND 500
ORDER BY sales_count DESC LIMIT 10\G输出（关键部分）：
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "4523.45" <- 优化器计算的总成本
},
"ordering_operation": {
"using_filesort": true, <- filesort！
"cost_info": {
"sort_cost": "4523.00" <- 排序成本占了绝大部分
},
"table": {
"table_name": "product",
"access_type": "range",
"rows_examined_per_scan": 34567,
"rows_produced_per_join": 3456,
"filtered": "10.00", <- 只有10%的行通过WHERE过滤
"index_condition": "(`price` between 100.00 and 500.00)",
"used_key_parts": ["category_id"],
"key": "idx_category"
}
}
}
}根据JSON输出发现：主要成本是filesort（排序），原因是sales_count不在索引中。
优化：
-- 创建覆盖ORDER BY的联合索引
ALTER TABLE product ADD INDEX idx_cat_price_sales (category_id, price, sales_count);
-- 重新EXPLAIN
EXPLAIN SELECT * FROM product
WHERE category_id = 10 AND price BETWEEN 100 AND 500
ORDER BY sales_count DESC LIMIT 10;
-- Extra: NULL（不再有filesort）坑3：EXPLAIN ANALYZE暴露优化器估算偏差
MySQL 8.0.18+支持EXPLAIN ANALYZE，显示实际执行数据：
EXPLAIN ANALYZE
SELECT * FROM product WHERE category_id IN (1,2,3,4,5) AND status = 1\G输出：
-> Filter: (product.status = 1) (cost=8234.56 rows=82345)
(actual time=0.234..1234.567 rows=45231 loops=1)
-> Index range scan on product using idx_category
over (category_id = 1) OR (category_id = 2) OR ...
(cost=7234.56 rows=823456)
(actual time=0.123..876.543 rows=823456 loops=1)关键发现：
- 优化器预估过了索引扫描后有82345行通过status过滤
- 实际上索引扫描了823456行，但只有45231行通过过滤（过滤率只有5.5%）
- 优化器的估算与实际差了10倍
解决方案：
-- 重新收集统计信息
ANALYZE TABLE product;
-- 或者创建更精确的联合索引
ALTER TABLE product ADD INDEX idx_cat_status (category_id, status);五、总结与延伸
EXPLAIN是SQL优化的核心工具，把重点梳理一遍：
type字段优先级记忆：const > eq_ref > ref > range > index > ALL，生产环境中出现index或ALL就应该关注。
key_len验算联合索引：用每个字段的字节数加起来，对比实际key_len，就能知道联合索引用了几个字段。
Extra是藏着金矿的地方：
Using index= 好消息（覆盖索引）Using filesort= 坏消息（需要优化排序）Using temporary= 坏消息（需要优化分组/去重）Using join buffer= 坏消息（JOIN缺少索引）
rows不是扫描行数，是估算值：真正的扫描行数要用EXPLAIN ANALYZE（8.0+）或慢查询日志中的Rows_examined字段确认。
下一篇我会讲MySQL事务隔离级别的底层实现，重点分析Next-Key Lock如何解决幻读，以及它在生产中制造的各种锁冲突。