MySQl索引基礎

平衡樹索引

平衡樹基礎

InnoDB引擎的平衡樹采用B+Tree索引，先分析B-Tree和B+Tree的結構特點，以及為什麼采用B+Tree結構。

• B-Tree結構：
  • 每個節點都有資料
  • 節點的值有自己的範圍： [ m 2 , m ] [\frac{m}{2}, m] [2m​,m]
  • 中序周遊是遞增的

    

• B+Tree結構：
  • 節點不存儲資料，隻存儲索引
  • 父節點的值，在所有的子節點中一定被包含；且父節點的值在子節點中一定是第一個或者最後一個
  • 子節點存儲資料，而且子節點互相連接配接，形成一個順序的鍊式結構

    

B-/+Tree作為索引的特點：

• 優：樹的高度低，一次讀取多個節點，減少磁盤的IO次數；
• 缺：是平衡樹結構複雜，需要調整結構耗費時間，但是這相對磁盤IO時間可以忽略。

B+Tree比B-Tree的優勢：

• B+Tree的非葉子節點沒有資料，是以可以使得葉子節點包含更多的鍵值，減低樹的高度，優化查詢次數
• B+Tree擷取一定範圍資料時，B+Tree不需要回溯等的操作，而是直接根據索引定位到葉子節點的資料範圍的兩端，然後利用葉子的鍊式結構讀取
• 插入或删除資料時，需要調整的僅僅時索引排列方式，底層的資料部分不變

缺點：B+Tree使用了多餘的節點，因為所有的孩子節點都要儲存父節點的資料。

推薦文章：https://blog.csdn.net/qq_26222859/article/details/80631121

InnoDB的B+Tree索引

比對特點：

• 索引執行最左比對的原則，從最左側向右側比對
• 索引比對無法跳過中間的列，向後繼續比對
• 無法比對表達式中的值，比如

  where id +1 < 6

  中的

  id

  就無法執行比對

設計索引的基本原則：

• 最左側比對盡量區分度較大
• 使用适中長度的可區分比對，太短區分效果不好，太長速度慢

索引互動：

• 相交操作(AND)出現了多個索引時，考慮合并這多個索引
• 聯合操作(OR)出現時，會耗費CPU進行排序等操作，耗費時間

哈希索引

• 隻能精确比對
• 索引列必須一次性全部使用，不能單獨使用
• 無法排序，查詢區間等。使用時，隻能進行

  =

  、

  !=

  或

  IN()

  等操作

聚簇索引

說明：聚簇索引是一種存儲形式，葉子節點是存儲資料的；而普通索引的葉子存儲的是指針，指向實際的資料位址

特點：

• 資料全部位于葉子節點中
• 資料行與相鄰的鍵位資料緊密相連
• 葉子中包含全部資料，節點中隻是索引
• 一個表中隻能有一個聚簇索引

資料存儲模式：

以資料頁的形式存儲，即一個批次的資料存放到一個資料頁中，加速IO

優勢：

• 相關的資料緊密排列在一起，一次IO操作可以批量讀取大量的資料。
• 針對主鍵的通路速度

劣勢

• 插入和删除的效率依賴主鍵，對主鍵執行過多的插入删除，會影響存儲頁的排列，進而引發效率問題
• 更新和移動資料，同樣也可能會造成類似的問題
• 稀疏的資料會使得資料頁使用率低，使得全表掃描效率差

索引的一些總結

使用索引有3個主要的優勢：

• 減少磁盤IO的次數
• 減少了排序和臨時表
• 把随機IO變成了順序IO

索引所在的列不能在表達式中，否則無法使用索引的特性。

索引的基本操作

建立索引：

ALTER TABLE table_name ADD INDEX index_name (column_list) # 普通索引
ALTER TABLE table_name ADD UNIQUE (column_list) # 唯一索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list) # 主鍵索引

# create的方式
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
           

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
           

檢視索引