SQL語(yǔ)句性能優(yōu)化_百度文庫(kù)

1、SQL語(yǔ)句性能優(yōu)化編碼過(guò)程一些注意事項(xiàng)：1、Oracle的From子句表的順序：記錄越多的表放在越前面（左）；右邊的表位驅(qū)動(dòng)表，驅(qū)動(dòng)表越小，最終掃描的數(shù)據(jù)越少。Oracle解析器按照從右向左的順序處理From子句中的表名，最右的表將被最先處理。如果有3個(gè)以上的表連接查詢，那就需要選擇交叉表(intersection table作為基礎(chǔ)表，交叉表是指那個(gè)被其它表所引用的表。2、Oracle的where子句表達(dá)式的順序：過(guò)濾掉最大數(shù)目記錄的條件放到where子句的末尾。Oracle的執(zhí)行計(jì)劃的條件過(guò)濾從最后向前進(jìn)行，過(guò)濾數(shù)據(jù)最多的在最后可以提高數(shù)據(jù)庫(kù)的查詢速度。3、Select子句中避免使用*，O

2、racle在解析的過(guò)程中會(huì)將*一次轉(zhuǎn)換成所有的列名，這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典完成的，這樣將耗費(fèi)更多的事件；4、在執(zhí)行結(jié)果等效的情況下，使用Truncate代替Delete；當(dāng)刪除表中記錄時(shí)，在通常情況下，回滾段用來(lái)存放可以被恢復(fù)的信息，如果沒(méi)有commit事務(wù)，oracle會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)。使用truncate時(shí)，回滾段不再存放任何可恢復(fù)的信息，數(shù)據(jù)不能被恢復(fù)。5、為了在查詢過(guò)程中要盡量使用索引，對(duì)于like語(yǔ)句避免使用右匹配或者中間匹配的模糊查詢；6、將過(guò)濾條件盡可能放到Where子句中，而不是放到Having子句中；Having子句是用來(lái)對(duì)分組后的結(jié)果進(jìn)行過(guò)濾，限制分組后的查

3、詢結(jié)果。Having子句會(huì)再檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾，而且在使用Having子句時(shí)一般需要執(zhí)行排序、統(tǒng)計(jì)等操作。執(zhí)行這些操作時(shí)，SQL優(yōu)化器會(huì)進(jìn)行一些額外的工作，這就需要消耗額外的時(shí)間。7、在SQL語(yǔ)句中，要減少對(duì)表的查詢，特別是在含有子查詢的SQL子句中；8、使用表的別名可以減少解析的時(shí)間并避免引起歧義；9、使用EXISTS替代IN，用NOT EXISTS替代NOT IN；10、通常情況下，采用表連接的方式比exists更有效率；11、當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門(mén)表和雇員表的查詢時(shí),避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換，EXISTS

