互聯(lián)網(wǎng)業(yè)務往往使用MySQL數(shù)據(jù)庫作為后臺存儲，存儲引擎使用InnoDB。我們針對互聯(lián)網(wǎng)自身業(yè)務特點及MySQL數(shù)據(jù)庫特性，講述在具體業(yè)務場景中如何設(shè)計表和分表。本文從介紹MySQL相關(guān)基礎(chǔ)架構(gòu)設(shè)計入手，并結(jié)合企業(yè)實際案例介紹分表和索引的設(shè)計實戰(zhàn)技巧。

-? ? 01、什么是InnoDB記錄存儲方式？??? ?-

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

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

頁間順序：磁盤文件中頁與頁之間使用雙向鏈表連接，頁間有可能是物理有序。大多數(shù)情況是邏輯上的有序；

頁內(nèi)順序：頁內(nèi)各記錄使用單項鏈表把記錄連接起來，所以頁內(nèi)是邏輯有序，配合slot數(shù)據(jù)結(jié)構(gòu)實現(xiàn)頁內(nèi)接近二分查找的查詢效率。

圖1為InnoDB頁內(nèi)空間分布：

圖1?Page Header

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

自增主鍵：主鍵值遞增，數(shù)據(jù)是順序插入的，所以在頁內(nèi)數(shù)據(jù)物理連續(xù)，寫滿一頁后在順序分配下一頁。在沒有刪除操作的情況下，整個表的記錄在磁盤文件中都是按照寫入順序連續(xù)存儲的。這中存儲方式磁盤利用率非常高，且隨機IO很低。插入效率相當高。

業(yè)務主鍵：比如用戶表使用uid做主鍵，商品表使用infoId做主鍵，這種有意義的主鍵，我們稱為業(yè)務主鍵。很明顯，業(yè)務主鍵不但無法做到記錄物理連續(xù)而且在插入數(shù)據(jù)時還可能造成頁的分裂，從而導致頁內(nèi)碎片，例如如果一個頁空間已滿，存儲主鍵值0~99，100條數(shù)據(jù)，如果要插入55這條記錄，頁內(nèi)已經(jīng)放不下，需要分裂成兩個頁才能完成插入操作，而分裂后的兩個頁很難被寫滿，會造成頁內(nèi)碎片，所以業(yè)務主鍵在寫入性能和磁盤利用率上都不如自增主鍵。

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

-? ? ? 02、什么是主鍵索引？??? ?-

InnoDB會自動在表的主鍵上創(chuàng)建索引，數(shù)據(jù)結(jié)構(gòu)使用B+Tree。根據(jù)存儲上的特點主鍵索引也被稱為聚簇索引。聚簇索引的索引結(jié)構(gòu)和實際數(shù)據(jù)是存儲在一起的，B+Tree葉子節(jié)點存儲的就是實際的記錄，如圖2所示：

圖2?聚簇索引

-? ? ? 03、什么是非主鍵索引？? ? ?-

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

二級索引有以下特點：

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

2、索引結(jié)構(gòu)葉子節(jié)點中的Data是主鍵值；

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

-? ? ? 04、什么是聯(lián)合索引？??? ?-

聯(lián)合索引也叫多列索引，索引結(jié)構(gòu)的key包含多個字段，排序時先第一列比較，如果相同再按第二列比較，以此類推。聯(lián)合索引結(jié)構(gòu)圖如圖3所示：

圖3?聯(lián)合索引

聯(lián)合索引上的查詢要滿足以下特點：

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

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

3、某列使用范圍查詢是，后面的列不能使用索引。

根據(jù)前綴索引特性，聯(lián)合索引(a,b,c)，可以滿足(a)，(a,b)，(a,b,c)三種查詢。

-? ? ? 05、小結(jié)? ? -

了解了InnoDB的索引后，我們再來分析自增主鍵和業(yè)務主鍵優(yōu)缺點：

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

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

自增主鍵相對業(yè)務主鍵在IO效率上優(yōu)勢在SSD硬盤下幾乎可以忽略，而在業(yè)務查詢性能上業(yè)務主鍵有明顯優(yōu)勢，所以在業(yè)務數(shù)據(jù)庫中，我們使用的都是業(yè)務主鍵。

-? ? ? 06、電商業(yè)務分表設(shè)計與實踐? ? -

針對MyQL數(shù)據(jù)庫特性結(jié)合自身業(yè)務特點制定了一系列數(shù)據(jù)庫使用規(guī)范，可以有效的指導一線RD在項目開發(fā)過程中數(shù)據(jù)庫表和索引的設(shè)計工作。下面介紹電商業(yè)務中表和索引的重點設(shè)計原則以及兩個實際案例

1、表設(shè)計原則

