浅谈mysql分表

关于分表：顾名思义就是一张数据量很大的表拆分成几个表分别进行存储。

我们先来大概了解以下一个数据库执行SQL的过程：

接收到SQL --> 放入SQL执行队列 --> 使用分析器分解SQL --> 按照分析结果进行数据的提取或者修改 --> 返回处理结果。在这个过程中一般比较花时间的是在队列里的等待时间和执行时间。归根到底就是执行时间，执行时间减少了等待时间自然就变短了。

为了保证数据的完整性，数据库有锁定机制。MySQL中有表锁定和行锁定，MySQL中myisam存储引擎是表锁定，innodb存储引擎是行锁定。分为包含共享锁和独占锁两种。独占锁就是整个数据文件归一个线程所有，其他线程就必须等待。如果数据太多，一次执行的时间太长，特别是在锁表的情况下，就会导致大量的其他SQL等待执行，严重影响系统的正常使用。

另外更新表数据时会导致索引更新，当单表数据量很大时这个过程比较耗时，这就是为什么对大表进行新增操作会比较慢的原因。并且更新表数据会进行表级锁或者行锁，这样就导致其他操作等待。

所以我们将大表拆分为多个字表，那么在更新或者查询数据的时候，压力会分散到不同的表上。由于分表之后每个表的数据较小，不管是查询还是更新都极大的提高了速度，即使出现最坏的“锁表”的情况，那其他表还是可以并行使用。

分表的几种常见策略：

（1）预先估计某个大表的数据量，按实际情况将其均分为固定数量表

根据分表算法，将数据平均分散到不同的数据表中，常见处理方式有对自增id取模、对某个字段进行hash。比如某系统用户预计支持1亿用户数，分100个表存储用户数据，按照自增id的最后2位来分表，对100取模，那么用户数据表就是user_01~user_99。

（2）按时间拆分

对于那种根据时间增长较快的数据可以按时间拆分，根据业务实际情况按天、按月、按年等进行拆分。比如进销存数据，我们可以按月分表，形如jxc_data_201201、jxc_data_201202

（3）按每个表固定记录行数拆分

一般根据自增长ID拆表，每张表存储指定数量的数据。一张表的数据行数到了指定数量，就自动保存到新的表里。

（4）将很久之前的数据迁移到一张历史表

比如日志记录，一般只会查询3个月之内的日志，对于超过三个月的日志记录我们可以迁移到到迁移到另一张表中，比如log_history

（5）分表之后的处理

前面已经说过分表的好处，但哪有那么十全十美的事情呢，这个也不例外。分表之后，麻烦的事情来了，业务数据分散在各个分表中， 之前的业务功能如何保证呢？比如说我要插入一条记录、更新一条记录、删除一条记录、查询统计数据，现在要怎么处理呢。

以用户信息维护功能为例，假设我们有一张表user保存所有的用户信息，拆分到100张表里。我们按照用户表自增id两位尾数取模分表，那么我们的用户表应该是user_01~user_99。

先看新增操作，这时记录还不存在，首先需要获取用户的唯一id，我们可以新建一张表来生成用户的唯一id

CREATE TABLE `seq_user_id` (  

    `id` BIGINT( 20  ) NOT NULL AUTO_INCREMENT PRIMARY KEY  

) ENGINE = MYISAM;

那么要新增用户的时候，先插入一条记录到seq_user_id拿到用户id

有了用户id之后，我们需要知道这个用户数据要插入到哪一张用户表里去。

根据我们的拆表规则计算出实际存储的表名，我们可以写这样一个函数来实现

function get_table_name($id) {  

    return 'user_'.intval($id)%100;  

}

这个时候我们就可以进行INSERT操作了。

更新和删除用户信息操作类似了，只是少了生成id的步骤，我们直接根据已有用户的id获取表名再来执行相应的UPDATE和DELETE动作。

如果我们要查询某个用户的信息，使用get_table_name获取实际存储该用户信息的表，然后进行SELECT操作即可完成。

从这个例子看出，我们只要更具分表规则获取到实际的数据表即可，其他与改造之前并没有太多的不一样，好像挺方便的。

但麻烦的事情来了，现在有一个需求要统计2011年12月到2012年3月这段时间注册的用户数，即需要根据时间段来查询用户信息。

