資料庫索引《一》

目錄

索引的型別  

回表

索引維護

思考與解答

---

   索引是儲存引擎實現的，沒有統一的標準，不同的引擎的索引工作方式不一樣。常見的三種索引結構：雜湊表／有序陣列／搜尋樹。

雜湊表	資料結構：陣列+連結串列的方式； 優點：等值查詢的速度比較快；  缺點：因為無序，做區間查詢比較慢，時間複雜度O(N)； 適用場景：membercache和noslq等一些儲存引擎；
有序陣列	資料結構：陣列； 優點：查詢和範圍查詢效率高，二分查詢，時間複雜度：O(logN)； 缺點：更新資料成本較高，效率低下； 適用場景：靜態儲存引擎[不需要修改的資料]；
N叉樹	資料結構： N叉樹； 優點：查詢和更新的複雜度可以維持在O(logN);
跳錶
LSM樹

索引的型別  

     我們直到MySql裡的預設儲存引擎是InnoDB，InnoDB使用了B+樹索引模型，所以資料都是儲存在B+樹中。每一個索引在InnoDB裡面對應一棵B+樹，每張表由多棵B+樹組成。
索引型別分為：主鍵索引和非主鍵索引。
  主鍵索引的葉子節點存的都是整行資料。在InnoDB，主鍵索引也稱為聚簇索引(clustered index)；
  非主鍵索引的葉子節點內容是主鍵的值。在InnoDB裡，非主鍵索引也被稱為二級索引(secondary index);
 下面通過一個例項來具體分析下索引的結構以及是如何工作的。

USER_TABLE | CREATE TABLE `USER_TABLE` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `orderId` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

//插入資料
insert into students (id, age, orderId) values (1,18,101),(2,19,102),(3,20,103),(4,21,104),(5,22,105);

假設給這張表插入5行資料（id,age,orderId）：(1,18,101),(2,19,102),(3,20,103),(4,21,104),(5,22,105)。假設該表的索引是一棵4叉樹。

回表

下面來看一下：主鍵索引和普通索引的區別

select * from USER_TABLE where id = 2; 

   主鍵索引及主鍵查詢方式，我們只需要搜尋id這棵B+樹，查到的就是整個id=2的行記錄； 

select * from USER_TABLE where orderId = 102;

   普通索引查詢，我們需要先查詢orderId 二級索引，得到id=1，然後再搜尋主鍵索引得到行記錄，這裡可以看到，我們進行來兩次查詢，這個過程叫做回表。所以很明顯，主鍵索引只需要搜尋一次就可以拿到資料。普通索引先搜尋索引拿到主鍵，然後再到主鍵索引搜尋一次才可以拿到這個值(回表)。我們在實際使用過程中，儘量使用主鍵索引減少查詢次數，提高效率。

索引維護

    我們知道B+樹在構造的過程中當某個分支的節點數大於N的時候存在分裂和合並的過程。這裡涉及到資料的增加和刪除，其中增加節點過程中的分頁：將會影響效能及空間利用率下降，因為需要移動一半的資料到一個新的空間。
    從上面我們可以看出主鍵索引的重要性，那麼自增主鍵做索引的好處是什麼呢？
 效能方面
     一般插入新紀錄的時候，我們不需要指定id的值，系統會獲取當前的最大的id自增後作為下一條記錄id的值。這樣的好處是：構造B+ 樹過程中，不需要移動大量的資料，不涉及到葉子節點的分裂，效能較高。不是有序的節點的插入往往成本較高。主要的是通過主鍵做索引無需回表查詢，減少磁碟訪問次數，提高效率。
儲存空間
     主鍵一般都是整型資料，只需要4個位元組，如果是長整型需要8個位元組。但是如果是業務欄位做索引，比如訂單號或者省份證號都會比主鍵大，而主鍵是二級索引的值，所以二級索引就會佔用更大的空間。
    顯然主鍵越小，普通的索引的葉子節點就越小，佔用的空間就越小。

   總體來說，我們需要“儘量使用主鍵來查詢”的原則，避免搜尋兩棵樹。

    當然在KV儲存場景中：只有一個索引且必須唯一，這個時候使用業務欄位直接做主鍵是十分合適的。

