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