資源描述:
《基于索引的sql語句優(yōu)化》由會(huì)員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在教育資源-天天文庫。
1、基于索引的SQL語句優(yōu)化一、盡量避免非操作符的使用通常情況下,為了對(duì)指定列建立特定的條件,需要在WHERE子句中使用諸如NOT、!=、<>、!<、!>等操作符,在索引列上使用這些非操作符,DBMS是不使用索引的,可以將查詢語句轉(zhuǎn)換為可以使用索引的查詢。例:SELECT*FROMORDERSWHEREORDERDATE<>1997-l2轉(zhuǎn)化為:SELECT*FROMORDERSWHEREORDERDATEl998-l-l這樣DBMS就能利用索引字段OR
2、DERDATE,大大提高查詢效率。二、避免困難的正規(guī)表達(dá)式MATCHES和LIKE關(guān)鍵字支持通配符匹配,技術(shù)上叫正規(guī)表達(dá)式。但這種匹配特別耗費(fèi)時(shí)間。例如:SELECT*FROMSTUDENTWHERESTUDENT_NUMLIKE“98__”即使在STUDENT_NUM字段上建立了索引,在這種情況下也還是采用順序掃描的方式。如果把語句改為SELECT*FROMSTUDENTWHERESTUDENT_NUM>”98000”,在執(zhí)行查詢時(shí)就會(huì)利用索引來查詢,顯然會(huì)大大提高速度。如果一定要使用通配符也
3、要避免通配符在搜索字段的首部出現(xiàn),這種情況下DBMS的優(yōu)化器不會(huì)使用索引[6]。三、避免在索引列上使用NULL關(guān)鍵字避免在索引中使用任何可以為空的列,ORACLE將無法使用該索引。即使索引有多列,只要這些列中有一列含有NULL,該列就會(huì)從索引中排除,也就是說如果某列存在空值,即使對(duì)該列建索引也不會(huì)提高性能[7]。任何在WHERE子句中使用ISNULL或ISNOTNULL的語句,優(yōu)化器是不允許使用索引的。四、避免對(duì)查詢的列使用數(shù)學(xué)運(yùn)算如果在查詢列使用數(shù)學(xué)運(yùn)算,則DBMS優(yōu)化器先要處理數(shù)學(xué)運(yùn)算也會(huì)影
4、響查詢效能。例如:(1)SELECT*FROMORDERDETAILSWHEREQUANTITY*2<50(2)SELECT*FROMORDERDETAILSWHEREQUANTITY<25雖然這兩條查詢的結(jié)果完全相同,但某些情況下第二個(gè)語句的執(zhí)行效率遠(yuǎn)高于第一個(gè),因此在查詢前應(yīng)將數(shù)學(xué)運(yùn)算轉(zhuǎn)化。五、盡量去掉IN或OR含有“IN”或“OR”的WHERE子句常會(huì)令索引失效;在不產(chǎn)生大量重復(fù)值的情況下,可以考慮把子句拆開,拆開的子句中應(yīng)該包含索引。例:SELECTCOUNT(*)FROMEMPWHER
5、EEMP_IDIN(‘0’,’1’);可以將子句分開:SELECTCOUNT(*)FROMEMPWHEREEMP_ID=‘0’;SELECTCOUNT(*)FROMEMPWHEREEMP_ID=‘1’;然后再做一個(gè)簡(jiǎn)單的加法,與原來的SQL語句相比,查詢速度有了明顯提高。六、限制查詢范圍,減少全范圍搜索例:以下查詢表RECORD中時(shí)間EMP_TIME中小于2003年6月1日的數(shù)據(jù)。SELECT*FROMRECORDWHEREEMP_TIME<=TO_DATE(‘20030601’,’YYYYMM
6、’);查詢計(jì)劃表明,上面的查詢對(duì)表進(jìn)行了全表掃描,如果知道表中最早的數(shù)據(jù)為2000年1月1日,那么可以增加一個(gè)最小時(shí)間,保證查詢?cè)谝粋€(gè)完整的范圍之內(nèi)[8]。SELECT*FROMRECORDWHEREEMP_TIME<=TO_DATE(‘20030601’,’YYYYMM’);ANDEMP_TIME>=TO_DATE(‘20000101’,’YYYYMM’);后一種SQL語句利用了EMP_TIME字段上的索引,從而可以提高查詢的效率。把“20030601”換為一個(gè)變量,根據(jù)取值的機(jī)率,可以證明有
7、5O%以上的機(jī)率提高查詢效率。同理,對(duì)于大于某個(gè)值的查詢,如果知道當(dāng)前可能的最大值,也可以在WHERE子句中加上“AND列名<最大值”來限制查詢范圍,以提高查詢的效率。七、避免使用不兼容的數(shù)據(jù)類型數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作。例如:SELECTTITLEFROMTITIESWHEREPRICE>100;在這條語句中,“PRICE”字段是“MONEY”型的,優(yōu)化器很難對(duì)其進(jìn)行優(yōu)化,因?yàn)?00是個(gè)整型數(shù),應(yīng)當(dāng)在編程時(shí)將整型轉(zhuǎn)化成為貨幣類型,而不要等到運(yùn)行時(shí)轉(zhuǎn)化。當(dāng)比
8、較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí),ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類型轉(zhuǎn)換。例如,假設(shè)EMP_TYPE是一個(gè)字符類型的索引列。SELECT*FROMEMPWHEREEMP_TYPE=123;這個(gè)語句被ORACLE轉(zhuǎn)換為:SELECT*FROMEMPWHERETO_NUM(EMP_TYPE)=123;因?yàn)閮?nèi)部發(fā)生了類型轉(zhuǎn)換,這個(gè)索引將不會(huì)被用到。為了避免ORACLE對(duì)SQL進(jìn)行隱式的類型轉(zhuǎn)換,最好把類型轉(zhuǎn)換用顯式形式表現(xiàn)出來。尤其要注意當(dāng)字符和數(shù)值比較時(shí),ORACLE會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型。八、CBO下