MySQL 优化 —— EXPLAIN 执行计划详解

忘是亡心i 2023-07-03 08:09 6阅读 0赞

# 引言 #

本博客大部分内容翻译自MySQL 官网 [Understanding the Query Execution Plan][] 专题。另外有一些**补充**，则来自于网课以及《高性能MySQL（第三版）》。

根据我们的表、字段、索引、以及 where 子句中的条件等信息，MySQL 优化器会考虑各种技术来更高效地执行查找。一个大表中的查找不一定要读取所有记录；多表连接也不一定需要比较每条联合记录。**优化器选择的执行最优查询的操作集，称为“查询执行计划”**，**也可以说是 EXPLAIN 计划**。我们的目标就是找到那些可以将查询优化地更好的点，然后通过学习 SQL 语法和索引等技术，来改善执行计划。

# 一、EXPLAIN 介绍 #

EXPLAIN 语句提供了 MySQL 如何执行语句的信息：

1、MySQL5.6 之后 EXPLAIN 可以和 SELECT DELETE INSERT REPLACE UPDATE 语句等一起工作；

2、当 EXPLAIN 和一个可解释的语句一起使用时，MySQL 就会展示来自**优化器**的关于语句执行计划的信息。即，MySQL 会解释它将会怎样执行语句，包括表是**如何连接**的，**以什么方式排序**的等信息。

3、When EXPLAIN is used with `FOR CONNECTION connection_id`  rather than an explainable statement, it displays the execution plan for the statement executing in the named connection.（这句暂不翻译）

4、对于 SELECT 语句， EXPLAIN 提供了额外的执行计划信息，可以用 SHOW WARNINGS 来查看。参考：[Section 8.8.3, “Extended EXPLAIN Output Format”][Section_8.8.3_ _Extended EXPLAIN Output Format].

5、EXPLAIN对于检查涉及分区表的查询非常有用。参考：[Section 22.3.5, “Obtaining Information About Partitions”][Section_22.3.5_ _Obtaining Information About Partitions].

6、FORMAT 选项可以用于选择输出格式。TRADITIONAL 以表格的形式展示。如果没有指定 FORMAT 选项，TRADITIONAL 就是默认的。JSON 格式会以 json 格式展示 EXPLAIN 信息。例如：EXPLAIN FORMAT = JSON SELECT... 。

在 EXPLAIN 的帮助下，你可以清楚的知道为了让查询变得更快，**该在哪里给表添加索引**。你也可以知道优化器是否以**最佳的顺序连接各个表**。为了让优化器使用 SELECT 语句中表的命名顺序连接各表，以 **SELECT  STRAIGHT\_JOIN**(而不是SELECT)开头即可。（参考：[Section 13.2.9, “SELECT Statement”][Section_13.2.9_ _SELECT Statement]）但是，**`STRAIGHT_JOIN` 可能会妨碍索引的使用，因为它禁用了半连接转换**（because it disables semijoin transformations. ）。参考：[Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”][Section_8.2.2.1_ _Optimizing Subqueries_ Derived Tables_ and View References with Semijoin Transformations].

**优化器跟踪（**The optimizer trace**）**有时可能提供与 EXPLAIN 互补的信息。但是，优化器跟踪的格式和内容会受不同版本的影响。更多细节，参考：[MySQL Internals: Tracing the Optimizer][MySQL Internals_ Tracing the Optimizer].

如果你对本应该使用索引而没有用到索引的情况感到疑惑，执行一下 **ANALYZE TABLE** 来更新表统计信息。例如列的基数（cardinality of keys），这会影响优化器做出的选择。参考：[Section 13.7.2.1, “ANALYZE TABLE Statement”][Section_13.7.2.1_ _ANALYZE TABLE Statement].

> **注意：**
> 
> EXPLAIN 还可以用来**获取表的列的信息**。
> 
> *EXPLAIN  tb1\_name  *与  *DESCRIBE  tb1\_name  、* *SHOW  COLUMNS  FROM  tb1\_name * 是等价的。
> 
> 更多信息，参考：[Section 13.8.1, “DESCRIBE Statement”][Section_13.8.1_ _DESCRIBE Statement]，和  [Section 13.7.5.5, “SHOW COLUMNS Statement”][Section_13.7.5.5_ _SHOW COLUMNS Statement]。

# 二、EXPLAIN 的输出格式 #

EXPLAIN会为 select 语句中的**每张表返回一行**信息。并会以MySQL处理语句时**读取这些表的顺序**罗列它们。

