Mysql分表分库背景知识

赵广陆

已于 2022-05-06 10:50:24 修改

阅读量449

点赞数

分类专栏： mysql 文章标签： mysql 数据库 big data

于 2022-05-05 20:56:29 首次发布

本文链接：https://blog.csdn.net/ZGL_cyy/article/details/124572561

版权

mysql 专栏收录该内容

40 篇文章 1 订阅

订阅专栏

1 海量数据的存储与访问瓶颈解决方案

遇到的瓶颈问题:

高访问量高并发对数据库的压力

随着公司业务快速发展，数据库中的数据量增大，访问性能也变慢了，虽然通过机器集群，sql优化，性能调优等方式在一定程度上可以起到提高性能的作用，但是并不能从根本上解决单库，单表数据量过大
的问题。

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

1.1 数据切分

数据切分，简单的说，就是通过某种条件，将我们之前存储在一台数据库上的数据，分散到多台数据库中，从而达到降低单台数据库负载的效果。数据切分，根据其切分的规则，大致分为两种类型，垂直切分和水平切分。

1.2 垂直切分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，
分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：
在这里插入图片描述

1.2.1 如何划分表

一个问题：在两台主机上的两个数据库中的表，能否关联查询？

答案：不可以关联查询。

分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。

#客户表 rows:20万
CREATE TABLE customer ( id INT AUTO_INCREMENT, NAME VARCHAR ( 200 ), PRIMARY KEY ( id ) );#订单表 rows:600万
CREATE TABLE orders ( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL ( 10, 2 ), PRIMARY KEY ( id ) );#订单详细表 rows:600万
CREATE TABLE orders_detail ( id INT AUTO_INCREMENT, detail VARCHAR ( 2000 ), order_id INT, PRIMARY KEY ( id ) );#订单状态字典表 rows:20
CREATE TABLE dict_order_type ( id INT AUTO_INCREMENT, order_type VARCHAR ( 200 ), PRIMARY KEY ( id ) );

以上四个表如何分库？客户表分在一个数据库，另外三张都需要关联查询，分在另外一个数据库。

1.2.2 垂直切分的优缺点

优点：

拆分后业务清晰，拆分规则明确；
系统之间容易扩展和整合；
数据维护简单；

缺点：

部分业务表无法join，只能通过接口调用，提升了系统的复杂度；
跨库事务难以处理；
垂直切分后，某些业务数据过于庞大，仍然存在单体性能瓶颈；

正如缺点中的最后一条所说，当某一个业务模块的数据暴增时，仍然存在着单机性能缺陷。还是之前的例子，如果出现了一个爆款商品，订单量急剧上升，达到了单机性能瓶颈，那么你所有和订单相关的业务都要受到影响。这时我们就要用到水平切分。

1.4 水平切分

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，
每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就
是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，如图：
在这里插入图片描述

水平拆分数据，要先订单拆分的规则，找到你要按哪个维度去拆分，还是前面订单的例子，我们按照订单尾号的奇偶去拆分，那么这样拆分会有什么影响呢？假如我是一个用户，我下了两个订单，一个订单尾号为奇数，一个订单尾号为偶数，这时，我去个人中心，订单列表页去查看我的订单。那么这个订单列表页要去怎么查，要根据我的用户id分别取订单1库和订单2库去查询出订单，然后再合并成一个列表，是不是很麻烦。所以，咱们在拆分数据时，一定要结合业务，选择出适合当前业务场景的拆分规则。那么按照用户id去拆分数据就合理吗？也不一定，比如：咱们的身份变了，不是买家了，而是卖家，我这个卖家有很多的订单，卖家的后台系统也有订单列表页，那这个订单列表页要怎么样去查？是不是也要在所有的订单库中查一遍，然后再聚合成一个订单列表呀。那这样看，是不是按照用户id去拆分订单又不合理了。所以在做数据水平拆分时，是对架构师的真正考验。

我们看看几种水平拆分的典型的分片规则：

用户id求模，我们前面已经提到过；
按照日期去拆分数据；
按照其他字段求模，去拆分数据；

上面是按照用户id去求模拆分的一个示意图。

1.4.1 水平拆分的优缺点

优点：

解决了单库大数据、高并发的性能瓶颈；
拆分规则封装好，对应用端几乎透明，开发人员无需关心拆分细节；
提高了系统的稳定性和负载能力；

缺点：

