Mysql数据库水平分表实现方案

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张表,代码如下:

1. functionget_hash_table($table,$userid)
2. {
3. $str=crc32($userid);
5. if($str<0){
6. $hash="0".substr(abs($str),0,1);
7. }else{
8. $hash=substr($str,0,2);
9. }
11. return$table."_".$hash;
12. }
14. echoget_hash_table('message','user18991');//结果为message_10
15. echoget_hash_table('message','user34523');//结果为message_13

另外,介绍我现在就是采用简单的取模分表,代码如下:

1. /**
2. *@paramstring$table_name表名
3. *@paramint$user_id用户id
4. *@paramint$total分表总数
5. *@linkhttp://www.phpfensi.com
6. */
7. functionhash_table($table_name,$user_id,$total)
8. {
9. return$table_name.'_'.(($user_id%$total)+1);
10. }
12. echohash_table("artice",1234,5);//artice_5
13. echohash_table("artice",3243,5);//artice_4

4.利用merge存储引擎分表

感觉merge存储引擎类似sql中union的感觉,但是查询效率不高,如下举例,拥有1000w记录的old_user表分表:

1),创建new_user表使用merge存储引擎,代码如下:

1. mysql>CREATETABLEIFNOTEXISTS`user1`(
2. ->`id`int(11)NOTNULLAUTO_INCREMENT,
3. ->`name`varchar(50)DEFAULTNULL,
4. ->`sex`int(1)NOTNULLDEFAULT'0',
5. ->PRIMARYKEY(`id`)
6. ->)ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=1;
7. QueryOK,0rowsaffected(0.05sec)
9. mysql>CREATETABLEIFNOTEXISTS`user2`(
10. ->`id`int(11)NOTNULLAUTO_INCREMENT,
11. ->`name`varchar(50)DEFAULTNULL,
12. ->`sex`int(1)NOTNULLDEFAULT'0',
13. ->PRIMARYKEY(`id`)
14. ->)ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=1;
15. QueryOK,0rowsaffected(0.01sec)
17. mysql>INSERTINTO`user1`(`name`,`sex`)VALUES('张映',0);
18. QueryOK,1rowaffected(0.00sec)
20. mysql>INSERTINTO`user2`(`name`,`sex`)VALUES('tank',1);
21. QueryOK,1rowaffected(0.00sec)
23. mysql>CREATETABLEIFNOTEXISTS`new_user`(
24. ->`id`int(11)NOTNULLAUTO_INCREMENT,
25. ->`name`varchar(50)DEFAULTNULL,
26. ->`sex`int(1)NOTNULLDEFAULT'0',
27. ->INDEX(id)
28. ->)TYPE=MERGEUNION=(user1,user2)INSERT_METHOD=LASTAUTO_INCREMENT=1;
29. QueryOK,0rowsaffected,1warning(0.00sec)
31. mysql>selectid,name,sexfromnew_user;
32. +----+--------+-----+
33. |id|name|sex|
34. +----+--------+-----+
35. |1|张映|0|
36. |1|tank|1|
37. +----+--------+-----+
38. 2rowsinset(0.00sec)
40. mysql>INSERTINTO`new_user`(`name`,`sex`)VALUES('tank2',0);
41. QueryOK,1rowaffected(0.00sec)
43. mysql>selectid,name,sexfromuser2
44. ->;
45. +----+-------+-----+
46. |id|name|sex|
47. +----+-------+-----+
48. |1|tank|1|
49. |2|tank2|0|
50. +----+-------+-----+

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