思考與解答

問題一：為什麼不使用二叉樹，要使用N叉樹？

答：雖然平衡二叉樹的搜尋和更新效率很高，但是大多數的資料庫並不是採用的二叉樹，這是因為索引不只是存在記憶體中，還要寫到磁碟上。僅僅對於1024個資料節點，二叉樹的樹高達到了10，那麼一次查詢就需要查詢10個資料塊。從磁碟隨機讀取一個資料塊的時間大概是20ms，所以這個時間是200ms，這個速度是很慢的。為了提高查詢的速度，訪問儘量少的資料塊，我們採用了“N叉樹”讓樹的高度降下來。這樣就減少了訪文磁碟的次數及時間。例如一個整數字段的索引，n差不多是1200，對於3層高的樹，其能容納的值為 17億，也就是說尋找一個資料最多隻需要訪問3次磁碟。由於N叉樹很好的讀寫效能優勢，以及適配磁碟的訪問模式，已經被廣泛的應用到資料庫引擎中了。

磁碟定址過程：每次訪問磁碟的一個塊時，磁臂就需移動到正確的磁軌上（這段時間為定址時間），然後碟片就需旋轉到正確的扇區上（這叫旋轉時延），這套動作需要時間，所以說順序寫比隨機寫效能高，大部分情況db的最大瓶頸在io；

問題二：B+樹的缺點

答：(1) 插入可能會引起資料頁的分裂；(2) 刪除可能會引起資料頁的合併。二者都是比較重的io消耗。所以比較好的方式是順序插入，這也是我們選擇自增主鍵的原因。

問題三：N叉樹的n能調整嗎？

 答：樹高取決於葉子樹(資料行數) 和”N叉樹”的N。而N又是頁大小和索引大小決定的。

     磁碟讀取資料是以磁碟塊為基本單位的（1 Block = 4KB），也即是每次最少讀4KB，記憶體頁的大小是4KB。為了達到最大效能，B+Tree 的每個節點的大小就是塊的大小（4KB），這樣每個節點可以一次性全部讀到記憶體，減少磁碟 io 次數。由於節點中只儲存 key 和 point。

假設有 n 個 key，那麼就有n+1 個point：
32位系統中，int 型別佔4個位元組，指標佔4個位元組，則有：4n+4*(n+1)<=4096  n=511。
64位系統中，int 型別佔4個位元組，指標佔8個位元組，則有：4n+8*(n+1) <= 4096n = 170；
大概值：32位系統中，B+Tree 的度為512，64位系統中，B+Tree 的度171。
如果 MySQL 的 B+Tree 結構中除了必備的 key 和 point，還有其他結構的話，度會相應減少。

問題4：重建索引需要注意什麼？

   二叉樹的高度對sql的查詢起著一定的影響，另外當資料插入的時候，可能會產生分頁，儲存空間利用率低，刪除的資料也會導致碎片空間，這時候就需要重建優化索引，一般情況很少去優化或者是重建。
   如果是主鍵索引，無論是刪除還是重建主鍵索引，都會將整個表重建，並且會使所有的二級索引失效，並且會用ROWID來做主鍵索引。當需要刪除和重建普通索引的時候，就只需要刪除該普通索引即可，正常來說沒有影響。
重建索引的方式：
(1) 離線的業務：整個資料庫遷移，先dump出來再重建表；
(2) 使用alter操作：ALTER TABLE TABLE ENGIN=InnoDB;這個命令回原地重建表結構；
(3) 使用repair table，這個取決於儲存引擎是否支援；

 

學習筆記，內容簡單，用於複習，原內容2月有更新。
##參考資料，《MySql實戰詳解》