Oracle数据库基于索引SQL优化方法的研究与实现

1、Oracle数据库基于索引SQL优化方法的研究与实现第25卷第12期计算机工程与设计COMPUTERENGINEERINGANDDESIGN2004年12月文章编号中图分类号文献标识码:AOracle数据库基于索引SQL优化方法的研究与实现宋彩霞,路新春(山东大学计算机科学与技术学院,山东济南250061)摘要:在系统分析了Oracle数据库索引特点的根底上,要注意的问题进行了总结,进行了一些新的探索和尝试,关键词:Oracle;索引;SQL优化对如何使用SQL语句的索引对数据库进行优化的实现方法中提出了Oracle数据库SQL优化的其它注意问题.Studyandimplementationo

2、foptimizationofSQLaboutoracledatabasebasedonindexesSONGCai-xia,LUXinchun(CollegeofComputerScienceandTechnology,ShandongUniversity,Jinan250061,China)Abstract:Basedontheanalysisofthecharacteristicsoftheoracledatabaseindexes,somequestionsaresummarizedconceminghowotherquestionsrelatedtoSQLoptimizationon

3、oracledatabasearediscussed.Keywords:oracle;index;SQLoptimization1引言本文根据自己在Oracle数据库的学习与工作体会,对Ora-cle数据库基于索引SQL优化方法进行了一些研究,并对合理使用索引的一些方法进行了总结,对某些方法提出了自己的观点.2在表上建立合理的索引索引是表的一个概念局部,用来提高检索数据的效率.Oracle采用两种访问表中记录的方式:全表扫描:全表扫描就是顺序地访问表中每条记录.Oracle采用一次读入多个数据块(databaseblock)的方式优化全表扫描;通过ROWID访问表:ROWID包含了表中记录的物

4、理位置信息Oracle采用索引(DEx)实现了数据和存放数据的物理位置(ROW1D)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.实际上,Oracle使用了一个复杂的自平衡Btree结构.通常,通过索引查询数据比全表扫描要快.当Oracle找出执行查询和UPDATE语句的最正确路径时,Oracle优化器将使用索引.同样在联结多个表时使用索引也可提高效率.另一个使用索引的好处是,它提供了主键的惟性验证.在表上面建立索引是提高对表查询更新速度有效的方法,但是并不是只要建立索引就可提高查询速度,更不是索引建立越多系统性能越高.因为索引需要额外的存

5、储空间和处理,那些不必要的索引反而会使查询反响时间变慢.所以索引的建立要根据实际表的大小与多少,根据实际需要来建立.首先必须熟悉数据库应用程序中所有的语句,并从中统计出常用且可能对性能有影响的局部语句:然后分析,归纳出作为WHERE条件子句的字段及其各种组合方式;在这根底上可初步判断出哪些表的哪些字段应该建立索引.其次,必须熟悉应用程序,要了解哪些表是数据操作频繁的表;哪些表经常与其它表进行连接;哪些表中的数据量可能很大;数据量大的表中各个字段的数据分布情况如何,优化器的选择等.影响索引的因素及建立索引常用的规那么如下.(1)该字段是否为查询关键词,表的主键和外键必须有索引;(2)该字段是否为

6、连接操作字段,对经常与其它表进行连接的表的连接字段应该建立索引;(3)该字段出现在WHERE字句中的频率,经常出现在WHERE子句中的字段应该建立索引(尤其是数据量较大的表的字段);(4)该字段是否是小字段,小字段应该建立索引,对于较长的文本字段甚至超长字段,不要建索引;(5)选择性高的字段应该建立索引.索引的选择性是指索引列里不同值的数目与表中记录数的比.如果表中有1000个记录,表索引列有900个不同的值,那么这个索引的选择性收稿日期:2003.1210.作者简介:宋彩霞(1977.),女,山东即墨人,硕士研究生,研究方向为分布式监控和配电自动化;路新春(1948一),男,山东济南人,副教

