MySQL分库分表/主从复制

分库分表

注意：

1、单表超过500万行或单表容量超过2GB，才推荐进行分库分表

为什么需要进行分库分表？

1、请求数太多

2、数据查询慢

3、数据量太大。

①当一个库的数据存储量太大时，就算每张表的并发数不多，但是因为是海量数据，单库中存在大量的数据表，每张表都有一部分并发请求，导致最终单库的连接数阈值成为数据库的瓶颈。

②当一张表数据太多时，导致单表查询速度严重下降，虽然InnoDB存储引擎的表允许的最大行数为10亿，但是如果一张表的数据行记录达到上亿级别，那就算通过索引去查询一条数据，它也需要至少经过上十次到几十次磁盘IO，从而导致单表查询速度直线下降；一般一张表的数据行数在800~1200W左右最合适。

4、单体架构的通病。比如单库中某个表遇到问题需要修复时，会影响整个库中数据。

5、MySQL数据库的瓶颈。

• ​ 磁盘IO瓶颈
• ​ 网络IO瓶颈

6、CPU瓶颈

​ CPU瓶颈也会分为两种，一种是运算密集型瓶颈，另一种则是阻塞密集型瓶颈

阐述

​ 不管是因为以上哪种原因，本质上都是数据库遭遇到了瓶颈，只不过情况不同，不同的数据库瓶颈。而导致数据库出现此类问题的原因，实则就是随着业务的发展，系统的数据不断增多、用户量不断增长、并发量不断变大，因此对于数据再进行CRUD操作的开销也会越来越大，再加上物理服务器的CPU、磁盘、内存、IO等资源有限，最终也会限制数据库所能承载的最大数据量、数据处理能力。

​ 当出现上述这类问题，并且无法通过升级硬件、版本、调优等手段解决时，或者只能临时解决，却无法保障未来业务增长的可用性时，此刻就需要合理的设计数据库架构来满足不断增长的业务，这就是分库分表诞生的初衷，目的就是为了避免单库由于压力过高，导致出现之前所说的一系列问题，合理的设计架构能最大限度上提高数据库的整体吞吐量。

​ 如果是遇到金融业务，基本上对数据的实时性要求特别高，所以MQ、Redis只能承担一部分的流量，其他大部分的流量依旧会需要进行落库处理。最终，根据服务不同的业务规模，拆成了规模不同、业务不同的库。拆分规则也可分为：水平、垂直 两个维度，按拆分的粒度来排序，共计可分为四种方案：垂直分表、水平分表、垂直分库、水平分库。

优化方案

垂直分表

​ 当一个表字段过多时，应当考虑垂直分表方案，将多余的字段拆分到不同的表中存储。

​ 从内存维度方面来看，单行数据越大，缓冲区中能放下的热点数据页会越少，当读写操作无法在内存中定位到相应的数据页，从而又会产生大量的磁盘IO。通常情况下，一般都按照冷热字段进行分表。

弊端

​ 切分的时候需要设置好映射的外键字段，因为如果需要增删改就需要同事操作多张表，必须开始事务来保证原子性。

水平分表

​ 当一个表的数据过多时，或者数据增长速率过快时，应当考虑通过水平分表方案，来降低单表的数据行数。

​ 一般情况下，都是把单表的数据量大概控制在500-600w一张，因为这个数据量级的，就算是使用分布式策略生成的分布式ID作为主键，也能够很好的把索引树高控制在3~5以内，也就意味着最多三到五次磁盘IO就一定能得到数据，从而将单表的查询性能控制在最佳范围内。

​ 水平拆分之后的表、索引等结构完全相同，可以按照主键ID或者日期进行拆分。通常来说是按照日期来拆分，或者是以一定阶段来拆分，比如热点活动双十一、618等。

弊端

​ 水平切分主要是多表联查阶段，因为原本只需要连表查询一张表，现在需要多张表，无法确定查询数据所在位置。通常都是按照当前主表数据日期，以日期范围来确定数据所处哪张表，来进行查询。

注意：

​ 当进行聚合操作的话，一般都是借用第三方来进行操作，单靠表内只想聚合，执行效率非常低。

解决方案：

