Mysql高级部分学习笔记（三）——读写分离和分库分表

一、读写分离

1.介绍

读写分离主要是为了将数据库的读和写操作分布到不同的数据库节点上。主服务器负责写，从服务器负责读。

读写分离可以大幅提高读性能，小幅提高写的性能。因此，读写分离更适合单机并发读请求比较多的场景。

总结四个字：主从同步（通过binlog进行一致性通信）

主从复制原理

MySQL binlog(binary log 即二进制日志文件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)。因此，我们根据主库的 MySQL binlog 日志就能够将主库的数据同步到从库中。

架构场景分类：

单机房场景下读写分离：

多机房场景下读写分离：

从以上两种类型的架构图，我们可以分析出如下几个使用读写分离的原因：

1. 读写量很大，为了提升数据库读写性能，将读写进行分离；
2. 多机房下如果写少读多，同时基于数据一致性考虑，只有一个主库存入所有的数据写入，本地再做从库提供读取，减少多机房间直接读取带来的时延。

读写分离会带来什么问题？如何解决？

问题：写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的 主从同步延迟 。

解决方法：

1. 强制将读请求路由到主库处理。（通过使用 Sharding-JDBC 工具）
2. 延迟读取。（既然主从同步存在延迟，那我就在延迟之后读取啊，比如主从同步延迟 0.5s,那我就 1s 之后再读取数据。）

2. 实现读写分离的原理与方案

1、基于MySQL proxy代理的方式

在应用和数据库之间增加代理层，代理层接收应用对数据库的请求，根据不同请求类型转发到不同的实例，在实现读写分离的同时可以实现负载均衡。

实现原理：

开源方案

MySQL的代理最常见的是mysql-proxy、cobar、mycat、Atlas等。这种方式对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多 个proxy的连接参数即可。

• mysql-proxy是一个轻量的中间代理，是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等，依靠内部一个lua脚本实现读写语句的判断。项目地址： https://github.com/mysql/mysql-proxy ，该项目已经六七年没有维护了，官方也不建议应用于生成环境。
• cobar是阿里提供的一个中间件，已经停止更新。项目地址：https://github.com/alibaba/cobar
• mycat的前身就是cobar，活跃度比较高，完全使用java语言开发。 项目地址：https://github.com/MyCATApache/Mycat-Server ，该项目当前已经有8.3k的点赞量。

2. 基于应用内路由的方式

基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行sql。

实现原理

实现方案

基于spring的aop实现: 用aop来拦截spring项目的dao层方法，根据方法名称就可以判断要执行的sql类型(即是read还是write类型)，进而动态切换主从数据源。

3. 基于mysql-connector-java的jdbc驱动方式

实现原理

使用mysql驱动Connector/J的可以实现读写分离。即在jdbc的url中配置为如下的形示：

jdbc:mysql:replication://master,slave1,slave2,slave3/test

实现方案

java程序通过在连接MySQL的jdbc中配置主库与从库等地址，jdbc会自动将读请求发送给从库，将写请求发送给主库，此外，mysql的jdbc驱动还能够实现多个从库的负载均衡。

4. 基于sharding-jdbc的方式

sharding-sphere是强大的读写分离、分表分库中间件，sharding-jdbc是sharding-sphere的核心模块。

实现原理

实现方案

sharding-jdbc可以与springboot集成.

5. 最后总结（重要）

以上四种方案各有优缺点，

• 基于MySQL proxy代理的方式对于应用来说相对简单，但是在项目稳定性、事务支持性等方面还存在问题；
• 基于应用内路由的方式固然灵活度比较高，但是也增加了应用逻辑的复杂度；
• 基于mysql-connector-java的jdbc驱动和sharding-jdbc的方式在使用上相对简单，但限制了需要使用java开发。

6.主从复制存在的问题

因为主从同步的时候需要走网络，极端情况下会出现延迟，导致数据不能及时同步，上述场景在极端情况 下slave从库会没有此订单数据。

解决方法：通过强制路由直接向master节点查询数据；

二、分库分表