想一下，用户信息现在是分散在不同的数据表里了，要怎么做？

联合这么多表一起查询么？想想都觉得恐怖；

又或者是分别到这100张表里分询这个时间段的用户数，然后再累加么？好像也很麻烦

如何你用的user分表的存储引擎是MyISAM，那么恭喜你，这里有一种很简单的处理方法。

利用merge存储引擎将拆分的表合并成一张表

 MERGE存储引擎可以将N个子表联合在一起,看成是一个整表,实际上还是N个真实的子表。

在我们查询这个merge表就相当于查询所有字表的数据了，非常方便。当然merge表还是有一定的限制的，具体请查看mysql官方手册。

这里引用MYSQL参考手册中的片段来说明如何操作

下面例子说明如何创建一个MERGE表：

mysql> CREATE TABLE t1 (

    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    ->    message CHAR(20));

mysql> CREATE TABLE t2 (

    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    ->    message CHAR(20));

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (

    ->    a INT NOT NULL AUTO_INCREMENT,

    ->    message CHAR(20), INDEX(a))

    ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

注意，一个列在MERGEN表中被索引，但没有被宣告为一个PRIMARY KEY，因为它是在更重要的MyISAM表中。这是必要的，因为MERGE表在更重要的表中的设置上强制非唯一性。

创建MERGE表之后，你可以发出把一组表当作一体来操作的查询：

mysql> SELECT * FROM total;

+---+---------+

| a | message |

+---+---------+

| 1 | Testing |

| 2 | table   |

| 3 | t1      |

| 1 | Testing |

| 2 | table   |

| 3 | t2      |

+---+---------+

根据这个方法的介绍，我们可以创建一张user的合并表，然后对这张表进行查询即可达到查询分表所有的数据的效果。

当然如果表存储引擎不是MyISAM的话就不能使用这个方法了，分别查询之后合并查询结果。

这里有几点个人建议供参考：

根据业务需求，选择合适的分表方式，尽量减少需要一次查询几个分表的操作

分别对不同的分表进行操作，最后在应用层合并数据。网上有介绍使用多线程分别查询各部分的数据最后合并的，效果应该不错，操作起来有麻烦。

对一些实时性要求不高的数据使用缓存

实例：一步一步实现大表数据分表处理

假设现在系统里有一个进销存日结的数据表，目前一般只需要查询某个月的数据，不用跨月查询，那么这里我们就按月来分表。如果实际业务还是很多地方需要对多张拆分出来的表进行合并查询的，就要重新考虑一下分表的方式了，可以按其他时间段或其他字段来拆分表。

这样每个月的开始都要进行一个操作建立一张以月为单位的新表来存储过去一个月的数据。即每月都要建立一张表比如 jxc_2011_04,jxc_2011_05,jxc_2011_06……

这里有一个小处理，有一张表的表明始终不变，用来保存当前月的数据，这样做的好处是我们的应用程序保存进销存数据时始终是操作jxc这种表，应用的默认查询当月进销存的功能也不用改动，始终是查询的当前月的数据。当然需要查询非当前月的数据时就需要通过时间进行定位实际存储数据的表，当然如果无法避免跨月查询，这个时候就需要应用程序进行合并处理了。

现在来看看实际如何操作，假如我们每天凌晨3点生成前一天的数据到jxc表，比如到2012-07-01的时候，就会进行执行如下操作：

create table struct_jxc like jxc;     -- 

rename table jxc to jxc_2012_06, struct_jxc to jxc;

这样就将jxc表的数据即6月的数据全部迁移到jxc_2012_06表中，jxc现在就是空表，那么从7月1号开始数据都是保存到jxc表中，依次8月1号的时候再进行类似的操作，即可实现了按月分表。

这里用rename的好处就是不需要在进行转移大量数据的时候进行导出和导入的操作，速度会快很多。

写在后面：

关于mysql分表也在学习研究之中，一些问题也没有完善的处理方案，如果你有什么的建议可以告诉我。留下几个问题，希望能起到抛砖引玉的效果。

1、你的业务数据更适合什么分库策略？

2、随着系统使用年限的增加，如果发现最初分配的100张表还是难堪重负，要怎么办？

3、由于数据分布在不同的表中，如何高效的进行负责的查询分析？

4、如果拆分出来的表分布在不同的服务器上，事务又该如何保证？