數(shù)據(jù)庫培訓(xùn)-高效率sql語句基礎(chǔ)

1、OracleOracle數(shù)據(jù)庫培訓(xùn)數(shù)據(jù)庫培訓(xùn)高效率高效率SQL語句基礎(chǔ)語句基礎(chǔ) -張林張林一個高效率的數(shù)據(jù)庫系統(tǒng)是從兩個方面來評價的：響應(yīng)時間和吞吐量。在應(yīng)用系統(tǒng)開發(fā)階段，由于開發(fā)庫上的數(shù)據(jù)比較少，在SQL語句的編寫上感覺不出各種寫法的性能差異，在將應(yīng)用系統(tǒng)提交實際應(yīng)用后，隨著數(shù)據(jù)庫中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就會成為最需要解決的主要問題之一。縮短系統(tǒng)的響應(yīng)時間，增加操作的并發(fā)度，可以提高系統(tǒng)的吞吐量。要縮短系統(tǒng)的響應(yīng)時間，就需要可以高效率執(zhí)行的SQL語句。引言引言一、優(yōu)化器介紹一、優(yōu)化器介紹二、高效率二、高效率SQL基礎(chǔ)基礎(chǔ)三、三、oracle的執(zhí)行計劃的執(zhí)行計劃四、書寫高效率四、書寫高效率

2、SQL語句建議語句建議目錄目錄Oracle數(shù)據(jù)庫中優(yōu)化器（Optimizer）是SQL分析和執(zhí)行的優(yōu)化工具，它負(fù)責(zé)指定SQL的執(zhí)行計劃，也就是它負(fù)責(zé)保證SQL執(zhí)行的效率最高，比如優(yōu)化器決定Oracle以什么樣的方式來訪問數(shù)據(jù)，是全表掃描（FullTableScan），索引范圍掃描（IndexRangeScan）還是全索引快速掃描（INDEXFastFullScan：INDEX_FFS）;對于表關(guān)聯(lián)查詢，它負(fù)責(zé)確定表之間以一種什么方式來關(guān)聯(lián)，比如HASH_JOHN還是NESTEDLOOPS或者M(jìn)ERGEJOIN。這些因素直接決定SQL的執(zhí)行效率，所以優(yōu)化器是SQL執(zhí)行的核心，它做出的執(zhí)行計劃好壞

3、，直接決定著SQL的執(zhí)行效率。優(yōu)化器介紹優(yōu)化器介紹Oracle的優(yōu)化器有兩種：RBO(Rule-BasedOptimization):基于規(guī)則的優(yōu)化器CBO(Cost-BasedOptimization):基于代價的優(yōu)化器從Oracle10g開始，RBO已經(jīng)被棄用，但是我們依然可以通過Hint方式來使用它。CBO的思路是讓Oracle獲取所有執(zhí)行計劃相關(guān)的信息，通過對這些信息做計算分析，最后得出一個代價最小的執(zhí)行計劃作為最終的執(zhí)行計劃。優(yōu)化器介紹優(yōu)化器介紹使用CBO時，需要注意如下幾個方面：1、編寫SQL語句時，不必考慮FROM子句后面的表或視圖的順序和WHERE子句后面的條件順序；2、使用C

4、BO時，SQL語句FROM子句后面的表的個數(shù)不宜太多，因為CBO在選擇表連接順序時，會對FROM子句后面的表進(jìn)行階乘運算，選擇最好的一個連接順序。3、如果一個語句使用RBO的執(zhí)行計劃確實比CBO好，則可以通過hint機(jī)制，強(qiáng)制使用RBO。Hint機(jī)制可以參考這篇文章：http:/ PredicateInformation(identifiedbyoperationid):-2-filter(B.MGRISNOTNULL)4-access(A.EMPNO=B.MGR)Access:表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路勁（全表掃描還是索引）。要注意access，考慮謂詞的條件，使用的訪問路徑是

5、否正確。Filter：表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起過濾的作用。oracle的執(zhí)行計劃的執(zhí)行計劃2、看懂執(zhí)行計劃、看懂執(zhí)行計劃名詞解釋：recursivecalls遞歸調(diào)用dbblockgets從buffercache中讀取的block的數(shù)量，當(dāng)前請求的塊數(shù)目consistentgets從buffercache中讀取的undo數(shù)據(jù)的block的數(shù)量，這里的概念是在你處理你這個操作的時侯需要在一致性讀狀態(tài)上處理多個塊physicalreads物理讀，就是從磁盤上讀取數(shù)據(jù)塊的數(shù)量。其產(chǎn)生的主要原因是：1：在數(shù)據(jù)庫高速緩存中不存在這些塊。2：全表掃描3：磁盤排序oracle的執(zhí)行計劃的

