輕松掌握Oracle索引（二）

4????? 數據的訪問路徑

條條大路通羅馬，你可以選擇最近的路線，也可以選擇一條較遠的路線，甚至你也可以繞地球一周。執行SQL其實是同樣的道理，完成同樣的查詢有很多方式，有的方式可能需要訪問10個塊，而有的卻可能需要訪問10萬個塊，執行時間可以相差數千倍。

如果你把數據庫當成個黑盒子，不去搞懂它的話，真的可能干出“繞地球一周”的傻事。也許你覺得不可能有這么大的差距，同一個語句要做的事應該是一樣多的啊。這就是SQL和普通編程語言的差異了：SQL不是面向過程的語言，而是面向結果的。你實際上只告訴了數據庫要什么數據，而沒有說明具體的執行步驟，優化器會選擇它認為最優化的方式來執行，這就是所謂“執行計劃”。可惜優化器不是總能做出正確的選擇的，如果沒有創建合適的索引，優化器更是無能為力了。

執行計劃有三個方面需要考慮：訪問路徑、連接方式和連接順序。訪問路徑就是通過什么路徑去訪問數據，包括全表掃描和各種索引掃描。對于單表查詢不用考慮連接方式和連接順序，暫時我們也不討論多表連接的情況，打好基礎最重要。

本章會介紹各種索引掃描方式，如何選擇索引。學習了這部分，對于單表查詢，相信就能做出正確的選擇了。

4.1????? 全表掃描（TABLE ACCESS FULL）

4.1.1??????? 什么是全表掃描

在數據庫中，對無索引的表進行查詢一般稱為全表掃描。全表掃描是數據庫服務器用來搜尋表的每一條記錄的過程，直到所有符合給定條件的記錄返回為止。全表掃描是最常見的訪問路徑，所以在介紹索引掃描前先介紹下全表掃描。

4.1.2??????? 全表掃描實例

下面是個全表掃描的例子。

SQL> set linesize 1000???????? --為了展示的好看些

SQL> set autotrace on????????? --使用autotrace的命令

SQL> select /*+full(a)*/ count(*) from t_userserviceinfo a;

COUNT(*)

----------

2170606

--這部分是執行計劃

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

| Id? | Operation????????? | Name????????????? | Rows? | Cost (%CPU)| Time???? |

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

|?? 0 | SELECT STATEMENT?? |?????????????????? |???? 1 |? 9370?? (1)| 00:01:53 |

|?? 1 |? SORT AGGREGATE??? |?????????????????? |???? 1 |??????????? |????????? |

|?? 2 |?? TABLE ACCESS FULL| T_USERSERVICEINFO |? 2170K|? 9370?? (1)| 00:01:53 |

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

--這部分是性能統計數據

Statistics

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

1? recursive calls

0? db block gets

53602? consistent gets??????????????????????? --說明需要讀53602個數據塊

53598? physical reads??????????????????????? --其中53598是物理讀

0? redo size

529? bytes sent via SQL*Net to client

520? bytes received via SQL*Net from client

2? SQL*Net roundtrips to/from client

0? sorts (memory)

0? sorts (disk)

1? rows processed

4.1.3??????? AUTOTRACE工具

AUTOTRACE是Oracle自帶的工具，它不僅可以展示執行計劃，還可以告訴我們SQL執行過程中訪問了多少數據塊。

我們可以看到這次查詢讀取了53602個數據塊，其中53598是物理讀，“1? rows processed”表示最后返回了一條數據。

如果把該語句再執行一次，你可以看到consistent gets幾乎沒變化，而physical reads很可能變成了0，這是因為數據已經在緩存中了，不產生物理讀了。

如果把該表的所有數據全部delete再執行相同的查詢，你會發現consistent gets幾乎沒變化，這是因為delete不會降低表的高水位，即使數據塊是空塊，全表掃描時也需要讀取該數據塊。

4.1.4??????? 全表掃描執行計劃

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

| Id? | Operation????????? | Name????????????? | Rows? | Cost (%CPU)| Time???? |

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

|?? 0 | SELECT STATEMENT?? |?????????????????? |???? 1 |? 9370?? (1)| 00:01:53 |

|?? 1 |? SORT AGGREGATE??? |?????????????????? |???? 1 |??????????? |????????? |

|?? 2 |?? TABLE ACCESS FULL| T_USERSERVICEINFO |? 2170K|? 9370?? (1)| 00:01:53 |

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

上面是最簡單的一個執行計劃，它只有三步。它的執行順序是從內到外的，即2->1->0。TABLE ACCESS FULL表示這一步是對表T_USERSERVICEINFO做全表掃描；SORT AGGREGATE表示對上一步結果執行count，注意這里的SORT不表示排序，而是集函數的意思；SELECT STATEMENT表示這是一個查詢語句。

