MySQL的数据拆分

🕗 发布于 2024-07-23 12:29 mysql 数据库

作者：太阳

一、拆分的概念

数据拆分

当数据过大，存储、SQL性能达到瓶颈；或多个业务共用一个数据库实例，一个小功能故障导致整个系统瘫痪；为解决类似问题，需考虑对数据进行拆分；

粗一级的拆分，针对的是业务系统，将不同业务拆至不同数据库实例；

细一级的拆分，针对的是数据库中的表数据，将一张大表拆至不同实例多个库的多个子表中。

数据拆分能成功的前提条件：

资深DB，熟悉业务情况、熟悉数据增长情况；需开发支持，与开发协同配合完成数据拆分；拆分后需能解决当前存在的痛点问题或避免不拆分后续可能出现的痛点问题。

拆分带来的影响

拆分不是目的，最终目标是为了能使业务更快更高效的运作。能不用拆分解决的，先不要考虑拆分。拆分之后业务复杂度变高、对开发的能力要求会更高，另外拆分后运维复杂度也会更高、对运维的能力要求也会更高。

拆分的思路

先整体梳理整个数据库环境，找到热点表，确认高峰期会造成性能瓶颈的SQL，单独将有问题的进行拆分。

二、什么情况下需要拆分

1、性能问题

1)响应时间太长
表数据量太大、B+ tree层数变多、写入时间变长，当写成为瓶颈时，将单节点单个大表拆成多节点多个小表，可提高写入并发(需中间件或代码解决主键唯一问题)；
当读成为瓶颈时，应该优先加从库节点或提升从库规格配置，对于大表查询有好索引的情况下不会有太大问题，拆分对于大表查询不一定有性能提升，因为需要在中间层做聚合，只在特定情况下才能提升读的效率。

2)DDL时间太长(>1h)
热点大表频繁需要做DDL变更，耗时太长很不方便，需考虑做拆分

3)连接数太多(5.6>200,5.7>300,8.0>500)
连接数太多会影响性能，如果有多个库、连接数很高，可以将不同库拆至不同实例降低以单个实例的连接数

2、容量问题

1)单实例磁盘太大导致备份时间太长；

2)单库SAS盘>300G、SSD>1T，磁盘太大对整个实例的变更操作会变得很麻烦，需要考虑拆分。

3、隔离【分级保障】

1)需要保障核心业务更加稳定：

如果不同业务不同库同实例，业务高峰期可能会不稳定；

可考虑将不同库进行拆分，可将边缘业务(该业务有较大资源消耗但业务影响很低)拆出去

2)日志表剥离：

比如日志表与核心业务表同在一个库中，日志表增长很快，容易导致磁盘不足影响业务表

三、水平拆分与垂直拆分

垂直拆分：

1、以表为级别：将同一库或不同库中的不同表拆至不同实例，使用场景较多；

2、以表中的列为级别：将同一张表根据不同列拆成多张子表，子表与原先的表，包含的列并不相同，使用场景很少；

水平拆分：

水平拆分是以表中的数据行为级别：将同一张表拆成多张子表，子表与原先的表除表名外其余结构完全相同。

水平拆分常见的是基于hash、range算法来实现，比如基于时间范围先range分区，再基于userid进行hash分区。

水平拆分有3种方式：

1、只分表：将一个库的一张表拆至一个库的多张表中，比如将db.user拆成db.user_0、db.user_1

2、只分库：将一个库的一张表拆至多个库中，每个库只有一张表，比如将db.user拆成db_0.user、db_1.user

3、分库分表：将一个库的一张表拆成多个库的多张表中，比如将db.user拆成db_0.user_0、db_0.user_1、db_1.user_0、db_1.user_1

分库的好处：降低单台机器的负载压力，提升写入性能；

分表的好处：无法降低单台机器的负载压力，可提升表的写入并发。

最常见的是分库分表的方式。

四、业务拆分需考虑的问题

1、拆成多少合适

单表<2000万，最好5G以内，最高不要超过10G

单库<1000表，总大小<1T

总的约束：一个实例小于10000张表

参考：可按照单库百表，10库10表，10库100表，100库100表的方式拆分。

2、拆分后SQL需带上拆分键

如果拆分没有带拆分键，会造成读放大；即查询会下发至每一张表上执行，然后在中间件或在前端应用程序做聚合处理，消耗很大；

如果一定要做这种操作，可先将表的全部数据同步到CK上，在CK上执行这种查询。

3、自增列全局唯一的问题

有些中间件产品内部会解决自增列全局唯一的问题；

如果是根据代码来全局生成唯一的自增列，可放到redis里统一生成；或用微秒的时间戳生成，可能会重复，需要加锁。

4、事务支持

SQL最好是可以单节点命中，即根据拆分键下发至指定节点中执行，由数据库本身的事物模型来进行支持；

对于需要多节点请求的SQL，XA事务处理，采用最终提交的方法，性能并不好。

5、join

尽可能在单个节点内进行join；

如果SQL需做跨库join，SQL拆分至不同节点分别执行，然后在中间件进行聚合，性能并不好。

6、拆分后SQL编写的注意事项

SQL最好使用拆分键进行等值的访问数据；

减少跨节点的数据库访问；

减少order by，group by排序动作；

减少分布式事务的使用。

五、拆分方案

云上的分布式数据库产品

1、阿里云的DRDS，又名PolarDB-X 1.0：

https://help.aliyun.com/document_detail/174121.html?spm=a2c4g.29657.0.0.3ca33dcbEbPhs8

产品架构：
请添加图片描述

内核架构：

请添加图片描述

2、腾讯云的TDSQL：

https://cloud.tencent.com/document/product/557

架构图：