数据库

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 结果 (部分):

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEordersALLorder_date_idxNULLNULLNULL100000010.00Using where

问题分析:

  • type: ALL: 全表扫描。

  • key: NULL: 没有使用索引。

优化方案:

  1. 强制使用索引: FORCE INDEX (order_date_idx)。

  2. 检查索引定义: 确认索引是否正确。

  3. 更新统计信息: ANALYZE TABLE orders;。

优化后的 SQL:

EXPLAIN SELECT * FROM orders FORCE INDEX (order_date_idx) WHERE order_date >= '2022-01-01';

优化后的 EXPLAIN 结果 (部分):

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEordersrangeorder_date_idxorder_date_idx4NULL10000100.00Using index condition

优化效果:

  • type 从 ALL 提升到 range: 索引范围扫描。

  • rows 大幅减少。

案例 2: 商品搜索性能瓶颈 (文件排序)

EXPLAIN SELECT product_name, price FROM products WHERE category_id = 5 ORDER BY price DESC;

EXPLAIN 结果 (部分):

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEproductsrefcategory_id_idx, price_idxcategory_id_idx4const100100.00Using where; Using filesort

问题分析:

  • Extra: Using filesort: 文件排序。

优化方案:

  • 创建复合索引 (category_id, price DESC):
CREATE INDEX category_price_idx ON products (category_id, price DESC);

优化后的 EXPLAIN 结果 (部分):

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEproductsrefcategory_id_idx, price_idx, category_price_idxcategory_price_idx4const100100.00Using where; Backward index scan

优化效果:

  • Using filesort 消失。

  • Backward index scan: 使用倒序索引扫描。

总结

EXPLAIN 命令是 MySQL 查询优化的 “瑞士军刀”。熟练掌握 EXPLAIN 的输出字段含义，结合案例分析，能帮助我们深入理解 MySQL 执行计划，快速定位性能瓶颈，找到优化方案，构建高性能的 MySQL 应用。