拆分规则很难抽象；
分片事务一致性难以解决；
二次扩展时，数据迁移、维护难度大。比如：开始我们按照用户id对2求模，但是随着业务的增长，2台数据库难以支撑，还是继续拆分成4个数据库，那么这时就需要做数据迁移了。

1.5 总结

世界上的万物没有完美的，有利就有弊，就像数据切分一样。无论是垂直切分，还是水平切分，它们解决了海量数据的存储和访问性能问题，但也随之而来的带来了很多新问题，它们的共同缺点有：

分布式的事务问题；
跨库join问题；
多数据源的管理问题；

1.6 针对多数据源的管理问题，主要有两种思路：

客户端模式，在每个应用模块内，配置自己需要的数据源，直接访问数据库，在各模块内完成数据的整合；
中间代理模式，中间代理统一管理所有的数据源，数据库层对开发人员完全透明，开发人员无需关注拆分的细节。

基于这两种模式，目前都有成熟的第三方软件，接下来在我们的架构师课程中，会分别给大家介绍这两种模式的代表作：

中间代理模式：MyCAT
客户端模式：Sharding-JDBC

2 如何正确使用数据库读写分离

在应用系统发展的初期，我们并不知道以后会发展成什么样的规模，所以一开始不会考虑复杂的系统架构，复杂的系统架构费时费力，开发周期长，与系统发展初期这样的一个定位是不吻合的。所以，我们都会采用简单的架构，随着业务不断的发展，访问量不断升高，我们再对系统进行架构方面的优化。

2.1 架构演进

系统建立初期，我们的架构都非常的简单，主要满足业务的正常运行，如图：
在这里插入图片描述

但是随着访问量的升高，人们对系统的可靠性有了更高的要求，所以，我们为了避免单点故障，对系统应用层进行了横向的扩展，如图：
在这里插入图片描述

这样，保证了系统应用层的高可用，在发生宕机，或者系统升级时，系统对外还是可用的。而且在访问量升高的时候，系统应用层的压力也会得到分摊，使得每一个单体的系统应用的压力在一个合理的区间范围内。
但是，随着访问量的升高，所有的压力都将集中到数据库这一层。那么数据库这一层，我们要怎么处理呢？能不能像系统应用层那样进行扩展呢？答案当然时否定的，我们想象一下，如果数据库层也像系统应用层那样，进行横向扩展，如图：
在这里插入图片描述

那么，如果系统应用层产生了一条数据，这条数据应该插入到DB1还是DB2呢？假设插入了DB1，那么这条数据被读取时，应用层怎么知道从哪个数据库读取这条数据呢？问题是不是很复杂，如果数据库不进行扩展，那么一台数据库是承载不了这么大的访问量的，那我们怎么办呢？

2.2 数据库读写分离

办法总比问题多，随着互联网技术的发展，以及一代代互联网人对互联网的深入研究，人们发现在互联网的系统应用是一个读多写少的应用，比如咱们课程中的电商系统，商品浏览的次数是比下单要多的。数据库承载压力大，主要是由这些读的请求造成的，那么我们是不是可以把读操作和写操作分开，让所有读的请求落到专门负责读的数据库上，所有写的操作落到专门负责写的数据库上，写库的数据同步到读库上，这样保证所有的数据修改都可以在读取时，从读库获得，系统的架构如图所示：
在这里插入图片描述

如果系统的读请求比较多的话，读库可以多部署几台，这样读请求就可以均摊到多台读库上，降低每一个读库上的压力。但是在写数据的时候，数据要落在一个确定，且唯一的写库中。上图中，咱们的写库只有一个，你当然可以部署多个写库，但是数据怎么分片是一个十分重要的问题，这个问题我们在后续的课程中会给大家做介绍。目前仅以一个写库为例，比如：商户发布商品时，将这个商品的数据落在了写库上，同时，写库将这条数据同步给两个读库，买家在网站浏览商品时，会从读库将这个商品数据读取。至于从哪个读库取出数据，那就要看这个请求在当时的路由情况了。
总之，将大量的读操作从数据库中剥离，让读操作从专用的读数据库中读取数据，大大缓解了数据库的访问压力，也使得读取数据的响应速度得到了大大的提升。那么读写分离有什么弊端吗？是不是所有的场景都适用读写分离这种架构呢？

2.3 读写分离的弊端

