聊聊索引和SQL優化

和一個朋友聊起索引和執行路徑的問題，覺得很有意思，就把一點想法寫下來，權當一個記錄。

討論是這樣：有一個資料表，其中有欄位為月份（使用數字型別）。根據業務的需要，很多搜尋都是依據這個月份欄位來進行的。於是從提高整體搜尋效能角度，選擇該列作為一個索引列。但是某個月份的資料是在一個統一的時間點被插入到系統中。所以，該表中所包含對應月份資料，是逐漸的增加的。開始的一段時間裡，可能只有一個月份的資料。當進行使用月份作為搜尋條件的SQL語句時，朋友發現執行路徑並沒有使用索引。

問題原理和解釋

這個問題其實是比較好理解的。設定了索引，而執行路徑沒有選擇索引路徑。最直接的可能性就是Oracle優化器認為當前的資料分佈情況下，使用全表掃描的成本更低。

這裡面我們再說一下Oracle優化器。SQL語句本質上是一種描述性語句，本身不會決定獲取資料的操作和方法。Oracle對於SQL語句的解析parse中，需要對該SQL生成執行計劃，也就是確定這個SQL進行操作的方式。從SQL語句到執行計劃的過程，其實就是Oracle優化器的主要工作。

Oracle優化器主要是兩個型別，基於規則（Rule-Based Optimizer RBO）和基於成本(Cost-Based Optimizer CBO)。RBO是早期的一種優化器，是依據SQL語句本身書寫的一些規則來確定執行計劃。這種方法的優點是規則簡單，我們通常指通過表結構、索引結構和執行SQL語句，就可以確定出執行計劃。生活是複雜的，簡單通常也就伴隨著武斷。一些時候，RBO生成的執行計劃往往不是最好。例如：當我們在where條件中書寫一個欄位選擇，並且欄位為索引列。RBO會直接選擇索引路徑。但是這時候，走索引可能不是很好的選擇。因為索引本身讀取也需要額外的成本付出。

於是，CBO應運而生。CBO依據的是資料表和資料項的統計量，包括資料分佈情況，取值分佈等。根據這些資料當前的實際情況，CBO去生成執行計劃。應該說，CBO是更科學、更貼近實際需要的優化器方法。從Oracle9i之後，CBO成為優化器預設的配置（感謝 lixin_2002 的指正），成為優化器發展的一個方向。

本質上說，RBO是一系列生成規則的集合，而CBO則是一系列引數控制公式的集合。對每個SQL語句，Oracle優化器都會生成多條執行計劃，根據收集的統計資料和成本引數（作為系統引數的一部分）為每個執行計劃試算出一個成本cost數值，依據最少cost的原則確定選擇的執行計劃。

回到朋友提到的案例。在最開始的時候，資料表中只有一個月份的資訊，在月份列上建立了索引物件。當執行帶月份的條件查詢時，Oracle會生成兩份執行計劃：全表掃描計劃FTS和索引計劃。當全表資料都是一個月份的情況下，Oracle如果選擇索引執行計劃，意味著會搜尋全部索引樹，並且根據索引樹中所有的rowid獲取資料表。成本要遠遠大於直接進行FTS。

下面我們進行一個簡單實驗。

SQL> conn scott/tiger@orcl

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

//構建資料表

SQL> create table t as select rownum as ronum, object_id,object_name, 201101 as month from all_objects;

Table created

SQL> select month,count(*) from t group by month;

MONTH COUNT(*)

---------- ----------

201101 40718

構建索引，並且收集統計量。

SQL> create index idx_t_month on t(month);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

首先，月份month列值有一種取值201101，上面建立索引。我們先檢視搜尋路徑。

SQL> select * from t where month=201101;

已選擇40718行。

已用時間: 00: 00: 00.98

執行計劃

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40718 | 1590K| 63 (4)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 40718 | 1590K| 63 (4)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("MONTH"=201101)

統計資訊

----------------------------------------------------------

201 recursive calls

0 db block gets

2995 consistent gets

0 physical reads

0 redo size

1608149 bytes sent via SQL*Net to client

30239 bytes received via SQL*Net from client

