Oracle Index 的三個問題

來源:互聯網
上載者:User
oracle|問題
索引 ( 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 拆開,有時候會極大地提高效率,因為能獲得很好的最佳化。當然這已經是題外話了。


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。