EXPLAIN 输出格式

EXPLAIN 语句提供有关 MySQL 如何执行语句的信息。EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。

EXPLAIN 为 SELECT 语句中使用的每个表返回一行信息。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。这意味着 MySQL 从第一个表中读取一行,然后在第二个表中找到匹配的行,然后在第三个表中查找,依此类推。当所有表都处理完毕后,MySQL 输出所选列并回溯表列表,直到找到一个有更多匹配行的表。从该表中读取下一行,然后继续处理下一个表。

EXPLAIN 输出列

本节介绍 EXPLAIN 生成的输出列。后面的部分提供了有关 type 和 Extra 列的附加信息。

EXPLAIN 的每个输出行提供有关一个表的信息。每行包含 表 10.1,"EXPLAIN 输出列" 中总结的值,并在表后提供更详细的描述。列名显示在表的第一列中;第二列提供使用 FORMAT=JSON 时显示的等效属性名称。

表 10.1 EXPLAIN 输出列

列 JSON 名称 含义
id select_id SELECT 标识符
select_type 无 SELECT 类型
table table_name 输出行的表
partitions partitions 匹配的分区
type access_type 连接类型
possible_keys possible_keys 可供选择的索引
key key 实际选择的索引
key_len key_length 所选键的长度
ref ref 与索引比较的列
rows rows 要检查的行数估计值
filtered filtered 按表条件过滤的行百分比
Extra 无 附加信息

EXPLAIN 连接类型

EXPLAIN 输出的 type 列描述了表是如何连接的。在 JSON 格式的输出中,这些作为 access_type 属性的值找到。以下列表描述连接类型,按从最佳到最差的顺序排列:

value IN (SELECT key_column FROM single_table WHERE some_expr)


- [`range`](https://dev.mysql.com/doc/refman/9.6/en/explain-output.html#jointype_range)

仅检索给定范围内的行,使用索引选择行。输出行中的 `key` 列指示使用哪个索引。`key_len` 包含使用的最长键部分。此类型的 `ref` 列为 `NULL`。

当使用任何 [`=`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_equal)、[`<>`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_not-equal)、[`>`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_greater-than)、[`>=`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_greater-than-or-equal)、[`<`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_less-than)、[`<=`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_less-than-or-equal)、[`IS NULL`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_is-null)、[`<=>`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_equal-to)、[`BETWEEN`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_between)、[`LIKE`](https://dev.mysql.com/doc/refman/9.6/en/string-comparison-functions.html#operator_like) 或 [`IN()`](https://dev.mysql.com/doc/refman/9.6/en/comparison-operators.html#operator_in) 运算符将键列与常量进行比较时,可以使用 [`range`](https://dev.mysql.com/doc/refman/9.6/en/explain-output.html#jointype_range):

```sql
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

EXPLAIN 额外信息

EXPLAIN 输出的 Extra 列包含有关 MySQL 如何解析查询的附加信息。以下列表解释可以出现在此列中的值。每个项目还指示 JSON 格式输出中哪个属性显示 Extra 值。对于其中一些,有特定的属性。其他则显示为 message 属性的文本。

如果你想使查询尽可能快,请注意 Extra 列值为 Using filesort 和 Using temporary,或在 JSON 格式的 EXPLAIN 输出中,using_filesort 和 using_temporary_table 属性等于 true。

EXPLAIN 输出解释

通过取 EXPLAIN 输出的 rows 列中值的乘积,你可以很好地了解连接的质量。这应该告诉你 MySQL 必须检查多少行才能执行查询。如果你使用 max_join_size 系统变量限制查询,则此行乘积还用于确定执行哪些多表 SELECT 语句以及中止哪些语句。请参阅 第 7.1.1 节,"配置服务器"。

以下示例显示如何根据 EXPLAIN 提供的信息逐步优化多表连接。

假设你有此处显示的 SELECT 语句,并且计划使用 EXPLAIN 检查它:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

对于此示例,做出以下假设:

最初,在执行任何优化之前,EXPLAIN 语句生成以下信息:

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

因为每个表的 type 都是 ALL,所以此输出表明 MySQL 正在生成所有表的笛卡尔积;即,每行的组合。这需要相当长的时间,因为必须检查每个表中行数的乘积。对于手头的情况,此乘积为 74 × 2135 × 74 × 3872 = 45,268,558,720 行。如果表更大,你可以想象需要多长时间。

这里的一个问题是,如果将列声明为相同的类型和大小,MySQL 可以更有效地使用列上的索引。在这种情况下,如果 VARCHAR 和 CHAR 声明为相同的大小,则被视为相同。tt.ActualPC 声明为 CHAR(10),et.EMPLOYID 为 CHAR(15),因此存在长度不匹配。

要修复列长度之间的这种差异,请使用 ALTER TABLE 将 ActualPC 从 10 个字符延长到 15 个字符:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)。再次执行 EXPLAIN 语句会产生此结果:

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

这并不完美,但要好得多:rows 值的乘积减少了 74 倍。此版本在几秒钟内执行。

可以进行第二次更改以消除 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 比较的列长度不匹配:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

在该修改之后,EXPLAIN 产生此处显示的输出:

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

此时,查询几乎已经优化到最好。剩下的问题是,默认情况下,MySQL 假设 tt.ActualPC 列中的值均匀分布,而 tt 表并非如此。幸运的是,告诉 MySQL 分析键分布很容易:

mysql> ANALYZE TABLE tt;

有了额外的索引信息,连接就完美了,EXPLAIN 产生了这个结果:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN 输出中的 rows 列是 MySQL 连接优化器的有根据的猜测。通过将 rows 乘积与查询返回的实际行数进行比较,检查这些数字是否接近真实情况。如果数字相差很大,你可能会通过在 SELECT 语句中使用 STRAIGHT_JOIN 并尝试以不同的顺序列出 FROM 子句中的表来获得更好的性能。(但是,STRAIGHT_JOIN 可能会阻止使用索引,因为它禁用了半连接转换。请参阅 使用半连接转换优化 IN 和 EXISTS 子查询谓词。)

延伸阅读