全面解析 Msyql Explain 执行计划，优化SQL查询性能

雨点打透心脏的1/2处 2023-10-11 22:08 17阅读 0赞

在 MySQL 中，查询执行计划是指 MySQL 在执行 SQL 查询语句时，优化器生成的用于指导查询引擎执行查询操作和访问数据的一个计划。这个计划包含了查询语句的执行顺序、使用的索引以及关联表等信息，因此它对查询性能的影响非常大。而 `EXPLAIN` 关键字可以帮助我们分析查询执行计划，从而优化查询性能。

### 一、基本语法 ###

在 MySQL 中，使用 `EXPLAIN` 关键字可以分析查询语句的执行计划。其基本语法如下：

EXPLAIN SELECT column1, column2, ... FROM table_name WHERE condition;

其中，`SELECT` 语句是需要分析的查询语句，可以通过 `WHERE` 条件限制查询范围；`EXPLAIN` 关键字用来分析该查询语句的执行计划，输出查询计划的相关信息。下面是一个简单的例子：

EXPLAIN SELECT * FROM users WHERE age > 18;

### 二、输出结果 ###

执行 `EXPLAIN` 关键字后，MySQL 会输出查询语句的执行计划，包括以下几个方面的信息：

1.  `id`: 查询编号，表示查询语句的执行顺序。
2.  `select_type`: 查询类型，表示查询的类型及优化器使用的策略。
3.  `table`: 表名，表示查询时访问的表名称。
4.  `partitions`: 分区，表示查询操作涉及到的分区。
5.  `type`: 访问方式，表示 MySQL 在查找表时使用的读取方式。
6.  `possible_keys`: 可能使用的索引，表示 MySQL 可以使用哪些索引来优化查询。
7.  `key`: 实际使用的索引，表示 MySQL 最终选择哪个索引来优化查询。
8.  `key_len`: 索引长度，表示 MySQL 在使用索引时所需要的长度。
9.  `ref`: 返回匹配条件的列，表示 MySQL 在索引中查找值时使用的比较值。
10. `rows`: 扫描的行数，表示 MySQL 检索数据的行数。
11. `filtered`: 过滤比例，表示 MySQL 对检索的数据进行过滤的比例。
12. `Extra`: 其他信息，可能会包含一些有用的辅助信息。

接下来，我们将逐个解释这些查询计划中提供的信息。

#### 1. 查询编号 id ####

`id` 表示了查询语句的执行顺序。在一个查询语句中，不同的操作都会有一个唯一的编号。这个编号为整数类型，表示 MySQL 执行查询操作的顺序。在查询计划中，如果两个操作的编号相同，则表示它们是同一级别的操作。

#### 2. 查询类型 select\_type ####

`select_type` 表示了查询操作的类型。根据查询操作的类型，MySQL 可以使用不同的优化器策略来处理查询语句。常见的几种查询类型如下：

*  `SIMPLE`: 简单查询，不包含子查询或者 UNION 查询。
 *  `PRIMARY`: 主查询，包含多个子查询或者 UNION 查询。
 *  `SUBQUERY`: 子查询，作为其它查询的子查询使用。
 *  `DERIVED`: 派生表，作为其它查询的临时表使用。
 *  `UNION`: UNION 查询。
 *  `UNION RESULT`: UNION 查询的结果集。
 *  `DEPENDENT UNION`: UNION 查询的依赖查询。
 *  `DEPENDENT SUBQUERY`: 依赖子查询，其结果集取决于外层查询。
 *  `MATERIALIZED`: 物化查询，将查询结果先缓存再做后续操作。

可以看到，查询类型分为简单查询和复杂查询两种。其中，复杂查询还可以分为主查询、子查询、派生表以及 UNION 查询等几个子类型。

#### 3. 表名 table ####

`table` 表示了当前执行的操作涉及到的表名称。如果包含多个表，则中间使用逗号 `,` 隔开。

#### 4. 分区 partitions ####

`partitions` 表示当前正在操作的分区，如果查询操作没有涉及到分区，则该字段值为空。否则会显示出查询操作所涉及到的分区名称。

#### 5. 访问方式 type ####

`type` 表示了 MySQL 在查找表时使用的读取方式，也就是访问的方式。常见的几种访问方式如下：

