ORACLE+SQL优化

199146

已于 2023-05-31 21:29:31 修改

阅读量1.6k

点赞数

文章标签： oracle sql 数据库

于 2023-05-23 15:53:32 首次发布

本文链接：https://blog.csdn.net/qq_58774513/article/details/130809492

版权

ORACLE+SQL优化

1、SQL优化注意的点
2、SQL+INDEX
3、SQL+EXPLAIN

1、SQL优化注意的点

1）SQL书写的规范

###SQL语句执行步骤：
语法分析> 语义分析> 视图转换 >表达式转换> 选择优化器 >选择连接方式 >选择连接顺序 >选择数据的搜索路径 >运行“执行计划”

访问Table的方式

1、全表扫扫描：
全表扫描就是顺序地访问表中每条记录，ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
2、通过ROWID访问表：
ROWID包含了表中记录的物理位置信息，ORACLE采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系，通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。

SGA共享池

1、缓存机制：
Oracle提供对执行过的SQL语句进行高速缓冲的机制。被解析过并且确定了执行路径的SQL语句存放在SGA的共享池中。注意：该SQL不可被改动，否则下一次访问将重新执行解析。

2、放大SGA缓存池：
Oracle执行一个SQL语句之前每次先从SGA共享池中查找是否有缓冲的SQL语句，如果有则直接执行该SQL语句。所以可以通过适当调整SGA共享池大小来达到提高Oracle执行性能的目的。

2）ORACLE的解析执行顺序和表关联顺序

ORACLE+SQL解析顺序

oracle解析是由顺序的，系统会自动的默认排序组合，按照SQL的书写从右往左进行解析，先从最后一个from子句开始，将所有from子句解析之后，会将第一个解析的from子句的检索记录与其他的from子句纪律进行合并。

SQL表关联顺序

sql使用关联也要看表数据量的大小，来确认其位置

-- 假设表A有10W条数据 B只有1条
select  count(*) from A ,B  
select  count(*) from B ,A  -- 时间较短

1.了解关联表查询的基本原理：
在Oracle数据库中，关联查询是通过使用JOIN语句来实现的。JOIN语句可以将两个或更多的表连接起来，并生成一个虚拟表，包含多个表中的数据。JOIN语句的执行顺序通常是先执行FROM子句中的第一张表，然后再依次执行后续的表。例如，以下语句是一种基本的关联查询语句：

SELECT * FROM table1
JOIN table2 ON table1.key = table2.key;

在这个查询中，我们使用了JOIN语句将表table1和table2连接起来。在执行该查询时，Oracle会首先执行FROM子句中的第一张表table1，并根据JOIN语句中的条件将table2与之连接起来。因此，关联表查询的性能往往会受到关联表顺序的影响。

2.调整关联表顺序的方法：
在实际应用中，我们可以根据实际情况来调整关联表的顺序以优化查询性能。以下是一些常用的方法：
（1）根据表的大小和过滤条件进行调整
如果表table1比table2小得多，并且WHERE子句中包含筛选条件，则将table1放在FROM子句的第一个位置通常会更有效率。在这种情况下，首先可以筛选出table1中满足条件的记录，然后再通过JOIN与table2进行连接。
例如：

SELECT * FROM table1
JOIN table2 ON table1.key = table2.key
WHERE table1.column1 = ‘value’;

（2）根据索引进行调整
索引是提高查询性能的重要手段。因此，在关联查询中，我们也可以根据表的索引情况来调整关联表的顺序。通常来讲，将有索引的表放在FROM子句的第一个位置会更有效率。

（3）使用INNER JOIN和OUTER JOIN

INNER JOIN和OUTER JOIN是两种常用的关联方式。INNER JOIN会返回两个表中共有的记录，而OUTER JOIN则会返回两个表中所有的记录，可以直接对缺失值进行处理。

在使用INNER JOIN和OUTER JOIN时，也可以根据实际情况来调整关联表的顺序。通常情况下，将INNER JOIN放在FROM子句中的第一个位置会更有效率，因为INNER JOIN返回的是两个表中共有的记录，可以先通过第一张表筛选出共有的记录，然后实现更快的查询。例如：

SELECT * FROM table1
INNER JOIN table2 ON table1.key = table2.key;

在编写关联查询时，选择合适的关联表顺序是优化查询性能的重要手段。
以上介绍的方法可以帮助我们更好地调整关联表顺序，实现更高效的查询。为此，我们需要充分了解表的大小、过滤条件和索引情况，并根据实际情况进行灵活的调整。
示例代码：

SELECT * FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

