一、分库分表方案

数据库架构演变

刚开始我们只用单机数据库就够了，随后面对越来越多的请求，我们将数据库的写操作和读操作进行分离， 使用多个从库副本（Slaver Replication）负责读，使用主库（Master）负责写， 从库从主库同步更新数据，保持数据一致。架构上就是数据库主从同步。 从库可以水平扩展，所以更多的读请求不成问题。

但是当用户量级上来后，写请求越来越多，该怎么办？加一个Master是不能解决问题的， 因为数据要保存一致性，写操作需要2个master之间同步，相当于是重复了，而且更加复杂。

这时就需要用到分库分表（sharding），对写操作进行切分。

分库分表前的问题

任何问题都是太大或者太小的问题，我们这里面对的数据量太大的问题。

用户请求量太大

因为单服务器TPS，内存，IO都是有限的。

解决方法：分散请求到多个服务器上； 其实用户请求和执行一个sql查询是本质是一样的，都是请求一个资源，只是用户请求还会经过网关，路由，http服务器等。

单库太大

单个数据库处理能力有限；单库所在服务器上磁盘空间不足；

单库上操作的IO瓶颈 解决方法：切分成更多更小的库

单表太大

CRUD都成问题；索引膨胀，查询超时

解决方法：切分成多个数据集更小的表。

分库分表的方式方法

一般就是垂直切分和水平切分，这是一种结果集描述的切分方式，是物理空间上的切分。

我们从面临的问题，开始解决，阐述： 首先是用户请求量太大，我们就堆机器搞定（这不是本文重点）。

然后是单个库太大，这时我们要看是因为表多而导致数据多，还是因为单张表里面的数据多。

如果是因为表多而数据多，使用垂直切分，根据业务切分成不同的库。

如果是因为单张表的数据量太大，这时要用水平切分，即把表的数据按某种规则切分成多张表，甚至多个库上的多张表。 

分库分表的顺序应该是先垂直分，后水平分。 因为垂直分更简单，更符合我们处理现实世界问题的方式。

垂直拆分

  1. 垂直分表
    也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。
  2. 垂直分库
    垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。
    数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平拆分

  1. 水平分表
    针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。
  2. 水平分库分表
    将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
  3. 水平分库分表切分规则
  1. RANGE
    从0到10000一个表，10001到20000一个表；
  2. HASH取模
    一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
  3. 地理区域
    比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
  4. 时间
    按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

分库分表后面临的问题

事务支持

分库分表后，就成了分布式事务了。

如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

多库结果集合并（group by，order by）

TODO

跨库join

TODO 分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。粗略的解决方法： 全局表：基础数据，所有库都拷贝一份。 字段冗余：这样有些字段就不用join去查询了。 系统层组装：分别查询出所有，然后组装起来，较复杂。

分库分表方案产品

目前市面上的分库分表中间件相对较多，其中基于代理方式的有MySQL Proxy和Amoeba， 基于Hibernate框架的是Hibernate Shards，基于jdbc的有当当sharding-jdbc， 基于mybatis的类似maven插件式的有蘑菇街的蘑菇街TSharding， 通过重写spring的ibatis template类的Cobar Client。

还有一些大公司的开源产品：

MySQL的分库分表 mysql分库分表原理_MySQL的分库分表

数据库水平分表策略：一致性哈希算法

二、分库分表带来的完整性和一致性问题

在最近做的一个项目中，由于每天核算的数据量过于庞大，需要把数据库进行分库保存。当数据分散到各个库之后，带来的数据更新操作就会存在一个一致性和完整性的问题。下面是一个典型的场景

假设目前存在三个物理库，现在有一个文件，里面有1W条数据，根据分库的规则，可以把文件里面的数据分到三个库中，现在需要保证这1W条数据要要完整的保存到这三个库里面，并且数据是一致性的，也就是说 三个库里面已导入的数据完全和文件里面的数据一致。

正常情况下，我们先把文件里面的数据按照所属的数据库分成三份，然后针对每一份数据库进行保存，在单库的情况下，可以保证单库的数据完整性。但是三个库要保证一致性，就是非常复杂的一项工作，很有可能第一个库的数据保存成功了，但是后面三个库的数据保存失败了，导致整个文件的里面的数据在数据库里面不完整。