MySQL 解决所有 join 的方法是使用一个“**嵌套循环关联**”的方法。也就是说，**MySQL会从第一张表中读取一条记录，然后找到第二张表中与之匹配的记录，然后再找第三张表，依此类推**。当所有的表处理完毕，MySQL会输出查询的列并回溯**表列表**（table list），直到找到一个有更多行的表（译者注：连接表的时候，主表查询出的记录往往是最多的，从主表开始关联查询，再回溯到主表，可能官网想表达的是这个意思）。下一条记录会从该表中读取，并且继续处理下一张表。

EXPLAIN 输出包含了**分区信息**（partitions 列）。同样，对于 SELECT 语句，EXPLAIN 会生成扩展信息，只要在 EXPLAIN 执行完成后，直接执行 SHOW WARNINGS 即可。参考：[Section 8.8.3, “Extended EXPLAIN Output Format”][Section_8.8.3_ _Extended EXPLAIN Output Format]

> 注意
> 
> 旧的MySQL版本中, 分区和扩展信息使用 [`EXPLAIN PARTITIONS`][EXPLAIN PARTITIONS] 和 `EXPLAIN EXTENDED `输出。这些语法依然向后兼容，但是分区和扩展输出现在默认都是开启的了。所以 `PARTITIONS` 和 `EXTENDED` 关键字完全多余。未来版本也会移除的。
> 
> **不可以**在EXPLAIN语句中同时使用 PARTITIONS 和 EXTENDED 关键字。另外，哪一个都不能和 FORMAT 选项一起使用。

> MySQL Workbench （译者注：这是一款由 MySQL 官方出品的，类似 Navicat 的数据库管理工具）有一个 Visual Explain 功能，可以提供可视化的 EXPLAIN 输出信息。参考：[Tutorial: Using Explain to Improve Query Performance][Tutorial_ Using Explain to Improve Query Performance].

## 2.1 EXPLAIN 输出字段（[EXPLAIN Output Columns][]） ##

这一节描述了 EXPLAIN 的输出字段。后面的两节则提供了更多的关于 type 和 Extra 字段的信息。

**EXPLAIN输出的每一行都对应一张表。**下面的表提供了EXPLAIN的输出字段，第一列是字段名称，第二列是当 FORMAT = JSON 时的输出字段名称：

<table> 
 <thead> 
  <tr> 
   <th>Column</th> 
   <th>JSON Name</th> 
   <th>Meaning</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_id" rel="nofollow"><code>id</code></a></td> 
   <td><code>select_id</code></td> 
   <td>The&nbsp;<code>SELECT</code>&nbsp;identifier：查询id</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_select_type" rel="nofollow"><code>select_type</code></a></td> 
   <td>None</td> 
   <td>The&nbsp;<code>SELECT</code>&nbsp;type：查询类型</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_table" rel="nofollow"><code>table</code></a></td> 
   <td><code>table_name</code></td> 
   <td>The table for the output row：对应的表</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_partitions" rel="nofollow"><code>partitions</code></a></td> 
   <td><code>partitions</code></td> 
   <td>The matching partitions：匹配的分区</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_type" rel="nofollow"><code>type</code></a></td> 
   <td><code>access_type</code></td> 
   <td>The join type：访问类型</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_possible_keys" rel="nofollow"><code>possible_keys</code></a></td> 
   <td><code>possible_keys</code></td> 
   <td>The possible indexes to choose：可能用到的索引</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_key" rel="nofollow"><code>key</code></a></td> 
   <td><code>key</code></td> 
   <td>The index actually chosen：真正被用到的索引</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_key_len" rel="nofollow"><code>key_len</code></a></td> 
   <td><code>key_length</code></td> 
   <td>The length of the chosen key：用到的索引长度</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_ref" rel="nofollow"><code>ref</code></a></td> 
   <td><code>ref</code></td> 
   <td>The columns compared to the index：与索引比较的列</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows" rel="nofollow"><code>rows</code></a></td> 
   <td><code>rows</code></td> 
   <td>Estimate of rows to be examined：大约要检索的行数</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_filtered" rel="nofollow"><code>filtered</code></a></td> 
   <td><code>filtered</code></td> 
   <td>Percentage of rows filtered by table condition：按表条件过滤的行的百分比</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_extra" rel="nofollow"><code>Extra</code></a></td> 
   <td>None</td> 
   <td>Additional information：附加信息</td> 
  </tr> 
 </tbody> 
</table>

### **1、id（JSON 名：select\_id）** ###

SELECT 标识符（SELECT identifier）。这是一个连续的数字，用以标识查询中的 SELECT 。如果引用了**其他行的联合结果集（union result of other rows）**，那么 id 会为 NULL。这种情况下，该行的 **table** 字段会显示为 **<union M, N>** 这样的形式，表示该行代表了 id 值为 M 和 N 的行的联合（the row refers to the union of the rows with `id` values of *`M`* and *`N `*）。