1. 为什么要分库分表

如果一个网站业务快速发展，那这个网站流量也会增加，数据的压力也会随之而来，比如电商系统来说双十一大促对订单数据压力很大，Tps十几万并发量，如果传统的架构（一主多从），主库容量肯定无法满足这么高的Tps，业务越来越大，单表数据超出了数据库支持的容量，持久化磁盘IO,传统的数据库性能瓶颈，产品经理业务·必须做，改变程序，数据库刀子切分优化。数据库连接数不够需要分库，表的数据量大，优化后查询性能还是很低，需要分。

2. 什么是分库分表

分库分表方案是对关系型数据库数据存储和访问机制的一种补充。

• 分库：将一个库的数据拆分到多个相同的库中，访问的时候访问一个库
• 分表：把一个表的数据放到多个表中，操作对应的某个表就行

3.分库分表的几种方式

1.垂直拆分

(1) 数据库垂直拆分

根据业务拆分，如图，电商系统，拆分成订单库，会员库，商品库

(2)表垂直拆分

根据业务去拆分表，如图，把user表拆分成user_base表和user_info表，use_base负责存储登录，user_info负责存储基本用户信息

垂直拆分特点

1. 每个库（表）的结构都不一样
2. 每个库（表）的数据至少一列一样
3. 每个库（表）的并集是全量数据

垂直拆分优缺点

优点：

1. 拆分后业务清晰（专库专用按业务拆分）
2. 数据维护简单，按业务不同，业务放到不同机器上

缺点:

1. 如果单表的数据量过大，写读压力大
2. 受某种业务决定，或者被限制，也就是说一个业务往往会影响到数据库的瓶颈（性能问题，如双十一抢购）
3. 部分业务无法关联join，只能通过java程序接口去调用，提高了开发复杂度

2. 水平拆分

(1) 数据库水平拆分

如图，按会员库拆分，拆分成会员1库，会员2库，以userId拆分，userId尾号0-5为1库
6-9为2库，还有其他方式，进行取模，偶数放到1库，奇数放到2库

(2) 表水平拆分

水平拆分的其他方式

• range来分,每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了,优点：扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了 缺点：大部分的 请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据
• hash分发,优点：可以平均分配每个库的数据量和请求压力 缺点：扩容起来比较麻烦，会有一个数据迁移的这么一个过程
• 一致性哈希：形成环状，避免大面积击穿

水平拆分特点

1. 每个库（表）的结构都一样
2. 每个库（表）的数据都不一样
3. 每个库（表）的并集是全量数据

水平拆分优缺点

优点：

1. 单库/单表的数据保持在一定量（减少），有助于性能提高
2. 提高了系统的稳定性和负载能力
3. 拆分表的结构相同，程序改造较少。

缺点:

1. 数据的扩容很有难度维护量大
2. 拆分规则很难抽象出来
3. 分片事务的一致性问题部分业务无法关联join，只能通过java程序接口去调用

4. 分库分表带来的问题

1. 分布式事务
2. 跨库join查询
3. 分布式全局唯一id
4. 开发成本 对程序员要求高

五、分库分表技术如何选型

分库分表的开源框架
jdbc 直连层：shardingsphere、tddl
proxy 代理层：mycat，mysql-proxy（360）

jdbc直连层

jdbc直连层又叫jdbc应用层,是因为所有分片规则，所有分片逻辑，包括处理分布式事务
所有这些问题它都是在应用层，所有项目都是由war包构成的，所有分片都写成了jar包，放到了war包里面，java需要虚拟机去运行的，虚拟机运行的时候就会把war包里面的字节文件进行classLoder加载到jvm内存中，所有分片逻辑都是基于内存方进行操作的

proxy代理层

如图,proxy代理层，所有分片规则，所有分片逻辑，包括处理分布式事务都在mycat写好了,所有分片逻辑都是基于mycat方进行操作

jdbc直连层和proxy代理层优缺点

• jdbc直连层性能高，只支持java语言，支持跨数据库
• proxy代理层开发成本低，支持跨语言，不支持跨数据库