前言:
每天都在跟 mysql 打交道，你知道執行一條簡單的 select 語句，都經歷了哪些過程嗎？
首先，mysql 主要是由 server 層和儲存層兩部分構成的。server 層主要包括聯結器、查詢快取，分析器、優化器、執行器。儲存層主要是用來儲存和查詢資料的，常用的儲存引擎有 InnoDB、MyISAM，MySQL 5.5.5版本後使用 InnoDB 作為預設儲存引擎。
聯結器
聯結器主要負責將 mysql 客戶端和服務端建立連線，連線成功後，會獲取當前連線使用者的許可權。這裡獲取到的許可權對整個連線都有效，一旦連線成功後，如果使用管理員賬號對該使用者更改許可權，當前連線中的擁有的許可權保持不變，只有等到下次重新連線才會更新許可權。
查詢快取
- 連線成功後，即開始要正式執行 select 語句了，但是在執行查詢之前，mysql 會去看下有沒有該條語句的快取內容，如果有快取直接從快取中讀取並返回資料，不再執行後面的步驟了，結束查詢操作。
- 如果沒有快取則繼續往後執行，並將執行結果和語句儲存在快取中。
- 注意在 mysql8 後已經沒有查詢快取這個功能了，因為這個快取非常容易被清空掉，命中率比較低。只要對錶有一個更新，這個表上的所有快取就會被清空，因此你剛快取下來的內容，還沒來得及用就被另一個更新給清空了。
分析器
- 既然沒有查到快取，就需要開始執行 sql 語句了，在執行之前肯定需要先對 sql 語句進行解析。分析器主要對 sql 語句進行語法和語義分析，檢查單詞是否拼寫錯誤，還有檢查要查詢的表或欄位是否存在。
- 如果分析器檢測出有錯誤就會返回類似 "You have an error in your sql" 這樣的錯誤資訊，並結束查詢操作。
優化器
- 通過分析器之後，mysql 就算是理解了你要執行的操作了。通常對於同一個 sql 語句，mysql 內部可能存在多種執行方案，比如存在多個索引時，該選擇哪個索引，多個表關聯查詢時，怎麼確認各個表的連線順序。
- 這些方案的執行結果都一樣，但是執行效率不一樣，所以 mysql 在執行之前需要嘗試找出一個最優的方案來，這就是優化器的主要工作。但是 mysql 也會有選擇錯誤方案的時候，這裡暫不細說，留到後面再解釋原因。
執行器
- 經過優化器選定了一個方案後，執行器就按照選定的方案執行 sql 語句。前面我們有講過，在聯結器中會讀取當前使用者的許可權，聯結器中只是獲取許可權而已，並沒有對許可權進行判斷和校驗。
- 所以在執行器中，在執行語句之前會判斷許可權，如果沒有對應的許可權則會直接返回並提示沒有相關許可權。
- 這裡你可能會問，為什麼不在聯結器中就直接判斷許可權呢，這裡我覺得可能是因為 mysql 要查詢的表並不一定僅限於 sql 語句中字面上的那些表，有的時候可能需要經過分析器和優化器之後才能確定到底要怎麼執行，所以許可權校驗放在執行器中是有道理的。
- 注意如果是在前面的查詢快取中查到快取之後，也會在返回結果前做許可權校驗的。
- 許可權校驗通過之後，就繼續開啟表，呼叫儲存引擎提供的介面去查詢並返回結果集資料。
到這裡，一條查詢 sql 語句就執行結束了。
開始
- 不知道你有沒有這種感覺，那些所謂的資料結構和演算法，在日常開發工作中很少用到或者幾乎不曾用到，可能只是在每次換工作準備面試的時候才會撿起來學習學習。
那我希望今天這篇文章能讓你對資料結構的具體應用能有個初步的概念，就像上面說的一樣,先從我們每天都在用的 mysql 資料庫說起吧。
- 首先，mysql 主要是由 server 層和儲存層兩部分構成的。server 層主要包括聯結器、查詢快取，分析器、優化器、執行器。儲存層主要是用來儲存和查詢資料的，常用的儲存引擎有 InnoDB、MyISAM，MySQL 5.5.5版本後使用 InnoDB 作為預設儲存引擎。
- 我們主要討論 mysql 的儲存層，不同的儲存引擎其底層的資料結構是不一樣的，我們這裡就以預設的 InnoDB 為例，所以嚴格來說應該是 InnoDB 為啥要選擇 B+ 樹這種資料結構來儲存資料。
在文章正式開始之前，你先要知道 mysql 中的 InnoDB 在底層是採用 B+ 樹這種資料結構來儲存資料的。你先記住就好了，下面我們再來一步一步解釋為什麼。
幾種常見的資料結構
首先你要知道，mysql 的索引主要是為了提高查詢效率的，那一定得找一個合適的資料結構來儲存資料，雜湊表、陣列、二叉搜尋樹這三種常見的資料結構都可以提高查詢效率。
雜湊表
- 雜湊表就是一種以鍵值對來儲存資料的結構，你可以通過一個 key 就可以很快的查詢出對用的 value 值。雜湊表主要是利用了陣列的隨機訪問特性，實現思想主要是通過一個雜湊函式把 key 轉換成一個雜湊值，這個雜湊值就對應陣列中的某個下標。
- 但是由於雜湊表是無序的，區間查詢效率會非常的慢，所以雜湊表通常只用於查詢單個值。
有序陣列
- 陣列就好說了，陣列具有連續性和隨機訪問特性，因此陣列都能很高效的進行單個等值查詢和區間查詢，但是 mysql 不僅僅是查詢資料，還會有插入和刪除資料的操作。
- 在有序陣列中插入或刪除一個資料會需要批量移動陣列中其他資料，這是一個不小的消耗，影響效能。因此有序陣列適合處理靜態資料，比如一些過往的不會再修改的資料。
- 在這裡你可能會問，既然雜湊表其實也是利用了陣列的特性，那有了陣列為啥還需要雜湊表呢。是因為陣列下標 key 只能是數字，而雜湊表可以支援字串 key，雜湊函式可以把這個 key 轉換成一個陣列下標。
- 同時，不同的 key 如果通過雜湊函式轉換成了相同的陣列下標，這就會造成衝突，在雜湊表中一般會通過再拉出一個連結串列來儲存這個衝突的值。
二叉搜尋樹
- 注意，二叉搜尋樹和二叉樹不一樣，二叉樹是指每個節點的左兒子小於父節點，父節點又小於右兒子，即二叉搜尋樹的中序遍歷就是一個有序序列。
- 由於索引不僅僅是存在記憶體中，還會儲存在硬碟中，因此就會涉及到 IO 效能了，就要求樹的高度不能太高。實際上 B+ 樹就是通過二叉搜尋樹推演改進的，我將在後面的文章再詳細解釋這個改進過程。
小結
- 雜湊表適合等值查詢，由於是無序的，區間查詢會很慢。
- 有序陣列適合等值和區間查詢，但是陣列具有連續性，插入和刪除操作都可能需要移動其他元素。
- 二叉搜尋樹由於樹的高度，區間查詢需要中序遍歷，都會導致查詢效率很慢。
- 注意，在一些文章中經常會把 B+ 樹說成 B 樹或者 B-tree，這其實是錯誤的，B 樹和 B+ 是兩種不同的樹，B+ 樹是 B 樹的一個優化，後面的文章我會再詳細解釋這個優化過程。
- 而且 B- 樹其實也就是 B 樹，這個符號並不是加減中的減號，並不是所謂的 "B 減樹"，只是一個連線符號而已。
具體的原因
索引為什麼要儲存在硬碟中
- 首先要明白幾個概念，伺服器儲存一般分記憶體和硬碟，記憶體的大小相對於硬碟來說是很小的。記憶體的訪問速度是納秒級別的，非常快，而硬碟的訪問速度相對記憶體來說就比較慢了。
- 不管是訪問記憶體還是硬碟資料，作業系統都是按資料頁來讀取資料的，即每訪問一次硬碟或記憶體，只讀取一頁大小的資料，一頁的大小約等於 4 kb，向硬碟讀取資料的操作叫做磁碟 IO。
- 看到這裡你或許會知道了 mysql 索引為啥不儲存在記憶體中了吧，一方面是雖然記憶體訪問速度快但容量一般都比較小，存不了多少資料，再一個 mysql 需要讓資料持久化，如果伺服器斷電或異常重啟會導致資料丟失。
怎麼讓二叉搜尋樹支援區間查詢
上面提到過二叉搜尋樹，為了讓二叉搜尋樹也支援區間查詢，我們把二叉樹的葉子節點通過一個雙向連結串列來連線，並且這個連結串列是有序的，注意葉子節點和普通節點是不一樣的
因此只需要先找到區間的起始值在連結串列中的位置，然後再往後遍歷，直到遍歷到區間的終止值，即可完成區間查詢。如下圖查詢 7-30 這個區間的資料。
如何提升查詢速度
- 因為二叉搜尋樹儲存在硬碟中，我們每訪問一個節點，就對應著一次硬碟 IO 操作，上面有說過向硬碟讀取資料速度比較慢。因此樹的高度就代表硬碟 IO 操作的次數，所以我們要想辦法讓樹的高度變矮，來減少硬碟 IO。
- 要想樹變矮一些，那就把樹多分一些叉來吧，變成一顆多叉樹。下面分別用二叉樹和五叉樹來儲存 16 條資料，看下樹的高度又怎樣的變化。
- 根節點一般儲存在記憶體中，普通節點和葉子結點儲存在硬碟中，因此顯然二叉樹的高度為 5，需要 5 次硬碟 IO，而五叉樹的高度為 2，查詢一個資料只需要 2 次硬碟 IO。
- 當然這僅僅是一個小資料的例子，如果有一億條資料，我們構建一個 100 叉樹，這棵樹的高度也只有 3，因此多叉樹能大大降低硬碟 IO，提升查詢速度。
那麼問題又來了，對於相同的資料量，是不是構建的多叉樹的叉越多越好呢，因為叉越多樹的高度就會越矮。
上面有說過操作系是按資料頁大小來訪問硬碟的，每次 IO 只讀取一個資料頁大小的資料，如果要讀取的資料大於一個資料頁，則會導致多次 IO。因此我們要儘量讓每個節點的資料大小剛好等於一個資料頁大小，即每訪問一個節點只需一次 IO。
插入和刪除資料怎麼辦
上面其實都是為了提高查詢效能的，mysql 通常還有插入和刪除操作的，這裡我們再簡單說一下 B+ 樹如何處理插入和刪除節點的操作。
- 這裡我們把多叉樹稱作 m 叉樹，這個 m 值是通過資料頁大小和節點數計算出來的，儘量保證每訪問一個節點就是一個資料頁的大小，而且每個節點最多隻有 m 個子節點。
- 現在我們要往資料庫中插入新的資料，即要往 m 叉樹中插入新的節點，這可能就會導致某些節點的子節點個數大於 m，也就會導致該節點大小大於一個資料頁，訪問該節點就需要多次 IO。
- 為了解決這個問題，m 叉樹會把該節點分裂成兩個節點，然後改分裂操作又會導致其父節點的子節點數可能超過 m，我們再用同樣的方法分裂節點，一直影響到根節點。
- 刪除操作也是類似的思想，如果有頻繁的刪除節點，就會導致某些節點的子節點過少，就會浪費儲存空間並降低查詢效率。所以就要想辦法讓這些節點合併起來，合併的話就有可能會導致其子節點數超過 m，超過的話就再用上面的分裂方法分裂子節點。
關於節點分裂和合並操作就簡單說這些了，也不畫圖了，知道這個處理思想就好了。
下面再總結一下 B+ 樹：
- B+ 樹就是一種多叉樹，是由二叉搜尋樹不斷演變過來的，為了滿足區間快速查詢，B+ 樹的葉子節點通過雙向連結串列串聯起來。
- 這裡使用雙向連結串列是為了支援順序和倒序查詢，雖然雙向連結串列相對於單向連結串列雖然會浪費一倍的指標空間，但是在硬碟中這點空間幾乎微乎其微，用這點空間換時間是一件很值得的事情。
- B+ 樹的子節點數不超過 m 個，同時也不能少於 m/2 個，一旦超過就需要分裂，一旦少於就需要合併。