我們進行資料庫檢索最佳化的方法，通常是對特定條件列加索引，減少由於全表掃描帶來的邏輯物理IO消耗。索引的種類很多，我們經常使用的B*樹索引，由於結構簡單、適應性強，可以應對大多數資料訪問最佳化需求。除B*樹索引外，其他一些索引型別，也在一些場合中扮演著獨特的地位。本篇來介紹其中的函式索引。

1、從B*樹索引的失效談起

和通常一樣，我們準備實驗環境。

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> create table t as select * from dba_objects;

Table created

//構建兩個索引用作實驗物件

SQL> create index idx_t_owner on t(owner);

Index created

SQL> create index idx_t_ddlt on t(last_ddl_time);

Index created

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

PL/SQL procedure successfully completed

環境中，我們在資料表T上建立了一般意義的索引。當我們進行檢索的時候，CBO會適時選擇合適的索引執行計劃。

SQL> explain plan for select * from t where owner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

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

| 0 | SELECT STATEMENT | | 2419 | 229K| 71 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 2419 | 229K| 71 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 2419 | | 6 (0)|

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT')

14 rows selected

但是，很多時候，我們可能會遇到在where條件裡對索引列進行函式處理的情況。比如，選擇owner列取值第二個字母是“c”的資料列，或者選取在特定天進行ddl操作的物件資訊。這樣的情況下，直接的想法就是在where條件列中加入列函式處理，但是這樣做，會帶來B*樹索引的失效問題。

SQL> explain plan for select * from t where substr(owner,2,1)='C';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 726 | 70422 | 283 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 726 | 70422 | 283 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter(SUBSTR("OWNER",2,1)='C')

13 rows selected

在對條件列owner進行substr操作之後，生成的執行計劃就不會帶有索引路徑，出現全表掃描。如果列上的B*樹普通索引就是為該查詢對應的用例服務的，那麼這個索引的存在就失去了意義。

那麼，這種時候應該如何處理呢？答案是：在SQL語句本身不存在重構最佳化的空間時（此種情況通常出現在系統的運維階段），可以考慮使用函式索引來解決問題。

2、函式索引

函式索引與通常B*樹索引的結構，存在很大相似性。區別就在於形成樹結構的葉子節點上，儲存的不是索引列的取值，而是經過特定的函式處理過的索引列值。

這樣的結構，進行搜尋的時候，就可以直接使用到函式索引的葉子節點，獲取到對應的rowid集合。要求是出現於構建函式索引完全相同的函式條件。

首先，我們來構建函式索引。

SQL> create index idx_t_ownerf on t(substr(owner,2,1));

Index created

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

PL/SQL procedure successfully completed

構建函式索引的語法和一般索引的語法沒有過多的區別，最大的差異就是在宣告索引列的位置上，寫清楚應用的函式語句。此時，資料字典檢視系列中，已經反映出函式索引的不同。

SQL> select index_type from dba_indexes where index_name='IDX_T_OWNERF';

INDEX_TYPE

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

FUNCTION-BASED NORMAL

此時，我們再進行查詢，執行計劃會發生變化。

SQL> explain plan for select * from t where substr(owner,2,1)='C';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2485331276

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

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

| 0 | SELECT STATEMENT | | 4839 | 467K| 135 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| T | 4839 | 467K| 135 (0)

|* 2 | INDEX RANGE SCAN | IDX_T_OWNERF | 4839 | | 9 (0)

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

Predicate Information (identified by operation id):

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

2 - access(SUBSTR("OWNER",2,1)='C')

14 rows selected

加入函式索引之後，我們可以發現同樣的SQL語句，執行計劃發生變化。函式索引開始起效。

那麼，函式索引的本質是什麼呢？我們檢查資料字典檢視，就可以發現函式索引的本質。

SQL> col table_name for a20;

SQL> col table_owner for a20;

SQL> col column_name for a30;

SQL> select table_owner, table_name, column_name from dba_ind_columns where index_name='IDX_T_OWNERF';

TABLE_OWNER TABLE_NAME COLUMN_NAME

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

SYS T SYS_NC00016$

SQL> select column_expression from dba_ind_expressions where index_name = 'IDX_T_OWNERF';

COLUMN_EXPRESSION

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

SUBSTR("OWNER",2,1)

SQL> select column_name,data_type,data_default from dba_tab_cols where wner='SYS' and table_name='T' and column_name='SYS_NC00016$';

