Oracle数据库SQL性能优化

1. 选用适合的oracle优化器

了解一下,oracle优化器的种类:3种

• a.  RULE (基于规则)
• b. COST (基于成本)
• c. CHOOSE (选择性)

2. 访问table的方式

oracle采用两种方式访问表中的记录:

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

3. 共享SQL语句

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system globalarea)的共享池(shared bufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

4. 选择最优效率的表名顺序(只在基于规则的优化器中有效)

Oracle的解析器按照从右到左的顺序处理from字句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.

如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.

虽然这种只在基于规则的优化器中有效,但这样写绝对不会错,因为基于其他规则的会首先检索出基础表然后进行首先处理.

5. WHERE字句中的连接顺序 (强烈推荐!!!)

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

6. SELECT子句中避免使用 ‘ * ‘  (强烈推荐!!!)

7. 使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

8. 在删除全表时,用TRUNCATE替代DELETE  (强烈推荐!!!)

这样使用时,而且无需执行COMMIT操作.

9. 选择合适的时机使用COMMIT (强烈推荐!!!)

比如,我在实现某个业务需求,在需要频繁操作数据库的地方,设置每操作500次COMMIT一次.

10. 计算记录条数  (强烈推荐!!!)

和一般的观点相反,count(*)比count(1)稍快,当然如果可以通过索引检索,对索引列的计数仍旧是最快的.例如COUNT(ID).

这个我之前一直错误的以为count(1)是最快的.

11. 用WHERE子句替换HAVING子句  (强烈推荐!!!)

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

HAVING中的条件一般用于对一些集合函数的比较,如COUNT()等等.除此而外,一般的条件应该写在WHERE子句中.

12. 使用表的别名,减少解析的时间  (强烈推荐!!!)

13. 用EXISTS替代IN,用NOT EXISTS替代NOT IN.  (强烈推荐!!!)

14. 通过脚本来识别低效执行的SQL语句.

这种方式,其实我还不是太熟悉.先记录下.

15. 用EXPLAIN PLAN分析SQL语句.  (强烈推荐!!!)

16. 合理的使用索引来提高效率  (强烈推荐!!!)

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

定期重构索引是有必要的.

17. 用UNION替换OR(适用于索引列)  (强烈推荐!!!)

通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.

如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面.

注意: 
WHERE KEY1 = 10  (返回最少记录) OR KEY2 = 20 (返回最多记录)
ORACLE内部将以上转换为:
WHERE KEY1 = 10 AND ((NOT KEY1 = 10) AND KEY2 = 20)

这个我在生产环境中,有应用到,实际效果确实蛮不错的.

18. 用IN替换OR.

19. 总是使用索引的第一个列  (强烈推荐!!!)

如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

20. 用UNION ALL替换UNION (如果有可能的话)   (强烈推荐!!!)

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序.如果用UNION ALL替代UNION,这样排序就不是必要了,效率就会因此得到提高.需要注意的是，UNION ALL将重复输出两个结果集合中相同记录.因此各位还是要从业务需求分析使用UNION ALL的可行性.

21. 需要当心的WHERE字句  (强烈推荐!!!!!)

(1) 索引对不等号和NOT的限制
    如果WHERE条件中出现!=或者<>，即使该列建立了索引，则该索引也不会被使用；如果不恰当的使用了NOT，则索引也不会被使用。
    在下面的例子里, ‘!=’将不使用索引.记住,索引只能告诉你什么存在于表中,而不能告诉你什么不存在于表中.
    不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0;
    使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
    如果索引列是数字，则对于不等号的处理可以变更为NOT的方式或者(大于 OR 小于)的方式.
(2) 下面的例子中, ‘||’是字符连接函数.就象其他函数那样,停用了索引.
    不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
    使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = ‘AMEX’ AND ACCOUNT_TYPE=’ A’;
(3) 索引对函数的限制,避免在索引列上使用计算:
    下面的例子中, ‘+’是数学函数.就象其他数学函数那样,停用了索引.
    不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT + 3000 >5000;
    使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT > 2000 ;
    注意: 对在WHERE子句中经常要使用函数时,应该建立基于函数的索引,且只有当查询语句包含该函数或者表达式时，基于函数的索引才会被调用。
(4) 避免在索引列上使用IS NULL和IS NOT NULL:
    因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引. 
    一般对要建立索引的列不要设置为可空，如果确实含有空值，建议使用默认值代替空值.
(5) 注意通配符%的影响 
    使用通配符的情况下Oracle可能会停用该索引。
(6) 避免改变索引列的类型
    当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
    假设 EMPNO是一个数值类型的索引列.
    SELECT … FROM EMP WHERE EMPNO = ‘123’
    实际上,经过ORACLE类型转换,语句转化为:SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123’)
    幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
    现在,假设EMP_TYPE是一个字符类型的索引列.
    SELECT … FROM EMP WHERE EMP_TYPE = 123
    这个语句被ORACLE转换为:SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123
    因为内部发生的类型转换,这个索引将不会被用到!
(7) 索引的一些“脾气” 
    a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. 
    b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

22. 避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.通常,带有UNION,MINUS ,INTERSECT的SQL语句都可以用其他方式重写.

23. 优化GROUP BY  (强烈推荐!!!)

提高GROUP BY语句的效率,可以通过将不需要的记录在GROUP BY之前过滤掉.

参考文章:

https://www.cnblogs.com/wanghang/p/6299429.html

https://www.cnblogs.com/cxxjohnson/p/5686715.html

https://blog.csdn.net/holandstone/article/details/51473010