mysql 分表技術淺析

Mysql系統的分表技術淺析

*一、案例描述

智能交通項目中，隨著城市車輛日漸繁多，資料量急劇增大，主表的無限制增長勢必影響系統效能。為了使資料庫保持在較好效能，需要採用分表機制。

首先要瞭解為什麼要分表及其分表的好處是什麼。我們先資料庫執行SQL的過程：

接收到SQL --> 放入SQL執行隊列 --> 流量分析器分解SQL --> 按照分析結果進行資料的提取或者修改 --> 返回處理結果.

這意味著如果前一個SQL沒有執行完畢的話，後面的SQL是不會執行的，因為為了保證資料的完整性，必須對資料表檔案進行鎖定，包括共用鎖定和獨享鎖兩種鎖定。共用鎖定是在鎖定的期間，其它線程也可以訪問這個資料檔案，但是不允許修改操作，相應的，獨享鎖就是整個檔案就是歸一個線程所有，其它線程無法訪問這個資料檔案。

一般MySQL中最快的儲存引擎MyISAM，它是基於表鎖定的，就是說如果一鎖定的話，那麼整個資料檔案外部都無法訪問，必須等前一個操作完成後，才能接收下一個操作，那麼在這個前一個操作沒有執行完成，後一個操作等待在隊列裡無法執行的情況叫做阻塞，一般我們通俗意義上叫做“鎖表”。

鎖表直接導致的後果是什嗎？就是大量的SQL無法立即執行，必須等隊列前面的SQL全部執行完畢才能繼續執行。這個無法執行的SQL就會導致沒有結果，或者延遲嚴重，影響使用者體驗。

特別是對於一些使用比較頻繁的表，比如SNS系統中的使用者資訊表、論壇系統中的文章表等等，都是訪問量大很大的表，為了保證資料的快速提取返回給使用者，必須使用一些處理方式來解決這個問題，這個就是我今天要聊到的分表技術。

*二、案例分析和解決

下面給出幾種分表的操作思路和基本步驟：

第一招、基於基礎資料表的分表處理

其基本思路在於將業務分為主要表和基礎資料表，其中主要表儲存所有的基本資料，如果業 務需要使用到具體資料，則必須從基礎資料表中尋找指定的表名等。

以智能交通的過車資料為例，過車資訊主要包含  車牌號等車牌資訊，圖片資訊(圖片路徑、圖片大小等)，其餘資訊（如時間、違法類型等）。所以我們可以把過車資訊表設計為基礎資訊表，將車牌資訊表和圖片資訊表設計為業務表。

過車資訊、車牌資訊表、圖片資訊表的大小關係如下:過車資訊<車牌資訊表<圖片資訊表。

因此我們可以針對車牌資訊表和圖片資訊表做分表操作。我們加深我們的表資訊如下

Passvehicle_tb表

基礎資料表ID   INT(10)

資料類型名稱  char(50)

子表ID   INT(10)   

車牌表Plate_tb  表

車牌表ID   INT(10)

基礎資料表ID   INT(10)

圖片表Picture_tb表

圖片表ID   INT(10)

車牌表ID   INT(10)

我們有一條Passvehicle_tb的記錄為：

基礎資料表ID 車牌類型 子表ID

    1          藍牌       1

那麼我們根據Passvehicle_tb可以組合出指標的名字，比如blueplate_1。然後所有的藍牌過車資料就儲存到blueplate_1中。

當我們的車牌種類比較少的時候，那麼這種做法可能沒有什麼好處，但當資料類型多的時候，那麼對於資料的插入、尋找將有非常大的提高。

比如我們要尋找所有的藍牌車輛的過車資訊。我們就可以直接搜尋:select *from bluePlate_1 where baseinfoid=1;

當某一個地方藍牌車輛非常多的情況下，我們可以根據尾牌來進行分表。這時候我們的子表ID可以更加豐富，比如以尾號為區分，把所有的藍牌過車分為：blueplate_0、blueplate_1...blueplate_9這樣的10個數字。

這種方式的處理優缺點如下：

【優勢】增加刪除節點非常方便，為後期升級維護帶來很大便利

【劣勢】需要增加表或者對某一個表進行操作，還是無法離開資料庫，會產生瓶頸

第二招、基於Hash演算法的分表

Hash表就是通過某個特殊的Hash演算法計算出的一個唯一值，且可以使用這個計算出來的值尋找到需要的值，這個叫做雜湊表。

分表的設計原理跟這個思想類似：通過一個原始目標的ID或者名稱通過一定的hash演算法計算出資料存放區表的表名，進而可以訪問相應的表。

繼續以交通訊息表為例，每一個基礎資料表的id和車牌資訊的id是唯一的，這兩項值是固定的，並且是惟一的，那麼我們就可以考慮通過對這兩項值中的一項進行一些運算得出一個目標表的名稱。

