MySQL innodb 叢集索引的概念與使用教程

在MySQL中，InnoDB引擎表是(聚集)索引組織表(clustered index organize table)，而MyISAM引擎表則是堆組織表(heap organize table)。

也有人把叢集索引稱為聚簇索引。

當然了，叢集索引的概念不是MySQL裡特有的，其他資料庫系統也同樣有。

簡言之，叢集索引是一種索引組織形式，索引的索引值邏輯順序決定了表資料行的實體儲存體順序，而非叢集索引則就是普通索引了，僅僅只是對資料列建立相應的索引，不影響整個表的實體儲存體順序。

我們先來看看兩種儲存形式的不同之處：

簡單說，IOT表裡資料實體儲存體順序和主鍵索引的順序一致，所以如果新增資料是離散的，會導致資料區塊趨於離散，而不是趨於順序。而HOT表資料寫入的順序是按寫入時間順序儲存的。

IOT表相比HOT表的優勢是：

範圍查詢效率更高;

資料頻繁更新(叢集索引本身不更新)時，更不容易產生片段;

特別適合有一小部分熱點資料頻繁讀寫的情境;

通過主鍵訪問資料時快速可達;

IOT表的不足則有：

資料變化如果是離散為主的話，那麼效率會比HOT表差;

HOT表的不足有：

索引回表讀開銷很大;

大部分資料讀取時隨機的，無法保證被順序讀取，開銷大;

每張InnoDB表只能建立一個叢集索引，叢集索引可以由一列或多列組成。

上面說過，InnoDB是叢集索引組織表，它的叢集索引選擇規則是這樣的：

首先選擇顯式定義的主鍵索引做為叢集索引;

如果沒有，則選擇第一個不允許NULL的唯一索引;

還是沒有的話，就採用InnoDB引擎內建的ROWID作為叢集索引;

我們來看看InnoDB主鍵索引的示意圖：

可以看到，在這個索引結構的葉子節點中，節點key值是主鍵的值，而節點的value則儲存其餘列資料，以及額外的ROWID、rollback pointer、trx id等資訊。

結合這個圖，以及上面所述，我們可以知道：在InnoDB表中，其叢集索引相當於整張表，而整張表也是叢集索引。主鍵必然是叢集索引，而叢集索引則未必是主鍵。

MyISAM是堆組織表，它沒有叢集索引的概念。



innodb叢集索引clustered index


InnoDB和SolidDB支援clustered index.


clustered index不是一種單獨的索引結構，而是一種資料存放區方式，clustered index實際上是在B+ tree中同時儲存了主鍵索引和資料。

clustered index儲存結構的表中資料行是儲存在索引的葉子頁中的，clustered意思是資料行和索引主鍵都儲存在一起。每個表只有一個clusterd index，就是那個primary key，換句話說Innodb按主鍵進行聚集。

如果沒有定義primary key，Innodb會試著使用 Unique nonullable index列來代替，如果這都沒有，會定義隱藏的主鍵然後再上面進行聚集。

 

clustered index的好處:

1）資料行是按主鍵順序儲存在一起的，讀取少量的磁碟頁面就可以把相鄰主鍵的資料讀出來。

2）索引和資料都儲存在一棵B+ tree中，從索引中讀取資料較快

3）使用覆蓋索引的查詢可以使用包含在葉子節點的主索引值。

clustered index缺點

1）更新clustered index列代價是昂貴的，因為要強制把每個更新的資料行移到新位置

2）按主鍵順序插入新行是一種好方法，否則更新主鍵或插入到隨機插入效能開銷比較大，資料行移動的時候還可能分頁，其實道理和第一條一樣。

3）輔助索引葉子節點包含的是主鍵列，而不是直接的資料行地址，所以輔助索引會相對較大

4）通過輔助索引的尋找先找到的是主鍵列，然後再在主鍵索引中進行第二次尋找，需要2次尋找索引，但是Innodb的自雜湊索引功能能減少這種損失。

 

Innodb和myisam的的索引及資料存放區比較

都是使用的 B+ tree索引結構，但是他們的資料存放區方式不一樣，參考《Mysql High performance》

MyIsam是按資料行插入的時間順序儲存儲存資料行，並且資料行和索引時分開儲存的（table.MYD, table.MYI），應該不會強行建立隱式主鍵索引；Myisam的主鍵索引和其它索引沒有結構上的區別。

而Innodb是按主鍵順序聚集儲存資料，資料行和索引都儲存在B+ tree裡面（叢集索引的主鍵最好的auto_increment的int型：插入的時候是按主鍵順序插入的；而且會使輔助索引的大小更小）。