当前位置：首页 > news >正文

MySQL EXPLAIN执行计划详解：从原理到实战优化慢查询

news 2026/8/17 11:51:34

1. 从一次线上慢查询说起：为什么我们需要Explain

那天下午，监控系统突然告警，一个核心业务接口的响应时间从平时的几十毫秒飙升到了十几秒。整个团队立刻紧张起来，数据库的CPU使用率也冲到了90%以上。登录到数据库服务器，用SHOW PROCESSLIST一看，果然有几个状态是“Sending data”的查询已经执行了很长时间。问题大概率出在SQL上。

我们立刻找到了那条“罪魁祸首”的SQL，是一个多表关联查询，看起来逻辑并不复杂。但为什么平时跑得好好的，今天突然就“挂”了呢？是数据量变大了？还是索引失效了？抑或是MySQL的优化器今天“心情不好”，选错了执行路径？

在这种时候，靠猜是没用的。你需要一个“透视镜”，能够深入数据库引擎内部，看清楚这条SQL到底打算怎么执行，每一步的成本是多少，瓶颈在哪里。这个“透视镜”，就是EXPLAIN命令。它不是去实际执行你的SQL（EXPLAIN ANALYZE除外），而是让数据库的查询优化器告诉你：“如果我来执行这条语句，我打算这么做。” 通过解读这份“作战计划”，我们就能精准定位问题，是索引没命中、是全表扫描、还是关联顺序出了问题。

对于后端开发、DBA甚至是对性能有要求的数据分析师来说，EXPLAIN都是必须掌握的技能。它不局限于MySQL，在 PostgreSQL、SQL Server（称为执行计划）、Oracle 等主流数据库中都有类似功能，只是输出格式和细节略有不同。理解了一份执行计划，就等于拿到了数据库性能调优的“地图”。

2. 解读执行计划输出：读懂每一列的含义

当我们执行EXPLAIN SELECT * FROM users WHERE age > 30;后，会得到一张表格。这张表的每一列都承载着关键信息。我们以MySQL 8.0为例，逐列拆解其含义。这是读懂计划的基础，务必吃透。

2.1 核心列：id、select_type、table

id(查询序列号)这一列是一个编号，表示SELECT语句的执行顺序。规则很简单：

id相同：执行顺序从上到下。通常出现在包含子查询或UNION的语句中，表示这些部分是同一层级，按出现的顺序执行。
id不同：如果是子查询，id序号会递增。id值越大，优先级越高，越先被执行。这很好理解，内层子查询需要先算出结果，才能供外层查询使用。
id为NULL：最后执行。通常表示这是一个由优化器生成的临时结果集，例如UNION操作的去重步骤。

select_type(查询类型)这列说明了查询的类型，是简单查询还是复杂的子查询。常见的有：

SIMPLE：最简单的SELECT，不包含子查询或UNION。这是我们最希望看到的。
PRIMARY：查询中若包含任何复杂的子部分，最外层的SELECT被标记为PRIMARY。
SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY。
DERIVED：在FROM列表中包含的子查询（即派生表），MySQL会递归执行并将结果放入一个临时表中。DERIVED表无法建立索引，如果数据量大，往往是性能瓶颈。
UNION：UNION中的第二个或后面的SELECT语句。
UNION RESULT：从UNION的匿名临时表检索结果的SELECT。

注意：在MySQL 5.7及之前，DERIVED查询会物化到磁盘临时表，性能损耗大。MySQL 8.0引入了对派生表的“合并”优化，很多情况下可以避免物化，执行计划中可能不再显示DERIVED，而是将子查询合并到外层查询中，这是一个重要的性能改进。

table(访问的表)显示这一步访问的是哪张表。有时不是表名，而是：

<derivedN>：其中N是id值，指向一个派生（临时）表。
<unionM,N>：指向id为M和N的查询进行UNION操作后产生的临时表。

2.2 关键性能指标列：type、possible_keys、key、key_len

type(访问类型)这是衡量查询性能最关键的一列。它表示MySQL决定如何查找表中的行。从最优到最差，常见的类型有：