> 重点：id 是一个自然数编号，如1、2，但有时也可以是NULL。如上所述，**NULL的时候，就是引用了一个 UNION 结果集**。
> 
> 当 id 为数字的时候，**编号大的会先执行**。有时候，编号会相同，**相同编号就从上到下执行**。

### **2、select\_type（JSON 名：无）** ###

**查询类型**。MySQL将查询分为简单和复杂类型，复杂类型可分为**三大类**：**简单子查询**、**FROM子查询**，**以及UNION查询**。select\_type 就是用于区分这三类复杂查询。可选值如下（红色标记为常见值）：

<table> 
 <thead> 
  <tr> 
   <th><code>select_type</code>&nbsp;Value</th> 
   <th>JSON Name</th> 
   <th>Meaning</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><span style="color:#f33b45;"><strong><code>SIMPLE</code></strong></span></td> 
   <td>None</td> 
   <td><strong>简单查询</strong>（没有任何 UNION 或 子查询）。</td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;"><strong><code>PRIMARY</code></strong></span></td> 
   <td>None</td> 
   <td><strong>主查询</strong>，如果查询中包含任何复杂的子部分，那么<strong>最外层查询</strong>被标记<strong>PRIMARY</strong>。</td> 
  </tr> 
  <tr> 
   <td><strong><a href="https://dev.mysql.com/doc/refman/5.7/en/union.html" rel="nofollow"><span style="color:#f33b45;"><code>UNION</code></span></a></strong></td> 
   <td>None</td> 
   <td><strong>UNION 中的第二个或后面的SELECT语句</strong></td> 
  </tr> 
  <tr> 
   <td><code>DEPENDENT UNION</code></td> 
   <td><code>dependent</code>&nbsp;(<code>true</code>)</td> 
   <td>UNION 中的第二个或后面的SELECT语句, 依赖于外部查询</td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;"><strong><code>UNION RESULT</code></strong></span></td> 
   <td><code>union_result</code></td> 
   <td><strong>从UNION 的结果获取数据的SELECT。</strong></td> 
  </tr> 
  <tr> 
   <td><strong><a href="https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-subquery" rel="nofollow"><span style="color:#f33b45;"><code>SUBQUERY</code></span></a></strong></td> 
   <td>None</td> 
   <td><strong>SELECT子句或WHERE子句中的子查询</strong></td> 
  </tr> 
  <tr> 
   <td><code>DEPENDENT SUBQUERY</code></td> 
   <td><code>dependent</code>&nbsp;(<code>true</code>)</td> 
   <td>子查询中的第一个 SELECT, 依赖于外层查询</td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;"><strong><code>DERIVED</code></strong></span></td> 
   <td>None</td> 
   <td><strong>派生表。FROM子句中的子查询。</strong>MySQL会递归执行这些子查询，<strong>把结果放在临时表里</strong>。</td> 
  </tr> 
  <tr> 
   <td><code>MATERIALIZED</code></td> 
   <td><code>materialized_from_subquery</code></td> 
   <td>Materialized subquery&nbsp; <strong>物化子查询</strong>。参考《<a href="https://morty.blog.csdn.net/article/details/106319526" rel="nofollow">MySQL高级 —— 查询性能优化</a>》4.1节</td> 
  </tr> 
  <tr> 
   <td><code>UNCACHEABLE SUBQUERY</code></td> 
   <td><code>cacheable</code>&nbsp;(<code>false</code>)</td> 
   <td>非缓存子查询，结果不能被缓存的子查询，必须被外部查询的每一行重新求得</td> 
  </tr> 
  <tr> 
   <td><code>UNCACHEABLE UNION</code></td> 
   <td><code>cacheable</code>&nbsp;(<code>false</code>)</td> 
   <td>非缓存子查询（uncacheable subquery）的 UNION 中的第二个或后面的 SELECT</td> 
  </tr> 
 </tbody> 
</table>

SUBQUERY还可以被标记为DEPENDENT SUBQUERY，这一般是指SELECT依赖于外层查询发现的数据（很可能是依赖于FROM派生表的外层SELECT）。参考：[Section 13.2.10.7, “Correlated Subqueries”][Section_13.2.10.7_ _Correlated Subqueries] 。

**DEPENDENT SUBQUERY 的取值与 UNCACHEABLE SUBQUERY（由于用户变量等原因） 的取值不同。**对于 DEPENDENT SUBQUERY ，对于来自其外部查询的变量的每组不同值，子查询只重新计算一次。而对于 UNCACHEABLE SUBQUERY ，对外部查询的每行记录，该子查询都会计算一遍。