对于这个查询，我们可以将INNER JOIN放在FROM子句中的第一个位置，以实现更高效的查询。同时，我们还可以根据实际情况添加WHERE子句来进行更精细的筛选。
例如：

-- 如果可以，尽量不要查sql写*，写到具体的字段
SELECT e.department_id , d.department_id FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

2、SQL+INDEX

索引是为表服务的一种数据结构，默认是B-TREE（Balance Tree, 平衡树）。它可以帮助提高查询速度，但是会占用额外的磁盘空间。

1）B-Tree

B树索引，就是我们最常用的普通索引（传统索引,平衡树二分）。
简单创建方式为：create index test_id_idx on test(id)。

2）oracle存储(段区块)

段区块是 Oracle 数据库的逻辑上的概念。块属于区，区属于段,段就是树的根节点。
1)Oracle 在创建表时，会分配一个段来存储这个表。可以理解为一个表就是一个段。
实际上， Oracle 在创建空表，在没有添加数据时，是不分配逻辑结构的（为了节省更多的空间），只有在添加数据的时候才会分配逻辑结构。当然，不管有没有数据一个表还是一个段。

2)Oracle 数据库是由很多个表组成，表又是由很多数据行来组成。其中数据是存储在块中，块的大小是8k，可以修改，一条数据的占的空间较小，且是完整的存储在块中，所以块可以存储很多的数据，而区是由很多个连续的块组成。 Oracle 在创建好表，添加数据时，会产生一个新的区， Oracle 会往这些新的区中添加数据，这些区由很多个块组成。当这个区用完之后，会接着产生下一个区。

3)块是 Oracle I/O 的最小单位。 I/O 是指从磁盘到内存的过程。数据是存放在磁盘上的。所以通常我们检索一行数据， Oracle 在磁盘中，不可能只调取一行数据，最少是调取一个块，也就是 8k 。比如我要读取的一行数据大小为 100 字节，但是 Oracle 每次读这行数据时，都会读取它所在的块，也就是 8k。

所以数据量小的时候，不需要建索引，建索引反而会耗费更多的查询时间。还有索引是具有顺序性的，当有新数据插入的时候，索引会重新排序，删除和修改则不会有太大的影响，其中删除则是依旧保留这个索引，不会立即回收所占的空间。

3）复合索引的选择顺序

组合索引：
当我们的where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引
按照索引列的个数，索引可以分为单列索引和复合索引。单列索引是基于单个列所建立的索引。复合索引（Composite Indexes），也称为连接索引、组合索引或多列索引，是在某个表中的多个列上建立的索引。复合索引中的列应该以在检索数据的查询中最有意义的顺序出现，但在表中不必是相邻的。若WHERE子句引用了复合索引中的所有列或前导列，则复合索引可以加快SELECT语句的数据检索速度。所以，在复合索引的定义中所使用的列顺序很重要。
1、一般情况下，把最常被访问和选择性较高的列放在前面。
2、复合索引适合于单列条件查询返回多、组合条件查询返回少的场景（and）。

3、SQL+EXPLAIN

1）执行计划相关参数

执行计划的常用列字段解释：

基数（Rows）：Oracle估计的当前操作的返回结果集行数
字节（Bytes）：执行该步骤后返回的字节数
耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）
时间（Time）：Oracle估计的当前操作所需的时间
执行顺序：
根据Operation缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）
同一级如果某个动作没有子ID就最先执行
同一级的动作执行时遵循最上最右先执行的原则

/+index(t idx)/ 强制指定索引
/+use_nl(t t1)/ 强制走nl
/+use_hash(t t1)/ 强制走hash
/+leading(t)/ 强制从t表出发，A LEFT JOIN B , leading(A)
/+parallel(2)/强制开启并行

2）NESTED LOOP JOIN（小表做驱动表）

Oracle及传统关系型数据库的运算分析中，表与表之间的连接方式非常重要。如果SQL选择了错误的连接方式，本来几秒就能出结果的SQL 可能执行一天都执行不完。如果想要快速的定位超大型sql的性能问题，我们就必须深入理解表连接方式。
在取多表关联的结果集时，一般情况下只能是两个表先关联，关联后的结果再和其他表或结果集关联。如果执行计划中出现了filter，这时可以一次性关联多个表，但是大多数IO会影响SQL本身执行效率。
（一）算法介绍
当驱动表每返回一行数据时，这一行数据将通过连接列传值给被驱动表。也就是说驱动表返回多少行数据，被驱动表就要被扫描多少次。嵌套循环可以快速返回两表关联的前几条数据，如果SQL中添加了HINT:first_rows，在两表关联时，优化器将会更倾向选择嵌套循环。

（二）优化思路