*  `ALL`: 全表扫描，将整个表的数据都读入内存，对于大表来说，这种方式的代价非常大，一般不建议使用。
 *  `index`: 全索引扫描，需扫描整个索引文件，并且需要进行回表操作，从而导致性能低下。
 *  `range`: 范围扫描，只扫描满足查询条件的记录。
 *  `ref`: 索引查找，通过某个索引找到一个或多个值，并访问对应的行。
 *  `eq_ref`: 唯一索引查找，类似 `ref`，区别在于索引本身是唯一的，因此只返回一行数据。
 *  `const`: 常量查找，MySQL 在查询时发现查询条件中有常量值时，直接按常量值进行查询。

常用的访问方式包括 `ref`、`eq_ref`、`range` 和 `index`。这些访问方式都是使用索引进行查找数据的方式，可以有效地提高查询效率。

#### 6. 可能使用的索引 possible\_keys ####

`possible_keys` 表示 MySQL 可以使用哪些索引来优化查询。在查询计划中，可能会有多个索引可以用于查询，这个字段列举了这些索引的名称（多个索引名之间以逗号 `,` 分隔）。MySQL 在执行查询操作时，会根据所提供的查询条件使用其中一个索引。

#### 7. 实际使用的索引 key ####

`key` 表示 MySQL 实际使用的索引。如果查询语句中包含了可用的索引，则 MySQL 将使用其中一个索引以优化查询。这个字段显示了实际使用的索引的名称。

#### 8. 索引长度 key\_len ####

`key_len` 表示 MySQL 在使用索引时所需要的长度。这个长度是以字节为单位计算的，并且包含了被索引字段的所有部分。这个长度对查询性能非常重要，如果这个值太大，将会导致查询速度变慢。

#### 9. 返回匹配条件的列 ref ####

`ref` 表示 MySQL 在索引中查找值时使用的比较值。这个比较值是查询条件中列的值，或者是常量值。如果使用的索引是唯一索引（`eq_ref`），则这个值只有一个。否则，就可能有多个值。

#### 10. 扫描的行数 rows ####

`rows` 表示 MySQL 检索数据的行数。这个值是一个估计值，并不一定非常准确。

#### 11. 过滤比例 filtered ####

`filtered` 表示 MySQL 对检索的数据进行过滤的比例。如果使用了索引，则这个比例表示已经从索引中检索出的行数占总行数的比例。

#### 12. 其他信息 Extra ####

`Extra` 表示其他一些信息，可能包括：

*  `Using index`: 表示 MySQL 使用了覆盖索引，而无需回到表中去查找数据。
 *  `Using where`: 表示 MySQL 使用了 WHERE 条件来过滤数据。
 *  `Using temporary`: 表示 MySQL 使用了临时表。
 *  `Using filesort`: 表示 MySQL 需要使用文件排序来完成查询操作。
 *  `Using join buffer`: 表示 MySQL 使用了连接缓存来加速联接（JOIN）操作。
 *  `Impossible where`: 表示 MySQL 发现查询条件是不可能出现的，因此不需要执行。
 *  `Select tables optimized away`: 表示 MySQL 可以通过优化查询的方式省略某些表。

### 三、参数选项 ###

`EXPLAIN` 关键字还支持一些参数选项，可以帮助我们分析查询计划和优化查询性能。以下是一些常用的参数选项：

#### 1. EXTENDED ####

`EXTENDED` 参数选项将返回更详细的查询执行计划信息，包括扫描的行数、内存使用情况等。在默认情况下，MySQL 只返回一些基本的信息，该参数选项可以使输出更加详细。

EXPLAIN EXTENDED SELECT column1, column2, ... FROM table_name WHERE condition;

例如：

EXPLAIN EXTENDED SELECT name, age FROM users ORDER BY age DESC;

使用 `EXTENDED` 参数输出的结果如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>users</td> 
   <td></td> 
   <td>index</td> 
   <td>NULL</td> 
   <td>age</td> 
   <td>4</td> 
   <td>NULL</td> 
   <td>1000</td> 
   <td>100.00</td> 
   <td>Using index; Using temporary; Using filesort</td> 
  </tr> 
 </tbody> 
</table>

查询语句中使用了 `ORDER BY` 子句，因此 MySQL 使用了临时表和文件排序的方式进行优化。

#### 2. FORMAT ####

`FORMAT` 参数选项可以指定输出的格式，常用的格式有 `JSON` 和 `XML` 两种。

EXPLAIN FORMAT=JSON SELECT column1, column2, ... FROM table_name WHERE condition;

例如：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 18;

使用 `JSON` 格式输出的结果如下：

