MySQL分庫分表的原則

一、分表

　　當一個表的資料達到幾千萬條的時候，每一次查詢都會花費更長的時間，如果這時候在使用連結串列查詢，那麼我想應該會實在那裡，那麼我們應該如何解決這個問題呢？

　　1、為什麼要分表：

　　　　分表的目的就是為了解決當資料量太大的時候，資料查詢變慢的問題，那麼我們應該如何去解決這個問題呢？這時候就用到了分表。

　　　　那麼什麼是分表呢？
　　　　a.根據業務邏輯進行縱向拆分（按照資料庫欄位的必要性進行拆分）

　　　　b.根據資料庫資料量級進行拆分，一般拆分是在幾百萬這個量級上，因為超過千萬條資料庫查詢就會明顯變慢，具體如何拆分可以根據自己資料庫伺服器的實際情況進行測試和分析後決定。

　　2、分表的邏輯是什麼？

　　　　分表方法一：預先估計出要頻繁插入的表，這樣的表之後大概率會是一個資料量非常大的表，那麼我們就可以預先分出n個表來儲存資料，儲存資料時可以根據一定的規則判斷儲存到那張表，讀取時也是使用相同的規則進行判斷。如可以使取模的方式，也可使用hash的方式進行判斷。

　　取模方式：
function hash_table($table_name, $user_id, $total){

return $table_name . '_' . (($user_id % $total) + 1);

} 　　hash方式：　

　　　　function get_hash_table($table,$userid) {

　　　　　　$str = crc32($userid);

　　　　　　 if($str<0){

　　　　　　　　$hash = "0".substr(abs($str), 0, 1);

　　　　　　}else{

　　　　　　　　$hash = substr($str, 0, 2);

　　　　　　}

　　　　　　return $table."_".$hash;

　　　　}

　　　　查詢資料也可以使用上面的邏輯，先判斷出要查詢的資料位於哪些表中，然後在進行查詢

　　　　該方法一旦形成表資料，那麼想要改變規則非常困難，那將是一個夢魘，所以擴充套件性極差

　　　　分表方法二：利用merge儲存引擎來實現分表
　　　　　　merge引擎是就是一組MyISAM表的組合，類似於檢視，merge表本身不儲存任何資料，你對merge表的操作，實際上是對其字表的操作，但是這個對於程式來說是透明的，在插入的時候預設插入到第一張或最後一張的字表中，這個可以通過merge建立時進行設定。如果子表是預先建立的那麼你也可以選擇在插入的時候按照一定的規則，這幾對子表進行插入。

　　　　例項：
　　　　　　分表1：

CREATE TABLE `AuthTrace1` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`AuthId` char(255) DEFAULT NULL,
`Addtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '新增記錄的當前時間',
`UserIp` varchar(255) DEFAULT NULL COMMENT '使用者Ip',
`Date` date DEFAULT NULL COMMENT 'yyyyMMdd格式',
`ExtraInfo` varchar(255) DEFAULT NULL COMMENT '額外資訊',
`UserMAC` varchar(255) DEFAULT NULL COMMENT 'mac地址',
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

　　　　　　分表2：

CREATE TABLE `AuthTrace1` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`AuthId` char(255) DEFAULT NULL,
`Addtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '新增記錄的當前時間',
`UserIp` varchar(255) DEFAULT NULL COMMENT '使用者Ip',
`Date` date DEFAULT NULL COMMENT 'yyyyMMdd格式',
`ExtraInfo` varchar(255) DEFAULT NULL COMMENT '額外資訊',
`UserMAC` varchar(255) DEFAULT NULL COMMENT 'mac地址',
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

　　　　Merge表：

      CREATE TABLE `AuthTrace_Merge` (
          `Id` int(11) NOT NULL AUTO_INCREMENT,
          `AuthId` char(255) DEFAULT NULL,
          `Addtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '新增記錄的當前時間',
          `UserIp` varchar(255) DEFAULT NULL COMMENT '使用者Ip',
          `Date` date DEFAULT NULL COMMENT 'yyyyMMdd格式',
          `ExtraInfo` varchar(255) DEFAULT NULL COMMENT '額外資訊',
          `UserMAC` varchar(255) DEFAULT NULL COMMENT 'mac地址',
          PRIMARY KEY (`Id`)
      )ENGINE=MERGE DEFAULT CHARSET=utf8 INSERT_METHOD=LAST AUTO_INCREMENT=1 UNION=(`AuthTrace1`,`AuthTrace2`);

INSERT_METHOD：表示插入方式；

INSERT_METHOD=LAST 新增資料的時候插入到最後一個表，這裡就是t2

INSERT_METHOD=FIRST 新增資料的時候插入到第一個表，這裡就是t1

INSERT_METHOD=0 不允許插入

UNION = (`AuthTrace1`,`AuthTrace2`) 關聯AuthTrace1和AuthTrace2表

Merge表資料結構必須和分表完全一致

　　 merge存在的問題：資料插入式無法保證資料id的不重複

當然分表還有其他方式很多種方式和邏輯，我在這裡做個拋磚引玉，大家有什麼別的方法可以留言

MySQL分庫分表的原則

相關文章