4.1.5??????? 全表掃描提示

/*+full(a)*/是全表掃描的提示，表示告訴優化器對a表使用全表掃描的訪問路徑，其中a是表的別名。

提示和注釋很像，但是它有固定的格式并且要放在合適的位置。提示不一定能生效，當提示錯誤時優化器會忽略提示而不會報錯。Oracle不建議使用提示來固定執行計劃，因為如果指定的提示不合適，還不如讓優化器來選擇呢。當你確定要選擇全表掃描時可以使用提示固定執行計劃。

重要知識點

執行計劃有三個方面需要考慮：訪問路徑、連接方式和連接順序。訪問路徑就是通過什么路徑去訪問數據，包括全表掃描和各種索引掃描。

AUTOTRACE是Oracle自帶的工具，它不僅可以展示執行計劃，還可以告訴我們SQL執行過程中訪問了多少數據塊。

/*+full(a)*/是全表掃描的提示，表示告訴優化器對a表使用全表掃描的訪問路徑，其中a是表的別名。

4.2????? 索引唯一掃描（INDEX UNIQUE SCAN）

4.2.1??????? 什么是索引唯一掃描

當根據主鍵或唯一索引查詢數據時，可以使用索引唯一掃描。這種查詢的代價非常小，因為它最多從索引樹中查出一條數據。索引唯一掃描在訪問索引時最多訪問的數據塊數就是索引樹的高度，而索引數一般不會超過4層，所以它的查詢速度很快。

4.2.2??????? 只訪問索引而不訪問表

當索引中包含了要查詢的所有數據時，則只需要訪問索引而不用再訪問表，下面是一個索引唯一掃描的例子，其中，PK_T_USERSERVICEINFO是創建在列phonenumber和servicetype上的主鍵。

從統計結果可以看到，實際只需要讀取3個數據塊（3? consistent gets），還記得前面這個索引樹的高度嗎，正好就是3，說明這次查詢只需要從索引根節點訪問到葉子節點。

從執行計劃看，首先執行索引唯一掃描（INDEX UNIQUE SCAN），然后執行count操作（SORT AGGREGATE），并沒有訪問表的操作。

為什么這個操作不要訪問表？

因為要查詢count（*），而唯一索引的列是不允許為空的，所以在索引中count和在表中count是一樣的。

SQL> select /*+index(a PK_T_USERSERVICEINFO)*/count(*) from t_userserviceinfo a where phonenumber = '674977819' and servicetype = 1;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 2484190785

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

| Id? | Operation????????? | Name?????????????? ??| Rows? | Bytes | Cost (%CPU)| Time???? |

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

|?? 0 | SELECT STATEMENT?? |????????????????????? |???? 1 |??? 13 |???? 2?? (0)| 00:00:01 |

|?? 1 |? SORT AGGREGATE??? |????????????????????? |???? 1 |??? 13 |??????????? |????????? |

