MySQL数据库性能优化--数据分库分表

小咪咪 2023-09-29 12:21 2阅读 0赞

**目录**

前言

1.什么时候需要分库分表？

1.1.第一次改造

1.2.分库分表的必要性

1.3.第二次改造

2.分库分表应该怎么分？

3.垂直分库会带来哪些问题？

3.1.跨库的关联查询

3.2.分布式事务

4.水平的分库分表带来哪些问题？

4.1.动态数据源选择问题

4.2.水平分库分表以后的翻页的查询问题

4.3.全局ID问题

5.Sharding JDBC分库分表实战

5.1.核心概念

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

## 前言 ##

1.什么时候需要分库分表？  
2.分库分表，到底怎么分？  
3.分库分表带来的问题与解决方案  
4.Sharding JDBC分库分表实战

一般公司里面接触的项目的数据量比较少，比较小，用的数据库的优化的手段，里面还没有用到分库分表，那什么时候我们要用分库分表呢？分库分表能够帮我们解决什么样的问题？**这个是我们第一个要探讨的;**

我们的表已经到了要分库分表的地步的时候，分库分表到底又要怎么分呢？**这是第二个问题：**

**第三个问题：**其实大部分时候我们都没有用到分库分表，毕竟它会给我们的业务系统的使用带来很大的复杂性，这个复杂性到底包括了什么样的问题？为了解决这些问题，我们的解决方案又是什么样的?

**最后一个：**基于客户端的分库分表的解决方案，在springboot里面到底是怎么样使用，到底是怎么帮我们解决我们前面说的分库分表所带来的系统的复杂性的问题的

## 1.什么时候需要分库分表？ ##

**分库：**将原来存放在单个数据库中的数据，拆分到多个数据库中存放。

**分表：**将原来存放在单个表中的数据，拆分到多个表中存放。

这里以消费金融系统为例，这是一个典型的**单体架构**的应用，单体架构的应用的特点：就是所有的代码，都在一个工程里面，把这一个工程的所有的代码打成一个war包，一个war包打天下。

![dc0fee9fbc4d415db8ac91c05a0c9607.png][]

### **1.1.第一次改造** ###

随着业务发展，代码会逐渐越来越大，原来的一套单体架构的应用要支撑的这个并发访问的数数量也要越来越高，明显不够用，常见优化方法：优化数据库，优化sql语句，加缓存等，都无法解决高并发问题。

此时，系统拆分刻不容缓，按照业务的流程 业务的模块的分类 拆分成了很多的子系统：客户系统 合同系统 放款系统。。。。。。

![19d1e13f997241838b6cf45850a181f3.png][]

最开始的时候 我们刚把它拆分出来的时候，他们依然是共用一套核心数据库，简称为核心数据库。

这样去对我们的应用系统去做一个拆分，它会有什么样的好处呢？原来是一个工程，现在分了很多子模块，但是它最终还是一个工程，现在把它拆分成了一个一个独立的工程，这样拆分有什么样的好处：**互不影响、解耦。**

**至此：原来我们一个系统使用一个数据库，变成了多个子系统去共用一个oracle的核心的数据库，完成第一次改造。**

> **存在问题:多**个子系统共用一个核心的数据库，无论是从性能还是从存储的角度来讲，其实都是满足不了需求。

### **1.2.分库分表的必要性** ###

a.一个物理数据库 它能够支持的并发访问量是有限，也就是出现了数据库的性能瓶颈的时候;

b.从存储的角度来讲，数据库的本质是以文件存放在磁盘上面，数据越来越大，那占用的磁盘的空间也会越来越大，单个数据库的服务的节点满足不了需求

###  **1.3.第二次改造** ###

一个数据库满足不了业务访问的需求 这个时候我们是不是也要对我们原来一个一个子系统，他的数据库也去做一个拆分 是不是所以这个我们后第二次改造 ，把原来的每一业务系统相关的表独立拆分出来 变成了自己的数据库

![ea00db47da0a486fa306ef77cec5b202.png][]

是为了解决数据库的性能瓶颈的问题 ，最终导致影响我们的应用的性能，才会去做这种数据库的拆分，所以数据库分库， 它其实是我们的系统在拆分过程中，因为我们的模块要拆分，或者子系统要进行一个划分，它最终带来的一个必然的结果。

在我们分库之后，如果单张表的数据量过大 它会带来我们的数据库的访问的性能的下降以及存储的性能瓶颈的问题，这个时候我们还要把一张表里面的数据再拆分到多个库去存储，原来的一个客户表 我把它拆分成了三张客户表，放到了三个数据库里面去存储，就叫做**水平的分库。**

**数据水平拆分**

![2900d0cd4cb146c9bf42d4538e592107.png][]

>  **把数据去做一个分散的存储 它能够带来什么样的好处呢?**

