一个前辈看得懂的分库分表方案转载本文请联系三太子敖丙公众号。众所周知，随着业务量的增长，数据量也会随之增加。这时候需要关注大业务表，因为大表会影响查询性能，DDL变化时间很长，影响业务的可用性。库延迟很大。如果业务与读写分离，会导致用户通过重复操作产生脏数据，比如重复下单。今天和大家一起探讨一下当年MySQL采用的分表分库方案，分表分库后的问题和解决方法，希望对大家有新的收获。MySQL表大小限制MySQL一般安装部署在Linux操作系统（如CentOS7.4）上，默认为InnoDB存储引擎，并启用独立表空间选项（参数innodb_file_per_table=1），此时创建一??个表orders会自动生成一个数据文件orders.ibd，文件大小受操作系统的块大小限制，下面是块大小与ext3文件系统最大大小的对应关系。操作系统块大小最大文件大小最大文件系统大小1KB16GB2TB2KB256GB8TB4KB2TB16TB8KB16TB32TB检查操作系统页面大小和块大小这意味着单个MySQL表的最大大小不能超过2TB。让我们做一个简单的计算。假设一张表的平均行长为32KB（InnoDB的最大行长限制为65536字节，64KB），最多可以存储多少行数据？4x1024x1024x1024/32=134217728，不足1.4亿。对于饿了么来说，美团外卖交易系统的订单表是1.4亿，轻而易举。平均每天2000万单，一周会达到1.4亿单。用户所在地将数据写入对应的IDC数据中心。这其实是一种高端的分表方案，不在我们今天的讨论范围之内。分表方案的应用场景是单表数据量增长过快，影响业务接口响应时间，但MySQL实例负载不高。).我们知道一个表的大小满足以下公式：TABLE_SIZE=AVG_ROW_SIZExROWS，从这里我们可以知道表太大，或者平均行长太大，也就是字段太多在表中，或表中的记录数太多。这样就产生了两种不同的分表方案，即拆分字段（垂直拆分）和拆分记录（水平拆分）。垂直分表，以订单表orders为例，按字段分表。这里，需要考虑一个问题，如何拆分字段，使表上的DML性能最大化。常规的解决方案是冷热分离（经常使用的字段放在一张表中，其余使用率低的字段放在另一张表中）。orders表拆分后变成两张表orders01和orders02。两个数据文件orders01.ibd和orders02.ibd将存储在磁盘上。订单表的最大大小为4TB。拆分后，如何查询？例如：分析上面的SQL，select后面的列位于两个表中（order_id，order_sn在orders01，source在orders02），上面的SQL可以改写为如下形式。如果使用数据库中间件，会自动实现查询重写，比如mycat，sharding-sphere，不用中间件也可以实现，但是稍微麻烦一点，可以创建路由表（主键ID，原表名,字段名,子表名),每次解析SQL时,需要根据原表名+字段名获取需要的子表,然后重写SQL,执行SQL并返回结果.这种代码修改量太大，容易出错，所以这种垂直拆分在实际业务中用的不多。如果业务表中有必要的Text类型来存储数据，可以使用垂直拆分来减小表的大小，将文本字段拆分成子表。这样，将文本类型拆分成分表后，原表的平均行长变小，可以存储更多的数据。水平分表水平分表就是按照表中的记录进行分片。比如orders表，目前有2000万条数据。按照业务的增长，估计一年后会达到1亿。也可以参考AlibabaCloudRDSforMySQL的最佳实践，单表建议不要超过500w，1亿条数据分20个分表就够了。问题是，拆分的依据是什么？主键id还是用户的user_id。通过主键ID拆分数据非常统一。几乎没有通过ID查询订单的场景。业务接入单的大部分场景都是根据user_id进行过滤的。而且user_id的唯一性很高（一个user_id对应的orders表中记录不多，选择性很好），使用user_id作为shardingkey可以满足大部分业务场景，每个sub的数据-拆分后的表比较统一。这样，orders表被拆分成了20个子表，分别对应InnoDB存储上的20个数据文件（orders_0.ibd、orders_1.ibd等）。此时执行SQL语句selectorder_id,order_sn,sourcefrom**orders**whereuser_id=1001;可以在orders_1中快速定位到你要找的记录的位置，然后做query重写，转换成SQL语句selectorder_id,order_sn,sourcefrom**orders_01**whereuser_id=1001，这个很多中间件都有实现了这个查询改写功能，常用的有sharding-sphere或者sharding-jdbc。对于会计或计费系统，每晚会做前一天的日结账或日账任务，每月1号做月结账或月账任务。任务执行完毕后，相关表中的数据将全部静态化（业务层不需要这些数据）。根据业务特点，可以按月建表。例如bills表bill，可以按月创建子表（表bills_202010为10月，表为202011年11月），当月账任务完成后，可以归档到历史数据库，在数据中使用仓库ETL做分析报告。确认数据同步到历史数据库后，可以删除这些表以释放空间。您可能会想到MySQL分区表。经过上面的水平分表，需要修改代码，保证SQL正确路由，执行并返回结果。这个调用链接有点长。MySQL中的表分区有解决方案吗？?其实是有的。可以考虑使用MySQL的HASH分区。常规哈希也是基于分区数的取模（%）运算。和上面的user_id%20是一样的。让我们看一个例子。这样就创建了20个分区，对应磁盘上有20个数据文件（orders#p#p0.ibd到orders#p#p19.ibd），我们来看看SQL的执行过程。从执行计划可以看出，通过分区键user_id过滤，可以直接定位到数据所在的分区p19（user_id=1019%20=19，所以在p19分区上），然后访问数据文件命令#p#对应p19p19.ibd来获取数据。这种方案的好处是MySQL内部实现了SQL路由的功能，不需要修改业务代码。分库方案下面说说分表方案。数据库什么时候分库？我们知道MySQL的高可用架构大多是一主多从，所有的写操作都发生在Master上。随着业务的增长，随着数据量的增加，很多接口的响应时间变得很长，经常出现Timeout，升级MySQL实例配置无法解决问题。这时候就必须对数据库进行划分。通常有两种方式：按业务拆库和按表分库，下面分别介绍两种分库方案。比如按业务分库，将交易系统的交易数据库单独部署在一个RDS实例上。现在交易需求和功能越来越多，订单、价格、库存相关的表增长很快，部分接口的耗时增加。同时存在大量慢查询告警，升级RDS配置收效甚微。这时候就需要考虑拆分业务，把库存和价格相关的接口分开。按照业务模块拆分后，对应的贸易数据库拆分为三个RDS实例。提高了数据库的写入能力，缩短了服务接口的响应时间，提高了系统的稳定性。上面介绍了分表方案。常见的有垂直分表和水平分表（拆分后的子表都存储在同一个RDS实例中），对应的分表数据库有垂直分表和水平分表，这里的分库其实就是拆分RDS实例，将拆分后的分表存放在不同的RDS实例中。垂直分库在实际业务中很少用到，就不介绍了。我主要介绍横向分库。比如事务数据库的订单表有2亿多条数据，RDS实例遇到了写瓶颈。一次正常的insert需要50ms，经常会收到CPU占用率告警。这时候就要考虑拆分数据库了。根据业务量的增长趋势，计划扩容一个相同配置的RDS实例，将订单表orders拆分为20个子表，每个RDS实例10个。这样就解决了orders表过大的问题。查询时，首先要通过分区键user_id定位到RDS实例，然后定位到具体的子表，再进行DML操作。问题是代码改造工作量大，服务调用链路变长，对系统稳定性有一定影响。事实上，一些数据库中间件已经实现了分库分表的功能，比如常见的mycat，阿里云的DRDS等。通过以上分表分库方案的介绍，分布式数据库将主要遇到以下三类问题：单个MySQLmaster的写性能瓶颈。分库分表后的SQL解析过程，服务调用链路变长，系统变得不稳定。分库分表后动态扩容不易实现。比如一开始分了20张表，想在不影响业务的情况下扩容到50张表就不容易了。拆分后的问题垂直拆分跨库Join问题垂直拆分前，系统中需要的数据可以通过表Join来完成，而拆分后数据库可能分布在不同的RDS实例中，Join处理起来比较麻烦。按照MySQL开发规范，一般是禁止跨库join的。如何处理？首先，应该考虑这种纵向拆分的合理性。如果可以调整，则应先调整。根据实践经验，总结出几种常见的解决方案。全局表用过mycat分库分表的朋友都知道，有一个全局表的概念，就是每个DataNode都有全量的数据，比如一些数据字典表，数据很少被修改，这样可以避免跨数据库的Join性能问题。数据同步对于分布式系统，不同服务的数据库分布在不同的RDS实例上。当禁止跨库join时，数据同步是一种解决方案。通过数据同步工具将用户库的users表实时同步到交易库中，这样就可以直接在交易库中进行Join操作，这就比较依赖于同步工具的稳定性。如果同步有延迟，会导致数据不一致和脏数据。数据，需要做好风险评估和备份计划。分布式事务问题拆分后，数据分布在不同的RDS实例上，对表的DML操作变成了多个子表的DML操作，涉及到分布式事务，必须遵循事务的ACID特性。提到了两个重要的理论：CAP（Consistency一致性，Availability可用性，Partitiontolerance分区容错）和BASE（BasicallyAvailable，Softstate软状态，Eventuallyconsistent最终一致性），然后是针对分布式不同场景下业务问题的解决方案。MySQLXA事务MySQL支持分布式事务（XA事务或2PC两阶段提交），分为两个阶段：Prepare和Commit。在事务中滚动他们的信息的好处是可以最大程度保证数据的强一致性，适用于对数据一致性要求高的业务场景；缺点是实现复杂，可用性牺牲，性能影响大，不适合高并发的高性能场景。本地消息表本地消息表的实现应该是业界用的最多的了。它的核心思想是将分布式事务拆分成本地事务进行处理。基本的设计思想是将远程分布式事务拆分成一系列本地事务。Processmessageproducer：需要额外创建一个消息表，记录消息的发送状态。消息表和业务数据必须在一个事务中提交，也就是说必须在一个数据库中。然后消息会通过MQ发送给消息的消费者。如果消息发送失败，将重试。消息消费者：需要处理这条消息，完成自己的业务逻辑。此时如果本地事务处理成功，说明处理成功。如果处理失败，则将重试执行。如果是业务失败，可以向生产者发送业务补偿消息，通知生产者进行回滚等操作。生产者和消费者定期扫描本地消息表，重新发送未处理的消息或失败的消息。如果有靠谱的自动对账和补货逻辑，这个方案还是很实用的。水平拆分分布式全局唯一IDMySQLInnoDB表均采用自增主键ID。数据库分表后，数据表分布在不同的分片上。如果使用自增ID作为主键，会出现不同分片上的主机。在ID重复的情况下，可以使用Snowflake算法生成唯一ID。shardkey的选择在选择shardkey的时候，需要先统计表上所有的SQL，尽量选择使用频率高、唯一值多的字段作为shardkey，这样不仅可以达到一个均匀的分布数据，也能快速定位数据Location，比如user_id，order_id等。以数据扩容为例。目前，交易数据库trade中的order表orders已经横向划分（位于两个不同的RDS实例上）。这时发现两个RDS的写入性能不够，需要再扩容一个RDS。同时要将订单从原来的20个分表扩充到40个（user_id%40），需要进行数据迁移，实现数据再平衡。需要停止数据迁移，修改代码，有点吃力不讨好。跨库join问题和垂直拆分中的跨库join问题是一样的。在处理orderbyuser_idlimitn场景进行跨库排序分页时，当排序字段为分片字段user_id时，可以通过分片key轻松定位具体分片，当排序字段不是分片字段时，例如，按create_time排序将变得难以处理。需要对不同分片中的数据进行排序返回，对不同分片返回的结果集进行汇总和重新排序，最终返回给用户。跨库函数处理在使用max、min、sum、count等函数进行统计和计算时，需要先对每个分片数据源进行相应的函数处理，然后再对每个结果集进行二次处理。最后返回处理结果。ER碎片在RDBMS系统中，表与表之间往往存在一些关联。如果能先确定关联关系，把有关联关系的表记录放在同一个分片中，就可以避免交叉分片。切片连接问题。非shardingkey过滤大部分业务场景都可以基于shardingkey进行过滤，但是有些场景没有shardingkey过滤，比如按照状态和时间范围查询订单表订单，比如普通的SQL。这是非常痛苦的。只能扫描一次所有的分片数据，等每个分片的数据Union之后再回复客户端。这种场景可以考虑创建复合索引（status,create_time），让SQL扫描索引范围，并减少返回的数据量。如果是核心业务场景，可以考虑实时实时数仓（如基于MPP架构的分析型数据库ADB、分布式列式数据库Clickhouse），将需要的表实时同步到数仓，以及then然后再做处理，这也是实际业务中常见的解决方案。综上所述，讲了MySQL的分表方案，分库方案，拆分后的问题，常见的解决方法。在实际开发中，核心业务表会快速增长，数据量会很大。MySQL写性能瓶颈问题。这时候就需要根据业务的特点来考虑分库分表了。您可以研究相关解决方案。主要有两种解决方案：代码改造（数据库中间件mycat、sharding-sphere）和分布式数据库（PingCAPTiDB和阿里云DRDS在实际业务中被广泛使用），可以优先使用分布式数据库方案。虽然成本会增加，但对应用程序没有侵入性，可以更好地支持业务增长。快速迭代系统，今天就说这么多，希望对大家有所收获。好了，以上就是本期的全部内容。我是敖丙你知道的越多，你不知道的就越多。下期见。
