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

黃建軍　龔瑋瑋　肖英劍

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

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

中圖分類號(hào)：TP311 ? ? ?文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2019）13-0010-02

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

1 Oracle的優(yōu)化器

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

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

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

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

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

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

2 Oracle的執(zhí)行計(jì)劃

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

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

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

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

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

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

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

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

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

3.1 索引優(yōu)化

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

3.2 SQL語(yǔ)句優(yōu)化

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

①exits和in的用法

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

②Where和Having的用法

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

③帶通配符“%”的like語(yǔ)句

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

④用TRUNCATE替代DELETE

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

3.3 共享SQL語(yǔ)句

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

4 結(jié)語(yǔ)

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

參考文獻(xiàn)：

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

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

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

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

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

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