一文读懂Mysql数据库的分库分表和读写分离

乡村天地

分库分表适用场景

分库分表用于应对当前互联网常见的两个场景——大数据量和高并发。通常分为垂直拆分和水平拆分两种。
垂直拆分是根据业务将一个库（表）拆分为多个库（表）。如：将经常和不常访问的字段拆分至不同的库或表中。由于与业务关系密切，目前的分库分表产品均使用水平拆分方式。
水平拆分则是根据分片算法将一个库（表）拆分为多个库（表）。如：按照ID的最后一位以3取余，尾数是1的放入第1个库（表），尾数是2的放入第2个库（表）等。
关系型数据库在大于一定数据量的情况下检索性能会急剧下降。在面对互联网海量数据情况时，所有数据都存于一张表，显然会轻易超过数据库表可承受的数据量阀值。这个单表可承受的数据量阀值，需根据数据库和并发量的差异，通过实际测试获得。
单纯的分表虽然可以解决数据量过大导致检索变慢的问题，但无法解决过多并发请求访问同一个库，导致数据库响应变慢的问题。所以通常水平拆分都至少要采用分库的方式，用于一并解决大数据量和高并发的问题。这也是部分开源的分片数据库中间件只支持分库的原因。
但分表也有不可替代的适用场景。最常见的分表需求是事务问题。同在一个库则不需考虑分布式事务，善于使用同库不同表可有效避免分布式事务带来的麻烦。目前强一致性的分布式事务由于性能问题，导致使用起来并不一定比不分库分表快。目前采用最终一致性的柔性事务居多。分表的另一个存在的理由是，过多的数据库实例不利于运维管理。综上所述，最佳实践是合理地配合使用分库+分表。
一. 数据库分表的背景

1 索引的极限

单表数据量达到几十万或上百万以上，使用索引性能提升也不明显。
2 分表的边界

分表使用门槛：
单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
3.分表适用场景

分库分表主要用于应对当前互联网常见的两个场景——大数据量和高并发。
1.数据量大时，减少单表查询压力
2.减少单表的访问压力
3.单表数据量上百万至千万，索引性能下降
4.索引也很难在提高性能或者更新、插入数据频繁等。
4.分表所造成的复杂度

• join 操作
  

水平分表后，数据分散在多个表中，如果需要与其他表进行 join 查询，需要在业务代码或者数据库中间件中进行多次 join 查询，然后将结果合并。

• count() 操作
  

水平分表后，虽然物理上数据分散到多个表中，但某些业务逻辑上还是会将这些表当作一个表来处理。例如，获取记录总数用于分页或者展示，水平分表前用一个 count() 就能完成的操作，在分表后就没那么简单了。常见的处理方式有下面两种：
count() 相加：具体做法是在业务代码或者数据库中间件中对每个表进行 count() 操作，然后将结果相加。这种方式实现简单，缺点就是性能比较低。例如，水平分表后切分为 20 张表，则要进行 20 次 count(*) 操作，如果串行的话，可能需要几秒钟才能得到结果。
记录数表：具体做法是新建一张表，假如表名为“记录数表”，包含 table_name、row_count 两个字段，每次插入或者删除子表数据成功后，都更新“记录数表”。
这种方式获取表记录数的性能要大大优于 count() 相加的方式，因为只需要一次简单查询就可以获取数据。缺点是复杂度增加不少，对子表的操作要同步操作“记录数表”，如果有一个业务逻辑遗漏了，数据就会不一致；且针对“记录数表”的操作和针对子表的操作无法放在同一事务中进行处理，异常的情况下会出现操作子表成功了而操作记录数表失败，同样会导致数据不一致。
此外，记录数表的方式也增加了数据库的写压力，因为每次针对子表的 insert 和 delete 操作都要 update 记录数表，所以对于一些不要求记录数实时保持精确的业务，也可以通过后台定时更新记录数表。定时更新实际上就是“count() 相加”和“记录数表”的结合，即定时通过 count() 相加计算表的记录数，然后更新记录数表中的数据。

• order by 操作
  

水平分表后，数据分散到多个子表中，排序操作无法在数据库中完成，只能由业务代码或者数据库中间件分别查询每个子表中的数据，然后汇总进行排序。
二. 数据库表拆分方式

1.表的垂直拆分

垂直分库
是指按功能模块拆分，比如分为订单库、商品库、用户库…这种方式多个数据库之间的表结构不同。
垂直分表



即当一张表有有太多的字段后，需要按照业务模块进行拆分，如用户表拆成用户基础信息，用户登陆信息表，用户地址信息表等等，然后通过用户ID进行关联。



