Mysql索引結合explain分析範例

2022-02-18 13:05:03

簡介

Mysql 在我們專案中使用是非常廣的，當我們資料量大的時候，就需要考慮建立索引了，我感覺這也是一種以空間換時間的方式；在我們查詢的時候，通過使用索引來提高速度；那麼，我們在使用的過程中，怎麼判定有沒有走索引呢？有一個explain語句來進行分析，根據阿里的Java程式設計規範，至少型別要提升到range;我那時候就在想為什麼要提升到range呢？後來結合Mysql的索引終於知道explain和Mysql底層B+樹的對應關係；注：以下內容都是基於InnoDB引擎；

1.索引分類

索引分為聚簇索引和非聚簇索引；那麼，我們先來探討一下聚簇索引；

聚簇索引

那麼在InnoDB中，如果沒有定義主鍵，那麼會怎麼辦？

首先，他會看你有沒有定義唯一鍵；如果有唯一鍵，那麼就會把這個唯一鍵當作主鍵來建立索引；如果連唯一鍵也沒有的話，就會預設建立一個隱藏列 row_id 通過這個row_id來建立索引；所以，由於有這個機制（這個機制是為了配合普通索引的），使用Innodb的話，還是，需要有一個主鍵最好是遞增主鍵；不用白不用；（還有，就是主鍵儘量小一點，如果像UUID一樣，問題很多第一：主鍵被其他普通索引葉子使用，佔用空間第二：插入的時候，需要隨機存取I/O，並且，容易導致頁分裂）

聚簇索引的結構 假設，我們主鍵遞增，它的結構示意圖如下：

這是一個簡單示意圖：一頁資料能存16k 所以，第一層節點資料肯定比這多多了；但是，我們可以得到一個結論：對於非葉子節點存的是主鍵 + 指標對於葉子節點存的是主鍵 + 真實的資料；

普通索引的結構 假設以 create index idx_t1_bcd on t1(b, c, d)來建立索引; 其示意圖如下：

對於普通的索引來說，採用的也是B+樹結構，但是：對於非葉子節點來說存的是建立索引的欄位（b,c,d） + 指向資料指標對於葉子節點來說存的是建立索引的欄位(b,c,d) + 主鍵的指標；

這裡由於存的是主鍵的指標出現會導致回表：普通索引為什麼需要這麼設計（為什麼不存資料）？個人理解原因如下：

如果，普通索引也要存下資料的話，那麼需要記憶體空間太大了；
如果，普通索引也存資料的話，當發生修改的話，就需要修改全部的資料；

所以，很明顯這個普通索引是比聚簇索引佔用空間小很多的，這個特性在count(*) 的時候會用到；

那麼，為了解決回表問題，覆蓋索引來進行解決；

為什麼選擇B+樹

剛剛已經介紹了Mysql 聚簇索引和普通索引的特徵；那麼，現在問題來了？為什麼要選擇B+樹呢？

原因：和Mysql的特性所致：針對磁碟來說 IO是它的一大瓶頸，索引的出現是為了快速找到對應的資料，所以說：IO越少效率越好，（就是磁碟頁載入到記憶體次數越少越好）那麼，為什麼使用B+樹就會載入的少呢？

舉例說明：我們假設待儲存資料一行大小是1k; 所以，我們一頁可以存16行資料；假設我們的主鍵id為bigint型別，長度為8位元組（如果是int 4位元組），而指標大小為6位元組；一頁為 16k 16 * 1024 /14 = 1170，所以，我們非節點頁，可以存放1170個主鍵 + 指標；綜上：如果是2層節點的B+樹；可以存的資料是 1170 * 16 = 18720行資料；

那麼，如果是三層的B+樹呢？這時候第一頁非葉子節點，可以存1170個主鍵+ 指標（指向的是非葉子節點）第二頁非葉子節點，也可以存1170個主鍵 + 指標（指向葉子節點）；第三層每一頁可以存16行資料；所以，總共可以存 1170 * 1170 * 16行資料；（這已經是千萬條資料了）而且，第一層或者第二層非葉子節點一般是是快取在記憶體中的，其實千萬條資料找一或兩次就可以了；其他的以此類推；如果，採用B樹，因為它的非葉子節點中也是存資料的層級會高過B+樹；

原因2：使用B+樹結構時，因為資料是存在葉子節點中，所以，對於存取查詢找到第一個值，就可以通過葉子節點的雙向連結串列進行遍歷查詢；而如果B樹，就需要採用中序遍歷；

綜上： 1.B+樹層級會比較低 2.對範圍查詢效率比較高；

explain

介紹完Mysql索引結構，我們可以來講解explain了；

這是explain的欄位，我記得我剛剛開始的時候是怎麼都記不住；後來和索引建立聯絡以後就記住了；
id :表示表的載入順序，id 越大越先查詢用於大表驅動小表；如果相關，就從上到下執行；
type: 查詢使用了那種型別從最好到最差 system > const > eq_ref > ref > range > index > ALL;
const：只匹配一次出行在主鍵索引或者唯一索引
ref : 非唯一性索引掃描，返回匹配某個單個值的所在行；就是通過一個where 條件找到一條或多條資料；
range：範圍查詢時使用到；最低標準了；
index: 只遍歷索引樹比全表掃描好一點點因為通常來說索引檔案比資料檔案小；
all : 全表掃描

row :通過採用函數推算出來的要讀的條數，涉及索引的選擇，正常情況下誤差不會很大； extra: Using filesort 檔案排序需要對找出來的資料進行外部排序，不能使用表內索引完成排序；慢需要優化
Using temporary : 使用了臨時表來儲存中間結果更慢需要優化
Using index : 使用了覆蓋索引 Using where 使用了Where 這兩個不用優化
如果，對Using filesort Using temporary為什麼慢感興趣的同學，可以檢視我的另一篇文章 Using filesort Using temporary為什麼這麼慢

從索引樹的角度分析為什麼ref>range>index

比如說：where key = 4 這個時候，它首先在第一頁進行查詢（這裡它對連結串列處理過，引入了陣列，為了查詢快速，使用的是二分查詢）然後，找到資料指標是0005，所以就去0005資料頁中，進行查詢，（在頁中查詢也是使用二分查詢）找到了第一條資料key = 4,然後，只要找下一條，看看是不是key != 4 如果，不等於4，那麼，就找完了；這是ref級別的過程；
然後，where key >= 4;同理，先找到key = 4，然後，因為大於4，所以，就按照葉子節點中的指標向後找，找到底，這個是type = range 的情況；
至於 type = index 其實就是對整個索引樹進行遍歷，比如說：我建立了普通索引 user(姓名，身份證號) 我想把所有的身份證號找出來，這個時候，就可能使用基於索引樹的全表掃描了，因為，索引樹相對來說內容小一點，如果，全部掃描的話，記憶體中沒有對應資料頁還得都去找出來；通過，這樣推理，可以感受到 ref > range > index；

最左字首原則理解 我們都知道有最左字首原則，那麼，為什麼會有這個原則呢？