5G時代，如何徹底搞定海量資料庫的設計與實踐

5G時代，業務資料越來越豐富，業務使用MySQL資料庫作為後臺儲存，儲存引擎使用InnoDB，會帶來哪些挑戰？如何針對公司業務特點及MySQL資料庫特性，制定若干資料庫使用規範供一線RD在設計業務時參考部分內容要求強制執行。本文從介紹MySQL相關關鍵基礎架構，並結合實際案例介紹表和索引的設計技巧，並對規範中重點內容做詳細解讀。

一、基礎知識

1.1 InnoDB記錄儲存方式

大家都知道在InnoDB儲存引擎中記錄是按主鍵順序儲存，並且依靠這個特性為表建立了主鍵聚簇索引。

InnoDB是如何實現記錄“順序儲存”的呢？首先要知道“順序”分頁內順序和頁間順序，頁為InnoDB內外存交換的基本單位。

頁間順序：磁碟檔案中頁與頁之間使用雙向連結串列連線，頁間有可能是物理有序。大多數情況是邏輯上的有序；

頁內順序：頁內各記錄使用單項鍊表把記錄連線起來，所以頁內是邏輯有序，配合slot資料結構實現頁內接近二分查詢的查詢效率。

圖1 為InnoDB頁內空間分佈：

圖1  InnoDB頁內空間釋出

根據以上特點，我們來分析下使用不同的主鍵對儲存會造成哪些影響：

自增主鍵：主鍵值遞增，資料是順序插入的，所以在頁內資料物理連續，寫滿一頁後在順序分配下一頁。在沒有刪除操作的情況下，整個表的記錄在磁碟檔案中都是按照寫入順序連續儲存的。其中儲存方式磁碟利用率非常高，且隨機IO很低。插入效率相當高。

業務主鍵：比如使用者表使用“uid”做主鍵，商品表使用“infoId”做主鍵，這種有意義的主鍵，我們稱為業務主鍵。很明顯，業務主鍵不但無法做到記錄物理連續而且在插入資料時還可能造成頁的分裂，從而導致頁內碎片，例如如果一個頁空間已滿，儲存主鍵值0~99，100條資料，如果要插入55這條記錄，頁內已經放不下，需要分裂成兩個頁才能完成插入操作，而分裂後的兩個頁很難被寫滿，會造成頁內碎片，所以業務主鍵在寫入效能和磁碟利用率上都不如自增主鍵。

透過上面的分析，我們是不是可以得出結論：使用自增主鍵一定好呢？在我們分析完InnoDB的索引以前，現在下結論還有些早。

1.2 主鍵索引

InnoDB會自動在表的主鍵上建立索引，資料結構使用B+ Tree。根據儲存上的特點主鍵索引也被稱為聚簇索引。聚簇索引的索引結構和實際資料是儲存在一起的，B+ Tree葉子節點儲存的就是實際的記錄，如圖2所示：

圖2  B+ Tree儲存結構

1.3 非主鍵索引

既然記錄儲存在主鍵索引結構中，那麼在其他列建立的索引是如何找到記錄的呢？我們可以很自然的想到，非主鍵列上的索引可以先透過自身索引結構查詢到主鍵值，然後在用主鍵值在聚簇索引上找到相應的記錄。InnoDB就是這麼做的，所以我們也稱非主鍵列上的索引為二級索引(因為一次查詢需要查詢兩個索引樹)。

二級索引有以下特點：

1、除了主鍵索引以外的索引；

2、索引結構葉子節點中的Data是主鍵值；

3、一次查詢需要查詢自身和主鍵兩個索引；

1.4 聯合索引

聯合索引也叫多列索引，索引結構的key包含多個欄位，排序時先第一列比較，如果相同再按第二列比較，以此類推。聯合索引結構圖如圖3所示：

圖3 聯合索引結構圖

聯合索引上的查詢要滿足以下特點：

1、key按照最左開始查詢，否則無法使用索引；

2、跳過中間列，會導致後面的列不能使用索引；

3、某列使用範圍查詢時，後面的列不能使用索引。

根據字首索引特性，聯合索引(a,b,c)，可以滿足(a)，(a,b)，(a,b,c)三種查詢。

