很多朋友在論壇和留言區(qū)域問(wèn)mysql在什么情況下才需要進(jìn)行分庫(kù)分表，以及采用何種設(shè)計(jì)方式才是最優(yōu)的選擇，根據(jù)這些問(wèn)題，小編為大家整理了關(guān)于MySQL分庫(kù)分表的應(yīng)用場(chǎng)景和最優(yōu)的設(shè)計(jì)方式舉例。

一. 分表

 場(chǎng)景：對(duì)于大型的互聯(lián)網(wǎng)應(yīng)用來(lái)說(shuō)，數(shù)據(jù)庫(kù)單表的記錄行數(shù)可能達(dá)到千萬(wàn)級(jí)甚至是億級(jí)，并且數(shù)據(jù)庫(kù)面臨著極高的并發(fā)訪問(wèn)。采用Master-Slave復(fù)制模式的MySQL架構(gòu)，

只能夠?qū)?shù)據(jù)庫(kù)的讀進(jìn)行擴(kuò)展，而對(duì)數(shù)據(jù)庫(kù)的寫(xiě)入操作還是集中在Master上，并且單個(gè)Master掛載的Slave也不可能無(wú)限制多，Slave的數(shù)量受到Master能力和負(fù)載的限制。

因此，需要對(duì)數(shù)據(jù)庫(kù)的吞吐能力進(jìn)行進(jìn)一步的擴(kuò)展，以滿足高并發(fā)訪問(wèn)與海量數(shù)據(jù)存儲(chǔ)的需要！

對(duì)于訪問(wèn)極為頻繁且數(shù)據(jù)量巨大的單表來(lái)說(shuō)，我們首先要做的就是減少單表的記錄條數(shù)，以便減少數(shù)據(jù)查詢所需要的時(shí)間，提高數(shù)據(jù)庫(kù)的吞吐，這就是所謂的分表！

 在分表之前，首先需要選擇適當(dāng)?shù)姆直聿呗?，使得?shù)據(jù)能夠較為均衡地分不到多張表中，并且不影響正常的查詢！

 對(duì)于互聯(lián)網(wǎng)企業(yè)來(lái)說(shuō)，大部分?jǐn)?shù)據(jù)都是與用戶關(guān)聯(lián)的，因此，用戶id是最常用的分表字段。因?yàn)榇蟛糠植樵兌夹枰獛嫌脩鬷d，這樣既不影響查詢，又能夠使數(shù)據(jù)較為均衡地

分布到各個(gè)表中(當(dāng)然，有的場(chǎng)景也可能會(huì)出現(xiàn)冷熱數(shù)據(jù)分布不均衡的情況)，如下圖：

假設(shè)有一張表記錄用戶購(gòu)買信息的訂單表order，由于order表記錄條數(shù)太多，將被拆分成256張表。

拆分的記錄根據(jù)user_id%256取得對(duì)應(yīng)的表進(jìn)行存儲(chǔ)，前臺(tái)應(yīng)用則根據(jù)對(duì)應(yīng)的user_id%256，找到對(duì)應(yīng)訂單存儲(chǔ)的表進(jìn)行訪問(wèn)。

這樣一來(lái)，user_id便成為一個(gè)必需的查詢條件，否則將會(huì)由于無(wú)法定位數(shù)據(jù)存儲(chǔ)的表而無(wú)法對(duì)數(shù)據(jù)進(jìn)行訪問(wèn)。

注：拆分后表的數(shù)量一般為2的n次方，就是上面拆分成256張表的由來(lái)！

假設(shè)order表結(jié)構(gòu)如下：

create table order_( 
 order_id bigint(20) primary key auto_increment, 
 user_id bigint(20), 
 user_nick varchar(50), 
 auction_id bigint(20), 
 auction_title bigint(20), 
 price bigint(20), 
 auction_cat varchar(200), 
 seller_id bigint(20), 
 seller_nick varchar(50) 
) 