假如我們針對我們的視頻電警程式，假設系統最大允許1億條資料，考慮每個表儲存100萬條記錄，那麼整個系統就不超過100個表就能夠容納。按照這個標準，我們假設在基礎過車表的ID的基礎上進行hash運算，獲得一個key值，這個值就是我們的表名，然後訪問相應的表。

我們構造一個簡單的hash演算法：

int get_hash(int id){

String str = bin2hex(id);

String hash = substr(str, 0, 4);

     if (strlen(hash)<4){

         hash = str_pad(hash, 4, "0");//不足4位則補齊

     }

     return StringToIni(hash);

}

演算法大致就是傳入一個基礎資訊表ID值，然後函數返回一個4位的數字。比如：get_hash(1)，輸出的結果是“3100”，輸入：get_hash(23819)，得到的結果是：3233，我們繼續經過簡單的跟表首碼組合，就能夠訪問這個表了。那麼我們需要訪問ID為1的內容時候哦，組合的表將是：plate_3100、picture_3100，那麼就可以直接對目標表進行訪問了。

這裡要說明的是，即使使用hash演算法後，有部分資料是可能在同一個表的，這一點跟hash表不同，hash表是盡量解決衝突，但我們不需要，我們只需要做好預測和分析表資料可能儲存的表名，且資料的合理分配。

如果需要儲存的資料更多，同樣的，可以對版塊的名字進行hash操作，比如也是上面的二進位轉換成十六進位，因為漢字比數字和字母要多很多，那麼重複幾率更小，但是可能組合成的表就更多了，相應就必須考慮一些其它的問題。

總之，使用hash方式的話必須選擇好的hash演算法，才能產生更多的表，使資料查詢的更迅速。

【優點hash演算法直接得出目標表名稱，效率很高】

【劣勢】擴充性比較差，選擇了一個hash演算法，定義了多少資料量，以後只能在這個資料量上跑，不能超過過這個資料量，可擴充性稍差

第三招、基於重新命名表明的分表技術

這種分表技術適用於將資料庫主表的某一個欄位作為分表的依據欄位，還是以過車資訊表為例，所有的過車資料都集中在1~12這12個月份中。我們完全可以把資料分為vehicle_2011_01、vehicle_2011_02...vehicle_2011_12這樣的12張表中。

這時候我查詢某一個時間段的資料、統計某一個月份的資料就不需要多表查詢（因為很多查詢操作發生在查詢本月的過車資訊中）。但是如果你的查詢操作經常發生在多個月份的聯集查詢那麼這種分表技術就不是很適合了。

每個月初建立一張以月為單位的新表用來儲存過去一個月的資料，此外有一張表明不變的資料表，該表是用於暫時儲存新的一個月的資料的。不妨命名為my_table，那這張表是一直存在的，比如現在是十一月二號，那六月份的資料就暫時儲存在這張表中，這時候首先需要my_table變名，

rename table my_table to table_2011_10; 這時候就重新命名了my_table這張表，但是原來的my_table這張表還是存在的，但所有的資料都已經儲存在table_2011_11這張表，需要注意的時rename期間表是鎖定的。但採用rename進行批處理的好處，大量的資料不需要在進行轉移的時候不需要進行匯出和匯入的操作，那樣效率會很低，而且對資料庫造成的壓力會很大，

在重新命名了my_table這張表後需要重新要建立my_table這張表，CREAT my_table (.......)

table_2011_10 原則上儲存的都是十月份的資料，但是因為是三號才進行這個操作，所以肯定是十一月一號到三號的資料還是儲存到了table_2011_10 這張表上，那就需要從這張表中把一號到三號的資料重新尋找出來插入到my_table （暫時儲存的是10月份的資料）

insert into my_table (field1,field2....) SELECT field1,field2....FROM my_table_2011_10 WHERE filed3 >= "2011-11-01"; 這樣就把一號到三號的資料存入到了暫時儲存六月份資料的my_table ，每到新月份時時還要對該表進行重複的操作進行分表處理，但my_table 這張表保持不變，每次插入的sql語句就不用改了，但是進行查詢的時候就需要對時間進行判斷，以確定具體尋找那一張表，因為我們已經進行了分表操作。

這樣每個月初都重複這樣一個操作，分表就順利實現，且進行插入時還要進行此操作：

Alter table my_table max_rows=10000000 avg_row_length=15000;這樣操作的好處是保證表格儲存體好大量資料（超過2G）的資料（如果是資料量很小的話就不需要進行這個操作了）

三、總結

在大負載應用當中，資料庫一直是個很重要的瓶頸，必須要突破，現在客戶的要求越來越高，在保證資料的穩定性的基礎上，本文粗略講解了兩種分表的方式，僅起到拋磚引玉的作用。當然，本文代碼和設想沒有經過嚴謹推敲，所以無法保證設計的完全準確實用，見諒！