MySQL5.5表的水平拆分

一：业务背景

二：第一种业务水平拆分方案

三：第二种业务水平拆分方案

1.1由于业务量的急剧增加,某张表的数据越来越大,现在假设的情况为某家公司的P2P网站,由于前期宣传比较好,在近三个月的时间里业务量急剧增加,使得流水记录表(trade)的数据量变的越来越大,已经超过300万行记录,预计年内流水表的记录可能会超过1000万行,这个时候就需要考虑做表的水平拆分了

1.2 假设随着业务量的增加，用户量也增加了，由以前的几千个用户上升到100万，也想对其进行水平拆分

 

 

2.1假设年内达到1000万行记录,再加上后续的考虑,暂且把流水表(trade)拆分为10张表,分别为trade_0, trade_1, trade_2, trade_3... , trade_10

 

 

2.2流水表中的字段假设如下字段

serial_number流水号 user_ID用户ID request_message请求信息

 

 

2.3因为获取流水是根据user_id（用户ID）来获取自己的流水情况的，所以这里以user_id来做分表

 

 

2.4 user_id%10用user_id模上10，等于几就把等于这个user_id的记录插入到trade_x中（x等于0到10）

 

 

2.5具体步骤

2.5.1先查看一下当前trade表中有多少条记录，以便最后拆分完成以后核对记录是否丢失

2.5.2把user_id%10=0的记录插入到trade_0，trade_1...trade_9表中

2.5.3把trade_0...trade_10表的记录加起来，看是否是trade表的总记录数

 

 

 

2.5.4以上是把已有的数据拆分成多张表了，那么查询和插入如何操作呢？因为流水是和用户的操作相关的，所以查询和插入的时候，可以使用拼接字符串的方式进行插入数据，例如上面的拆分方法就可以这样拼接

1

2

3

4

5

6

7

8

9

int suffix = user_id%10;

 

String table_name = "trade_" + suffix;

 

INSERT INTO table_name(...) values(...);

 

或者

 

SELECT * FROM table_name WHERE ...;

 

 

2.5.5总结，由于以上拆分的表是根据已有的用户ID进行拆分的，先有用户，再有流水记录，所以这种业务做拆分是比较容易的

 

 

 

3.1由于用户量的增长，对用户表也要做数据拆分，拆分方案和以上相同，拆分为user_main_0...user_main_5

3.2现在的问题是，用户插入的时候，我们不知道用户ID是多少，也就意味着不知道应该往user_main_0...user_main_5那张表中插入数据

 

 

 

3.3为了解决以上问题我们可以先执行一下SQL语句

1

mysql> SELECT AUTO_INCREMENT FROMinformation_schema.TABLES WHERE TABLE_NAME='user_main';

它返回的是当前表的下一个自增长ID的值

 

 

 

3.4通过以上SQL语句已经得到了当前user_main表下一个ID值，下面创建user_main_uuid表

1

mysql> CREATE TABLEuser_main_uuid(user_main_next_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT);

 

3.5然后把“3.3 SQL语句”返回的值插入到user_main_uuid中

3.6那么用户注册的时候，提交用户填写的信息，进入到controll的时候，校验完用户信息以后，首先先查user_main_uuid表的user_mian_next_id字段返回的值，然后根据这个值%5，等于几，就给user_main_x中插入

 

 

 

3.7还有一个问题，用户注册完成之后，应该把user_main_next_id的值自动加1，否则，下个用户注册的时候ID会重复导致不能成功插入数据，所以在把数据成功插入到对应的user_main_x表中以后，再执行以下SQL语句

1

mysql> INSERT INTO user_main_uuidVALUES(NULL);

由于插入一条记录的方式来使ID自增的，所以“3.6”中应该获取的是user_mian_next_id字段的最大值MAX(user_mian_next_id)

3.8除了以上更新user_mian_next_id的值以外，还可以是以下方式

这样user_main_uuid表中就永远只有一条记录，就不用使用MAX()函数了

本文出自 “凡夫俗子” 博客，请务必保留此出处http://fanfusuzi.blog.51cto.com/9978141/1674267