基于Oracle數(shù)據(jù)庫查詢優(yōu)化策略的研究

黃建軍　龔瑋瑋　肖英劍

摘要：隨著信息的高速發(fā)展，我們進入了云計算，大數(shù)據(jù)的時代，Oracle數(shù)據(jù)庫作為信息技術(shù)發(fā)展的重要產(chǎn)物，它是目前市場上占有率最高，使用范圍最廣泛的關(guān)系型數(shù)據(jù)庫。由于數(shù)據(jù)庫中的數(shù)據(jù)量日漸龐大，對數(shù)據(jù)的處理速度提出了更高的要求，如果對數(shù)據(jù)庫的查詢不進行優(yōu)化，將會消耗大量的系統(tǒng)資源和影響數(shù)據(jù)的查詢效率，所以開發(fā)人員應(yīng)該重視數(shù)據(jù)庫查詢優(yōu)化的重要性。本文將對Oracle數(shù)據(jù)庫的查詢優(yōu)化策略進行探討。

關(guān)鍵詞：Oracle數(shù)據(jù)庫;SQL優(yōu)化;查詢

中圖分類號：TP311 ? ? ?文獻標識碼：A

文章編號：1009-3044（2019）13-0010-02

大數(shù)據(jù)的“大”，云計算的“云”都體現(xiàn)了當今世界的信息量之大、數(shù)據(jù)規(guī)模之大，在數(shù)據(jù)庫的操作中，查詢是最常用的操作，因此我們有必要對數(shù)據(jù)庫的查詢進行優(yōu)化，提高系統(tǒng)的查詢效率。影響數(shù)據(jù)庫的查詢效率有很多因素，最常見的是沒有合理使用索引、查詢語句沒有優(yōu)化和臨時表的建立。下面我們就開始探討Oracle數(shù)據(jù)庫的查詢優(yōu)化策略。

1 Oracle的優(yōu)化器

優(yōu)化器是Oracle數(shù)據(jù)庫中內(nèi)置的一個核心系統(tǒng)，優(yōu)化器的目的是按照一定的判斷原則來得到它認為的目標SQL在當前情景下最高效的執(zhí)行路徑，簡單地說，優(yōu)化器的目的就是給SQL選擇一種最優(yōu)的執(zhí)行計劃。

Oracle在執(zhí)行一個SQL之前，首先要分析一下語句的執(zhí)行計劃，然后再按執(zhí)行計劃去執(zhí)行，分析語句的執(zhí)行計劃的工作就是由優(yōu)化器來完成。不同的情況，一條SQL可能有多種執(zhí)行計劃，但是在某一個時間點，一定只有一種花費時間最少的計劃。根據(jù)選擇執(zhí)行計劃時所用的判斷原則，Oracle的優(yōu)化器有兩種，即基于規(guī)則的優(yōu)化器（RBO）和基于成本的優(yōu)化器（CBO）。

1）基于規(guī)則的優(yōu)化器

Oracle會在代碼里事先給各種類型的執(zhí)行路徑定一個等級，一共有15個等級，從等級1到等級15。Oracle認為等級值低的執(zhí)行路徑的執(zhí)行效率比等級值高的更高，等級1所對應(yīng)的執(zhí)行路徑的執(zhí)行效率最高，等級15所對應(yīng)的執(zhí)行路徑的執(zhí)行效率最低，Oracle根據(jù)目標對象在數(shù)據(jù)字典中緩存的順序判斷選擇哪一種執(zhí)行計劃。RBO是一種適用于OLTP類型SQL語句的優(yōu)化器，RBO對數(shù)據(jù)不“敏感”，就按照優(yōu)先順序規(guī)則進行執(zhí)行計劃的選擇，因為RBO存在一些先天的缺陷，RBO在Oracle 10g中已經(jīng)不被支持，但RBO的相關(guān)實現(xiàn)代碼并沒有從Oracle數(shù)據(jù)庫的代碼中移除。

2）基于成本的優(yōu)化器

