SQL语句优化-东软软件公司课件

SQL语句优化东软股份社保医疗卫生夏淼主要内容SQL语句优化原则PL/SQLDeveloper用法简介使用SQL语句监控数据库性能SQL语句优化原则合理使用索引提高查询速度通常情况下,使用索引要比全表扫描块几倍乃至几千倍！不合理的索引检索数据量超过表中30%的记录数，使用索引将没有显著的效率提高，有些时候甚至会比全表扫描慢。一个表中的索引并不是越多越好！索引过多增加系统开销。用不上索引的SQL语句使用不等于操作符(<>,!=)SELECT*FROMdeptWHEREstaff_num<>1000;×SELECT*FROMdeptWHEREstaff_num<1000ORstaff_num>1000;√使用isnull或isnotnull任何包含null值的列都将不会被包含在索引中。解决这个问题的办法就是：建表时把需要索引的列定义为非空(notnull)索引列使用函数或计算SELECT*FROM fin_ipb_feeinfoWHERE trunc(fee_date)>'2007-01-01';×SELECT*FROMfin_ipb_feeinfoWHEREfee_date>to_date('2007-01-01','yyyy-mm-dd');√WHEREsal*1.1>950×WHEREsal>950/1.1√用不上索引的SQL语句索引列与比较值数据类型不一致emp_no：NUMBER型

WHEREemp_no=123（好）√

WHEREemp_no='123'（也可）emp_type：CHAR型

WHEREemp_type=123（此时，查询时，不利用索引列）×

WHEREemp_type='123‘

√复合索引，必须使用主索引列例：复合索引(deptno,job)WHEREdeptno=20ANDjob=’MANAGER’√WHEREdeptno=20√WHEREjob=’MANAGER’ANDdeptno=20√WHEREjob=’MANAGER’

×用不上索引的SQL语句like子句WHEREnameLIKE‘王%’√WHEREnameLIKE‘%王%’×WHEREnameLIKE‘%王’×用不上索引的SQL语句ORDERBY子句列的顺序与索引列的顺序一致,列应为非空列。使用表的别名多表连接时，使用表的别名来引用列。例：SELECTabc002,abd003FROM ab001,ab020WHERE ab001.col2=ab020.col3

×SELECTt1.abc002,t2.abd003FROM ab001t1,ab020t2WHERE t1.col2=t2.col3

√SQL语句优化用NOTEXISTS代替NOTIN例：

SELECT......FROMempWHEREdept_noNOTIN(SELECTdept_noFROMdeptWHEREdept_cat=’A’);

×

SELECT......FROMempeWHERENOTEXISTS(SELECT‘X’FROMdeptWHEREdept_no=e.dept_noANDdept_cat=’A’);

√SQL语句优化用多表连接代替EXISTS子句例：

SELECT......FROMempWHEREEXISTS(SELECT ‘X’FROM deptWHERE dept_no=e.dept_noAND dept_cat=’A’);×

SELECT......FROMempe,deptdWHEREe.dept_no=d.dept_noANDdept_cat=’A’; √SQL语句优化用UNIONALL替换UNION(如果有可能的话)当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序。如果用UNIONALL替代UNION,这样排序就不是必要了。效率就会因此得到提高。SQL语句优化使用ROWID提高检索速度ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。因此那些基于索引列的查询就可以得到性能上的提高.对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID将会使效率大大提高。例：SELECTrowidINTOv_rowidFROMt1WHEREcon1=...;UPDATEt1SETcol2=......WHERErowid=v_rowid;SQL语句优化WHERE过滤原则过滤记录数最多的条件放在最前面例：SELECTinfo FROMtabaa,tabbb,tabccWHEREa.acolbetween:alowand:ahigh ANDb.bcolbetween:blowand:bhigh ANDc.ccolbetween:clowand:chigh ANDa.key1=b.key1 ANDa.key2=c.key2;其中，A表的acol列可以最多减少查询的记录数目，其次为B表的bcol列，依次类推。SQL语句优化共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。如经常使用select*fromdeptwheredeptno=值;值改为变量select*fromdeptwheredeptno=:d;则可以使用共享。可惜的是ORACLE只对简单的表提供高速缓冲(cachebuffering),这个功能并不适用于多表连接查询。SQL语句优化清除大量数据truncate替代delete，truncate不用commit，delete需要commit;尽量避免使用select*ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。强制索引对ORACLE优化器缺省指定的索引不满意,需要手工修改，则采用强制索引：SELECT/*+INDEX(表名索引名)*/列名FROM……SQL语句优化PL/SQLDeveloper用法简介界面简介判断SQL语句是否优化数据导入导出PL/SQLDeveloper用法简介使用SQL语句监控数据库性能当前数据库各个终端连接数SELECT"连接数","终端名称"FROM( SELECT

COUNT(TERMINAL)AS

连接数, TERMINALAS

终端名称

FROMv$sessionGROUP

BYTERMINAL )ORDER

BY

连接数DESC;查询性能最差的SQLSELECTdisk_reads,executions,rows_processed, first_load_time,sql_textFROM

sys.v_$sqlareaWHEREdisk_reads>10ANDexecutions<10ORDER

BYfirst_load_time;使用SQL语句监控数据库性能找使用CPU多的用户sessionSELECTa.sid,spid,status,substr(gram,1,40)prog,a.terminal,osuser,value/60/100valueFROMv$sessiona,v$processb,v$sesstatcWHEREc.statistic#=12ANDc.sid=a.sidANDa.paddr