MySQL– 索引指南

  • 2020 年 2 月 10 日
  • 筆記

本文以MySQL InnoDB引擎為基礎，講解索引相關概念以及優化手段，很適合開發以及業務同學參考，避免工作中因為DB性能導致的一系列雪崩問題。

索引的數據結構

在InnoDB下，索引文件與數據文件是合併在一起的，想像一下，一顆樹，非葉子節點都是索引文件，葉子節點都是數據文件，從而構成一張完整的表數據，因此還有一種專業說法叫索引組織表。該模式下索引文件即數據文件，數據文件即索引文件，但是為了提高查詢速度，必然會在邏輯上分為索引與數據兩部分內容，索引大多數時候都是存放在內存中，當一個查詢在索引中定位數據後，會去磁盤中加載對應的數據內容，因此一定程度上磁盤IO的次數成為了提高索引性能的關鍵點。

B+樹結構

B+樹為InnoDB的數據結構，與B樹最大的不同有兩點。1：其葉子節點只存放數據，非葉子節點存放索引。2：葉子節點由於數據都是有序的，因此葉子節點之間使用雙向鏈表連接。換一種描述，為增加樹的高度，從而減少查詢的磁盤IO次數。接下來分別闡述為什麼這麼做。

1. 為什麼非葉子節點不能存放數據？ 該問題本質上是為什麼不能使用B樹的結構。這裡借用 為什麼 MySQL 使用 B+ 樹 · Why』s THE Design? 中的描述圖。 B樹的非葉子節點中會存放數據，因此每一次查詢都必須從根節點開始，因為每一個節點上都可能會有數據，因此對於範圍類型查詢就會很吃力，磁盤IO次數會增加不少。其次由於節點中包含數據，一般情況下數據比索引會大很多，因此也不適合全部存放到內存，索引本身就會消耗掉一些磁盤IO，而B+樹則改進了這些缺點，更加優化了索引定位後，再獲取數據這一策略。

2. 葉子節點為什麼使用雙向鏈表？ 對於B+樹，其葉子節點使用雙向鏈表關聯起來，假設查詢條件where age > 5 and age < 9，其中age存在索引，那麼根據age查詢到9這個節點後，其可以利用索引的有序性，直接通過雙向鏈表進行範圍查詢，而不需要再次從根節點出發。

索引的類型

聚集索引

在InnoDB中，聚集索引指的是主鍵所對應的B+樹，之所以叫聚集，因為其是根據主鍵索引進行存儲，索引即數據，數據即索引。 一張表只能有一個主鍵，一張表只能有一個聚集索引，因此聚集索引的選擇就比較真貴，對無意義的id來管理聚集索引的方式應該有所排斥。

非聚集索引

在InnoDB中，主鍵以外的輔助索引為非聚集索引，也有說法叫次級索引，其特點是葉子節點會指向對應的主鍵索引，而不是直接指向數據，索引本身就是索引。因此當一個查詢使用到了輔助索引，其本質上會查詢兩顆樹，一是該輔助索引對應的B+樹，找到主鍵後，再去主鍵索引對應的B+樹去獲取對應的數據。

索引的優化

闡述上述結構的最終目地是為了優化，而優化更多的是從開發者角度建立合適的表結構以及索引，寫出走合理索引的SQL。

選擇合適的列建立索引

索引的選擇與具體的業務息息相關，比如用戶表一般都是根據手機號以及郵箱查詢，那麼就不適合在name上建立索引，索引字段的選擇一般參考以下幾個因素：

  • 區分度要高，例如sex與phone兩列相比，sex的區分度太低，此時及時條件走了索引，仍然會命中大量數據，然後進一步篩選。
  • 有序，索引具有有序性，每次寫入或者刪除數據時，會涉及B+樹的調整，因此當表的寫入量很大時，需要考慮這一步的消耗
  • 長度合適，索引可以認為是全部放到內存中的，因此合適的大小能夠減少大量的內存消耗，針對超長字符串可以選擇指定索引前綴長度等方式進行優化。

編寫走索引的SQL

如何編寫走索引的SQL，該問題需要了解MySQL針對索引所定製的一些規則，比如最左前綴匹配規則，索引失效場景規則，以及一些常見查詢索引解決方案。該部分情況比較複雜，因此不展開詳細說，了解大概規則後，最佳做法是使用explan關鍵詞進行分析，針對分析結果進行SQL或者索引的調整。

還有一些可以很好的從應用層解決的問題，比如一張用戶黑名單表,使用 身份證 + 手機號作為唯一約束,那麼索引該怎麼設計才能提高查詢效率？這種場景設計比較多，比如直接建立自增id主鍵，然後身份證+手機號聯合索引，如果更新不頻繁還可以通過md5方式，取一個hash後作為主鍵，然而這種場景下應用層完全可以使用布隆過濾器進行預先過濾，布隆過濾器中不存在的則一定不存在，大大會減少DB查詢量，因此設計索引時需要關注具體業務場景，不能只看索引。

三星索引法

三星索引法是《數據庫索引設計與優化》提出的一種建立合理索引的措施，具體如下：

  • 一星索引：取出所有等值謂詞的列，作為索引的開頭，目的是減少掃描數據數量
  • 二星索引：將order by中的列加入到索引當中，目的是避免文件排序增加磁盤IO
  • 三星索引：將查詢語句剩餘的列加入到索引當中，目的是走覆蓋索引，避免磁盤IO 以select id, name, age from user where name='link' order by age desc為例，索引(name, age, id) 是一個三星索引，而索引(name) 不是一個三星索引，然而在業務中往往索引(name)就能夠滿足該查詢，從而減少內存消耗，因此三星索引可以作為一個索引好不好的參考，而具體業務場景下沒必要過分追求三星索引。

參考

為什麼 MySQL 使用 B+ 樹 · Why』s THE Design? MySQL 索引設計概要

VirMach 便宜 VPS

QNews

QNews