**读写快**：打个比方你从一张有一条数据的表里面去检索一条数据和从一张只有100万条数据的表里面去检索数据 他的 他的消耗的时间肯定是不一样的；

**缓解数据库的压力：**把所有的数据都放到一个数据库的服务的节点上面存储的时候，他要承受所有的应用访问的压力，做了分库分表以后 那所有的节点 他们是不是可以去共同分担来自于我们的客户端的访问的请求；

**提升存储容量:**一张表或者一个库 到底能存存多少数据啊 它是受限于你的硬件(磁盘的大小),表去做了一个 拆分的时候分布到多个节点上面的时候 它能够存储的数据就更多

> **一张表存储的数量达到多少的时候才需要去做一个分表的处理呢？**

**阿里巴巴开发手册【推荐：**单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。  
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

这个是不一定，这个只是加阿里内部推荐，并不是强制执行的一个规约，以我们业务的实际情况为准，看你的表表结构表的字段，以及你的表情况，如果表的字段不是特别多，对于你常用的这些查询，已经建立了合适的索引，那么一张表去存储千万级别的数据是问题不大的。

>  所以，分库分表不是以数据量为准的，而是业务的实际的使用为准。

## 2.分库分表应该怎么分？ ##

> 分库分表维度：垂直和水平两种

![0210220b01674c3a8f382c74c8cca657.png][]

**水平的切分 ：**是基于这个数据来划分的 ，划分得到的结果是表结构相同的，只是存储的数据不同而已

**垂直的切分：**它是按照这个表结构来 或者说字段来划分的，切分以后会得到不同的业务相关表 。一个是单库的垂直的切分， 把一个表分成多个表，在同一个库里面存储；还有就是不同的业务相关的表放到不同的库里面去存储，也是垂直切分。

## 3.垂直分库会带来哪些问题？ ##

![6b42fc4e05bb48e3914ae4636ee60d2c.png][]

### **3.1.跨库的关联查询** ###

解决方案：RPC-系统层的组装、缓存、视图、字段冗余，数据同步(binlog、ETL、MQ)

### **3.2.分布式事务** ###

本地的事物，它有**ACID**的特性，要保证在一个业务流程里面对于一张表 ，或多张表的这种操作要么都成功 ，要么都失败 ，这样才能实现数据的一致性，把这些数据分散到多个数据库里面去存储，在一个贷款的流程里面，跨库的数据过程操作的动作不是全部，同时成功会不是同时失败的话，也会出现数据了一次性的问题

**解决方案：**协调者--在一个尽量短的时间里面把这两个数据提交的动作,同时做完了， 其实是缩短了这个在各个库里面提交数据时间，降低提交失败的一个风险的概率而已

也或者两阶段提交，三阶段的提交 还有TCC的这种补偿的事务，基于MQ实现的这种消息的最终一致性。

例如：

**Fescar—SEATA  
Atomikos  
LCN**

> **小结：**垂直的分库，要去解决的一些问题，一个是跨库的关联查询；一个要保证我们的数据在多个库里面要么都成功，要么都失败来实现数据的一致性
> 
> **垂直分库：**当我们对这个一个库里面的原来的不同的业务相关的表去做了分库的处理 ,并没有解决解决这个单表的数据量过大的这个问题

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

## 4.水平的分库分表带来哪些问题？ ##

> **单库水平切分:**一张表在一个库里面存储不下的时候，把它切分成了多个数据库，放到了不同的数据库里边，把它叫做水平的分库分表

![97caecab2a484d7795e73dc78d91dd00.png][]

水平的分库分表，又会带来一些什么样的问题呢

### **4.1.动态数据源选择问题** ###

无论你是分库还是水平的分库分表，那对于我的应用层来使用 你原来是不是只要在你的项目里面配置一个数据源，我只要查一个库就行了 现在你把它分散到了多个库里面去存储 第一个问题来了我的数据源,怎么配置呢 ok 假设我是可以配置五个数据 但是我的这个应用层如何选择怎么选择数据源，我们选择自己去实现这种动态的数据源的选择

**自定义一个注解 然后把这个注解加在我的方法上面 而这个注解呢 它能够实现在访问数据库的时候 是去连接一个数据源**

**编码层：**用aop加上一个注解实现在spring里面，有一个抽象类AbsrtactRoutingDataSource，继承就可以实现动态的数据源的选择

我们把实现的动态的数据源选择，以及我们前面排序，翻页，还有跨库的关联查询等，这些一系列的逻辑全部抽取出来，做成一个独立的服务，由他去连接数据库，应用只要来访问这一个运行的服务，这种方案 我们把它叫做代理层的动态的数据员选择的方案

