反向索引處理前%

        今天我又被問到一個全表查詢的問題。這次好在不是前後%，僅僅前%。比如需求是要手機尾號或者卡號尾號。我其實不理解為什麼有這樣的需求。不過比起前後%這樣的還算可以。我先說一下這個是在Oracle中的，MySQL據我所知沒有。PG等其他資料庫我需要研究看看。這個主要是說明我怎麼構思的。

SQL> create table xuexiaogang3 (id int ,a int ,b varchar2(10));

Table created

SQL> create index x3 on xuexiaogang3 (id);

Index created

SQL> exec xxg3;儲存過程寫入100萬資料

PL/SQL procedure successfully completed


SQL> exec dbms_stats.gather_table_stats(ownname=>'XXG',tabname=>'XUEXIAOGANG3');

PL/SQL procedure successfully completed

收集統計資訊。然後我們查詢id樣本資料。如圖1，大家可以看到資料大致長這個樣子。

                                                                     圖1

我們按照ID查詢，因為ID是有索引的，那麼看到執行計劃使用了索引X3。如圖2

                                                                             圖2

而如果查詢B列，由於B列沒有索引，必然是全表掃描。如圖3.

                                                                                          圖3

更加不用說圖4中的前%，那麼就更加是全表查詢了。

                                                                                               圖4

在這種情況下要處理我們只查尾號，那麼就用反向索引。如圖5. 

                                                                                                  圖5

當然我們的SQL也要改寫，改寫成如圖6。我們可以看到結果和圖4的結果一樣。區別是這樣不是全表查。使用到了xd3的反向索引。

 這個案例表明前%，我們也是可以解決的。只要不是前後同時%，那麼就要涉及到特殊處理了。

 當然這個功能不是新功能，也不是主流功能，是一個小眾功能，但是依然有很多人不知道。如果在MySQL中沒有這個功能怎麼辦？

 那麼我的建議是新建一列，把要處理的尾號單獨儲存。查詢的時候查這個尾號列。

 Oracle強大給了我們很多開發和運維便利的方法，但是很多人不知道。

這些功能值得大家去用，儘管到現在還有很多人不知道。所以作為Oracle-ACE應該推廣在Oracle和MySQL上的使用。