ORACLE-Select語句執(zhí)行順序及如何提高Oracle基本查詢效率

1、【轉(zhuǎn)】 ORACLE-Select 語句執(zhí)行順序及如何提高 Oracle 基本查詢效率 博客分類： ORCALE轉(zhuǎn)自： 首先，要了解在 Oracle 中 Sql 語句運(yùn)行的機(jī)制。以下是 sql 語句的執(zhí)行步驟：1 )語法分析，分析語句的語法是否符合規(guī)范，衡量語句中各表達(dá)式的意義。2 )語義分析， 檢查語句中涉及的所有數(shù)據(jù)庫對象是否存在， 且用戶有相應(yīng)的權(quán)限。3 )視圖轉(zhuǎn)換，將涉及視圖的查詢語句轉(zhuǎn)換為相應(yīng)的對基表查詢語句。4 )表達(dá)式轉(zhuǎn)換， 將復(fù)雜的 SQL 表達(dá)式轉(zhuǎn)換為較簡單的等效連接表達(dá)式。5 )選擇優(yōu)化器，不同的優(yōu)化器一般產(chǎn)生不同的“執(zhí)行計(jì)劃”6 )選擇連接方式，ORACLE有三種連接方式

2、，對多表連接 ORACLE可選擇適 當(dāng)?shù)倪B接方式。7 )選擇連接順序， 對多表連接 ORACLE 選擇哪一對表先連接，選擇這兩表 中哪個(gè)表做為源數(shù)據(jù)表。8 )選擇數(shù)據(jù)的搜索路徑，根據(jù)以上條件選擇合適的數(shù)據(jù)搜索路徑， 如是選用全表搜索還是利用索引或是其他的方式。9 )運(yùn)行“執(zhí)行計(jì)劃” 。這里不得不提的是 Oracle 共享原理：將執(zhí)行過的 SQL 語句存放在內(nèi)存的共享池 (shared buffer pool)中，可以被所有的數(shù)據(jù)庫用戶共享當(dāng)你執(zhí)行一個(gè)SQL語句(有時(shí)被稱為一個(gè)游標(biāo) ) 時(shí), 如果它和之前的執(zhí)行過的語句完全相同 , Oracle 就能很快獲得已經(jīng)被解析的語 句以及最好的 執(zhí)行路徑

3、這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。在了解了 SQL語句的運(yùn)行機(jī)制與 Oracle共享原理后，我們可以知道 SQL語句的書寫 方式對SQL語句的執(zhí)行效率有很大的影響。 那么下面我們了解一下 SQL中Select語句中各個(gè) 關(guān)鍵字執(zhí)行的順序。SQL 語言不同于其他編程語言的最明顯特征是處理代碼的順序。在大多數(shù)據(jù)庫語言中，代碼按編碼順序被處理。但在 SQL語句中，第一個(gè)被處理的子句是 FRO M而不是第一出現(xiàn)的 SELECT SQL查詢處理的步驟序號(hào)：(8) SELECT (9) DISTINCT (11) <TOP_specification> <selec

4、t_list>(1) FROM <left_table>(3) <join_type> JOIN <right_table>(2) ON <join_condition>(4) WHERE <where_condition>(5) GROUP BY <group_by_list>(6) WITH CUBE | ROLLUP(7) HAVING <having_condition>(10) ORDER BY <order_by_list>以上每個(gè)步驟都會(huì)產(chǎn)生一個(gè)虛擬表，該虛擬表被用作下一個(gè)步驟的

5、輸入。這些虛擬表對 調(diào)用者(客戶端應(yīng)用程序或者外部查詢 )不可用。只有最后一步生成的表才會(huì)會(huì)給調(diào)用者。如 果沒有在查詢中指定某一個(gè)子句，將跳過相應(yīng)的步驟。邏輯查詢處理階段簡介：1、 FROM對FROMF句中的前兩個(gè)表執(zhí)行笛卡爾積(交叉聯(lián)接)，生成虛擬表 VT1。表名 執(zhí)行順序是從后往前，所以數(shù)據(jù)較少的表盡量放后。2、 ON:對VT1應(yīng)用ON篩選器，只有那些使為真才被插入到TV2。3、 OUTER(JOIN):如果指定了 OUTERJOIN(相對于 CROSSJOIN 或 INNER JOIN)，保留表 中未找到匹配的行將作為外部行添加到VT2,生成TV3o如果FROMF句包含兩個(gè)以上的表， 則

