昨天我們回顧了索引的一些基礎知識，今天我們需要了解一些索引最佳化相關的知識。首先我們從索引的分類來研究一下索引的主要種類以及各類索引在使用時的一些要點。

首先我們來看看最常見的B樹索引，B樹索引適用於幾乎所有的場合，也是系統中使用最為廣泛的索引形式。實際上我們所說的普通索引，反轉鍵索引、降序索引、函式索引等都是B樹結構的，其物理儲存結構是完全相同的。與之相對應的點陣圖索引是完全不同的儲存結構，點陣圖索引不是樹狀結構，沒有枝節點，只有葉節點。對於B樹索引的操作可以進行索引唯一性掃描、索引範圍掃描、快速索引全掃描和索引全掃描，而對於點陣圖索引的訪問方式只有一種，就是索引全掃描。在使用點陣圖索引的時候，只有對索引完全掃描一遍，才能找到所有的所需要的行。

從昨天的知識點我們知道，索引是一個樹狀的結構，組織形式是一顆擴充套件了的B樹，和普通B樹不同的是，這棵B樹的所有葉節點上有一條雙向鏈，稱為葉節點鏈。這條雙向鏈是根據索引鍵值的大小進行排序的。這條雙向鏈的存在十分關鍵，這是實現索引範圍掃描的最關鍵的技術。當進行索引範圍掃描時，首先透過B樹的定位演算法，從根開始，找到範圍掃描起始鍵值的位置，然後從這個位置開始，透過葉節點鏈按照升序或者降序的方式掃描相關的葉節點，直到找到超出掃描範圍的鍵值為止。

最為普通的索引是按照鍵值升序排列的，索引樹的右面的枝葉的鍵值總比左邊的大。而如果我們設計了降序索引，那麼情況正好倒過來，索引左面的枝葉的鍵值總比右面的大。

函式索引是一種特殊的B樹索引，引入函式索引的目的是解決那些在使用過程中，必須在欄位上做函式運算的情況。一般情況下，我們在程式設計時都會建議開發人員不要在WHERE條件中的表欄位上使用函式，因為這樣我們無法為其設計索引。不過事實上，我們無法杜絕這樣的函式的存在。比如說，我們必須從某個人欄位取第二和第三位進行比較：WHERE substr(id,2,3)='ID'，如果這樣的查詢條件放棄函式的話，程式設計師的處理將十分複雜。函式索引為這種情況提供了很好的幫助，如果這個查詢條件使用索引效果較好的話，我們可以在ID欄位上建立一個以函式substr(id,2,3）為鍵的B樹索引。事實上，在絕大多數應用系統中,函式索引都是不可避免的，不過不幸的是，在我做過的最佳化專案中，我基本上沒有看到過使用者在使用這種索引。

接下來我們來討論一下反轉鍵索引（reverse key index），這是一種十分著名的索引，反轉鍵索引是在儲存鍵值的時候，先將鍵值進行翻轉。比如'1234'儲存在索引中的鍵值是'4321'。設計反轉鍵索引的目的是解決索引的熱塊衝突問題。索引塊出現熱塊衝突是在效能最佳化時經常會碰到的問題，比如一個主鍵是透過sequence生成的，那麼主鍵索引就可能成為熱塊。這種情況下，如果我們確定針對主鍵的查詢不存在或者很少有索引範圍掃描，那麼我們可以考慮使用反轉鍵索引來解決主鍵的熱塊衝突問題。反轉鍵索引解決索引熱塊衝突的原理很簡單，就是透過鍵值的反轉，打亂索引資料塊中的資料組織，從而將熱點資料分散到不同的索引資料塊中。

不過除了解決熱塊衝突的問題外，DBA界還流傳著反轉鍵索引可以解決 like '%abc'無法使用索引的問題。粗想起來，還確實是這麼回事，like '%abc'這樣的條件，由於萬用字元在第一個字元，因此這樣的查詢條件，無法進行索引範圍掃描，因此一般情況下使用全表掃描比較合適。不過在某些情況下，如果表十分巨大，這種全表掃描成本太高，如果能使用索引就好了。而反轉鍵索引正好在儲存鍵值的時候是反轉過來的，1abc,2abc在索引鍵裡的儲存為abc1,abc2，這種情況下，做就可以透過範圍掃描將符合條件的記錄找出來嗎？這個解釋似乎是很合理，我也曾經被這個理論所矇蔽過一段時間，直到有一天我自己做了一個實驗，才發現問題遠非那麼簡單。下面我們來回顧一下這個實驗。首先我們建立測試表：

DROP TABLE TINDEX;

CREATE TABLE TINDEX as SELECT DISTINCT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,STATUS,TEMPORARY,TIMESTAMP,'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234456' abc

from dba_objects;

INSERT INTO TINDEX SELECT DISTINCT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,STATUS,TEMPORARY,TIMESTAMP,'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234456' abc

from dba_objects;

create index idx_tindex_name on tindex(object_name ) reverse;

然後做一次表分析：

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott', tabname=>'tindex', estimate_percent=>30, -

cascade=>true, degree=>2);