如何解决这种问题，目前想到的有几个办法：

方案1

 使用类似JTA提供的分布式事物机制，也就是说需要相关的数据库提供支持XA的驱动。（ XA 是指由 X/Open 组织提出的分布式交易处理的规范）。这个需要依赖特定的数据库厂商，也是比较简单的方案。毕竟复杂的事务管理都可以通过提供JTA服务的厂商和提供XA驱动的数据库厂商来完成。目前大多数实现了JTA的服务器厂商比较多，比如JBOSS，或者开源的JOTM(Java Open Transaction Manager)——ObjectWeb的一个开源JTA实现。但是引入支持XA的数据库驱动会带来很多潜在的问题，在 《java事务设计策略》里面：在Java事务管理中，常常令人困惑的一个问题是什么时候应该使用XA，什么时候不应使用XA。由于大多数商业应用服务器执行单阶段提交（one-phase commit）操作，性能下降并非一个值得考虑的问题。然而，非必要性的在您的应用中引入XA数据库驱动，会导致不可预料的后果与错误，特别是在使用本地事务模型（Local Transaction Model）时。因此，一般来说在您不需要XA的时候，应该尽量避免使用它。”  所以这个是一个可选的方案，也是最简单的一个方案

 

方案2

建立一张文件批次表（放在一个独立的数据库里面），保存待处理的文件批次信息（不是明细数据，简单说的就是要处理的文件名和所在路径），在每次处理文件数据的时候，先往表里面插入一条文件批次信息，并且设置文件的状态为初始状态，在文件中的数据全部成功的保存到三个分库里面之后，在更新文件的批次状态为成功。如果保存到分库的过程中出现异常，文件批次的状态还是初始状态。而后台启动一个定时机制，定时去扫描文件批次状态，如果发现是初始状态，就重新执行文件的导入操作，直到文件完全导入成功。这个方案看起来没有问题，但是可能存在重复导入的情况，比如批次导入到第一个分库成功了，后面两个库失败了，重新导入的话，可能会重复把数据重复导入第一个分库。我们可以在导入之间进行判断，如果导入过，就不进行导入，但是极端的情况，我们无法判断数据是否导入过，也是一个有缺陷的方案，并且如果每次导入之前，都进行数据是否导入的操作，性能会有一些影响。我们也可以通过异常恢复机制来进行，如果发现文件导入失败了，我们删除已经导入入库的流水，但是这也引入了错误处理带来的一致性问题，比如我们已经导入成功2个分库的数据，在导入第三个分库失败的情况下，要删除掉前面两个分库的数据，这也没有办法保证是一致的。

在这个方案里面，我们可以在进行一定的优化，让它看起来运作起来是没有问题的。首先再建立一张子批次表（和文件批次表放在同一个库），在进行处理的时候，我们把大的文件的数据按照分库规则拆成三个子文件，每一个子文件里面的数据对应一个分库。这样就产生三条子批次信息，由于文件批次信息和子批次信息 在同一个库里面，可以保证一致性。这样每个待处理的文件就分成了四条记录，一条主文件批次信息，三条子批次信息，在导入数据之前，这些批次的信息的状态都是初始状态。这样一个文件的导入就分解为三个子文件，分别导入到对应库里面去。对于每个子文件批次，我们可以保证子文件数据的都是在同一个库里面，保证每个子文件里面数据的一致性和完整性，然后导入成功之后，在更新子批次的状态为成功，如果所有的子文件的批次状态都为成功，那么对应的文件批次状态就更新为成功。这样看起来非常完美，解决了问题。但是仔细考虑一下，有一个小的细节问题：子批次信息和一个独立的库，要导入的数据是和子批次信息可能不再一个库，没有办法保证这两个操作是一致性的，也就是说 子文件里面的数据成功的导入到分库，但是可能子批次信息状态没有更新。那子批次信息能不能放在每个分库里面了，这样的话，又回到刚开始提出的问题了（这里面就不解释，可以去自己去想想）。

下面一副图简单的演示的设计思想：