Sharding JDBC和mycat的区别，在于一个工作在客户端的，集成在我们的项目里面用的，一个它是要运行一个独立的服务，可以把它当做一个数据库来用因为它也实现了jdbc的规范

![37091aef89b04422a5537f03dbf46d87.png][]

### **4.2.水平分库分表以后的翻页的查询问题** ###

**查询范围数据时，需要与**跟多个数据库建立连接，然后把这个数据拿到一起，然后再做一个汇总，然后再排序，才能返回给客户端，没有别的办法 你的数据就是这么分布的 你要拿到完整的数据 你只能从这里拿一条 这里拿一条正能来条，拿到你全部需要的数据 再重新在你的代码在你的内存里面再去做一个处理，一般这个也不需要我们自己去写代码去实现啊 在中间键里面，这个是一个非常简单的问题

### **4.3.全局ID问题** ###

把这个不同的数据放到了不同的库里面的不同的表去存储，每一张表是不是有他自己的增长规则，这个时候就出现了这个id的重复的问题 ，不能用它来作为业务意义上面的这个主键。

这个时候我们的我既要让他在多个库里面保持递增，又要连续，所以这个是全局id的问题，

> **可以用哪些哪些办法去解决?**

**解决方案**:UUID、雪花算法、redis、ZK、美团的Leaf、以及最简单的加锁，数据库自定义表存主键，自增生成(序号表),拿一个id之前先锁表；

UUID为什么能作为全局ID，因为它的重复的概率极小极小，并不是说他不会重复重复，所以他可以用来做全局的id，我们不建议用它来作为全局的id，或者说作为主见，因为它不能排序 所以这个是一个不好的地方

Redis因为他也也有这种原子性的操作，我们可以基于它的原子性来生成

**原则 ：**满足不重复而且不会变 小 那就是ok了，最好不要让他连续，可能会暴露业务量

## 5.Sharding JDBC分库分表实战  ##

[概览 :: ShardingSphere][_ ShardingSphere]

ShardingSphere:工作于驱动层，本质上也是对JDBC的四大对象进行封装。起源于当当，从当当的RDB组件里边脱离出来，捐赠给Apache的一个轻量级框架。

> **`连接`**、**`增量`** 和 **`可插拔`**是 Apache ShardingSphere 的核心概念。
> 
>  *  `连接`：通过对数据库协议、SQL 方言以及数据库存储的灵活适配，快速的连接应用与多模式的异构数据库；
>  *  `增量`：获取数据库的访问流量，并提供流量重定向（数据分片、读写分离、影子库）、流量变形（数据加密、数据脱敏）、流量鉴权（安全、审计、权限）、流量治理（熔断、限流）以及流量分析（服务质量分析、可观察性）等透明化增量功能；
>  *  `可插拔`：项目采用微内核 + 三层可插拔模型，使内核、功能组件以及生态对接完全能够灵活的方式进行插拔式扩展，开发者能够像使用积木一样定制属于自己的独特系统。

![8cb292187f3943688b98f014a54484dc.png][]

原来这是我们的这个java的项目,自己配置数据源,如果要用Shardingjdbc帮你动态的选择一个你分片以后的数据库,先引入依赖 ,进行一些简单的配置，访问数据库的时候他会先经过Shardingjdbc的代码 再到jdbc的代码 最后再到数据库，所以他叫做一个增强胆的jdbc的驱动.

### 5.1.核心概念 ###

![b25c7a4e30014460966864983306da3e.png][]

**真实表**

真实表，你原来是一张表，把它切分到了多个数据库，现在变成了多个库里面的，每个库里面都有一张表 这个就叫它是我们的物理数据库的节点里面实际存在的表 这个叫做真实表

**逻辑表**

对于我们的应用来说,当我们用了这种分库分表的中间件去访问我们的数据库以后 我们的代码里面sql里面，不需要做任何的改动，依然是把这个不同的数据库里面的表，当成是一张表来使用的 ，感觉不到它的变化 这个就叫做逻辑表

**分片键**

我们要把原来的一张表的数据，分片到多个节点上面的时候 我们必须要制定一个规则，选择一个字段或者多个字段，大部分情况下我们会用一个字段,决定我们的数据的分片，那么这个字段呢 我们就把它叫做ShardingColumn（分片键）

**分片算法**

有了这个分片键之后，数据要怎么划分呢？常见的数据拆分的分片的算法或者叫做分片的策略：一致性哈希(哈希环)、**取模**、时间、范围、随机(数据不均匀)

取模：**user\_id%3=0放到第一个数据，user\_id%3=1放到第二个数据，user\_id%3=3放到第三个数据**

**日期：按天，按周，按月存储**

**组合：先取模后范围**

**范围：1-2000W,2000-4000W**

**综合考虑：**一定是根据业务的实际的情况，尽量减少它的数据的重新的分配，又要尽量的避免热点数据的问题，根据业务情况来选择一种适合于自己的分片的这个算法

