Oracle索引在數(shù)據(jù)查詢中的應(yīng)用

夏忠球，俞國紅

（蘇州健雄職業(yè)技術(shù)學(xué)院，江蘇 蘇州 215411）

Oracle索引在數(shù)據(jù)查詢中的應(yīng)用

夏忠球，俞國紅

（蘇州健雄職業(yè)技術(shù)學(xué)院，江蘇 蘇州 215411）

文章以O(shè)racle數(shù)據(jù)庫生產(chǎn)實踐中的數(shù)據(jù)掃描為主要研究內(nèi)容，結(jié)合索引技術(shù)等相關(guān)知識，提出了合理使用索引技術(shù)，避免執(zhí)行全表掃描的設(shè)計總體思路，通過實際的需求分析，介紹了基于索引的SQL語句優(yōu)化方法，實現(xiàn)數(shù)據(jù)庫的高效查詢。

索引；索引掃描；Oracle

隨著大數(shù)據(jù)的廣泛應(yīng)用，數(shù)據(jù)高效查詢已經(jīng)在生產(chǎn)實踐中成為新的研究熱點。數(shù)據(jù)量大的時候，常出現(xiàn)軟件性能的問題，需要反復(fù)修改結(jié)構(gòu)化查詢語言（Structured Query Language，SQL）語句，調(diào)試性能以達(dá)到最優(yōu)。利用數(shù)據(jù)索引技術(shù)，正確使用數(shù)據(jù)庫的索引，解決海量數(shù)據(jù)查詢處理難的問題，不僅可實現(xiàn)高效的查詢，而且提高磁盤IO的讀寫效率。

1 Oracle數(shù)據(jù)掃描技術(shù)

1.1 索引概述

索引是數(shù)據(jù)庫中的對象，類似書的目錄結(jié)構(gòu)，Oracle的索引創(chuàng)建在數(shù)據(jù)表的列。索引作為一種獨立的數(shù)據(jù)結(jié)構(gòu)，需要占用存儲空間。索引最大的作用是加快查詢速度，其直接指向包含所查詢值的行的位置，并存儲了表中某一列的所有值。一個索引是基于數(shù)據(jù)表中的某一列而創(chuàng)建的，并且這列是排過序的。索引使用的數(shù)據(jù)結(jié)構(gòu)有兩種：MsSQL使用的是B+Tree結(jié)構(gòu)，而Oracle及Sysbase使用的是B-Tree結(jié)構(gòu)。

索引使用默認(rèn)的鍵值排序來取代全表掃描，提高查詢效率。那什么是全表掃描呢？全表掃描就是獲取表中所有數(shù)據(jù)塊，再根據(jù)條件進行過濾。在數(shù)據(jù)庫中，對沒有索引的表進行查詢，搜尋表中每一條記錄，直到找到所有符合給定條件的記錄的過程，稱為全表掃描。

1.2 數(shù)據(jù)庫索引的工作原理

Oracle使用ROWID列來建立內(nèi)部索引。那什么是ROWID呢？ROWID為數(shù)據(jù)表中行的唯一標(biāo)識，是一個偽列，可以用在SELECT中，但不可以用INSERT，UPDATE來修改該值。每個表Oracle都存在一個偽列ROWID，這個偽列可以用SELECT查看，但是不可以用INSERT和UPDATE，DELETE命令來修改刪除操作。

數(shù)據(jù)庫執(zhí)行查詢時，首先會檢查表中的列有沒有創(chuàng)建索引，如果該列有索引，數(shù)據(jù)庫還要判斷是否應(yīng)該使用索引檢索要查找的值。

查詢數(shù)據(jù)庫的結(jié)果集時，當(dāng)讀取的數(shù)據(jù)比重大，占表數(shù)據(jù)20%～70%時，最好使用全掃描。一般超過10%的數(shù)據(jù)要讀取就會選擇全表掃描。

訪問數(shù)據(jù)有兩種基本的數(shù)據(jù)訪問途徑：全表掃描和索引掃描。索引掃描每次有IO操作，一次是對索引塊，一次是對數(shù)據(jù)塊。