2716 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

40718 rows processed

執行計劃上，可以方便看到執行路徑為全表掃描，並沒有選擇索引路徑。如果我們強制要求Oracle生成走索引的路徑，可以使用hint。

SQL> select /*+ index(t idx_t_month) */ * from t where month=201101;

已選擇40718行。

已用時間: 00: 00: 01.09

執行計劃

----------------------------------------------------------

Plan hash value: 3445114591

--------------------------------------------------------------------------------

-----------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40718 | 1590K| 349 (1)| 00:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 40718 | 1590K| 349 (1)| 00:00:05 |

|* 2 | INDEX RANGE SCAN | IDX_T_MONTH | 40718 | | 93 (3)| 00:00:02 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("MONTH"=201101)

統計資訊

----------------------------------------------------------

64 recursive calls

0 db block gets

5766 consistent gets

92 physical reads

0 redo size

2191670 bytes sent via SQL*Net to client

30239 bytes received via SQL*Net from client

2716 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40718 rows processed

從執行計劃和實際執行計時上看，強制走索引的執行計劃明顯佔劣勢。可見，Oracle優化器在做這個選擇的時候，是選擇成本較低的全表掃面執行計劃。

所以，我們不難得出結論。我們確定某某列上需要加索引，是由於業務操作需求上，存在對該列訪問需求。但是，這不意味著該索引一定會成為所有情況SQL的執行計劃。索引路徑只是作為Oracle可選的一種執行路徑，實際執行情況還要看各種可選路徑的成本估算值。也就是說，沒有走索引，也不一定是壞事，重點在於是否滿足效能需求，要區別對待。

索引方案的使用

那麼，在什麼情況下，索引路徑才會執行呢？索引路徑成本的組成=讀取索引樹中符合條件記錄的rowid成本+據rowid成本獲取資料表塊記錄成本。只有選擇性比較好的時候，這種成本小於全表掃描的時候，Oracle會選擇索引路徑。

繼續實驗，我們修改一下資料表資料結構。

SQL> select month,count(*) from t group by month;

MONTH COUNT(*)

---------- ----------

201103 1999

200112 499

201102 999

201101 40718

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

我們重新整理了一下資料分佈情況，增加了資料的選擇性（雖然資料取值稍偏）。我們進行搜尋。

SQL> select * from t where month=201102;

已選擇999行。

已用時間: 00: 00: 00.11

執行計劃

----------------------------------------------------------

Plan hash value: 3445114591

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1022 | 39858 | 10 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1022 | 39858 | 10 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T_MONTH | 1022 | | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("MONTH"=201102)

統計資訊

----------------------------------------------------------

383 recursive calls

0 db block gets

209 consistent gets

8 physical reads

0 redo size

44074 bytes sent via SQL*Net to client

1111 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

999 rows processed

索引起效果了。Oracle針對這個查詢，發現執行索引路徑成本較低。反之，對一些SQL，Oracle同樣會執行全表掃描。

SQL> select * from t where month=201101;

已選擇40718行。

已用時間: 00: 00: 01.00

執行計劃

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40773 | 1552K| 73 (3)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 40773 | 1552K| 73 (3)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("MONTH"=201101)

統計資訊

----------------------------------------------------------

1 recursive calls

0 db block gets

3011 consistent gets

29 physical reads

0 redo size

1608149 bytes sent via SQL*Net to client

30239 bytes received via SQL*Net from client

2716 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40718 rows processed

這種靈活性，也就是體現出CBO的優勢，針對實際情況，執行靈活的執行計劃。

最後，筆者感覺有必要說明一個原則，就是優化的原則。在相同的情況（軟硬體、配置）下，全表獲取一百條資料的成本要低於全表獲取100萬條資料的成本。從100行資料中獲取10行資料成本要低於從100萬行資料中獲取10行資料。隨著資料表的容量擴大，系統總會出現瓶頸。我們進行優化的原則就是隨著系統容量增加，效能變化處於一個線性變化就可以了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-686787/，如需轉載，請註明出處，否則將追究法律責任。

聊聊索引和SQL優化

相關文章