mysql資料表分表策略

標籤：

mysql分表方法：

方法一、 做資料庫叢集！ 主從資料庫 雙向熱備份（或一對多的資料庫即時備份策略），這樣可將資料庫查詢分攤到幾個伺服器去（可跟伺服器負載平衡結合起來架構）

優點：擴充性好，沒有多個分表後的複雜操作（php代碼）
缺點：單個表的資料量還是沒有變，一次操作所花的時間還是那麼多，硬體開銷大。

方法二、 根據特殊情況，按照特定規則分表：比如 使用者聊天表，
message_00,message_01,message_02……….message_98,message_99.然後根據使用者的ID來判斷 這個使用者的聊天資訊放到哪張表裡面，你可以用hash的方式來獲得，可以用求餘的方式來獲得，方法很多，比如用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 ;}echo get_hash_table( ‘message‘ , ‘user18991‘ );     //結果為message_10echo get_hash_table( ‘message‘ , ‘user34523‘ );    //結果為message_13

　　

優點：避免一張表出現幾百萬條資料，縮短了一條sql的執行時間說明一下，上面的這個方法，告訴我們user18991這個使用者的訊息都記錄在message_10這張表裡，user34523這個使用者的訊息都記錄在message_13這張表裡，讀取的時候，只要從各自的表中讀取就行了

缺點：當一種規則確定時，打破這條規則會很麻煩，上面的例子中我用的hash演算法是crc32，如果我現在不想用這個演算法了，改用md5後，會使同一個使用者的訊息被儲存到不同的表中，這樣資料亂套了。擴充性很差。

方法三：利用merge儲存引擎來實現分表

假如有一張使用者表user，有50W條資料，現在要拆成二張表user1和user2，每張表25W條資料，

CREATE TABLE `test`.`user` (`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE = MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE `test`.`user1` (`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE = MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  CREATE TABLE `test`.`user2` (`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE = MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, ‘user1‘ , ‘123‘ ,‘[email protected]‘ );INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, ‘user2‘ , ‘123‘ ,‘[email protected]‘ );INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, ‘user3‘ , ‘123‘ ,‘[email protected]‘ );INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, ‘user4‘ , ‘123‘ ,‘[email protected]‘ ); INSERT INTO user1(user1.id,user1.username,user1.pwd,user1.email) SELECT user.id,user.username,user.pwd,user.email FROM user where user.id >=2;INSERT INTO user2(user2.id,user2.username,user2.pwd,user2.email) SELECT user.id,user.username,user.pwd,user.email FROM user where user.id >2; DROP TABLE `user`;CREATE TABLE `test`.`user` (`id` INT( 11 ) NOT NULL ,`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,INDEX ( `id` )) ENGINE = MRG_MYISAM UNION=(user1,user2) INSERT_METHOD=LAST CHARSET=utf8  AUTO_INCREMENT=1 ; 測試：INSERT INTO `user` (`id`,`username`, `pwd`,`email`) VALUES(5, ‘user5‘ , ‘123‘ , ‘[email protected]‘ );INSERT INTO `user` (`id`,`username`, `pwd`,`email`) VALUES(6, ‘user6‘ , ‘123‘ , ‘[email protected]‘ );INSERT INTO `user` (`id`,`username`, `pwd`,`email`) VALUES(7, ‘user7‘ , ‘123‘ , ‘[email protected]‘ );  INSERT INTO `user` (`username`, `pwd`,`email`) VALUES( ‘user8‘ , ‘123‘ , ‘[email protected]‘ ); //這樣的話 id居然是0  這個有點奇怪，如果解決不了，每次插入新資料，要加上last_id。。。。（php代碼得改）

優點：擴充性好，並且php代碼幾乎不用改但如此分表要注意點東西：
1.不能將merge儲存引擎變成其它儲存引擎
2.執行一個 insert，資料進入第一個或者最後一個 myisam 表（取決於 insert_method 選項的值）。mysql確保唯一索引值在那個 myisam 表裡保持唯一，但不是跨集合裡所有的表。
3. merge表 必須和分表的結構一模一樣····

缺點：這種方法的效果比第二種要差一點

建議：具體情況具體分析，方法一、方法二、方法三綜合使用。

mysql資料表分表策略（轉）