一、什么是分庫分表？

其實(shí)就是字面意思，很好理解：

分庫：將原來存放在單個(gè)數(shù)據(jù)庫中的數(shù)據(jù)，拆分到多個(gè)數(shù)據(jù)庫中存放。

分表：將原來存放在單個(gè)表中的數(shù)據(jù)，拆分到多個(gè)表中存放。

二、為什么要分庫分表？

關(guān)鍵字：提升性能、增加可用性。

從性能上看

隨著單庫中的數(shù)據(jù)量越來越大、數(shù)據(jù)庫的查詢QPS越來越高，相應(yīng)的，對(duì)數(shù)據(jù)庫的讀寫所需要的時(shí)間也越來越多。數(shù)據(jù)庫的讀寫性能可能會(huì)成為業(yè)務(wù)發(fā)展的瓶頸。對(duì)應(yīng)的，就需要做數(shù)據(jù)庫性能方面的優(yōu)化。本文中我們只討論數(shù)據(jù)庫層面的優(yōu)化，不討論緩存等應(yīng)用層優(yōu)化的手段。

從可用性上看

單個(gè)數(shù)據(jù)庫如果發(fā)生意外，很可能會(huì)丟失所有數(shù)據(jù)。尤其是云時(shí)代，很多數(shù)據(jù)庫都跑在虛擬機(jī)上，如果虛擬機(jī)/宿主機(jī)發(fā)生意外，則可能造成無法挽回的損失。因此，除了傳統(tǒng)的 Master-Slave、Master-Master 等部署層面解決可靠性問題外，我們也可以考慮從數(shù)據(jù)拆分層面解決此問題。

三、如何分庫分表

下邊以電商系統(tǒng)中的例子來說明，下圖是電商系統(tǒng)賣家模塊的表結(jié)構(gòu)：

通過以下SQL能夠獲取到商品相關(guān)的店鋪信息、地理區(qū)域信息：

隨著公司業(yè)務(wù)快速發(fā)展，數(shù)據(jù)庫中的數(shù)據(jù)量猛增，訪問性能也變慢了，優(yōu)化迫在眉睫。分析一下問題出現(xiàn)在哪兒呢？ 關(guān)系型數(shù)據(jù)庫本身比較容易成為系統(tǒng)瓶頸，單機(jī)存儲(chǔ)容量、連接數(shù)、處理能力都有限。當(dāng)單表的數(shù)據(jù)量達(dá)到1000W或100G以后，由于查詢維度較多，即使添加從庫、優(yōu)化索引，做很多操作時(shí)性能仍下降嚴(yán)重。

方案1：

通過提升服務(wù)器硬件能力來提高數(shù)據(jù)處理能力，比如增加存儲(chǔ)容量 、CPU等，這種方案成本很高，并且如果瓶頸在MySQL本身那么提高硬件也是有很的。

方案2：

把數(shù)據(jù)分散在不同的數(shù)據(jù)庫中，使得單一數(shù)據(jù)庫的數(shù)據(jù)量變小來緩解單一數(shù)據(jù)庫的性能問題，從而達(dá)到提升數(shù)據(jù)庫性能的目的，如下圖：將電商數(shù)據(jù)庫拆分為若干獨(dú)立的數(shù)據(jù)庫，并且對(duì)于大表也拆分為若干小表，通過這種數(shù)據(jù)庫拆分的方法來解決數(shù)據(jù)庫的性能問題。

分庫分表就是為了解決由于數(shù)據(jù)量過大而導(dǎo)致數(shù)據(jù)庫性能降低的問題，將原來獨(dú)立的數(shù)據(jù)庫拆分成若干數(shù)據(jù)庫組成 ，將數(shù)據(jù)大表拆分成若干數(shù)據(jù)表組成，使得單一數(shù)據(jù)庫、單一數(shù)據(jù)表的數(shù)據(jù)量變小，從而達(dá)到提升數(shù)據(jù)庫性能的目的。

