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

-? ? 01、什么是InnoDB記錄存儲(chǔ)方式？??? ?-

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

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

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

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

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

圖1?Page Header

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

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

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

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

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

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

圖2?聚簇索引

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

既然記錄存儲(chǔ)在主鍵索引結(jié)構(gòu)中，那么在其他列創(chuàng)建的索引是如何找到記錄的呢？我們可以很自然的想到，非主鍵列上的索引可以先通過自身索引結(jié)構(gòu)查找到主鍵值，然后在用主鍵值在聚簇索引上找到相應(yīng)的記錄。InnoDB就是這么做的，所以我們也稱非主鍵列上的索引為二級(jí)索引(因?yàn)橐淮尾樵冃枰檎覂蓚€(gè)索引樹)

二級(jí)索引有以下特點(diǎn)：

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

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

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

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

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

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

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

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

2、跳過中間列，會(huì)導(dǎo)致后面的列不能使用索引；

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

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

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

了解了InnoDB的索引后，我們?cè)賮矸治鲎栽鲋麈I和業(yè)務(wù)主鍵優(yōu)缺點(diǎn)：

自增主鍵：寫入、查詢效率和磁盤利用率都高，但每次查詢都需要兩級(jí)索引，因?yàn)榫€上業(yè)務(wù)不會(huì)有直接使用主鍵列的查詢。

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

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

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

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

1、表設(shè)計(jì)原則

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

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

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

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

2、實(shí)際案例

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

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

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

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

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

這對(duì)上述場(chǎng)景，我們?cè)诒碓O(shè)計(jì)上可以做哪些優(yōu)化呢？常用的方案是把表垂直查分，這種很常見我們不做過多討論。

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

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

答案是不會(huì)！因?yàn)槁?lián)合索引的第一列uid是唯一且不會(huì)變的，所以u(píng)id就已經(jīng)決定了索引的順序，switch列的改變只會(huì)改變索引節(jié)點(diǎn)上第二個(gè)key的值，不會(huì)改變索引結(jié)構(gòu)。

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

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

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

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

大家思考一個(gè)問題：

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

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

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

2、讀從上一個(gè)月開始讀；

如圖4所示：

圖4 冗余存儲(chǔ)方式

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

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

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

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

3、索引不是越多越好，太多的索引會(huì)導(dǎo)致過大的索引文件；

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

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

長(zhǎng)按訂閱更多精彩▼
如有收獲，點(diǎn)個(gè)在看，誠(chéng)摯感謝