假如你有幸能够在什么经营商、银行等公司上班，你会发现他们一个表，几百个字段都是很常见的事情。所以，应该要进行拆分，拆分准则一般是如下三点:
(1)把不常使用的字段单独放在一张表。
(2)把常使用的字段单独放一张表
(3)经常组合查询的列放在一张表中（联合索引）。
垂直拆分的好处
1.单表的数据行数变少了，查询检索的字段也变少了，性能得到了一定的提升
垂直拆分的坏处
垂直分表引入的复杂性主要体现在表操作的数量要增加。例如，原来只要一次查询就可以获取 name、age、sex、nickname、description，现在需要两次查询，一次查询获取 name、age、sex，另外一次查询获取 nickname、description。
2 水平拆分

将同一个表的数据进行分块保存到不同的数据库中，这些数据库中的表结构完全相同。
1.范围路由拆分：
选取有序的数据列（例如，整形、时间戳等）作为路由的条件，不同分段分散到不同的数据库表中。以最常见的用户 ID 为例，路由算法可以按照 1000000 的范围大小进行分段，1 ~ 999999 放到数据库 1 的表中，1000000 ~ 1999999 放到数据库 2 的表中，以此类推
再如可以按订单的日前按年份才分，2003年的放在db1中，2004年的db2,以此类推。当然也可以按主键标准拆分。
范围路由设计的复杂点主要体现在分段大小的选取上，分段太小会导致切分后子表数量过多，增加维护复杂度；分段太大可能会导致单表依然存在性能问题，一般建议分段大小在 100 万至 2000 万之间，具体需要根据业务选取合适的分段大小。
优点：可部分迁移。范围路由的优点是可以随着数据的增加平滑地扩充新的表。例如，现在的用户是 100 万，如果增加到 1000 万，只需要增加新的表就可以了，原有的数据不需要动。
缺点：数据分布不均，可能2003年的订单有100W，2008年的有500W。且跨年查询实现方式困难可能会查多张表。再如按照 1000 万来进行分表，有可能某个分段实际存储的数据量只有 1000 条，而另外一个分段实际存储的数据量有 900 万条。
解决建议：
2.hash取模
优点：数据分布均匀
缺点：数据量大后，数据重新分表后迁移的时候麻烦;不能按照机器性能分摊数据 。
解决建议：先同步到大数据平台，然后在大数据平台重新根据分片算法路由到各个表，最后同步给业务平台mysql数据表

数据库优化的几个阶段请参考：
https://www.songma.com/news/txtlist_i21939v.html

3.配置路由
配置路由就是路由表，用一张独立的表来记录路由信息。同样以用户 ID 为例，我们新增一张 user_router 表，这个表包含 user_id 和 table_id 两列，根据 user_id 就可以查询对应的 table_id。
配置路由设计简单，使用起来非常灵活，尤其是在扩充表的时候，只需要迁移指定的数据，然后修改路由表就可以了。
配置路由的缺点就是必须多查询一次，会影响整体性能；而且路由表本身如果太大（例如，几亿条数据），性能同样可能成为瓶颈，如果我们再次将路由表分库分表，则又面临一个死循环式的路由算法选择问题。
二. 主从同步

1 mysql主从复制原理

Mysql的主从复制中主要有三个线程： master（binlog dump thread）、slave（I/O thread 、SQL thread） ，Master一条线程和Slave中的两条线程。
整体上来说，复制有3个步骤：

• master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
  
• 从节点 I/O线程接收 binlog 变动内容，并将其写入到 中继日志(relay log) 文件中。；
  
• 从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。
  

复制流程如下：



在这里插入图片描述

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。
详细流程：
2.复制策略

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。
全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。
流程如下：
第一部分就是master记录二进制日志。
在每个事务更新数据完成之前，master在二进制日志中记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
第二部分就是slave将master的binary log拷贝到它自己的中继日志。
首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
SQL slave thread（SQL从线程）处理该过程的最后一步。
SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。
2 MySQL数据库主从同步延迟是怎么产生的

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围（即执行这些ddl语句会耗时较大），那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。
3 MySQL数据库主从同步延迟解决方案

1.数据库层面：
最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。
2.引入缓存：
数据库的写操作，先写数据库，再写cache，但是有效期很短，就比主从延时的时间略微长一点。然后读请求的时候，先读缓存，缓存存在则直接返回。
假如缓存不存在(这时主从同步已经完成)，再读数据库。
3.强制路由：
对应一致性要求较高的读写请求，直接对主库读写
4.读取入库之前的数据：
将要准备入库的数据，如设置在上下文内存中，读的时候读内存而不是读数据库插入的数据。
总结：建议从强制路由控制，这样才是正真的强一致性，而从数据库无法完全保证，且会牺牲数据安全等其他代价。