垂直分表

分庫分表包括分庫和分表兩個(gè)部分，在生產(chǎn)中通常包括：垂直分庫、水平分庫、垂直分表、水平分表四種方式。

先說 垂直分表：

通常在商品列表中是不顯示商品詳情信息的，如下圖：

用戶在瀏覽商品列表時(shí)，只有對(duì)某商品感興趣時(shí)才會(huì)查看該商品的詳細(xì)描述。因此，商品信息中商品描述字段訪問頻次較低，且該字段存儲(chǔ)占用空間較大，訪問單個(gè)數(shù)據(jù)IO時(shí)間較長；商品信息中商品名稱、商品圖片、商品價(jià)格等其他字段數(shù)據(jù)訪問頻次較高。

由于這兩種數(shù)據(jù)的特性不一樣，因此他考慮將商品信息表拆分如下：

將訪問頻次低的商品描述信息單獨(dú)存放在一張表中，訪問頻次較高的商品基本信息單獨(dú)放在一張表中。

商品列表可采用以下sql：

需要獲取商品描述時(shí)，再通過以下sql獲?。?/p>

垂直分表定義：將一個(gè)表按照字段分成多表，每個(gè)表存儲(chǔ)其中一部分字段。

它帶來的提升是：

1.為了避免IO爭(zhēng)搶并減少鎖表的幾率，查看詳情的用戶與商品信息瀏覽互不影響

2.充分發(fā)揮熱門數(shù)據(jù)的操作效率，商品信息的操作的高效率不會(huì)被商品描述的低效率所拖累。

為什么大字段IO效率低：第一是由于數(shù)據(jù)量本身大，需要更長的讀取時(shí)間；第二是跨頁，頁是數(shù)據(jù)庫存儲(chǔ)單位，很多查找及定位操作都是以頁為單位，單頁內(nèi)的數(shù)據(jù)行越多數(shù)據(jù)庫整體性能越好，而大字段占用空間大，單頁內(nèi)存儲(chǔ)行數(shù)少，因此IO效率較低。第三，數(shù)據(jù)庫以行為單位將數(shù)據(jù)加載到內(nèi)存中，這樣表中字段長度較短且訪問頻率較高，內(nèi)存能加載更多的數(shù)據(jù)，命中率更高，減少了磁盤IO，從而提升了數(shù)據(jù)庫性能。

一般來說，某業(yè)務(wù)實(shí)體中的各個(gè)數(shù)據(jù)項(xiàng)的訪問頻次是不一樣的，部分?jǐn)?shù)據(jù)項(xiàng)可能是占用存儲(chǔ)空間比較大的BLOB或是TEXT。例如上例中的商品描述。所以，當(dāng)表數(shù)據(jù)量很大時(shí)，可以將表按字段切開，將熱門字段、冷門字段分開放置在不同庫中，這些庫可以放在不同的存儲(chǔ)設(shè)備上，避免IO爭(zhēng)搶。垂直切分帶來的性能提升主要集中在熱門數(shù)據(jù)的操作效率上，而且磁盤爭(zhēng)用情況減少。

通常我們按以下原則進(jìn)行垂直拆分:

把不常用的字段單獨(dú)放在一張表;

把text，blob等大字段拆分出來放在附表中;

經(jīng)常組合查詢的列放在一張表中;

垂直分庫

通過垂直分表性能得到了一定程度的提升，但是還沒有達(dá)到要求，并且磁盤空間也快不夠了，因?yàn)閿?shù)據(jù)還是始終限制在一臺(tái)服務(wù)器，庫內(nèi)垂直分表只解決了單一表數(shù)據(jù)量過大的問題，但沒有將表分布到不同的服務(wù)器上，因此每個(gè)表還是競(jìng)爭(zhēng)同一個(gè)物理機(jī)的CPU、內(nèi)存、網(wǎng)絡(luò)IO、磁盤。

