小菜鸟

java菜鸟号正在起航

0%

执行计划

发表于 更新于 分类于 阅读次数： 80 Valine： 0
本文字数： 4.7k 阅读时长 ≈ 4 分钟

MySQL 执行计划详解：读懂 Explain，优化 SQL 不求人

执行计划（Explain）是 MySQL 优化的 “导航图”，它能模拟优化器执行 SQL 的过程，展示 MySQL 如何解析和执行你的查询。掌握执行计划的分析方法，是写出高效 SQL 的前提。本文将系统拆解执行计划的每个字段，教你如何通过 Explain 定位性能瓶颈。

执行计划的基本使用

通过EXPLAIN关键字可以生成执行计划，语法简单：

1
2
3
4
5
6
-- 基础用法
EXPLAIN SELECT * FROM user WHERE age > 18;

-- 结合索引未使用的原因（配合show warnings）
EXPLAIN SELECT * FROM user WHERE age > 18;
SHOW WARNINGS; -- 显示优化器对SQL的改写和分析

执行后会生成一张包含 12 个字段的表格，每个字段都隐藏着查询的关键信息：

1
2
3
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

执行计划字段详解

1. id：查询执行顺序标识

表示 SELECT 子句或操作表的序号，决定执行顺序。

  • id 相同：执行顺序由上至下（按 table 字段顺序）。
    例：多表连接查询中，id 相同表示按表的出现顺序依次访问。
  • id 不同：id 越大优先级越高，先执行（子查询的 id 通常大于外层查询）。
    例：子查询的 id 为 2，外层查询 id 为 1，则先执行子查询。
  • id 混合（有相同也有不同）：先执行 id 大的，id 相同的按顺序执行。

2. select_type：查询类型

区分普通查询、子查询、联合查询等，核心类型如下：

类型 含义
SIMPLE 简单查询（无 subquery 或 UNION）
PRIMARY 最外层查询（包含子查询时，外层查询标记为 PRIMARY）
SUBQUERY SELECT 或 WHERE 中的子查询（非 FROM 子句中）
DERIVED FROM 子句中的子查询（结果会放入临时表，标记为）
UNION UNION 中第二个及以后的 SELECT（第一个为 PRIMARY）
DEPENDENT UNION UNION 中第二个及以后的 SELECT，依赖外部查询的结果
UNION RESULT UNION 的结果集（临时表，标记为 ）

例子：
EXPLAIN SELECT * FROM (SELECT id FROM user WHERE age>18) AS t1 UNION SELECT id FROM order;

  • 子查询SELECT id FROM user为 DERIVED（id=2）。
  • 第二个 SELECT（UNION 后）为 UNION（id=3）。
  • 最终合并结果为 UNION RESULT（无 id）。

3. table：操作的表

显示当前行操作的表名，特殊情况：

  • <derived N>：表示临时表，N 对应 id（如 表示 id=2 的查询生成的临时表）。
  • <union M,N>：表示 UNION 的结果集，M 和 N 对应参与合并的查询 id。

4. partitions：分区信息

显示查询匹配的分区，非分区表显示NULL。对分区表优化有参考价值（如是否命中预期分区）。

5. type：访问类型（核心字段）

表示 MySQL 访问数据的方式，直接反映查询效率，从优到差排序如下：
system > const > eq_ref > ref > range > index > ALL

关键类型解析（需重点关注）：
  • system：表中只有一行数据（如 MySQL 的系统表），是 const 的特例，几乎不会出现。
  • const：通过主键或唯一索引等值查询（WHERE id=1），只匹配 1 行，效率极高。
    例：EXPLAIN SELECT * FROM user WHERE id=100;（id 为主键）
  • eq_ref：连接查询中，被连接表使用主键或唯一索引作为连接条件，每行只匹配 1 条记录。
    例：EXPLAIN SELECT * FROM order o JOIN user u ON o.user_id = u.id;（u.id 为主键）
  • ref：非唯一索引的等值查询，或连接查询中使用非唯一索引作为连接条件，可能匹配多行。
    例：EXPLAIN SELECT * FROM user WHERE age=20;（age 为普通索引）
  • range：索引范围查询（BETWEEN、IN、>、<等），只扫描索引的某一范围。
    例：EXPLAIN SELECT * FROM user WHERE age BETWEEN 18 AND 30;
  • index：全索引扫描（遍历整个索引树），比 ALL 好（索引文件通常比数据文件小）。
    例：EXPLAIN SELECT age FROM user;（age 为索引，无需访问数据行）
  • ALL：全表扫描（遍历整个数据文件），效率极低，需优化。

优化目标：至少达到range级别，最好能到ref或const。

6. possible_keys vs key：索引候选与实际使用

  • possible_keys：查询可能用到的索引（基于 WHERE 条件和连接条件推测）。
  • key：查询实际使用的索引（可能为 NULL，即未用索引）。

分析技巧：

  • 若 possible_keys 有值但 key 为 NULL：说明索引失效（可能因函数操作、类型转换等）。
  • 若 key 使用的索引不在 possible_keys 中：可能是优化器选择了更优的隐藏索引。

