MySQL业务拆分、主从复制，表分区，分库与分表中间件Sharding-JDBC

keep one's resolveY

已于 2024-07-25 16:41:25 修改

阅读量2.3k

点赞数 1

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

于 2020-12-26 00:09:27 首次发布

本文链接：https://blog.csdn.net/weixin_45433031/article/details/111713718

版权

数据库+ES 专栏收录该内容

28 篇文章 4 订阅

订阅专栏

一、MySQL扩展具体的3种实现方式

随着业务规模的不断扩大，需要选择合适的方案去应对数据规模的增长，以应对逐渐增长的访问压力和数据量。

关于数据库的扩展主要包括：业务拆分、主从复制，数据库分库与分表。这篇文章主要讲述数据库分库与分表

（1）业务拆分

在大型网站应用之海量数据和高并发解决方案总结一二一篇文章中也具体讲述了为什么要对业务进行拆分。

业务起步初始，为了加快应用上线和快速迭代，很多应用都采用集中式的架构。随着业务系统的扩大，系统变得越来越复杂，越来越难以维护，开发效率变得越来越低，并且对资源的消耗也变得越来越大，通过硬件提高系统性能的方式带来的成本也越来越高。

因此，在选型初期，一个优良的架构设计是后期系统进行扩展的重要保障。

例如：电商平台，包含了用户、商品、评价、订单等几大模块，最简单的做法就是在一个数据库中分别创建users、shops、comment、order四张表。

这里写图片描述

但是，随着业务规模的增大，访问量的增大，我们不得不对业务进行拆分。每一个模块都使用单独的数据库来进行存储，不同的业务访问不同的数据库，将原本对一个数据库的依赖拆分为对4个数据库的依赖，这样的话就变成了4个数据库同时承担压力，系统的吞吐量自然就提高了。

这里写图片描述

（2）主从复制-读写分离

1、MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解

2、MySQL主从复制的常见拓扑、原理分析以及如何提高主从复制的效率总结

3、使用mysqlreplicate命令快速搭建 Mysql 主从复制

上述三篇文章中，讲述了如何配置主从数据库，以及如何实现数据库的读写分离，这里不再赘述，有需要的选择性点击查看。

上图是网上的一张关于MySQL的Master和Slave之间数据同步的过程图。

主要讲述了MySQL主从复制的原理：数据复制的实际就是Slave从Master获取Binary log文件，然后再本地镜像的执行日志中记录的操作。由于主从复制的过程是异步的，因此Slave和Master之间的数据有可能存在延迟的现象，此时只能保证数据最终的一致性。

--------------------------------------------------------------------------------------------------------------------------

读写分离：（主从复制是读写分离的前提）

1、概述
在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的
通过主从复制来同步数据，再通过读写分离来提升数据库并发负载能力的方案来进行部署和实施

2、读写分离原理
让主数据库处理事务性增、删、改，而从数据库处理SELECT查询操作
数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库

3、读写分离存在的作用
因为数据库的“写”操作是比较耗时的
但是数据库的“读”（读取10000条数据可能只需要5秒钟）
所以读写分离解决的是数据库的写入，影响了查询的效率

8_2、mysql读写分离及实现_问答快的博客-CSDN博客_mysql读写分离实现方式

（3）数据库分库与分表

我们知道每台机器无论配置多么好它都有自身的物理上限，所以当我们应用已经能触及或远远超出单台机器的某个上限的时候，我们惟有寻找别的机器的帮助或者继续升级的我们的硬件，但常见的方案还是通过添加更多的机器来共同承担压力。

我们还得考虑当我们的业务逻辑不断增长，我们的机器能不能通过线性增长就能满足需求？因此，使用数据库的分库分表，能够立竿见影的提升系统的性能，关于为什么要使用数据库的分库分表的其他原因这里不再赘述，主要讲具体的实现策略。请看下边章节。

二、分表实现策略

关键字：用户ID、表容量

对于大部分数据库的设计和业务的操作基本都与用户的ID相关，因此使用用户ID是最常用的分库的路由策略。用户的ID可以作为贯穿整个系统用的重要字段。因此，使用用户的ID我们不仅可以方便我们的查询，还可以将数据平均的分配到不同的数据库中。（当然，还可以根据类别等进行分表操作，分表的路由策略还有很多方式）

接着上述电商平台假设，订单表order存放用户的订单数据，sql脚本如下（只是为了演示，省略部分细节）：

CREATE TABLE `order` (
  `order_id` bigint(32) primary key auto_increment,
  `user_id` bigint(32),
   ...
)

当数据比较大的时候，对数据进行分表操作，首先要确定需要将数据平均分配到多少张表中，也就是：表容量。

这里假设有100张表进行存储，则我们在进行存储数据的时候，首先对用户ID进行取模操作，根据 user_id%100 获取对应的表进行存储查询操作，示意图如下：

这里写图片描述

例如，user_id = 101 那么，我们在获取值的时候的操作，可以通过下边的sql语句：

select * from order_1 where user_id= 101

其中，order_1是根据 101%100 计算所得，表示分表之后的第一章order表。

三、分库实现策略

数据库分表能够解决单表数据量很大的时候数据查询的效率问题，但是无法给数据库的并发操作带来效率上的提高，因为分表的实质还是在一个数据库上进行的操作，很容易受数据库IO性能的限制。

因此，如何将数据库IO性能的问题平均分配出来，很显然将数据进行分库操作可以很好地解决单台数据库的性能问题。

分库策略与分表策略的实现很相似，最简单的都是可以通过取模的方式进行路由。

还是上例，将用户ID进行取模操作，这样的话获取到具体的某一个数据库，同样关键字有：

用户ID、库容量

