數(shù)據(jù)庫培訓-高效率sql語句基礎ppt課件

1、Oracle數(shù)據(jù)庫培訓高效率SQL語句根底 -張林一個高效率的數(shù)據(jù)庫系統(tǒng)是從兩個方面來評價的：呼應時間和吞吐量。在運用系統(tǒng)開發(fā)階段，由于開發(fā)庫上的數(shù)據(jù)比較少，在SQL語句的編寫上覺得不出各種寫法的性能差別，在將運用系統(tǒng)提交實踐運用后，隨著數(shù)據(jù)庫中數(shù)據(jù)的添加，系統(tǒng)的呼應速度就會成為最需求處理的主要問題之一?？s短系統(tǒng)的呼應時間，添加操作的并發(fā)度，可以提高系統(tǒng)的吞吐量。要縮短系統(tǒng)的呼應時間，就需求可以高效率執(zhí)行的SQL語句。引言一、優(yōu)化器引見二、高效率SQL根底三、oracle的執(zhí)行方案四、書寫高效率SQL語句建議目錄Oracle數(shù)據(jù)庫中優(yōu)化器Optimizer是SQL分析和執(zhí)行的優(yōu)化工具，它擔任指

2、定SQL的執(zhí)行方案，也就是它擔任保證SQL執(zhí)行的效率最高，比如優(yōu)化器決議Oracle以什么樣的方式來訪問數(shù)據(jù)，是全表掃描Full Table Scan，索引范圍掃描Index Range Scan還是全索引快速掃描INDEX Fast Full Scan：INDEX_FFS;對于表關聯(lián)查詢，它擔任確定表之間以一種什么方式來關聯(lián)，比如HASH_JOHN還是NESTED LOOPS或者MERGE JOIN。這些要素直接決議SQL的執(zhí)行效率，所以優(yōu)化器是SQL執(zhí)行的中心，它做出的執(zhí)行方案好壞，直接決議著SQL的執(zhí)行效率。優(yōu)化器引見Oracle的優(yōu)化器有兩種：RBO(Rule-Based Optimi

3、zation):基于規(guī)那么的優(yōu)化器CBO(Cost-Based Optimization):基于代價的優(yōu)化器從Oracle 10g開場，RBO曾經被棄用，但是我們依然可以經過Hint方式來運用它。CBO的思緒是讓Oracle獲取一切執(zhí)行方案相關的信息，經過對這些信息做計算分析，最后得出一個代價最小的執(zhí)行方案作為最終的執(zhí)行方案。優(yōu)化器引見運用CBO 時，需求留意如下幾個方面：1、編寫SQL語句時，不用思索FROM 子句后面的表或視圖的順序和WHERE 子句后面的條件順序；2、運用CBO 時，SQL語句 FROM 子句后面的表的個數(shù)不宜太多，由于CBO在選擇表銜接順序時，會對FROM 子句后面的表

4、進展階乘運算，選擇最好的一個銜接順序。3、假設一個語句運用 RBO的執(zhí)行方案確實比CBO 好，那么可以經過hint機制，強迫運用RBO。Hint機制可以參考這篇文章：czmmiao.iteye/blog/1478465優(yōu)化器引見了解索引大多數(shù)情況下，數(shù)據(jù)庫運用索引來檢索表，優(yōu)化器根據(jù)用戶定義的索引來提高執(zhí)行性能。但是，假設在SQL語句的where子句中寫的SQL代碼不合理，就會呵斥優(yōu)化器忽略索引而采用全表掃描，而這種SQL語句就是所謂的劣質SQL語句。在編寫SQL語句時需求了解優(yōu)化器根據(jù)何種原那么來運用索引，這將有助于寫出高性能的SQL語句。高效率SQL根底1、條件中包含NULL值(IS NU

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

6、句: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ù)，通配符如此運用會降低查詢速度。當通配符出如今字符串其他位置時，優(yōu)化器就能利用索引。在

7、下面的查詢中索引得到了運用：select*fromemployeewherelast_namelikec%;了解索引-常見問題分析4、 order by語句order by語句決議了數(shù)據(jù)庫如何將前往的查詢結果排序。orderby語句對要排序的列沒有什么特別的限制，也可以將函數(shù)參與列中象聯(lián)接或者附加等。任何在orderby語句的非索引項或者有計算表達式都將降低查詢速度。需求仔細檢查orderby語句以找出非索引項或者表達式，它們會降低性能。處理這個問題的方法就是重寫orderby語句以運用索引，也可以為所運用的列建立另外一個索引，同時應絕對防止在orderby子句中運用表達式。了解索引-常見問題