读写分离给我们带来的好处是很多的，我们对比一下原始的架构和读写分离的架构，从数据流上看，他们的区别是，数据从写入到数据库，到从数据库取出，读写分离的架构多了一个同步的操作。大家想一想，同步操作的时间是多少，延迟如果太大对系统有没有影响，如果同步挂了怎么办？举一个亲身经历的案例，在做个人中心的订单列表页，这个功能挺简单，只需要把订单数据取出来，在页面上展示就可以了。但是在做的时候，订单以及订单相关的数据都是从读库取出的，其中就包括支付状态，这个用户非常敏感的字段。就在某一天的某一个时段，突然接到了用户大量的投诉，说用户已经付了钱了，但是订单的状态还是未支付。我也觉得很奇怪，马上要了一个订单号，去数据库里查询，发现订单状态就是未支付呀，没有问题，过了一会，为了保险起见，我还是去写库再查一下这个订单吧，发现写库的订单状态确实是已支付，这下完了，写库和读取的数据不一致，我马上通知DBA，让他去查数据库，他的反馈是同步挂掉了。
大家看到了吧，这就是读写分离的弊端，当同步挂掉，或者同步延迟比较大时，写库和读库的数据不一致，这个数据的不一致，用户能不能接受，订单支付状态这个不一致当然是不能接受的了，其他的业务场景能不能接受呢？这个要对不同的业务场景做具体的分析了。

2.4 如何正确的使用读写分离

一些对数据实时性要求不高的业务场景，可以考虑使用读写分离。但是对数据实时性要求比较高的场景，比如订单支付状态，还是不建议采用读写分离的，或者你在写程序时，老老实实的从写库去读取数据。我也咨询过专门做数据同步的机构，他们给出的建议是，如果你做数据的同步，你的网络延迟应该在5ms以内，这个对网络环境要求是非常高的，大家可以ping一下你网络中的其他机器，看看能不能达到这个标准。如果你的网络环境很好，达到了要求，那么使用读写分离是没有问题的，数据几乎是实时同步到读库，根本感觉不到延迟。
读写分离呢，就给大家介绍到这，大家在使用的时候，还是要从业务出发，看看你的业务是否适合使用读写分离，每种技术架构都有自己的优缺点，没有好不好，只有适合不适合。只有适合业务的架构才是好的架构。

3 MyCat概述与基本概念

3.1 什么是MyCat

MyCat 是什么？从定义和分类来看，它是一个开源的分布式数据库系统，前端的用户可以把它看成一个数据库代理，用MySql客户端和命令行工具都可以访问，而其后端则是用MySql原生的协议与多个MySql服务之间进行通信。MyCat的核心功能是分库分表，即将一个大表水平切分成N个小表，然后存放在后端的MySql数据当中。

MyCat发展到目前的版本，已经不是一个单纯的MySql代理了，它的后端支持MySql，Oracle，SqlServer，DB2等主流的数据库，也支持MongoDB这种NoSql数据库。而对于前端的用户来说，无论后端采用哪一种数据库，在MyCat里都是一个传统的数据库，支持标准的SQL语句，对于前端的开发人员来说，可以大大地降低开发难度，提升开发速度。

对于DBA来说，可以这样理解MyCat：

MyCat就是MySql，而MyCat后面连接的MySql，可以理解为MySql中的存储引擎，比如：MyISAM、InnoDB等。所以，MyCat本身不存储数据，数据都是存储在MyCat后面连接的MySql上，数据的可靠性和事务都是MySql保证的。

对于开发人员，可以这样理解MyCat：

MyCat就是一个近似等于MySql的数据库库服务，你可以使用连接MySql的方式连接MyCat。绝大多数情况，你也可以使用常用的ORM框架连接MyCat，但是，对于分片的表，还是建议使用标准SQL语句，这样能够达到最佳的性能。

对于架构师来说，可以这样理解MyCat：

MyCat是一个强大的数据库中间件，不仅仅可以用作读写分离、分库分表，还可以用于容灾备份，云平台建设等，让你的架构具备很强的适应性和灵活性。
在这里插入图片描述

3.2 MyCat的应用场景

MyCat发展到现在，使用的场景很丰富，常见的典型的应用场景有：

单纯的读写分离，此时配置最为简单，支持读写分离，主从切换；
分库分表，对于超过1000w的表进行分片，最大支持1000亿的数据；
多租户应用，每个应用一个数据库，应用只连接MyCat，程序本身不需要改造；
代替HBase，分析大数据

3.3 MyCat中的基本概念

