不管是啥業務，最終數據都要落地，數據庫這一環是肯定少不了的。隨著業務發展，并發越來越高，數據庫很容易成為整個鏈路的短板。這也是大廠面試中比較常被問到的。而調優的第一步，都是從sql語句、索引入手。先得保證單個數據庫執行沒問題，才會有更高層次的分庫分表、彈性、容災等等。

Part1為什么Kafka不需要我們關心索引，而Mysql卻需要？

Kafka 和 MySQL 雖然最終數據都是落磁盤，但是兩者在用途和數據查詢方式上有著很大的差異，所以決定了數據的存儲結構不同，進而決定了索引的復雜程度。

我們先看下kafka的存儲結構：

由于 kafka 的定位是進行穩定的高性能數據讀寫。所以對磁盤來說，是采用順序讀寫的方式，落在了一些 .log 文件中，并以基準偏移量補0命名。

為了實現高速查找 kafka 創建了稀疏索引文件(隔一段數據創建一條，而非全量)，即index文件。其中維護消息的 offset 和 .log文件的物理位置。通過二分查找快速定位log文件并順序掃描找到目標。

所以，kafka的索引組織方式是相對簡單、方案相對固定，但MySQL卻不行。Mysql是關系型數據庫，是為了支持復雜的業務數據查詢而創建的，查詢方式、數據獲取需求多種多樣，要求MySQL具備更加復雜的索引機制來加速復雜業務查詢場景。

Part2MySQL數據怎么被組織[1][2]

以InnoDB存儲引擎來看mysql數據存儲：

參考了三本資料，基本把最重要的部分都概括了

數據被分了多個邏輯層：行->頁->區塊->段->表空間。

我們知道，InnoDB存儲引擎表是Index organized的（數據即索引，索引即數據），他們都維護在一個B+樹上，數據段就是葉子節點，索引段就是非葉子節點；