{
        
      "query_block": {
        
        "select_id": 1,
        "cost_info": {
        
          "query_cost": "0.10"
        },
        "table": {
        
          "table_name": "users",
          "access_type": "range",
          "possible_keys": [
            "age"
          ],
          "key": "age",
          "used_key_parts": [
            "age"
          ],
          "key_length": "4",
          "rows_examined_per_scan": 20,
          "rows_produced_per_join": 20,
          "filtered": "100.00",
          "index_condition": "(`users`.`age` > 18)"
        }
      }
    }

#### 3. PARTITIONS ####

`PARTITIONS` 参数选项可以帮助我们分析查询操作涉及到的分区。在查询计划中，`partitions`字段可以显示出查询操作所涉及的分区名称。使用 `PARTITIONS` 参数选项可以让 MySQL 输出更多分区相关的信息。

EXPLAIN PARTITIONS SELECT column1, column2, ... FROM table_name PARTITION (p1,p2...) WHERE condition;

例如：

EXPLAIN PARTITIONS SELECT * FROM orders WHERE date >= '2022-01-01' AND date < '2022-02-01';

使用 `PARTITIONS` 参数输出的结果如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>orders</td> 
   <td>p202201</td> 
   <td>range</td> 
   <td>date</td> 
   <td>date</td> 
   <td>3</td> 
   <td>const</td> 
   <td>10</td> 
   <td>100.00</td> 
  </tr> 
 </tbody> 
</table>

结果显示，查询操作涉及到了名为 `p202201` 的分区表。

#### 4. ANALYZE ####

`ANALYZE` 参数选项可以强制 MySQL 对查询操作进行实际的执行，从而获取更准确的查询计划信息。如果没有使用 `ANALYZE` 参数，则 MySQL 可能会基于统计信息来做出一些估算。

EXPLAIN ANALYZE SELECT column1, column2, ... FROM table_name WHERE condition;

例如：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 18;

使用 `ANALYZE` 参数输出的结果中包含了实际执行查询的时间和 I/O 统计信息，如下所示：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
   <th>cost</th> 
   <th>analyzed_time</th> 
   <th>duration</th> 
   <th>sampled_pages</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>users</td> 
   <td></td> 
   <td>range</td> 
   <td>age</td> 
   <td>age</td> 
   <td>4</td> 
   <td>NULL</td> 
   <td>20</td> 
   <td>100.00</td> 
   <td>Using where</td> 
   <td>0.10</td> 
   <td>11.796041488647</td> 
   <td>0.000464</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

结果显示，执行该查询的实际执行时间为 `analyzed_time` 字段所示，I/O 消耗的页数为 `sampled_pages` 字段所示。

### 四、示例 ###

下面举几个不同类型的查询语句的查询计划输出结果，以帮助读者更好地理解 `EXPLAIN` 关键字的用法。

#### 1. 简单查询 ####

EXPLAIN SELECT * FROM users WHERE age > 18;

输出结果：

该查询操作为简单查询（`SIMPLE`），使用了范围扫描（`range`）方式进行查询。MySQL 可能使用了 `age` 索引来优化查询，而实际上确实使用了该索引（`age`）。需要扫描的行数为 20 行，没有涉及分区的相关信息。

#### 2. 复杂查询（主查询） ####

EXPLAIN SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

输出结果：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>PRIMARY</td> 
   <td>users</td> 
   <td></td> 
   <td>ALL</td> 
   <td>age</td> 
   <td>null</td> 
   <td>null</td> 
   <td>null</td> 
   <td>1000</td> 
   <td>10.00</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>SUBQUERY</td> 
   <td></td> 
   <td></td> 
   <td>const</td> 
   <td></td> 
   <td></td> 
   <td>4</td> 
   <td>const</td> 
   <td>1</td> 
   <td>100.00</td> 
  </tr> 
 </tbody> 
</table>

该查询操作为复杂查询，包含一个子查询（`SUBQUERY`）。这个查询语句中使用了 `AVG` 函数来计算平均值，并且使用该平均值作为主查询的查询条件。在查询计划中，MySQL 将主查询和子查询分别分配了不同的查询编号。主查询使用了全表扫描（`ALL`）方式，子查询使用了常量查找（`const`）方式。

#### 3. JOIN 查询 ####

EXPLAIN SELECT users.name, orders.order_number FROM users JOIN orders ON users.id = orders.user_id;

输出结果：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>users</td> 
   <td></td> 
   <td>ALL</td> 
   <td>PRIMARY</td> 
   <td>null</td> 
   <td>null</td> 
   <td>null</td> 
   <td>1000</td> 
   <td>100.00</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>orders</td> 
   <td></td> 
   <td>ref</td> 
   <td>user_id</td> 
   <td>user_id</td> 
   <td>4</td> 
   <td>dbname.users.id</td> 
   <td>5</td> 
   <td>100.00</td> 
  </tr> 
 </tbody> 
