面試必備之MYSQL索引底層原理分析

Java特種兵發表於2019-03-08
面試MySql索引
  • Mysql索引的本質
  • Mysql索引的底層原理
  • Mysql索引的實戰經驗

面試

問：資料庫中最常見的慢查詢優化方式是什麼？答：加索引。問：為什麼加索引能優化慢查詢？答1：...不知道答2：因為索引其實就是一種優化查詢的資料結構，比如Mysql中的索引是用B+樹實現的，而B+樹就是一種資料結構，可以優化查詢速度，可以利用索引快速查詢資料，所以能優化查詢。問：你知道哪些資料結構可以提高查詢速度？（聽到這個問題就感覺此處有坑...）答：雜湊表、完全平衡二叉樹、B樹、B+樹等等。問：那這些資料結構既然都能優化查詢速度，那Mysql種為何選擇使用B+樹？答：...不知道

提問

SHOW INDEX FROM employees.titles;

有一個titles表，主鍵由emp_no，title，from_date三個欄位組成。那麼以下幾個語句會用到索引嗎：

  1. select * from employees.titles where emp_no = 1
  2. select * from employees.titles where title = '1'
  3. select * from employees.titles where emp_no = '1' and title = 1;
  4. select * from employees.titles where title = '1' and emp_no = 1;

索引(Index)

到底什麼是索引(Index)？大學老師是這麼定義的：索引就像書的目錄Mysql官網是這麼定義的：Indexes are used to find rows with specific column values quickly我是這麼定義的：索引是一種優化查詢的資料結構

為什麼雜湊表、完全平衡二叉樹、B樹、B+樹都可以優化查詢，為何Mysql獨獨喜歡B+樹？

雜湊表是什麼？

雜湊表（Hash table，也叫雜湊表），是根據鍵值(Key value)而直接進行訪問的資料結構。也就是說，它通過把鍵值對映到表中一個位置來訪問記錄，以加快查詢的速度。這個對映函式叫做雜湊函式，存放記錄的陣列叫做雜湊表。雜湊表的做法其實很簡單，就是把Key通過一個固定的演算法函式既所謂的雜湊函式轉換成一個整型數字，然後就將該數字對陣列長度進行取餘，取餘結果就當作陣列的下標，將value儲存在以該數字為下標的陣列空間裡。而當使用雜湊表進行查詢的時候，就是再次使用雜湊函式將key轉換為對應的陣列下標，並定位到該空間獲取value，如此一來，就可以充分利用到陣列的定位效能進行資料定位。

雜湊表的特點是什麼？

假如有這麼一張表(表名：sanguo)：

現在對name欄位建立雜湊索引：注意欄位值所對應的陣列下標是雜湊演算法隨機算出來的，所以可能出現雜湊衝突。那麼對於這樣一個索引結構，現在來執行下面的sql語句：select * from sanguo where name = '周瑜'可以直接對‘周瑜’按雜湊演算法算出來一個陣列下標，然後可以直接從資料中取出資料並拿到鎖對應那一行資料的地址，進而查詢那一行資料。那麼如果現在執行下面的sql語句：select * from sanguo where name > '周瑜'則無能為力，因為雜湊表的特點就是可以快速的精確查詢，但是不支援範圍查詢。

如果用完全平衡二叉樹呢？

還是上面的表資料用完全平衡二叉樹表示如下圖（為了簡單，資料對應的地址就不畫在圖中了。）：圖中的每一個節點實際上應該有四部分：

  1. 左指標，指向左子樹
  2. 鍵值
  3. 鍵值所對應的資料的儲存地址
  4. 右指標，指向右子樹

另外需要提醒的是，二叉樹是有順序的，簡單的說就是“左邊的小於右邊的”假如我們現在來查詢‘周瑜’，需要找2次（第一次曹操，第二次周瑜），比雜湊表要多一次。而且由於完全平衡二叉樹是有序的，所以也是支援範圍查詢的。

如果用B樹呢？