6、對上一個(gè)聯(lián)接生成的結(jié)果表和下一個(gè)表重復(fù)執(zhí)行步驟 1 到步驟 3，直到處理完所有的表位 置。4、 WHERE對TV3應(yīng)用 WHER篩選器，只有使為true的行才插入TV4。執(zhí)行順序?yàn)閺?前往后或者說從左到右。5、 GROUPBY:按GROUfBY子句中的列列表對 TV4中的行進(jìn)行分組，生成 TV5。執(zhí)行順 序從左往右分組。6、CUTE|ROLLUP把超組插入VT5,生成VT&7、 HAVING:對VT6應(yīng)用HAVING篩選器，只有使為 true的組插入到 VT7。Having語句 很耗資源，盡量少用8 、SELECT：處理SELECT列表，產(chǎn)生 VT&9、DISTINCT :將重

7、復(fù)的行從 VT8中刪除，產(chǎn)品 VT9。10、 ORDER BY將VT9中的行按 ORDER BYF句中的列列表順序，生成一個(gè)游標(biāo)(VC10)。 執(zhí)行順序從左到右，是一個(gè)很耗資源的語句。11、TOP從VC10的開始處選擇指定數(shù)量或比例的行，生成表TV11，并返回給調(diào)用者?？吹竭@里，應(yīng)該是清楚了整個(gè)SQL語句整個(gè)執(zhí)行的過程，那么我們就接下來進(jìn)一步要坐得就是在實(shí)現(xiàn)功能同時(shí)有考慮性能的思想，努力提高SQL的執(zhí)行效率。第一、只返回需要的數(shù)據(jù) 返回?cái)?shù)據(jù)到客戶端至少需要數(shù)據(jù)庫提取數(shù)據(jù)、網(wǎng)絡(luò)傳輸數(shù)據(jù)、客戶端接收數(shù)據(jù)以及 客戶端處理數(shù)據(jù)等環(huán)節(jié)，如果返回不需要的數(shù)據(jù)，就會(huì)增加服務(wù)器、網(wǎng)絡(luò)和客戶端的無效勞 動(dòng)，其害

8、處是顯而易見的，避免這類事件需要注意：A 、橫向來看，(1) 不要寫 SELECT *的語句，而是選擇你需要的字段。(2) 當(dāng)在 SQL 語句中連接多個(gè)表時(shí) , 請使用表的別名并把別名前綴于每個(gè)Column上.這樣一來，就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤。B 、縱向來看，(1) 合理寫 WHER子句，不要寫沒有 WHER的 SQL語句。(2) SELECT TOP N * -沒有 WHER條件的用此替代第二、盡量少做重復(fù)的工作、控制同一語句的多次執(zhí)行， 特別是一些基礎(chǔ)數(shù)據(jù)的多次執(zhí)行是很多程序員很少注意的。B 、減少多次的數(shù)據(jù)轉(zhuǎn)換， 也許需要數(shù)據(jù)轉(zhuǎn)換是設(shè)計(jì)的問題， 但

9、是減少次數(shù)是程 序員可以做到的。C、杜絕不必要的子查詢和連接表， 子查詢在執(zhí)行計(jì)劃一般解釋成外連接，多余的連接表帶來額外的開銷。D、合并對同一表同一條件的多次UPDATEE 、UPDATED作不要拆成 DELETE操作+INSERT操作的形式，雖然功能相同，但 是性能差別是很大的。第三、注意臨時(shí)表和表變量的用法在復(fù)雜系統(tǒng)中， 臨時(shí)表和表變量很難避免， 關(guān)于臨時(shí)表和表變量的用法， 需要注意：A、如果語句很復(fù)雜，連接太多，可以考慮用臨時(shí)表和表變量分步完成。B、如果需要多次用到一個(gè)大表的同一部分?jǐn)?shù)據(jù)，考慮用臨時(shí)表和表變量暫存這部分?jǐn)?shù)據(jù)。C、如果需要綜合多個(gè)表的數(shù)據(jù)，形成一個(gè)結(jié)果，可以考慮用臨時(shí)表和表