1.3 數(shù)據(jù)庫索引使用的場合

是否使用索引，由Oracle優(yōu)化器決定。Oracle的優(yōu)化器有3種：RULE（基于規(guī)則）優(yōu)化器、COST（基于成本）優(yōu)化器、CHOOSE（選擇性）優(yōu)化器。缺省情況下，Oracle采用CHOOSE優(yōu)化器，因為該優(yōu)化器默認(rèn)選擇的是全表掃描，所以應(yīng)該盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化器。

1.4 索引的種類

根據(jù)數(shù)據(jù)存儲方式的類型，可分為B-Tree索引、反向索引、位圖索引3類。

根據(jù)創(chuàng)建索引所在列的數(shù)量，可分為單列索引和組合索引，組合索引又稱為復(fù)合索引。單列索引：基于單個列創(chuàng)建的B-Tree索引，是Oracle默認(rèn)的索引類型。復(fù)合索引：基于兩列或多列創(chuàng)建的索引，就是復(fù)合索引。復(fù)合索引引導(dǎo)列的選擇：（1）前綴性，條件中最常使用到的字段作引導(dǎo)列；（2）選擇性，選擇性高的字段作引導(dǎo)列。

2 Oracle組合索引的創(chuàng)建

建立組合索引需要遵循的原則：（1）引導(dǎo)列要選擇過濾條件的列作為引導(dǎo)列，比如where c.yyy='yyy'或者c.yyy〉或者c.yyy〈。引導(dǎo)列的選擇性越高越好，因為選擇性越高，掃描的leaf block就越少，效率就越高。（2）盡量把join列放到組合索引最后面，排序時應(yīng)按照組合索引中各列的順序進行排序，即使索引中只有一個列時也要排序。

3 Oracle掃描的優(yōu)化

3.1 使用索引掃描

索引掃描，因為有了索引這個有序的數(shù)據(jù)結(jié)構(gòu)作支持，只訪問需要的索引塊和數(shù)據(jù)塊，而不是訪問整張表，所以會比全表掃描的效率高。

例如，select count（*）from table1，主鍵索引掃描，要優(yōu)于全表掃描。因為主鍵索引中，已經(jīng)包含所有記錄的主鍵。

下列3種情況，會使用索引掃描。

（1）沒有謂語，但select列表中，其中一列有索引。

（2）謂語中包含一個位于索引中非引導(dǎo)列中的條件。

（3）數(shù)據(jù)可以通過一個排過序的索引來獲取并且會省略單獨的排序步驟。

3.2 使用全表掃描

試想，對一張擁有百萬級數(shù)據(jù)的表進行全表掃描，其掃描的性能會非常差。那么哪些情況適合使用全表掃描呢？下列3種情況，會使用全表掃描。

（1）表包含的數(shù)據(jù)行很小，使用索引占用的存儲空間比表占用的空間反而還要大。

（2）訪問的數(shù)據(jù)占全表數(shù)據(jù)的百分比大（一般超過70%），通過索引訪問的總成本大于全表掃描的成本。

（3）表包含的數(shù)據(jù)未進行排序。

3.3 會引起全表掃描的幾種SQL查詢語句

3.3.1 SQL中使用了模糊查詢

原因：like由于是模糊查詢，效率比較低，查詢條件應(yīng)該盡量避免使用like；對于全模糊查找，形如like‘%...%’，是無法使用索引的。另外，由于匹配算法的關(guān)系，模糊查詢的字段長度越大，模糊查詢效率越低。

解決辦法：首先盡量避免模糊查詢，如果因為業(yè)務(wù)需要一定要使用模糊查詢，則至少保證不要使用全模糊查詢，對于右模糊查詢，即like‘…%’，是會使用索引的；左模糊like‘%...’無法直接使用索引，但可以變化成like‘…%’，則可使用索引。

3.3.2 SQL中使用了空值判斷

原因：在數(shù)據(jù)庫中，如果不知道某行記錄的某列具體數(shù)據(jù)，通常會使用NULL來表示，NULL是空值的含義。查詢條件中含有is null的select語句執(zhí)行慢，會導(dǎo)致數(shù)據(jù)庫引擎放棄使用索引而進行全表掃描。