經(jīng)過思考，他把原有的SELLER_DB(賣家?guī)?，分為了PRODUCT_DB(商品庫)和STORE_DB(店鋪庫)，并把這兩個(gè)庫分散到不同服務(wù)器，如下圖：

由于商品信息與商品描述業(yè)務(wù)耦合度較高，因此一起被存放在PRODUCT_DB(商品庫)；而店鋪信息相對(duì)獨(dú)立，因此單獨(dú)被存放在STORE_DB(店鋪庫)。

垂直分庫是指按照業(yè)務(wù)將表進(jìn)行分類，分布到不同的數(shù)據(jù)庫上面，每個(gè)庫可以放在不同的服務(wù)器上，它的核心理念是專庫專用。

它帶來的提升是：

解決業(yè)務(wù)層面的耦合，業(yè)務(wù)清晰

能對(duì)不同業(yè)務(wù)的數(shù)據(jù)進(jìn)行分級(jí)管理、維護(hù)、監(jiān)控、擴(kuò)展等

高并發(fā)場(chǎng)景下，垂直分庫一定程度的提升IO、數(shù)據(jù)庫連接數(shù)、降低單機(jī)硬件資源的瓶頸

垂直分庫通過將表按業(yè)務(wù)分類，然后分布在不同數(shù)據(jù)庫，并且可以將這些數(shù)據(jù)庫部署在不同服務(wù)器上，從而達(dá)到多個(gè)服務(wù)器共同分?jǐn)倝毫Φ男Ч?，但是依然沒有解決單表數(shù)據(jù)量過大的問題。

水平分庫

經(jīng)過垂直分庫后，數(shù)據(jù)庫性能問題得到一定程度的解決，但是隨著業(yè)務(wù)量的增長，PRODUCT_DB(商品庫)單庫存儲(chǔ)數(shù)據(jù)已經(jīng)超出預(yù)估。粗略估計(jì)，目前有8w店鋪，每個(gè)店鋪平均150個(gè)不同規(guī)格的商品，再算上增長，那商品數(shù)量得往1500w+上預(yù)估，并且PRODUCT_DB(商品庫)屬于訪問非常頻繁的資源，單臺(tái)服務(wù)器已經(jīng)無法支撐。此時(shí)該如何優(yōu)化？

再次分庫？但是從業(yè)務(wù)角度分析，目前情況已經(jīng)無法再次垂直分庫。

嘗試水平分庫，將店鋪ID為單數(shù)的和店鋪ID為雙數(shù)的商品信息分別放在兩個(gè)庫中。

也就是說，要操作某條數(shù)據(jù)，先分析這條數(shù)據(jù)所屬的店鋪ID。如果店鋪ID為雙數(shù)，將此操作映射至RRODUCT_DB1(商品庫1)；如果店鋪ID為單數(shù)，將操作映射至RRODUCT_DB2(商品庫2)。此操作要訪問數(shù)據(jù)庫名稱的表達(dá)式為RRODUCT_DB[店鋪ID%2 + 1] 。

水平分庫是把同一個(gè)表的數(shù)據(jù)按一定規(guī)則拆到不同的數(shù)據(jù)庫中，每個(gè)庫可以放在不同的服務(wù)器上。

垂直分庫是把不同表拆到不同數(shù)據(jù)庫中，它是對(duì)數(shù)據(jù)行的拆分，不影響表結(jié)構(gòu)

它帶來的提升是：

解決了單庫大數(shù)據(jù)，高并發(fā)的性能瓶頸。

提高了系統(tǒng)的穩(wěn)定性及可用性。