system > const > eq_ref > ref > range > index > ALL。我们至少要保证查询达到range级别，最好能达到ref。

system：表只有一行记录（等于系统表），是const类型的特例。
const：通过主键或唯一索引一次就找到了，最多返回一条记录。因为只读一次，所以速度极快。SELECT * FROM users WHERE id = 1;
eq_ref：通常出现在多表关联查询中，对于前表的每一行，在后表中只匹配到唯一一行。这通常是通过后表的主键或唯一非空索引关联实现的。这是除system和const之外最好的关联类型。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。比如在age列上有一个普通索引，查询WHERE age = 30，可能会用到ref。
range：只检索给定范围的行，使用一个索引来选择行。关键是在WHERE子句中出现了BETWEEN、<、>、IN()等范围查询操作符。这比全索引扫描 (index) 好，因为它只扫描索引树的一部分。
index：全索引扫描（Full Index Scan）。index与ALL的区别是index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。但如果需要回表，且索引覆盖不全，数据量大时依然很慢。
ALL：全表扫描（Full Table Scan）。这是最坏的情况，意味着MySQL必须扫描整张表来找到匹配的行。对于大表，这通常是灾难性的，必须通过增加索引来避免。

possible_keys与key

possible_keys：查询可能使用到的索引。这一列显示的是理论上可以被优化器选用的索引。如果为NULL，则没有相关的索引。
key：查询实际使用到的索引。如果为NULL，则表示没有使用索引。这一列是事实，possible_keys是理论。优化器可能出于成本考虑，选择了一个不在possible_keys中的索引，或者决定全表扫描。

实操心得：如果key列为NULL，而possible_keys有值，这往往是一个危险信号。它可能意味着：1) 你的索引建得不对（比如在选择性极差的列上建索引）；2) 查询写法导致索引失效（比如对索引列做了函数计算WHERE YEAR(create_time)=2023）；3) 表数据量很小，优化器认为全表扫描比走索引回表更快。

key_len(使用的索引长度)表示索引中使用的字节数。通过这个值可以算出具体使用了索引的哪些部分（复合索引的前缀）。计算规则取决于列的数据类型和字符集。

例如，一个INTNOT NULL 列，key_len是4。
一个VARCHAR(255)UTF8字段，如果字段可为NULL，key_len= 255 * 3 + 1（长度前缀）+ 1（NULL标志位）= 767。如果实际查询只用到了前10个字符，且索引是前缀索引，key_len可能更小。作用：判断是否充分使用了复合索引。如果key_len小于索引定义的长度，说明只使用了索引的左前缀部分。

2.3 扫描与过滤列：rows、filtered、Extra

rows(预估扫描行数)MySQL优化器根据统计信息，预估为了找到所需的行，需要读取多少行数据。这是一个预估值，不是精确值，但能很好地反映查询的成本。对于关联查询，这个值是通过将前一个表的rows值乘以后一个表的filtered百分比得到的嵌套循环次数。

filtered(过滤百分比)这是一个百分比值，表示存储引擎返回的数据在Server层过滤后，剩下多少满足查询条件。rows * filtered / 100可以估算出将与下一张表关联的行数。这个值越大越好。如果很低（比如10%），说明索引过滤性不好，或者查询条件写得太宽泛。

Extra(额外信息)这一列包含不适合在其他列显示但非常重要的额外信息。很多性能问题在这里露出马脚。

Using index：表示使用了覆盖索引，即查询的列全部包含在使用的索引中，无需回表查询数据行。这是性能最好的情况之一。
Using where：表示在存储引擎返回行之后，Server层又进行了过滤。如果type是ALL或index，出现这个提示通常不是好兆头，说明大量数据被从磁盘读到内存后又被丢弃。
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序 (ORDER BY) 和分组 (GROUP BY)，且没有用到索引。这通常涉及磁盘IO，性能很差。
Using filesort：表示MySQL无法利用索引完成排序，需要额外的排序步骤。这个排序可能在内存中完成，也可能需要磁盘文件，取决于sort_buffer_size的设置和待排序数据的大小。
Using join buffer (Block Nested Loop)：表示关联查询时，被驱动表没有使用索引，需要用到连接缓冲区来加速。这也是一个需要关注的性能点。
Impossible WHERE：WHERE子句的值总是false，导致查不到任何数据，如WHERE 1=0。

