一文搞懂各种数据库SQL执行计划:MySQL、Oracle等
副标题[/!--empirenews.page--]
执行计划(execution plan,也叫查询计划或者解释计划)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想,我们首先应该查看它的执行计划。本文主要介绍如何在各种数据库中获取和理解执行计划,并给出进一步深入分析的参考文档。 现在许多管理和开发工具都提供了查看图形化执行计划的功能,例如 MySQL Workbench、Oracle SQL Developer、SQL Server Management Studio、DBeaver 等;不过我们不打算使用这类工具,而是介绍利用数据库提供的命令查看执行计划。 我们先给出在各种数据库中查看执行计划的一个简单汇总: MySQL 执行计划 MySQL 中获取执行计划的方法很简单,就是在 SQL 语句的前面加上EXPLAIN关键字: 执行该语句将会返回一个表格形式的执行计划,包含了 12 列信息: 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 选项可以显示实际执行时间等额外的信息: 其中,Nested loop inner join 表示使用嵌套循环连接的方式连接两个表,employees 为驱动表。cost 表示估算的代价,rows 表示估计返回的行数;actual time 显示了返回第一行和所有数据行花费的实际时间,后面的 rows 表示迭代器返回的行数,loops 表示迭代器循环的次数。 Oracle 执行计划 Oracle 中提供了多种查看执行计划的方法,本文使用以下方式: 使用EXPLAIN PLAN FOR命令生成并保存执行计划; 显示保存的执行计划。 首先,生成执行计划: EXPLAIN PLAN FOR命令不会运行 SQL 语句,因此创建的执行计划不一定与执行该语句时的实际计划相同。 该命令会将生成的执行计划保存到全局的临时表 PLAN_TABLE 中,然后使用系统包 DBMS_XPLAN 中的存储过程格式化显示该表中的执行计划。以下语句可以查看当前会话中的最后一个执行计划: 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 提供了查看图形化执行计划的简单方法,这里我们介绍一种通过命令查看的方法: SET STATISTICS PROFILE ON 以上命令可以打开 SQL Server 语句的分析功能,打开之后执行的语句会额外返回相应的执行计划: 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,该步骤预计被执行的次数; (编辑:站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |