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

1、數(shù)據(jù)庫培訓(xùn)高效率語句基礎(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)速度就會成為最需要解決的主要問題之一?？s短系統(tǒng)的響應(yīng)時間，增加操作的并發(fā)度，可以提高系統(tǒng)的吞吐量。要縮短系統(tǒng)的響應(yīng)時間，就需要可以高效率執(zhí)行的SQL語句。引言引言一、優(yōu)化器介紹一、優(yōu)化器介紹二、高效率二、高效率SQL根底根底三、三、oracle的執(zhí)行方案的執(zhí)行方案四、書寫高效率四、書寫高效率SQL語句建議語句建議目錄目錄Oracle數(shù)據(jù)庫中優(yōu)化器Optim

2、izer是SQL分析和執(zhí)行的優(yōu)化工具，它負責(zé)指定SQL的執(zhí)行方案，也就是它負責(zé)保證SQL執(zhí)行的效率最高，比方優(yōu)化器決定Oracle以什么樣的方式來訪問數(shù)據(jù)，是全表掃描FullTableScan，索引范圍掃描IndexRangeScan還是全索引快速掃描INDEXFastFullScan：INDEX_FFS;對于表關(guān)聯(lián)查詢，它負責(zé)確定表之間以一種什么方式來關(guān)聯(lián)，比方HASH_JOHN還是NESTEDLOOPS或者MERGEJOIN。這些因素直接決定SQL的執(zhí)行效率，所以優(yōu)化器是SQL執(zhí)行的核心，它做出的執(zhí)行方案好壞，直接決定著SQL的執(zhí)行效率。優(yōu)化器介紹優(yōu)化器介紹Oracle的優(yōu)化器有兩種：RB

3、O(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、使用CBO時，SQL語句FROM子句后面的表的個數(shù)不宜太多，因為CBO在選擇表連接順序

4、時，會對FROM子句后面的表進展階乘運算，選擇最好的一個連接順序。3、如果一個語句使用RBO的執(zhí)行方案確實比CBO好，那么可以通過hint機制，強制使用RBO。Hint機制可以參考這篇文章：:/czmmiao.iteye/blog/1478465優(yōu)化器介紹優(yōu)化器介紹理解索引大多數(shù)情況下，數(shù)據(jù)庫使用索引來檢索表，優(yōu)化器根據(jù)用戶定義的索引來提高執(zhí)行性能。但是，如果在SQL語句的where子句中寫的SQL代碼不合理，就會造成優(yōu)化器忽略索引而采用全表掃描，而這種SQL語句就是所謂的劣質(zhì)SQL語句。在編寫SQL語句時需要了解優(yōu)化器根據(jù)何種原那么來使用索引，這將有助于寫出高性能的SQL語句。高效率高效率S

5、QL根底根底1、條件中包含NULL值(ISNULL與ISNOTNULL)以NULL值做條件時，將無法使用包含NULL值的列上的索引。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，在使用NULL值做條件時，即使對該列建索引也不會提高性能。理解索引理解索引-常見問題分析常見問題分析2、列的連接列被包含到表達式中導(dǎo)致不能使用索引。對于有連接的列，即使最后的聯(lián)接值為一個靜態(tài)值，優(yōu)化器是不會使用索引的。例：假定有一個職工表(employee)，對于一個職工的姓和名分成兩列存放(FIRST_NAME)和(LAST_NAME)，現(xiàn)在要查詢一個叫B

6、illCliton的職工。下面是一個采用聯(lián)接查詢的SQL語句:select*fromemployeewherefirst_name|last_name=BillCliton;改進方法：select*fromemployeewherefirst_name=Billandlast_name=Cliton;理解索引理解索引-常見問題分析常見問題分析3、帶通配符%的like語句select*fromemployeewherelast_namelike%cliton%;由于通配符%在搜尋詞首出現(xiàn)，所以數(shù)據(jù)庫將不使用last_name的索引。在很多情況下可能無法防止這種情況，但是一定要心中有數(shù)，通配符如此

7、使用會降低查詢速度。當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用：select*fromemployeewherelast_namelikec%;理解索引理解索引-常見問題分析常見問題分析4、orderby語句orderby語句決定了數(shù)據(jù)庫如何將返回的查詢結(jié)果排序。orderby語句對要排序的列沒有什么特別的限制，也可以將函數(shù)參加列中象聯(lián)接或者附加等。任何在orderby語句的非索引項或者有計算表達式都將降低查詢速度。需要仔細檢查orderby語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的方法就是重寫orderby語句以使用索引，也可以為所使用

8、的列建立另外一個索引，同時應(yīng)絕對防止在orderby子句中使用表達式。理解索引理解索引-常見問題分析常見問題分析5、NOTNOT可用來對任何邏輯運算符號取反，例：select*fromemployeewherenot(salary=3000);要使用NOT，那么應(yīng)在取反的短語前面加上括號。NOT運算符的以上形式比較少用，但是它會包含在另外一個邏輯運算符中，這就是不等于運算符，如：select*fromemployeewheresalary3000;解決方法:不使用NOT，例：select*fromemployeewheresalary3000;這兩種查詢的結(jié)果一樣，但是第二種查詢會對salar

9、y列使用索引，會更快些，而第一種查詢那么不會使用索引。理解索引理解索引-常見問題分析常見問題分析6、IN和EXISTS(一)開發(fā)過程中經(jīng)常會在where子句中使用子查詢，主要有兩種方式：.wherecolumnin(selectcolumnfrom.where.);.whereexists(selectXfrom.where.);采用第二種格式要比第一種格式的效率高。第二種格式中，子查詢以selectX開場。運用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作這里假定在where語句中使用的列存在索引。理解索引理解索引-常見

10、問題分析常見問題分析6、IN和EXISTS(二)使用IN子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。所以使用EXISTS通常比使用IN查詢速度快。應(yīng)盡可能使用NOTEXISTS來代替NOTIN，盡管二者都使用了NOT(不能使用索引而降低速度)，但NOTEXISTS要比NOTIN查詢效率高。理解索引理解索引-常見問題分析常見問題分析7、不可優(yōu)化的where子句一以下這條語句在nsrsbh字段上建有恰當(dāng)?shù)乃饕?，但?zhí)行卻很慢：select*fromzk_kj_kpxxwheresu

11、bstr(nsrsbh,0,6)=000000;(24秒)類似的還有如下形式的語句假設(shè)以下字段都建有恰當(dāng)?shù)乃饕簊elect*fromrecordwhereamount/301000;即：where子句中對列的任何操作結(jié)果都是在SQL運行時逐列計算得到的，因此它不得不進展表搜索，而沒有使用該列上面的索引。理解索引理解索引-常見問題分析常見問題分析7、不可優(yōu)化的where子句二我們把上面的兩條語句進展重寫：select*fromzk_kj_kpxxwherensrsbhlike000000%;(495毫秒)select*fromrecordwhereamount19991201anddate20

12、0026秒selectdate,sum(amount)fromrecordgroupbydate27秒selectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)19991201anddate2000(19990901andplacein(BJ,SH),=,EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP; -要解析的要解析的SQL腳本腳本 SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); oracle的執(zhí)行方案的執(zhí)行方案1、如何查看執(zhí)行方案、如何查看執(zhí)行方案4在在SQ

13、L*PLUS下下(有些命令在有些命令在PL/SQL下無效下無效)執(zhí)行如下命令一執(zhí)行如下命令一SQLSET TIMING ON -控制顯示執(zhí)行時間統(tǒng)計數(shù)據(jù)控制顯示執(zhí)行時間統(tǒng)計數(shù)據(jù) SQL SET AUTOTRACE TRACEONLY -這樣設(shè)置會有執(zhí)行方案、統(tǒng)計這樣設(shè)置會有執(zhí)行方案、統(tǒng)計信息，不會有腳本數(shù)據(jù)輸出信息，不會有腳本數(shù)據(jù)輸出 SQL執(zhí)行需要查看執(zhí)行方案的執(zhí)行需要查看執(zhí)行方案的SQL語句語句 SQLSET AUTOTRACE OFF -不生成不生成AUTOTRACE報告，這是報告，這是缺省模式缺省模式這是這是SQL*PLUS下最常用的查看執(zhí)行方案的方式，沒有腳本數(shù)據(jù)的下最常用的查看執(zhí)行

14、方案的方式，沒有腳本數(shù)據(jù)的輸出，節(jié)省時間，易查看。輸出，節(jié)省時間，易查看。oracle的執(zhí)行方案的執(zhí)行方案1、如何查看執(zhí)行方案、如何查看執(zhí)行方案4在在SQL*PLUS下下(有些命令在有些命令在PL/SQL下無效下無效)執(zhí)行如下命令二執(zhí)行如下命令二SQL SET AUTOTRACE ON -這樣設(shè)置包含執(zhí)行方案、統(tǒng)計信這樣設(shè)置包含執(zhí)行方案、統(tǒng)計信息、以及腳本數(shù)據(jù)輸出息、以及腳本數(shù)據(jù)輸出 SQL執(zhí)行需要查看執(zhí)行方案的執(zhí)行需要查看執(zhí)行方案的SQL語句語句 SQLSET AUTOTRACE OFF oracle的執(zhí)行方案的執(zhí)行方案1、如何查看執(zhí)行方案、如何查看執(zhí)行方案4在在SQL*PLUS下下(有些命

15、令在有些命令在PL/SQL下無效下無效)執(zhí)行如下命令三執(zhí)行如下命令三SQLSET AUTOTRACE ON EXPLAIN -包含執(zhí)行方案、腳本數(shù)據(jù)輸包含執(zhí)行方案、腳本數(shù)據(jù)輸出，沒有統(tǒng)計信息出，沒有統(tǒng)計信息 SQL執(zhí)行需要查看執(zhí)行方案的執(zhí)行需要查看執(zhí)行方案的SQL語句語句SQLSET AUTOTRACE OFForacle的執(zhí)行方案的執(zhí)行方案1、如何查看執(zhí)行方案、如何查看執(zhí)行方案4在在SQL*PLUS下下(有些命令在有些命令在PL/SQL下無效下無效)執(zhí)行如下命令四執(zhí)行如下命令四SQLSET AUTOTRACE TRACEONLY STAT -這樣設(shè)置只包含有統(tǒng)計信這樣設(shè)置只包含有統(tǒng)計信息息

16、SQL執(zhí)行需要查看執(zhí)行方案的執(zhí)行需要查看執(zhí)行方案的SQL語句語句 SQLSET AUTOTRACE OFForacle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案Plan hash value 這一行是這一條語句的的這一行是這一條語句的的hash值，我們知道值，我們知道ORACLE對每一條對每一條ORACLE語句產(chǎn)生的執(zhí)行方案放在語句產(chǎn)生的執(zhí)行方案放在SHARE POOL里面，第一次要經(jīng)過硬解析，產(chǎn)生里面，第一次要經(jīng)過硬解析，產(chǎn)生hash值。下次再執(zhí)行時比較值。下次再執(zhí)行時比較hash值，如果一樣就不會執(zhí)行硬解析。值，如果一樣

17、就不會執(zhí)行硬解析。oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案id: 執(zhí)行序列，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)執(zhí)行序列，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)Operation縮縮進來判斷采用最右最上最先執(zhí)行的原那么看層次關(guān)系，在同一級如果進來判斷采用最右最上最先執(zhí)行的原那么看層次關(guān)系，在同一級如果某個動作沒有子某個動作沒有子ID就最先執(zhí)行。一般按縮進長度來判斷，縮進最大的最就最先執(zhí)行。一般按縮進長度來判斷，縮進最大的最先執(zhí)行，如果有先執(zhí)行，如果有2行縮進一樣，那么就先執(zhí)行上面的。行縮進一樣，那么就先執(zhí)行上面的。operation：當(dāng)前操作的內(nèi)容。：當(dāng)前操作的內(nèi)容。 or

18、acle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案Name：操作對象：操作對象 Rows：oracle估計當(dāng)前操作的返回結(jié)果集行數(shù)。估計當(dāng)前操作的返回結(jié)果集行數(shù)。 Bytes：表示執(zhí)行該步驟后返回的字節(jié)數(shù)。：表示執(zhí)行該步驟后返回的字節(jié)數(shù)。 Cost%CPU：表示執(zhí)行到該步驟的一個執(zhí)行本錢，用于說明：表示執(zhí)行到該步驟的一個執(zhí)行本錢，用于說明SQL執(zhí)行的代價。執(zhí)行的代價。 Cost沒有單位，是一個相對值，是沒有單位，是一個相對值，是SQL以以CBO方式解析執(zhí)方式解析執(zhí)行方案時，供行方案時，供ORACLE來評估來評估CBO本錢，選擇執(zhí)行方案用的。沒有明確的本錢，選擇執(zhí)行方案用的。沒有明確的含

19、義，但是在比照時非常有用含義，但是在比照時非常有用Time：oracle 估計當(dāng)前操作的時間。估計當(dāng)前操作的時間。 oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案謂詞說明：謂詞說明： Predicate Information (identified by operation id): - 2 - filter(B.MGR IS NOT NULL) 4 - access(A.EMPNO = B.MGR) Access: 表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路勁全表掃描還是表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路勁全表掃描還是索引。要注意索引。要注意access，考慮謂詞的條件

20、，使用的訪問路徑是否正確。，考慮謂詞的條件，使用的訪問路徑是否正確。Filter：表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起過濾的作用。：表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起過濾的作用。 oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案名詞解釋：名詞解釋：recursive calls 遞歸調(diào)用遞歸調(diào)用 db block gets 從從buffer cache中讀取的中讀取的block的數(shù)量，當(dāng)前請求的塊數(shù)目的數(shù)量，當(dāng)前請求的塊數(shù)目 consistent gets 從從buffer cache中讀取的中讀取的undo數(shù)據(jù)的數(shù)據(jù)的block的數(shù)量，這里的的數(shù)量，這里的概念

21、是在你處理你這個操作的時侯需要在一致性讀狀態(tài)上處理多個塊概念是在你處理你這個操作的時侯需要在一致性讀狀態(tài)上處理多個塊physical reads 物理讀，就是從磁盤上讀取數(shù)據(jù)塊的數(shù)量。物理讀，就是從磁盤上讀取數(shù)據(jù)塊的數(shù)量。其產(chǎn)生的主要原因是：其產(chǎn)生的主要原因是： 1：在數(shù)據(jù)庫高速緩存中不存在這些塊。：在數(shù)據(jù)庫高速緩存中不存在這些塊。 2：全表掃描：全表掃描 3：磁盤排序：磁盤排序 oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案名詞解釋：名詞解釋：redo size DML生成的生成的redo的大小的大小 sorts (memory) 在內(nèi)存執(zhí)行的排序量在內(nèi)存執(zhí)行的排序量 sor

22、ts (disk) 在磁盤執(zhí)行的排序量在磁盤執(zhí)行的排序量 1610 bytes sent via SQL*Net to client 從從SQL*Net向客戶端發(fā)送了向客戶端發(fā)送了1610字節(jié)的數(shù)據(jù)。字節(jié)的數(shù)據(jù)。 519 bytes received via SQL*Net from client 客戶端向客戶端向SQL*Net發(fā)送了發(fā)送了519字節(jié)的數(shù)據(jù)。字節(jié)的數(shù)據(jù)。oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案如果在執(zhí)行方案中有如下提示：如果在執(zhí)行方案中有如下提示： Note - -dynamic sampling used for the statement 這提示用戶這

23、提示用戶CBO當(dāng)前使用的技術(shù)，需要用戶在分析方案時考慮這些因素。當(dāng)前使用的技術(shù)，需要用戶在分析方案時考慮這些因素。 當(dāng)出現(xiàn)這個提示，說明當(dāng)前表使用了動態(tài)采樣。我們從而推斷這個表當(dāng)出現(xiàn)這個提示，說明當(dāng)前表使用了動態(tài)采樣。我們從而推斷這個表可能沒有做過分析?？赡軟]有做過分析。 oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案這里會出現(xiàn)兩種情況：這里會出現(xiàn)兩種情況： 1 如果表沒有做過分析，那么如果表沒有做過分析，那么CBO可以通過動態(tài)采樣的方式來獲取分析可以通過動態(tài)采樣的方式來獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行方案。數(shù)據(jù)，也可以或者正確的執(zhí)行方案。 2 如果表分析過，但是分析信息過舊

24、，這時如果表分析過，但是分析信息過舊，這時CBO就不會在使用動態(tài)采樣，就不會在使用動態(tài)采樣，而是使用這些舊的分析數(shù)據(jù)，從而可能導(dǎo)致錯誤的執(zhí)行方案。而是使用這些舊的分析數(shù)據(jù)，從而可能導(dǎo)致錯誤的執(zhí)行方案。oracle的執(zhí)行方案的執(zhí)行方案2、看懂執(zhí)行方案、看懂執(zhí)行方案訪問表方式總結(jié)：訪問表方式總結(jié)：1Full Table Scan (FTS) 全表掃描全表掃描2Index Lookup 索引掃描，包含如下五種方式索引掃描，包含如下五種方式 index unique scan -索引唯一掃描索引唯一掃描 index range scan -索引局部掃描索引局部掃描 index full scan -索引全局掃描索引全局掃描 index fast full scan -索引快速全局掃描，不帶索引快速全局掃描，不帶order by情況下情況下常發(fā)生常發(fā)生 index skip scan -索引跳躍掃描，索引跳躍掃描，where條件列是非索引的前提下條件列是非索引的前提下常發(fā)生常發(fā)生3rowid 物理物理ID掃描，是最快的訪問數(shù)據(jù)方式掃描，是最快的訪問數(shù)據(jù)方式oracle的執(zhí)行方案的執(zhí)行方案1、在select/in