7、授,硕士生导师,研究方向为分布式监控和配电自动化.2327就是0.9.最好的可能性选择是1.依据非空值列的惟一索引,通常其选择性为1;(6)对数据库的插入,删除操作的频率.频繁进行插入,删除操作的表,不要建立过多索引:(7)索引的维护代价.删除无用索引,防止对执行方案造成负面影响;(8)复合索引的建立需要经过仔细分析,尽量考虑用单字段索引代替:复合索引中的主列字段,一般是选择选择性较好的字段;复合索引的几个字段是否经常同时以AND方式出现在WHERE子句中?单字段的查询极少甚至没有?如果出现以上两种情况或两者之一,那么应该建立复合索引;否那么考虑单字段索引;如果复合索引中包含的字段经常单独出现

8、在WHERE子句中,那么分解为多个单字段索引;复合索引所包含的字段一般不要超过3个,否那么需要仔细考虑其必要性;如果既有单字段索引,又有包含这几个字段的复合索引,一般可以删除复合索引.建立复合索引的特殊情况:如果表中的数据相当稳定且字段不多,可以考虑充分索引一个表,即创立一个复合索引,它包括所有在查询期间通常会被选择的列,查询所要求的所有数据可以通过索引访问提供,防止了索引扫描随后的任何表访问.3基于索引的sQL优化方法的研究与实现(1)防止在索引列上使用NOT:通常,我们要防止在索引列上使用NOT,当Oracle遇到NOT,它就会停止使用索引转而执行全表扫描.例如:SELECTFROMDEP

9、TWHEREDEPTCODENOT:O;使用的就是全表扫描.需要注意的是,在某些时候,Oracle优化器会自动将NOT转化成相对应的关系操作符:NOT>to<:;NOT>:to<NOT<to>:NOT<:to>.(2)用>:替代>:如果DEPTNO上有一个索引.高效:SELECTFROMEMPWHEREDEPTNO>-4:低效:SELECTFROMEMPWHEREDEPTNO>3:两者的区别在于,前者DBMS将直接跳到第1个DE

10、PT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第1个DEPT大于3的记录.第2个用的时间长.(3)尽量去掉IN或OR,严禁使用NOTIN:含有IN或OR的WHERE子句常会令索引失效;在不产生大量重复值的情况下,可以考虑把子句拆开,拆开的子句中应该包含索引.例:SELECTCOUNT()FROMEMPWHEREEMPIDIN(O.1);可以将子句分开:SELECTCOUNT()FROMEMPWHEREEMPID:0:SELECTCOUNT()FROMEMPWHEREEMPID:|1;然后再做一个简单的加法,与原来的SQL语句相比,查询速度有了明显提高.(4)限制查询范围

11、,减少全范围搜索:例:以下查询表RE.CORD中时间EMPTIME中小于2003年6月1日的数据.?2328?SELECTFROMRECORDWHEREEMPTIME<=TODATE(20030601,YYYYMM);查询方案说明,上面的查询对表进行了全表扫描,如果知道表中最早的数据为2000年1月1日.那么可以增加一个最小时间,保证查询在一个完整的范围之内.SELECTFROMRECORDWHEREEMPTIME<=TODATE(20030601,YYYYMM)ANDEMPTIME>=TODATE(20000101,YYYYMM);后一种SQL语句利

12、用了EMPTIME字段上的索引,从而可以提高查询的效率.把20030601换为一个变量,根据取值的机率,可以证明有5O%以上的机率提高查询效率.同理,对于大于某个值的查询,如果知道当前可能的最大值,也可以在WHERE子句中加上AND列名<最大值来限制查询范围,以提高查询的效率.(5)防止在索引列上使用ISNULL和ISNOTNULL:防止在索引中使用任何可以为空的列,Oracle将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录.对于复合索引,如果每个列都为空,索引中同样不存在此记录.如果至少有一个列不为空,那么记录存在于索引中.(6)总是使用索引的第1个列:如

13、果索引是建立在多个列上,只有在它的第1个列(1eadingcolumn)被WHERE子句引用时,优化器才会选择使用该索引.(7)在ORDERBY中使用索引:ORDERBY子句只在两种严格的条件下使用索引.需要我们格外注意.ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.ORDERBY中所有的列必须定义为非空.WHERE子句使用的索引和ORDERBY子句中所使用的索引不能并列.举例:下面这种情况ORDERBY使用索引:假设EMPNONOTNULL:SELECTFROMEMPORDERBYEMPNO.查询速度提高.但是在下面的情况中ORDERBY中的索引不被用到,为了提高查