参考：深入解析Mysql 主从同步延迟原理及解决方案 https://www.cnblogs.com/cnmenglang/p/6393769.html

三、读写分离

1.读写分离的意义

优点
1.减轻数据库的io压力，由多个数据库分担用户的查询请求压力，而且大多数公司的业务场景都是读多写少
2.减少因为数据库读写而导致的加锁逻辑，影响数据库的读写性能。
缺点
1.主从数据延迟，导致业务数据无法保证强一致性
2.读写分离分配机制

1. 程序代码封装

程序代码封装指在代码中抽象一个数据访问层（所以有的文章也称这种方式为“中间层封装”），实现读写操作分离和数据库服务器连接的管理。例如，基于 Hibernate 进行简单封装，就可以实现读写分离，基本架构是：



程序代码封装的方式具备几个特点：

• 实现简单，而且可以根据业务做较多定制化的功能。
  
• 每个编程语言都需要自己实现一次，无法通用，如果一个业务包含多个编程语言写的多个子系统，则重复开发的工作量比较大。
  
• 故障情况下，如果主从发生切换，则可能需要所有系统都修改配置并重启。
  

目前开源的实现方案中，淘宝的 TDDL（Taobao Distributed Data Layer，外号: 头都大了）是比较有名的。它是一个通用数据访问层，所有功能封装在 jar 包中提供给业务代码调用。其基本原理是一个基于集中式配置的 jdbc datasource 实现，具有主备、读写分离、动态数据库配置等功能，基本架构是：



类似的使用开源组件sharing-jdbc也可以实现数据库的读写分离控制。
2. 中间件封装

中间件封装指的是独立一套系统出来，实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供 SQL 兼容的协议，业务服务器无须自己进行读写分离。对于业务服务器来说，访问中间件和访问数据库没有区别，事实上在业务服务器看来，中间件就是一个数据库服务器。其基本架构是：



数据库中间件的方式具备的特点是：

• 能够支持多种编程语言，因为数据库中间件对业务服务器提供的是标准 SQL 接口。
  
• 数据库中间件要支持完整的 SQL 语法和数据库服务器的协议（例如，MySQL 客户端和服务器的连接协议），实现比较复杂，细节特别多，很容易出现 bug，需要较长的时间才能稳定。
  
• 数据库中间件自己不执行真正的读写操作，但所有的数据库操作请求都要经过中间件，中间件的性能要求也很高。
  
• 数据库主从切换对业务服务器无感知，数据库中间件可以探测数据库服务器的主从状态。例如，向某个测试表写入一条数据，成功的就是主机，失败的就是从机。
  

由于数据库中间件的复杂度要比程序代码封装高出一个数量级，一般情况下建议采用程序语言封装的方式，或者使用成熟的开源数据库中间件。如果是大公司，可以投入人力去实现数据库中间件，因为这个系统一旦做好，接入的业务系统越多，节省的程序开发投入就越多，价值也越大。
目前的开源数据库中间件方案中，MySQL 官方先是提供了 MySQL Proxy，但 MySQL Proxy 一直没有正式 GA，现在 MySQL 官方推荐 MySQL Router。MySQL Router 的主要功能有读写分离、故障自动切换、负载均衡、连接池等，其基本架构如下：



奇虎 360 公司也开源了自己的数据库中间件 Atlas，Atlas 是基于 MySQL Proxy 实现的，基本架构如下：

作者：daiwei-dave
链接：https://blog.csdn.net/sinat_34814635/article/details/127376731

PS :与常见开源产品对比
了对其他开源项目表示尊重，我们无意评论目前仍在更新中的项目。这里仅列出目前停止更新，但仍然在数据库分片领域非常有影响力的几个项目，请参见表1。


经上述对⽐，可以发现 Sharding-JDBC 是直连数据库、没有中间层的⽅式，性能上更优，同时由于 Sharding-JDBC 集成在应⽤代码内，并不会增加额外的运维成本，使开发者可以专注于⾃⾝代码逻辑。另外，Sharding-JDBC ⽬前仅⽀持 Java，考虑到⽬前团队技术栈主要以 Java 为主，所以采⽤ Sharding-JDBC 的分库⽅案成为了⼀个不错的选择。
补充，如果对分表分库还不了解的话，可以参考下面的一篇帖子：
我是终端研发部的小于哥
@终端研发部
每天专注技术开发小技巧，技术教程进阶，职场经验，面试的分享，希望我的回答能够帮助到你哈，笔芯~