mysql中適合分表的情況：1、數(shù)據(jù)量過大，正常運(yùn)維影響業(yè)務(wù)訪問時(shí)，例如對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份需要大量的磁盤IO和網(wǎng)絡(luò)IO、對(duì)一個(gè)表進(jìn)行DDL修改會(huì)鎖住全表、對(duì)大表進(jìn)行訪問與更新出現(xiàn)鎖等待；2、隨著業(yè)務(wù)發(fā)展，需要對(duì)某些字段垂直拆分；3、單表中的數(shù)據(jù)量快速增長(zhǎng)，當(dāng)性能接近瓶頸時(shí)，就需要考慮水平切分。

本教程操作環(huán)境：windows7系統(tǒng)、mysql8版本、Dell G3電腦。

并不是所有表都需要進(jìn)行切分，主要還是看數(shù)據(jù)的增長(zhǎng)速度。切分后會(huì)在某種程度上提升業(yè)務(wù)的復(fù)雜度，數(shù)據(jù)庫(kù)除了承載數(shù)據(jù)的存儲(chǔ)和查詢外，協(xié)助業(yè)務(wù)更好的實(shí)現(xiàn)需求也是其重要工作之一。

不到萬不得已不用輕易使用分庫(kù)分表這個(gè)大招，避免"過度設(shè)計(jì)"和"過早優(yōu)化"。分庫(kù)分表之前，不要為分而分，先盡力去做力所能及的事情，例如：升級(jí)硬件、升級(jí)網(wǎng)絡(luò)、讀寫分離、索引優(yōu)化等等。當(dāng)數(shù)據(jù)量達(dá)到單表的瓶頸時(shí)候，再考慮分庫(kù)分表。

那么mysql中什么時(shí)候考慮分表

1、數(shù)據(jù)量過大，正常運(yùn)維影響業(yè)務(wù)訪問

這里說的運(yùn)維，指：

• 對(duì)數(shù)據(jù)庫(kù)備份，如果單表太大，備份時(shí)需要大量的磁盤IO和網(wǎng)絡(luò)IO。例如1T的數(shù)據(jù)，網(wǎng)絡(luò)傳輸占50MB時(shí)候，需要20000秒才能傳輸完畢，整個(gè)過程的風(fēng)險(xiǎn)都是比較高的

• 對(duì)一個(gè)很大的表進(jìn)行DDL修改時(shí)，MySQL會(huì)鎖住全表，這個(gè)時(shí)間會(huì)很長(zhǎng)，這段時(shí)間業(yè)務(wù)不能訪問此表，影響很大。如果使用pt-online-schema-change，使用過程中會(huì)創(chuàng)建觸發(fā)器和影子表，也需要很長(zhǎng)的時(shí)間。在此操作過程中，都算為風(fēng)險(xiǎn)時(shí)間。將數(shù)據(jù)表拆分，總量減少，有助于降低這個(gè)風(fēng)險(xiǎn)。

• 大表會(huì)經(jīng)常訪問與更新，就更有可能出現(xiàn)鎖等待。將數(shù)據(jù)切分，用空間換時(shí)間，變相降低訪問壓力

2、隨著業(yè)務(wù)發(fā)展，需要對(duì)某些字段垂直拆分

舉個(gè)例子，假如項(xiàng)目一開始設(shè)計(jì)的用戶表如下：

在項(xiàng)目初始階段，這種設(shè)計(jì)是滿足簡(jiǎn)單的業(yè)務(wù)需求的，也方便快速迭代開發(fā)。而當(dāng)業(yè)務(wù)快速發(fā)展時(shí)，用戶量從10w激增到10億，用戶非常的活躍，每次登錄會(huì)更新 last_login_name 字段，使得 user 表被不斷update，壓力很大。而其他字段：id, name, personal_info 是不變的或很少更新的，此時(shí)在業(yè)務(wù)角度，就要將 last_login_time 拆分出去，新建一個(gè) user_time 表。

personal_info 屬性是更新和查詢頻率較低的，并且text字段占據(jù)了太多的空間。這時(shí)候，就要對(duì)此垂直拆分出 user_ext 表了。

3、數(shù)據(jù)量快速增長(zhǎng)

隨著業(yè)務(wù)的快速發(fā)展，單表中的數(shù)據(jù)量會(huì)持續(xù)增長(zhǎng)，當(dāng)性能接近瓶頸時(shí)，就需要考慮水平切分，做分庫(kù)分表了。此時(shí)一定要選擇合適的切分規(guī)則，提前預(yù)估好數(shù)據(jù)容量

業(yè)務(wù)案例分析

1、用戶中心業(yè)務(wù)場(chǎng)景

用戶中心是一個(gè)非常常見的業(yè)務(wù)，主要提供用戶注冊(cè)、登錄、查詢/修改等功能，其核心表為：

任何脫離業(yè)務(wù)的架構(gòu)設(shè)計(jì)都是耍流氓，在進(jìn)行分庫(kù)分表前，需要對(duì)業(yè)務(wù)場(chǎng)景需求進(jìn)行梳理：

• 用戶側(cè)：前臺(tái)訪問，訪問量較大，需要保證高可用和高一致性。主要有兩類需求：

• • 用戶登錄：通過login_name/phone/email查詢用戶信息，1%請(qǐng)求屬于這種類型

  • 用戶信息查詢：登錄之后，通過uid來查詢用戶信息，99%請(qǐng)求屬這種類型