MySQL的分库分表 mysql分库分表原理_读写分离_02

 

MySQL的分库分表 mysql分库分表原理_分库分表_03

 

方案3

第2个方案的基础上，可以继续加以优化。首先我们保留第二个方案的文件批次信息表和子文件批次信息表，而且我们必须把这两个表放在同一个库里面（这里假设分配到主库），保证我们拆分任务时的一致性。然后在各个分库里面，我们建立一张各个分库的子文件批次表。这个表模型基本上是和主库的子文件批次信息表一样。当拆分任务的时候，先保证主库数据的完整性，也就是产生了一条文件批次信息记录和三条子文件批次记录，然后把这三条子文件批次信息分别复制到对应的分库中的子文件批次信息表里面，然后更新主库的子文件批次信息状态为“已同步”。当然，这个过程是无法保证一致性的。解决方案启动一个定时任务，定期的把主库重点的子文件批次表信息中初始状态的记录 同步到各个分库的子文件批次表里面，这里面可能导致两种情况

1 分库子批次信息表已经存在相同的信息（这个可以通过唯一性主键保证），说明已经同步，直接更新主库的子文件批次信息状态为 “已经同步”

2 分库子批次信息不存在，则往对应的分库insert一条数据，然后更新主库的子文件批次信息状态为 “已经同步”

然后各个分库 就是先导入子文件中的数据，在更新分库的子文件批次表的状态为处理成功 ，这两个操作由于都是分库的上的操作，可以保证一致性。最后，在更新主库的子批次信息表的状态为 “处理成功”。同样，更新主库的子批次信息状态如果失败，可以采取类似的定时机制，同步分库子文件批次信息表和主库的子文件批次信息表的状态。通过这种努力重试型机制，保证了主库中的子文件批次表和分库的子文件批次表是一致的。等所有的主库子文件批次信息表状态全部更新为“处理成功”，则文件批次状态就更新为“处理成功”。

相比第二个方案，我们在两个库里面增加了数据的同步，用这种机制，保证了主库分库数据的一致性。

这里简单的介绍一下第二个方案的简单实现细节：

首先是数据库之间表结构关联关系

MySQL的分库分表 mysql分库分表原理_读写分离_04

 

 下面用脚本的方式简单的演示一下这个过程

我们假设有四个库，一个主库MAIN,三个字库SUB1,SUB2,SUB3

MAIN库两张表：