3. 实战案例拆解：从执行计划定位性能瓶颈

光说不练假把式。我们结合几个具体的慢查询案例，看看如何运用EXPLAIN进行诊断和优化。

3.1 案例一：缺失索引导致的全表扫描

假设我们有一张订单表orders，约有1000万行数据。业务反馈“查询用户最近订单”的接口变慢。

原始SQL：

SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 10;

执行计划 (EXPLAIN):

id	select_type	table	type	possible_keys	key	key_len	rows	filtered	Extra
1	SIMPLE	orders	ALL	NULL	NULL	NULL	9876543	10.00	Using where; Using filesort

解读与诊断：

type: ALL：最严重的警报！这意味着MySQL对orders表进行了全表扫描，读取了近1000万行。
key: NULL：没有使用任何索引。
Extra: Using where; Using filesort：在扫描出的1000万行中，用WHERE条件过滤出user_id=12345的行（过滤性filtered仅10%？这里10%是预估，实际可能一个用户没那么多订单）。然后，对过滤出的结果进行文件排序 (filesort) 来满足ORDER BY。

根因：WHERE user_id = 12345这个条件没有索引可用。ORDER BY create_time DESC加剧了问题，因为没有索引，排序只能靠临时文件。

优化方案：为user_id创建索引。

CREATE INDEX idx_user_id ON orders(user_id);

再次查看执行计划：

id	select_type	table	type	possible_keys	key	key_len	rows	filtered	Extra
1	SIMPLE	orders	ref	idx_user_id	idx_user_id	4	15	100.00	Using index condition; Using filesort

优化后分析：

type: ref：访问类型提升到了索引等值查找，优秀。
rows: 15：预估扫描行数从1000万骤降到15行，天壤之别。
但是，Extra: Using filesort依然存在。因为索引只帮助定位了user_id，排序create_time仍需额外步骤。

进阶优化：创建复合索引(user_id, create_time)。这样索引可以同时满足查询条件和排序需求，实现“索引覆盖排序”。

CREATE INDEX idx_user_create ON orders(user_id, create_time DESC); -- MySQL 8.0支持降序索引

优化后的执行计划，Extra列很可能变为Using index，实现了覆盖索引，连回表和文件排序都省了，性能达到极致。

3.2 案例二：索引失效与隐式转换

有一张用户表users，其中phone字段是VARCHAR(20)，并且建立了索引idx_phone。查询时发现速度很慢。

原始SQL：

SELECT * FROM users WHERE phone = 13800138000; -- 注意，phone是字符串类型，但条件写成了数字

执行计划：

id	select_type	table	type	possible_keys	key	key_len	rows	filtered	Extra
1	SIMPLE	users	ALL	idx_phone	NULL	NULL	100000	10.00	Using where

解读与诊断：

possible_keys: idx_phone：优化器知道这个索引存在。
key: NULL：但最终没有使用它！type是ALL，全表扫描。
rows值很大。

根因：隐式类型转换。phone列是VARCHAR，而查询条件13800138000是一个数字。MySQL在执行比较时，会将字符串类型的phone列值隐式转换为数字，相当于对索引列做了函数操作 (CAST(phone AS SIGNED))，导致索引失效。

优化方案：确保查询条件与列类型一致。

SELECT * FROM users WHERE phone = '13800138000'; -- 加上引号

优化后，执行计划会显示type: ref, key: idx_phone，查询瞬间完成。