14、询速度需要用WHERE代替ORDERBY.例如:表DEPT包含以以下:DEPTCODEPKNOTNULL;DEPT_DESCNOTNULL:DEPTTYPENULL.非唯一性的索引(DEPTTYPE).低效:(索引不被使用)SELECTDEPCjODEFROMDEPTORDERBYDETYPE.高效:(使用索引)SELECTDEPTCODEFROMDEPTWHEREDEP臃>0.ORDERBY也能使用索引.这是个容易被无视的知识点,但是它的使用又有许多的限制,需要我们在编程的时候合理地使用.(8)防止改变索引列的类型:当比拟不同数据类型的数据时.Oracle自动对列进行简单的类型

15、转换.假设EMPNO是一个数值类型的索引列.SELECTFROMEMPWHEREEMPNO=123:实际上,经过Oracle类型转换,语句转化为:SELECT.FROMEMPWHEREEMPNO=TONUMBER(123);幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.现在,假设EMPTYPE是一个字符类型的索引列.SELECTFROMEMPWHEREEMP_1YPE=123;这个语句被Oracle转换为:SELECTFROMEMPWHERETONUMBER(EMPTYPE)=123:因为内部发生的类型转换,这个索引将不会被用到.为了防止Oracle对SQL进行隐式的类型转换,最

16、好把类型转换用显式表现出来.尤其要注意当字符和数值比拟时,Oracle会优先转换数值类型到字符类型.(9)利用索引提高效率:当索引选择性低,但数据值分布情况差异很大时,仍然可以利用索引提高效率.在数据分布不均匀的特殊情况下,选择性不高的索引也要建立.假设表Tabl中数据量很大,有100万行,其中有一个字段Flag,取值范围为枚举值:【0,l,2,3,4,5,6,7】.按照前面所述索引建立的规那么:选择性不高的字段不应该建立索引,该字段只有8种取值,索引值的重复率很高,索引选择性明显很低,通常被考虑为不必建立索引.然而,如果该字段上数据值的分布情况非常特殊,如表l所示.表l字段Flag的取值分布

17、情况表取值范围(枚举型)占总数据量的百分比(%)1%98%1%假定常用的查询中,查询Flag<6的情况既多又频繁,毫无疑问,如果能够建立索引,并且被应用,那么必将大大提高这种情况的查询效率.(10)建立基于函数的索引:通常情况下,为了对指定例建立特定的条件,需要在WHERE子句中使用诸如=,I,+,UP.PER,REPLACE或SUBSTRD等函数.但是这些函数的使用,会带来一个问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比拟,查询会花费更多的时间.例如:不使用索引:SELECTACCOUNTNAMEFROMTRANSACT10NWHEREAMOUN