• 運(yùn)營(yíng)側(cè)：后臺(tái)訪問，支持運(yùn)營(yíng)需求，按照年齡、性別、登陸時(shí)間、注冊(cè)時(shí)間等進(jìn)行分頁的查詢。是內(nèi)部系統(tǒng)，訪問量較低，對(duì)可用性、一致性的要求不高。

2、水平切分方法

當(dāng)數(shù)據(jù)量越來越大時(shí)，需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行水平切分，上文描述的切分方法有"根據(jù)數(shù)值范圍"和"根據(jù)數(shù)值取模"。

"根據(jù)數(shù)值范圍"：以主鍵uid為劃分依據(jù)，按uid的范圍將數(shù)據(jù)水平切分到多個(gè)數(shù)據(jù)庫(kù)上。例如：user-db1存儲(chǔ)uid范圍為0~1000w的數(shù)據(jù)，user-db2存儲(chǔ)uid范圍為1000w~2000wuid數(shù)據(jù)。

• 優(yōu)點(diǎn)是：擴(kuò)容簡(jiǎn)單，如果容量不夠，只要增加新db即可。

• 不足是：請(qǐng)求量不均勻，一般新注冊(cè)的用戶活躍度會(huì)比較高，所以新的user-db2會(huì)比user-db1負(fù)載高，導(dǎo)致服務(wù)器利用率不平衡

"根據(jù)數(shù)值取模"：也是以主鍵uid為劃分依據(jù)，按uid取模的值將數(shù)據(jù)水平切分到多個(gè)數(shù)據(jù)庫(kù)上。例如：user-db1存儲(chǔ)uid取模得1的數(shù)據(jù)，user-db2存儲(chǔ)uid取模得0的uid數(shù)據(jù)。

• 優(yōu)點(diǎn)是：數(shù)據(jù)量和請(qǐng)求量分布均均勻

• 不足是：擴(kuò)容麻煩，當(dāng)容量不夠時(shí)，新增加db，需要rehash。需要考慮對(duì)數(shù)據(jù)進(jìn)行平滑的遷移。

非uid的查詢方法

水平切分后，對(duì)于按uid查詢的需求能很好的滿足，可以直接路由到具體數(shù)據(jù)庫(kù)。而按非uid的查詢，例如login_name，就不知道具體該訪問哪個(gè)庫(kù)了，此時(shí)需要遍歷所有庫(kù)，性能會(huì)降低很多。

對(duì)于用戶側(cè)，可以采用"建立非uid屬性到uid的映射關(guān)系"的方案；對(duì)于運(yùn)營(yíng)側(cè)，可以采用"前臺(tái)與后臺(tái)分離"的方案。

1、建立非uid屬性到uid的映射關(guān)系

• 映射關(guān)系

例如：login_name不能直接定位到數(shù)據(jù)庫(kù)，可以建立login_name→uid的映射關(guān)系，用索引表或緩存來存儲(chǔ)。當(dāng)訪問login_name時(shí)，先通過映射表查詢出login_name對(duì)應(yīng)的uid，再通過uid定位到具體的庫(kù)。

映射表只有兩列，可以承載很多數(shù)據(jù)，當(dāng)數(shù)據(jù)量過大時(shí)，也可以對(duì)映射表再做水平切分。這類kv格式的索引結(jié)構(gòu)，可以很好的使用cache來優(yōu)化查詢性能，而且映射關(guān)系不會(huì)頻繁變更，緩存命中率會(huì)很高。

• 基因法

分庫(kù)基因：假如通過uid分庫(kù)，分為8個(gè)庫(kù)，采用uid%8的方式進(jìn)行路由，此時(shí)是由uid的最后3bit來決定這行User數(shù)據(jù)具體落到哪個(gè)庫(kù)上，那么這3bit可以看為分庫(kù)基因。

2、前臺(tái)與后臺(tái)分離

對(duì)于用戶側(cè)，主要需求是以單行查詢?yōu)橹鳎枰ogin_name/phone/email到uid的映射關(guān)系，可以解決這些字段的查詢問題。

而對(duì)于運(yùn)營(yíng)側(cè)，很多批量分頁且條件多樣的查詢，這類查詢計(jì)算量大，返回?cái)?shù)據(jù)量大，對(duì)數(shù)據(jù)庫(kù)的性能消耗較高。此時(shí)，如果和用戶側(cè)公用同一批服務(wù)或數(shù)據(jù)庫(kù)，可能因?yàn)楹笈_(tái)的少量請(qǐng)求，占用大量數(shù)據(jù)庫(kù)資源，而導(dǎo)致用戶側(cè)訪問性能降低或超時(shí)。

這類業(yè)務(wù)最好采用"前臺(tái)與后臺(tái)分離"的方案，運(yùn)營(yíng)側(cè)后臺(tái)業(yè)務(wù)抽取獨(dú)立的service和db，解決和前臺(tái)業(yè)務(wù)系統(tǒng)的耦合。由于運(yùn)營(yíng)側(cè)對(duì)可用性、一致性的要求不高，可以不訪問實(shí)時(shí)庫(kù)，而是通過binlog異步同步數(shù)據(jù)到運(yùn)營(yíng)庫(kù)進(jìn)行訪問。在數(shù)據(jù)量很大的情況下，還可以使用ES搜索引擎或Hive來滿足后臺(tái)復(fù)雜的查詢方式。

【