数据库分库分表及MySQL主从复制

Ysming88

已于 2022-02-06 22:17:37 修改

阅读量1.3k

点赞数

分类专栏： Mysql 文章标签：数据库 mysql 服务器

于 2021-12-11 15:47:41 首次发布

本文链接：https://blog.csdn.net/m0_45861545/article/details/121844353

版权

Mysql 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

本文详细介绍了数据库分库分表的原因、方法及其带来的挑战，包括垂直切分和水平切分，以及如何处理事务、跨库join和数据排序分页问题。同时，探讨了MySQL主从复制的实现，用于数据库同步和读写分离，以应对高并发场景。文章还提及了主从同步的基本原理和配置步骤。

摘要由CSDN通过智能技术生成

一、为什么要分库分表

解决大数据存储时数据访问性能，具体来说就是解决超大容量问题和性能问题。

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

单库太大
单个数据库处理能力有限；单库所在服务器上磁盘空间不足；单库上操作的IO瓶颈。
解决方法：切分成更多更小的库

二、如何分库分表

一般就是垂直切分和水平切分，这是一种结果集描述的切分方式，是物理空间上的切分。
我们从面临的问题，开始解决，阐述：首先是用户请求量太大，我们就堆机器搞定（这不是本文重点）。
然后是单个库太大，这时我们要看是因为表多而导致数据多，还是因为单张表里面的数据多。
如果是因为表多而单库数据多，使用垂直切分，根据业务将表切分到不同的库。
如果是因为单张表的数据量太大导致单库数据多，这时要用水平切分，即把表的数据按某种规则切分成多张表，甚至多个库上的多张表。

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

垂直拆分

1.垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

2.垂直分库

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。切分后，要放在多个服务器上，而不是一个服务器上。为什么？我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前，全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上，随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps（系统吞吐量）等非常吃紧。所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平拆分

1.水平分表（单库下）

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。

2.水平分库分表（多库下）

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

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

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

3.地理区域
比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

4.时间切分
按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

三、分库分表后面临的问题

1.事务支持
分库分表后，就成了分布式事务了，多个数据库表之间需要保证原子性。
如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担，所以需要分布式事务的解决方案。

2.跨库join的问题 select a.x,b.y from user a join merchant b on a.id=b.userid
分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
解决方法：

设计的时候充分考虑到应用层的join问题，尽量避免跨库join；
关联表数据可以通过服务层去远程RPC调用，例如上述sql语句，可以先本地查询出a表数据，然后通过远程RPC调用获取关联b表数据；
创建全局表，即基础数据在每个数据库中都创建相同的表，数据变更较少的基于全局应用的表；
做字段冗余，用空间换时间，例如在订单表中保存商户id，商户名称。

3.跨分片数据排序分页
解决方法：
在应用层进行数据拼接，对每个表中的数据进行查询，然后按照排序字段再进行数据拼接。

4.唯一主键问题
例如用自增ID做主键，分库后必然会出现重复主键。
解决方法：

用UUID作为主键，UUID字符串比较大，造成生成的索引较大，性能较低；
利用Snowflake雪花算法生成主键，根据时间序列、机器标识、技术顺序号，按照指定算法生成唯一ID；
借助MongoDB的ObjectId作为唯一主键；
借助zookeeper自动生成递增ID作为唯一主键。

四、MySQL主从复制实现数据库同步

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

但是当用户量级上来后，写请求越来越多，该怎么办？加一个Master是不能解决问题的，因为数据要保存一致性，写操作需要2个master之间同步，相当于是重复了，而且更加复杂。这时就需要用到上面讲的分库分表（sharding），对写操作进行切分了。

绝大部分应用是一个写少读多的操作，只读数据库会从主数据库同步数据。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记录二进制日志binlog。在每个事务更新数据完成之前，master会在二进制日志中记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

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

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

参考博客：https://blog.csdn.net/wangpf2011/article/details/84495059
https://www.cnblogs.com/aksir/p/9085694.html