似乎一切都準備好了，下面我們來測試一下反轉鍵索引是否真的能夠解決like語句的問題：

SQL> set autotrace traceonly

select * froM tindex where object_name like '%TINDEX';

SQL>

Execution Plan

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

Plan hash value: 2264840918

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

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

| 0 | SELECT STATEMENT | | 5297 | 553K| 103 (3)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TINDEX | 5297 | 553K| 103 (3)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_NAME" LIKE '%TINDEX')

Statistics

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

1 recursive calls

0 db block gets

1717 consistent gets

0 physical reads

0 redo size

939 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

似乎索引並沒有被自動使用，我們使用hint強制索引看看：

SQL> select /*+ INDEX(TINDEX idx_tindex_name ) */ * froM tindex where object_name like '%TINDEX';

Execution Plan

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

Plan hash value: 2021627753

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

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

| 0 | SELECT STATEMENT | | 5297 | 553K| 5548 (1)| 00:00:54 |

| 1 | TABLE ACCESS BY INDEX ROWID| TINDEX | 5297 | 553K| 5548 (1)| 00:00:54 |

|* 2 | INDEX FULL SCAN | IDX_TINDEX_NAME | 5287 | | 520 (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_NAME" LIKE '%TINDEX')

Statistics

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

1 recursive calls

0 db block gets

520 consistent gets

0 physical reads

0 redo size

939 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

雖然使用了索引，但是掃描方式是全索引掃描，而不是我們期待的索引範圍掃描。看樣子反轉鍵索引並不能解決這個問題，我以前是被忽悠了。於是我繼續做實驗：

SQL> create index idx_tindex_func on tindex(reverse(object_name));

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott', tabname=>'tindex', estimate_percent=>30, -

> cascade=>true, degree=>2);

PL/SQL procedure successfully completed.

SQL> select * froM tindex where reverse(object_name) like 'XEDNIT%';

Execution Plan

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

Plan hash value: 1286384425

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

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

| 0 | SELECT STATEMENT | | 4 | 436 | 7 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TINDEX | 4 | 436 | 7 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TINDEX_FUNC | 4 | | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')

filter(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')

Statistics

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

1 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

939 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

這才是我們所需要的效果，透過reverse函式，然後將%TINDEX反轉為XEDNIT%，才真正的解決了這個問題，這個SQL的開銷是原SQL的幾百分之一。實際上這種解決方案只能在修改應用的前提下實現，不如使用反轉鍵索引這麼簡單，而且用途廣泛。不過我們也終於透過實驗糾正了一個錯誤的，流傳甚廣的誤解。我在網上透過google查閱了大量的關於此話題的英文資料，終於明白了這個誤解的來源，最初的時候，網上確實有一篇文章，介紹使用reverse函式解決這個問題，後來這篇文章在被轉載的時候

SELECT *

FROM customer

WHERE Cust_Name LIKE '%Vilas%'

修改為：

SELECT *

FROM customer

WHERE reverse(Cust_Name) LIKE '%saliV%';

被錯誤的寫成了：

SELECT *

FROM customer

WHERE Cust_Name LIKE '%saliV%';

以此版本為藍本，終於引發了透過反轉鍵索引最佳化like操作這個錯誤的觀點。由此可見，網路上的知識，不經過自己驗證就全盤吸收是多麼危險。

不過以Oracle反轉鍵索引的儲存結構，確實具備對Like條件做範圍掃描的基礎，只不過這樣的掃描，和以往Oracle提供的任何一種索引掃描技術都不相同，是一種全新的索引掃描方式。也許在Oracle 12或者13裡，真的會出現類似的功能呢，Oracle的每個新版本給人帶來的驚喜一直都是出乎大多數人的意外的。

理解索引（2）反轉鍵索引的誤區（摘自老白DBA日記）

相關文章