|*? 2 |?? INDEX UNIQUE SCAN| PK_T_USERSERVICEINFO |???? 1 |??? 13 |???? 2?? (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("PHONENUMBER"='674977819' AND "SERVICETYPE"=1)

Statistics

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

1? recursive calls

0? db block gets

3? consistent gets

0? physical reads

0? redo size

526? bytes sent via SQL*Net to client

520? bytes received via SQL*Net from client

2? SQL*Net roundtrips to/from client

0? sorts (memory)

0? sorts (disk)

1? rows processed

4.2.3??????? 需要訪問表

下面再來看一個需要訪問表的例子，將上面的SQL中的count(*)修改為另一個列paykind。我們可以執行計劃發生了變化：由于不需要count，所以沒有了SORT AGGREGATE，取而代之的是TABLE ACCESS BY INDEX ROWID，這一步的含義是根據索引中的rowid訪問表數據。

看下最后的統計信息，這次訪問了4個數據塊（4? consistent gets），剛好比前一次多了一個。因為索引中只查到了一條數據，所以只需要訪問一個數據塊。

為什么這次需要訪問表呢？

因為這次要查詢的結果列是paykind，這個列在使用的索引中不存在，只有到表里才能查到它的信息。

SQL> select /*+index(a PK_T_USERSERVICEINFO)*/paykind from t_userserviceinfo a where phonenumber = '674977819' and servicetype = 1;

PAYKIND

----------

1

Execution Plan

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

Plan hash value: 2896004350

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

| Id? | Operation?????????????????? | Name???????????????? | Rows? | Bytes | Cost (%CPU)| Time???? |

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

|?? 0 | SELECT STATEMENT??????????? |????????????????????? |???? 1 |??? 16 |???? 3?? (0)| 00:00:01 |

|?? 1 |? TABLE ACCESS BY INDEX ROWID| T_USERSERVICEINFO??? |???? 1 |??? 16 |???? 3?? (0)| 00:00:01 |

|*? 2 |?? INDEX UNIQUE SCAN???????? | PK_T_USERSERVICEINFO |???? 1 |?????? |???? 2?? (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("PHONENUMBER"='674977819' AND "SERVICETYPE"=1)

Statistics

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

1? recursive calls

0? db block gets

4? consistent gets

1? physical reads

0? redo size

525? bytes sent via SQL*Net to client

520? bytes received via SQL*Net from client

2? SQL*Net roundtrips to/from client

0? sorts (memory)

0? sorts (disk)

1? rows processed

4.2.4??????? 索引掃描提示

/*+index(a PK_T_USERSERVICEINFO)*/這個提示表示強制指定某個索引，如果可以的話，優化器會使用該索引，不過不一定是索引唯一掃描。PK_T_USERSERVICEINFO是要使用的索引名，a是表的別名。

4.2.5??????? 與全表掃描的對比

索引唯一掃描一般比全表掃描的效率高的多，因為索引唯一掃描只需要訪問幾個塊，而全表掃描可能需要訪問上萬個塊。

當表非常小時，兩者的查詢效率就差不多了，此時也就不用關注性能差異了。

重要知識點

當根據主鍵或唯一索引查詢數據時，可以使用索引唯一掃描。這種查詢的代價非常小，因為它最多從索引樹中查出一條數據。

索引唯一掃描在訪問索引時最多訪問的數據塊數就是索引樹的高度。

當索引中包含了要查詢的所有數據時，那么就只需要訪問索引而不用再訪問表。

當表數據量很大時，索引唯一掃描一般比全表掃描的效率高的多。

4.3????? 索引范圍掃描（INDEX RANGE SCAN）

4.3.1??????? 什么是索引范圍掃描

索引范圍掃描跟索引唯一掃描類似，從根節點一直掃描到葉子節點，每一層（不包括葉子節點）掃描一個索引塊。它們的區別是，索引唯一掃描在葉子塊里最多只能找到一條目標數據，而索引范圍掃描可能找到N條目標數據（N>=0）。由此可見，索引唯一掃描實際是索引范圍掃描的一個特例。

當目標數據很多時，就可能占據多個葉子塊，例如查詢條件為userid=1000，可能查出很多條數據，假設占用了三個索引塊，如下圖所示（圖中省略了rowid）。

我們可以確定，這三個索引塊是相鄰的葉子塊。這里說的相鄰是使用指針關聯的，而不一定是物理位置的連續。對于這個查詢需要且只需要訪問三個葉子塊：首先從索引樹定位到最左側第一個含有目標數據的葉子塊，然后使用指針找到另外兩個葉子塊。

為什么不再訪問第四個葉子塊呢？因為第三個葉子塊中已經包含了比1000大的數據，那么后面就肯定不會再有1000了，記住葉子塊中的鍵值是有序的。

同理可以推出，當目標數據存在于100個葉子塊，那么這100個葉子塊都需要訪問，但是注意，只有第一次是從根節點下來的，后面的訪問都是根據葉子塊的指針來訪問的。理解這一點很重要，很多初學者會以為每查一條數據都要從根節點開始掃描的。

4.3.2??????? 使用場景分析

什么情況下會使用索引范圍掃描？

查詢列上有唯一索引就一定是索引唯一掃描嗎？

查詢列上有非唯一索引就一定是索引范圍掃描嗎？

查詢條件使用了查詢列就一定能用索引范圍掃描嗎？

大部分的索引掃描都是索引范圍掃描，我們主要分析下哪些場景不能使用索引范圍掃描：

（1）查詢條件中沒有使用索引列

如果只告訴你漢字的拼音卻要你使用部首的索引，顯然是辦不到的。

（2）使用全模糊查詢

例如username like ’%abc%’，這個查詢就不能使用username的索引范圍掃描（注意不是說一定不能使用這個索引，只是不能使用這種掃描類型）。

就像查字典，如果要查CH開頭的字，可以去索引里檢索，但如果要查中間包含AB的字就不好使用索引了。

（3）沒有使用組合索引的前導列（一般是索引的第一個列）

假設userid和servicetype上有組合索引，查詢條件userid=1可以用該索引做索引范圍掃描，但servicetype=1就不可以。這個原因和第一點是類似的。其實在索引中對組合索引并沒有很多特殊處理，它只保存鍵值和rowid，對于組合索引來說，鍵值就是多個字段的值的拼接。

你可以把一個字符串列的索引想象成一個組合索引，每一位是一個列。你也可以把一個單列索引認為是只有一個列的組合索引，這樣說來，單列索引只是組合索引的一個特例。

（4）查詢空值

當鍵值為空時，索引是不包含該數據的，所以查詢username is null就不能使用username的索引。有的人會有一個錯誤的認識：索引的列必須是非空的。顯然，這種理解是錯誤的，索引列允許為空。

雖然username列可以為空，但是username=’abc’是可以使用該索引的，因為null不是要查的數據，索引不包含username為空的數據對查詢沒有影響。

（5）使用不等于或not in

查詢條件userid<>123不能使用索引范圍掃描，不過修改為userid>123 or userid <123卻可以，當然一般這樣的查詢效率也比較低。

（6）查詢列上有表達式或函數

例如username || ‘’ = ‘abc’，此時username的索引就無法使用索引范圍掃描了，這也是常用的屏蔽索引的方法。

歸納下，索引范圍掃描的使用場景如下表所示，其中username列有非唯一索引，phonenumber列上有唯一索引，userid和servicetype上有主鍵（組合索引）。

場景

查詢條件舉例

說明

可以使用索引范圍掃描場景

對非唯一索引的等值查詢

username =’abc’

對非唯一索引的范圍查詢

username >’abc’

對非唯一索引的模糊查詢

username like ’abc%’

必須是前匹配，如果是全模糊，則無法使用索引范圍掃描

對唯一索引的范圍查詢

phonenumber>’123’

對唯一索引的模糊查詢

phonenumber like ’123%’

必須是前匹配

對唯一索引使用前導列查詢

userid=123

userid是組合索引的第一個列

不可以使用索引范圍掃描場景

非前匹配的模糊查詢

username like ’%abc’

username like ’%abc%’

查詢列上有表達式

username || ‘’ = ’abc’

userid +0=123

to_number(phonenumber)= 123

查詢列上有隱式轉換

phonenumber=123

等價于to_number(phonenumber)= 123

查詢空值

phonenumber is null

使用組合索引但未使用前導列

servicetype=1

userid和servicetype上有索引

使用不等于

userid<>123

4.3.3??????? 索引范圍掃描性能分析概述

索引范圍掃描的性能好不好？這要怎么衡量呢？還記得前面說過，簡單的說，可以算下查詢需要訪問的數據塊（包括表和索引的塊），如果很多就不好，反之則好。

如果索引范圍掃描不好如何處理呢？你可以有以下選擇：

換個索引

對于userid=1 and status=1，一般userid的索引要好于status的索引，因為status的重復數據多，而userid唯一性好。

使用全表掃描

如果要你把字典里所有的字都看一遍（包括內容），顯然沒必要一個個通過索引來查找了，直接按順序看內容更快。

使用快速全索引掃描

如果要你把字典里所有的字都看一遍，不需要看內容，那是不是只要把索引翻一遍即可，畢竟索引只有幾十頁，而內容有幾千頁。這種掃描類型后面還會討論。

4.3.4??????? 選擇哪個索引好

對于一個特定的SQL，走哪個索引訪問的數據塊少，就應該選擇哪個索引。要選擇好索引，就需要了解數據的大概分布。

其實，我們也不需要精確的計算實際要訪問多少個塊，一般而言，多掃描幾個塊對性能的影響也很小。我們需要關注的是差異很大的索引，如果一個索引只需要訪問10個塊，另一個需要訪問1萬個塊，當然要選擇前者。

索引范圍掃描要訪問的塊包括索引塊（主要是葉子塊）和表塊（根據葉子塊的rowid訪問）。下面詳細分析下索引范圍掃描過程中需要訪問的幾種數據塊。

（1）根節點和枝節點

前面討論過，索引的層高都比較小，并且每層只需要掃描一個塊，所以這部分的開銷是很小的，一般都是個位數。而且，由于這部分索引塊經常被訪問到，一般都是在內存中，基本上都是邏輯讀，所以這部分的開銷幾乎可以忽略。

（2）葉子節點

這部分的開銷可大可小，對性能影響巨大。下面分幾種場景詳細討論下：

根據索引條件只查到一條數據

例如，查詢status=1，如果只查到一條數據，那么就只需要掃描一個葉子塊，此時這部分的開銷最小。

根據索引條件查出很多數據

例如，查詢status=1，如果符合條件的數據有100萬，那么就可能需要訪問上萬個數據塊，那么這部分的開銷就很大。

有rownum的條件

例如，查詢status=1 and rownum<=100，此時符合status=1條件的數據即使有100萬，依然可以走status的索引，因為只要查詢到100條status=1的數據后就中止了，不會繼續掃描后面的葉子塊了。這一點比較容易迷惑，需要重點理解。

訪問了很多索引塊，實際目標數據很少

索引列的查詢條件就是為了準確定位到目標數據。假如userid, servicetype上有一個組合索引，當查詢userid=1 and servicetype=1的數據時，根據這個索引可以準確定位到第一個目標葉子塊。但是當查詢userid>1 and servicetype=1時，servicetype=1條件就起不到定位數據作用，而僅僅是過濾數據的作用。

下面看一個例子：

假設有100萬的數據滿足userid>1，其中只有一條滿足servicetype=1，那么通過這個組合索引實際只查出來一條數據，那么是不是只需要訪問一個葉子塊呢？當然不是，根據查詢條件只能定位到第一個滿足userid>1的葉子塊，后面所有滿足userid>1的數據都可能出現servicetype=1的數據，也可能一條都沒有，所以實際上還是訪問了幾萬個葉子塊。

如果反過來，查詢條件是userid=1 and servicetype>1，數據中有100萬滿足userid=1，其中只有1條滿足servicetype>1。此時使用這個索引可以精確定位到目標數據的第一個葉子塊，往后掃描時，只要出現一條不滿足userid=1 and servicetype>1的數據就可以中止了，后面不會再有目標數據了。

為什么會有這樣大的差異呢？因為組合索引的鍵值是先按照第一個列排序的，當第一個列相同時才按照第二個列排序。這個例子可能不太好理解，但是很重要。

（3）表數據塊

如果要訪問的數據都在索引里，此時不需要訪問表。實際應用中，這種場景并不多，大部分的SQL是需要訪問表的。

例如userid=1 and username=’abc’，假設走userid的索引并且查出了30條滿足userid=1的數據，那么就需要根據rowid訪問表數據才能判斷username=’abc’的條件是否成立。此時userid=1是訪問謂詞，它用于查找數據位置，而username=’abc’就是過濾謂詞，它不能定位數據的位置，只用于從查到的數據中過濾掉不要的數據。

如果這30條數據分布在兩個數據塊，那么就只要再掃描兩個數據塊；如果分布在30個數據塊，那么就需要掃描30個數據塊。索引中相鄰的數據如果在數據塊中也是相鄰的，這樣做索引范圍掃描是比較理想的。可惜實際數據卻不一定是這樣的，因為索引中的數據是有序的，而表中的數據是無序。

再看下面這個例子：

假如有一個查詢是userid>=0 and phonenumber>= '0'，假如這兩個列都有索引并且查詢條件都是命中所有數據，那么哪個索引好呢？

此時查詢的開銷就主要體現在要訪問多少的表塊了，因為從索引中查出來的數據是一樣多的，訪問的索引塊也相當。

如果userid是一個自增長的列，而phonenumber表示用戶號碼，你會發現對于這個查詢，userid索引比phonenumber索引效率高不少。因為userid是自增長的，相鄰的數據往往錄入表的時間也相近，出現在同一個表塊的可能性比較大；而phonenumber相鄰的數據在表中就是隨機分布的。

其實索引有個 “聚集因子”的屬性就描述了索引這方面的情況，優化器會利用這個屬性估算索引掃描的開銷從而選擇不同的執行計劃。

由于表的數據塊很多，這種訪問出現物理讀的可能性比較大，所以這部分開銷往往是查詢的最大開銷。

4.3.5??????? 全表掃描還是索引范圍掃描

對于大部分的實時查詢，一般都不會使用全表掃描，但有時全表掃描可能比索引范圍掃描的效率高的多。

舉個例子，假設表A有200萬數據，占用了5萬個數據塊，列username有索引，占用了5000個塊。現在有一個查詢username= 'abc' and status=1，假設所有數據都滿足username= 'abc'，下面看看兩種查詢方式的開銷分別有多少。

（1）全表掃描

5萬個塊都需要被訪問，假設都是物理讀。假如多塊讀可以將IO的開銷降低到1/5，那么實際發生了1萬次的物理讀。

總開銷=1萬次物理IO

（2）索引范圍掃描

如果是第一次掃描的話，索引塊也是物理讀，并且只能是單塊讀，那么索引掃描需要5000次物理讀。根據rowid到表每次也只能訪問一個塊，如果每個葉子塊的數據都不在同一個數據塊里，那么最多可能發生200萬次的IO；如果每個葉子塊的數據都只在10個塊里，那最少要產生5萬次的IO。不管怎樣，對表的5萬次物理IO是不可避免的。

那么，計算下產生的總開銷：

假設1次物理IO=10次邏輯IO

最好情況=索引IO+表IO==5000物理IO+50000物理IO =5.5萬次物理IO

最壞情況=索引IO+表IO==5000物理IO+50000物理IO+1950000邏輯IO =20.5萬次物理IO

從上面的計算結果可以看到，當索引命中的數據過多時，索引范圍掃描比全表掃描的開銷可能高很多倍，一般查詢超過5%的數據就不建議使用索引范圍掃描。

相對于索引范圍掃描來說，全表掃描的成本是衡定不變的。無論你的查詢是想訪問一筆數據還是訪問整個表的所有數據，全表掃描都會把整張表讀出來。而索引范圍掃描就不同，因為有了索引這個有序的數據結構做支持，可以只訪問需要的索引塊和需要的數據塊，而不是訪問整張表，它可能很好，也可能極差。

重要知識點

索引唯一掃描實際是索引范圍掃描的一個特例。

索引范圍掃描的主要開銷是掃描索引葉子塊和表塊，尤其是后者。

掌握哪些場景可以使用索引范圍掃描，哪些不可以。

rownum條件對查詢的影響很大。索引的聚集因子也會影響查詢效率。

當索引命中的數據過多時，索引范圍掃描比全表掃描的開銷可能高很多倍，一般查詢超過5%的數據就不建議使用索引范圍掃描。

4.4????? 索引全掃描（INDEX FULL SCAN）

4.4.1??????? 什么是索引全掃描

索引全掃描一般會掃描所有葉子節點。它從第一個葉子節點開始掃描，通過葉子節點的鏈接依次訪問每一個葉子節點。

索引全掃描有以下幾個特點：

由于葉子節點是有序的，所以掃描結果也是有序的。

由于葉子塊之間是指針關聯的，所以對葉子塊只能使用單塊讀。

如果需要訪問表，由于每一條數據都需要使用rowid訪問，它的效率比全表掃描低的多。

前面說過，索引范圍掃描可能只訪問一個葉子塊也可能訪問很多葉子塊。那么，最壞情況下，索引范圍掃描也可能需要訪問所有葉子塊，此時它的效果和索引全掃描是一樣的，所以可以認為索引全掃描是索引范圍掃描的一種極端情況。

4.4.2??????? 索引全掃描實例

（1）索引列不允許為空

索引pk_t_userinfo所在列是非空列，不使用索引列查詢，但使用提示指定使用該索引，此時執行計劃顯示為全索引掃描。全索引掃描的提示和索引范圍掃描是一樣的。

由于需要count（*），而索引列是非空列，所以只在訪問索引，不需要訪問表。

（2）索引列允許為空

索引ix_userinfo_username所在列username允許為空，直接指定該索引，從執行計劃看，仍然是全表掃描。原因是該索引不包含所有數據，所以不能使用全索引掃描。

如果增加條件username is not null，此時可以使用索引全掃描了，由于這個查詢需要訪問所有列，所以需要訪問表（TABLE ACCESS BY INDEX ROWID）。

4.4.3??????? 索引全掃描的應用場景

索引全掃描是索引范圍掃描的最差情況，那么是否應該避免出現索引全掃描呢？

的確，一般情況下不應該使用索引全掃描，它甚至比全表掃描還差很多，一般是沒有合適的索引或者提示指定錯了索引導致使用了索引全掃描。

但是，尺有所長，寸有所短，它有一個重要特性：查詢結果是有序的。當需要對一個大數據量的表排序，然后返回TOP N時，此時可以使用索引全掃描來避免排序，因為排序需要把所有數據讀到內存中，非常消耗性能。

下面看一個例子：

對表t_userinfo按照phonenumber排序后再取前100條數據。雖然SQL中有排序，但執行計劃中并沒有SORT，這就是因為使用了全索引掃描的效果。

注意：這里的rownum<=100很重要，它表示掃描到100條目標數據就可以返回了，而不用掃描所有數據。如果需要掃描所有數據，那么全索引掃描即使不排序也很差。

對上面的查詢，如果增加一個條件：username = 'abc'，雖然執行計劃沒有變化，但是它的性能可能差很多，因為這個條件是個過濾謂詞，如果符合這個條件的數據很少，那么就可能掃描很多數據才能查到100條目標數據。所以執行計劃相同，性能不一定一樣，你必須了解數據的分布情況，才能做出正確的判斷。

重要知識點

索引全掃描是索引范圍掃描的最差情況，一般要避免使用。

當需要對一個大數據量的表排序，然后返回TOP N時，此時可以使用索引全掃描來避免排序，但一定不能掃描很多數據。

索引快速全掃描（INDEX FAST FULL SCAN）

4.5.1??????? 什么是索引快速全掃描

索引快速全掃描和索引全掃描極為類似，它也適用于所有類型的B+樹索引（包括唯一性索引和非唯一性索引）。和索引全掃描一樣，索引快速全掃描也需要掃描目標索引所有葉子塊的所有索引行。

索引快速全掃描與索引全掃描相比有如下三點區別。

（1）索引快速全掃描只適用于CBO。

（2）索引快速全掃描可以使用多塊讀，也可以并行執行。

（3）索引快速全掃描的執行結果不一定是有序的。

因為索引快速全掃描時Oracle是根據索引行在磁盤上的物理存儲順序來掃描，而不是根據索引樹的指針順序來掃描的，所以返回結果是無序的。

4.5.2??????? 索引快速全掃描實例

下面是使用索引快速全掃描的例子，它的提示是index_ffs，與索引范圍掃描不同。

4.5.3??????? 索引快速全掃描的應用場景

索引快速全掃描的應用條件：

（1）只需要訪問索引，不需要訪問表。

（2）要訪問的數據在索引中都存在，對于count（*）就不能從一個允許為空的索引中獲得結果。

索引快速全掃描的使用場景：在只需要訪問索引就能獲得結果的情況下，替代全表掃描使用。它的性能比全表掃描好，因為索引的塊比表少的多；它的性能一般也優于索引全掃描，因為它支持多塊讀，而索引全掃描不支持；它的性能不一定有索引范圍掃描好，更沒有索引唯一掃描好，因為它畢竟要訪問所有索引塊。

所以，索引快速全掃描只是在某些場景下替代全表掃描使用。

重要知識點

索引快速全掃描支持多塊讀，結果是無序的。

索引快速全掃描的使用場景：在只需要訪問索引就能獲得結果的情況下，替代全表掃描使用。

4.6????? 索引跳躍掃描（INDEX SKIP SCAN）

4.6.1??????? 什么是索引跳躍掃描

索引跳躍式掃描（INDEX SKIP SCAN）適用于所有類型的B+樹索引（包括唯一性索引和非唯一性索引），它使那些在where條件中沒有對目標索引的前導列指定查詢條件但同時又對該索引的非前導列指定了查詢條件的目標SQL依然可以用上該索引，這就像是在掃描該索引時跳過了它的前導列，直接從該索引的非前導列開始掃描一樣（實際的執行過程并非如此），這也是索引跳躍式掃描中"跳躍"（SKIP）一詞的含義。

4.6.2??????? 索引跳躍掃描實例

（1）首先構造一個測試表

數據量是100萬數據，該表有兩列，第一列只有0、1、2三個取值，在這兩個列上創建組合索引。

create table t_test nologging as select mod(rownum, 3) usertype, rownum userid from dual connect by level <= 1000000;

create index ix_test on t_test(usertype, userid) tablespace ringidx;

（2）使用userid列查詢，由于沒有使用索引的第一個列，所以不能使用索引范圍掃描，但是可以使用索引跳躍掃描，提示index_ss指定索引跳躍掃描。

（3）它實際上跟下面這個查詢的效率是相當的

（4）但是，它比下面這個查詢的效率高的多，雖然結果也是一樣的，想想為什么？

4.6.3??????? 索引跳躍掃描的應用場景

索引跳躍掃描一般是不應該出現的，往往是索引設計錯誤或者SQL錯誤導致的錯誤。不過，在某些場景下也可以使用，從前面的例子可以看到，索引跳躍掃描也可能優于索引范圍掃描。

索引跳躍的應用場景：索引前導列（一般就是第一列）的取值很少的情況。

重要知識點

索引跳躍掃描一般是不應該出現的，往往是索引設計錯誤或者SQL錯誤導致的錯誤。

索引跳躍掃描也可能優于索引范圍掃描。

索引跳躍的應用場景：索引前導列（一般就是第一列）的取值很少的情況。

4.7????? 數據訪問路徑總結

數據訪問路徑

主要特點

適用場景

全表掃描

1。掃描表的所有數據塊，包括空塊

2。支持多塊讀

1。需要訪問超過5%的數據總量

2。一般是后臺任務，前臺業務一般要避免對大表做全表掃描

索引唯一掃描

1。一次掃描的塊非常少

2。它是索引范圍掃描的特例

3。索引掃描最多查出一條數據

1。根據主鍵或唯一索引精確查詢

2。優先使用的方式

索引范圍掃描

1。最常用的索引掃描方式

2。性能最好跟索引唯一掃描一樣，最差跟索引全掃描一樣

3。當需要在索引中掃描大量數據并且需要訪問表時性能很差

4。索引返回結果是有序的

1。適合在索引中命中少量數據的場景，不超過5%的數據總量

索引全掃描

1。索引范圍掃描的特例

2。索引返回結果是有序的

1。一般不建議使用

2。在排序后查TOP N的場景下可以使用

索引快速全掃描

1。索引返回結果是無序的

2。性能優于全表掃描

3。支持多塊讀，優于全索引掃描

1。只需要訪問索引，不需要訪問表

2。作為全表掃描的替代方式使用

3。CBO可以使用，RBO不可以

索引跳躍掃描

1。組合索引查詢時，查詢條件沒有使用前導列

2。等價于遍歷前導列的所有取值分別做索引范圍掃描

1。一般不建議使用

2。前導列的取值很少時可以使用

3。CBO可以使用，RBO不可以

5????? 索引設計的一般原則

分區表不要創建全局索引

分區表一般創建本地索引（使用local關鍵字）。如果創建了全局索引，當刪除分區時會導致索引失效。

不要創建無用的索引

索引會降低DML語句的性能，所以一般不要創建無用的索引。

不要創建同樣功能的索引

例如下面兩個索引，如果在userid, playlistid上創建了索引，就沒有必要在userid上創建一個索引。

create index ix_ums_playcontentlist_userid on t_ums_userplaycontentlist (userid)

tablespace ringidx

/

create index ix_ums_playcontentlist_id on t_ums_userplaycontentlist (userid, playlistid)

tablespace ringidx

/

索引的key不宜過長

如果索引key過長，會導致索引樹高度很大，索引查詢效率會降低。對于組合索引，索引的列不宜過多。不要把長字符串列作為索引列，例如描述字段。

對于組合索引，要把高選擇度的列放在前面

如下例所示，useraccount選擇度遠高于accounttype，所以應該把useraccount作為索引的首列，這樣當查詢條件中有useraccount而沒有accounttype時仍能高效的使用索引。

create index ix_ums_usrordlib_account_type on t_ums_userorderlib (useraccount,accounttype)

tablespace ringidx

/

當需要對大數據量排序時，可以通過創建索引來避免排序

場景1：分頁查詢需要查詢歌曲（總數100萬），沒有查詢條件，查詢結果需要按照musicname排序，實際絕大部分的查詢是前幾頁。

解決方案：在musicname創建索引，通過索引全掃描來避免排序，只要不是查詢的數據非常靠后，效率就很高。

場景2：查詢某表中status為1的數據并且要求結果按照inserttime排序，處理完后status會更新為2。

解決方案：在status和inserttime上創建組合索引，查詢條件是status=1，由于索引的有序性，查詢出來的數據自然是按照inserttime排序的。

一般不在選擇率很低的列上創建索引

一般不在狀態，用戶類型這種取值很少的列上創建索引。但是，有些場景卻可以創建這種索引。例如，當需要查詢的那部分的取值在數據中的比例很低時或者使用rownum限制了每次只查詢出一小批數據。總的原則是，只要一次查詢的數據在數據中的比例非常小，那么就適合使用索引。這里之所以說“一次查詢”，是因為符合條件的數據可能很多，但是可能使用rownum限制了每次只查100條。

如果where語句中不得不對查詢列采用函數查詢，如upper函數，需要建立相應函數索引

如果查詢條件在列上使用了函數，那么直接在列上創建的索引是無法使用的，必須創建對應的函數索引。如下例所示。

create index ix_auditionauthlog_tonename on t_ums_auditionauthlog(upper(tonename)) local

tablespace ringidx

/

函數索引跟普通索引的區別是鍵值不是列的值，而是應用了函數后的值，如果這個函數不改變列的值的話，那么普通索引也可以看作是函數索引的一個特例。

索引和表一般要創建的不同的表空間中

可以提高IO的性能。

6????? 總結

索引是提高數據查詢最有效的方法，也是比較難掌握的技術。好的索引可能使效率提高上萬倍，而無效的索引可能浪費了數據庫空間，甚至大大降低查詢性能。

索引有很多種類型，最常用的是單列索引、復合索引、函數索引以及分區索引。每種索引都有不同的使用場合，只有深刻理解了各種索引的特性，才能在面對紛繁復雜的應用時游刃有余。

如果希望掌握索引的用法還必須能看懂執行計劃，了解Oracle優化器的原理。因為只要是稍微復雜一點的查詢語句，就會涉及到多表之間的連接，就很難靠調整索引解決所有問題。

本文不求面面俱到，僅僅是把平時遇到的問題和積累的經驗總結出來，希望能給初學者一點幫助。

Oracle SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。