**子查询缓存与缓存中的查询结果缓存不一样**（具体描述参考 [Section 8.10.3.1, “How the Query Cache Operates”][Section_8.10.3.1_ _How the Query Cache Operates]）。子查询缓存发生在**查询执行过程中**，而查询结果缓存只在**查询执行完毕时**才会存储结果。

当你在 EXPLAIN 语句中指定了 **FORMAT = JSON** ，输出的结果并没有一个对应 select\_type 的单独属性；query\_block 属性对应给定的 SELECT 。与刚才显示的大多数 SELECT 子查询类型等价的属性都是有的，并且在合适的时机就会展示。不过并没有与 SIMPLE 和 PRIMARY 等价的 JSON 值。

select\_type 属性值对于**非 SELECT** 语句，**会展示影响表的语句类型**，**如 DELETE 语句的 select\_type 就是 DELETE**。

### 3、table（JSON 名：table\_name） ###

explain 输出的每一行都对应一个**表别名或表名**。它可以是下面的值中的一个：

> <union M, N> : 这一行引用了 id 值为 M 和 N 的表的联合。
> 
> <derived N> : 这一行引用了 id 值为 N 的表所派生的表。派生的表可能是一个结果集，比如，FROM 子句中的子查询。
> 
> <subquery N> : 这一行引用了 id 值为 N 的物化子查询的结果。参考：[Section 8.2.2.2, “Optimizing Subqueries with Materialization”][Section_8.2.2.2_ _Optimizing Subqueries with Materialization].

### 4、partitions（JSON 名：partitions） ###

查询的记录将会在哪个分区中匹配。NULL 代表没有分区表。参考： [Section 22.3.5, “Obtaining Information About Partitions”][Section_22.3.5_ _Obtaining Information About Partitions].

### 5、type（JSON 名：access\_type） ###

关联类型，但更准确的说法是——**访问类型**，换言之就是**MySQL决定如何查找表中的行**。参考 2.2 节。

### 6、possible\_keys（JSON 名：possible\_keys） ###

该属性可以表明查询中，对应表有哪些索引可以使用。注意这个属性完全不依赖于表在 explain 输出中的显示顺序。也就是说，以生成的表顺序 ，possible\_keys 中的有些索引可能实际中并不会用到。

如果该属性是 **NULL (或者在 JSON 格式中是 undefined )，代表没有相关的索引。**这时，你可能就应该努力通过调试 WHERE 子句来提升你的查询性能，检查是否涉及到了一些字段或者适合索引查询的字段。如果有，就创建一个合适的索引，然后再次通过 EXPLAIN 进行检验。

查看一个表有哪些索引，可以使用 ***SHOW INDEX FROM tbl\_name***语句。

### 7、key（JSON 名：key） ###

这一列表示 MySQL 决定采用哪个索引来优化对该表的访问。如果 MySQL 决定使用 possible\_keys 中的一个索引去查找记录，那么这个索引就会列在 key 属性中。

**key 中也会出现 possible\_key 中没有出现的索引。**发生这种情况，很可能是 possible\_keys 没有找到适合查询的索引，但是所有查询的字段都在索引中。也就是说，查询**使用了覆盖索引**。因此，尽管它不用于决定要查询哪些行，但却依然可以用于查询字段，因为索引扫描依然比行扫描更高效。换句话说，**possible\_keys 揭示了哪一个索引能有助于高效地行查找，而 key 显示的是优化采用哪一个索引可以最小化查询成本**。

对于InnoDB ，即使查询列表中有主键，二级索引也可能覆盖所查询的字段，因为**InnoDB用每个二级索引存储了主键值**。如果列是NULL, MySQL就找不到索引来更有效地执行查询。

要强制MySQL使用或忽略在 possiblele\_keys 中列出的索引，请在查询中使用 `FORCE INDEX`，`USE INDEX `或 `IGNORE INDEX 。参考：` [Section 8.9.4, “Index Hints”][Section_8.9.4_ _Index Hints].

对于 MyISAM，运行 ANALYZE TABLE 可以帮助优化器选择更好的索引。对于 MyISAM 表来说， myisamchk --analyze 也是一样的。参考： [Section 13.7.2.1, “ANALYZE TABLE Statement”][Section_13.7.2.1_ _ANALYZE TABLE Statement], 和 [Section 7.6, “MyISAM Table Maintenance and Crash Recovery”][Section_7.6_ _MyISAM Table Maintenance and Crash Recovery].

### 8、key\_len（JSON 名：key\_length） ###

该字段表示 MySQL **在索引里使用的字节数。**

