Oracle数据库SQL性能优化

简介: 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

目录
打赏
0
0
0
0
4
分享
相关文章
第二篇:关系型数据库的核心概念与 SQL 基础
本篇内容深入浅出地讲解了关系型数据库的核心概念与SQL基础,适合有一定计算机基础的学习者。文章涵盖数据库的基本操作(CRUD)、数据类型、表的创建与管理等内容,并通过实例解析SELECT、INSERT、UPDATE、DELETE等语句的用法。此外,还推荐了多种学习资源与实践建议,帮助读者巩固知识。学完后,你将掌握基础数据库操作,为后续高级学习铺平道路。
129 1
数据库数据恢复——sql server数据库被加密的数据恢复案例
SQL server数据库数据故障: SQL server数据库被加密,无法使用。 数据库MDF、LDF、log日志文件名字被篡改。 数据库备份被加密,文件名字被篡改。
GitHub 热门!MindsDB 破解 AI + 数据库瓶颈,究竟有什么惊艳亮点?只需 SQL 即可实现智能预测
MindsDB 是一款将 AI 能力直接注入数据库的开源工具,支持 MySQL、PostgreSQL 等多种数据库连接,通过 SQL 即可完成模型训练与预测。它提供 AutoML 引擎、LLM 集成、联邦查询等功能,简化 MLOps 流程,实现数据到智能的无缝衔接。项目在 GitHub 上已获 32.4k 星,社区活跃,适用于客户流失预警、推荐系统、情感分析等场景。开发者无需深入模型细节,即可快速构建智能解决方案。项目地址:https://github.com/mindsdb/mindsdb。
111 0
oracle数据恢复—oracle数据库执行错误truncate命令的数据恢复案例
oracle数据库误执行truncate命令导致数据丢失是一种常见情况。通常情况下,oracle数据库误操作删除数据只需要通过备份恢复数据即可。也会碰到一些特殊情况,例如数据库备份无法使用或者还原报错等。下面和大家分享一例oracle数据库误执行truncate命令导致数据丢失的数据库数据恢复过程。
【Oracle】使用Navicat Premium连接Oracle数据库两种方法
以上就是两种使用Navicat Premium连接Oracle数据库的方法介绍,希望对你有所帮助!
654 28
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
【赵渝强老师】Oracle的闪回数据库
Oracle闪回数据库功能类似于“倒带按钮”,可快速将数据库恢复至 earlier 状态,无需还原备份。本文介绍了闪回数据库的使用方法及实战案例:包括设置归档模式、开启闪回功能、记录SCN号、执行误操作后的恢复步骤等。通过具体 SQL 操作演示了如何利用闪回数据库恢复被误删的用户数据。注意,使用此功能前需确保数据库为归档模式。
119 9
【赵渝强老师】Oracle数据库的闪回表
本文介绍了Oracle数据库中的闪回表(Flashback Table)功能,它能够将表的数据快速恢复到特定时间点或系统改变号(SCN),无需备份。文章通过实战示例详细演示了如何使用闪回表恢复数据,包括授权、创建测试表、记录时间与SCN号、删除数据、启用行移动功能、执行闪回操作以及验证恢复结果等步骤。同时,还展示了如何通过触发器禁止插入操作,并在闪回过程中处理触发器的启用问题。文末附有视频讲解,帮助读者更好地理解闪回表的使用方法。
175 10

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问