10、變量分步匯總這多個(gè)表的數(shù)據(jù)。D、其他情況下，應(yīng)該控制臨時(shí)表和表變量的使用。E、關(guān)于臨時(shí)表和表變量的選擇，很多說法是表變量在內(nèi)存，速度快，應(yīng)該首選表變量， 但是在實(shí)際使用中發(fā)現(xiàn)， (1) 主要考慮需要放在臨時(shí)表的數(shù)據(jù)量， 在數(shù)據(jù)量較多的 情況下，臨時(shí)表的速度反而更快。 (2) 執(zhí)行時(shí)間段與預(yù)計(jì)執(zhí)行時(shí)間 (多長 )F、關(guān)于臨時(shí)表產(chǎn)生使用 SELECTNTO和CREATETABLE+ INSERTINTO的選擇，一般情況下，SELECTINTO會(huì)比 CREATRABLE+ INSERTINTO 的方法快很多，但是 SELECT1NTO 會(huì)鎖定TEMPDB勺系統(tǒng)表SYSOBJECTSSYSINDEXE

11、SSYSCOLUMNS在多用戶并發(fā)環(huán)境下，容 易阻塞其他進(jìn)程，所以我的建議是，在并發(fā)系統(tǒng)中，盡量使用CREATETABLE+ INSERTINTO，而大數(shù)據(jù)量的單個(gè)語句使用中，使用 SELECT INTO。第四、注意子查詢的用法子查詢是一個(gè) SELECT查詢，它嵌套在 SELECT INSERT UPDATE DELETE語句或 其它子查詢中。任何允許使用表達(dá)式的地方都可以使用子查詢，子查詢可以使我們的編程靈 活多樣，可以用來實(shí)現(xiàn)一些特殊的功能。但是在性能上，往往一個(gè)不合適的子查詢用法會(huì)形 成一個(gè)性能瓶頸。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關(guān)子 查詢。相關(guān)子查詢可以用

12、 IN、NOT IN、EXISTS、NOT EXISTS引入。 關(guān)于相關(guān)子查詢，應(yīng)該注意：(1)A、NOT IN、NOT EXISTS的相關(guān)子查詢可以改用 LEFT JOIN代替寫法。比如：WHERE PUB_ID NOT IN (SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS') 可以改寫成：SELECT A.PUB_NAMEFROM PUBLISHERS A LEFT JOIN TITLES B ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_IDWHERE B.PUB_

13、ID IS NULL(2)SELECT TITLEFROM TITLESWHERE NOT EXISTS (SELECT TITLE_ID FROM SALES WHERE TITLE_ID = TITLES.TITLE_ID) 可以改寫成：SELECT TITLEFROM TITLES LEFT JOIN SALES ON SALES.TITLE_ID = TITLES.TITLE_IDWHERE SALES.TITLE_ID IS NULLB、 如果保證子查詢沒有重復(fù)，IN、EXISTS的相關(guān)子查詢可以用 INNER JOIN代替。 比如：SELECT PUB_NAMEFROM PUBLI

14、SHERSWHERE PUB_ID IN (SELECT PUB_ID FROM TITLESWHERE TYPE = 'BUSINESS')可以改寫成：SELECT DISTINCT A.PUB_NAMEFROMPUBLISHERS A INNERJOIN TITLES B ON B.TYPE = 'BUSINESS' ANDA.PUB_ID=B. PUB_IDC、IN的相關(guān)子查詢用 EXISTS代替，比如SELECT PUB_NAMEFROM PUBLISHERSWHERE PUB_ID IN (SELECT PUB_ID FROM TITLES WHERE

15、 TYPE = 'BUSINESS')可以用下面語句代替：SELECT PUB_NAMEFROM PUBLISHERSWHERE EXISTS (SELECT 1 FROM TITLES WHERE TYPE = 'BUSINESS' AND PUB_ID= PUBLISHERS.PUB_ID)D不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFT JOIN或者EXISTS,比如有人寫這樣的語句：SELECT JOB_DESCFROM JOBSWHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.

16、JOB_ID)=0應(yīng)該改成：SELECT JOBS.JOB_DESCFROM JOBS LEFT JOIN EMPLOYEE ON EMPLOYEE.JOB_ID=JOBS.JOB_IDWHERE EMPLOYEE.EMP_ID IS NULLSELECT JOB_DESC FROM JOBS WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0應(yīng)該改成：SELECT JOB_DESC FROM JOBSWHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=J

