Oracle——sql语句执行步骤以及提高sql基本查询效率

基本原理来源：https://www.cnblogs.com/likeju/p/5039128.html

sql语句执行顺序

  • 语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。
  • 语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。
  • 视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。
  • 表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。
  • 选择优化器，不同的优化器(rule : 基于规则/cost:基于成本/choose:选择性优化)一般产生不同的“执行计划。
  • 选择连接方式， ORACLE 有三种连接方式（SID,ServiceName,TNSName），对多表连接 ORACLE 可选择适当的连接方式。
  • 选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。
  • 选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。
  • 运行“执行计划”。

Oracle共享原理

  • 将执行过的SQL语句存放在内存的系统全局区域SGA(system global area)的共享池(shared buffer pool)中，可以被所有的数据库用户共享当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, Oracle就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用。
  • 数据库管理员可以在init.ora中为这个区域设置合适的参数，这个内存区域越大，就可以保留更多的语句，被共享的可能性也越大。也并不是越大越好，应遵守“够用就好”的原则。
  • 当向ORACLE 提交一个SQL语句，ORACLE会首先在共享池中查找相同的语句。需要说明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同（包括空格、换行等）。
  • 共享的语句必须满足三个条件：
  1. 字符级的比较：当前被执行的语句和共享池中的语句必须完全相同。
  2. 两个语句所指的对象必须完全相同。
  3. C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 

Oracle访问Table的方式

a. 全表扫描

全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式进行全表扫描。

b. 通过ROWID访问表

采用基于ROWID的访问方式会提高访问表的效率，ROWID包含了表中记录的物理位置信息。

ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。 通常索引提供了快速访问ROWID的方法，因此基于索引列的查询可以得到性能上的提高。

sql语句单体执行顺序

在了解了SQL语句的运行机制与Oracle共享原理后，我们可以知道SQL语句的书写方式对SQL语句的执行效率有很大的影响。

下面是SQL中Select语句中各个关键字执行的顺序。

SQL语言不同于其他编程语言的最明显特征是处理代码的顺序，在大多数据库语言中，代码按编码顺序被处理。

但在SQL语句中，第一个被处理的子句是FROM，而不是第一出现的SELECT。SQL查询处理的步骤序号：

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list> 
(1) FROM <left_table> 
(3) <join_type> JOIN <right_table> 
(2) ON <join_condition> 
(4) WHERE <where_condition> 
(5) GROUP BY <group_by_list> 
(6) WITH {CUBE | ROLLUP} 
(7) HAVING <having_condition> 
(10) ORDER BY <order_by_list>

以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。

这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。

逻辑查询处理阶段简介：

1、 FROM：对FROM子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。表名执行顺序是从后往前，所以数据较少的表尽量放后。
2、 ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。
3、 OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。
如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表位置。
4、 WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。执行顺序为从前往后或者说从左到右。
5、 GROUP BY：按GROUP BY子句中的列列表对TV4中的行进行分组，生成TV5。执行顺序从左往右分组。
6、 CUTE|ROLLUP：把超组插入VT5，生成VT6。
7、 HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。Having语句很耗资源，尽量少用　　 
8、 SELECT：处理SELECT列表，产生VT8。
9、 DISTINCT：将重复的行从VT8中删除，产品VT9。
10、ORDER BY：将VT9中的行按ORDER BY子句中的列列表顺序，生成一个游标(VC10)。执行顺序从左到右，是一个很耗资源的语句。
11、TOP：从VC10的开始处选择指定数量或比例的行，生成表TV11，并返回给调用者。

看到这里，应该是清楚了整个SQL语句整个执行的过程，那么我们就接下来进一步要坐得就是在实现功能同时有考虑性能的思想，努力提高SQL的执行效率。

 sql语句优化技巧

1.选择最优效率的表名顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。

在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。

当ORACLE处理多个表时，会运用排序及合并的方式连接它们。

首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例：表数据少的表当基础表。

  • A表anameid 1000条数据 ； B表bnameid 1条数据 ；nameid连接

sql语句：

select count(*) from a
left join b on a.nameid = b.nameid
或 select count(*) from tab1,tab2
优于：
select count(*) from b
left join a on a.nameid = b.nameid
或 select count(*) from tab2,tab1
  • A表anameid 1000条数据 ； B表bnameid 1条数据 ；C表为连接表 nameid连接。
高效语句：
SELECT * 
FROM A,B,C
WHERE A.NAMEID = C.NAMEID
AND C.NAMEID = B.NAMEID
低效语句：
SELECT * 
FROM C,B,A
WHERE A.NAMEID = C.NAMEID
AND C.NAMEID = B.NAMEID

2.WHERE子句中的连接顺序

