mysql之EXPLAIN 详解
文章目录
EXPLAIN 详解
EXPLAIN 命令是 MySQL 性能分析和优化的核心工具，它能透视 SQL 查询的执行计划 (Execution Plan)，揭示 MySQL 如何执行查询、使用索引、访问方法以及预估资源消耗。
核心概念：执行计划 (Execution Plan) 的重要性
执行计划是 MySQL 优化器为 SQL 查询生成的一系列指令，描述了 MySQL 如何访问表、使用索引、连接表、排序和过滤数据。执行计划的好坏直接决定了查询的性能。EXPLAIN 将执行计划可视化地呈现出来，帮助我们分析查询性能瓶颈。
EXPLAIN 命令的基本用法
在 SQL 查询语句前加上 EXPLAIN 关键字即可：
EXPLAIN SELECT * FROM orders WHERE order_date >= '2023-01-01';
EXPLAIN SELECT o.order_id, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.city = 'New York';
EXPLAIN 返回一个结果集，包含多行和多列，每一行代表执行计划中的一个步骤（通常对应一个表的操作）。
EXPLAIN 输出字段详解
id (查询序列号)
-
含义: 标识
SELECT查询的序列号。值越大，执行优先级越高 (通常子查询先执行，序号小的先显示)。id相同，则执行顺序从上到下。id为NULL，表示结果集合并操作 (如UNION的结果)。 -
值类型: 整数或
NULL。 -
性能含义: 主要用于理解复杂查询 (包含子查询或
UNION) 的执行顺序。
select_type (查询类型)
-
含义: 表示
SELECT查询的类型。 -
常见值及其性能含义 (从好到坏):
-
SIMPLE: 简单查询，不包含子查询或UNION。 -
PRIMARY: 复杂查询中最外层的SELECT。 -
SUBQUERY:SELECT语句位于子查询中 (非FROM子句)。 -
DEPENDENT SUBQUERY: 依赖外部查询结果的子查询 (相关子查询)。 -
DERIVED:SELECT语句位于FROM子句的子查询, MySQL 会先执行子查询, 将结果放在临时派生表 (Derived Table) 中。 -
UNION:UNION语句的第二个或后面的SELECT。 -
UNION RESULT: 匿名临时表，用于存放UNION结果集。 -
DEPENDENT UNION/UNCACHEABLE UNION/UNCACHEABLE QUERY: 特殊类型的UNION或查询。 -
MATERIALIZED: 物化子查询
- 性能含义总结:
SIMPLE和PRIMARY通常理想。SUBQUERY,DEPENDENT SUBQUERY,DERIVED,UNION等表示查询较复杂。避免DEPENDENT UNION/UNCACHEABLE UNION/UNCACHEABLE QUERY等类型。
table (表名)
-
含义: 表示对应行记录关联的表名、别名、派生表名 (例如
<derivedN>)、UNION结果集名 (例如<unionM,N>)。 -
值类型: 表名、别名、派生表名、
UNION结果集名。 -
性能含义: 指明当前步骤操作哪个表或临时表。
<derivedN>表示使用 id 为 N 的查询生成的派生表。
partitions (分区)
-
含义: 如果表是分区表，显示查询将访问的分区。非分区表为
NULL。 -
值类型: 分区名列表或
NULL。 -
性能含义: 确认查询是否使用了分区裁剪 (Partition Pruning), 只访问必要的分区。
type (访问类型 / 访问方法 - Access Method)
这是 EXPLAIN 结果中最关键的列之一，直接反映 MySQL 如何查找表中的行。
-
含义: 表示 MySQL 找到所需行的方式。值越靠前 (列表上方)，效率越高。
-
常见值及其性能含义 (从最优到最差):
-
system: 表只有一行记录 (系统表或常量表)。 -
const: 使用主键或唯一二级索引与常量值进行等值匹配。 -
eq_ref: 连接查询中，被驱动表通过主键或唯一二级索引等值匹配访问, 且保证对于驱动表的每一行，被驱动表都最多只返回一条匹配记录。 -
ref: 使用非唯一二级索引进行等值匹配。 -
fulltext: 使用全文索引进行MATCH AGAINST查询。 -
ref_or_null: 类似于ref，但多了对NULL值的处理。 -
index_merge: 索引合并。可以使用多个索引。 -
unique_subquery/index_subquery: 子查询优化类型,unique_subquery用于IN (SELECT ...)子查询, 子查询返回唯一值,index_subquery类似于unique_subquery但子查询使用非唯一索引。 -
range: 索引范围扫描 (例如BETWEEN,>,<,IN,OR等)。 -
index: 索引全扫描。扫描整个索引树，但只访问索引列数据 (覆盖索引)。 -
ALL: 全表扫描 (Full Table Scan)。
- 性能含义总结:
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>range>index>ALL。目标是将type优化到ref或更好级别，避免ALL和index。
possible_keys (可能用到的索引)
-
含义: 列出 MySQL 可能使用到的索引。注意：
possible_keys列出的是 “可能” 使用的索引，实际不一定会被使用。 -
值类型: 索引名列表或
NULL。 -
性能含义:
possible_keys为空, 意味着当前查询没有任何索引可以使用。possible_keys不为空，只是提供索引选择的 “候选集”。
key (实际用到的索引)
-
含义: 显示 MySQL 实际选择使用的索引。如果没选择索引, 则为
NULL。 -
值类型: 索引名或
NULL。 -
性能含义:
key列是判断索引是否生效的关键。-
key为NULL: 表示没有使用索引, 可能是全表扫描或索引全扫描。 -
key不为NULL: 表示使用了索引。
-
key_len (索引长度)
-
含义: 表示 MySQL 在索引里使用的字节数。可以估算使用了索引的哪些列, 以及索引的使用程度。
-
值类型: 整数 (字节数)。
-
计算规则:
key_len计算索引字段的长度，与字段类型、字符集、是否允许NULL值等有关。-
字符串类型:
varchar(10)UTF8 字符集，允许NULL，key_len可能为 33 (10 * 3 + 1 字节NULL标志 + 2 字节varchar长度)。 -
整数类型:
int类型,key_len通常为 4 字节。 -
日期类型:
date类型,key_len通常为 3 字节。 -
复合索引:
key_len会累加所有被用到的索引列的长度。
-
-
性能含义:
-
key_len越大，表示索引使用的越充分 (复合索引情况下)。 -
key_len越小，表示索引使用程度越低 (或只使用了索引的前缀部分)。
-
ref (索引列的参照)
-
含义: 指出索引列的参照对象，即哪些列或常量值被用于索引查找。
-
值类型:
-
const: 常量值。 -
表名.列名: 例如table1.col1。 -
func: 函数返回值。 -
NULL: 没有明确的参照对象。
-
-
性能含义: 帮助理解索引如何被使用，以及索引查找的条件来源。
rows (预估扫描行数)
这是 EXPLAIN 结果中非常重要的列，直接反映 MySQL 优化器预估的需要扫描的行数。
-
含义: MySQL 优化器 预估 需要扫描的行数。注意：
rows是 预估值，并非实际扫描行数。 -
值类型: 整数 (行数)。
-
性能含义:
-
rows值越小，查询效率可能越高。 -
rows值越大，查询效率可能越低，甚至可能有性能问题。
-
filtered (按表条件过滤的百分比)
-
含义: 表示存储引擎返回的数据，在 server 层进行过滤后，剩余的比例百分比。
-
值类型: 百分比 (0.00 - 100.00)。
-
性能含义:
-
filtered值越高 (接近 100%)，效率可能越高。 -
filtered值越低 (接近 0%)，效率可能降低。
-
Extra (额外信息)
这是 EXPLAIN 结果中非常重要的列，提供了关于 MySQL 执行计划的额外信息。
-
含义: 包含 MySQL 执行查询的额外提示信息。
-
常见值及其性能含义 (部分重要值):
-
Using index(索引覆盖): 非常好！ 查询信息直接从索引树中获取, 不需要回表。 -
Using where: 在存储引擎层返回的数据基础上，再在 server 层进行WHERE条件过滤。 -
Using temporary(使用临时表): 通常意味着性能损耗！ MySQL 使用临时表辅助计算 (如GROUP BY、ORDER BY、UNION DISTINCT等)。 -
Using filesort(文件排序): 通常意味着性能损耗！ MySQL 对结果集进行文件排序，无法利用索引排序。 -
Using join buffer(使用连接缓存): 连接查询中, MySQL 使用 Join Buffer 加速连接 (Block Nested Loop Join 算法)。 -
Impossible WHERE:WHERE子句条件永远为假。 -
Select tables optimized away: 优化器能直接从索引中获取结果。 -
Distinct: MySQL 在查询优化阶段就进行DISTINCT优化。 -
No tables used: 查询语句没有FROM子句。 -
Using index condition：索引条件下推。 -
Not exists: 左（外）连接中，被驱动表的某个列等于 NULL 且该列不允许存储 NULL 值。 -
Using intersect(...)、Using union(...)和Using sort_union(...): 使用索引合并。 -
Zero limit: LIMIT 子句的参数为 0。 -
Start temporary, End temporary:IN 子查询转换为 semi-join 时，使用 DuplicateWeedout 执行策略。 -
LooseScan: IN 子查询转换为 semi-join 时, 使用 LooseScan 执行策略。 -
FirstMatch(tbl_name): IN 子查询转换为 semi-join 时，使用 FirstMatch 执行策略。
- 性能含义总结: 重点关注
Using index(好),Using temporary(坏),Using filesort(坏),Using join buffer(需评估) 等值。
EXPLAIN 的输出格式
-
EXPLAIN FORMAT=TRADITIONAL(默认): 表格格式。 -
EXPLAIN FORMAT=JSON: JSON 格式，更结构化、更详细。 -
EXPLAIN FORMAT=TREE(MySQL 8.0+): 树形格式，更直观。
EXPLAIN 的局限性与注意事项
-
预估值而非实际值:
EXPLAIN输出的rows等列是 预估值。 -
只能解释
**SELECT**,**DELETE**,**INSERT**,**UPDATE**语句: 不能解释CREATE TABLE,ALTER TABLE等 DDL 语句。 -
无法解释存储过程、函数、触发器等: 只能解释单个 SQL 查询语句。
-
优化器可能会调整执行计划:
EXPLAIN展示的是分析阶段的计划, 实际执行时可能调整。 -
EXPLAIN EXTENDED (已过时): MySQL 5.6 之后已过时。
EXPLAIN 优化实践与案例分析 (电商网站场景)
案例 1: 订单查询性能慢 (全表扫描)
EXPLAIN SELECT * FROM orders WHERE order_date >= '2022-01-01';
EXPLAIN 结果 (部分):
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | orders | ALL | order_date_idx | NULL | NULL | NULL | 1000000 | 10.00 | Using where |
问题分析:
-
type: ALL: 全表扫描。 -
key: NULL: 没有使用索引。
优化方案:
-
强制使用索引:
FORCE INDEX (order_date_idx)。 -
检查索引定义: 确认索引是否正确。
-
更新统计信息:
ANALYZE TABLE orders;。
优化后的 SQL:
EXPLAIN SELECT * FROM orders FORCE INDEX (order_date_idx) WHERE order_date >= '2022-01-01';
优化后的 EXPLAIN 结果 (部分):
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | orders | range | order_date_idx | order_date_idx | 4 | NULL | 10000 | 100.00 | Using index condition |
优化效果:
-
type从ALL提升到range: 索引范围扫描。 -
rows大幅减少。
案例 2: 商品搜索性能瓶颈 (文件排序)
EXPLAIN SELECT product_name, price FROM products WHERE category_id = 5 ORDER BY price DESC;
EXPLAIN 结果 (部分):
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | products | ref | category_id_idx, price_idx | category_id_idx | 4 | const | 100 | 100.00 | Using where; Using filesort |
问题分析:
Extra: Using filesort: 文件排序。
优化方案:
- 创建复合索引 (category_id, price DESC):
CREATE INDEX category_price_idx ON products (category_id, price DESC);
优化后的 EXPLAIN 结果 (部分):
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | products | ref | category_id_idx, price_idx, category_price_idx | category_price_idx | 4 | const | 100 | 100.00 | Using where; Backward index scan |
优化效果:
-
Using filesort消失。 -
Backward index scan: 使用倒序索引扫描。
总结
EXPLAIN 命令是 MySQL 查询优化的 “瑞士军刀”。熟练掌握 EXPLAIN 的输出字段含义，结合案例分析，能帮助我们深入理解 MySQL 执行计划，快速定位性能瓶颈，找到优化方案，构建高性能的 MySQL 应用。
参考：https://relph1119.github.io/mysql-learning-notes/#/mysql ,推荐理解本文之后去看原书，原书有一定深度需前后贯穿仔细理解
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
30
0- 0
已为社区贡献1条内容
所有评论(0)