**执行计划（execution plan，也叫查询计划或者解释计划）是数据库执行 SQL 语句的具体步骤**，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想，我们首先应该查看它的执行计划。本文主要介绍如何在各种数据库中获取和理解执行计划，并给出进一步深入分析的参考文档。

> 现在许多管理和开发工具都提供了查看图形化执行计划的功能，例如 MySQL Workbench、Oracle SQL Developer、SQL Server Management Studio、DBeaver 等；不过我们不打算使用这类工具，而是介绍利用数据库提供的命令查看执行计划。

我们先给出在各种数据库中查看执行计划的一个简单汇总：

本文使用的示例表和数据可以点击链接[《SQL 入门教程》示例数据库](https://tonydong.blog.csdn.net/article/details/86518676)。

### MySQL 执行计划
MySQL 中获取执行计划的方法很简单，就是在 SQL 语句的前面加上`EXPLAIN`关键字：

```sql
EXPLAIN
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;
```

执行该语句将会返回一个表格形式的执行计划，包含了 12 列信息：

```sql
id|select_type|table|partitions|type  |possible_keys    |key    |key_len|ref                 |rows|filtered|Extra      |
--|-----------|-----|----------|------|-----------------|-------|-------|--------------------|----|--------|-----------|
 1|SIMPLE     |e    |          |ALL   |emp_department_ix|       |       |                    | 107|   33.33|Using where|
 1|SIMPLE     |d    |          |eq_ref|PRIMARY          |PRIMARY|4      |hrdb.e.department_id|   1|     100|           |
```

> MySQL 中的`EXPLAIN`支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。

接下来，我们要做的就是理解执行计划中这些字段的含义。下表列出了 MySQL 执行计划中的各个字段的作用：

|列名| 作用 |
|:--|:--|
| **id** | 语句中 SELECT 的序号。如果是 UNION 操作的结果，显示为 NULL；此时 table 列显示为 <unionM,N>。 |
| **select_type** | SELECT 的类型，包括： - SIMPLE，不涉及 UNION 或者子查询的简单查询； - PRIMARY，最外层 SELECT； - UNION，UNION 中第二个或之后的 SELECT； - DEPENDENT UNION，UNION 中第二个或之后的 SELECT，该 SELECT 依赖于外部查询； - UNION RESULT，UNION 操作的结果； - SUBQUERY，子查询中的第一个 SELECT； - DEPENDENT SUBQUERY，子查询中的第一个 SELECT，该 SELECT 依赖于外部查询； - DERIVED，派生表，即 FROM 中的子查询； - DEPENDENT DERIVED，依赖于其他表的派生表； - MATERIALIZED，物化子查询； - UNCACHEABLE SUBQUERY，无法缓存结果的子查询，对于外部表中的每一行都需要重新查询； - UNION 中第二个或之后的 SELECT，该 UNION属于 UNCACHEABLE SUBQUERY。|
| **table** | 数据行的来源表，也有可能是以下值之一： - <unionM,N>，id 为 M 和 N 的 SELECT 并集运算的结果； - \<derivedN>，id 为 N 的派生表的结果； - \<subqueryN>，id 为 N 的物化子查询的结果。 |
| **partitions** | 对于分区表而言，表示数据行所在的分区；普通表显示为 NULL。 |
| **type** | 连接类型或者访问类型，性能从好到差依次为： - system，表中只有一行数据，这是 const 类型的特殊情况； - const，最多返回一条匹配的数据，在查询的最开始读取； - eq_ref，对于前面的每一行，从该表中读取一行数据； - ref，对于前面的每一行，从该表中读取匹配索引值的所有数据行； - fulltext，通过 FULLTEXT 索引查找数据； - ref_or_null，与 ref 类似，额外加上 NULL 值查找； - index_merge，使用索引合并优化技术，此时 key 列显示使用的所有索引； - unique_subquery，替代以下情况时的 eq_ref：`value IN (SELECT primary_key FROM single_table WHERE some_expr)`； - index_subquery，与 unique_subquery 类似，用于子查询中的非唯一索引：`value IN (SELECT key_column FROM single_table WHERE some_expr)`； - range，使用索引查找范围值； - index，与 ALL 类型相同，只不过扫描的是索引； - ALL，全表扫描，通常表示存在性能问题。 |
| **possible_keys** | 可能用到的索引，实际上不一定使用。 |
| **key** | 实际使用的索引。 |
| **key_len** | 实际使用的索引的长度。 |
| **ref** | 用于和 key 中的索引进行比较的字段或者常量，从而判断是否返回数据行。 |
| **rows** | 执行查询需要检查的行数，对于 InnoDB 是一个估计值。 |
| **filtered** | 根据查询条件过滤之后行数百分比，rows × filtered 表示进入下一步处理的行数。 |
| **Extra** | 包含了额外的信息。例如 Using temporary 表示使用了临时表，Using filesort 表示需要额外的排序操作等。 |

对于上面的示例，只有一个 SELECT 子句，id 都为 1；首先对 employees 表执行全表扫描（type = ALL），处理了 107 行数据，使用 WHERE 条件过滤后预计剩下 33.33% 的数据（估计不准确）；然后针对这些数据，依次使用 departments 表的主键（key = PRIMARY）查找一行匹配的数据（type = eq_ref、rows = 1）。

使用 MySQL 8.0 新增的 ANALYZE 选项可以显示实际执行时间等额外的信息：

```sql
EXPLAIN ANALYZE
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;
-> Nested loop inner join  (cost=23.43 rows=36) (actual time=0.325..1.287 rows=3 loops=1)
    -> Filter: ((e.salary > 15000.00) and (e.department_id is not null))  (cost=10.95 rows=36) (actual time=0.281..1.194 rows=3 loops=1)
        -> Table scan on e  (cost=10.95 rows=107) (actual time=0.266..0.716 rows=107 loops=1)
    -> Single-row index lookup on d using PRIMARY (department_id=e.department_id)  (cost=0.25 rows=1) (actual time=0.013..0.015 rows=1 loops=3)
```

其中，Nested loop inner join 表示使用嵌套循环连接的方式连接两个表，employees 为驱动表。cost 表示估算的代价，rows 表示估计返回的行数；actual time 显示了返回第一行和所有数据行花费的实际时间，后面的 rows 表示迭代器返回的行数，loops 表示迭代器循环的次数。

关于 MySQL EXPLAIN 命令的使用和参数，可以参考 MySQL 官方文档[ EXPLAIN 语句](https://dev.mysql.com/doc/refman/8.0/en/explain.html)。

关于 MySQL 执行计划的输出信息，可以参考 MySQL 官方文档[理解查询执行计划](https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html)。

### Oracle 执行计划
Oracle 中提供了多种查看执行计划的方法，本文使用以下方式：

1. 使用`EXPLAIN PLAN FOR`命令生成并保存执行计划；
 2. 显示保存的执行计划。

首先，生成执行计划：

```sql
EXPLAIN PLAN FOR
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;
```

> `EXPLAIN PLAN FOR`命令不会运行 SQL 语句，因此创建的执行计划不一定与执行该语句时的实际计划相同。

该命令会将生成的执行计划保存到全局的临时表 PLAN_TABLE 中，然后使用系统包 DBMS_XPLAN 中的存储过程格式化显示该表中的执行计划。以下语句可以查看当前会话中的最后一个执行计划：

```sql
SELECT * FROM TABLE(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT                                                                           |
--------------------------------------------------------------------------------------------|
Plan hash value: 1343509718                                                                 |
                                                                                            |
--------------------------------------------------------------------------------------------|
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     ||
--------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT             |             |    44 |  1672 |     6  (17)| 00:00:01 ||
|   1 |  MERGE JOIN                  |             |    44 |  1672 |     6  (17)| 00:00:01 ||
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 ||
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 ||
|*  4 |   SORT JOIN                  |             |    44 |   968 |     4  (25)| 00:00:01 ||
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |    44 |   968 |     3   (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------|
                                                                                            |
Predicate Information (identified by operation id):                                         |
---------------------------------------------------                                         |
                                                                                            |
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      |
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      |
   5 - filter("E"."SALARY">15000)                                                           |
```

> Oracle 中的`EXPLAIN PLAN FOR`支持 SELECT、UPDATE、INSERT 以及 DELETE 语句。

接下来，我们同样需要理解执行计划中各种信息的含义：

- **Plan hash value** 是该语句的哈希值。SQL 语句和执行计划会存储在库缓存中，哈希值相同的语句可以重用已有的执行计划，也就是软解析；
 - **Id** 是一个序号，但不代表执行的顺序。执行的顺序按照缩进来判断，缩进越多的越先执行，同样缩进的从上至下执行。Id 前面的星号表示使用了谓词判断，参考下面的 Predicate Information；
 - **Operation** 表示当前的操作，也就是如何访问表的数据、如何实现表的连接、如何进行排序操作等；
 - **Name** 显示了访问的表名、索引名或者子查询等，前提是当前操作涉及到了这些对象；
 - **Rows** 是 Oracle 估计的当前操作返回的行数，也叫基数（Cardinality）；
 - **Bytes** 是 Oracle 估计的当前操作涉及的数据量
 - **Cost (%CPU)** 是 Oracle 计算执行该操作所需的代价；
 - **Time** 是 Oracle 估计执行该操作所需的时间；
 - **Predicate Information** 显示与 Id 相关的谓词信息。access 是访问条件，影响到数据的访问方式（扫描表还是通过索引）；filter 是过滤条件，获取数据后根据该条件进行过滤。

在上面的示例中，Id 的执行顺序依次为 3 -> 2 -> 5 -> 4- >1。首先，Id = 3 扫描主键索引 DEPT_ID_PK，Id = 2 按主键 ROWID 访问表 DEPARTMENTS，结果已经排序；其次，Id = 5 全表扫描访问 EMPLOYEES 并且利用 filter 过滤数据，Id = 4 基于部门编号进行排序和过滤；最后 Id = 1 执行合并连接。显然，此处 Oracle 选择了排序合并连接的方式实现两个表的连接。

关于 Oracle 执行计划和 SQL 调优，可以参考 Oracle 官方文档[《SQL Tuning Guide》](https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/)。

### SQL Server 执行计划
SQL Server Management Studio 提供了查看图形化执行计划的简单方法，这里我们介绍一种通过命令查看的方法：

```sql
SET STATISTICS PROFILE ON
```

以上命令可以打开 SQL Server 语句的分析功能，打开之后执行的语句会额外返回相应的执行计划：

```sql
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;

first_name|last_name|salary  |department_name|
----------|---------|--------|---------------|
Steven    |King     |24000.00|Executive      |
Neena     |Kochhar  |17000.00|Executive      |
Lex       |De Haan  |17000.00|Executive      |

Rows|Executes|StmtText                                                                                                                                                                                           |StmtId|NodeId|Parent|PhysicalOp          |LogicalOp           |Argument                                                                                                                                                           |DefinedValues                                                       |EstimateRows|EstimateIO  |EstimateCPU|AvgRowSize|TotalSubtreeCost|OutputList                                                            |Warnings|Type    |Parallel|EstimateExecutions|
----|--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------|------|------|--------------------|--------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------|------------|------------|-----------|----------|----------------|----------------------------------------------------------------------|--------|--------|--------|------------------|
   3|       1|SELECT e.first_name,e.last_name,e.salary,d.department_name¶  FROM employees e¶  JOIN departments d ON (e.department_id = d.department_id)¶ WHERE e.salary > 15000                                  |     1|     1|     0|                    |                    |                                                                                                                                                                   |                                                                    |   2.9719627|            |           |          |     0.007803641|                                                                      |        |SELECT  |       0|                  |
   3|       1|  |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[department_id]))                                                                                                                              |     1|     2|     1|Nested Loops        |Inner Join          |OUTER REFERENCES:([e].[department_id])                                                                                                                             |                                                                    |   2.9719627|           0|          0|        57|     0.007803641|[e].[first_name], [e].[last_name], [e].[salary], [d].[department_name]|        |PLAN_ROW|       0|                 1|
   3|       1|       |--Clustered Index Scan(OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00)))                                     |     1|     3|     2|Clustered Index Scan|Clustered Index Scan|OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00))                                     |[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id]|           3|0.0038657407|   2.747E-4|        44|     0.004140441|[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id]  |        |PLAN_ROW|       0|                 1|
   3|       3|       |--Clustered Index Seek(OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD)|     1|     4|     2|Clustered Index Seek|Clustered Index Seek|OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD|[d].[department_name]                                               |           1|    0.003125|   1.581E-4|        26|       0.0035993|[d].[department_name]                                                 |        |PLAN_ROW|       0|                 3|
```

> SQL Server 中的执行计划支持 SELECT、INSERT、UPDATE、DELETE 以及 EXECUTE 语句。

SQL Server 执行计划各个步骤的执行顺序按照缩进来判断，缩进越多的越先执行，同样缩进的从上至下执行。接下来，我们需要理解执行计划中各种信息的含义：

- **Rows** 表示该步骤实际产生的记录数；
 - **Executes** 表示该步骤实际被执行的次数；
 - **StmtText** 包含了每个步骤的具体描述，也就是如何访问和过滤表的数据、如何实现表的连接、如何进行排序操作等；
 - **StmtId**，该语句的编号；
 - **NodeId**，当前操作步骤的节点号，不代表执行顺序；
 - **Parent**，当前操作步骤的父节点，先执行子节点，再执行父节点；
 - **PhysicalOp**，物理操作，例如连接操作的嵌套循环实现；
 - **LogicalOp**，逻辑操作，例如内连接操作；
 - **Argument**，操作使用的参数；
 - **DefinedValues**，定义的变量值；
 - **EstimateRows**，估计返回的行数；
 - **EstimateIO**，估计的 IO 成本；
 - **EstimateCPU**，估计的 CPU 成本；
 - **AvgRowSize**，平均返回的行大小；
 - **TotalSubtreeCost**，当前节点累计的成本；
 - **OutputList**，当前节点输出的字段列表；
 - **Warnings**，预估得到的警告信息；
 - **Type**，当前操作步骤的类型；
 - **Parallel**，是否并行执行；
 - **EstimateExecutions**，该步骤预计被执行的次数；

对于上面的语句，节点执行的顺序为 3 -> 4 -> 2 -> 1。首先执行第 3 行，通过聚集索引（主键）扫描 employees 表加过滤的方式返回了 3 行数据，估计的行数（3.0841121673583984）与此非常接近；然后执行第 4 行，循环使用聚集索引的方式查找 departments 表，循环 3 次每次返回 1 行数据；第 2 行是它们的父节点，表示使用 Nested Loops 方式实现 Inner Join，Argument 列（OUTER REFERENCES:([e].[department_id])）说明驱动表为 employees ；第 1 行代表了整个查询，不执行实际操作。

最后，可以使用以下命令关闭语句的分析功能：

```sql
SET STATISTICS PROFILE OFF
```

关于 SQL Server 执行计划和 SQL 调优，可以参考 SQL Server 官方文档[执行计划](https://docs.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver15)。

### PostgreSQL 执行计划
PostgreSQL 中获取执行计划的方法与 MySQL 类似，也就是在 SQL 语句的前面加上`EXPLAIN`关键字：

```sql
EXPLAIN
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;

QUERY PLAN                                                            |
----------------------------------------------------------------------|
Hash Join  (cost=3.38..4.84 rows=3 width=29)                          |
  Hash Cond: (d.department_id = e.department_id)                      |
  ->  Seq Scan on departments d  (cost=0.00..1.27 rows=27 width=15)   |
  ->  Hash  (cost=3.34..3.34 rows=3 width=22)                         |
        ->  Seq Scan on employees e  (cost=0.00..3.34 rows=3 width=22)|
              Filter: (salary > '15000'::numeric)                     |
```

> PostgreSQL 中的`EXPLAIN`支持 SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS 以及 CREATE MATERIALIZED VIEW AS 语句。

PostgreSQL 执行计划的顺序按照缩进来判断，缩进越多的越先执行，同样缩进的从上至下执行。对于以上示例，首先对 employees 表执行全表扫描（Seq Scan），使用 salary > 15000 作为过滤条件；cost 分别显示了预估的返回第一行的成本（0.00）和返回所有行的成本（3.34）；rows 表示预估返回的行数；width 表示预估返回行的大小（单位 Byte）。然后将扫描结果放入到内存哈希表中，两个 cost 都等于 3.34，因为是在扫描完所有数据后一次性计算并存入哈希表。接下来扫描 departments 并且根据 department_id 计算哈希值，然后和前面的哈希表进行匹配（d.department_id = e.department_id）。最上面的一行表明数据库采用的是 Hash Join 实现连接操作。

PostgreSQL 中的`EXPLAIN`也可以使用 ANALYZE 选项显示语句的实际运行时间和更多信息：

```sql
EXPLAIN ANALYZE
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;

QUERY PLAN                                                                                                      |
----------------------------------------------------------------------------------------------------------------|
Hash Join  (cost=3.38..4.84 rows=3 width=29) (actual time=0.347..0.382 rows=3 loops=1)                          |
  Hash Cond: (d.department_id = e.department_id)                                                                |
  ->  Seq Scan on departments d  (cost=0.00..1.27 rows=27 width=15) (actual time=0.020..0.037 rows=27 loops=1)  |
  ->  Hash  (cost=3.34..3.34 rows=3 width=22) (actual time=0.291..0.292 rows=3 loops=1)                         |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                            |
        ->  Seq Scan on employees e  (cost=0.00..3.34 rows=3 width=22) (actual time=0.034..0.280 rows=3 loops=1)|
              Filter: (salary > '15000'::numeric)                                                               |
              Rows Removed by Filter: 104                                                                       |
Planning Time: 1.053 ms                                                                                         |
Execution Time: 0.553 ms                                                                                        |
```

`EXPLAIN ANALYZE`通过执行语句获得了更多的信息。其中，actual time 是每次迭代实际花费的平均时间（ms），也分为启动时间和完成时间；loops 表示迭代次数；Hash 操作还会显示桶数（Buckets）、分批数量（Batches）以及占用的内存（Memory Usage），Batches 大于 1 意味着需要使用到磁盘的临时存储；Planning Time 是生成执行计划的时间；Execution Time 是执行语句的实际时间，不包括 Planning Time。

关于 PostgreSQL 的执行计划和性能优化，可以参考 PostgreSQL 官方文档[性能提示](https://www.postgresql.org/docs/12/performance-tips.html)。

### SQLite 执行计划
SQLite 也提供了`EXPLAIN QUERY PLAN`命令，用于获取 SQL 语句的执行计划：

```sql
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT e.first_name,e.last_name,e.salary,d.department_name
   ...>   FROM employees e
   ...>   JOIN departments d ON (e.department_id = d.department_id)
   ...>  WHERE e.salary > 15000;
QUERY PLAN
|--SCAN TABLE employees AS e
`--SEARCH TABLE departments AS d USING INTEGER PRIMARY KEY (rowid=?)
```

> SQLite 中的`EXPLAIN QUERY PLAN`支持 SELECT、INSERT、UPDATE、DELETE 等语句。

SQLite 执行计划同样按照缩进来显示，缩进越多的越先执行，同样缩进的从上至下执行。以上示例先扫描 employees 表，然后针对该结果依次通过主键查找 departments 中的数据。SQLite 只支持一种连接实现，也就是 nested loops join。

另外，SQLite 中的简单`EXPLAIN`也可以用于显示执行该语句的[虚拟机指令](https://www.sqlite.org/opcode.html)序列：

```sql
sqlite> EXPLAIN
 ...> SELECT e.first_name,e.last_name,e.salary,d.department_name
 ...> FROM employees e
 ...> JOIN departments d ON (e.department_id = d.department_id)
 ...> WHERE e.salary > 15000;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 15 0 00 Start at 15
1 OpenRead 0 5 0 11 00 root=5 iDb=0; employees
2 OpenRead 1 2 0 2 00 root=2 iDb=0; departments
3 Rewind 0 14 0 00
4 Column 0 7 1 00 r[1]=employees.salary
5 Le 2 13 1 (BINARY) 53 if r[1]<=r[2] goto 13
6 Column 0 10 3 00 r[3]=employees.department_id
7 SeekRowid 1 13 3 00 intkey=r[3]
8 Column 0 1 4 00 r[4]=employees.first_name
9 Column 0 2 5 00 r[5]=employees.last_name
10 Column 0 7 6 00 r[6]=employees.salary
11 Column 1 1 7 00 r[7]=departments.department_name
12 ResultRow 4 4 0 00 output=r[4..7]
13 Next 0 4 0 01
14 Halt 0 0 0 00
15 Transaction 0 0 8 0 01 usesStmtJournal=0
16 Integer 15000 2 0 00 r[2]=15000
17 Goto 0 1 0 00
```

关于 SQLite 的执行计划和优化器相关信息，可以参考 SQLite 官方文档[解释查询计划](https://www.sqlite.org/eqp.html)。

欢迎关注❤️、点赞?、转发?！

ITPub博客

一次搞定各种数据库SQL执行计划：MySQL、Oracle、SQL Server、PostgreSQL以及SQLite

最新文章