復合索引

復合索引顧名思義，區別于單列索引，是由兩個或多個列一起構成的索引。其在B樹上的數據結構是什么樣？如下圖，是一個包含兩列的復合索引。

如果你觀察仔細，還會發現它的葉子節點是ASC遞增排序的。現根據第一個值排序，然后根據第二個值排序。查的時候也一樣，先查第一個值，在查第二個值。可以想象三個列組成的復合索引數據結構該是什么樣的。

在實際創建表時，一開始，只會創建一個主鍵id，oracle會為主鍵默認創建索引。隨著經驗的增加，開始去考慮性能，會開始研究索引，比如查詢報表、訂單等業務場景。這時候多多少少都會創建索引了，然后開始研究復合索引。復合索引首先帶來的問題是你如何在腦海中構建一個B樹上的復合索引結構？接下來就到了如何創建復合索引的問題。

索引的建立，是為了提高查詢效率，但索引設計不合理，又很影響寫性能，這就像CAP只能滿足其中兩個條件一樣。所以完美的東西不存在，萬物是在矛盾中前行。說白了，就是要用最少、結構最簡單的索引來達到目標。

---

基礎環境

基礎表：

說明：

1、a、b和c都經常要被查詢，并且有經常a、b、c組合查詢的可能性

2、a列區分度不大，b和c都有區分度(數值不會過多重復)

思路：

1、分別在a、b和c上分別建單列索引，即我們最終有三個索引，分別是(a)、(b)、(c)。這當然可以。但還有沒有更好的？

2、在(a、b)、(b、c)、(a、c)上建復合索引。這樣也可以，但維護起來開銷比較大，還有沒有更好的？

3、在(a、b、c)上建復合索引。這樣維護起來開銷也很大。并且a作為先導列，區分度不大，不是一個很好的選擇。

4、最好的，應該是在(b、c)上建立復合索引，在c上建單列索引，這樣(b、c)索引可以覆蓋到a and b和b and c查詢，c單列索引可以覆蓋到a和b查詢。

---

實例測試

1、創建測試表

CREATE TABLE sunyang_test(  id number,  a number,  b number,  c number )tablespace ebiz; alter table sunyang_test add constraint pk_sunyang_test primary key (id) using index tablespace ebiz; 

2、插入數據

begin  for i in 1 .. 100000 loop  insert into sunyang_test values(i,mod(i,2),mod(i,20000),mod(i,20000));  end loop;  commit; end; 

3、創建索引

create index idx_sunyang_test_bc on sunyang_test(b,c) tablespace ebiz; create index idx_sunyang_test_c on sunyang_test(c) tablespace ebiz; 

4、執行下面的SQL

select * from sunyang_test where b=5000; 

執行計劃：

select * from sunyang_test where c=5000; 

執行計劃：

select * from sunyang_test where a=1 and b=5000; 

執行計劃：

select * from sunyang_test where a=1 and c=5000; 

執行計劃：

select * from sunyang_test where b=5000 and c=5000; 

執行計劃：

select * from sunyang_test where a=1 and b=5000 and c=5000; 

執行計劃：

可以看到，在a列區分度不大，且需要a、b、c單列查詢或組合查詢時，這樣建索引將所有查詢情況都覆蓋到了，并且索引不復雜。

---

復合索引的設計，對于前導列的選擇(也就是復合索引中的第一個列)非常重要，至少不應該在這里選擇a作為一個復合索引的前導列，因為a的區分度太低了。有人說，不是有INDEX SKIP SCAN嗎？這個確實要看a的實際值，如果a的取值很少，比如這里的兩個，那么可以走INDEX SKIP SCAN，否則開銷是很大的，往往Oracle直接去FULL TABLE SCAN了，也不會去INDEX SKIP SCAN，因為其涉及到索引分裂。

后面會分享更多DBA方面內容，感興趣的朋友可以關注下！