SQL語句優(yōu)化-東軟軟件公司課件

SQL語句優(yōu)化東軟股份社保醫(yī)療衛(wèi)生夏淼主要內(nèi)容SQL語句優(yōu)化原則PL/SQLDeveloper用法簡介使用SQL語句監(jiān)控?cái)?shù)據(jù)庫性能SQL語句優(yōu)化原則合理使用索引提高查詢速度通常情況下,使用索引要比全表掃描塊幾倍乃至幾千倍！不合理的索引檢索數(shù)據(jù)量超過表中30%的記錄數(shù)，使用索引將沒有顯著的效率提高，有些時(shí)候甚至?xí)热頀呙杪?。一個(gè)表中的索引并不是越多越好！索引過多增加系統(tǒng)開銷。用不上索引的SQL語句使用不等于操作符(<>,!=)SELECT*FROMdeptWHEREstaff_num<>1000;×SELECT*FROMdeptWHEREstaff_num<1000ORstaff_num>1000;√使用isnull或isnotnull任何包含null值的列都將不會(huì)被包含在索引中。解決這個(gè)問題的辦法就是：建表時(shí)把需要索引的列定義為非空(notnull)索引列使用函數(shù)或計(jì)算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語句索引列與比較值數(shù)據(jù)類型不一致emp_no：NUMBER型

WHEREemp_no=123（好）√

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

WHEREemp_type=123（此時(shí)，查詢時(shí)，不利用索引列）×

WHEREemp_type='123‘

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

×用不上索引的SQL語句like子句WHEREnameLIKE‘王%’√WHEREnameLIKE‘%王%’×WHEREnameLIKE‘%王’×用不上索引的SQL語句ORDERBY子句列的順序與索引列的順序一致,列應(yīng)為非空列。使用表的別名多表連接時(shí)，使用表的別名來引用列。例：SELECTabc002,abd003FROM ab001,ab020WHERE ab001.col2=ab020.col3

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

√SQL語句優(yōu)化用NOTEXISTS代替NOTIN例：

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

×

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

√SQL語句優(yōu)化用多表連接代替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語句優(yōu)化用UNIONALL替換UNION(如果有可能的話)當(dāng)SQL語句需要UNION兩個(gè)查詢結(jié)果集合時(shí),這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并,然后在輸出最終結(jié)果前進(jìn)行排序。如果用UNIONALL替代UNION,這樣排序就不是必要了。效率就會(huì)因此得到提高。SQL語句優(yōu)化使用ROWID提高檢索速度ROWID包含了表中記錄的物理位置信息。ORACLE采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系。因此那些基于索引列的查詢就可以得到性能上的提高.對(duì)SELECT得到的單行記錄，需進(jìn)行DELETE、UPDATE操作時(shí)，使用ROWID將會(huì)使效率大大提高。例：SELECTrowidINTOv_rowidFROMt1WHEREcon1=...;UPDATEt1SETcol2=......WHERErowid=v_rowid;SQL語句優(yōu)化WHERE過濾原則過濾記錄數(shù)最多的條件放在最前面例：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列可以最多減少查詢的記錄數(shù)目，其次為B表的bcol列，依次類推。SQL語句優(yōu)化共享SQL語句 為了不重復(fù)解析相同的SQL語句,在第一次解析之后,ORACLE將SQL語句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域SGA(systemglobalarea)的共享池(sharedbufferpool)中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享.因此,當(dāng)你執(zhí)行一個(gè)SQL語句(有時(shí)被稱為一個(gè)游標(biāo))時(shí),如果它和之前的執(zhí)行過的語句完全相同，ORACLE就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑。ORACLE的這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。如經(jīng)常使用select*fromdeptwheredeptno=值;值改為變量select*fromdeptwheredeptno=:d;則可以使用共享?？上У氖荗RACLE只對(duì)簡單的表提供高速緩沖(cachebuffering),這個(gè)功能并不適用于多表連接查詢。SQL語句優(yōu)化清除大量數(shù)據(jù)truncate替代delete，truncate不用commit，delete需要commit;盡量避免使用select*ORACLE在解析的過程中,會(huì)將’*’依次轉(zhuǎn)換成所有的列名,這個(gè)工作是通過查詢數(shù)據(jù)字典完成的,這意味著將耗費(fèi)更多的時(shí)間。強(qiáng)制索引對(duì)ORACLE優(yōu)化器缺省指定的索引不滿意,需要手工修改，則采用強(qiáng)制索引：SELECT/*+INDEX(表名索引名)*/列名FROM……SQL語句優(yōu)化PL/SQLDeveloper用法簡介界面簡介判斷SQL語句是否優(yōu)化數(shù)據(jù)導(dǎo)入導(dǎo)出PL/SQLDeveloper用法簡介使用SQL語句監(jiān)控?cái)?shù)據(jù)庫性能當(dāng)前數(shù)據(jù)庫各個(gè)終端連接數(shù)SELECT"連接數(shù)","終端名稱"FROM( SELECT

COUNT(TERMINAL)AS

連接數(shù), TERMINALAS

終端名稱

FROMv$sessionGROUP

BYTERMINAL )ORDER

BY

連接數(shù)DESC;查詢性能最差的SQLSELECTdisk_reads,executions,rows_processed, first_load_time,sql_textFROM

sys.v_$sqlareaWHEREdisk_reads>10ANDexecutions<10ORDER

BYfirst_load_time;使用SQL語句監(jiān)控?cái)?shù)據(jù)庫性能找使用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