了解知识
根据经验,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张表:
 
  1. function get_hash_table($table$userid)  
  2.     $str = crc32($userid); 
  3.      
  4.     if ($str < 0) { 
  5.         $hash = "0" . substr(abs($str), 0, 1); 
  6.     } else { 
  7.         $hash = substr($str, 0, 2); 
  8.     } 
  9.   
  10.     return $table . "_" . $hash
  11.   
  12. echo get_hash_table('message''user18991'); //结果为message_10 
  13. echo get_hash_table('message''user34523'); //结果为message_13 
 
另外,介绍我现在就是采用简单的取模分表:
 
  1. /** 
  2.  * @param string $table_name 表名 
  3.  * @param int $user_id 用户id 
  4.  * @param int $total 分表总数 
  5.  * @link http://www.phpddt.com 
  6.  */ 
  7. function hash_table($table_name$user_id$total
  8.     return $table_name . '_' . (($user_id % $total) + 1); 
  9.   
  10. echo hash_table("artice", 1234, 5); //artice_5 
  11. echo hash_table("artice", 3243, 5); //artice_4 
 
4.利用merge存储引擎分表
 
 
感觉merge存储引擎类似sql中union的感觉,但是查询效率不高。
如下举例,拥有1000w记录的old_user表分表:
(1)创建new_user表使用merge存储引擎
 
  1. mysql> CREATE TABLE IF NOT EXISTS `user1` ( 
  2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
  3.  ->   `namevarchar(50) DEFAULT NULL
  4.  ->   `sex` int(1) NOT NULL DEFAULT '0'
  5.  ->   PRIMARY KEY (`id`) 
  6.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 
  7. Query OK, 0 rows affected (0.05 sec) 
  8.   
  9. mysql> CREATE TABLE IF NOT EXISTS `user2` ( 
  10.  ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
  11.  ->   `namevarchar(50) DEFAULT NULL
  12.  ->   `sex` int(1) NOT NULL DEFAULT '0'
  13.  ->   PRIMARY KEY (`id`) 
  14.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 
  15. Query OK, 0 rows affected (0.01 sec) 
  16.   
  17. mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0); 
  18. Query OK, 1 row affected (0.00 sec) 
  19.   
  20. mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1); 
  21. Query OK, 1 row affected (0.00 sec) 
  22.   
  23. mysql> CREATE TABLE IF NOT EXISTS `new_user` ( 
  24.  ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
  25.  ->   `namevarchar(50) DEFAULT NULL
  26.  ->   `sex` int(1) NOT NULL DEFAULT '0'
  27.  ->   INDEX(id) 
  28.  -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; 
  29. Query OK, 0 rows affected, 1 warning (0.00 sec) 
  30.   
  31. mysql> select id,name,sex from new_user; 
  32. +----+--------+-----+ 
  33. | id | name   | sex | 
  34. +----+--------+-----+ 
  35. |  1 | 张映 |   0 | 
  36. |  1 | tank   |   1 | 
  37. +----+--------+-----+ 
  38. rows in set (0.00 sec) 
  39.   
  40. mysql> INSERT INTO `new_user` (`name`, `sex`) VALUES('tank2', 0); 
  41. Query OK, 1 row affected (0.00 sec) 
  42.   
  43. mysql> select id,name,sex from user2 
  44.  -> ; 
  45. +----+-------+-----+ 
  46. | id | name  | sex | 
  47. +----+-------+-----+ 
  48. |  1 | tank  |   1 | 
  49. |  2 | tank2 |   0 | 
  50. +----+-------+-----+ 
  51. rows in set (0.00 sec) 
 
(2)我old_user数据进行分表:
 
  1. INSERT INTO user1(user1.id,user1.name,user1.sex) SELECT (user.id,user.name,user.sex)FROM old_user where user.id <= 5000000 
  2. INSERT INTO user2(user2.id,user2.name,user2.sex) SELECT (user.id,user.name,user.sex)FROM old_user where user.id > 10000000 
 
这种方案最大的好处是,几乎不用动业务代码。
标签: MySQL
扩展知识