7. key_len：索引使用的字节数

表示索引中被使用部分的长度（字节），越短越好（在不影响精度的情况下）。

计算规则（关键）：
  • 字符串：
    • char(n)：n * 字符集长度（utf8mb4=4，utf8=3，gbk=2），允许 NULL 加 1 字节。
    • varchar(n)：n * 字符集长度 + 2（变长标识），允许 NULL 加 1 字节。
      例：varchar(10) NOT NULL（utf8mb4）：10*4 + 2 = 42字节。
  • 数值类型：
    • tinyint (1)、smallint (2)、int (4)、bigint (8)，允许 NULL 加 1 字节。
  • 时间类型：
    • date (3)、timestamp (4)、datetime (8)，允许 NULL 加 1 字节。

用途：判断复合索引是否被充分利用（如 key_len=10，可能只用到了复合索引的前 10 字节）。

8. ref：索引匹配的条件

显示在 key 字段的索引中，表查找值所用到的列或常量。
例：

  • const：使用常量匹配（如WHERE id=100）。
  • user.age：使用其他表的列匹配（如连接查询ON a.id = b.user_id）。

9. rows：估算扫描行数

MySQL 估算找到目标数据需要扫描的行数（非精确值），值越小越好。
结合filtered字段（符合条件的百分比），可估算实际处理行数：rows * filtered%。

10. filtered：符合条件的行百分比

表示符合查询条件的行数占扫描行数的百分比（100 为全部符合）。
例：rows=1000，filtered=10% → 实际符合条件的约 100 行。

11. Extra：额外信息（优化关键）

包含查询的细节信息，是 SQL 优化的重要依据，常见值如下：

Extra 值 含义与优化建议
Using filesort MySQL 无法用索引排序，需额外排序（文件排序或内存排序）。 优化：添加排序字段的索引。
Using temporary 使用临时表存储中间结果（常见于 GROUP BY 或 DISTINCT）。 优化：用索引覆盖排序 / 分组字段。
Using index 索引覆盖查询（查询字段均为索引列，无需访问数据行）。 状态：优秀，无需优化。
Using where 需通过 WHERE 条件过滤数据（索引未完全覆盖查询条件）。
Using join buffer 连接查询未使用索引，需用缓冲区存储中间结果。 优化：为连接字段添加索引。
Using index condition 查询字段未完全被索引覆盖，WHERE 条件含范围查询（索引下推优化）。
Impossible WHERE WHERE 条件恒为 false（如WHERE 1=0），无需扫描数据。

重点关注：Using filesort和Using temporary是性能杀手，必须优化；Using index是理想状态。

  • filesort可以使用的内存排序空间大小为sort_buffer_size，默认2M，当不够用时，会使用临时文件来存储，使用临时文件存储会进行文件的合并

    show GLOBAL status like 'Sort_merge_passes'查看merge次数，如果次数过大，建议增大sort_buffer_size

    explain不会显示到底使用了哪种排序

  • 临时表可能是在内存/磁盘上创建的，内存临时表最大容量为tmp_table_size和max_heap_table_size的最小值，大于该值时会使用磁盘临时表

    show GLOBAL status like '%tmp%'查看

    1
    2
    3
    4
    5
    6
    7
    +-------------------------+---------+
    | Variable_name           | Value   |
    +-------------------------+---------+
    | Created_tmp_disk_tables | 4025    |
    | Created_tmp_files       | 6366    |
    | Created_tmp_tables      | 2096332 |
    +-------------------------+---------+

    Created_tmp_disk_tables表示创建的磁盘临时表的总数

    Created_tmp_tables表示创建临时表的总数

    如果在磁盘上创建的临时表次数过多，可增大tmp_table_size和max_heap_table_size参数配置

执行计划分析步骤

  1. 看 type：是否达到 range 及以上，若为 ALL（全表扫描）或 index（全索引扫描），优先优化索引。
  2. 看 key：是否使用了预期的索引，若未使用，结合 possible_keys 和 show warnings 分析原因（如索引失效）。
  3. 看 Extra：是否有 Using filesort、Using temporary，若有，优化排序 / 分组逻辑或添加合适索引。
  4. 看 rows 和 filtered：估算扫描行数是否合理，rows 过大说明索引设计不佳。

实战案例：从执行计划到 SQL 优化

问题 SQL：
SELECT name, age FROM user WHERE age > 18 ORDER BY register_time;

执行计划关键字段：

  • type: ALL（全表扫描）
  • key: NULL（未用索引）
  • Extra: Using where; Using filesort

优化步骤：

  1. 为age添加索引（解决全表扫描）：CREATE INDEX idx_age ON user(age);
  2. 优化排序：将索引改为复合索引，覆盖排序字段：CREATE INDEX idx_age_regtime ON user(age, register_time);

优化后执行计划：

  • type: range（范围扫描）
  • key: idx_age_regtime（使用复合索引）
  • Extra: Using index condition（索引覆盖部分查询）
相关文章

欢迎关注我的其它发布渠道

表情 | 预览
快来做第一个评论的人吧~
Powered By Valine
v1.3.10