数据库分库分表及MySQL主从复制实现数据库同步

一、为什么要分库分表
解决大数据存储时数据访问性能，具体来说就是解决超大容量问题和性能问题。

举例说明，订单表或用户表如果数据量达到上亿条记录，此时数据库的IO能力、处理能力就会出现一个瓶颈（MySQL官方统计单表数据量超过1000万性能会逐渐下降）。另一方面，单一数据库本身的CPU、内存、磁盘、IO都有性能极限。

二、如何分库分表
垂直分库：解决数据表过多问题，按照功能模块、业务维度、ER图、领域模型等把相关联的表部署在一个数据库上。

垂直分表：解决单表列数过多或大字段性能问题问题，按照列划分为几个小的数据表。如果一张表中有一个大字段，而且并不是必须要展示的或者不是当前需要用的，那么虽然没有刻意去查询，但是在根据id或者其他索引进行查询的时候就会把大字段一起查出来，会严重影响查询的性能，所以才有的垂直分表。
 

 

水平分表：解决数据量过大问题，把数据存储到结构相同的不同表中。比如3000万订单数据，分别存放到三张表中，每张表存放1000万数据。

三、常见拆分策略
垂直拆分：按照功能模块、业务维度、ER图、领域模型等进行表划分，将相关联的表放到统一数据库中，避免跨库关联join

水平拆分：

1）一致性Hash：例如订单表，可以按照订单userid字段进行哈希取模算法，路由映射到不同表或数据库中。一致性Hash数据存储具有随机性。

2）范围切分：按照某个字段（例如ID）在某个维度区间将数据路由映射到不同表或数据库中。例如ID=0~100000之间的数据保存奥数据库DB1，ID=100001~200000之间的数据保存至数据库DB2。范围切分能保证数据是连续的。

3）日期拆分：例如当前一年的业务数据存放到生产库，将一年前的数据备份至备份库中。

四、拆分以后带来的问题
1）跨库join的问题，select a.x,b.y from user a join merchant b on a.id=b.userid

解决方法：

设计的时候充分考虑到应用层的join问题，尽量避免跨库join；

关联表数据可以通过服务层去远程RPC调用，例如上述sql语句，可以先本地查询出a表数据，然后通过远程RPC调用获取关联b表数据；

创建全局表，即在每个数据库中都创建相同的表，数据变更较少的基于全局应用的表；

做字段冗余，用空间换时间，例如在订单表中保存商户id，商户名称。

2）跨分片数据排序分页

解决方法：

在应用层进行数据拼接，对每个表中的数据进行查询，然后按照排序字段再进行数据拼接。

3）唯一主键问题，例如用自增ID做主键，分库后必然会出现重复主键

解决方法：

用UUID作为主键，UUID字符串比较大，造成生成的索引较大，性能较低；

利用Snowflake雪花算法生成主键，根据时间序列、机器标识、技术顺序号，按照指定算法生成唯一ID；

借助MongoDB的ObjectId作为唯一主键；

借助zookeeper自动生成递增ID作为唯一主键。

4）分布式事务

多个数据库表之间保证原子性

五、MySQL主从复制实现数据库同步
绝大部分应用是一个写少读多的操作，只读数据库会从主数据库同步数据。MySQL数据库的读写分离，通常做法是采用MySQL主从复制实现数据库同步，由从数据库提供只读操作。这里以windows环境下简要说明MySQL主从配置，实现主从数据库的数据同步。MySQL数据库版本mysql-5.6.32-winx64，主数据库端口号3306，从数据库端口号3307。

1）主库（master）的配置

进入主库mysql-5.6.32-winx64目录中，在此目录中新建my.ini文件并添加一下配置。

[client]
port=3306
default-character-set=utf8
[mysqld]
#主库配置
server-id=1
log-bin=mysql-bin
log-bin-index=mysql-bin.index
#端口
port=3306
character_set_server=utf8
#设置数据库引擎为INNODB
default-storage-engine=INNODB
#设置mysql服务器字符集
collation-server=utf8_general_ci
#解压目录
basedir=D:\mysql-5.6.32-winx64
#解压目录下data目录
datadir=D:\mysql-5.6.32-winx64\data

2）从库（slave）的配置

进入主库mysql-5.6.32-winx64目录中，在此目录中新建my.ini文件并添加一下配置。

[client]
port=3307
default-character-set=utf8
[mysqld]
#主库配置
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
read_only=1
#端口
port=3307
character_set_server=utf8
#设置数据库引擎为INNODB
default-storage-engine=INNODB
#设置mysql服务器字符集
collation-server=utf8_general_ci
#解压目录
basedir=D:\mysql-5.6.32-winx64
#解压目录下data目录
datadir=D:\mysql-5.6.32-winx64\data

3）关联主库（master）与从库（slave）

主从数据库启动成功后，分别执行show master status;和show slave status\G;查看主从数据库状态。此时主库（master）下生成了一个二进制的日志文件，而slave下是空的，所以就要把主库与从库关联起来，即只需要让从库（slave）知道主库（master）的地址就可以了。在从库（slave）执行如下命令，将主库与从库联系起来，然后执行命令start slave开启主从同步。

master_host='localhost',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120;

3）主从同步原理

 

1、master记录二进制日志。在每个事务更新数据完成之前，master会在二进制日志中记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

2、slave将master的binary log拷贝到它自己的中继日志。slave首先会开始一个工作线程即I/O线程，I/O线程在master上打开一个普通的连接，之后开始binlog dump process。binlog dump process从master的二进制日志中读取事件并写入中继日志，如果已经跟上master，它会睡眠并等待master产生新的事件。

3、SQL线程从中继日志读取事件，并重放其中的事件去更新slave的数据，使其与master中的数据一致。