MySQL學習 - 索引

Balance-Tree & Balance+Tree 

為什麼索引這麼快，一個好的索引能將檢索速度提升幾個量級，這種效率離不開這個資料結構

1.1 門路清

為什麼需要"索引" ? 

我們總得依據什麼才能去找你想查的東西，那麼我們就依據 id=1去尋找一條記錄，怎麼找呢？ 難道是"順序檢索" ?  

資料庫裡的東西現在大都大到分佈在很多個磁碟頁上。如果順序檢索基本就是噩夢。你知道"二分法" 比較快，那是因為資料已經被排過序了。 同樣的如果現在存在一種排過序的資料結構，使得我們能快速去找出我們想要的東西在哪兒，這樣一定很方便對吧。

所以歸根到底，現在最大的問題的就是"這條記錄到底存在哪"。 解決辦法是這樣的: 

• 依據這個鍵比如ID，去產生一個序號，這樣就組成了 Key[序號]-Value[記錄的位置] 的形式
• 這種K-V結構，會變成資料結構的一個節點。到時候我們拿著ID，計算出序號，根據這個序號，在這種資料結構裡暢遊快速找到這個節點
• 找到對應節點後查詢出記錄的位置，去記憶體裡取，完成" select * from users where id = 1"

為什麼叫它"索引" ? 

在上面的介紹中我們已經看到了，實現快速找到內容的關鍵就是這個 "序號" 。這個所謂的序號就非常像"索引"這個名詞

為什麼不用二分法查詢?

理論上，二分法已經是 O(logN), 非常快了。 實際上索引可能很多，多到你都沒辦法把索引全部載入到記憶體裡，所以這些索引基本上都在磁碟裡，依靠磁碟IO，分批次載入到記憶體裡。

既然提到磁碟IO，就應該知道磁碟IO效率非常低，低到實際上就，如果誰能減少磁碟IO次數，誰就會是最好的索引實現方案。

• 二分法一次兩個節點
• 整個樹非常的 "瘦高"  -> 
• 我們需要向下遍歷很多層 -> 
• 我們需要向下載入很多次磁碟IO -> 效率不高
• 我們想要把樹變的矮胖，減少層數
  

1.2 Balance-Tree

1.2.1 Balance-Tree中的節點為什麼是這樣子 ?    查詢資料的過程

• 一個節點中會包含很多個鍵值對, 以及很多個 "指標p" 我們以 { 2 6 } 節點舉例看裡面有什麼
• 節點內部是這樣安排的    {    [p1]     [序號=2]     [p2]     [序號=6]     [p3]      }
• 假設一個現在 序號[6] 來了，正好能匹配上，前往6對應位置取回資料
• 假設一個現在 序號[3] 來了，[3] 不匹配任何，但是位於2~6之間，前往p2繼續尋找
• 假設一個現在 序號[1] 來了，[1] 不匹配任何，且小於2，前往p1繼續尋找
• 節點內的所有元素都已經排過序了，因此拿著[序號]來節點裡查詢的時候效率也比較高

剛剛，我們就走完了一個節點，我們完成了一輪查詢。每一輪開始之前我們會先執行磁碟IO，把下一個節點對應內容從磁碟載入到記憶體裡，然後再在組內查詢，找得到就退出，找不到繼續

根據上述結論，我們也能發現一個重要結論: 既然我們不能一次性把所有索引都載入到記憶體裡，既然我們要分批次做磁碟IO。那樹的高度其實就是我們IO的次數，那麼矮樹就會是最快的方案

func balance_tree_search (node *Node , key int) (*Data,error) {

    if node == nil {
        return nil,fmt.Errorf("最終也沒能找到對應序號")
    }

    for _, pair := range node {

        if (pair.Key == key){
            return pair.Data,nil
        }

        if (pair.Key > key) {
            // 考慮到節點內鍵值對是已經排序，從小到大的
            // 那既然上個鍵值對不滿足，這個鍵值對又過於大
            // 那說明沒有符合的，前往下一個節點
            return balance_tree_search( pair.NextNode, key )
        }
    }

    // 比節點內所有鍵值對都大，直接前往下一個
    return balance_tree_search( pair.NextNode, key )
}  
複製程式碼

1.2.2 Balance-Tree的插入過程

• 現在是這樣，因為BT不能允許一個節點裡的有過多的鍵值對，因此當 [序號4] 過來的時候
• 實際節點 { 3 5 } 不能容納 [序號4] 
• 如果不能容納，那麼我們會一路向上找到能容納的下的節點，於是我們找到根節點
• 相對應的，根節點變成兩個，多出一個位置用於存放指標，相對應的樹結構做出調整
•  {   [p1]  [序號=4]  [p2]   }                   ->                 {   [p1] [序號=4] [p2] [序號=9] [p3]   }

自調整的過程雖然很漫長，看起來也很麻煩，但是這個恰好是滿足了BT的自調整性質

1.2.3 Balance-Tree的刪除過程

• 假設我們現在想要刪除這個紅色的節點，但是刪除後 [序號12] 左邊會缺少一個位置
• 於是我們現在需要調整一下，旋轉一下，成為滿足要求的Balance-Tree

1.2.4 Balance-Tree的定義

• 依據自旋轉過程，保證樹都是一樣高的，m (也叫秩m)
• 每個節點上元素的數量  [m/2 ~ m]
• 節點上元素需要排序

1.3 Balance+Tree

• 在B+T中，中間節點並不會儲存任何跟"資料在那兒"相關的資訊，只會做資料索引，指引你前往葉子節點。 
• 無論怎樣你都要遍歷一下葉子節點，比BT更加穩定
• 不需要前往中間節點，遍歷全部資料的時候比BT快
  
