一次搞定各种数据库 SQL 执行计划｜原力计划

执行计划（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关键字：

EXPLAINSELECT e.first_name,e.last_name,e.salary,d.department_nameFROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000;

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

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 执行计划中的各个字段的作用：

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

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

EXPLAIN ANALYZESELECT e.first_name,e.last_name,e.salary,d.department_nameFROM 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 中提供了多种查看执行计划的方法，本文使用以下方式：

使用EXPLAIN PLAN FOR命令生成并保存执行计划；显示保存的执行计划。首先，生成执行计划：

EXPLAIN PLAN FORSELECT e.first_name,e.last_name,e.salary,d.department_nameFROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000;

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

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

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 执行计划