因为key\_len是通过查找表的定义而被计算出，而不是表中的数据，因此它显示了在索引字段中可能的最大长度，**而不是表中数据使用的实际字节数**。key\_len 的值可以让你判断 **MySQL 究竟用到了复合索引的哪几个索引列。如果 key 属性的值为 NULL , 那么 key\_len 肯定也是 NULL 。**

由于索引的存储格式，那些可以为 NULL 的字段的索引长度要比非空字段的索引长度大一些。

MySQL并不总是显示一个索引真正使用了多少。例如，如果对一个前缀模式匹配（例如 '张%'）执行LIKE查询，它会显示**列的完整宽度**正在被使用。

> 计算 key\_len 的简易方法：
> 
> int 类型在MySQL中以4个字节存储，key\_len 为 4，如果列值允许为 NULL，那么需要 + 1，即 key\_len 为 5.
> 
> double 类型以8个字节存储，key\_len 为 8，如果允许 NULL，那么同样 +1， 即 key\_len 为 9.
> 
> char(n) 定长字符串，首先需要看字符集，常见的**utf8以3个字节存**储每个字符，gbk用2个，latin用1个。key\_len 就等于**每个字节长度乘以允许最大字符数n，如果允许NULL，key\_len 也要 +1**。例如 char(20) DEFAULT NULL，编码为utf8 ,那么 key\_len 就是 3 × 20 + 1 = 61。如果不允许为 NULL ，就是60。
> 
> varchar(n)变长字符串，每个字符：utf8为3字节、gbk为2字节、latin为1字节。由于是变长，因此 key\_len 要 +2，如果允许 NULL，同样 +1。其他和 char计算方式一样。例如，varchar(20) DEFAULT NULL，编码 utf8，那么 key\_len 就是：
> 
> 3 × 20 + 2 + 1 = 63，如果不允许为 NULL，就是62。
> 
> 上面的说明只是单独计算每种列值类型的方法，如果是复合索引，那么key\_len 就是用到的索引列长度和。

### 9、ref（JSON 名：ref） ###

**ref 列显示了常量或哪些列与 key 列中的索引进行了比较。**只有 type 列是 ref 的时候，ref 列才会有值。

简单的说，就是 key 中的索引，如果与一个常量比较，那么 ref 会显示 const，如果是与其他表的某个列进行比较，那么就会显示该列名。

如果 ref 属性的值是 func ，那么用到的值就是某些函数的结果。想要知道是哪个函数，在 EXPLAIN 执行后使用 SHOW WARNINGS ，查看EXPLAIN 的扩展信息。

函数实际上可能是一个运算符，比如算术运算符。

### 10、rows（JSON 名：rows） ###

**rows 列表示MySQL认为执行查询必须检查的行数。**这个数字是内嵌关联循环计划里的**循环数目**。也就是说，它不是最终的结果集里的行数，而是MySQL为了找到符合条件的结果集而必须读取的行的平均数。

对于 InnoDB 表，这个数是一个估值，而且可能并不总是准确的。

### 11、filtered（JSON 名：filtered） ###

filtered 属性表示**被筛选条件过滤掉的记录条数占全表的估计百分比**。最大值是100，意味着记录全部被过滤掉。从100开始递减的值表示过滤的量在增加。rows 属性表示了需要检查的估计行数，rows 乘 filtered 表示了将会被后面的表关联的记录条数。例如，如果 rows 是1000，filtered 是 50.00（50%），那么要与后面的表连接的记录条数就是 1000 × 50% = 500。

> 对于filtered ，原文的描述是：The `filtered` column indicates an estimated percentage of table rows that will be filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred.  这里面有一个语义上的陷阱，即 filtered 究竟表示的是 “被过滤掉的” ？还是 “过滤后(留下来)的” ，经过本人测试，filtered 表示的是前者，即 “被过滤掉的” ，这样后面的语义也就基本自洽了。而 filtering 则表示 “过滤后（留下来）的” 。

### 12、Extra（JSON 名：none） ###

这一列显示了关于 MySQL如何**处理查询的额外信息**。对于不同值的描述，参考：[Extra Information][]. 或参考下面 2.3 节。

## **2.2** **EXPLAIN type访问类型**（[EXPLAIN Join Types][]） ##

type 属性描述了表之间是如何连接（或关联）的。在 JSON 格式输出中，对应 access\_type 属性。下面的列表描述了访问类型，顺序从“最理想类型”到“最糟糕的类型”：

> **system > const > eq\_ref > ref > range > index > ALL**

### 2.2.1 system（不常见） ###

表只有一行（=系统表）。是 const 连接类型的一种特殊情况。

### 2.2.2 const ###