• 放入第三方中间件中，然后依赖于第三方中间件完成，如ES。就需要把需要聚合的表数据进行同步到ES。
• 定期跑脚本查询出一些常用的聚合数据，然后放入Redis缓存中，后续从Redis中获取。但存在数据的准确性问题。。
• 首先从所有表中统计出各自的数据，然后在Java中作聚合操作。此方案比上不足比下有余。就是根据条件查询匹配的数据，进行流的聚合操作，内部处理繁杂。

总结

​ 分表方案主要是针对于单表字段过多或数据过多的情况去做的，通过垂直、水平分表的手段，能够很好解决单表由于字段、数据量过多产生的一系列负面影响，但无论是垂直分表还是水平分表，都必须建立在单库压力不高，但是单表性能不够的情况下进行的，因为它们都属于库内分表。

​ 如果单库的压力大，可以考虑分库分表。因为单个库的数据连接是有限，即使是连接复用，但是能承受的QPS事有限的。当高并发请求过了，如果处理不当，就会直接把数据库搞奔溃。

注意：

​ 跨库也存在一定的问题。比如：

​ 1、跨库Join问题。解决方案如下：

​ ①在不同的库需要数据的表中冗余字段，把常用的字段放到需要要数据的表中，避免跨库连表。

​ ②选择同步数据，通过广播表/网络表/全局表将对应的表数据直接完全同步一份到相应库中。

​ ③在设计库表拆分时创建ER绑定表，具备主外键的表放在一个库，保证数据落到同一数据库。

​ ④Java系统中组装数据，通过调用对方服务接口的形式获取数据，然后在程序中组装后返回。

​ 2、分布式事务问题。一般都是采用分布式事务中间件TCC模式或MQ事务模式来解决。

MySQL - 主从复制与读写分离

前言

​ 刚刚上面说的，如果流量突然增长，超过数据库承受压力，就会导致数据库奔溃。为了解决这一问题，就需要采用一些数据库高可用方案，比如主从复制、读写分离、双柱热备等。但是即使考虑这些方案也需要考虑承受压力的问题。

主从方案

​ 一般会搭建读写分离，写请求发往主节点处理，读请求发往从节点处理，从节点会完全同步主节点的数据，从而实现读写请求分开处理的效果，能够再一定程度上提升数据存储层整体的并发处理能力。同时当主机挂掉时，从机也能够在很快的时间内替换成主机，以此确保数据层的高可用。

​ 主从复制主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。

1、binlog 线程: 负责将主服务器上的数据更改写入二进制日志中。

2、I/O 线程: 负责从主服务器上读取二进制日志，并写入从服务器的中继日志中。

3、SQL 线程: 负责读取中继日志并重放其中的 SQL 语句。

​ 读写分离。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于:

1、主从服务器负责各自的读和写，极大程度缓解了锁的争用；

2、从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；

3、增加冗余，提高可用性。

多主方案

​ 多主指的是双主方案，两台数据库节点之间互为主从，相互同步各自的数据，两台节点中都具备完整的数据，读写请求可以发给任意节点处理。相较于前面的主从读写分离架构，这种双主双写架构的灾备能力更强，因为当其中某个节点宕机时，另一个节点可以完全接替对方的流量，不存在从机切换成主机的时间开销，因此能够保证数据100%不丢失。

分库有点

• ①能够得到最大的性能收益，吞吐量会随机器数量呈直线性增长。
• ②能够最大程度上保障存储层的高可用，任意节点宕机都不会影响整体业务的运转。
• ③具备相当强的容错率，当一个库中的结构存在问题需要重构时，无需将所有业务停机更新。
• ④具备高稳定性，分库+配备完善的监控重启策略后，基本上能确保线上无需人工介入管理。

小结

​ 优化方案一般都是基于现行业务增长，但是要提前预留号扩容方案，不管是数据库还是后端，要提供出来负载压力的入口，才能实现优雅的系统升级，实现高可用。

分库分表之后数据如何访问

1、动态数据源切换。

2、在JDBC驱动层拦截SQL语句，然后改写SQL实现。例如：Sharding-JDBC等。

总结

​ 分库分表、读写分离 都是为了得到最大的性能收益，也同时为了保证服务的可用性以及数据原子性，为程序提供数据的准确性保证。