那么分表以后，假設(shè)user_id = 257,并且auction_id = 100,需要根據(jù)auction_id來(lái)查詢對(duì)應(yīng)的訂單信息，則對(duì)應(yīng)的SQL語(yǔ)句如下：

select * from order_1 where user_id=257 and auction_id = 100; 

其中，order_1是根據(jù)257%256計(jì)算得出，表示分表之后的第一張order表。

二. 分庫(kù)

   場(chǎng)景：分表能夠解決單表數(shù)據(jù)量過(guò)大帶來(lái)的查詢效率下降的問(wèn)題，但是，卻無(wú)法給數(shù)據(jù)庫(kù)的并發(fā)處理能力帶來(lái)質(zhì)的提升。面對(duì)高并發(fā)的讀寫(xiě)訪問(wèn)，當(dāng)數(shù)據(jù)庫(kù)master

服務(wù)器無(wú)法承載寫(xiě)操作壓力時(shí)，不管如何擴(kuò)展slave服務(wù)器，此時(shí)都沒(méi)有意義了。

因此，我們必須換一種思路，對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，從而提高數(shù)據(jù)庫(kù)寫(xiě)入能力，這就是所謂的分庫(kù)!

    與分表策略相似，分庫(kù)可以采用通過(guò)一個(gè)關(guān)鍵字取模的方式，來(lái)對(duì)數(shù)據(jù)訪問(wèn)進(jìn)行路由，如下圖所示：

  還是之前的訂單表，假設(shè)user_id 字段的值為258，將原有的單庫(kù)分為256個(gè)庫(kù)，那么應(yīng)用程序?qū)?shù)據(jù)庫(kù)的訪問(wèn)請(qǐng)求將被路由到第二個(gè)庫(kù)(258%256 = 2)。

三. 分庫(kù)分表

場(chǎng)景：有時(shí)數(shù)據(jù)庫(kù)可能既面臨著高并發(fā)訪問(wèn)的壓力，又需要面對(duì)海量數(shù)據(jù)的存儲(chǔ)問(wèn)題，這時(shí)需要對(duì)數(shù)據(jù)庫(kù)既采用分表策略，又采用分庫(kù)策略，以便同時(shí)擴(kuò)展系統(tǒng)的

并發(fā)處理能力，以及提升單表的查詢性能，這就是所謂的分庫(kù)分表。

分庫(kù)分表的策略比前面的僅分庫(kù)或者僅分表的策略要更為復(fù)雜，一種分庫(kù)分表的路由策略如下：

    1. 中間變量 = user_id % (分庫(kù)數(shù)量 * 每個(gè)庫(kù)的表數(shù)量)

    2. 庫(kù) = 取整數(shù) (中間變量 / 每個(gè)庫(kù)的表數(shù)量)

    3. 表 = 中間變量 % 每個(gè)庫(kù)的表數(shù)量

同樣采用user_id作為路由字段，首先使用user_id 對(duì)庫(kù)數(shù)量*每個(gè)庫(kù)表的數(shù)量取模，得到一個(gè)中間變量；然后使用中間變量除以每個(gè)庫(kù)表的數(shù)量，取整，便得到

對(duì)應(yīng)的庫(kù)；而中間變量對(duì)每個(gè)庫(kù)表的數(shù)量取模，即得到對(duì)應(yīng)的表。

分庫(kù)分表策略詳細(xì)過(guò)程如下：

假設(shè)將原來(lái)的單庫(kù)單表order拆分成256個(gè)庫(kù)，每個(gè)庫(kù)包含1024個(gè)表，那么按照前面所提到的路由策略，對(duì)于user_id=262145 的訪問(wèn)，路由的計(jì)算過(guò)程如下：

1.  中間變量 = 262145 % (256 * 1024) = 1

2.  庫(kù) = 取整 (1/1024) = 0

3.  表 = 1 % 1024 = 1

這就意味著，對(duì)于user_id=262145 的訂單記錄的查詢和修改，將被路由到第0個(gè)庫(kù)的第1個(gè)order_1表中執(zhí)行?。?！

最新評(píng)論