解決方法：可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：select id from t where num=0。

3.3.3 SQL中使用了不等于操作符

原因：SQL中，不等于操作符會限制索引，引發(fā)全表掃描。在索引使用過程中，被索引的表數(shù)據(jù)存儲在索引的葉結(jié)點中，索引只能查找索引中存在的數(shù)據(jù)。如果where條件中引用了不等于運算符（〈〉，!=），即使比較的字段上有索引，索引也無法發(fā)揮作用。

解決方法：把不等于操作符改成or，可以使用索引，避免全表掃描。例如，把columnA〈〉’bbb’，改寫成columnA〈’bbb’or columnA〉’bbb’，就可以使用索引了。

3.3.4 or語句使用不當(dāng)

or語句使用不當(dāng)會引起全表掃描。原因：where子句中比較的兩個條件，一個條件有索引，另外一個條件沒索引，使用or連接兩個條件則會引起全表掃描。

4 Oracle索引使用的注意點

4.1 預(yù)防索引陷阱

一張表中如果有上百萬條數(shù)據(jù)，對某個字段加了索引，但是查詢性能并沒有提高，可能就是Oracle索引失效造成的。Oracle索引的目標(biāo)是避免全表掃描，提高查詢效率，但有些時候卻適得其反，SQL優(yōu)化會觸發(fā)索引陷阱，造成索引失效。

在Oracle創(chuàng)建索引的實際操作中會出現(xiàn)的限制條件，如果在數(shù)據(jù)查詢中違反了索引限制條件，那么添加的索引不會執(zhí)行，Oracle仍然會自動選擇執(zhí)行全表掃描，反而可能由于數(shù)據(jù)庫維護索引的系統(tǒng)開銷造成查詢性能更差。

4.2 索引創(chuàng)建策略

索引創(chuàng)建策略主要包含5條建議：對于取值范圍很小的字段（比如性別字段）應(yīng)當(dāng)建立位圖索引；為索引設(shè)置合適的PCTFREE值，即指定數(shù)據(jù)塊中必需保留的最小空間的比例；存儲索引的表空間最好單獨設(shè)定導(dǎo)入數(shù)據(jù)后再創(chuàng)建索引；不需要為很小的表創(chuàng)建索引；限制表中的索引的數(shù)目。

[1]鄧小培，孫洪濤.Oracle的索引[J].科技信息，2010（13）：66.

[2]李素奇.關(guān)于SQL索引建立規(guī)則與優(yōu)化的探討[J].科技展望，2014（19）：1-2.

[3]曾明霏，劉強.基于SQL和表設(shè)計的Oracle數(shù)據(jù)庫開發(fā)審計研究[J].軟件導(dǎo)刊，2016（12）：136-138.

[4]杭聰，黃連月，黃鑫.數(shù)據(jù)庫SQL審查與性能優(yōu)化技術(shù)研究與應(yīng)用[J].電力信息與通信技術(shù)，2016（4）：146-153.

[5]李永亮.Oracle數(shù)據(jù)庫中數(shù)據(jù)訪問優(yōu)化方法[J].科技視界，2015（15）：66.

Application of Oracle index in data query

Xia Zhongqiu, Yu Guohong
（Suzhou Chien-shiung Institute of Technology, Suzhou 215411, China）

Taking the data scanning of Oracle database in the practical production as the main research content, combining with the index technology and other related knowledge, this paper puts forward the rational use of indexing technology to avoid the overall design idea of performing full table scans, through the actual demand analysis, introduces the SQL statement optimization method based on index to realize the efficient query of database.

index; index scan; Oracle

2015年江蘇省現(xiàn)代教育技術(shù)課題；項目名稱：高職移動交互式數(shù)字教材的開發(fā)與應(yīng)用研究；項目編號：2015-R-26864。

夏忠球（1964— ），男，江蘇太倉人，工程師，學(xué)士；研究方向：網(wǎng)絡(luò)安全，數(shù)據(jù)庫技術(shù)及應(yīng)用。