• B+T所有節點都會按順序儲存好資料，並且所有葉子節點都是串在一起的，這樣當資料來的時候我們只要按照粉色的路徑一路尋找就好了

關於索引，你需要知道

2.1 多個列，組合索引

• 在你 create table 的時候假設你定義出 A+B+C 成為你的組合索引，你需要知道這三列現在開始是不等價的，實際上這裡面只有第一列 A 重量級最高
• 在上面我們提到了[序號]，那麼 A+B+C 現在就是這條資料的序號，在進行索引比較的時候會 先比較A，A相同了比較B，B相同了再去比較C
• 多說一句，我們不喜歡使用字串作為索引的原因，是因為"字串比較" 會比 "整數比較" 開銷更大，那種很長的字串比較就更是麻煩了
• A重量級最高:  如果你想真的從索引中受益，那麼你的WHERE篩選條件中一定要帶上這個重量級最高的A，否則索引沒有真的發揮作用，舉個例子，你可以這麼使用索引
• WHERE A=1                            (首列精確匹配)
• WHERE A IS LIKE 1                  (首列的近似匹配)
• WHERE A=1 AND B IS LIKE 2  (首列精確，二列近似)
• 如果你嘗試在篩選條件裡不帶上A，那麼本次查詢索引就根本沒有發揮作用
• 我們都知道 BT&B+T 能擁有一個很良好的"排序性質"，我們既然能按照排序的方式快速找到一個鍵值對，那麼在BT&B+T為基礎的索引上
• 完成 ORDER BY 很快
• 完成範圍查詢很快 ->  B IS IN ( 20,100 )

2.2 雜湊索引

假設我們定義出A+B+C作為索引列，雜湊索引就是針對每一條記錄計算出hash(A,B,C) 對應的值是這條記錄儲存的位置，雜湊索引非常快，但是也有自身對應的一些弊端

• 因為已經沒有排序結構了，因此ORDER BY 功能已經沒有那麼快了
• 雜湊功能需要所有索引列參與，你不能在只有B&C的情況下去指望使用上雜湊索引

2.2.1 雜湊衝突

假設現在兩條記錄能雜湊出同一個值，這種時候:

// 如果只依賴hash 則返回兩條記錄
SELECT * FROM users WHERE hash(name) = 1;
>> liangxiaohan 23 M
   zhangxiaoming 24 F

// 最好的辦法是不僅使用hash同時也指定索引列自身的值
// hash衝突下，形成連結串列，儲存引擎遍歷連結串列所有行
SELECT * FROM users WHERE hash(name) = 1 and name = "liangxiaohan"
>> liangxiaohan 23 M
複製程式碼

2.2.2 自創索引

InnoDB支援雜湊，但他的支援是指，它會自優化你的B樹索引成為"某種程度上的"雜湊索引。針對這一點，你可以自己實現一個簡單的雜湊索引

// 更新表，新建一列用於存放雜湊值
ALTER TABLE ADD COLUMN name_crc VARCHAR(20)

// 關於雜湊值，你可以使用 TRIGGER 實現自動插入
// 你只負責插入name就行了，關於crc32雜湊值它每次會自己計算
CREATE TRIGGER crc_create BEFORE INSERT ON users 
FOR EACH ROW SET NEW.name_crc = crc32(NEW.name)複製程式碼

2.3 聚簇索引

2.3.1 關於聚簇索引，你需要知道

• 聚簇索引 並不是一種新型的索引，它所代表的只是一種資料儲存方式
• 在聚簇索引，相當於一種變形B+T
• 它的中間節點同樣不負責儲存任何資料
• 它的葉子節點會存下這條記錄的所有內容實體，而不是一個指標
• 葉子節點首位相接
• 一個表只能有一個聚簇索引：按照聚簇索引的要求，資料會被存在一個指定的位置。但是資料不能既在這裡又在哪裡，所以只能有一個聚簇索引
• 它之所以被叫做“聚簇” : 是因為按照索引的要求，資料全都被儲存在了指定位置，並且索引上相鄰的位置，他們也儲存的很近

2.3.2 聚簇索引的優點 & 缺點

• 優點
• "聚簇"可能可以發揮出很大的威力: 假設我們令使用者ID成為聚簇索引，那麼這個使用者可能所有相關的資訊全存在一起，我們有可能存在一次IO讀取所有郵件
• 訪問更快:  聚簇索引將索引+資料全都儲存在同一個BT中，讀資料通常更快
• 缺點
• 假設一個應用，像是讀郵件這種應用，本質上是IO密集應用，如果這種時候我們能好好利用聚簇索引，效果卓群。但是如果資料全都存在記憶體中，並不涉及磁碟IO那就沒有多少優勢了
• 最大的問題:  執行插入的速度 。  因為在聚簇索引中，所有的資料已經是直接存在B+T中並且排序了，那麼問題就來了
• 如果我按照聚簇索引順序插入 -> 速度很快
• 如果我隨機插入 -> 涉及資料的複製移動等，速度感人
• 還是關於聚簇的隨機插入: 頁分裂。 假設現在一個記憶體頁已經填滿了資料，但是現在有一個資料試圖在中間插入，儲存引擎會將這一頁分裂成兩頁，將第一頁的一部分複製到第二頁去
• 效率極低
• 產生記憶體碎片
• 因為記憶體不連續導致掃描全表變慢
• 如果真的要隨機插入，記得在插入完成以後使用 OPTIOMIZE TABLE 重新整理記憶體