MySQL 分库分表的基本概念和常见问题

╰半夏微凉° 2022-09-10 04:08 61阅读 0赞

> 简单介绍了分库分表的概念以及相关问题。

### 文章目录 ###

*  0 读写分离不能解决的问题
 *  1 分表
 *   *  1.1 水平切分
     *  1.2 垂直切分
 *  2 分库
 *   *  2.1 按业务分库
     *  2.2 按表分库
 *  3 分库分表的问题
 *   *  3.1 跨库join操作
     *  3.2 分布式事务
     *  3.3 跨库排序、分页、函数计算问题
     *  3.4 分布式ID
     *  3.5 数据扩容
     *  3.6 数据迁移
 *  4 分库分表工具

# 0 读写分离不能解决的问题 #

读写分离主要应对的是数据库读并发的问题，但还有其他问题不能解决：

1.  单个数据库的可支持的并发量是有限的，在高并发场景下，大量请求都需要操作数据库，导致连接数不够了，后续请求处于阻塞状态。
2.  业务量剧增，单库数据量越来越大，给磁盘存储造成巨大压力。
3.  单表数据量太大，会极大影响sql 执行的性能，对于表的DDL操作（比如想加一个索引）也会造成长时间阻塞。

查看数据库的最大连接数与响应最大连接数：

show variables like '%max_connections%';
    show variables like '%max_user_connections%';

为了解决上面这些问题，分库分表是一个非常好的办法。分库分表是两回事儿，可以光分库不分表，也可以光分表不分库，也可以两者都执行，都有可能。

相关概念：

1.  QPS：Queries Per Second 每秒处理的查询数（如果是数据库，就相当于读取）。
2.  TPS：Transactions Per Second 每秒处理的事务数(如果是数据库，就相当于写入、修改)。
3.  IOPS：每秒磁盘进行的I/O操作次数。

# 1 分表 #

**单表数据量太大，会极大影响你的 sql 执行的性能，一般来说MySQL单表数据量达到500万以上时就可以考虑分表了。**

**分表的意思就是把本数据一张表的数据分散到不同的表上，如果单表数据量太大，影响了业务接口的响应时间，但是 MySQL 数据库实例的负载并不高，这时候只需要分表，不需要分库。**

**一个数据库表太大，有两个维度来描述，一个是表的字段太多，另一个是表的记录数太多，因此分表的方案也有两个维度：切分字段（垂直切分）和切分记录（水平切分）。**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5YiYSmF2YQ_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]

## 1.1 水平切分 ##

**水平切分是对数据表记录数的拆分，把一张记录数比较多的表拆分为多张记录数比较少的表，用以减轻单张表的压力**。一般来说，单表不建议超过 500w，1亿数据分20个子表。比如uaer，拆分为user\_0、user\_1、user\_2……

**水平切分又称Sharding，即数据分片存储，每一张切分表的结构都是一样的，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。**

水平切分的方式有很多，常见的就是通过主键ID进行切分。首先确定到底需要拆分的表数目n，分表的数量需要根据目前的数据量以及未来的增长量来评估。然后使用ID对该值取余：ID%n，即可确定每一条记录所在的表。如果需要对其他非数值字段进行分表，那么可使用MD5加密之后的16进制数进行取余，或者执行hash之后进行取余。

按照这种取模的方式分表之后，查询sql时可根据参数执行查询重写即可确定数据所在的表，这种查询重写功能在Sharding-JDBC中已经实现了，我们直接引入使用即可。

这种取模的方式，会使得子表数据分布比较均匀，不容易出现热点和并发访问的瓶颈，但是后期分表扩容时，会涉及到数据的从新取模，涉及到数据的迁移问题（使用一致性 hash 算法能较好的避免这个问题）。

还有一种水平切分的方式时按照时间的维度，比如各种财务系统的财务表、流水表之类的，可以按照每日、每月、每季度、每年进行定时切分。比如bills拆分为bills\_202101、bills\_202102、bills\_202103。

按照时间连续分表，可能产生数据热点，最近时间产生的数据被访问得更多，而此前的数据不会被访问到，为此可以定时将历史时间的拆分表的数据进行合并后删除，也能能避免分表数量无限制的增加。

## 1.2 垂直切分 ##

**垂直切分是对数据表字段的拆分，把一张字段比较多的表拆分为多张字段比较少的表，用以减轻单张表的压力。**

垂直切分的字段通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中，不同的表使用ID关联。

MySQL 底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外，通常数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘 IO，从而提升了数据库性能。

垂直垂直依然存在单表数据量过大的问题 （需要水平切分），因此用得比较少，更多的还是水平切分。

# 2 分库 #

**分库，就是将一个数据库中的数据分散到不同的数据库上。如果应用的并发量太大，单个数据库实例已经无法满足需求，此时就需要进行分库。**

分库同样有两种做法：按业务分库和按表分库。

## 2.1 按业务分库 ##

这种方式为具体的功能模块单独创建数据库，将不同的表分散到不同的数据库中，以降低单个数据库服务器中的并发量。现在火热的微服务架构设计中，通常不同的服务都有自己的独立的数据库。

## 2.2 按表分库 ##

上面我们讲了分表，实际上水平分表和垂直分表之后得到的子表都可以存在于一个数据库实例中，这样可以提升单表的查询性能，但是对于整个数据库服务器来说是没用的，全部的请求还是打到这个数据库服务器上来。

