|
|
51CTO旗下网站
|
|
移动端

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

本文主要介绍如何在各种数据库中获取和理解执行计划,并给出进一步深入分析的参考文档。

作者:编程资源库来源:今日头条|2019-12-25 14:55

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

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

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

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

MySQL 执行计划

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

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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

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

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

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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

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

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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

Oracle 执行计划

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

  • 使用EXPLAIN PLAN FOR命令生成并保存执行计划;
  • 显示保存的执行计划。

首先,生成执行计划:

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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

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

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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》。

SQL Server 执行计划

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

  1. SET STATISTICS PROFILE ON 

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

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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 行代表了整个查询,不执行实际操作。

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

  1. SET STATISTICS PROFILE OFF 

关于 SQL Server 执行计划和 SQL 调优,可以参考 SQL Server 官方文档执行计划。

PostgreSQL 执行计划

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

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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执行计划:MySQL、Oracle等

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

关于 PostgreSQL 的执行计划和性能优化,可以参考 PostgreSQL 官方文档性能提示。

SQLite 执行计划

SQLite 也提供了EXPLAIN QUERY PLAN命令,用于获取 SQL 语句的执行计划:

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

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

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

另外,SQLite 中的简单EXPLAIN也可以用于显示执行该语句的虚拟机指令序列:

一文搞懂各种数据库SQL执行计划:MySQL、Oracle等

关于 SQLite 的执行计划和优化器相关信息,可以参考 SQLite 官方文档解释查询计划。

【编辑推荐】

  1. MySQL数据库ORDER BY优化总结(为排序使用索引)
  2. 我们是怎样打造一款分布式数据库的
  3. MySQL索引八大法则之上篇
  4. 详解Oracle数据库LRU算法:LRU链、脏块与脏LRU链
  5. 面试官:MySQL的可重复读级别能解决幻读问题吗?
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

云架构师修炼手册

云架构师修炼手册

云架构师的必备技能
共3章 | Allen在路上

9人订阅学习

Devops之监控神器Prometheus

Devops之监控神器Prometheus

监控主流
共22章 | 小罗ge11

109人订阅学习

手把手玩转Elasticsearch

手把手玩转Elasticsearch

Chandler_珏瑜
共20章 | Chandler_珏瑜

79人订阅学习

读 书 +更多

网管员必读——网络基础(第2版)

本书是在《网管员必读—网络基础》(第1版)基础上修改而成的。全书共分9章,分别介绍计算机网络概述(修改)、数制(新增)、网络通信基础...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微