Oracle Index 的三個問題
2024-08-29 13:29:06
供稿：網友
索引 ( index ) 是常見的數據庫對象，它的設置好壞、使用是否得當，極大地影響數據庫應用程序和 database 的性能。雖然有許多資料講索引的用法， dba 和 developer 們也經常與它打交道，但筆者發現，還是有不少的人對它存在誤解，因此針對使用中的常見問題，講三個問題。此文所有示例所用的數據庫是 oracle 8.1.7 ops on hp n series , 示例全部是真實數據，讀者不需要注意具體的數據大小，而應注意在使用不同的方法后，數據的比較。本文所講基本都是陳詞濫調，但是筆者試圖通過實際的例子，來真正讓您明白事情的關鍵。
第一講、索引并非總是最佳選擇
如果發現 oracle 在有索引的情況下，沒有使用索引，這并不是 oracle 的優化器出錯。在有些情況下， oracle 確實會選擇全表掃描（ full table scan ） , 而非索引掃描（ index scan ）。這些情況通常有：
1. 表未做 statistics, 或者 statistics 陳舊，導致 oracle 判斷失誤。
2. 根據該表擁有的記錄數和數據塊數，實際上全表掃描要比索引掃描更快。
對第 1 種情況，最常見的例子，是以下這句 sql 語句：
select count(*) from mytable;
在未作 statistics 之前，它使用全表掃描，需要讀取 6000 多個數據塊（一個數據塊是 8k ） , 做了 statistics 之后，使用的是 index (fast full scan) ，只需要讀取 450 個數據塊。但是， statistics 做得不好，也會導致 oracle 不使用索引。
第 2 種情況就要復雜得多。一般概念上都認為索引比表快，比較難以理解什么情況下全表掃描要比索引掃描快。為了講清楚這個問題，這里先介紹一下 oracle 在評估使用索引的代價（ cost ）時兩個重要的數據： cf(clustering factor) 和 ff(filtering factor).
cf: 所謂 cf, 通俗地講，就是每讀入一個索引塊，要對應讀入多少個數據塊。
ff: 所謂 ff, 就是該 sql 語句所選擇的結果集，占總的數據量的百分比。
大約的計算公式是： ff * (cf + 索引塊個數 ) ，由此估計出，一個查詢， 如果使用某個索引，會需要讀入的數據塊塊數。需要讀入的數據塊越多，則 cost 越大， oracle 也就越可能不選擇使用 index. （全表掃描需要讀入的數據塊數等于該表的實際數據塊數）
其核心就是， cf 可能會比實際的數據塊數量大。 cf 受到索引中數據的排列方式影響，通常在索引剛建立時，索引中的記錄與表中的記錄有良好的對應關系， cf 都很小；在表經過大量的插入、修改后，這種對應關系越來越亂， cf 也越來越大。此時需要 dba 重新建立或者組織該索引。
如果某個 sql 語句以前一直使用某索引，較長時間后不再使用，一種可能就是 cf 已經變得太大，需要重新整理該索引了。
ff 則是 oracle 根據 statistics 所做的估計。比如 , mytables 表有 32 萬行，其主鍵 myid 的最小值是 1 ，最大值是 409654 ，考慮以下 sql 語句：
select * from mytables where myid>=1; 和
select * from mytables where myid>=400000
這兩句看似差不多的 sql 語句，對 oracle 而言，卻有巨大的差別。因為前者的 ff 是 100% ， 而后者的 ff 可能只有 1% 。如果它的 cf 大于實際的數據塊數，則 oracle 可能會選擇完全不同的優化方式。而實際上，在我們的數據庫上的測試驗證了我們的預測 . 以下是在 hp 上執行時它們的 explain plan:
第一句：
sql> select * from mytables where myid>=1;
已選擇 325917 行。
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=3132 card=318474 byt es=141402456)
1 0 table access (full) of 'mytables' (cost=3132 card=318474 byt es=141402456)
statistics
----------------------------------------------------------
7 recursive calls
89 db block gets
41473 consistent gets
19828 physical reads
0 redo size
131489563 bytes sent via sql*net to client
1760245 bytes received via sql*net from client
21729 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
325917 rows processed
第二句：
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=346 card=663 bytes=2 94372)
1 0 table access (by index rowid) of 'mytables' (cost=346 card=663
bytes=294372)
2 1 index (range scan) of 'pk_mytables' (unique) (cost=5 card=663)
statistics
----------------------------------------------------------
1278 recursive calls
0 db block gets
6647 consistent gets
292 physical reads
0 redo size
3544898 bytes sent via sql*net to client
42640 bytes received via sql*net from client
524 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7838 rows processed
顯而易見，第 1 句沒有使用索引，第 2 句使用了主鍵索引 pk_mytables. ff 的巨大影響由此可見一斑。由此想到，我們在寫 sql 語句時，如果預先估計一下 ff, 你就幾乎可以預見到 oracle 會否使用索引。
第二講、索引也有好壞
索引有 b tree 索引， bitmap 索引， reverse b tree 索引， 等。最常用的是 b tree 索引。 b 的全稱是 balanced , 其意義是，從 tree 的 root 到任何一個 leaf ，要經過同樣多的 level. 索引可以只有一個字段（ single column ） , 也可以有多個字段（ composite ） , 最多 32 個字段， 8i 還支持 function-based index. 許多 developer 都傾向于使用單列 b 樹索引。
除此之外呢？我們還是來看一個例子吧：
在 hp （ oracle 8.1.7 ） 上執行以下語句：
select count(1) from mytabs where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd') 。
一開始，我們有兩個單列索引： i_mytabs1(coid), i_mytabs2(issuedate), 下面是執行情況：
count(1)
----------
6427
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=384 card=1 bytes=11)
1 0 sort (aggregate)
2 1 table access (by index rowid) of 't_mytabs' (cost=384 card
=126 bytes=1386)
3 2 index (range scan) of 'i_mytabs2' (non-unique) (cost=11
card=126)
statistics
----------------------------------------------------------
172 recursive calls
1 db block gets
5054 consistent gets
2206 physical reads
0 redo size
293 bytes sent via sql*net to client
359 bytes received via sql*net from client
2 sql*net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，它讀取了 7000 個數據塊來獲得所查詢的 6000 多行。
現在，去掉這兩個單列索引，增加一個復合索引 i_mytabs_test ( coid, issuedate), 重新執行，結果如下：
count(1)
----------
6436
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=3 card=1 bytes=11)
1 0 sort (aggregate)
2 1 index (range scan) of 'i_mytabs_test' (non-unique) (cost=3 card=126 bytes=1386)
statistics
----------------------------------------------------------
806 recursive calls
5 db block gets
283 consistent gets
76 physical reads
0 redo size
293 bytes sent via sql*net to client
359 bytes received via sql*net from client
2 sql*net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，這次只讀取了 300 個數據塊。
7000 塊對 300 塊，這就是在這個例子中，單列索引與復合索引的代價之比。這個例子提示我們， 在許多情況下，單列索引不如復合索引有效率。
可以說，在索引的設置問題上，其實有許多工作可以做。正確地設置索引，需要對應用進行總體的分析。
第三講、索引再好，不用也是白搭
拋開前面所說的，假設你設置了一個非常好的索引，任何傻瓜都知道應該使用它，但是 oracle 卻偏偏不用，那么，需要做的第一件事情，是審視你的 sql 語句。
oracle 要使用一個索引，有一些最基本的條件：
1 ， where 子句中的這個字段，必須是復合索引的第一個字段；
2 ， where 子句中的這個字段，不應該參與任何形式的計算
具體來講，假設一個索引是按 f1, f2, f3 的次序建立的，現在有一個 sql 語句 , where 子句是 f2 = : var2, 則因為 f2 不是索引的第 1 個字段，無法使用該索引。
第 2 個問題，則在我們之中非常嚴重。以下是從 實際系統上面抓到的幾個例子：
select jobid from mytabs where isreq='0' and to_date (updatedate) >= to_date ( '2001-7-18', 'yyyy-mm-dd') ；
………
以上的例子能很容易地進行改進。請注意這樣的語句每天都在我們的系統中運行，消耗我們有限的 cpu 和 內存資源。
除了 1 ， 2 這兩個我們必須牢記于心的原則外，還應盡量熟悉各種操作符對 oracle 是否使用索引的影響。這里我只講哪些操作或者操作符會顯式（ explicitly ）地阻止 oracle 使用索引。以下是一些基本規則：
1 ， 如果 f1 和 f2 是同一個表的兩個字段，則 f1>f2, f1>=f2, f1
2 ， f1 is null, f1 is not null, f1 not in, f1 !=, f1 like ‘ %pattern% ' ;
3 ， not exist
4 ， 某些情況下， f1 in 也會不用索引；
對于這些操作，別無辦法，只有盡量避免。比如，如果發現你的 sql 中的 in 操作沒有使用索引，也許可以將 in 操作改成 比較操作 + union all 。筆者在實踐中發現很多時候這很有效。
但是， oracle 是否真正使用索引，使用索引是否真正有效，還是必須進行實地的測驗。合理的做法是，對所寫的復雜的 sql, 在將它寫入應用程序之前，先在產品數據庫上做一次 explain . explain 會獲得 oracle 對該 sql 的解析（ plan ） , 可以明確地看到 oracle 是如何優化該 sql 的。
如果經常做 explain, 就會發現，喜愛寫復雜的 sql 并不是個好習慣，因為過分復雜的 sql 其解析計劃往往不盡如人意。事實上，將復雜的 sql 拆開，有時候會極大地提高效率，因為能獲得很好的優化。當然這已經是題外話了。
，歡迎訪問網頁設計愛好者web開發。
