Oracle索引在數據查詢中的應用

夏忠球，俞國紅

（蘇州健雄職業技術學院，江蘇 蘇州 215411）

Oracle索引在數據查詢中的應用

夏忠球，俞國紅

（蘇州健雄職業技術學院，江蘇 蘇州 215411）

文章以Oracle數據庫生產實踐中的數據掃描為主要研究內容，結合索引技術等相關知識，提出了合理使用索引技術，避免執行全表掃描的設計總體思路，通過實際的需求分析，介紹了基于索引的SQL語句優化方法，實現數據庫的高效查詢。

索引；索引掃描；Oracle

隨著大數據的廣泛應用，數據高效查詢已經在生產實踐中成為新的研究熱點。數據量大的時候，常出現軟件性能的問題，需要反復修改結構化查詢語言（Structured Query Language，SQL）語句，調試性能以達到最優。利用數據索引技術，正確使用數據庫的索引，解決海量數據查詢處理難的問題，不僅可實現高效的查詢，而且提高磁盤IO的讀寫效率。

1 Oracle數據掃描技術

1.1 索引概述

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

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

1.2 數據庫索引的工作原理

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

數據庫執行查詢時，首先會檢查表中的列有沒有創建索引，如果該列有索引，數據庫還要判斷是否應該使用索引檢索要查找的值。

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

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

1.3 數據庫索引使用的場合

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

1.4 索引的種類

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

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

2 Oracle組合索引的創建

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

3 Oracle掃描的優化

3.1 使用索引掃描

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

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

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

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

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

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

3.2 使用全表掃描

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

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

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

（3）表包含的數據未進行排序。

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

3.3.1 SQL中使用了模糊查詢

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

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

3.3.2 SQL中使用了空值判斷

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

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

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

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

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

3.3.4 or語句使用不當

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

4 Oracle索引使用的注意點

4.1 預防索引陷阱

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

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

4.2 索引創建策略

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

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

[2]李素奇.關于SQL索引建立規則與優化的探討[J].科技展望，2014（19）：1-2.

[3]曾明霏，劉強.基于SQL和表設計的Oracle數據庫開發審計研究[J].軟件導刊，2016（12）：136-138.

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

[5]李永亮.Oracle數據庫中數據訪問優化方法[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年江蘇省現代教育技術課題；項目名稱：高職移動交互式數字教材的開發與應用研究；項目編號：2015-R-26864。

夏忠球（1964— ），男，江蘇太倉人，工程師，學士；研究方向：網絡安全，數據庫技術及應用。