1. 選用適合的oracle優(yōu)化器
了解一下,oracle優(yōu)化器的種類(lèi):3種
- a. RULE (基于規(guī)則)
- b. COST (基于成本)
- c. CHOOSE (選擇性)
2. 訪問(wèn)table的方式
oracle采用兩種方式訪問(wèn)表中的記錄:
a. 全表掃描
全表掃描就是順序地訪問(wèn)表中每條記錄. ORACLE采用一次讀入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描.b. 通過(guò)ROWID訪問(wèn)表
ROWID包含了表中記錄的物理位置信息.ORACLE采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系.通常索引提供了快速訪問(wèn)ROWID的方法,因此那些基于索引列的查詢(xún)就可以得到性能上的提高.
3. 共享SQL語(yǔ)句
為了不重復(fù)解析相同的SQL語(yǔ)句,在第一次解析之后, ORACLE將SQL語(yǔ)句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域SGA(system globalarea)的共享池(shared bufferpool)中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶(hù)共享.因此,當(dāng)你執(zhí)行一個(gè)SQL語(yǔ)句(有時(shí)被稱(chēng)為一個(gè)游標(biāo))時(shí),如果它和之前的執(zhí)行過(guò)的語(yǔ)句完全相同,ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執(zhí)行路徑. ORACLE的這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用.
4. 選擇最優(yōu)效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)
Oracle的解析器按照從右到左的順序處理from字句中的表名,因此FROM子句中寫(xiě)在最后的表(基礎(chǔ)表 driving table)將被最先處理.在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.
如果有3個(gè)以上的表連接查詢(xún),那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)表,交叉表是指那個(gè)被其他表所引用的表.
雖然這種只在基于規(guī)則的優(yōu)化器中有效,但這樣寫(xiě)絕對(duì)不會(huì)錯(cuò),因?yàn)榛谄渌?guī)則的會(huì)首先檢索出基礎(chǔ)表然后進(jìn)行首先處理.
5. WHERE字句中的連接順序 (強(qiáng)烈推薦!!!)
ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫(xiě)在其他WHERE條件之前,那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在WHERE子句的末尾.
6. SELECT子句中避免使用 ‘ * ‘ (強(qiáng)烈推薦!!!)
7. 使用DECODE函數(shù)來(lái)減少處理時(shí)間
使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.
8. 在刪除全表時(shí),用TRUNCATE替代DELETE (強(qiáng)烈推薦!!!)
這樣使用時(shí),而且無(wú)需執(zhí)行COMMIT操作.
9. 選擇合適的時(shí)機(jī)使用COMMIT (強(qiáng)烈推薦!!!)
比如,我在實(shí)現(xiàn)某個(gè)業(yè)務(wù)需求,在需要頻繁操作數(shù)據(jù)庫(kù)的地方,設(shè)置每操作500次COMMIT一次.
10. 計(jì)算記錄條數(shù) (強(qiáng)烈推薦!!!)
和一般的觀點(diǎn)相反,count(*)比count(1)稍快,當(dāng)然如果可以通過(guò)索引檢索,對(duì)索引列的計(jì)數(shù)仍舊是最快的.例如COUNT(ID).
這個(gè)我之前一直錯(cuò)誤的以為count(1)是最快的.
11. 用WHERE子句替換HAVING子句 (強(qiáng)烈推薦!!!)
避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾.這個(gè)處理需要排序,總計(jì)等操作.如果能通過(guò)WHERE子句限制記錄的數(shù)目,那就能減少這方面的開(kāi)銷(xiāo).
HAVING中的條件一般用于對(duì)一些集合函數(shù)的比較,如COUNT()等等.除此而外,一般的條件應(yīng)該寫(xiě)在WHERE子句中.
12. 使用表的別名,減少解析的時(shí)間 (強(qiáng)烈推薦!!!)
13. 用EXISTS替代IN,用NOT EXISTS替代NOT IN. (強(qiáng)烈推薦!!!)
14. 通過(guò)腳本來(lái)識(shí)別低效執(zhí)行的SQL語(yǔ)句.
這種方式,其實(shí)我還不是太熟悉.先記錄下.
15. 用EXPLAIN PLAN分析SQL語(yǔ)句. (強(qiáng)烈推薦!!!)
16. 合理的使用索引來(lái)提高效率 (強(qiáng)烈推薦!!!)
雖然使用索引能得到查詢(xún)效率的提高,但是我們也必須注意到它的代價(jià).索引需要空間來(lái)存儲(chǔ),也需要定期維護(hù),每當(dāng)有記錄在表中增減或索引列被修改時(shí),索引本身也會(huì)被修改.這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5次的磁盤(pán)I/O.因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢(xún)反應(yīng)時(shí)間變慢.
定期重構(gòu)索引是有必要的.
17. 用UNION替換OR(適用于索引列) (強(qiáng)烈推薦!!!)
通常情況下,用UNION替換WHERE子句中的OR將會(huì)起到較好的效果.對(duì)索引列使用OR將造成全表掃描.注意,以上規(guī)則只針對(duì)多個(gè)索引列有效.如果有column沒(méi)有被索引,查詢(xún)效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低.
如果你堅(jiān)持要用OR,那就需要返回記錄最少的索引列寫(xiě)在最前面.
注意:
WHERE KEY1 = 10 (返回最少記錄) OR KEY2 = 20 (返回最多記錄)
ORACLE內(nèi)部將以上轉(zhuǎn)換為:
WHERE KEY1 = 10 AND ((NOT KEY1 = 10) AND KEY2 = 20)
這個(gè)我在生產(chǎn)環(huán)境中,有應(yīng)用到,實(shí)際效果確實(shí)蠻不錯(cuò)的.
18. 用IN替換OR.
19. 總是使用索引的第一個(gè)列 (強(qiáng)烈推薦!!!)
如果索引是建立在多個(gè)列上,只有在它的第一個(gè)列(leading column)被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引.
20. 用UNION ALL替換UNION (如果有可能的話) (強(qiáng)烈推薦!!!)
當(dāng)SQL語(yǔ)句需要UNION兩個(gè)查詢(xún)結(jié)果集合時(shí),這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并,然后在輸出最終結(jié)果前進(jìn)行排序.如果用UNION ALL替代UNION,這樣排序就不是必要了,效率就會(huì)因此得到提高.需要注意的是墓陈，UNION ALL將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄.因此各位還是要從業(yè)務(wù)需求分析使用UNION ALL的可行性.
21. 需要當(dāng)心的WHERE字句 (強(qiáng)烈推薦!!!!!)
(1) 索引對(duì)不等號(hào)和NOT的限制
如果WHERE條件中出現(xiàn)!=或者<>，即使該列建立了索引第献，則該索引也不會(huì)被使用贡必；如果不恰當(dāng)?shù)氖褂昧薔OT，則索引也不會(huì)被使用庸毫。
在下面的例子里, ‘!=’將不使用索引.記住,索引只能告訴你什么存在于表中,而不能告訴你什么不存在于表中.
不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0;
使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
如果索引列是數(shù)字仔拟，則對(duì)于不等號(hào)的處理可以變更為NOT的方式或者(大于 OR 小于)的方式.
(2) 下面的例子中, ‘||’是字符連接函數(shù).就象其他函數(shù)那樣,停用了索引.
不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = ‘AMEX’ AND ACCOUNT_TYPE=’ A’;
(3) 索引對(duì)函數(shù)的限制,避免在索引列上使用計(jì)算:
下面的例子中, ‘+’是數(shù)學(xué)函數(shù).就象其他數(shù)學(xué)函數(shù)那樣,停用了索引.
不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT + 3000 >5000;
使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT > 2000 ;
注意: 對(duì)在WHERE子句中經(jīng)常要使用函數(shù)時(shí),應(yīng)該建立基于函數(shù)的索引,且只有當(dāng)查詢(xún)語(yǔ)句包含該函數(shù)或者表達(dá)式時(shí)，基于函數(shù)的索引才會(huì)被調(diào)用飒赃。
(4) 避免在索引列上使用IS NULL和IS NOT NULL:
因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引.
一般對(duì)要建立索引的列不要設(shè)置為可空利花，如果確實(shí)含有空值科侈，建議使用默認(rèn)值代替空值.
(5) 注意通配符%的影響
使用通配符的情況下Oracle可能會(huì)停用該索引。
(6) 避免改變索引列的類(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í)際上,經(jīng)過(guò)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)型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
這個(gè)語(yǔ)句被ORACLE轉(zhuǎn)換為:SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123
因?yàn)閮?nèi)部發(fā)生的類(lèi)型轉(zhuǎn)換,這個(gè)索引將不會(huì)被用到!
(7) 索引的一些“脾氣”
a. 如果檢索數(shù)據(jù)量超過(guò)30%的表中記錄數(shù).使用索引將沒(méi)有顯著的效率提高.
b. 在特定情況下, 使用索引也許會(huì)比全表掃描慢, 但這是同一個(gè)數(shù)量級(jí)上的區(qū)別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
22. 避免使用耗費(fèi)資源的操作
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語(yǔ)句會(huì)啟動(dòng)SQL引擎執(zhí)行耗費(fèi)資源的排序(SORT)功能.通常,帶有UNION,MINUS ,INTERSECT的SQL語(yǔ)句都可以用其他方式重寫(xiě).
23. 優(yōu)化GROUP BY (強(qiáng)烈推薦!!!)
提高GROUP BY語(yǔ)句的效率,可以通過(guò)將不需要的記錄在GROUP BY之前過(guò)濾掉.
參考文章:
https://www.cnblogs.com/wanghang/p/6299429.html
https://www.cnblogs.com/cxxjohnson/p/5686715.html
https://blog.csdn.net/holandstone/article/details/51473010