COLUMN_NAME DATA_TYPE DATA_DEFAULT

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

SYS_NC00016$ VARCHAR2 SUBSTR("OWNER",2,1)

檢查了三個檢視的情況，我們可以清楚的看出Oracle函式索引的本質。Oracle建立函式索引之後，就會先建立出一個不可見的內部列（SYS_NC00016$）。之後，對這個列建立普通的B*樹索引。為了保證該列在不受影響的情況下進行資料生成，使用預設值技術，在資料插入或者變化的時候，進行同步。

3、函式索引使用

函式索引是一種很特殊的索引型別，可以應對開發階段出現的對資料列加函式處理SQL最佳化。但是，筆者以為，函式索引的使用還是應當注意一些細節的，在大部分場合下，函式索引可以作為一種應急或者是不得為之的策略。

首先，函式索引的綜合消耗要大於普通的B*樹索引。相對於傳統索引，函式索引要保證創造的函式列資料一致性和多次進行函式計算。這樣的消耗要遠大於普通B*樹索引；

其次，函式索引的適應範圍較小。函式索引其效果的最大要素就是函式的使用和定義是100%相同。如第二部分的例子中，取字串的第二位字串。如果有一個變更的需求，要求取第三位，這樣原來的那個函式索引就不能發揮效應了。而相對來說，普通的B*樹索引參與各種SQL的能力要很多。應該說，函式索引的針對性很強，如果這個需求不屬於關鍵需求，這樣價效比略差。

最後，函式索引通常是一種事後補救措施。筆者認為，一個良好設計的應用，一個劃分合理的資料庫邏輯結構,應該是可以避免函式運算元據列的SQL大量出現的。只有在系統上線之後，開發團隊開發的問題暴露出來，但是也沒有精力進行修改時，運維人員才開始使用函式索引，保證系統功能能夠實現。

對開發人員和開發DBA而言，函式索引通常是不得已為之的方案，要保證在SQL和資料表結構權衡無效的情況下，再考慮使用函式索引。

首先，考慮SQL結構的最佳化。這個方法可以消滅掉很多看似不得不使用函式索引的場合。如字串型別比較、日期匹配等等，都可以透過程式碼檢查和SQL改寫來避免進入函式索引的狀況。下面一個例子：

//獲取前一天進行ddl操作的物件列表

SQL> select count(*) from t where trunc(last_ddl_time)=trunc(sysdate)-1;

COUNT(*)

----------

9

日期型操作最大的問題就是時分秒結構的處理。Date型別本身是帶有時分秒資訊的，而進行查詢的時候，常常是使用特定的年月日。這樣，就會帶來一些檢索條件的問題。很多開發人員，就是直接使用trunc函式，將資料列上的時分秒資訊進行裁剪。這樣的確簡單，而且滿足需求。但是也留下了列索引失效的隱患。

正確的解決方式，應該將SQL進行改寫，變等於條件為範圍條件。如下：

SQL> explain plan for select count(*) from t where last_ddl_time between to_date('2011-5-20 00:00:00','yyyy-mm-dd hh24:mi:ss')

2 and to_date('2011-5-20 23:59:59','yyyy-mm-dd hh24:mi:ss');

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3824876144

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

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

| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 8 | | |

|* 2 | INDEX RANGE SCAN| IDX_T_DDLT | 91 | 728 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("LAST_DDL_TIME">=TO_DATE(' 2011-05-20 00:00:00',

'syyyy-mm-dd hh24:mi:ss') AND "LAST_DDL_TIME"<=TO_DATE(' 2011-05-2

23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

16 rows selected

經過改寫，沒有使用函式索引，原有的B*樹索引起效。很多時候，經過SQL的重新思考，是可以避免函式索引使用場合出現的。特別是在專案的開發階段，這個尤為重要。

其次，就是對設計表的改進。我們常說一正規化：列不可分。如果出現很多的對資料列的函式處理，我們就需要重新審視我們的設計表方案。是不是存在設計不合理、沒有考慮到實際業務技術需求的方面。當SQL沒有最佳化空間時，設計表的重構，冗餘欄位的加入可能是比較好的思路方法。

4、結論

本篇從一般的函式索引，談到了SQL的改寫和設計表最佳化。核心要義就是一點，慎用函式索引。而且，在絕大多數的情況下，我們是不需要使用函式索引的。只要能夠理智冷靜的分析實際需求和SQL結構，通常都可以獲取到一個折中的方案。

說說函式索引

相關文章