如何查看MySQL的执行计划？

用户50687835307
428 阅读7分钟

MySQL 的执行计划（Execution Plan）是 SQL 性能调优的核心工具。它展示了 MySQL 优化器为执行一条 SQL 语句所选择的详细步骤和策略。读懂执行计划，就能定位查询瓶颈，从而进行有效优化。

一、如何获取执行计划？

使用 EXPLAIN 关键字，只需将其加在你的 SQL 语句之前即可。

-- 查看 SELECT 语句的执行计划
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

-- 查看 INSERT/UPDATE/DELETE 语句的执行计划（了解其如何查询数据）
EXPLAIN UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5;

-- MySQL 8.0+ 支持更详细的 ANALYZE 命令，会实际执行语句并统计耗时
EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'Smith';

二、执行计划详解：各字段含义

EXPLAIN 命令的输出包含以下核心字段。理解它们是读懂执行计划的关键。

字段含义说明
id查询序号标识 SELECT 子句的执行顺序。相同 id 从上到下执行；不同 id，id 值越大优先级越高。
select_type查询类型表示查询的复杂度（如简单查询、子查询、联合查询等）。
table访问的表显示这一步访问的是哪个表，有时是衍生表（如 <derived2>）。
partitions匹配的分区如果表定义了分区，这里显示命中了哪些分区。
type访问类型极其重要！表示如何查找数据，从好到坏：system > const > eq_ref > ref > range > index > ALL。
possible_keys可能用的索引优化器可能选用的索引列表，但不一定实际使用。
key实际用的索引重要！优化器实际选择的索引。为 NULL 表示未使用索引。
key_len索引长度使用的索引键的长度（字节数）。可推断使用了复合索引的哪些部分。
ref索引引用显示哪些列或常量被用来与 key 列指定的索引进行比较。
rows预估扫描行数重要！优化器预估为了找到所需记录需要扫描的行数。值越小越好。
filtered过滤百分比表示存储引擎返回的数据中，经过服务器层过滤后剩余的比例（百分比）。
Extra额外信息极其重要！包含优化器处理的额外信息，如 Using where, Using index 等。

三、核心字段深度解析

1. type（访问类型）—— 性能关键指标

这是判断查询效率最重要的字段，性能从最优到最差排列：

  • system：表只有一行数据（系统表），是 const 的特例。
  • const：通过主键（Primary Key）或唯一索引（Unique Index）一次就找到了唯一一行记录。性能最佳。 
    EXPLAIN SELECT * FROM employees WHERE id = 123; -- id 是主键
  • eq_ref：在联表查询（JOIN）时，使用主键或唯一索引作为关联条件，对于前表的每一行，后表只有一行匹配。仅次于 const。 
    EXPLAIN SELECT * FROM employees e
JOIN departments d ON e.dept_id = d.id; -- d.id 是 departments 的主键
  • ref：使用非唯一索引进行查找，返回所有匹配某个单独值的行。 
    EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith'; -- last_name 有普通索引
  • range：只检索给定范围的行，使用了索引范围扫描。常见于 BETWEEN、IN()、>、< 等操作。 
    EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
  • index：全索引扫描（Full Index Scan）。遍历整个索引树来查找数据，虽然比全表扫描快（因为索引文件通常比数据文件小），但依然不理想。 
    EXPLAIN SELECT id FROM employees; -- id 是主键，但只需查索引，无需回表
  • ALL：全表扫描（Full Table Scan）。性能最差，意味着没有可用的索引，必须扫描整张表。这是必须优化的重点。

2. Extra（额外信息）—— 优化的重要提示

此字段会显示很多详细信息，帮助您了解优化器是如何工作的：

  • Using index：覆盖索引（Covering Index）。查询的列全部包含在索引中，无需回表（回表：通过索引找到主键后，再回主键索引树查找完整记录）。性能极佳。 
    -- 假设索引是 (last_name, first_name)