17、OBS.JOB_ID)第五、盡量使用索引 , 并注意對含索引列的運(yùn)算建立索引后, 并不是每個(gè)查詢都會(huì)使用索引, 在使用索引的情況下, 索引的使用效率 也會(huì)有很大的差別。只要我們在查詢語句中沒有強(qiáng)制指定索引,索引的選擇和使用方法是 SQLSERVER勺優(yōu)化器自動(dòng)作的選擇，而它選擇的根據(jù)是查詢語句的條件以及相關(guān)表的統(tǒng)計(jì)信 息，這就要求我們在寫SQL語句的時(shí)候盡量使得優(yōu)化器可以使用索弓I。為了使得優(yōu)化器能高效使用索引,寫語句的時(shí)候應(yīng)該注意：A、不要對索引字段進(jìn)行運(yùn)算，而要想辦法做變換，比如SELECT ID FROM T WHERE NUM/2=100應(yīng)改為 :SELECT ID FROM T WH

18、ERE NUM=100*2SELECT ID FROM T WHERE NUM/2=NUM1如果NUM有索引應(yīng)改為：SELECT ID FROM T WHERE NUM=NUM1*2如果NUM侑索引則不應(yīng)該改。發(fā)現(xiàn)過這樣的語句：SELECT年,月,金額 FROM 結(jié)余表 WHERE 100*年+月=2010*100+10應(yīng)該改為：SELECT年,月,金額 FROM 結(jié)余表 WHERE年=2010 AND 月=10B、不要對索引字段進(jìn)行格式轉(zhuǎn)換 日期字段的例子：WHERE CONVERT(VARCHAR(1(日期字段,120)='2010-07-15'應(yīng)該改為WHER日期字段=&

19、#39;2010-07-15' AND日期字段 <'2010-07-16TS NULL轉(zhuǎn)換的例子：WHERE ISNULL字段，”)<>”應(yīng)改為：WHERE字段 <>'WHERE ISNULL字段,'')=''不應(yīng)修改WHERE ISNULL字段,'F') ='T'應(yīng)改為:WHERE字段 ='T'WHERE ISNULL字段 ,'F')<>'T'不應(yīng)修改C、不要對索引字段使用函數(shù)WHERE LEFT(NAME, 3)=

20、'ABC' 或者 WHERE SUBSTRING(NAME,1, 3)='ABC' 應(yīng)改為： WHERE NAME LIKE 'ABC%'日期查詢的例子：WHERE DATEDIFF(DAY,日期,'2010-06-30')=0應(yīng)改為：WHERE日期 >='2010-06-30' AND日期 <'2010-07-01'WHERE DATEDIFF(DAY日期 ,'2010-06-30')>0應(yīng)改為：WHERE日期 <'2010-06-30'WH

21、ERE DATEDIFF(DAY日期 ,'2010-06-30')>=0應(yīng)改為：WHERE日期 <'2010-07-01'WHERE DATEDIFF(DAY日期 ,'2010-06-30')<0應(yīng)改為：WHERE日期 >='2010-07-01'WHERE DATEDIFF(DAY日期 ,'2010-06-30')<=0應(yīng)改為：WHERE日期 >='2010-06-30'D、不要對索引字段進(jìn)行多字段連接比如：WHERE FAME+ '. '+LN

22、AME='HAIWEI.YANG'應(yīng)改為 ：WHERE FNAME='HAIWEI' AND LNAME='YANG' 第六、注意多表連接的連接條件的選擇與表示多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的 時(shí)候需要特別注意。A 、多表連接的時(shí)候，連接條件必須寫全，寧可重復(fù)，不要缺漏。B 、連接條件盡量使用聚集索引C 、注意ON WHER和HAVING部分條件的區(qū)別：ON是最先執(zhí)行， WHERE次之，HAVING最后，因?yàn)?ON是先把不符合條件的記錄過濾后才進(jìn)行統(tǒng)計(jì)，它就可以減少中間運(yùn)算 要處理的數(shù)據(jù)，按理說應(yīng)該速度是最快的，WHER也應(yīng)該比HAVING快點(diǎn)的，因?yàn)樗^濾數(shù)據(jù)后才進(jìn)行SUM在兩個(gè)表聯(lián)接時(shí)才用ON的，所以在一個(gè)表的時(shí)候，就剩下WHER跟 HAVING比較了1 、考慮聯(lián)接優(yōu)先順序：2 、 INNER JOIN3 、 LEFT JOIN （ 注： RIGHT JOIN 用 LEFT JOIN 替代）4 、 CROSS JOIN