避坑指南：除了隐式转换，导致索引失效的常见“坑”还有：
对索引列使用函数：WHERE LEFT(name, 3) = 'abc'，WHERE YEAR(date_column) = 2023。
对索引列进行运算：WHERE age + 1 > 20。
在索引列上使用NOT,!=,<>（并非绝对，但多数情况下优化器会放弃索引）。
使用OR连接条件，如果OR前后的条件列并非都有索引，会导致索引失效。
复合索引未遵循最左前缀原则：索引是(a, b, c)，查询条件是WHERE b = 1 AND c = 2。

3.3 案例三：复杂关联与驱动表选择

有两张表：dept部门表（数据量小，约100行）和emp员工表（数据量大，约100万行）。查询某个部门的所有员工。

原始SQL：

SELECT e.* FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE d.name = '研发部';

执行计划：

id	select_type	table	type	possible_keys	key	key_len	rows	filtered	Extra
1	SIMPLE	d	ALL	PRIMARY,idx_name	NULL	NULL	100	10.00	Using where
1	SIMPLE	e	ref	idx_dept_id	idx_dept_id	4	5000	100.00	Using index condition

解读与诊断：这是一个典型的关联查询。执行顺序是id相同，从上到下。

第一行 (d表)：type: ALL，对dept表进行了全表扫描，用WHERE d.name = '研发部'过滤。虽然dept表只有100行，全表扫描代价不大，但这里name字段没有索引（possible_keys有idx_name但key为NULL，可能索引失效或优化器认为全表更快）。
第二行 (e表)：对于dept表扫描过滤出的每一行（假设过滤后只有1行“研发部”），通过idx_dept_id索引去emp表中查找匹配的员工。type: ref，效率尚可。

问题：虽然最终效果可能还行，但驱动表（第一行）的全表扫描是不必要的。如果dept.name条件能更快定位，整体效率会提升。

优化方案：确保dept.name上有高效索引，并让优化器能利用它。

-- 确保dept.name有索引 CREATE INDEX idx_dept_name ON dept(name); -- 或者，使用STRAIGHT_JOIN（慎用）强制指定驱动表，但通常让优化器选择更好 -- SELECT e.* FROM dept d STRAIGHT_JOIN emp e ON e.dept_id = d.id WHERE d.name = '研发部';

优化后，dept表的访问类型可能变为const或ref，直接定位到“研发部”这一行，然后用这一行的id去驱动大表emp的索引查询，效率最优。

关于驱动表选择的经验：在多表关联时，优化器会选择它认为成本最小的表作为驱动表（即执行计划中的第一张表）。通常，小表驱动大表是原则，因为驱动表需要被循环。我们可以通过给被驱动表的关联字段加索引来加速内层循环。使用EXPLAIN可以验证优化器是否做出了我们期望的选择。

4. 高级技巧与深度优化：不止于看结果

掌握了基础解读和常见案例，我们可以更进一步，利用EXPLAIN的一些高级功能和结合其他工具进行深度优化。

4.1 EXPLAIN ANALYZE：获取实际执行数据

MySQL 8.0.18 引入了EXPLAIN ANALYZE。它与EXPLAIN最大的区别是：它会实际执行查询，然后输出执行计划以及每一步的实际耗时、实际返回行数等详细信息。这对于验证优化器预估 (rows) 是否准确、查找实际执行瓶颈至关重要。

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 10;

输出格式是树状的，包含了每个节点的实际执行时间（如actual time=0.1..0.2表示首次执行到所有循环完成的时间范围）、实际返回行数 (rows=10)、循环次数等。

解读重点：

对比estimated rows和actual rows，如果差异巨大，说明表的统计信息可能过期了，需要运行ANALYZE TABLE来更新。
查看每个节点的actual time，找到最耗时的步骤（即瓶颈）。
观察是否有Filter操作，它表示在索引扫描后进行了额外的过滤，如果过滤掉很多行，说明索引效率不高。

4.2 格式化输出与可视化

默认的表格输出在复杂查询时可能不够直观。EXPLAIN支持多种格式化输出：

EXPLAIN FORMAT=JSON SELECT ...：输出详细的JSON格式信息，包含成本估算等更多细节，适合程序解析。
EXPLAIN FORMAT=TREE SELECT ...：输出树形结构，更清晰地展示执行流程的层次关系，尤其在包含子查询或UNION时。