8、分析5、 NOTNOT可用來對任何邏輯運算符號取反，例：select * from employee where not (salary=3000);要運用NOT，那么應在取反的短語前面加上括號。NOT運算符的以上方式比較少用，但是它會包含在另外一個邏輯運算符中，這就是不等于運算符，如：select * from employee where salary3000;處理方法:不運用NOT，例：select * from employee where salary3000; 這兩種查詢的結果一樣，但是第二種查詢會對salary列運用索引，會更快些，而第一種查詢那么不會運用索引。了解索引-常見問題

9、分析6、 IN和EXISTS(一)開發(fā)過程中經常會在where子句中運用子查詢，主要有兩種方式：. where column in(select column from . where .);.whereexists (selectXfrom.where.);采用第二種格式要比第一種格式的效率高。第二種格式中，子查詢以selectX開場。運用EXISTS子句不論子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不用遍歷整個表而僅根據(jù)索引就可完成任務這里假定在where語句中運用的列存在索引。了解索引-常見問題分析6、 IN和EXISTS(二)運用IN子查詢時，首先執(zhí)行子查詢，并將獲得

10、的結果列表存放在在一個加了索引的暫時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行終了，存放在暫時表中以后再執(zhí)行主查詢。所以運用EXISTS通常比運用IN查詢速度快。應盡能夠運用NOTEXISTS來替代NOTIN，雖然二者都運用了NOT(不能運用索引而降低速度)，但NOTEXISTS要比NOTIN查詢效率高。 了解索引-常見問題分析7、 不可優(yōu)化的where子句一以下這條語句在nsrsbh字段上建有恰當?shù)乃饕?，但?zhí)行卻很慢：select * from zk_kj_kpxx where substr(nsrsbh,0,6)=000000;(24秒)類似的還有如下方式的語句假設以下字段都

11、建有恰當?shù)乃饕簊elect*fromrecordwhereamount/301000; 即： where子句中對列的任何操作結果都是在SQL運轉時逐列計算得到的，因此它不得不進展表搜索，而沒有運用該列上面的索引。了解索引-常見問題分析7、 不可優(yōu)化的where子句二我們把上面的兩條語句進展重寫：select * from zk_kj_kpxx where nsrsbh like 000000%;(495毫秒)select*fromrecordwhereamount19991201anddate200026秒selectdate, sum(amount)fromrecordgroupbydat

12、e27秒selectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)19991201anddate2000(19990901andplacein(BJ,SH),=,EXPLAIN PLAN FORSELECT * FROM SCOTT.EMP; -要解析的SQL腳本SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);oracle的執(zhí)行方案1、如何查看執(zhí)行方案4在SQL*PLUS下(有些命令在PL/SQL下無效)執(zhí)行如下命令一SQLSET TIMING ON -控制顯示執(zhí)行時間統(tǒng)計數(shù)據(jù)SQL SET AUT

13、OTRACE TRACEONLY -這樣設置會有執(zhí)行方案、統(tǒng)計信息，不會有腳本數(shù)據(jù)輸出SQL執(zhí)行需求查看執(zhí)行方案的SQL語句SQLSET AUTOTRACE OFF -不生成AUTOTRACE報告，這是缺省方式這是SQL*PLUS下最常用的查看執(zhí)行方案的方式，沒有腳本數(shù)據(jù)的輸出，節(jié)省時間，易查看。oracle的執(zhí)行方案1、如何查看執(zhí)行方案4在SQL*PLUS下(有些命令在PL/SQL下無效)執(zhí)行如下命令二SQL SET AUTOTRACE ON -這樣設置包含執(zhí)行方案、統(tǒng)計信息、以及腳本數(shù)據(jù)輸出SQL執(zhí)行需求查看執(zhí)行方案的SQL語句SQLSET AUTOTRACE OFForacle的執(zhí)行方案

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

