mysql分表分庫的應用場景和設計方式

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

一. 分表

 場景：對于大型的互聯(lián)網應用來說，數(shù)據庫單表的記錄行數(shù)可能達到千萬級甚至是億級，并且數(shù)據庫面臨著極高的并發(fā)訪問。采用Master-Slave復制模式的MySQL架構，

只能夠對數(shù)據庫的讀進行擴展，而對數(shù)據庫的寫入操作還是集中在Master上，并且單個Master掛載的Slave也不可能無限制多，Slave的數(shù)量受到Master能力和負載的限制。

因此，需要對數(shù)據庫的吞吐能力進行進一步的擴展，以滿足高并發(fā)訪問與海量數(shù)據存儲的需要！

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

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

 對于互聯(lián)網企業(yè)來說，大部分數(shù)據都是與用戶關聯(lián)的，因此，用戶id是最常用的分表字段。因為大部分查詢都需要帶上用戶id，這樣既不影響查詢，又能夠使數(shù)據較為均衡地

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

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

拆分的記錄根據user_id%256取得對應的表進行存儲，前臺應用則根據對應的user_id%256，找到對應訂單存儲的表進行訪問。

這樣一來，user_id便成為一個必需的查詢條件，否則將會由于無法定位數(shù)據存儲的表而無法對數(shù)據進行訪問。

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

假設order表結構如下：

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) 
) 

那么分表以后，假設user_id = 257,并且auction_id = 100,需要根據auction_id來查詢對應的訂單信息，則對應的SQL語句如下：

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

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

二. 分庫

   場景：分表能夠解決單表數(shù)據量過大帶來的查詢效率下降的問題，但是，卻無法給數(shù)據庫的并發(fā)處理能力帶來質的提升。面對高并發(fā)的讀寫訪問，當數(shù)據庫master

服務器無法承載寫操作壓力時，不管如何擴展slave服務器，此時都沒有意義了。

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

    與分表策略相似，分庫可以采用通過一個關鍵字取模的方式，來對數(shù)據訪問進行路由，如下圖所示：

  還是之前的訂單表，假設user_id 字段的值為258，將原有的單庫分為256個庫，那么應用程序對數(shù)據庫的訪問請求將被路由到第二個庫(258%256 = 2)。

三. 分庫分表

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

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

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

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

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

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

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

對應的庫；而中間變量對每個庫表的數(shù)量取模，即得到對應的表。

分庫分表策略詳細過程如下：

假設將原來的單庫單表order拆分成256個庫，每個庫包含1024個表，那么按照前面所提到的路由策略，對于user_id=262145 的訪問，路由的計算過程如下：

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

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

3.  表 = 1 % 1024 = 1

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