MySQL調優篇 | 索引知識解讀（2）

前言

經常有一些朋友向我諮詢，如何寫出高效的SQL，這不是三言兩語能說得清的，索性認真來寫一下，增刪查改方面的知識我不再贅述，如果有基礎薄弱的同學，可以好好的補一補再來看。

以MySQL為基礎，MySQL調優篇內容主要包含MySQL邏輯架構、索引知識、表關聯演算法、explain執行計劃解讀及SQL調優實戰等。

文章受眾主要為兩類人：

第一類人是工作中不可避免的會接觸到MySQL的人，比如說一些專案人員、開發人員、測試人員等。

第二類人是專職DBA。

其實不管是專職的還是非專職的，就我接觸到的情況而言，很多DBA平時維護MySQL看起來沒什麼問題，但其實沒有很好的理論支撐，知其然而不知其所以然，解釋一個簡單的問題就能問倒一大部分的人。

比如說：MySQL的邏輯架構，分析當前業務架構優缺點？SQL工作原理是什麼樣的？

而且很多公司招聘面試的時候，考驗的也是背後的原理居多，基本上沒有機試。面試官問一個問題，即便你會解決但就是說不出原理，那麼你肯定要不了高薪。

理論+實戰=高薪

文章能夠讓大家有所收穫、有所借鑑那是最好的。

【索引知識】

面試的時候，提起索引不要再拿目錄類比索引的優點了，這樣顯的很菜。一句話： 索引是資料結構，是一個排好序且快速查詢的資料結構。

1、索引的本質

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構。提取句子主幹，就可以得到索引的本質：索引是資料結構。

查詢是資料庫的最主要功能之一，速度當然是越快越好，因此資料庫系統的設計者會從查詢演算法的角度進行最佳化。常見的查詢演算法有 順序查詢（linear search）、二分查詢（binary search）、二叉樹查詢（binary tree search）等。

在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高/級查詢演算法。

這種資料結構，就是索引。

看一個例子：

 

 

圖1展示了一種可能的索引方式。

左邊是資料表，一共有兩列七條記錄，最左邊的是資料記錄的實體地址（注意邏輯上相鄰的記錄在磁碟上也並不是一定物理相鄰的）。

為了加快Col2的查詢，可以維護一個右邊所示的二叉查詢樹，每個節點分別包含索引鍵值和一個指向對應資料記錄實體地址的指標，這樣就可以運用二叉查詢在O(log2n)的複雜度內獲取到相應資料。

2、索引結構

MySQL 索引一般是雜湊表或 B+ 樹，常用的 InnoDB 引擎預設使用的是 B+ 樹來作為索引的資料結構。

2.1為什麼是B+樹

B+樹是B樹的變種，是基於B樹來改進的。為什麼B+樹會比B樹更加優秀呢？

B樹：有序陣列+平衡多叉樹； 

B+樹：有序陣列連結串列+平衡多叉樹；

B+樹的關鍵字全部存放在葉子節點中，非葉子節點用來做索引，而葉子節點中有一個指標指向一下個葉子節點。

B+ 樹查詢過程

 

 

磁碟塊 1 中儲存 17 和 35 資料項，還有 P1、P2、P3 指標，P1 表示資料項小於 17 的磁碟塊，P2 表示資料項在 17 和 35 之間的資料項，P3 表示資料項大於 35 的資料項。非葉子節點不儲存資料，只儲存指引搜尋方向的資料項。

我們知道每次 IO 讀取一個資料頁的大小，也就是一個磁碟塊。

假設我們要查詢 29 這個資料項，首先進行第一次 IO 將磁碟塊 1 讀進記憶體，發現17 < 29 < 35，然後選用 P2 指標進行第二次 IO 將磁碟塊 3 讀進記憶體，發現26 < 29 < 30，然後選用 P2 指標將磁碟塊 8 讀進記憶體，在記憶體中做二分查詢，找到 29，結束查詢。

透過分析查詢過程，我們可以知道 IO 次數和 B+ 樹的高度成正比。H 為樹的高度，M 為每個磁碟塊的資料項個數，N 為資料項總數。

從下面的公式可以看出如果資料量N一定，M越大相應的H越小。

 

 

M 等於磁碟塊的大小除以資料項大小，由於磁碟塊大小一般是固定的，所以減小資料項大小才能使得 M 更大從而讓樹更矮胖。這也是為什麼 B+ 樹把真實資料放在葉子節點而不是非葉子節點的原因。

如果真實資料放在非葉子結點，磁碟塊儲存的資料項會大幅度減少，樹就會增高相應查詢資料時的 IO 次數就會變多。

3、索引實現

在MySQL中，不同儲存引擎對索引的實現方式是不同的，本文主要討論MyISAM和InnoDB兩個儲存引擎的索引實現方式。

3.1、MyISAM索引實現

MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是資料記錄的地址。

下圖是MyISAM索引的原理圖：

 

 

在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯/一的，而輔助索引的key可以重複。

MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的聚集索引區分。

3.2、InnoDB索引實現

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

1、InnoDB的資料檔案本身就是索引檔案。2、InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。
這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。

 

 

可以看到葉節點包含了完整的資料記錄。這種索引叫做聚集索引。

瞭解不同儲存引擎的索引實現方式對於正確使用和最佳化索引都非常有幫助，例如知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。

4、如何使用索引

說了那麼多原理，總結一下索引的優缺點和使用時機。

4.1、優點

• 提高資料檢索的效率，降低資料庫的IO成本；
• 透過索引列對資料進行排序，降低資料排序的成本，降低了CPU的消耗；

所以記住，索引功能是：搜尋+排序

4.2、缺點

• 索引提升查詢效率的同時也會降低更新的效率，更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案，每次更新新增了索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引資訊；
• 實際上索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄，所以索引列也是要佔用空間的。

4.3、索引使用時機

• 主鍵自動建立唯/一索引；
• 頻繁作為查詢條件的欄位應該建立索引；
• 查詢中與其它表關聯的欄位，外來鍵關係建立索引；
• 單鍵/組合索引的選擇問題， 組合索引價效比更高；
• 查詢中排序的欄位，如order by create_time，排序欄位若透過索引去訪問將大大提高排序速度；
• 查詢中統計或者分組欄位；

【總結】

索引調優是一項技術活，沒有理論不行，僅靠理論也不行，實際情況千變萬化，結合實踐中不斷的實驗和摸索，從而真正達到高效使用MySQL索引的目的。

以上就是全部內容了，下一篇講表關聯演算法相關的知識，希望對大家的學習或者工作具有一定的參考價值。