而我們劃分的段、區塊 其實都是為了利用操作系統的資源（比如每次從磁盤加載到內存的數據大小按區塊來約定等等`）來達到更高效讀寫的目的，邏輯劃分的。

其中頁是MySQL和磁盤交互的最小單位，怎么從頁找到行，怎么聚合到塊、到段再到空間呢。

1數據記錄最小單位-- 行

從上面總圖中摘出一條記錄的結構如下圖：

我們可以看到，記錄頭中除了行號，還有下一條記錄的標識next_record，所以，我們可以通過next_record將記錄連接起來，以單向鏈表的形式，所以這就決定了，當我們在記錄鏈中尋找某記錄時，只能順序遍歷，這也決定了一條數據鏈不會太長。

但一個頁默認是16K，加上行溢出等處理，一頁最多存放7992行記錄，這么多的記錄，必須順序遍歷么？當然不需要，讓我看看頁是怎么組織記錄行的。

2與磁盤最小交互單位-- 頁

作為與磁盤交互的最小單位，是用來存放實際數據的(頁類型是b-tree Node存真實數據,還有其他類型如索引目錄頁等用來加速查詢)從上面的大圖中可以大致看到一個頁的整體結構：

讓我們來看幾個關鍵的字段參數：

Page Directory 決定著記錄項在頁內的查詢效率

為了更快速的查詢，頁目錄存儲的本頁的數據目錄(槽)，包含最大最小記錄和 分組數據鏈的最大記錄的偏移量。方便使用二分法快速查找數據，不需要再從最小值開始遍歷，如下圖：

圖片來自《從根兒上理解 MySQL》

File Header決定頁和頁之間怎樣關聯

記錄本頁的一些通用信息，主要包含< 本頁頁號、上一頁、下一頁、頁類型、所屬表空間等等>。

通過頁號來找到本頁、通過上下頁進行雙向鏈表串聯、通過類型判斷是索引頁還是數據頁。。。

圖片來自《從根兒上理解 MySQL》

此字段決定了頁和頁之間可以很方便的通過上述屬性進行關聯。

Page Header決定頁的層級

存儲的本頁的數據信息，主要包含**<** 本頁記錄數量、在B+樹中的層級、歸屬的索引ID、插入方向、最大事務ID等等 >。

有了頁面的數據組織概念，那么，怎么利用這些結構來實現的數據快速查詢呢？

Part3索引的演進思路

從上面的數據組織的知識里可以看到，行記錄之間串聯成單向鏈表，在每頁中都按分組方式分布在此頁的最小記錄和最大記錄之間。

頁面之間通過上一頁、下一頁的指針，串聯成雙向鏈表，在磁盤中進行存儲，如下圖：

那么，要查詢一條記錄，可以怎么做？

3原始：順序方式

如上圖所示的數據串聯方式，自然的提供了一種查詢方式：即按主鍵順序遍歷每頁和頁中的記錄行。

但是，這樣的查詢方式，除了在頁內有二分優化，再無效率可言。怎么辦？

尋求改進：既然頁內的行記錄可以分組入槽，那數據頁之間為什么不行呢?

4改進：目錄方式

我們將頁向上聚蔟，構建一個頁號目錄，先在目錄中查找，再到對應頁中查找，就比順序查找要快很多了。

尋求改進：這樣的方式所需大量連續空間 + 目錄會隨數據變動而頻繁變動，怎么辦？

5演進：主鍵B+樹方式

其實，在敘述行記錄結構的時候，我們就看到，數據行的結構中，除了實際業務數據外，還有很多額外空間。

如record_type用來表示該記錄的類型是數據還是索引。正是這些額外的空間的設計，給InnoDB以更加適合的方式組織索引提供了支持：

圖片來自《從根兒上理解 MySQL》

這就是一棵B+樹，頁節點有層級區分，頁中的行記錄有類型區分。

業務數據都包含在葉子節點中，目錄數據都包含在其他非葉節點中。

這樣組織方式的優勢，是允許足夠少的層級容納足夠多的數據項(可以簡單的假設每一頁的數據項大小來預估)。

而這個索引方式就是我們常說的聚蔟索引。即使用主鍵值進行記錄和頁的排序，且葉子節點含有全部用戶數據。

尋求改進：如果我想用其他列來查詢，怎么辦？

6擴展：二級索引、聯合索引

二級索引

比如用戶需要根據某一列（a列）的值來查詢，那就再重新創建一個B+樹。此索引樹和聚蔟索引樹的差別在于，索引節點是以a列的值為目錄，且葉子節點只包含a列的值和主鍵兩個值。

如果用戶需要查詢除c列以外的更多信息，則需要拿主鍵ID再去聚蔟索引查一次，也叫回表。

聯合索引

二級索引是除主鍵外的單列索引，而聯合索引則是多個列共同排序。假設用戶需要用a 、b 兩個列進行有序查詢，那內在含義是，在a列值相同的情況下，再判斷b的值。

同二級索引一樣，InnoDB也需要再創建一棵B+樹，且目錄項的排序按先a，后b進行排序串聯，葉子節點的數據項只包含 a 、b、主鍵三個值。

Part4生產實踐之觸類旁通

7美團定時任務索引優化[3]

系統需要定時的撈取特定時間段內特定狀態、特定類型、特定操作者的任務進行定時處理。 

select * from task   
where  
   status=x  
   and operator_id=xxxx   
   and operate_time>xxxxxxxx01  
   and operate_time<xxxxxxxx99  
   and type=x; 

開發發現此sql運行的越來越慢，希望給每個字段加二級索引，被優化師叫停，而是考慮的該表所有查詢方式后，創建了一個聯合索引： 

(status,operator_id,type,operate_time) 

為什么不建多個的二級索引？為什么范圍查詢的字段要放在最后？

分析：

（1）從前面的原理部分我們知道，索引是要占內存的，不是越多越好，能起作用就行。

（2）用于范圍匹配的字段的索引位置要嚴謹。因為創建索引的時候，根據索引字段的順序來進行排序，如果把time字段放在type字段前面建索引，在查詢時，因為time是一個范圍值，那么多個time值延續到type字段，整體是無序的，無法用到type索引。

8螞蟻分布式主事務表的索引運用

螞蟻的分布式事務中的主事務表起到了維護整體事務狀態的作用，其中包含了整體事務狀態、操作時間等字段。而在業務支付發生異常，且實時回滾失敗時，需要事務恢復系統從遠程撈取前1分鐘的異常數據，并撈取對應的分支記錄表發起異步回滾。

考慮查詢效率，查詢sql會限定業務發生時間在[前10分鐘，前1分鐘]，是有范圍查詢，所以，針對其他字段，業務時間的索引順序需要置于聯合索引的最后。此操作的原理和上一部分美團定時任務的原理是一樣的。

9阿里開發手冊中幾條典型的規范[4]

【強制】 在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度。

原理關聯：字段越長，索引占內存越多，只要其長度可以保證區分度即可

【強制】 字符搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決。

原理關聯：左模糊的字段不是有序的，無法用到索引

【推薦】 如果有 order by 的場景，請注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現 file_sort 的情況，影響查詢性能。

原理關聯：如果條件中有范圍查詢，則后續字段是無序的，order by時無法用到索引

【推薦】 建組合索引的時候，區分度最高的在最左邊。

原理關聯：區分度越高，查詢路徑越短，效率越高

等等，參見阿里Java開發手冊

Part5總結

本文從MySQL的存儲結構、索引的設計思路演進、美團阿里大型系統索引使用等幾個部分，闡述了索引的原理和對業務系統的重要作用。

當然，對于高并發下的數據庫的優化遠不止索引優化這一個方面，本文只從索引這一個點出發，讓大家對其優化原理和優化方向有一個大致的概念，在業務發展遇到數據庫瓶頸時能有所幫助。