MySQL的索引型別和實現原理

　　一、按表列屬性分類：

　　1.單列索引

　　以表的單個列欄位建立的索引

　　2.聯合索引

　　以表的多個列欄位組合建立的索引，在查詢條件使用索引的從左欄位順序才會生效，遵循最左匹配原則。

　　單列索引和聯合索引又包括：

　　普通索引

　　非主鍵，非唯一列的索引

　　主鍵索引

　　基於該表主鍵自動生成成的索引，如果未給表定義主鍵，會查詢該表中是否存在非空、整形、唯一索引作為其主鍵(可透過select _rowid from 表名檢視)，若都不滿足會隱式生成一個rowid作為主鍵(無法直接查到)

　　唯一索引

　　基於表的唯一列生成的索引，允許為空值

　　全文索引

　　將儲存於資料庫中的整本書或整篇文章中任意內容資訊查詢出來，如大量級的文字中如like %關鍵字%，普通索引的效率與全文索引相比是非常低的。

　　二、按資料結構分類：

　　1.B+tree索引

　　b+tree基於平衡二叉樹的一種多路平衡查詢樹，所有記錄都按照順序存放在葉子節點中，各個葉子節點直接透過連結串列相連。與b樹不同的是：

　　非葉子節點只儲存鍵值資訊。

　　所有葉子節點之間都有一個鏈指標。

　　資料記錄都存放在葉子節點中。

　　2.hash索引

　　基於hash表結構實現的索引，mysql中只有MEMORY/HEAP和NDB儲存引擎支援;

　　InnoDB引擎支援自適應hash索引，但是是資料庫自身建立使用的，而不能進行人為定義。當二級索引被頻繁的訪問時，便會自動建立自適應雜湊索引;

　　透過 命令SHOW ENGINE INNODB STATUS可檢視自適應hash索引的使用情況;

　　透過 命令SHOW VARIABLES LIKE '%ap%hash_index' 檢視是否開啟自適應hash索引。

　　對比：

　　由於hash索引是比較其hash值，hash索引只能進行等值查詢而不能進行範圍查詢

　　hash索引無法進行排序：原因同上

　　不支援最左匹配原則，複合索引時合併一起計算hash值

　　hash索引的檢索效率很高可以一次定位，但是當發生大量hash碰撞的時候，連結串列變長，hash索引效率上是不如b+tree的

　　由於存在hash碰撞的問題，當需要獲得總數時候，hash 索引在任何時候都不能避免表掃描

　　3.T-tree索引 無錫人流醫院哪家好

　　4.R-tree索引

　　三、按儲存結構分類：

　　1.聚簇索引(聚集索引)

　　InnoDB的聚簇索引實際上是在同一個BTree結構中同時儲存了索引和整行資料，透過該索引查詢可以直接獲取查詢資料行。

　　聚簇索引不是一種單獨的索引型別，而是一種資料的儲存方式，聚簇索引的順序，就是資料在硬碟上的物理順序。

　　在mysql通常聚簇索引是主鍵的同義詞，每張表只包含一個聚簇索引(其他資料庫不一定)。

　　2.輔助索引(非聚集索引，次級索引，二級索引)

　　非聚集索引在BTree的葉子節點中儲存了索引列和主鍵。如果查詢列不在該索引內，只能查到其主鍵值，還需要回表操作查詢聚簇索引進行查詢。

　　聚簇索引的優點：

　　可以把相關資料儲存在一起，如：實現電子郵箱時，可以根據使用者ID來聚集資料，這樣只需要從磁碟讀取少量的資料頁就能獲取某個使用者全部郵件，如果沒有使用聚集索引，則每封郵件都可能導致一次磁碟IO

　　資料訪問更快，聚集索引將索引和資料儲存在同一個btree中，因此從聚集索引中獲取資料通常比在非聚集索引中查詢要快

　　使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值

　　聚簇索引的缺點：

　　聚簇資料最大限度地提高了IO密集型應用的效能，但如果資料全部放在記憶體中，則訪問的順序就沒有那麼重要了，聚集索引也沒有什麼優勢了

　　插入速度嚴重依賴於插入順序，按照主鍵的順序插入是載入資料到innodb表中速度最快的方式，但如果不是按照主鍵順序載入資料，那麼在載入完成後最好使用optimize table命令重新組織一下表

　　更新聚集索引列的代價很高，因為會強制innodb將每個被更新的行移動到新的位置

　　基於聚集索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨頁分裂的問題，當行的主鍵值要求必須將這一行插入到某個已滿的頁中時，儲存引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作，頁分裂會導致表佔用更多的磁碟空間

　　聚集索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由於頁分裂導致資料儲存不連續的時候

　　二級索引可能比想象的更大，因為在二級索引的葉子節點包含了引用行的主鍵列。

　　二級索引訪問需要兩次索引查詢，而不是一次