**表最多只有 1 条匹配记录，在查询开始时就会读取该表。**因为只有一行，所以这一行中列的值可以被其他优化器视为常量。const 访问类型非常快，因为他们只会被读取一次。MySQL能将这个查询转换为一个常量，然后可以高效地将表从连接操作中移除。

const 会在你使用**整个主键（all parts of a PRIMARY KEY）**或**唯一索引（UNIQUE index）**去比较一个常量的时候用到。在下面的查询中，**tb1\_name** 就是一张 const 表：

SELECT * FROM tbl_name WHERE primary_key=1;
    
    SELECT * FROM tbl_name
      WHERE primary_key_part1=1 AND primary_key_part2=2;

### 2.2.3 eq\_ref ###

使用这种索引查找，MySQL知道最多只返回一条符合条件的记录。它会在**所有的索引部分都被用到**的时候以及**索引是主键**或**非空唯一索引**时出现到，它会将它们与某个参考值做比较。MySQL 对于这类访问类型的优化做的非常好，因为**MySQL知道无须估计匹配行的范围或在找到匹配行后再继续查找**。

**eq\_ref 会在索引列使用 = 号的时候用到。**比较的值可以是一个**常量**也可以是一个**从前表读取的列**（的表达式）。在下面的例子中，MySQL 可以使用 eq\_ref 类型来处理 ref\_table：

SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
    
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;

### 2.2.4 ref ###

这是一种**索引访问**（有时也叫“索引查找”）**，**它返回所有匹配某个单个值的行，是查找和扫描的混合体。此类索引访问只有当使用**非唯一性索引**或**唯一性索引的非唯一性前缀**时才会发生。把它叫做 ref 是因为**索引要跟某个参考值相比较**。这个参考值可以是一个常数，或是来自多表查询的结果值。如果该筛选列可以匹配少量的记录，那 ref 还算是一个不错的连接类型。

ref\_or\_null 是ref 之上的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目。

**ref 也可以在索引列使用 = 或 <=> 号的时候被用到**。下面的例子，MySQL 可以使用 ref 来处理 ref\_table：

SELECT * FROM ref_table WHERE key_column=expr;
    
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
    
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;

### 2.2.5 full\_text（不常见） ###

这种连接方式会在使用 FULLTEXT 索引的时候用到。

### 2.2.6 ref\_or\_null（不常见） ###

这种连接方式和 ref 类似，除此之外， MySQL 还会额外搜索包含 NULL 值的记录。这种连接类型的优化绝大多数是在处理子查询的时候。在下面的例子中， MySQL 会使用 ref\_or\_null 来处理 ref\_table：

SELECT * FROM ref_table
      WHERE key_column=expr OR key_column IS NULL;

参考：[Section 8.2.1.13, “IS NULL Optimization”][Section_8.2.1.13_ _IS NULL Optimization].

### 2.2.7 index\_merge（不常见） ###

这种连接类型表示使用了**索引合并优化（Index Merge optimization）**。这种情况下，explain 中的 key 属性会罗列出被用到的索引，key\_len 属性会列出用到的索引的最长的索引部分。参考：[Section 8.2.1.3, “Index Merge Optimization”][Section_8.2.1.3_ _Index Merge Optimization].

### 2.2.8 unique\_subquery（不常见） ###

这种类型在类似下面的一些使用 IN 的子查询时取代了 eq\_ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique\_subquery 只是一个**索引查找函数**，它完全**取代了子查询**，以提高效率。

### 2.2.9 index\_subquery（不常见） ###

这种连接类型有点像 unique\_subquery 。它取代了 IN 子查询，但它只在子查询中**有非唯一索引**时才会起作用，类似下面这样：

value IN (SELECT key_column FROM single_table WHERE some_expr)

### 2.2.10 range  ###

这种连接类型会使用**索引查询给定范围内**的记录。EXPLAIN 输出中的 key 属性表示了哪个 索引列 被用到。key\_len 包含了被用到的最长的索引部分。ref 属性为 NULL。

range 类型会在索引列使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、或 IN() 任意一种操作符去比较常量的时候被用到。当使用 IN或 OR 列表的时候，显示的范围扫描，其实并不能和 > 这类比较符的性能等同，虽然它们在EXPLAIN中显示的类型都是 range，但是 IN() 列表其实属于等值列表。参考《[MySQL高级 —— 高性能索引][MySQL_ _]》6.2 节。

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);

### 2.2.11 index ###

index 类型除了会扫描索引树之外，其他和 ALL 是一样的。会有两种情况出现：

1、如果索引是一个覆盖索引，那么这种类型的查询就**只会扫描索引树**。这种情况下， Extra 属性会显示 **Using Index**。一个只扫描索引的方式比 ALL 更快，这是因为索引数据肯定要比表中数据要少。