MyCat是一个数据库的中间件，介于应用与数据库之间，是进行数据处理和交互的中间服务。正是由于它是一个数据库代理的中间件，它里边也有很多新的概念，这些概念先给大家介绍一下，大家有个印象，对后面的学习也很有帮助。

3.3.1 逻辑库（Schema）

在实际的开发中，开发人员不需要知道数据库中间件的存在，开发人员只需要有数据库的概念就可以了。所以数据库中间件可以被看做是一个或者多个数据库集群构成的逻辑库。例如：上图中的例子，我们可以理解为系统先做了垂直切分，被分为了3个库，用户库，订单库，商品库，而这3个库就被称为逻辑库。

3.3.2 逻辑表（table）

既然有逻辑库，那么就有逻辑表，对于应用系统来说，读写数据的表，就是逻辑表。而逻辑表中的数据，则是被水平切分后，分布在不同的分片库中。如上图所示：假设用户库中有一张用户表，这个用户表就被称为逻辑表，而用户表又被水平切分为3个表，每一个表中都存储一部分用户数据。业务系统在进行用户数据的读写时，只需要操作逻辑表就可以了，后面的分片细节则由MyCat进行操作，这些对于业务开发人员来说时完全透明的。当然，有些表的数据量没有那么大，完全不需要进行分片，只在一个物理的数据库表中即可。

凡是我们做的数据水平切分的表，我们把它叫做分片表。而数据量比较小，没有进行分片的表，我们叫它非分片表。

在真实的业务系统中，往往存在着大量的字典表，这些表的数据基本上很少变动，比如：订单状态。我们查询的时候，往往需要关联字典表去查询，比如：查询订单时，需要把订单状态关联查出，如果订单表做了分片，分布在不同的数据库中，而订单状态表由于数据量小，没有做分片，那么我们查询的时候就要跨库关联查询订单状态，增加了不必要的麻烦，不如我们干脆把订单状态表冗余到所有的订单分片库中，这样关联查询就不需要跨库了。我们把这种通过数据冗余方式复制到所有的分片库中的表，叫做全局表。

3.3.3 分片节点（dataNode）

数据被切分后，一张大表被分到不同的分片数据库上面，每个分片表所在的数据库就叫做分片节点。

3.3.4 节点主机（dataHost）

数据切分后，每一个分片节点不一定都会占用一个真正的物理主机，会存在多个分片节点在同一个物理主机上的情况，这些分片节点所在的主机就叫做节点主机。为了避免单节点并发数的限制，尽量将读写压力高的分片节点放在不同的节点主机上。

3.3.5 分片规则（rule）

一个大表被拆分成多个分片表，就需要一定的规则，按照某种业务逻辑，将数据分到一个确定的分片当中，这个规则就叫做分片规则。数据切分选择合适的分片规则非常重要，这将影响到后的数据处理难度，结合业务，选择合适的分片规则，是对架构师的一个重大考验。对于架构师来说，选择分片规则是一个艰难的，难以抉择的过程。

3.3.6 全局序列号（sequence）

大家有没有想过，数据切分以后，数据库表的中的id怎么办？原来在一张表的时候，我们采用id自增，但是数据分布到多个库怎么办？比如：向用户表插入数据，第一条记录插入了用户库1，它的id为1；第二条记录插入了用户库2，如果是自增，它的id也为1。这样id就混乱了，我们也无法确定一条数据的唯一标识了。这时，我们需要借助外部的机制保证数据的唯一标识，这种保证数据唯一标识的机制，我们叫做全局序列号。

4 总结

通过上面我们对MyCat的介绍，我相信大家对MyCat有了一个整体的认识。在接下来体验一下MyCat的读写分离和分库分表的强大功能。

Mysql集群部署实现主从复制读写分离分表分库

赵广陆

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mysql分表分库背景知识

目录1 海量数据的存储与访问瓶颈解决方案-数据切分1.1 数据切分1.2 垂直切分1.2.1 垂直切分的优缺点1.4 水平切分1.4.1 水平拆分的优缺点1.5 总结1.6 针对多数据源的管理问题，主要有两种思路：2 如何正确使用数据库读写分离2.1 架构演进2.2 数据库读写分离2.3 读写分离的弊端2.4 如何正确的使用读写分离3 MyCat概述与基本概念3.1 什么是MyCat3.2 MyCat的应用场景3.3 MyCat中的基本概念3.3.1 逻辑库（Schema）3.3.2 逻辑表（table）3
复制链接

扫一扫