還是上面的表資料用B樹表示如下圖（為了簡單，資料對應的地址就不畫在圖中了。）：我們可以發現同樣的元素，B樹的表示要比完全平衡二叉樹要“矮”，原因在於B樹中的一個節點可以儲存多個元素。

如果用B+樹呢？

還是上面的表資料用B+樹表示如下圖（為了簡單，資料對應的地址就不畫在圖中了。）：我們可以發現同樣的元素，B+樹的表示要比B樹要“胖”，原因在於B+樹中的非葉子節點會冗餘一份在葉子節點中，並且葉子節點之間用指標相連。

那麼B+樹到底有什麼優勢呢？

這裡我們用“反證法”，假如我們現在就用完全平衡二叉樹作為索引的資料結構，我們來看一下有什麼不妥的地方。實際上，索引也是很“大”的，因為索引也是儲存元素的，我們的一個表的資料行數越多，那麼對應的索引檔案其實也是會很大的，實際上也是需要儲存在磁碟中的，而不能全部都放在記憶體中，所以我們在考慮選用哪種資料結構時，我們可以換一個角度思考，哪個資料結構更適合從磁碟中讀取資料，或者哪個資料結構能夠提高磁碟的IO效率。回頭看一下完全平衡二叉樹，當我們需要查詢“張飛”時，需要以下步驟

  1. 從磁碟中取出“曹操”到記憶體，CPU從記憶體取出資料進行筆記，“張飛”<“曹操”，取左子樹（產生了一次磁碟IO）
  2. 從磁碟中取出“周瑜”到記憶體，CPU從記憶體取出資料進行筆記，“張飛”>“周瑜”，取右子樹（產生了一次磁碟IO）
  3. 從磁碟中取出“孫權”到記憶體，CPU從記憶體取出資料進行筆記，“張飛”>“孫權”，取右子樹（產生了一次磁碟IO）
  4. 從磁碟中取出“黃忠”到記憶體，CPU從記憶體取出資料進行筆記，“張飛”=“張飛”，找到結果（產生了一次磁碟IO）

同理，回頭看一下B樹，我們發現只傳送三次磁碟IO就可以找到“張飛”了，這就是B樹的優點：一個節點可以儲存多個元素，相對於完全平衡二叉樹所以整棵樹的高度就降低了，磁碟IO效率提高了。而，B+樹是B樹的升級版，只是把非葉子節點冗餘一下，這麼做的好處是為了提高範圍查詢的效率。

所以，到這裡，我們可以總結出來，Mysql選用B+樹這種資料結構作為索引，可以提高查詢索引時的磁碟IO效率，並且可以提高範圍查詢的效率，並且B+樹裡的元素也是有序的。

那麼，一個B+樹的節點中到底存多少個元素合適呢？

這裡有必要先來了解一下磁碟IO的原理。

磁碟I/O的本質

磁碟分類

機械硬碟

固態硬碟

從上面的原理我們也能知道，固態硬碟比機械硬碟快的最根本最簡單的原因就是：固態硬碟使用的電路進行讀寫，而機械硬碟使用的機械運動。其實不管是機械硬碟還是固態硬碟都是儲存介質，真正控制讀寫的是作業系統。

機械硬碟儲存原理

磁碟立體結構圖

一個磁碟由大小相同且同軸的圓形碟片組成，磁碟可以轉動（各個磁碟必須同步轉動）。在磁碟的一側有磁頭支架，磁頭支架固定了一組磁頭，每個磁頭負責存取一個磁碟的內容。磁頭不能轉動，但是可以沿磁碟半徑方向運動（實際是斜切向運動），每個磁頭同一時刻也必須是同軸的，即從正上方向下看，所有磁頭任何時候都是重疊的（不過目前已經有多磁頭獨立技術，可不受此限制）。

磁碟片結構圖

碟片被劃分成一系列同心環，圓心是碟片中心，每個同心環叫做一個磁軌，所有半徑相同的磁軌組成一個柱面。磁軌被沿半徑線劃分成一個個小的段，每個段叫做一個扇區，每個扇區是磁碟的最小儲存單元，大小一般為521位元組。

磁碟讀取資料邏輯