mycat相对于Shardingjdbc的一个优势在于自自带了很多的分片的策略，比如取模，哈希，日期等

**动态表**

**表的名字会动态变化的一些表，比如有一种按月度划分的表，在每一个库里面 他的表名是不一样的，order202201,order202202......**

![9cd0dd2d1d8d4709a4bed6a312765f8e.png][]

他会根据你的查询条件自动把这个后面的表名的后缀拼接上去 那么这种表我们就把它叫做动态表 我们的应用层不需要去改变，只要用那个逻辑表的名字就行，

还有一个叫做广播表：有一些数据，它的数据量不是特别大 几万十几万变化又很小 而且在每一个库里面都要存储相同的数据，更新数据的时候 或者插入数据的时候，必然要同时在这所有的库里面执行相同的操作 而你要查询数据的时候 因为他们的数据已经保持重复了，从其中的任何一个节点拿拿到数据都是个ok的这样的表 我们把它叫做广播表

还有一个绑定表 这个广播表是为了我们说为了解决那种跨扩的关联查询的问题的一个解决方案 只要在所有的库里面都存储相同的数据 ，

那什么叫做绑定表呢 他其实也是我们避免或者说解决跨库的关联查询的问题的一个解决方案，逻辑的从属关系（一个订单多个商品）

![971df74a8168485ebb11b08f2080c1d1.png][]

他有一些表是有这种逻辑的从属关系的：一个订单表 ，一个订单它里面又可以包含很多不同的商家的不同的商品，不同的数量，不同的金额，这个叫做副表，例如这个订单有三个商品，这种有逻辑的同属关系副表和子表 ，也叫主表和明细表 。

但是：每个商品的他的商品标和单价，他们通常会有一个关联查询的操作，比如我要查询一个明细的订单的时候，把这个主表里面的orderid去关联，副表是存了关联主键的值

有个问题，当业务量越来越来越大的时候，把这个订单主表的这个数据分布了几个库里面去存储，你主表用的是主表的分片的规则 他用的是范围 而子表呢用的是自己的分片的规则 他用取模，然后这个时候 我们要去做关联查询的时候 我发现，这个0001的订单跟这个001的子表的数据 它在不同的库里面,没法做关联查询，有没有办法让这样的，父表和子表的这个id的值相同的数据一定分布到同一个节点上面呢，

那我们就要对我们对于这样的表去分片的时候 就要让他们使用相同的分片键 都用id去分片，而且要配置相同的分片的规则 比如说都用取膜，这样我们就能保证这样的数据能够落到相同的库里面去，关联查询是不是就不会存在跨库的这个问题，所以这种表呢 这种分片的规则 我们把它叫做绑定表

[dc0fee9fbc4d415db8ac91c05a0c9607.png]: https://img-blog.csdnimg.cn/dc0fee9fbc4d415db8ac91c05a0c9607.png
[19d1e13f997241838b6cf45850a181f3.png]: https://img-blog.csdnimg.cn/19d1e13f997241838b6cf45850a181f3.png
[ea00db47da0a486fa306ef77cec5b202.png]: https://img-blog.csdnimg.cn/ea00db47da0a486fa306ef77cec5b202.png
[2900d0cd4cb146c9bf42d4538e592107.png]: https://img-blog.csdnimg.cn/2900d0cd4cb146c9bf42d4538e592107.png
[0210220b01674c3a8f382c74c8cca657.png]: https://img-blog.csdnimg.cn/0210220b01674c3a8f382c74c8cca657.png
[6b42fc4e05bb48e3914ae4636ee60d2c.png]: https://img-blog.csdnimg.cn/6b42fc4e05bb48e3914ae4636ee60d2c.png
[97caecab2a484d7795e73dc78d91dd00.png]: https://img-blog.csdnimg.cn/97caecab2a484d7795e73dc78d91dd00.png
[37091aef89b04422a5537f03dbf46d87.png]: https://img-blog.csdnimg.cn/37091aef89b04422a5537f03dbf46d87.png
[_ ShardingSphere]: https://shardingsphere.apache.org/document/current/cn/overview/
[8cb292187f3943688b98f014a54484dc.png]: https://img-blog.csdnimg.cn/8cb292187f3943688b98f014a54484dc.png
[b25c7a4e30014460966864983306da3e.png]: https://img-blog.csdnimg.cn/b25c7a4e30014460966864983306da3e.png
[9cd0dd2d1d8d4709a4bed6a312765f8e.png]: https://img-blog.csdnimg.cn/9cd0dd2d1d8d4709a4bed6a312765f8e.png
[971df74a8168485ebb11b08f2080c1d1.png]: https://img-blog.csdnimg.cn/971df74a8168485ebb11b08f2080c1d1.png