18、T!:O:使用索引:SELECTACCOUNTNAMEFROMTRANSACT10NWHEREAMOUNT>O:相同的索引列不能互相比拟,这将会启用全表扫描.不使用索引:SELECTACCOUNTNAME,AMOUNTFROMTRANSACT10NWHEREACCOUNTNAME=NVL(:ACCNAME,ACCOUNT_NAME);使用索引:SELECTACCOUNT_NAME,AMOUNTFROMTRANSACT10NWHEREACCOLINTNAMELIKENVL(:ACCNAME.%);如果一定要对使用函数的列启用索引,Oracle新的功能基于函数的索引(Function

19、.BasedIndex)就是一个较好的方案.CREATEnDEXEMPIONEMP(UPPER(ENAME):,幸建立基于函数的索引?/SELECTFR0MEMPWHEREUPPER(ENAM匝)=BLACKSNAIL;将使用索引/(11)防止全表扫描:如果数据是枚举值,且取值范围固定,那么尽量去掉,修改为OR,防止全表扫描.例:UPDATEEMPSETEMPKIND=0WHEREEMPKINDO:以上语句由于其中包含了,执行方案中进行了全表扫描,根本没有用到EMPKIND字段上的索引.在实际应用中,由于业务逻辑的限制,字段EMP为枚举值,只能等于_KINDO,l或2,而且,值等于l,2的情况

20、很少,因此可以考虑去掉,利用索引来提高效率.可以将原语句修改为:UPDATEEMPSETEMPKIND=0WHEREEMPKINDlOREMPKIND=2:(12)CBO下使用更具选择性的索引:基于本钱的优化器(CBO.Cost-BasedOptimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率.前面我们讲过选择性高的字段应该建立索引,原因是:如果索引有很高的选择性,那就是说对于每个不重复的索引键值,只对应数量很少的记录.选择性越高,通过索引键值检索出的记录就越少.如果索引的选择性很低,检索数据就需要大量的索引范围查询操作和R0w【)访问表的操作.也许会比全表扫描的效率更低.记

21、住下面两条经验:如果检索数据量超过30%的表中记录数,使用索引将没有显着的效率提高;在特定情况下,使用索引也许会比全表扫描慢,但这是同一个数量级上的区别.通常情况下,使用索引比全表扫描要块几倍乃至几千倍.(13)定期地重构索引是有必要的:索引需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的sERT,DELETE,UPDATE将为此多付出4至5次的磁盘I/O.可以通过ALTERINDEX<INDEX.NAME>REBuILD<TABLEsPACENAME>来定期重构.(14)别离表和索引:总是将表和

22、索引建立在不同的表空间内(TABLEsPACEs).决不要将不属于Oracle内部系统的对象存放到SYSTEM表空间里.同时确保数据表空间和索引表空间置于不同的硬盘上(不同的硬盘控制卡控制的硬盘E).4Oracle中优化SQL的其它注意问题前面我们主要讲的是基于索引的SQL优化中要注意的问题,实际上在提高Oracle数据库的性能上面,还有许多事情要做.用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下,回滚段(rollbackseg?ments)用来存放可以被恢复的信息.如果没有COMMIT事务,Oracle会将数据恢复到删除之前的状态.而当运用TRUN一-2329-54121

23、67CATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短尽量多使用COMMIT只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少.COMMIT所释放的资源:回滚段上用于恢复数据的信息;被程序语句获得的锁;Redologbuffer中的空间;Oracle为管理上述3种资源中的内部花费.4-3防止使用消耗资源的操作带有DISTINCT,UN10N,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎,执行消耗资源的排序(s0RT)功能.DISTINCT需要

24、一次排序操作,而其它的至少需要执行两次排序.通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其它方式重写.如果数据库的SORTAREASIZE调配得好,使用UNION,MINUS,INTERSECT也是可以考虑的,毕竟它们的可读性很强.使用显式的游标(Cursors)使用隐式的游标将会执行两次操作:第1次检索记录;第2次检查TOOMANYROWS这个EXCEPTION.而显式游标不执行第2次操作,速度提高.优化EXPORT和IMPoRT使用较大的BUFFER(比方10MB,10,240,000)可以提高EXPORT和IMPORT的速度.Oracle将尽可能地获取所指定的

25、内存大小,即使在内存不满足,也不会报错.这个值至少要和表中最大的列相当,否那么列值会被截断.5结束语Oracle数据库基于索引SQL优化方法目的是帮助用户提高查询速度.从上述例子中可以看出,在保证结果正确的前提下,要提高查询速度,充分利用索引,使用Oracle本身提供的优化器识别的语句,减少表扫描I/0次数,就可防止全表的搜索.实际上Oracle的优化技术是一个复杂的过程,这里主要表达了其中的基于索引的优化技术,对其它优化方法只是做了简单的描述.更深入的应用当然还需要我们在实际的应用中去摸索发现.参考文献:【l】袁鹏飞.Oracle8数据库高级应用开发技术【M】.北京:人民邮电出版社,2000

26、.【2】数据库管理员基础教程M】.北京:机械工业出版社,2000.【3】宝典【M】.北京:电子工业出版社,2000.【4】使用全书M】.北京:电子工业出版社,2001.【5】卞荣兵,张迎春,赵远东.基于Oracle数据库性能优化的研究【J】.应用技术,2002,36-38.【6】左风朝,李贵民.运用数据库优化器提高SQL查询效率【J】.聊城师院,2001,14(3):58-60.(上接第2314页)6结束语连续的查询优于传统的查询在于动态的信息处理,甚至不从表中获得数据,而直接从信息源截获.本文基于目前流数据的开展提出了两种流数据如何结合的处理方案,并且总结了连续查询中常见的几个问题,并对这3个系统指标提出了改善系统性能的3个处理方法,并且在实际的例子中取得了很好的效果.但是,要想无缝隙地平衡3个指标,还需进一步努力研究,这也是我们将来要完成的工作.参考文献:【l】overstreamingdataC