2、**以索引次序扫描全表**。Extra 不会显示 Uses Index。

index 类型的主要优点是**避免了排序**，最大缺点是要承担按索引次序读取整个表的开销。

MySQL 会在查询只用到了单一索引列的时候用到 index 这种类型。

### 2.2.12 ALL ###

这就是人们常说的“全表扫描”，这种类型会对前面各表的组合记录都进行全表扫描。如果表是第一个没有被标记为 const 的表，这通常是不好的，在所有其他情况下通常是非常糟糕的。通常你可以通过增加索引来避免 ALL 。但也有例外，例如在查询中使用了 LIMIT，或在 Extra 列中显示“Using distinct/not exists”。

## 2.3 EXPLAIN Extra 信息（[EXPLAIN Extra Information][Extra Information]） ##

Extra 属性显示了MySQL如何执行查询的额外信息。

### 2.3.1 Using index ###

此值表示MySQL将使用覆盖索引，以避免访问表。不要把覆盖索引和 type = index 访问类型混淆了。

### 2.3.2 Using where ###

这意味着MySQL服务器将在存储引擎检索行后再进行过滤。当它读取索引时，就能被存储引擎检验，因此不是所有带有 WHERE子句的查询都会显示“Using where” 。有时“Using where” 的出现就是一个暗示：查询可受益于不同的索引。

### 2.3.3 Using temporary ###

这意味着MySQL在对查询结果排序时会使用一个临时表。

### 2.3.4 Using filesort ###

这意味着MySQL会对结果使用一个外部索引排序，而不是按照索引次序从表里读取行。MySQL有两种文件排序算法，两种方式都可以在内存或磁盘上完成。EXPLAIN 不会告诉你 MySQL将使用哪一种文件排序，也不会告诉你排序会在内存里还是在磁盘上完成。

### 2.3.5 Range checked for each record (index map:N) ###

这个值意味着没有好用的索引，新的索引将在连接的每一行上重新估算。N是显示在possible\_keys 列中索引的位图，并且是冗余的。

## 2.4 EXPLAIN 输出的解释 ##

EXPLAIN输出可以给你在连接各种表查询的时候一个非常好的指示作用。这会大致告诉你MySQL 在执行查询的时候必须要检查多少行记录。如果你限制了 max\_join\_size 系统变量，那么 EXPLAIN 也会被用来告诉我们一些有用的东西。参考： [Section 5.1.1, “Configuring the Server”][Section_5.1.1_ _Configuring the Server].

**下面的例子显示了多表连接是如何基于 EXPLAIN 提供的信息一点点优化的**。

假设你有一个查询语句，并且你通过 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;

对于这个例子，做出下面的假设：

1、比较的列（译者注：columns being compared，实际上指的就是where 子句后面作为筛选条件的列，因为往往需要用到 = 号等操作符，因此在官网中一般都被称为被比较的列）定义如下：

<table> 
 <thead> 
  <tr> 
   <th>Table</th> 
   <th>Column</th> 
   <th>Data Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>tt</code></td> 
   <td><code>ActualPC</code></td> 
   <td><code>CHAR(10)</code></td> 
  </tr> 
  <tr> 
   <td><code>tt</code></td> 
   <td><code>AssignedPC</code></td> 
   <td><code>CHAR(10)</code></td> 
  </tr> 
  <tr> 
   <td><code>tt</code></td> 
   <td><code>ClientID</code></td> 
   <td><code>CHAR(10)</code></td> 
  </tr> 
  <tr> 
   <td><code>et</code></td> 
   <td><code>EMPLOYID</code></td> 
   <td><code>CHAR(15)</code></td> 
  </tr> 
  <tr> 
   <td><code>do</code></td> 
   <td><code>CUSTNMBR</code></td> 
   <td><code>CHAR(15)</code></td> 
  </tr> 
 </tbody> 
</table>

2、表有以下这些索引：

<table> 
 <thead> 
  <tr> 
   <th>Table</th> 
   <th>Index</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>tt</code></td> 
   <td><code>ActualPC</code></td> 
  </tr> 
  <tr> 
   <td><code>tt</code></td> 
   <td><code>AssignedPC</code></td> 
  </tr> 
  <tr> 
   <td><code>tt</code></td> 
   <td><code>ClientID</code></td> 
  </tr> 
  <tr> 
   <td><code>et</code></td> 
   <td><code>EMPLOYID</code>&nbsp;(primary key)</td> 
  </tr> 
  <tr> 
   <td><code>do</code></td> 
   <td><code>CUSTNMBR</code>&nbsp;(primary key)</td> 
  </tr> 
 </tbody> 
</table>

3、tt 表的 ActualPC 字段不是均匀分布的。

首先，在所有优化执行之前， 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)

因为每张表的连接类型都是 ALL ，这表明MySQL 正在生成一张笛卡尔集（a Cartesian product），也就是表中的每一行都进行了组合。这会花费相当长的时间，因为必须检查每个表中行数的乘积。对于这个案例，乘积就是：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 倍（译者注：et 表的 rows 由 74 变为了 1）。这一版的执行会在几秒钟完成。

第二处修改可以针对 tt.AssignedPC = et\_1.EMPLOYID 和 tt.ClientID = do.SUTNMBR 这两个比较中有关列长度不匹配的问题。

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 可能会妨碍到索引的使用，因为它禁用了半连接转换。参考：[Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”][Section_8.2.2.1_ _Optimizing Subqueries_ Derived Tables_ and View References with Semijoin Transformations].）

在某些情况下，当EXPLAIN SELECT与子查询一起使用时，可以执行修改数据的语句。参考：[Section 13.2.10.8, “Derived Tables”][Section_13.2.10.8_ _Derived Tables].

# 总结 #

这篇译文翻译了很长时间，断断续续可能有一个月。本篇文章有些地方可能翻译的并不准确，因此希望各位可以与原文比较阅读，增加理解。

另外，本来想在 Extra 部分就结束本篇翻译，没想到 MySQL 官网在最后一节给出了一个非常亲民的案例讲解，可以让我们一览 EXPLAIN 的常规用法。这一部分也是我认为翻译的比较准确的部分。

因为 EXPLAIN 语句非常重要，因此，这篇译文我也会经常翻阅，加深理解的同时不断纠正文中翻译的不准确或有所偏颇之处，同时希望大家能给予意见或建议。

2020-05-29 追加的部分，分散在文章的各个小节中，主要是在读完《高性能MySQL（第三版）》的五六章，以及附录EXPLAIN的部分，对执行计划和一些索引的概念有了更进一步的理解和认识。之前翻译的不是很准确的地方做了校对和润色，某些废话也是能删就删，我还写了很多关于索引及查询优化相关的文章，可以和这些文章一起阅读，结合实践并反复回看的话，相信一定可以成为MySQL性能优化领域的好手。

[Understanding the Query Execution Plan]: https://dev.mysql.com/doc/refman/5.7/en/execution-plan-information.html
[Section_8.8.3_ _Extended EXPLAIN Output Format]: https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html
[Section_22.3.5_ _Obtaining Information About Partitions]: https://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html
[Section_13.2.9_ _SELECT Statement]: https://dev.mysql.com/doc/refman/5.7/en/select.html
[Section_8.2.2.1_ _Optimizing Subqueries_ Derived Tables_ and View References with Semijoin Transformations]: https://dev.mysql.com/doc/refman/5.7/en/semijoins.html
[MySQL Internals_ Tracing the Optimizer]: https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
[Section_13.7.2.1_ _ANALYZE TABLE Statement]: https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html
[Section_13.8.1_ _DESCRIBE Statement]: https://dev.mysql.com/doc/refman/5.7/en/describe.html
[Section_13.7.5.5_ _SHOW COLUMNS Statement]: https://dev.mysql.com/doc/refman/5.7/en/show-columns.html
[EXPLAIN PARTITIONS]: https://dev.mysql.com/doc/refman/5.7/en/explain.html
[Tutorial_ Using Explain to Improve Query Performance]: https://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html
[EXPLAIN Output Columns]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-output-columns
[Section_13.2.10.7_ _Correlated Subqueries]: https://dev.mysql.com/doc/refman/5.7/en/correlated-subqueries.html
[Section_8.10.3.1_ _How the Query Cache Operates]: https://dev.mysql.com/doc/refman/5.7/en/query-cache-operation.html
[Section_8.2.2.2_ _Optimizing Subqueries with Materialization]: https://dev.mysql.com/doc/refman/5.7/en/subquery-materialization.html
[Section_8.9.4_ _Index Hints]: https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
[Section_7.6_ _MyISAM Table Maintenance and Crash Recovery]: https://dev.mysql.com/doc/refman/5.7/en/myisam-table-maintenance.html
[Extra Information]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information
[EXPLAIN Join Types]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
[Section_8.2.1.13_ _IS NULL Optimization]: https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html
[Section_8.2.1.3_ _Index Merge Optimization]: https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html
[MySQL_ _]: https://morty.blog.csdn.net/article/details/106273683
[Section_5.1.1_ _Configuring the Server]: https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html
[Section_13.2.10.8_ _Derived Tables]: https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html