4、使查詢更為迅速，因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果。例子：(低效：SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP EWHERE D.DEPT_NO=E.DEPT_NO;(高效:SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS(SELECT X FROM EMP E WHERE E.DEPT_NO=D.DEPT_NO;12、使用>=替代>，這樣DBMS可直接跳到等于的記錄上，可能會(huì)避免向前的掃描工作；13、用union替代or，通常情況下, 用UNION替

5、換WHERE子句中的OR時(shí)，將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒(méi)有被索引, 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低.在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID ,

6、LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫(xiě)在最前面. 14、避免在索引列上使用計(jì)算，如where語(yǔ)句中的表達(dá)式sal *12 > 2500應(yīng)該改為 sal 2500/12；15、避免在索引列上使用IS NULL和IS NOT NULL工作，這樣index將不被使用；16、如果必要，使用函數(shù)索引，如對(duì)Upper（name）建立索引；17、避免在索引列上使用Not，這樣將執(zhí)行全表掃描；18、如果可能，用union-all替換union語(yǔ)

7、句：當(dāng)SQL語(yǔ)句需要UNION兩個(gè)查詢結(jié)果集合時(shí),這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進(jìn)行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會(huì)因此得到提高.19、避免使用耗費(fèi)資源的操作：帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語(yǔ)句會(huì)啟動(dòng)SQL引擎，執(zhí)行耗費(fèi)資源的排序(SORT功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序.20、盡量避免不必要的排序；21、能夠批量查詢的情況，不要使用循環(huán)語(yǔ)句單次查詢，避免IO開(kāi)銷(xiāo)；22、多使用Commit語(yǔ)句：在一個(gè)事務(wù)

8、中，避免大批量修改的，這樣可能由于不斷擴(kuò)充回滾段而影響性能。Commit所釋放的資源：A、回滾段上用于恢復(fù)數(shù)據(jù)的信息。B、被程序語(yǔ)句獲得的鎖。C、redo log buffer中的空間。D、oracle為管理上訴資源中的內(nèi)部花費(fèi)（oracle自身消耗）。23、在寫(xiě)完一條SQL語(yǔ)句后，使用適當(dāng)?shù)墓ぞ撸ㄈ鏓xplain plan）檢查SQL語(yǔ)句的性能；24、如果使用IN語(yǔ)句時(shí)，IN(a,b,c.,IN的內(nèi)容不能超過(guò)1000，可以使用變通的方法，將查詢內(nèi)容做個(gè)臨時(shí)表或者分多個(gè)in語(yǔ)句實(shí)現(xiàn)。25、連接查詢多個(gè)表而不是執(zhí)行多個(gè)查詢。執(zhí)行一條語(yǔ)句的效率要高于執(zhí)行兩個(gè)語(yǔ)句的效率；正在連接的過(guò)程中盡量少使用視

9、圖；連接的順序應(yīng)該是數(shù)據(jù)多的表應(yīng)該先進(jìn)行連接。26、使用case表達(dá)式而不是執(zhí)行多個(gè)查詢。Eg:統(tǒng)計(jì)男性作者的數(shù)量和女性作者的數(shù)量。Select count(* from anthors where gender=M;Select count(* from authors where gender=F;被替換為Select count(case when gender=M then 1 else null end male_auther,count(case when gender=F then 1 else null end female_autherfrom authors;提高查詢的效率

10、。27、在連接查詢中，使用完全限定的列別名引用，即我們所說(shuō)的利用別名。28、使用start with.connect by 子句進(jìn)行遞歸查詢時(shí)，查詢的對(duì)象必須是實(shí)際存在的，查詢的對(duì)象不能是使用dblink進(jìn)行的跨數(shù)據(jù)庫(kù)對(duì)象。29、如果想要查詢與列表匹配的記錄，可以有兩種方式，一種是使用OR關(guān)鍵字，另外一種就是使用IN關(guān)鍵字。但是，在SQL語(yǔ)句中應(yīng)該使用IN關(guān)鍵字代替OR關(guān)鍵字。因?yàn)镺R關(guān)鍵字并不會(huì)使用索引，因此，使用IN關(guān)鍵字可以提高數(shù)據(jù)的查詢速度。30、避免使用<>和!=操作符。例如查詢教師信息表中工資不為3000的教師記錄，查詢SQL語(yǔ)句如下：select teaID,teaN

11、ame,salary from T_teacherFrom T_teacherWhere salary<>30000;為了提高查詢效率，可以把這個(gè)查詢語(yǔ)句改寫(xiě)成下面這種形式。Select teaID,teaName,salaryFrom T_teacherWhere salary<3000 or salary>300031、對(duì)于一些功能復(fù)雜的累活使用存儲(chǔ)過(guò)程實(shí)現(xiàn)優(yōu)于SQL語(yǔ)句。存儲(chǔ)過(guò)程運(yùn)行在服務(wù)器端，在運(yùn)行之前，數(shù)據(jù)庫(kù)已經(jīng)對(duì)其進(jìn)行了變異并提供了相應(yīng)的優(yōu)化執(zhí)行方案，而SQL語(yǔ)句在執(zhí)行時(shí)，數(shù)據(jù)庫(kù)需要對(duì)其進(jìn)行語(yǔ)法分析和檢查，而且一直SQL語(yǔ)句解析之后是保存在內(nèi)存中，如果其他操

12、作使用內(nèi)存，該SQL語(yǔ)句就會(huì)被釋放，因此它并不是永久的。32、規(guī)范SQL語(yǔ)句書(shū)寫(xiě)格式。SQL語(yǔ)句可讀性好，勢(shì)必會(huì)降低程序調(diào)式的難度。33、減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)：procedure中有變量時(shí)盡量使用綁定變量。34、使用decode函數(shù)減少處理時(shí)間：使用decode函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。35、刪除重復(fù)記錄：最高效的刪除重復(fù)記錄方法，例如：Delete from emp e where e.rowin>(select min(x.rowid from emp x where x.emp_no=e.emp_no;36、減少對(duì)表的查詢：在含有子查詢的SQL語(yǔ)句中，要特別注

13、意減少對(duì)表的查詢，例如：Select tab_name from tables where (tab_name,db_ver=(select tab_name,db_ver from tab_columns where version=640; 37、所有腳本建議都是用大寫(xiě)進(jìn)行編寫(xiě)，因?yàn)閛racle總是先解析sql語(yǔ)句，把小寫(xiě)的字母轉(zhuǎn)換成大寫(xiě)的再執(zhí)行。38、在代碼中進(jìn)行少用“+”連接字符串，也就是不需要使用左右外連接時(shí)盡量少用。39、使用索引的第一個(gè)列。如果索引是建立在多個(gè)列上，只有在它的第一個(gè)列被where子句已用時(shí)，優(yōu)化器才會(huì)使用該索引。40、用WHERE替代ORDER BY。Order

14、by子句只在兩種嚴(yán)格的條件下使用索引。Order by中所有的列必須包含在相同的索引中并保持在索引中的排列順序。Order by中所有的列必須定義為非空。Where子句使用的索引和order by子句中所使用的索引不能并列。41、避免改變索引列的類(lèi)型.當(dāng)比較不同數(shù)據(jù)類(lèi)型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類(lèi)型轉(zhuǎn)換. 假設(shè) EMPNO是一個(gè)數(shù)值類(lèi)型的索引列. SELECT FROM EMP WHERE EMPNO = 123' 實(shí)際上2、, ORACLE類(lèi)型轉(zhuǎn)換, 語(yǔ)句轉(zhuǎn)化為: SELECT FROM EMP WHERE EMPNO = TO_NUMBER(123' 幸運(yùn)的是,類(lèi)型轉(zhuǎn)換沒(méi)有發(fā)生在索引列上,索引的用途沒(méi)有被改變. 現(xiàn)在,假設(shè)EMP_TYPE是一個(gè)字符類(lèi)型的索引列.