淺談聚簇索引與非聚簇索引

Brewin發表於2020-09-25

聚簇索引：將資料儲存與索引放到了一塊，找到索引也就找到了資料。表資料按照索引的順序來儲存的，也就是說索引項的順序與表中記錄的物理順序一致。
非聚簇索引：將資料儲存與索引分開，葉結點包含索引欄位值及指向資料頁資料行的邏輯指標，其行數量與資料錶行資料量一致。

檢視資料庫下儲存資料的資料夾，除去.frm代表的表結構檔案，使用InnoDB聚簇索引的表還可以看到一個.ibd檔案，而使用MYISAM非聚簇索引的表可以看到一個.MYI和.MYD的檔案，分別是索引和資料。

在innodb中，在聚簇索引之上建立的索引稱之為輔助索引，非聚簇索引都是輔助索引，像複合索引、字首索引、唯一索引。輔助索引葉子節點儲存的不再是行的物理位置，而是主鍵值，輔助索引訪問資料總是需要二次查詢，透過輔助索引首先找到的是主鍵值，再透過主鍵值找到資料行的資料頁，再透過資料頁中的Page Directory找到資料行。

情景	使用聚簇索引	使用非聚簇索引
列經常被分組排序	√	√
返回某範圍內的資料	√	×
一個或極小不同值	×	×
小數目的不同值	√	×
大數目的不同值	×	√
頻繁更新的列	×	√
外來鍵列	√	√
主鍵列	√	√
頻繁修改索引列	×	√

聚簇索引需要具有唯一性，一般要根據這個表最常用的SQL查詢方式來進行選擇，某個欄位作為聚簇索引，或組合聚簇索引。

聚簇索引預設是主鍵，如果表中沒有定義主鍵，InnoDB 會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB 會隱式定義一個主鍵來作為聚簇索引。InnoDB 只聚集在同一個頁面中的記錄。包含相鄰健值的頁面可能相距甚遠。

如果你想重建索引並且已經設定了主鍵為聚簇索引，必須先刪除主鍵，然後新增我們想要的聚簇索引，最後恢復設定主鍵。

此時其他索引只能被定義為非聚簇索引。

優點

1.資料訪問更快，因為聚簇索引將索引和資料儲存在同一個B+樹中，因此從聚簇索引中獲取資料比非聚簇索引更快

2.聚簇索引對於主鍵的排序查詢和範圍查詢速度非常快

缺點

1.插入速度嚴重依賴於插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響效能。因此，對於InnoDB表，我們一般都會定義一個自增的ID列為主鍵
2.更新主鍵的代價很高，因為將會導致被更新的行移動。因此，對於InnoDB表，我們一般定義主鍵為不可更新。
3.二級索引訪問需要兩次索引查詢，第一次找到主鍵值，第二次根據主鍵值找到行資料。

mysql InnoDB 引擎底層資料結構是 B+ 樹，表中的資料都是按順序儲存在 B+ 樹上的（所以說索引本身是有序的）。

mysql 在底層以預設大小為 16k(可以自定義)的資料頁為單位來儲存資料的，如果一個資料頁存滿了，mysql 就會去申請一個新的資料頁來儲存資料。

當主鍵為自增 id ，每次插入新的記錄時，記錄就會順序新增到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁。

但是主鍵為非自增 id時，為了確保索引有序，就需要將每次插入的資料都放到合適的位置上。當往一個快滿或已滿的資料頁中插入資料時，新插入的資料會將資料頁寫滿，就需要申請新的資料頁，並且把上個資料頁中的部分資料挪到新的資料頁上。這就造成了頁分裂，這種大量移動資料的過程是會嚴重影響插入效率的。

另外，在滿足業務需求的情況下，儘量使用佔空間更小的主鍵 id，比如int，有以下好處：

1、int 相比varchar、char、text使用更少的儲存空間，而且資料型別簡單，可以節約CPU的開銷，更便於表結構的維護

2、預設都會在主鍵上建立主鍵索引，使用整型作為主鍵可以將更多的索引載入記憶體，提高查詢效能。

3、對於InnoDB儲存引擎而言，每個二級索引都會使用主鍵作為索引值的字尾，使用自增主鍵可以減少索引的長度（大小），方便更多的索引資料載入記憶體。
4、可以使索引資料更加緊湊，在資料插入、刪除、更新時可以做到索引資料儘可能少的移動、分裂頁，減少碎片的產生（可以透過optimize table 來重建表），減少維護開銷。
5、在資料插入時，可以保證邏輯相鄰的元素物理也相鄰，便於範圍查詢。