Mysql資料庫水平分表實現方案

根據經驗，Mysql表資料一般達到百萬層級，查詢效率會很低，容易造成表鎖，甚至堆積很多串連，直接掛掉；水平分表能夠很大程度較少這些壓力。

1.按時間分表

這種分表方式有一定的局限性，當資料有較強的實效性，如微博發送記錄、微信訊息記錄等，這種資料很少有使用者會查詢幾個月前的資料，如就可以按月分表。
2.按區間範圍分表

一般在有嚴格的自增id需求上，如按照user_id水平分表：
table_1  user_id從1~100w
table_2  user_id從101~200w
table_3  user_id從201~300w
...
3.hash分表

通過一個原始目標的ID或者名稱通過一定的hash演算法計算出資料存放區表的表名，然後訪問相應的表。
按如下分10張表：

代碼如下	複製代碼
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_10 echo get_hash_table('message', 'user34523'); //結果為message_13

另外，介紹我現在就是採用簡單的模數分表：

代碼如下	複製代碼
/**  * @param string $table_name 表名  * @param int $user_id 使用者id  * @param int $total 分表總數  * @link http://www.phpddt.com  */ function hash_table($table_name, $user_id, $total) {     return $table_name . '_' . (($user_id % $total) + 1); }   echo hash_table("artice", 1234, 5); //artice_5 echo hash_table("artice", 3243, 5); //artice_4

4.利用merge儲存引擎分表

感覺merge儲存引擎類似sql中union的感覺，但是查詢效率不高。
如下舉例，擁有1000w記錄的old_user表分表：
（1）建立new_user表使用merge儲存引擎

代碼如下

複製代碼

mysql> CREATE TABLE IF NOT EXISTS `user1` (  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  ->   `name` varchar(50) DEFAULT NULL,  ->   `sex` int(1) NOT NULL DEFAULT '0',  ->   PRIMARY KEY (`id`)  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.05 sec)   mysql> CREATE TABLE IF NOT EXISTS `user2` (  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  ->   `name` varchar(50) DEFAULT NULL,  ->   `sex` int(1) NOT NULL DEFAULT '0',  ->   PRIMARY KEY (`id`)  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.01 sec)   mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('張映', 0); Query OK, 1 row affected (0.00 sec)   mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1); Query OK, 1 row affected (0.00 sec)   mysql> CREATE TABLE IF NOT EXISTS `new_user` (  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  ->   `name` varchar(50) DEFAULT NULL,  ->   `sex` int(1) NOT NULL DEFAULT '0',  ->   INDEX(id)  -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; Query OK, 0 rows affected, 1 warning (0.00 sec)   mysql> select id,name,sex from new_user; +----+--------+-----+ | id | name   | sex | +----+--------+-----+ |  1 | 張映 |   0 | |  1 | tank   |   1 | +----+--------+-----+ 2 rows in set (0.00 sec)   mysql> INSERT INTO `new_user` (`name`, `sex`) VALUES('tank2', 0); Query OK, 1 row affected (0.00 sec)   mysql> select id,name,sex from user2  -> ; +----+-------+-----+ | id | name  | sex | +----+-------+-----+ |  1 | tank  |   1 | |  2 | tank2 |   0 | +----+-------+-----+ 2 rows in set (0.00 sec)

（2）我old_user資料進行分表：

代碼如下	複製代碼
INSERT INTO user1(user1.id,user1.name,user1.sex) SELECT (user.id,user.name,user.sex)FROM old_user where user.id <= 5000000 INSERT INTO user2(user2.id,user2.name,user2.sex) SELECT (user.id,user.name,user.sex)FROM old_user where user.id > 10000000

這種方案最大的好處是，幾乎不用動業務代碼