CBO選擇執(zhí)行計劃時，以目標SQL成本為判斷原則，CBO會選擇一條執(zhí)行成本最小的執(zhí)行計劃作為SQL的執(zhí)行計劃，各條執(zhí)行路徑的成本通過目標SQL語句所涉及的表、索引、列等的統(tǒng)計信息算出。這里的成本是oracle通過相關(guān)對象的統(tǒng)計信息計算出來的一個值，它實際上代表目標SQL對應(yīng)執(zhí)行步驟所消耗的IO、CPU、網(wǎng)絡(luò)資源（針對dblink下的分布式數(shù)據(jù)庫系統(tǒng)而言）的消耗量，oracle會把網(wǎng)絡(luò)資源的消耗量計算在IO成本內(nèi)，實際上你看到的成本為IO、CPU資源。

2 Oracle的執(zhí)行計劃

在Oracle數(shù)據(jù)庫中，執(zhí)行計劃是SQL優(yōu)化最為復(fù)雜也是最關(guān)鍵的部分，因為它實際上代表了目標SQL在Oracle數(shù)據(jù)庫內(nèi)部的具體執(zhí)行步驟，只有知道并了解了這些執(zhí)行步驟，我們才能知道優(yōu)化器選擇的執(zhí)行計劃是否為當前情形下最優(yōu)的執(zhí)行計劃。下面將介紹幾種Oracle里常見的執(zhí)行計劃。

2.1 表訪問相關(guān)的執(zhí)行計劃

Oracle數(shù)據(jù)庫里與表訪問相關(guān)的方法有兩種，即全盤掃描和ROWID掃描。在執(zhí)行計劃上，與全盤掃描對應(yīng)的執(zhí)行計劃的關(guān)鍵字是“table access full”，與ROWID掃描對應(yīng)的執(zhí)行計劃的關(guān)鍵字是“table access by user rowid”或者“table access by user index rowid”。

2.2 B樹索引相關(guān)的執(zhí)行計劃

Oracle數(shù)據(jù)庫里與B樹索引訪問有關(guān)的方法有索引唯一掃描、索引范圍掃描、索引全掃描、索引快速全掃描和索引跳躍式掃描。在執(zhí)行計劃上，與索引唯一掃描對應(yīng)的關(guān)鍵詞是“index unique scan”，與索引范圍掃描對應(yīng)的關(guān)鍵詞是“index range scan”，與索引全掃描對應(yīng)的關(guān)鍵詞是“index full scan”，與索引快速全掃描對應(yīng)的關(guān)鍵詞是“index fast full scan”，與索引跳躍式掃描對應(yīng)的關(guān)鍵詞是“index skip scan”。

2.3 表連接相關(guān)的執(zhí)行計劃

Oracle數(shù)據(jù)庫里與表連接相關(guān)的方法有排序合并連接、嵌套循環(huán)連接、哈希連接等，在執(zhí)行計劃上，與排序合并連接對應(yīng)的關(guān)鍵詞是“sort join”和“merge join”，與嵌套連接循環(huán)連接對應(yīng)的關(guān)鍵詞是“nested loops”，與哈希連接對應(yīng)的關(guān)鍵詞是“hash join”。

3 Oracle數(shù)據(jù)庫的查詢優(yōu)化策略的實現(xiàn)

Oracle數(shù)據(jù)庫的查詢效率受很多因素的影響，以下就一些常用于提高查詢效率的策略進行分析。

3.1 索引優(yōu)化

索引是表的一個概念部分，用來提高檢索數(shù)據(jù)的效率，減少I/O操作和消除磁盤排序，ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)。通常，通過索引查詢數(shù)據(jù)比全表掃描要快，當Oracle找出執(zhí)行查詢的最佳路徑時， Oracle優(yōu)化器將使用索引。通常在以下條件使用索引可以提高查詢速度：表的主鍵和外鍵使用索引;對經(jīng)常與其他表進行連接的表的連接字段使用索引;經(jīng)常出現(xiàn)在Where子句中的字段使用索引;選擇性高的字段建立索引。一般，在大型表中使用索引特別有效，提高查詢速度特別明顯。

3.2 SQL語句優(yōu)化

對于Oracle數(shù)據(jù)庫而言，一個SQL語句的好壞將會直接影響系統(tǒng)的性能，SQL語句的優(yōu)化是查詢優(yōu)化的一個重要的方法，下面列出常見的SQL語句優(yōu)化。