二、小結

瞭解了InnoDB的索引後，我們再來分析自增主鍵和業務主鍵優缺點：

自增主鍵：寫入、查詢效率和磁碟利用率都高，但每次查詢都需要兩級索引，因為線上業務不會有直接使用主鍵列的查詢。

業務主鍵：寫入、查詢效率和磁碟利用率都低，但可以使用一級索引，依賴覆蓋索引的特性，某些情況下在非主鍵索引上也可以實現1次索引完成查詢（後面的案例中會介紹）。

自增主鍵相對業務主鍵在IO效率上優勢在SSD硬碟下幾乎可以忽略，而在業務查詢效能上業務主鍵有明顯優勢，所以在資料庫中使用的都是業務主鍵。

三、業務表設計

針對MyQL資料庫特性結合公司業務特點制定了一系列資料庫使用規範，可以有效的指導一線RD在專案開發過程中資料庫表和索引的設計工作。下面介紹業務中表和索引的重點設計原則以及兩個實際案例。

3.1 表設計原則

主鍵選擇：前面我們已經對比分析過業務主鍵和自增主鍵的優缺點，結論是業務主鍵更符合業務的查詢需求，而網際網路業務大多都符合讀多寫少的特性，所以所有線上業務都使用業務主鍵；

索引個數：由於過多的索引會造成索引檔案過大，所以要求索引數不多於5個；

列型別選擇：通常越小、越簡單越好，例如：BOOL欄位統一使用TINYINT，列舉欄位統一使用TINYINT，交易金額統一使用LONG。因為BOOL和列舉型別使用TINYINT可以很方便的擴充套件，針對金額資料，雖然InnoDB提供了支援精確計算的DECIMAL型別，但DECIMAL是儲存型別不是資料型別，不支援CPU原生計算，效率會低一些，所以我們簡單處理將小數轉為整數用LONG儲存。

分表策略：首先要明確資料庫出現效能問題一般在資料量到達一定程度後！所以要求我們提前做好預估，不要等需要拆分時再拆，一般把表的資料量控制在千萬級別；常用分表策略有兩種：按Key取模，讀寫均勻；按時間切分，冷熱資料明確；

3.2 實際案例

案例一：使用者表設計

使用者表包含欄位：uid，nickname，mobile，addr，image…..，switch；

uid為主鍵，業務上有按uid和mobile兩種查詢需求，所以要在moblie上建立索引。

switch列比較特殊，型別為BIGINT，用來儲存使用者的BOOL型別的屬性，每一位可以儲存使用者的一個屬性，例如我們用第一位儲存是否接收推送，第二位儲存是否儲存離線訊息等等。

這種設計有很高的擴充套件性(因為BIGINT有64位，可以儲存64個狀態，一般情況很難用滿)，但是同時也帶來一些問題，switch有很高的查詢頻率。由於InnoDB是行儲存，要找查詢switch需要把正行資料取出來。

這對上述場景，我們可以表設計上可以做哪些最佳化呢？常用的方案是把表垂直查分，這種很常見我們不做過多討論。

還有一種方案我們可以利用InnoDB覆蓋索引的特性，在uid和switch兩列上建立聯合索引，這樣在二級索引上包含uid和switch兩列的值，這樣用uid查詢switch時，只透過二級所以就能找到switch，不需要訪問記錄，甚至不需要到二級索引的葉子節點就可以找到要查詢的switch值，查詢效率非常高。

另外有一點需要考慮，可以想象switch的變更也是相當頻繁的，switch值得改變會導致聯合索引的變更嗎(這裡的變更指索引節點分裂或順序調整)？

答案是不會！因為聯合索引的第一列uid是唯一且不會變的，所以uid就已經決定了索引的順序，switch列的改變只會改變索引節點上第二個key的值，不會改變索引結構。

五、總結

1、  自增主鍵效能不一定高，需要結合實際業務場景做分析；

2、  大多數場景資料型別選擇上儘量使用簡單的型別；

3、  索引不是越多越好，太多的索引會導致過大的索引檔案；

4、  如果要查詢的資料可以在索引檔案中找到，儲存引擎就不會查詢主鍵索引訪問實際記錄。

更多免費資料及影片