當需要從磁碟讀取資料時，系統會將資料邏輯地址傳給磁碟，磁碟的控制電路按照定址邏輯將邏輯地址翻譯成實體地址，即確定要讀的資料在哪個磁軌，哪個扇區。為了讀取這個扇區的資料，需要將磁頭放到這個扇區上方，為了實現這一點，磁頭需要移動對準相應磁軌，這個過程叫做尋道，所耗費時間叫做尋道時間，然後磁碟旋轉將目標扇區旋轉到磁頭下，這個過程耗費的時間叫做旋轉時間。

固態硬碟儲存原理

固態硬碟（Solid State Drives），用固態電子儲存晶片陣列而製成的硬碟，由控制單元和儲存單元（FLASH晶片、DRAM晶片）組成。固態硬碟在介面的規範和定義、功能及使用方法上與普通硬碟的完全相同，在產品外形和尺寸上也完全與普通硬碟一致。

控制單元

每個SSD都有一個控制器(controller)將儲存單元連線到電腦，主控器可以通過若干個通道（channel）並行操作多塊儲存單元

儲存單元

一個Flash Page由兩個或者多個Die(又稱chips組成)，這些Dies可以共享I/0資料匯流排和一些控制訊號線。一個Die又可以分為多個Plane,而每個Plane又包含多個多個Block,每個Block又分為多個Page。以Samsung 4GB Flash為例，一個4GB的Flash Page由兩個2GB的Die組成，共享8位I/0資料匯流排和一些控制訊號線。每個Die由4個Plane組成，每個Plane包含2048個Block，每個Block又包含64個4KB大小的Page

訪問SSD的原理

Host是通過LBA（Logical BlockAddress，邏輯地址塊）訪問SSD的，每個LBA代表著一個Sector（一般為512B大小），作業系統一般以4K為單位訪問SSD，我們把Host訪問SSD的基本單元叫使用者頁（Host Page）。而在SSD內部，SSD主控與Flash之間是Flash Page為基本單元訪問Flash的，我們稱Flash Page為物理頁（Physical Page）。Host每寫入一個Host Page, SSD主控會找一個Physical Page把Host資料寫入，SSD內部同時記錄了這樣一條對映（Map）。有了這樣一個對映關係後，下次Host需要讀某個Host Page 時，SSD就知道從Flash的哪個位置把資料讀取上來。

區域性性原理與磁碟預讀

電腦科學中著名的區域性性原理：當一個資料被用到時，其附近的資料也通常會馬上被使用。所以作業系統為了提高效率，讀取資料時往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，作業系統也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這裡的一定長度叫做頁，也就是作業系統操作磁碟時的基本單位。一般作業系統中一頁的大小是4Kb。

總結

所以，回到我們的問題，B+樹中一個節點到底存多少個元素合適？，其實也可以換個角度來思考B+樹中一個節點到底多大合適？答案是：B+樹中一個節點為一頁或頁的倍數最為合適。因為如果一個節點的大小小於1頁，那麼讀取這個節點的時候其實也會讀出1頁，造成資源的浪費；如果一個節點的大小大於1頁，比如1.2頁，那麼讀取這個節點的時候會讀出2頁，也會造成資源的浪費；所以為了不造成浪費，所以最後把一個節點的大小控制在1頁、2頁、3頁、4頁等倍數頁大小最為合適。

那麼，Mysql中B+樹的一個節點大小為多大呢？

這個問題的答案是“1頁”，這裡說的“頁”是Mysql自定義的單位（其實和作業系統類似），Mysql的Innodb引擎中一頁的預設大小是16k（如果作業系統中一頁大小是4k，那麼Mysql中1頁=作業系統中4頁），可以使用命令SHOW GLOBAL STATUS like 'Innodb_page_size';檢視。並且還可以告訴你的是，一個節點為1頁就夠了。

為什麼一個節點為1頁（16k）就夠了？

解決這個問題，我們先來看一下Mysql中利用B+樹的具體實現。

Mysql中MyISAM和innodb使用B+樹