6、執(zhí)行計劃2、看懂執(zhí)行計劃、看懂執(zhí)行計劃名詞解釋：redosizeDML生成的redo的大小sorts(memory)在內(nèi)存執(zhí)行的排序量sorts(disk)在磁盤執(zhí)行的排序量1610bytessentviaSQL*Nettoclient從SQL*Net向客戶端發(fā)送了1610字節(jié)的數(shù)據(jù)。519bytesreceivedviaSQL*Netfromclient客戶端向SQL*Net發(fā)送了519字節(jié)的數(shù)據(jù)。oracle的執(zhí)行計劃的執(zhí)行計劃2、看懂執(zhí)行計劃、看懂執(zhí)行計劃如果在執(zhí)行計劃中有如下提示：Note-dynamicsamplingusedforthestatement這提示用戶CBO當(dāng)前使用的技

7、術(shù)，需要用戶在分析計劃時考慮這些因素。當(dāng)出現(xiàn)這個提示，說明當(dāng)前表使用了動態(tài)采樣。我們從而推斷這個表可能沒有做過分析。oracle的執(zhí)行計劃的執(zhí)行計劃2、看懂執(zhí)行計劃、看懂執(zhí)行計劃這里會出現(xiàn)兩種情況：（1）如果表沒有做過分析，那么CBO可以通過動態(tài)采樣的方式來獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行計劃。（2）如果表分析過，但是分析信息過舊，這時CBO就不會在使用動態(tài)采樣，而是使用這些舊的分析數(shù)據(jù)，從而可能導(dǎo)致錯誤的執(zhí)行計劃。oracle的執(zhí)行計劃的執(zhí)行計劃2、看懂執(zhí)行計劃、看懂執(zhí)行計劃訪問表方式總結(jié)：（1）FullTableScan(FTS)全表掃描（2）IndexLookup索引掃描，包含如下五種

8、方式indexuniquescan-索引唯一掃描indexrangescan-索引局部掃描indexfullscan-索引全局掃描indexfastfullscan-索引快速全局掃描，不帶orderby情況下常發(fā)生indexskipscan-索引跳躍掃描，where條件列是非索引的前提下常發(fā)生（3）rowid物理ID掃描，是最快的訪問數(shù)據(jù)方式oracle的執(zhí)行計劃的執(zhí)行計劃1、在select/insert語句中盡量避免使用通配符（*）。對于一張表而言，通配符（*）意味著要進(jìn)行全表遍歷，對于數(shù)據(jù)量比較大的表就會給oracle增加很大的壓力。2、盡量減少between的使用。3、能使用數(shù)字類型的字

9、段盡量使用數(shù)字類型，這將提高查詢和連接的性能，并減少存儲開銷。引擎在處理查詢和連接時會逐個比較字符串中的每個字符，對于數(shù)字類型只需要比較一次。4、盡量減少isnull和isnotnull的使用，在任何where子句中使用isnull和isnotnull的語句oracle內(nèi)部優(yōu)化器是不允許使用索引的。書寫高效率書寫高效率SQL語句建議語句建議5、優(yōu)化where字句中的連接順序。oracle采用自下而上的順序解析where子句，根據(jù)這個原理，表之間的連接必須寫在其他where條件之前，可以過濾掉最大數(shù)據(jù)記錄的條件必須寫在where字句的末尾。6、減少訪問數(shù)據(jù)庫的次數(shù)（使用綁定變量）。7、合理使用like關(guān)鍵字，如非必要盡量減少like關(guān)鍵字的使用。8、oracle對于相同的語句，如果大小寫或空格不同，oracle也會認(rèn)為是不同的，因此編寫sql語句的時候注意語句的大小寫保持一致，可以減少訪問數(shù)據(jù)庫的次數(shù)，實際上減少oracle的工作量，這對于多用戶多次執(zhí)行相同的語句非常有意義。書寫高效率書寫高效率SQL語句建議語句建議9、書寫sql語句的時候不要在索引字段上進(jìn)行任何操作，包括函數(shù)操作，例如：select*fromempwheresal*1210000;應(yīng)該寫成select*fromempwheresal10000