许多数据库客户端工具（如MySQL Workbench, DataGrip, Navicat）都提供了可视化的执行计划功能，将EXPLAIN的结果以图形化的方式展现，节点大小代表成本，箭头表示数据流，让性能瓶颈一目了然。善用这些工具可以极大提升分析效率。

4.3 结合性能模式 (Performance Schema) 与慢查询日志

EXPLAIN看的是“计划”，是预估。要全面诊断，还需要看“实际”运行情况。

慢查询日志 (Slow Query Log)：记录执行时间超过long_query_time阈值的SQL。通过mysqldumpslow或pt-query-digest工具分析慢日志，找到最耗时的TOP SQL，再用EXPLAIN去分析它们。这是发现问题的入口。
Performance Schema：MySQL内置的性能监控库。可以查看更细粒度的性能数据，例如：
- events_statements_summary_by_digest：汇总所有SQL模板的执行统计（次数，总耗时，扫描行数等），找到高频或高耗时的SQL模式。
- events_stages_*：查看SQL执行各阶段（如排序、创建临时表）的耗时。结合EXPLAIN和 Performance Schema，你可以知道一条SQL不仅计划怎么走，实际每一步花了多少时间。

4.4 优化器提示 (Optimizer Hints) 与索引提示

有时，优化器选择的计划并不是最优的（比如错误估计了数据分布）。我们可以使用优化器提示来影响它的决策。但这应该是最后的手段，并且需要充分测试。

使用索引提示：

SELECT * FROM users USE INDEX (idx_email) WHERE email LIKE 'a%'; -- 建议使用某个索引 SELECT * FROM users IGNORE INDEX (idx_email) WHERE ...; -- 忽略某个索引 SELECT * FROM users FORCE INDEX (idx_email) WHERE ...; -- 强制使用某个索引

使用优化器提示(MySQL 5.7+)：

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM big_table; -- 设置语句最大执行时间 SELECT /*+ JOIN_ORDER(t1, t2) */ * FROM t1 JOIN t2 ...; -- 指定关联顺序 SELECT /*+ MRR(t1) */ * FROM t1 WHERE ...; -- 启用多范围读优化

重要警告：滥用提示是危险的。数据库的统计信息会变，数据分布会变。今天你强制使用的索引可能是最快的，明天数据量增长后可能就成了最慢的。提示会使SQL语句变得脆弱，难以维护。优先考虑通过优化索引、重写查询、更新统计信息来让优化器做出正确选择。

4.5 统计信息与索引维护

优化器制定执行计划的依据是表的统计信息（如索引的区分度、数据行数等）。如果统计信息过期，优化器就可能制定出糟糕的计划。

更新统计信息：对于InnoDB表，运行ANALYZE TABLE table_name;来更新表的统计信息。在发生大量数据变更（如批量导入、删除）后，建议执行此操作。
索引维护：
- 索引选择性：索引列不同值的数量占总行数的比例。选择性越高（越接近1），索引效率越高。像“性别”这种只有两三种值的列，建索引通常意义不大。
- 索引合并：留意Extra中的Using union(idx_a, idx_b); Using intersect(...)。这表示优化器使用了多个索引然后合并结果。有时这不如一个合适的复合索引高效。
- 冗余与未使用索引：定期检查information_schema.STATISTICS和sys.schema_unused_indexes（需要安装sys库），清理那些从未被使用或重复的索引，因为它们会降低写性能。

解读EXPLAIN执行计划，是一个从“知其然”到“知其所以然”的过程。它要求我们不仅看懂每一列的输出，更要理解其背后的数据库原理——索引是如何工作的、优化器是如何基于成本做决策的、数据是如何在磁盘和内存间流动的。掌握了这个工具，你就拥有了从被动救火到主动预防数据库性能问题的能力。下次面对慢查询时，别急着盲目尝试，先EXPLAIN一下，让它告诉你问题的真相。

查看全文

http://www.cnnetsun.cn/news/4062973.html