15、案2、看懂執(zhí)行方案Plan hash value這一行是這一條語句的的hash值，我們知道ORACLE對每一條ORACLE語句產生的執(zhí)行方案放在SHARE POOL里面，第一次要經過硬解析，產生hash值。下次再執(zhí)行時比較hash值，假設一樣就不會執(zhí)行硬解析。oracle的執(zhí)行方案2、看懂執(zhí)行方案id: 執(zhí)行序列，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)Operation縮進來判別采用最右最上最先執(zhí)行的原那么看層次關系，在同一級假設某個動作沒有子ID就最先執(zhí)行。普通按縮進長度來判別，縮進最大的最先執(zhí)行，假設有2行縮進一樣，那么就先執(zhí)行上面的。operation：當前操作的內容。oracle的執(zhí)行方

16、案2、看懂執(zhí)行方案Name：操作對象Rows：oracle估計當前操作的前往結果集行數(shù)。Bytes：表示執(zhí)行該步驟后前往的字節(jié)數(shù)。Cost%CPU：表示執(zhí)行到該步驟的一個執(zhí)行本錢，用于闡明SQL執(zhí)行的代價。 Cost沒有單位，是一個相對值，是SQL以CBO方式解析執(zhí)行方案時，供ORACLE來評價CBO本錢，選擇執(zhí)行方案用的。沒有明確的含義，但是在對比時非常有用Time：oracle 估計當前操作的時間。oracle的執(zhí)行方案2、看懂執(zhí)行方案謂詞闡明：Predicate Information (identified by operation id):-2 - filter(B.MGR IS N

17、OT NULL)4 - access(A.EMPNO = B.MGR)Access: 表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路勁全表掃描還是索引。要留意access，思索謂詞的條件，運用的訪問途徑能否正確。Filter：表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起過濾的作用。oracle的執(zhí)行方案2、看懂執(zhí)行方案名詞解釋：recursive calls 遞歸調用db block gets 從buffer cache中讀取的block的數(shù)量，當前懇求的塊數(shù)目consistent gets 從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量，這里的概念是在他處置他這個操作的時侯需求在

18、一致性讀形狀上處置多個塊physical reads 物理讀，就是從磁盤上讀取數(shù)據(jù)塊的數(shù)量。其產生的主要緣由是：1：在數(shù)據(jù)庫高速緩存中不存在這些塊。2：全表掃描3：磁盤排序oracle的執(zhí)行方案2、看懂執(zhí)行方案名詞解釋：redo size DML生成的redo的大小sorts (memory) 在內存執(zhí)行的排序量sorts (disk) 在磁盤執(zhí)行的排序量1610 bytes sent via SQL*Net to client 從SQL*Net向客戶端發(fā)送了1610字節(jié)的數(shù)據(jù)。519 bytes received via SQL*Net from client 客戶端向SQL*Net發(fā)送了5

19、19字節(jié)的數(shù)據(jù)。oracle的執(zhí)行方案2、看懂執(zhí)行方案假設在執(zhí)行方案中有如下提示：Note-dynamic sampling used for the statement這提示用戶CBO當前運用的技術，需求用戶在分析方案時思索這些要素。 當出現(xiàn)這個提示，闡明當前表運用了動態(tài)采樣。我們從而推斷這個表能夠沒有做過分析。oracle的執(zhí)行方案2、看懂執(zhí)行方案這里會出現(xiàn)兩種情況：1 假設表沒有做過分析，那么CBO可以經過動態(tài)采樣的方式來獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行方案。2 假設表分析過，但是分析信息過舊，這時CBO就不會在運用動態(tài)采樣，而是運用這些舊的分析數(shù)據(jù)，從而能夠導致錯誤的執(zhí)行方案。ora

20、cle的執(zhí)行方案2、看懂執(zhí)行方案訪問表方式總結：1Full Table Scan (FTS) 全表掃描2Index Lookup 索引掃描，包含如下五種方式 index unique scan -索引獨一掃描 index range scan -索引部分掃描 index full scan -索引全局掃描 index fast full scan -索引快速全局掃描，不帶order by情況下常發(fā)生 index skip scan -索引騰躍掃描，where條件列是非索引的前提下常發(fā)生3rowid 物理ID掃描，是最快的訪問數(shù)據(jù)方式oracle的執(zhí)行方案1、在select/insert語句中盡量防止運用通配符*。對于一張表而言，通配符*意味著要進展全表遍歷，對于數(shù)據(jù)量比較大的表就會給oracle添加很大的壓力。2、盡量減少between的運用。3、能運用數(shù)字類型的字段盡量運用數(shù)字類型，這將提高查詢和銜接的性能，并減少存儲