1.“嵌套循环被驱动表必须走索引”
如果连接列没有包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次的全表扫描，SQL可能执行不出结果。所以嵌套循环被驱动表必须走索引。如index unique scan或者 index range scan。
通过日常数据分析工作我们不难发现，嵌套循环被驱动表的连接列基数应该很高，如果被驱动表的连接列基数低，那么被驱动表就不应该走索引。这样一来驱动表只能进行全表扫描，但与此同时被驱动表也走不了全表扫描。
2.“两表关联返回少量数据才能走嵌套循环”
如果我们需要返回一百万行数据时，被驱动表会走索引。那么就会产生一百万次回表，而回表一般是单块读，这时候SQL效率极低。所以只有当两表关联返回数据量少时才能走嵌套循环。
3.“更改嵌套循环驱动表”
在执行计划中，离nested loops关键字最近的就是嵌套循环的驱动表。当两表使用外链接关联，如果执行计划走的是嵌套循环，此时将无法更改嵌套循环的驱动表，驱动表只能是外链接主表。
如果外链接表中有过滤条件，那么此时外链接会变成内连接。
例如：

-- leading(驱动表) use_nl(驱动表，被驱动的表)  
select/*+leading(e) use_nl(d,e)*/ * from dept d
left join emp e on d.deptno=e.deptno
where e.sal<800;
这时可指定驱动表。 当HINT指定让从表EMP作为驱动表，执行计划中没有out时，说明SQL已经变为内连接，因为过滤条件已经排除了从表和主表没有关联显示为null的情况。

-- 执行计划 NESTED LOOP，一定要指定走索引，条件允许则加并行 /*+use_nl(a b) leading(b) index(a 索引) parallel(2)*/
-- leading(b) index(a ixd_id) 以b为驱动表，走a表的id字段索引 加两个并行{并行只有加在最外面才会有效}
merge /*+use_nl(a b) leading(b) index(a ixd_id) parallel(2)*/into  a
using(
       select/*+no_index(b)*/ b.* from b
       where create_time between start_date and end_date
) b on (a.id = b.id)
when not matched then
	insert(
	.......
	)values(
	.......
	)
when matched then 
	update
		set  ....... ;
commit;

3）HASH JOIN

哈希连接（HASH JOIN）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。
数据库使用hash连接来连接较大的数据集。优化器在内存中使用两个数据集中较小的一个在连接键上构建哈希表，使用确定性哈希函数在哈希表中指定存储每一行的位置。然后，数据库扫描较大的数据集，探测哈希表以查找满足连接条件的行。对于上述嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，驱动表的连接列上存在索引，走嵌套循环的话量也会很大，执行的效率肯定不高，反而有可能会极大的增加执行时间。为了解决这个问题，于是ORACLE引进了哈希连接。在ORACLE 10g及其以后的版本中，优化器（实际上是CBO，因为哈希连接仅适用于CBO）在解析目标SQL的时候是否考虑哈希连接受限于隐含参数_HASH_JOIN_ENABLED,默认值是TRUE.

1、对于哈希连接的优缺点及适用场景如下：

哈希连接不一定会排序，或者说大多数情况下都不需要排序，hash也不用走索引。
哈希只能用于CBO，而且只能用于等值连接的条件。(驱动表和被驱动表的数据量相差不大)
当两个哈希连接的时候，oracle表之间的连接之哈希连接(Hash Join)，其特点如下：
- 驱动表和被驱动表都是最多只被访问一次。Hash连接是基于吞吐量的操作，在返回大数据量的情况下非常高效。
- 哈希连接的表有驱动顺序，搞错顺序会导致效率很低，数据量较小的做驱动表。
- 哈希表连接的表无需要排序，但是他在做连接之前做哈希运算的时候，会用到HASH_AREA_SIZE来创建哈希表。
- 哈希连接不适用于的连接条件是：不等于<>，大于>，小于<，小于等于<=，大于等于>=，like 。
- 哈希连接索引列在表连接中无特殊要求，与单表情况无异。

2、HASH指定

-- /*+use_hash(A B) leading(B) no_index(A) leading(A) swap_join_inputs(A) parallel(2)*/
-- leading(A) swap_join_inputs(A) 表示A表是被驱动表,走hash就不用走索引，被驱动表 no_index(A)
-- 其中b表是较小的表，从b表出发驱动a表
merge /*+use_hash(a b) leading(b) no_index(a) leading(a) swap_join_inputs(a) parallel(2)*/into  a
using(
       select/*+no_index(b)*/ b.* from b
       where create_time between start_date and end_date
) b on (a.id = b.id)
when not matched then
	insert(
	.......
	)values(
	.......
	)
when matched then 
	update
		set  ....... ;
commit;