主鍵選擇：前面我們已經(jīng)對比分析過業(yè)務主鍵和自增主鍵的優(yōu)缺點，結(jié)論是業(yè)務主鍵更符合業(yè)務的查詢需求，而互聯(lián)網(wǎng)業(yè)務大多都符合讀多寫少的特性，所以所有線上業(yè)務都使用業(yè)務主鍵；

索引個數(shù)：由于過多的索引會造成索引文件過大，所以要求索引數(shù)不多于5個；

列類型選擇：通常越小、越簡單越好，例如：BOOL字段統(tǒng)一使用TINYINT，枚舉字段統(tǒng)一使用TINYINT，交易金額統(tǒng)一使用LONG。因為BOOL和枚舉類型使用TINYINT可以很方便的擴展，針對金額數(shù)據(jù)，雖然InnoDB提供了支持精確計算的DECIMAL類型，但DECIMAL是存儲類型不是數(shù)據(jù)類型，不支持CPU原聲計算，效率會低一些，所以我們簡單處理將小數(shù)轉(zhuǎn)換為整數(shù)用LONG存儲。

分表策略：首先要明確數(shù)據(jù)庫出現(xiàn)性能問題一般在數(shù)據(jù)量到達一定程度后！所以要求我們提前做好預估，不要等需要拆分時再拆，一般把表的數(shù)據(jù)量控制在千萬級別；常用分表策略有兩種：按key取模，讀寫均勻；按時間分，冷熱數(shù)據(jù)明確；

2、實際案例

案例一：用戶表設(shè)計

用戶表包含字段：uid，nickname，mobile，addr，image…..，switch；

uid為主鍵，業(yè)務上有按uid和mobile兩種查詢需求，所以要在moblie上創(chuàng)建索引。

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

這種設(shè)計有很高的擴展性(因為BIGINT有64位，可以保存64個狀態(tài)，一般情況很難用滿)，但是同時也帶來一些問題，switch有很高的查詢頻率。由于InnoDB是行存儲，要找查詢switch需要把正行數(shù)據(jù)取出來。

這對上述場景，我們在表設(shè)計上可以做哪些優(yōu)化呢？常用的方案是把表垂直查分，這種很常見我們不做過多討論。

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

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

答案是不會！因為聯(lián)合索引的第一列uid是唯一且不會變的，所以uid就已經(jīng)決定了索引的順序，switch列的改變只會改變索引節(jié)點上第二個key的值，不會改變索引結(jié)構(gòu)。

案例二：IM子系統(tǒng)分表方案

IM子系統(tǒng)包含：用戶、聯(lián)系人、云消息、系統(tǒng)消息四個主要的業(yè)務表。數(shù)據(jù)庫按業(yè)務拆分，每個業(yè)務使用單獨的實例。除系統(tǒng)消息表外，其他表都是以uid做key按128取模分了128個表。由于系統(tǒng)消息的業(yè)務比較特殊，所以其分表方案與其他業(yè)務不太一樣。

我們先來了解下系統(tǒng)消息的業(yè)務特點：系統(tǒng)消息表保存的是服務器發(fā)出通知類型的消息，既然是通知，就會有實效性，我們規(guī)定系統(tǒng)消息有效期為30天，所以針對以上特點我們采取如下分表方案：

按月對系統(tǒng)消息表進行分表，每個月的數(shù)據(jù)又分為128個表。

大家思考一個問題：

查詢一個人的系統(tǒng)消息時，由于是按月分表，而大多數(shù)查詢都是跨月的(因為需要查找30天內(nèi)的消息)，所以需要兩次數(shù)據(jù)庫交互。是否可以優(yōu)化呢？

我們可以冗余存儲，具體優(yōu)化方案如下：

1、插入系統(tǒng)消息時寫當前月和上個月兩個表；

2、讀從上一個月開始讀；

如圖4所示：

圖4 冗余存儲方式

這個方案我們可以保證一次查詢可以找到用戶所有有效期內(nèi)的系統(tǒng)消息，但是通過犧牲了存儲空間和寫入效率換取的，不一定是最優(yōu)的方案，但在總數(shù)據(jù)量不大，且比較注重查詢性能的業(yè)務場景下還是可以選用的。

-? ? ?07、總結(jié)??? ?-

1、自增主鍵性能不一定高，需要結(jié)合實際業(yè)務場景做分析；

2、大多數(shù)場景數(shù)據(jù)類型選擇上盡量使用簡單的類型；

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

4、如果要查詢的數(shù)據(jù)可以在索引文件中找到，存儲引擎就不會查找主鍵索引訪問實際記錄。

特別推薦一個分享架構(gòu)+算法的優(yōu)質(zhì)內(nèi)容，還沒關(guān)注的小伙伴，可以長按關(guān)注一下：

長按訂閱更多精彩▼
如有收獲，點個在看，誠摯感謝