SQL優(yōu)化簡(jiǎn)介
一般在應(yīng)用中， 糟糕的SQL語(yǔ)句是造成系統(tǒng)性能低下的最主要原因，例如大小寫的不統(tǒng)一、同樣的SQL語(yǔ)句不同的寫法等。而且，隨著數(shù)據(jù)量的增加，情況會(huì)變得越來(lái)越嚴(yán)重。（題外話：優(yōu)秀的Oracle數(shù)據(jù)庫(kù)優(yōu)化人才，是任何公司都稀缺的）
SQL優(yōu)化又稱SQL調(diào)節(jié)，其步驟一般包括：
SQL調(diào)節(jié)的目標(biāo)
SQL調(diào)節(jié)包括三大目標(biāo)：降低負(fù)載、均衡負(fù)載和并行化負(fù)載。
l降低負(fù)載：即尋找更高效的途徑來(lái)完成相同的功能
如某個(gè)非大表(小于2000萬(wàn)行數(shù)據(jù)數(shù)據(jù)或小于2G大小的單表)，常規(guī)查詢需要訪問(wèn)的數(shù)據(jù)實(shí)踐中90%情況下是不會(huì)超過(guò)20%的，此時(shí)建立合理的索引是有效的方法之一
l均衡負(fù)載：即應(yīng)該把任務(wù)分時(shí)段均衡調(diào)度
如一般系統(tǒng)白天是訪問(wèn)高峰，如果此時(shí)備份任務(wù)、批處理任務(wù)或報(bào)表數(shù)據(jù)抽取任務(wù)也 擠在這個(gè)時(shí)段則易造成負(fù)載峰值現(xiàn)象，正確的做法應(yīng)該是把備份任務(wù)、批處理任務(wù)和報(bào)表數(shù)據(jù)抽取任務(wù)放到晚上進(jìn)行處理，或采用并行化策略
l并行化負(fù)載：即大數(shù)據(jù)量的查詢?cè)L問(wèn)需要使用并發(fā)策略
如在數(shù)據(jù)倉(cāng)庫(kù)環(huán)境中應(yīng)該多使用并發(fā)策略，此舉可以明顯減少響應(yīng)時(shí)間
SQL優(yōu)化階段
使用OEM發(fā)現(xiàn)頂級(jí)SQL
在OEM中，選擇性能->其它監(jiān)視鏈接->定級(jí)活動(dòng)，如下圖：
不要用*代替所有列名
指定僅僅需要的列名與使用*對(duì)比：
時(shí)間：359/1327=27.05% CUP耗費(fèi): 4092121327/6413227637=63.81%
IO耗費(fèi)： 29601/110117=26.88% 可見大幅降低I/O從而降低響應(yīng)時(shí)間！
SQL優(yōu)化技巧
使用TRUNCATE代替DELETE
Oralce執(zhí)行DELETE后會(huì)使用UNDO表空間存放被刪除的信息以便恢復(fù)，如果之后用戶使用ROLLBACK而不是COMMIT，則 Oralce將利用該UNDO表空間中的數(shù)據(jù)進(jìn)行恢復(fù)。當(dāng)使用TRUNCATE時(shí)，Oracle不會(huì)將刪除的數(shù)據(jù)放入U(xiǎn)NDO表空間，因而速度要快很多。 當(dāng)要?jiǎng)h除某個(gè)表中的全部數(shù)據(jù)時(shí)，應(yīng)該使用TRUNCATE而不是不帶WHERE條件的DELETE。語(yǔ)法如下：
TRUNCATE TABLE table_name [DROP|REUSE STORAGE]
DROP STORAGE為默認(rèn)的方式，表示收回被刪除的表空間
REUSER STORAGE表示保留被刪除的空間以供該表的新數(shù)據(jù)使用
應(yīng)用開發(fā)中，可以編寫一個(gè)子程序讓其動(dòng)態(tài)的清除空表，以供調(diào)用。
默認(rèn)PCTFREE為10，假定為5,high-water mark是一個(gè)存儲(chǔ)段分配多少存儲(chǔ)器的標(biāo)記。
活用COMMIT
PL/SQL塊中，經(jīng)常將幾個(gè)相互聯(lián)系的DML語(yǔ)句寫在BEGIN …END，如果不影響事務(wù)的完整性，則建議在每個(gè)END前面寫一個(gè)COMMIT，以達(dá)到 對(duì)DML的及時(shí)提交和 釋放事務(wù)所占的資源的目的。
COMMIT釋放的資源包括：
lUNDO段上用于恢復(fù)數(shù)據(jù)的信息
l事物中DML語(yǔ)句獲得的鎖
lSGA中重做日志緩沖區(qū)中的空間
lOracle為管理相關(guān)資源(如上述資源) 而開銷的內(nèi)部資源
體驗(yàn)例子流程如下 ：
體驗(yàn)例子顯示 ：
減少表的查詢次數(shù)
1.一個(gè)邏輯單元中，將能讀出的列一次性讀出，且盡量存放在本地變量中，應(yīng)該杜絕不要用一個(gè)讀一個(gè)
2.在包含子查詢的SQL中，要特別注意減少對(duì)表的查詢次數(shù)，在代碼清晰時(shí)對(duì)于能減少查詢次數(shù)的應(yīng)堅(jiān)決減少,舉例如下：
2.執(zhí)行計(jì)劃如下，結(jié)論是什么？
以EXISTS代替DISTINCT
多表信息的查詢時(shí),避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXISTS替換, EXISTS 使查詢更為迅速,因?yàn)榇藭r(shí)RDBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果。
優(yōu)化前：
優(yōu)化后：
使用默認(rèn)值
使用默認(rèn)之后的執(zhí)行時(shí)間比為1.063/2.657=40.01%，快了一倍多！
可見在不含默認(rèn)值，是null的列上沒有使用索引，是全表掃描！而使用了默認(rèn)值的列上使用了索引范圍掃描！
l不能用null作索引，任何包含null值的列都將不會(huì)被包含在索引中。即使索引有多列的情況下，只要這些列中有一列含有null，該列就會(huì)從索引中排除。也就是說(shuō)如果某列存在空值，即使對(duì)該列建索引也不會(huì)提高性能
l任何在where子句中使用is null或is not null的語(yǔ)句優(yōu)化器是不允許使用索引的
l如果每列確實(shí)可能存在空值的情況，可以使用默認(rèn)值的方式替代以便充分利用索引提高性能
使用DECODE函數(shù)減少處理步驟
l使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.
lDECODE函數(shù)也可以運(yùn)用于GROUP BY 和ORDER BY子句中.
l上述例子有兩步相似的操作，使用DECODE后節(jié)省一半時(shí)間，如果一組相似的操作越多，節(jié)省的時(shí)間則越多，計(jì)算公式為n-1，其中n為相似操作的步驟數(shù)
通配符的使用技巧
上例中已知數(shù)據(jù)%DX_ACCOUNT_TRADE%，只有以I開頭的
首位使用通配符是首位不使用通配符執(zhí)行效率的：0.031/1.891=1.639%
l當(dāng)通配符出現(xiàn)在LIKE后面字符串的首位時(shí)，索引將不會(huì)被使用，因此在已知某字符的情況下，LIKE查詢中應(yīng)盡量不要把通配符寫在首位
l%代表不定長(zhǎng)的字符，_代表定長(zhǎng)的字符，如果在確定要通配的字符長(zhǎng)度時(shí)，應(yīng)該盡量使用_，而不是%
定義并執(zhí)行嚴(yán)格的SQL編寫規(guī)范
使用Oracle共享游標(biāo)的優(yōu)點(diǎn)是：
l降低和減少Oracle對(duì)SQL的解析數(shù)量
l動(dòng)態(tài)調(diào)整內(nèi)存
l提高內(nèi)存的使用率
風(fēng)格請(qǐng)參照前面章節(jié)中的“建議的程序風(fēng)格”
表的連接方式
FROM表順序選擇
使用基于規(guī)則的優(yōu)化器（CBO）時(shí)，Oracle解析器按照從右到左的順序處理FROM子句的表明，即FROM子句中最后的表（驅(qū)動(dòng)表）會(huì)最先被處理。
當(dāng)FROM子句包含多個(gè)表時(shí)，建議將記錄最少的表（一般是字典表）放在最后面。當(dāng)Oracle處理多個(gè)表時(shí)，一般采用排序或合并的方式連接這些表，系統(tǒng)首 先會(huì)掃描FROM子句部分的最后一個(gè)表，并對(duì)該表的數(shù)據(jù)行進(jìn)行排序；然后掃描倒數(shù)第二個(gè)表，并將從該表中取出的記錄與第一個(gè)表中的記錄進(jìn)行匹配合并，依此 類推。
如果是大于兩表相關(guān)聯(lián)，最好選擇交叉表為驅(qū)動(dòng)表，交叉表是指被其它表所引用的表。
RBO模式下，小表為驅(qū)動(dòng)表的執(zhí)行時(shí)間為大表是驅(qū)動(dòng)的執(zhí)行時(shí)間的：
0.078/2.253 = 2.26%！
驅(qū)動(dòng)表的選擇
此時(shí)的優(yōu)化器模式為CBO，二者的執(zhí)行時(shí)間僅僅相差：
0.328-0.313=0.015毫秒，二者幾乎接近，這是為什么呢？我們?cè)倏炊邎?zhí)行計(jì)劃：
我們發(fā)現(xiàn)，此時(shí)二者的執(zhí)行計(jì)劃 一模一樣！這又是為什么？
驅(qū)動(dòng)表的選擇
驅(qū)動(dòng)表(Driving Table)是指被最先訪問(wèn)的表，通常是以全表掃描的方式訪問(wèn)的。
如果優(yōu)化器是CBO，則優(yōu)化器會(huì)檢查SQL語(yǔ)句中每個(gè)表的物理大小、索引狀態(tài)，然后尋找開銷最小的執(zhí)行路徑。如果優(yōu)化器是RBO，且所有連接條件都有索引對(duì)應(yīng)，則驅(qū)動(dòng)表是FROM子句中最后一個(gè)表。
無(wú)論如何，我們建議始終將記錄小的表（如字典表）作為驅(qū)動(dòng)表，則能適應(yīng)CBO和RBO！
WHERE子句如何寫
Oralce優(yōu)化器的原理是采用自下而上的順序解析WHERE子句，因此表之間的連接必須寫在其他WHERE條件之前, 可過(guò)濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾 。
上述SQL語(yǔ)句的例子雖然符合優(yōu)化規(guī)范的比不符合優(yōu)化規(guī)范的寫法僅僅快了不到0.4秒，但重要的是這是在當(dāng)前單機(jī)環(huán)境、且沒有任何其它數(shù)據(jù)庫(kù)事務(wù)、業(yè)務(wù)很簡(jiǎn)單、連接的表僅有兩個(gè)表的情況下。如果在實(shí)際的大業(yè)務(wù)量環(huán)境下，則這種優(yōu)化效應(yīng)將成 倍數(shù)級(jí)增長(zhǎng)！
因此，我們建議任何時(shí)候編寫SQL語(yǔ)句時(shí)要 使用表的別名、 對(duì)表的連接永遠(yuǎn) 寫在WHERE后面的第一個(gè)位置，并對(duì)過(guò)濾條件進(jìn)行估算， 按照降序的大小將這些 條件從WHERE子句最后部分往前排列。
轉(zhuǎn)自私塾在線。
聯(lián)系客服