FILE_BATCH_NO,主要关注status状态 I(初始）->S（成功）

SUB_BATCH_NO,主要关注status状态 I(初始）->R（同步成功）->S(处理成功)

SUB库两张表

DATA_DEAIL:保存明细数据，也就是业务逻辑主要处理的表

SUB_BATCH_NO:主要关注status状态,I(初始)->S(处理成功)

 1 拆分文件批次的过程

begin
 
declare file_name,batch_no,sub_batch_no;
 
insert into MAIN.FILE_BATCH_INFO(id,file_name,batch_no,status) values(seq.FILE_BATCH_INFO,#file_name#,#batch_no#,'I');
 
insert into MAIN.SUB_BATCH_INFO(id,file_name,main_batch_no,status) values(seq.SUB_BATCH_INFO,#file_name#,#batch_no#,#sub_batch_no#,'I');
insert into MAIN.SUB_BATCH_INFO(id,file_name,main_batch_no,status) values(seq.SUB_BATCH_INFO,#file_name#,#batch_no#,#sub_batch_no#,'I');
insert into MAIN.SUB_BATCH_INFO(id,file_name,main_batch_no,status) values(seq.SUB_BATCH_INFO,#file_name#,#batch_no#,#sub_batch_no#,'I');
 
commit;
 
end;

　

2 同步MAIN库的子批次信息到分库的各个SUB库中对应的子批次信息表,同步成功，更新MAIN库对应的子批次信息状态为同步成功。

##分库的操作，从MAIN库SUB_BATCH_INFO表中获取对应的数据插入到SUB1库里面
begin transaction in SUB1
declare file_name,batch_no,sub_batch_no;
 
select SUB_BATCH_INFO.ID into SUB_ID from  MAIN.SUB_BATCH_INFO where SUB_BATCH_INFO.DATA_BASE = SUB1
//判断分库数据是否存在，存在就返回true
if(select * from SUB1.SUB_BATCH_INFO where SUB_ID = SUB_BATCH_INFO.ID)
return SUCCESS
insert into SUB1.SUB_BATCH_INFO(id,file_name,main_batch_no,status) values(SUB_ID,#file_name#,#batch_no#,#sub_batch_no#,'I');
commit;
 
end;
 
 
##SUB1库的操作完成之后，开始进行MAIN库SUB_BATCH_INFO表对应的update操作
begin transaction in MAIN
 
declare SUB_ID;
## R代表已经同步的状态,这里面可以判断status的状态，不过意义不大
update  MAIN.SUB_BATCH_INFO set status ='R' where ID = SUB_ID
 
commit;
end;

　上面只是一个SUB库的操作，如果有多个库，循环进行操作。如果某一个库没有同步成功，有定时恢复机制。定时恢复机制的对应的SQL就是从MAIN中提取出是状态的SUB_BATCH_INFO记录，重复进行上述处理的过程

3 SUB库处理子批次信息，对流水进行保存，然后更新SUB库对应的SUB_BATCH_INFO记录状态为处理成功。然后在更新MAIN库的对应的SUB_BATCH_INFO记录状态为成功。

+ View Code

　这里的情况一样，就是SUB库和MAIN库也存在状态同步的问题，这里也需要一个定时对MAIN库的　SUB_BATCH_INFO表状态进行同步更新

4 判断MAIN库对应的SUB_BATCH_INFO所有状态是否已经为成功，如果成功，更新MAIN库的FILE_BATCH_NO 的状态为成功。

在这四个过程中，需要三个定时器。有两个定时器保证MAIN库和SUB库之间的数据一致性问题，另外一个定时器负责异步更新MAIN库 批次和子批次的一致性问题。

对于第三个方案，可以抽取出通用的逻辑，来解决后续类似的场景。比如根据条件，删除各个分库中满足条件的流水，或者批量更新各个分库中满足条件的流水。我们可以把这些作为一个任务来抽象出来，一个具体的任务由N个子任务组成（N为分库的个数），系统要保证N个子任务要么全部成功，要么全部失败，不允许部分成功。我们可以在方案三的思想上，建立总任务表和子任务表，文件导入的处理只是其中的一个任务类型而已，批量删除，批量更新以及其他类似的操作，都可以当做具体的任务类型。

4 第四种方案就是经典的分布式事务设计中的 两阶段提交思想。两阶段提交的有三个重要的子操作：准备提交，提交，回滚。

继续拿文件导入来举例子，各个分库作为一个事务参与者 ， 我们需要设计各个分库的准备提交操作，提交，回滚操作。

准备提交阶段：各个分库可以把要处理的文件明细保存到一张临时表里面，并且记住这一次事务中上下文信息。

提交阶段：把这一次事务上下文中对应的临时表数据同步到对应的明细表中

回滚阶段：删除本次事务相关的临时表流水信息。

通过设计一个两阶段的提交的事务管理器，我们可以在导入文件的时候启动一个分布式事务，生成一个事务上下文（这个上下文信息要保存到数据库里面），然后在调用各个子参与者的时候，需要把这个上下文信息传递下去，分库先进行准备工作（就是保存明细到临时表），如果成功，就返回准备成功。等所有的参与者成功了，事务管理器就提交这个事务，这个分库完成提交动作，把数据从临时表插入到正式表。如果某一个准备操作失败，所有的分库执行回滚操作，删除导入的流水。

这里面最重要的就是，如果某分库准备阶段返回成功，那么提交一定要成功，否则只能做数据订正或者人工处理了。这个是在两阶段中事务中没有办法解决。

对于不同的操作，要设计对应的准备提交，提交，回滚操作，开发量比较大，而且分布式事务管理器的实现也需要一定的功底。

MySQL的分库分表 mysql分库分表原理_mysql_05

 

上面四种方案，能够保证完整性和一致性的只有第三种和第四种方案。其实这两种方案的设计思想是一致的。就是通过努力重试以及异步确认进行的。严格的说，第三种方案会有一定的问题，因为在整个处理过程中，只能保证最终一致性，而没有办法保证ACID里面的孤立性。因为存在部分提交的情况，而这一些数据有可能后续会进行回滚。不过可以就第三种方案在进行优化，加上一个锁机制，不过扩展下来就比较复杂了。

三、主从复制及读写分离原理

1.MySQL主从复制与读写分离原理

首先，我们看一个图：

MySQL的分库分表 mysql分库分表原理_分库分表_06

影响MySQL-A数据库的操作，在数据库执行后，都会写入本地的日志系统A中。

 

假设，实时的将变化了的日志系统中的数据库事件操作，在MYSQL-A的3306端口，通过网络发给MYSQL-B。

 

MYSQL-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库中完成。

 

那么，MYSQL-A的变化，MYSQL-B也会变化，这样就是所谓的MYSQL的复制，即MYSQL replication。

 

在上面的模型中，MYSQL-A就是主服务器，即master，MYSQL-B就是从服务器，即slave。

 

日志系统A，其实它是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即bin log。【注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】

 

日志系统B，并不是二进制日志，由于它是从MYSQL-A的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即relay log。

 

可以发现，通过上面的机制，可以保证MYSQL-A和MYSQL-B的数据库数据一致，但是时间上肯定有延迟，即MYSQL-B的数据是滞后的。

【即便不考虑什么网络的因素，MYSQL-A的数据库操作是可以并发的执行的，但是MYSQL-B只能从relay log中读一条，执行下。因此MYSQL-A的写操作很频繁，MYSQL-B很可能跟不上。】

 

 

2.主从复制的几种方式

 

同步复制

所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。

这样，显然不可取，也不是MYSQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

 

 

异步复制

如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。

 

 

半同步复制

master只保证slaves中的一个操作成功，就返回，其他slave不管。

这个功能，是由google为MYSQL引入的。

 

 

3.主从复制分析

问题1：master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？

假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。实际上，这里已经揭示了读写分离的概念。

问题2：主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要他们干嘛？

可以实现数据备份。

类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。

异地容灾，比如master在北京，地震挂了，那么在上海的slave还可以继续。

主要用于实现scale out,分担负载,可以将读的任务分散到slaves上。

【很可能的情况是，一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves进行操作】

 

问题3：主从复制中有master,slave1,slave2,...等等这么多MYSQL数据库，那比如一个JAVA WEB应用到底应该连接哪个数据库?

当 然，我们在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection(for master)进行操作，select用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select，例如简单的轮循算法。

这样的话，相当于应用程序完成了SQL语句的路由，而且与MYSQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应用程序就要修改了。能不能让应用程序与MYSQL的主从复制架构没有什么太多关系呢？可以看下面的图：

MySQL的分库分表 mysql分库分表原理_数据_07

找一个组件，application program只需要与它打交道，用它来完成MYSQL的代理，实现SQL语句的路由。

mysql proxy并不负责，怎么从众多的slaves挑一个？可以交给另一个组件(比如haproxy)来完成。

这就是所谓的MYSQL READ WRITE SPLITE，MYSQL的读写分离。

问题4：如果mysql proxy , direct , master他们中的某些挂了怎么办？

总统一般都会弄个副总统，以防不测。同样的，可以给这些关键的节点来个备份。

问题5：当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？

如果只发一次，发给了slave-1，那slave-2,slave-3,...它们怎么办？

显 然，应该发N次。实际上，在MYSQL master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作，还的维护N个线程，负担会很重。可 以这样，slave-1是master的从，slave-1又是slave-2,slave-3,...的主，同时slave-1不再负责select。 slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。

问题6：当一个select发往mysql proxy，可能这次由slave-2响应，下次由slave-3响应，这样的话，就无法利用查询缓存了。

应该找一个共享式的缓存，比如memcache来解决。将slave-2,slave-3,...这些查询的结果都缓存至mamcache中。

问题7：随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办呢？

scale on ?更好的服务器？ 没有最好的，只有更好的，太贵了。。。

scale out ? 主从复制架构已经满足不了。

可以分库【垂直拆分】，分表【水平拆分】。