EXPLAIN SELECT last_name, first_name FROM employees WHERE last_name = 'Smith';
  • Using where：服务器层（Server）在存储引擎返回行之后，又进行了过滤。表示索引可能没有完全覆盖查询条件。
  • Using index condition：索引下推（Index Condition Pushdown, ICP）。MySQL 5.6+ 特性，将部分 WHERE 条件下的过滤从 Server 层下推到存储引擎层执行，减少了回表的次数。
  • Using temporary：MySQL 需要创建一张临时表来处理查询。常见于 GROUP BY 和 ORDER BY 的子句涉及不同列时。通常需要优化。
  • Using filesort：MySQL 无法使用索引对结果进行排序，需要在内存或磁盘中进行额外的排序操作。ORDER BY 未用索引时会出现，需要考虑优化。
  • Select tables optimized away：使用某些聚合函数（如 MIN()/MAX()）访问索引时，优化器知道可以直接从索引中获取值，无需访问表。

3. key_len（索引长度）—— 判断索引使用程度

该值计算了使用索引字段的可能最大长度。对于复合索引，你可以通过它判断使用了索引的哪些部分。

  • 计算公式：key_len = {字符列长度} * {字符编码字节数} + {是否为NULL(1/0)} + {varchar类型额外字节(2)}
  • 示例：varchar(255)、utf8mb4（4字节）、允许 NULL。
    • key_len = 255 * 4 + 1 + 2 = 1023
  • 如果复合索引 (a, b, c)，你的查询只用了 a 和 b，那么 key_len 的值就是 a 和 b 的长度之和，说明 c 未使用。

四、实战：解读一个执行计划

假设我们有一个 orders 表，并在 product_id 上有一个索引。

EXPLAIN SELECT o.*, p.name
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.customer_id = 100 AND o.amount > 50
ORDER BY o.order_date DESC;

假设输出结果如下（我们聚焦关键字段）：

idselect_typetabletypepossible_keyskeykey_lenrowsExtra
1SIMPLEorefidx_customer,idx_productidx_customer550Using where; Using filesort
1SIMPLEpeq_refPRIMARYPRIMARY41NULL

解读：

  1. 执行顺序：id 都为 1，执行顺序是从上到下，先处理 o (orders) 表，再处理 p (products) 表。
  2. 对 o 表的查询：
    • type: ref：使用了非唯一索引（idx_customer）进行查找。
    • key: idx_customer：实际使用的索引是 idx_customer（可能建立在 customer_id 上）。
    • rows: 50：预估要扫描 50 行 orders 记录。
    • Extra: Using where; Using filesort：
      • Using where：存储引擎返回数据后，服务器层还需要用 amount > 50 条件进行过滤。说明 idx_customer 索引没有覆盖 amount 条件。
      • Using filesort：ORDER BY o.order_date DESC 无法利用索引排序，需要额外的排序操作。这是一个优化信号。
  3. 对 p 表的查询：
    • type: eq_ref：使用 products 表的主键 PRIMARY 进行精确查找，对于 o 表的每一行结果，p 表只有一行匹配。效率非常高。

优化建议：

  • 可以考虑建立一个复合索引 (customer_id, amount, order_date)。这个索引可以：
    1. 通过 customer_id 快速定位记录（替代 idx_customer）。
    2. 在 customer_id 相同的情况下，amount 已经是有序的，可以帮助过滤 amount > 50，可能减少 Using where。
    3. 在 customer_id 和 amount 相同的情况下，order_date 是有序的，可以直接消除 Using filesort，实现按日期排序。

总结

解读 MySQL 执行计划的步骤和重点：

  1. 先看 type：立刻判断查询的整体效率水平。目标是至少达到 range 级别，拒绝 ALL。
  2. 再看 key 和 key_len：确认是否使用了预期的索引，以及索引是否被充分利用。
  3. 关注 rows：预估扫描行数过高是潜在的性能瓶颈。
  4. 精读 Extra：这里藏着优化器给你的最重要提示（如是否覆盖索引、是否要排序、是否要临时表）。
  5. 综合分析：结合 SQL 语句和表结构，思考如何建立更合适的索引或改写查询来消除不良指标（如 Using filesort, Using temporary）。

通过不断实践和解读，你会逐渐养成看到 SQL 就能预估其执行效率，并通过执行计划验证和优化的能力。

avatar
文章
阅读
粉丝