路由的示意图如下：

这里写图片描述

上图中库容量为100。

同样，如果用户ID为UUID请先hash然后在进行取模。

四、分库与分表实现策略

上述的配置中，数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题。

有时候，我们需要同时考虑这两个问题，因此，我们既需要对单表进行分表操作，还需要进行分库操作，以便同时扩展系统的并发处理能力和提升单表的查询性能，就是我们使用到的分库分表。

分库分表的策略相对于前边两种复杂一些，一种常见的路由策略如下：

１、中间变量　＝ user_id%（库数量*每个库的表数量）;
２、库序号　＝　取整（中间变量／每个库的表数量）;
３、表序号　＝　中间变量％每个库的表数量;

例如：数据库有256 个，每一个库中有1024个数据表，用户的user_id＝262145，按照上述的路由策略，可得：

１、中间变量　＝ 262145%（256*1024）= 1;
２、库序号　＝　取整（1／1024）= 0;
３、表序号　＝　1％1024 = 1;

这样的话，对于user_id＝262145，将被路由到第０个数据库的第１个表中。

示意图如下：

这里写图片描述

五、分库分表总结

关于分库分表策略的选择有很多种，上文中根据用户ID应该是比较简单的一种。其他方式比如使用号段进行分区或者直接使用hash进行路由等。有兴趣的可以自行查找学习。

关于上文中提到的，如果用户的ID是通过UUID的方式生成的话，我们需要单独的进行一次hash操作，然后在进行取模操作等，其实hash本身就是一种分库分表的策略，使用hash进行路由策略的时候，我们需要知道的是，也就是hash路由策略的优缺点，优点是：数据分布均匀；缺点是：数据迁移的时候麻烦，不能按照机器性能分摊数据。

上述的分库和分表操作，查询性能和并发能力都得到了提高，但是还有一些需要注意的就是，例如：原本跨表的事物变成了分布式事物；由于记录被切分到不同的数据库和不同的数据表中，难以进行多表关联查询，并且不能不指定路由字段对数据进行查询。分库分表之后，如果我们需要对系统进行进一步的扩阵容（路由策略变更），将变得非常不方便，需要我们重新进行数据迁移。

最后需要指出的是，分库分表目前有很多的中间件可供选择，最常见的是使用淘宝的中间件Cobar。

GitHub地址：https://github.com/alibaba/cobara

文档地址为：Home · alibaba/cobar Wiki · GitHub

关于淘宝的中间件Cobar本篇内容不具体介绍，会在后边的学习中在做介绍。

另外Spring也可以实现数据库的读写分离操作，后边的文章，会进一步学习。

六、分库分表中间件

比较常见的包括：

cobar
TDDL
atlas
sharding-jdbc
mycat

cobar

阿里 b2b 团队开发和开源的，属于 proxy 层方案，就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 cobar 集群，cobar 根据 SQL 和分库规则对 SQL 做分解，然后分发到 MySQL 集群不同的数据库实例上执行。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。

TDDL
淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。

atlas
360 开源的，属于 proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。

sharding-jdbc
当当开源的，属于 client 层方案。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且目前推出到了 2.0 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。

ShardingJDBC核心概念与快速实战+执行Sql深入解读-CSDN博客（分库分表优质博客！）

mycat
基于 cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 sharding jdbc 来说，年轻一些，经历的锤炼少一些。

互联网行业处理海量数据的通用方法：分库分表。分库分表中间件全部可以归结为两大类型：

CLIENT模式；
PROXY模式；

CLIENT模式代表有阿里的TDDL，开源社区的sharding-jdbc（sharding-jdbc的3.x版本即sharding-sphere已经支持了proxy模式）。架构如下：在这里插入图片描述

PROXY模式代表有阿里的cobar，民间组织的MyCAT。架构如下：

在这里插入图片描述

无论是CLIENT模式，还是PROXY模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。

原文链接：【数据库与事务系列】分库分表中间件_人工智的博客-CSDN博客

七、分库分表统计数据时，应该如何统计查询呢？

1、使用分库分表中间件，中间件支持可以分库、跨库查询

2、把数据定时写入elasticssearch里面，然后在es里面实时做统计查询

3、加中间表 ---- 创建统计结果表。跑定时任务，统计出每天的结果存在统计表里面。总控里做不同维度的统计的时候直接从统计表查

4、union或者在代码中拼数据

大数量的分页查询操作示范：

1、延迟关联，先查id，然后关联查*

explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end\_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |
| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |
| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+

2、书签，首先获取符合条件的记录的最大 id和最小id，然后利用limit获取分页数据

3、利用数据库自增主键或者增加自增字段，通过范围sql limit处理分页

mysql大数据量分页查询优化总结_mysql大表分页优化原理_lxw1844912514的博客-CSDN博客

大数据量性能优化之分页查询_大数据量分页_JavaEdge.的博客-CSDN博客

八、掘金优质博客

表分区实践：

(十九)MySQL之表分区篇：涨知识了！携手共探鲜为人知的表分区！ - 掘金（做了表分区之后，表在逻辑上还是同一张，只是磁盘中会划分为多个文件存储而已，所以表分区并不会影响原有的增删改查操作。简单来说，表分区前后的区别，就类似于数组和链表的区别）

（为啥说表分区技术没用呢？其实作用还是有的，不过一般情况下都会有更好的选择代替，比如采用分表技术来划分表数据，不仅仅支持水平切分，甚至还可以做垂直切分。当单库的数据过多时，表分区技术其实派不上太大的用场，因为当数据达到一定量级时，要么会选择分库分表，要么会选择上分布式数据库，如TiDB，最后再配合大数据技术辅助，做历史数据归档。）