穩(wěn)定性體現(xiàn)在IO沖突減少，鎖定減少，可用性指某個(gè)庫出問題，部分可用`

當(dāng)一個(gè)應(yīng)用難以再細(xì)粒度的垂直切分，或切分后數(shù)據(jù)量行數(shù)巨大，存在單庫讀寫、存儲(chǔ)性能瓶頸，這時(shí)候就需要進(jìn)行水平分庫了，經(jīng)過水平切分的優(yōu)化，往往能解決單庫存儲(chǔ)量及性能瓶頸。但由于同一個(gè)表被分配在不同的數(shù)據(jù)庫，需要額外進(jìn)行數(shù)據(jù)操作的路由工作，因此大大提升了系統(tǒng)復(fù)雜度。

水平分表

按照水平分庫的思路對(duì)他把PRODUCT_DB_X(商品庫)內(nèi)的表也可以進(jìn)行水平拆分，其目的也是為解決單表數(shù)據(jù)量大的問題，如下圖：

與水平分庫的思路類似，不過這次操作的目標(biāo)是表，商品信息及商品描述被分成了兩套表。如果商品ID為雙數(shù)，將此操作映射至商品信息1表；如果商品ID為單數(shù)，將操作映射至商品信息2表。此操作要訪問表名稱的表達(dá)式為商品信息[商品ID%2 + 1] 。

水平分表是在同一個(gè)數(shù)據(jù)庫內(nèi)，把同一個(gè)表的數(shù)據(jù)按一定規(guī)則拆到多個(gè)表中。

它帶來的提升是：

優(yōu)化單一表數(shù)據(jù)量過大而產(chǎn)生的性能問題

避免IO爭(zhēng)搶并減少鎖表的幾率

庫內(nèi)的水平分表，解決了單一表數(shù)據(jù)量過大的問題，分出來的小表中只包含一部分?jǐn)?shù)據(jù)，從而使得單個(gè)表的數(shù)據(jù)量變小，提高檢索性能。

總結(jié)

垂直分表：可以把一個(gè)寬表的字段按訪問頻次、是否是大字段的原則拆分為多個(gè)表，這樣既能使業(yè)務(wù)清晰，還能提升部分性能。拆分后，盡量從業(yè)務(wù)角度避免聯(lián)查，否則性能方面將得不償失。

垂直分庫：可以把多個(gè)表按業(yè)務(wù)耦合松緊歸類，分別存放在不同的庫，這些庫可以分布在不同服務(wù)器，從而使訪問壓力被多服務(wù)器負(fù)載，大大提升性能，同時(shí)能提高整體架構(gòu)的業(yè)務(wù)清晰度，不同的業(yè)務(wù)庫可根據(jù)自身情況定制優(yōu)化方案。但是它需要解決跨庫帶來的所有復(fù)雜問題。

水平分庫：可以把一個(gè)表的數(shù)據(jù)(按數(shù)據(jù)行)分到多個(gè)不同的庫，每個(gè)庫只有這個(gè)表的部分?jǐn)?shù)據(jù)，這些庫可以分布在不同服務(wù)器，從而使訪問壓力被多服務(wù)器負(fù)載，大大提升性能。它不僅需要解決跨庫帶來的所有復(fù)雜問題，還要解決數(shù)據(jù)路由的問題(數(shù)據(jù)路由問題后邊介紹)。

水平分表：可以把一個(gè)表的數(shù)據(jù)(按數(shù)據(jù)行)分到多個(gè)同一個(gè)數(shù)據(jù)庫的多張表中，每個(gè)表只有這個(gè)表的部分?jǐn)?shù)據(jù)，這樣做能小幅提升性能，它僅僅作為水平分庫的一個(gè)補(bǔ)充優(yōu)化。

一般來說，在系統(tǒng)設(shè)計(jì)階段就應(yīng)該根據(jù)業(yè)務(wù)耦合松緊來確定垂直分庫，垂直分表方案，在數(shù)據(jù)量及訪問壓力不是特別大的情況，首先考慮緩存、讀寫分離、索引技術(shù)等方案。若數(shù)據(jù)量極大，且持續(xù)增長，再考慮水平分庫水平分表方案。

轉(zhuǎn)自：https://www.zhihu.com/question/448775613

https://blog.csdn.net/weixin_44062339/article/details/100491744