ORACLE采用自下而上,从左到右的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

3.SELECT子句中避免使用 "*"

在SELECT子句中列出所有的COLUMN时，使用*是一个方便的方法。但这是一个非常低效的方法。

因为ORACLE在解析的过程中，会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，将耗费更多的时间。

4. 减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。

例子：查询A表nameid ='22' B表nameid= ’33‘的记录

--低效：
select nameid from A a where a.nameid = '22'
union all
select nameid from B b where b.nameid = '33'
--高效：
select nameid from A,B
WHERE A.NAMEID = '22' OR B.NAMEID = '33'

5.  用TRUNCATE替代DELETE

[使用提示：TRUNCATE仅适用于全表删除]

当删除表中的记录时，在通常情况下,，回滚段(rollback segments ) 用来存放可以被恢复的信息。如果执行了ROLLBACK操作，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 。

而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

6.尽量可能的多使用COMMIT

[使用提示：业务允许的情况下尽量少使用事务，能不用尽量不用]
只要有可能，在程序中应尽量多使用COMMIT，这样程序的性能会得到提高，对于资源的需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:

  • 回滚段上用于恢复数据的信息.
  • 被程序语句获得的锁
  • redo log buffer 中的空间
  • ORACLE为管理上述3种资源中的内部花费

在COMMIT时必须要考虑事务的完整性，现实中效率和事务完整性往往不可兼得的。避免无任何意义的开启事务。

7.用Where子句替换HAVING子句

[使用提示：HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等。除此而外，限制条件必须写在WHERE子句中。]

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。

尽量通过WHERE子句来限制记录的数目,达到减少这方面开销的目的。

8.减少对表的查询

在含有子查询的SQL语句中,要特别注意减少对表的查询。

例:

select codeid,codename from dict_code c 
where c.codeid =(select codeid from dict_code1 c1 where c1.codeid = '123')  
and c.codename = (select codename from dict_code1 c1 where c1.code='123')
高效写法：
select codeid,codename from dict_code c 
where c.codeid，codename  =(select codeid,codename from dict_code1 c1 where c1.codeid = '123')

9.使用表的别名(Alias)

[使用提示：必须使用别名，禁止使用表名]
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。

可以减少解析的时间并减少那些由Column歧义引起的语法错误。

Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属 。

10.巧用EXISTS替代IN

EXISTS里的子查询结果集非空，EXISTS()子句的值就是true。
EXISTS里的子查询结果集为空，EXISTS()子句的值就是false。
not exists 反之

语法：
1.判空查询

select * from dict_item a 
where exists(select * from dict_item_charge b where b.itemname = 'sss')

子查询为空时无数据，不为空时查询全部数据。

2.关联查询

select * from dict_item a 
where exists(select * from dict_item_charge b where a.itemid = b.itemid and b.itemid = 'sss')

查询dict_item 中含有dict_item_charge的itemid的数据。
功能上相当于：

select * from dict_Item a 
where a.itemid in(select itemid from dict_Item_charge b where b.itemid = 'sss')

和in的区别
in是先执行子查询，再将子查询的结果集用于父查询。
子查询的查询条件不依赖于夫查询，这类子查询称为不相关子查询。

select * from t1 a where a.id in (select b.id from t2 b)
执行过程：
select * from t1 a
left join (select distinct id from t2 b) on t1.id = t2.id

select * from t1 a where exists (select * from t2 b where t1.id = t2.id)
执行过程：
for i in(select * from t1)
loop
if(exists(select * from t2 b where b.id = i.id ))
then
OUTPUT THE RECORD
end if;
end loop;

由上可知：
当子查询（select id from t2）的结果集很大，相对于主表（select * from t1）结果集较小，那么先执行主表速度较快，即使用exists查询。
当子查询结果集较小时，就该使用in。

11.用NOT EXISTS替代NOT IN

在子查询中，NOT IN子句将执行一个内部的排序和合并。

无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。

为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

12. 用EXISTS替换DISTINCT 

    当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换，因为EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

SELECT DISTINCT DEPT_NO,DEPT_NAME 
FROM DEPT D,EMP E 
WHERE D.DEPT_NO = E.DEPT_NO    低效

SELECT DEPT_NO,DEPT_NAME 
FROM DEPT D 
WHERE EXISTS ( SELECT *
FROM EMP E 
WHERE E.DEPT_NO = D.DEPT_NO);    高效

13.识别低效率的sql

--用下列SQL语句找出低效SQL: 
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, 
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
SQL_TEXT 
FROM V$SQLAREA 
WHERE EXECUTIONS>0 
AND BUFFER_GETS > 0 
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY 4 DESC;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

自强不息，厚德载物
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zhoufei2514/p/10749717.html