通常，单表数据量很大或者增量很大一般都是对应着应用的并发量很大，因此分库和分表可以同时进行，首先对表进行分表，然后将拆分得到的子表放在不同的数据库实例中，对应着就是垂直分库和水平分库。

如果既有分库又有分表，那么在查询时需要首先定位到某个数据库，然后再定味到该数据库中的某个子表，业务逻辑更加复杂。

# 3 分库分表的问题 #

**分库分表能够有效的缓解高并发、大数据量的情况下单数据库和单表带来的性能瓶颈，但分库分表会给系统带来巨大的复杂性，不是万不得已建议不要提前使用。**

## 3.1 跨库join操作 ##

在单个库中，我们可以非常方便的使用join 进行多表关联查询。但是经过分库分表后两张需要关联的表可能都不在一个数据库中，此时无法直接使用join，有如下办法：

1.  **全局表**：也可看做是 “数据字典表”，就是系统中所有模块都可能依赖的一些表，为了避免跨库 join 查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。
2.  **字段冗余**：一种反范式设计，把需要关联的字段放入一张表中，以空间换时间，避免 join 操作；缺点是冗余字段的数据一致性较难保证，如果在主表中更新了字段的值，那么另一张表对应的冗余字段值也需要修改。
3.  **应用层组装**：将关联查询分为多次基础查询，然后在应用层进行数据的拼接；

## 3.2 分布式事务 ##

单数据库事务非常的简单，使用本地事务即可搞定，但分库分表可能会将此前在一个数据库中的表和数据分散在不同的数据库中，并且单个操作涉及到同时更新多个数据库，此时数据库本地事务就无法满足事务的要求了，此时只能通过分布式事务解决。

**分布式事务除了要遵循ACID的特性之外，还引入了CAP和BASE理论，在业界一致是个难题，分布式事务还会影响性能，常用解决方案有：基于可靠消息（MQ）的解决方案（最终一致性，柔性事务）、2（3）PC（基于 XA 协议，Mysql层面，强一致性，刚性事物）等，或者阿里的分布式事务框架Seata（支持各种事务）。**

## 3.3 跨库排序、分页、函数计算问题 ##

先在不同的数据库节点中将数据进行分页或者排序并返回，然后将不同数据库返回的结果集在应用程序中进行再次分页和排序，最终返回给用户。

在使用max，min，sum，count之类的函数进行统计和计算的时候，需要先在每个数据库上执行相应的函数处理，然后将各个结果集在应用程序中进行二次处理，最终再将处理结果返回。

上面的操作时很耗费 CPU 和内存资源。

## 3.4 分布式ID ##

如果使用 Mysql 数据库在单库单表可以使用 ID自增作为主键，分库分表了之后就不行了，会出现id 重复。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：UUID、Snowflake雪花算法生成的ID、基于数据库自增单独维护一张 ID表、Redis原子自增。

## 3.5 数据扩容 ##

当分库分表一段时间之后，如果发现性能又不够用了，那么此时又需要进行扩容操作，此时需要进行数据的迁移操作。

如果采用数值范围分片，只需要添加节点就可以进行扩容了，不需要对分片数据迁移。如果采用的是数值取模分片，在扩容之后就需要迁移数据来实现数据重平衡，既要停机迁移数据，又要修改代码或者配置，相当的麻烦，或者说成倍的扩容，这样路由规则就可以不变。

## 3.6 数据迁移 ##

正式实行分库分表的时候，需要将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）。

最简单的方法就是停机迁移，一般是在深夜的时候。如果不想停机迁移数据，那么可以考虑双写方案：

1.  部署项目，此时的代码中除了保留对老库的更新操作（增删改）操作，同时也要写入新库，这就是双写，同时写老库和新库。如果操作的数据不存在于新库的话，需要插入到新库中。这样就能保证，咱们新库里的数据是最新的。
2.  系统部署之后，因为在迁移过程双写只会让被更新操作过的老库中的数据同步到新库，我们还需要写个脚本将老库中的数据和新库的每一条数据做比对，如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。如果最后修改时间在老库中更大，则使用老库的数据覆盖新库的数据。
3.  重复上一步的操作，直到老库和新库的数据一致为止。
4.  删除更新老库的代码，再重新部署上线一次，搞定。

迁移工具可以使用阿里的Canal做增量迁移。

# 4 分库分表工具 #

**Cobar（阿里b2b早起）、TDDL（淘宝早期）、Atlas（360早期）、Sharding-jdbc、Mycat。**

Sharding-JDBC：当当开源的产品，Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。 它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

Mycat：基于阿里的Cobar改造。Mycat属于 Proxy 层方案，需要进行部署，即需要运维成本，但是对于客户端来说是透明的，不需要代码改变。

**Sharding-JDBC是基于Client的分库分表工具，不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但需要后端开发开发，对后端人员有一定的技术要求，仅支持Java语言。**

**参考资料：**

1.  《MySQL实战45讲 | 极客时间 | 丁奇》

> 如有需要交流，或者文章有误，请直接留言。另外希望点赞、收藏、关注，我将不间断更新各种Java学习博客！

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5YiYSmF2YQ_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220829/1b15454d87c0456d93be9063a97face5.png