Oracle中执行SQL过程

最新推荐文章于 2024-07-10 11:31:50 发布

凉拌大橙子

最新推荐文章于 2024-07-10 11:31:50 发布

阅读量2.3k

点赞数 23

分类专栏：数据库基础文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/qq_15765269/article/details/131576914

版权

数据库基础专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1 Oracle中执行SQL过程

SQL执行过程的图大同小异，我们今天主要说执行器，因为执行器中执行计划是非常重要的，它可以辅助我们继续SQL优化，检查SQL语句BUG。

SQL解析：将SQL语句进行语法解析，确定语句的语义和结构。
查询优化：优化器根据SQL语句和数据库的统计信息，选择最优的执行计划。
执行计划生成：根据查询优化产生的最优执行计划，生成执行计划树。
执行计划执行：按照执行计划树的顺序，执行各个操作步骤，包括表扫描、索引扫描、排序、聚合等。
结果返回：将执行结果返回给客户端。
监控和调优：根据执行计划的执行情况，对性能进行监控和调优，如调整参数、优化SQL语句等。

在这里插入图片描述

2 Oracle执行计划详解（重点）

2.1 如何查询执行计划

目前一般开发人员【不包含DBA和资深数据开发人员】使用最多的就是PLSQL，而PLSQL本身自带查看执行计划的按钮，所以不过多说明。此处用NAVICAT说明：
第一步：EXPLAIN PLAN FOR
第二部：写sql语句
第三步：SELECT * FROM TABLE(dbms_xplan.display);
如下图：
在这里插入图片描述

2.2 执行计划字段含义

通过上图查看执行计划表可以发现一些字段，我们逐个进行解释：

ID：查询语句中每个操作的唯一标识符，可以通过ID来查看操作之间的关系。
Operation：查询语句中每个操作的类型，例如Full Table Scan、Index Scan等。
Name：所涉及的表或索引的名称。
Rows：操作所设计的行数。
Bytes：执行SQL对应步骤返回的字节数。
Cost：执行耗费CPU成本和I/O成本。
Time：执行sql步骤所需要的时间

2.3 Operation操作类型总结

在上述字段中，可能只有一个非常陌生：Operation，这就详细说明一下它的所有分类：

2.3.1 查表

全表扫描（TABLE ACCESS FULL）
全表扫描时从第一个区(EXTENT)的第一个块(BLOCK)开始扫描，一直扫描的到表的高水位线(High Water Mark)，这个范围内的数据块都会扫描到。
全表扫描慢是针对数据量很多的情况，数据量少的话，全表扫描并不慢的，不过随着数据量越多，高水位线也就越高，也就是说需要扫描的数据库越多，自然扫描所需要的IO越多，时间也越多。
ROWID扫描（TABLE ACCESS BY ROWID）
ROWID也就是表数据行所在的物理存储地址，所谓的ROWID扫描是通过ROWID所在的数据行记录去定位。
ROWID是一个伪列，数据库里并没有这个列，它是数据库查询过程中获取的一个物理地址，用于表示数据对应的行数。

2.3.2 索引

索引唯一扫描（INDEX UNIQUE SCAN）
索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)来说的，也就是建立唯一性索引才能索引唯一性扫描，唯一性扫描，其结果集只会返回一条记录。
索引范围扫描（INDEX RANGE SCAN）
适用于所有类型的B树索引，一般不包括唯一性索引，因为唯一性索引走索引唯一性扫描。当扫描的对象是非唯一性索引的情况，where谓词条件为Between、=、<、>等等的情况就是索引范围扫描，注意，可以是等值查询，也可以是范围查询。如果where条件里有一个索引键值列没限定为非空的，那就可以走索引范围扫描，如果改索引列是非空的，那就走索引全扫描**
在同等条件，索引范围扫描所需要的逻辑读至少会比相应的唯一性扫描的逻辑读多1
索引全扫描（INDEX FULL SCAN）
适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。
索引全扫描是指扫描目标索引所有叶子块的索引行，但不意思着需要扫描所有的分支块，索引全扫描时只需要访问必要的分支块，然后定位到位于改索引最左边的叶子块的第一行索引行，就可以利用改索引叶子块之间的双向指针链表，从左往右依次顺序扫描所有的叶子块的索引行。
索引快速全扫描（INDEX FAST FULL SCAN）
索引快速全扫描和索引全扫描很类似，也适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。和索引全扫描类似，也是扫描所有叶子块的索引行，这些都是索引快速全扫描和索引全扫描的相同点。
索引快速全扫描和索引全扫描区别：

索引快速全扫描只适应于CBO(基于成本的优化器)
索引快速全扫描可以使用多块读，也可以并行执行
索引全扫描会按照叶子块排序返回，而索引快速全扫描则是按照索引段内存储块顺序返回
索引快速全扫描的执行结果不一定是有序的，而索引全扫描的执行结果是有序的，因为索引快速全扫描是根据索引行在磁盘的物理存储顺序来扫描的，不是根据索引行的逻辑顺序来扫描的

索引跳跃式扫描（INDEX SKIP SCAN）
索引跳跃式扫描(INDEX SKIP SCAN)适用于所有类型的复合B树索引(包括唯一性索引和非唯一性索引)，索引跳跃式扫描可以使那些在where条件中没有目标索引的前导列指定查询条件但是有索引的非前导列指定查询条件的目标SQL依然可以使用跳跃索引

2.3.3 表连接方法

排序合并连接（merge sort join）
merge sort join是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配
嵌套循环连接（Nested loop join）
Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。对于被连接的数据子集较小的情况，nested loop连接是个较好的选择
哈希连接（Hash join）
散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
笛卡尔连接（Cross join）
如果两个表做表连接而没有连接条件，而会产生笛卡尔积，在实际工作中应该尽可能避免笛卡尔积。

2.4 如何看懂执行计划

真正的口诀是：
从上往下，从右往左
但是不太好理解，所以有另一个口诀：
缩进最深的，最先执行
缩进深度相同的，先上后下

所以上图的执行顺序是(以id代替)：5 -> 3-> 4 -> 7 -> 2 -> 6 -> 1 -> 0

2.5 如何优化执行计划

优化就更简单啦，

首先尽量避免出现全表扫描的情况，例如第三步的 TABLE ACCESS FULL ，当然也不绝对，还是要看业务逻辑和表数据量再去决定。
其次要多观察Name字段，主要观察索引情况，索引是可以极大减少查询时间的手段。如果没有走索引，那么此处是不会显示索引ID的，一定要检查自己写的SQL是否没有使用到索引。
Rows主要关注比较大的行数，行数比较大一定要看一下是否笛卡尔积，或者没有加where相关过滤条件，减少行数是必要的优化手段。越是内层的数据越要降低查询行数。
Time需要和前面几个字段联合起来看，不一定时间少就是最好的。
优化需要一定经验，不是简单就可以优化得，但是看懂执行计划很重要。