MySQL的btree索引和hash索引&叢集索引

標籤：

 1，BTREE是多叉樹，多重路徑搜尋樹。有N棵子樹的節點它包含N-1個關鍵字，例如，有3個子樹的非葉子節點，那麼就有2個關鍵字，每個關鍵字不儲存資料，只用來儲存索引（在索引儲存資料時，將索引指向關鍵字的值也儲存進來。最終實現key = &get; value結構）。所有的資料最終都要落在葉子節點，所有的葉子節點包括關鍵字資訊以及指向這些關鍵字的指標，而且葉子節點是根據關鍵字大小、順序連結的。所有的葉子節點都必須有個鏈表指標把資料串起來。所以，所有非葉子節點可以看成索引部分，包括子樹中最大值或最小值關鍵字等資訊。在btree索引下，擷取資料時只需要從索引樹的最小節點，一直不斷的向右進行遍曆就可以快速的得到想要的資料（這種遍曆有指標把資料串起來），不需要回溯到根節點， 這樣就可以理解為什麼innodb的主鍵索引不能用離散的資料。

為2層btree結構：

 

 

 

2，雜湊索引建立在雜湊表的基礎上，它對每個值採用精確尋找。每一行都需要先計算雜湊碼，比較好的雜湊演算法算出比較低的重複的度，這樣效率相對高一些。如果算出來的值是一樣的，那麼它需要再進行判斷哪個值才是想要的值，所以說在表裡面採用雜湊索引，但是重複度又比較高，那麼雜湊索引效率就比較低。 

HASH索引PK BTREE索引：大量不同資料等值精確查詢，HASH索引效率通常比BTREE高；HASH索引不支援聯合索引的最左匹配規則（where a =? and  b=? ,index(a,b,c)這樣無法同時使用a,b,c，相當於是範圍查詢）；HASH索引不支援排序；HASH索引不支援模糊尋找；

為雜湊索引：

 

3，叢集索引，其實就是索引的組織方式，整個表格儲存體的邏輯順序根叢集索引的順序是一致的，也就是說叢集索引決定了整個表的物理的儲存的邏輯順序。mysql一個表只支援一個叢集索引。在innodb裡面叢集索引就是整個表，表就是叢集索引，因為innodb的叢集索引後面是整行資料，在叢集索引btree裡面每個葉子節點最終儲存每行資料，這就是為什麼在innodb裡面沒有任何條件count （*），它會優先選擇普通索引來完成掃描，而不是採用主鍵索引，因為如果掃叢集索引，掃描的資料量更大，產生的IO更大，如果掃描普通輔助索引，那麼它的資料結構通常來講比主鍵索引小。

innodb的普通索引葉子節點裡面儲存著主鍵索引的索引值。叢集索引決定了物理表的儲存順序，如果叢集索引頻繁修改，可能會導致修改儲存的順序，那麼這個行資料會產生位移，產生資料離散IO。如果新增的資料太過離散，也會導致叢集索引儲存的位置相應的離散，也會導致隨機IO.

叢集索引的選擇：

a,含有大量非重複值的列；

b,被連續（順序）訪問的列；

c,返回大量結果集的查詢；

案例：

如果一個表很大，有1/3資料要刪除,如果是隨機刪除，會產很多空洞，刪完後產生的空洞不寫入，沒什麼影響，但這種刪除比較慢，因為需要對btree進行隨機掃描。刪完後索引樹會進行自旋，如果它的page填滿因數比較低，例如把2頁合并成1頁，在合并中進行寫入會比較慢。 刪完後可以執行alter  table engine=innodb 來整理片段，但是會鎖表。建議使用pt-osc來完成資料表空間回收。

MySQL的btree索引和hash索引&叢集索引