通常我們認為B+樹的非葉子節點不儲存資料，只有葉子節點才儲存資料；而B樹的非葉子和葉子節點都會儲存資料，會導致非葉子節點儲存的索引值會更少，樹的高度相對會比B+樹高，平均的I/O效率會比較低，所以使用B+樹作為索引的資料結構，再加上B+樹的葉子節點之間會有指標相連，也方便進行範圍查詢。上圖的data區域兩個儲存引擎會有不同。

MyISAM中的B+樹

MYISAM中葉子節點的資料區域儲存的是資料記錄的地址

主鍵索引

輔助索引

總結

MyISAM儲存引擎在使用索引查詢資料時，會先根據索引查詢到資料地址，再根據地址查詢到具體的資料。並且主鍵索引和輔助索引沒有太多區別。

InnoDB中的B+樹

InnoDB中主鍵索引的葉子節點的資料區域儲存的是資料記錄，輔助索引儲存的是主鍵值

主鍵索引

輔助索引

總結

Innodb中的主鍵索引和實際資料時繫結在一起的，也就是說Innodb的一個表一定要有主鍵索引，如果一個表沒有手動建立主鍵索引，Innodb會檢視有沒有唯一索引，如果有則選用唯一索引作為主鍵索引，如果連唯一索引也沒有，則會預設建立一個隱藏的主鍵索引（使用者不可見）。另外，Innodb的主鍵索引要比MyISAM的主鍵索引查詢效率要高（少一次磁碟IO），並且比輔助索引也要高很多。所以，我們在使用Innodb作為儲存引擎時，我們最好：

  1. 手動建立主鍵索引
  2. 儘量利用主鍵索引查詢

回到我們的問題：為什麼一個節點為1頁（16k）就夠了？

對著上面Mysql中Innodb中對B+樹的實際應用（主要看主鍵索引），我們可以發現，B+樹中的一個節點儲存的內容是：

  • 非葉子節點：主鍵+指標
  • 葉子節點：資料

那麼，假設我們一行資料大小為1K，那麼一頁就能存16條資料，也就是一個葉子節點能存16條資料；再看非葉子節點，假設主鍵ID為bigint型別，那麼長度為8B，指標大小在Innodb原始碼中為6B，一共就是14B，那麼一頁裡就可以儲存16K/14=1170個(主鍵+指標)，那麼一顆高度為2的B+樹能儲存的資料為：1170*16=18720條，一顆高度為3的B+樹能儲存的資料為：1170*1170*16=21902400（千萬級條）。所以在InnoDB中B+樹高度一般為1-3層，它就能滿足千萬級的資料儲存。在查詢資料時一次頁的查詢代表一次IO，所以通過主鍵索引查詢通常只需要1-3次IO操作即可查詢到資料。所以也就回答了我們的問題，1頁=16k這麼設定是比較合適的，是適用大多數的企業的，當然這個值是可以修改的，所以也能根據業務的時間情況進行調整。

最左字首原則

我們模擬資料建立一個聯合索引select *, concat(right(emp_no,1), "-", right(title,1), "-", right(from_date,2)) from employees.titles limit 10;

那麼對應的B+樹為

我們判斷一個查詢條件能不能用到索引，我們要分析這個查詢條件能不能利用某個索引縮小查詢範圍對於select * from employees.titles where emp_no = 1是能用到索引的，因為它能利用上面的索引所有查詢範圍，首先和第一個節點“4-r-01”比較，1<4，所以可以直接確定結果在左子樹，同理，依次按順序進行比較，逐步可以縮小查詢範圍。對於select * from employees.titles where title = '1'是不能用到索引的，因為它不能用到上面的所以，和第一節點進行比較時，沒有emp_no這個欄位的值，不能確定到底該去左子樹還是右子樹繼續進行查詢。對於select * from employees.titles where title = '1' and emp_no = 1是能用到索引，按照我們的上面的分析，先用title='1'這個條件和第一個節點進行比較，是沒有結果的，但是mysql會對這個sql進行優化，優化之後會將emp_no=1這個條件放到第一位，從而可以利用索引。


相關文章