【三 （4）数据处理工具之 SQL （根据执行计划进行SQL调优实践）】

文章导航

【一 简明数据分析进阶路径介绍（文章导航）】

一、什么是执行计划

Oracle执行计划是Oracle数据库为了执行某些SQL语句而生成的一系列具体的执行步骤。这些步骤是由Oracle优化器通过考虑具体参数和被访问对象的统计信息等内容来生成的。优化器会从中选择一个最优的执行步骤作为该SQL语句的执行计划。当Oracle执行SQL语句时，它会直接查找相应的执行计划，然后按照该计划读取相关的数据块到缓存中。

二、怎么查看执行计划

1、在sqldeveloper中按F5，可以点击扳手图标设置返回的字段，该方式看到的执行计划是格式化的

2、explain plan for[你的SQL语句;] select * from table(DBMS_XPLAN.DISPLAY());该方式看到的执行计划 并不是格式化的

3、优先关注Cost(耗费)，Cardinality(基数)，CPU cost(CPU耗费)，Depth(纵深)，IO cost(IO耗费)，Access predicates(访问谓词)，Filter predicates(过滤器谓词)字段

a.Cost(耗费)：

约等于CPU耗费和IO耗费的综合所得

a.Cardinality(基数)：

优化器预估筛选出来的行数

a.Depth(纵深)：

执行顺序，数字越大执行就越在前面。也可以通过树状结构看出来。

a.Access predicates(访问谓词)：

使用索引时所使用的筛选条件。组合索引时候，可以看出来使用了某个索引的那几个字段

a.Filter predicates(过滤器谓词)：

不使用索引时所使用的筛选条件。

三、表访问方式

1、TABLE ACCESS FULL

描述：执行全表扫描，即读取表中的所有行。
性能：通常效率较低，尤其是对于大表，因为它需要读取整个表的内容。这通常只在没有合适的索引可用或优化器认为全表扫描更高效时发生。

2、TABLE ACCESS BY INDEX ROWID

描述：通过索引的ROWID来访问表中的数据。这是优化器常用的策略，当能够通过索引快速定位到ROWID时，它会避免全表扫描。
性能：通常比全表扫描更高效，因为数据库可以直接定位到表中的特定行，而不需要扫描整个表。

3、TABLE ACCESS BY LOCAL INDEX

描述：这是通过本地索引（通常是分区索引）访问表中的数据。当表是分区的，并且查询条件可以利用分区索引时，这种访问方法会被使用。
性能：取决于索引的选择性和分区策略。对于分区表，如果查询条件能够限制到较少的分区，那么性能可能会比全表扫描好得多。

四、索引访问方式

1、INDEX UNIQUE SCAN

描述：唯一索引扫描。当查询条件能够唯一确定一行数据时，Oracle 会使用唯一索引扫描来直接定位到该行。
性能：唯一索引扫描通常非常快，因为它直接定位到所需的行，避免了全表扫描。

2、INDEX RANGE SCAN

描述：范围索引扫描。当查询条件涉及一个范围（如使用 BETWEEN、<、> 等操作符）时，Oracle 会使用范围索引扫描来检索多个行。
性能：范围索引扫描通常比全表扫描更快，因为它只检索满足条件的行。然而，如果范围很大，可能仍然需要检索很多行，性能可能会下降。

3、INDEX FULL SCAN

描述：全索引扫描。当查询条件没有使用索引的前导列或查询涉及整个索引范围时，Oracle 会执行全索引扫描，即扫描整个索引。
性能：全索引扫描通常比全表扫描更快，因为它只扫描索引而不是整个表。但是，如果索引很大且不满足查询条件的行很多，性能可能会受到影响。

五、表连接方式

1、NESTED LOOPS

描述：嵌套循环连接。这是最简单的连接算法，其中外层循环遍历一个表（通常是较小的表），内层循环遍历另一个表（通常是较大的表）以找到匹配的行。
性能：对于小表之间的连接，嵌套循环可能是高效的。然而，当涉及到大表时，性能可能会下降，因为它可能需要进行大量的表扫描。

2、HASH JOIN

描述：哈希连接。这种连接类型首先会构建一个表（驱动表）的哈希表，然后扫描另一个表（被连接表），并为该表中的每一行计算哈希值，以在哈希表中查找匹配的行。
性能：哈希连接通常对于等值连接（即连接条件为 =）是高效的，尤其是当连接条件列上有有效的索引时。哈希连接可以处理大量的数据，并且对于大表之间的连接通常比嵌套循环更快。

3、SORT MERGE JOIN

描述：排序合并连接。这种连接类型要求两个表都按照连接条件列进行排序，然后同时扫描这两个表，找到匹配的行。
性能：排序合并连接通常对于非等值连接（如范围连接）或当连接条件列没有有效索引时更有效。然而，由于需要进行排序操作，因此可能会有额外的I/O和CPU开销。

4、INDEX JOIN (如 INDEX NESTED LOOPS)

描述：索引连接。这种连接类型利用被连接表上的索引来加速连接操作。例如，索引嵌套循环连接会利用索引来减少被连接表的扫描行数。
性能：索引连接通常比全表扫描的连接更快，因为它可以显著减少需要检查的行数。然而，性能仍然受到索引的选择性和查询条件的影响。

六、优化器的分类

1、RBO方式(已废弃)：

优化器在分析语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。

a.Rule：

基于规则的方式。

2、CBO方式：

它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给 出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。

a.Choolse：

默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。

b.First Rows：

它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。

c.All Rows:

也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。

4、如何查询当前的优化器

SELECT name, value FROM v$parameter WHERE name LIKE 'optimizer%';

可以看到优化器模式为All Rows

4、注意：

走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full scan)是最好

七、SQL优化基本原则

1、 避免使用SELECT *,常用最小返回原则，只返回需要的字段
2、 oracle解析器按从左往右的顺序处理FROM子句中的表名，建议把小表(记录数少)放在后方
3、 采用自下而上或自右向左的顺序解析WHERE子句。根据这个原理,表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（仅在PRO优化器模式下有效）
4、 使用表的别名可以减少解析的时间并减少那些由Column歧义引起的语法错误。
5、 合理使用索引，避免进行全表扫描，可能导致全表扫描的操作有
a.在索引列上使用where XXX is null或者is not null条件
b.查询没有索引的字段
c.在索引列上使用like条件
d.在索引列上使用<>,!=,not in等not equals等
e.在索引列上使用计算
f.在索引列上使用内置函数to_char()等

八、实践

select a.*,b.*
  from TEST_GOC_FAULT_B_20240304 a
 left join TEST_GOC_FAULT_SUMMARY_20240304 b on a.id=b.fault_id and b.use_flag=1
where a.CREATE_TIME between to_date('2023-04-01', 'YYYY-MM-DD') and to_date('2023-04-30', 'YYYY-MM-DD')
and a.FAULT_ZONE in ('B')
and a.use_flag=1

可以看到两张表的访问方式都是TABLE ACCESS FULL，COST为1339

尝试将表A查询用到的字段创建索引

CREATE INDEX index_CREATE_TIME ON TEST_GOC_FAULT_B_20240304 (CREATE_TIME);  
CREATE BITMAP INDEX  index_FAULT_ZONE ON TEST_GOC_FAULT_B_20240304 (FAULT_ZONE);
CREATE BITMAP INDEX  index_use_flag ON TEST_GOC_FAULT_B_20240304 (use_flag);

可以看到表A的访问方式由TABLE ACCESS FULL变为TABLE ACCESS BY INDEX ROWID，且使用了INDEX RANGE SCAN和位图索引，COST降低为517