</table>

该查询为 JOIN 查询，使用了 `JOIN` 关键字将两个表 `users` 和 `orders` 进行联接。在查询计划中，MySQL 首先使用了全表扫描（`ALL`）方式扫描 `users` 表，然后使用索引查找（`ref`）方式查找 `orders` 表中的相关记录。需要扫描的行数比较少，分别为 1000 和 5 行。

#### 4. ORDER BY 和 GROUP BY 查询 ####

EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name ORDER BY name;

输出结果：

该查询为 GROUP BY 查询，使用了 `GROUP BY` 关键字，按照 `name` 列进行分组，并对分组后的结果进行 `COUNT(*)` 统计。此外，还有一个 `ORDER BY` 子句，按 `name` 的字母顺序排序。在查询计划中，MySQL 使用了索引查找（`index`）方式来进行查询，并且需要扫描 1000 行记录。

### 五、高级特性 ###

除了上述可选参数以外，`EXPLAIN` 还支持一些高级特性，可以通过在查询语句中使用特定的注释来启用这些特性。这些特性主要包括以下几种：

#### 1. STRAIGHT\_JOIN ####

`STRAIGHT_JOIN` 可以强制 MySQL 使用连接表的顺序。

例如：

EXPLAIN SELECT * FROM orders STRAIGHT_JOIN users ON orders.user_id = users.id;

使用 `STRAIGHT_JOIN` 注释启用该特性后，MySQL 将按照指定的顺序进行连接操作。

#### 2. SQL\_NO\_CACHE ####

`SQL_NO_CACHE` 可以让 MySQL 不缓存查询结果，每次都强制重新执行查询操作。

例如：

EXPLAIN SELECT SQL_NO_CACHE * FROM users WHERE age > 18;

使用 `SQL_NO_CACHE` 注释启用该特性后，MySQL 不会缓存查询结果。

#### 3. SQL\_CALC\_FOUND\_ROWS ####

`SQL_CALC_FOUND_ROWS` 可以在执行查询操作的同时获取总记录数，有效地避免了多次查询。

例如：

EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 18 LIMIT 10;

使用 `SQL_CALC_FOUND_ROWS` 注释启用该特性后，在查询操作的结果中，可以额外输出一个 `rows_examined` 字段，表示扫描的记录数，以及一个 `rows_founds` 字段，表示满足条件的总记录数。

### 六、性能优化 ###

通过使用 `EXPLAIN` 关键字，我们可以深入了解查询语句的执行过程，并发现其中的瓶颈和改进空间，从而优化查询性能，提升数据库系统的整体运行效率。

下面是一些常见的性能优化技巧：

#### 1. 使用索引 ####

在设计表结构时，可以通过创建索引来提高查询效率。可以使用 `EXPLAIN` 查看查询操作是否使用了索引，以及是否使用最优的索引；如果没有使用索引或者使用了不合适的索引，可以考虑为相应的列添加新的索引。

#### 2. 避免全表扫描 ####

全表扫描会消耗大量的 I/O 资源，导致查询效率下降。可以优化查询条件，尽可能地使用索引或者其他方式（如分区表）来避免全表扫描。

#### 3. 减少临时表和文件排序 ####

排序操作通常需要使用临时表和文件排序，会消耗大量的 CPU 和 I/O 资源，降低查询效率。可以通过优化查询条件、增加合适的索引、调整查询顺序等方式来减少排序操作的出现。

#### 4. 避免子查询 ####

子查询通常涉及到多次查询操作，会增加数据库系统的负担，导致查询效率下降。可以通过使用 JOIN 操作、合理使用索引等方式来避免子查询的出现。

#### 5. 避免隐式类型转换 ####

在查询操作中，经常会涉及到不同类型之间的比较，比如字符串和数字之间的比较。如果 MySQL 需要进行隐式类型转换，会导致查询效率下降。可以使用 CAST 或者 CONVERT 函数来显式转换数据类型，避免隐式类型转换的出现。

### 总结 ###

本文详细讲解了 MySQL 中的 `EXPLAIN` 关键字，包括其基本用法、输出结果的各个字段含义、可选参数、更多高级特性以及性能优化等相关内容。使用 `EXPLAIN` 关键字可以深入了解查询语句的执行过程，发现其中的瓶颈和改进空间，从而优化查询性能，提升数据库系统的整体运行效率。在实际应用中，我们应当密切关注查询语句的执行情况，不断改进优化，提高系统性能和稳定性。