①exits和in的用法

exists對外表做循環(huán)，每次循環(huán)再對內(nèi)表進行查詢，而in是把外表和內(nèi)表做hash連接，先查詢內(nèi)表，再對外表匹配。當用exists時對內(nèi)表查詢用了索引，而對外表查詢是全部遍歷，使用in時，對外表使用索引，而內(nèi)表是有多少都全部遍歷。綜上所述，當兩個表的大小相當時，in和exists的效率差不多;當子查詢表更大的時候，使用exists效率更高;當子查詢表更小的時候，使用in效率更高。

②Where和Having的用法

Where和Having都是給查詢結(jié)果做限定條件，兩者的不同之處在于Having常用于分組的查詢，與grounp ?by配合使用。Having和Where在數(shù)據(jù)量不大的情況下，效率差不多，但當數(shù)據(jù)量很大時，效果就很明顯了，因為使用Having時，是先分組后條件判斷，需要檢索所有的記錄再條件，判斷而當我們使用Where時，我們可以先判斷再分組，分組的效率就更快了，從而查詢的效率也很明顯地提高了。

③帶通配符“%”的like語句

在我們平時實現(xiàn)系統(tǒng)的某些功能是，往往我們需要對一個表模糊查詢，模糊查詢通常會用到like和‘%，如果模糊查詢的這一列有索引，這時我們就應(yīng)該小心了，要合理的使用這種方法，如果通配符‘%在搜尋詞首出現(xiàn)，那么索引就沒有用了，這時就要全表搜索，如果在其他位置，就可以利用索引，查詢的效率就會得到提升，例如：查詢student表中name列中包含‘夢的學(xué)生學(xué)生，并且name列建立了pk_name的索引，第一種方法是select stuid from student where name like ‘%夢%;另一種方法是select stuid from student where name like ‘夢%，方法一不會使用索引pk_name，方法二會使用索引，可以提高查詢的速率。

④用TRUNCATE替代DELETE

當刪除表中的記錄時，在通常情況下，回滾段（rollback segments ） 用來存放可以被恢復(fù)的信息。如果你沒有COMMIT事務(wù)，ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)（準確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況） 而當運用TRUNCATE時， 回滾段不再存放任何可被恢復(fù)的信息。當命令運行后，數(shù)據(jù)不能被恢復(fù)，因此很少的資源被調(diào)用，執(zhí)行時間也會很短。

3.3 共享SQL語句

為了不重復(fù)解析相同的SQL語句，在第一次解析之后， Oracle將SQL語句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域SGA（system globalarea）的共享池（shared bufferpool）中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享.因此，當你執(zhí)行一個SQL語句（有時被稱為一個游標）時，如果它和之前的執(zhí)行過的語句完全相同， Oracle就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑. Oracle的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用.

4 結(jié)語

綜上所述，開發(fā)人員應(yīng)該充分地理解Oracle數(shù)據(jù)庫的優(yōu)化器的工作原理和執(zhí)行計劃，充分利用索引、合理使用SQL語句和臨時表，結(jié)合不同的實例選擇不同的優(yōu)化方式，從而提高系統(tǒng)的查詢效率。

參考文獻：

[1] 韓峰.SQL優(yōu)化最佳實踐構(gòu)建高效率Oracle數(shù)據(jù)庫的方法與技巧[M].北京：機械工業(yè)出版社，2016.

[2] 崔華.基于Oracle的SQL優(yōu)化[M]. 北京：電子工業(yè)出版社，2018.

[3] 蓋國強，李軼楠 主編.oracle性能優(yōu)化與診斷案例精選[M]. 北京：人民郵電出版社，2016.

[4] 師慶棟.Oracl查詢優(yōu)化改寫技巧與案例2.0[M]. 北京：電子工業(yè)出版社，2018

[5] [美]Karen MortonKerry OsborneRobyn SandsRiyaj ShamsudeenJared Still.精通Oracle SQL [M]. 2版. 北京：人民郵電出版社，2014.

【通聯(lián)編輯：光文玲】