MySQL亿级流水表分表实践：Sharding-JDBC选型、多表分页与数据迁移详解

笔者在两年前接手公司财务系统的开发和维护工作。在系统移交的初期，我们团队就发现系统内存在一张数据量超过5000万的大表。

通过跟踪代码发现，该表用于存储资金流水，关联着众多功能点，同时也有众多的下游系统在依赖这张表的数据。

进一步的观察让人更加担忧：这张表还在以每月600万条以上的速度持续增长。这意味着，不超过半年，这张表的数据量就会突破一亿大关！

对于 MySQL 数据库而言，这个数据量级是绝对无法继续有效维护的。因此在接手系统两个月后，我们便启动了针对这张大表的拆分专项工作。

拆表前系统状态

在拆分之前，系统已暴露出明显的性能问题：

• 涉及到流水表查询的接口超时频发，部分接口基本处于不可用状态。
• 每日新增流水数据缓慢，瓶颈主要出现在数据插入环节。
• 单表占用磁盘空间过大，DBA的数据库监控经常触发报警。
• 无法对表结构进行任何变更，任何 ALTER 操作都会引起主从同步的高延迟和长时间锁表。

拆表的核心目标

我们为这次拆分工作设定了明确的目标：

1. 将流水大表数据拆分至各个分表，保证每张分表数据量控制在1000万左右（从经验来看，单表1000万对MySQL压力不大）。
2. 在拆分的前提下，针对不同接口的查询条件进行优化，保证所有对外、对内接口的可用性，彻底解决MySQL慢查询问题。

项目面临的难点分析

这次拆分绝非易事，我们面临着多方面的挑战：

• 影响范围广：该表数据是整个财务系统最基础的数据，相关功能和下游系统非常多。这要求开发、测试和上线流程必须极其严密。
• 改造场景复杂：涉及26个业务场景，需要改造32个Mapper方法，具体需要修改的代码方法更是数不胜数。
• 数据迁移风险高：原始数据量巨大，迁移过程必须保证系统稳定，不能影响线上业务。
• 上线要求苛刻：用户基数大且功能重要，要求我们尽量压缩系统不可用时长，同时设计完整可靠的上线、回滚及降级策略。
• 协调难度大：表的拆分会引起部分接口变化，进而需要推动其他关联系统进行改造。如何协调多方进行开发、测试和上线是另一个难点。

整体工作流程

我们为整个分表项目制定了清晰的工作流程，如下图所示：

具体实施细节

分表中间件调研

在技术选型阶段，我们选择了 Sharding-JDBC 作为分表插件。其优势在于：

1. 支持多种分片策略，能自动识别 = 或 IN 条件以判断数据所在的具体分表。
2. 属于轻量级组件，作为Maven依赖引入即可，对业务代码的侵入性较低。

在项目初期，团队也曾考虑引入Elasticsearch（ES）来存储流水数据以提升查询速度。但经过与公司ES维护团队的两轮深入讨论，我们发现公司提供的ES服务与我们的业务场景匹配度不高，具体对比如下表：

综合考虑后，我们最终放弃了引入ES的计划，决定直接从数据库查询数据，并采用为每张分表分配一个独立查询线程的方式来提升查询效率。

分表依据的选择

分表的方式有多种，如纵向分表、横向分表、按固定数量分表后取模等。结合资金流水数据“只增不删”的业务特性，适合我们的只有横向分表（按时间或范围分表），这样才能解决单表数据无限膨胀的问题。

分表依据的选择需要结合数据和业务进行综合分析，主要原则有：

• 尽可能选择查询条件中最常出现的字段，以减少代码改造工作量。
• 需确保根据该字段拆分后，数据能均匀分布，满足单表1000万左右数据量的要求。
• 该字段必须是必现字段，不允许出现空值。

综合分析后，“交易时间”成为最合适的分表依据。首先，这个字段是流水核心字段，必然存在；其次，若按交易月份拆分，每张表数据量大约在600-700万；最后，据统计，有70%的查询都附带“交易时间”作为条件。

遇到的技术难点与解决方案

1. 多数据源事务问题
Sharding-JDBC在使用时需要配置独立的数据源，这就不可避免地引入了多数据源事务问题。我们通过自定义注解和切面来管理事务，采用根据方法调用栈逐层回滚或提交的方式解决。出于保密考虑，具体代码细节在此不展开。

2. 多表联合查询的分页问题
分表后，跨表分页查询的复杂度大大增加。由于各分表查出的数据量不等，原始的SQL LIMIT 语句不再适用，需要设计新的分页逻辑。

我们的解决方案是：在出现跨表查询时，为每一张分表启用一个独立线程进行查询以提高效率。此方案的难点在于分页条件的转换。

例如，页面传入的 offset=8, pageSize=20。假设三个分表中符合条件的数据量分别为10, 10, 50条。那么我们需要将总的分页条件转化为三个分表各自的分页条件，转换过程如下图所示：

如图所示，总的分页条件 (offset=8, pageSize=20)，被转换为了三个子条件：(offset=8, pageSize=2)，(offset=0, pageSize=10)，(offset=0, pageSize=8)。

整个计算过程如下：

1. 多线程并行查询各分表中满足条件的数据总量。
2. 将各表数量按分表顺序累加，形成一个虚拟的数轴。
3. 判断请求数据的第一条和最后一条分别落在哪个分表区间内。
4. 除第一条和最后一条数据所在的分表外，其他中间分表的查询条件设置为 offset=0, pageSize=该表总量。
5. 计算第一条数据所在分表的 offset 和 pageSize。
6. 计算最后一条数据所在分表的 pageSize，并将其 offset 设置为0。

数据迁移方案

在数据迁移前，团队讨论过两种方案：DBA迁移和手写代码迁移。它们各有优劣：

综合考虑时间成本和对线上数据库的影响，我们决定采用两者结合的方案：

• 冷数据处理：交易时间在三个月前的历史数据，由于更新概率低，采用代码方式迁移。人为控制每次迁移的数据量，少量多次，像“蚂蚁搬家”一样在平时完成。
• 热数据处理：交易时间在三个月内的近期数据，由于上线前可能频繁更新，则在上线前短暂停止写操作，然后由DBA进行整体迁移。这样将时间成本平摊到平时，最终上线时系统不可用时间仅需约2小时。

整体上线流程

为保证新功能的稳定性和旧表平稳下线，我们将整个上线过程分为三个阶段：

• 第一阶段（双写验证）：建立分表，迁移历史数据，线上对新旧表进行双写，所有查询走新分表。此阶段主要用于观察验证。
• 第二阶段（切读切写）：停止向旧表写入新数据，并推动其他直连数据库的业务方改为调用我们提供的对外接口。此阶段继续观察。
• 第三阶段（清理下线）：确认无误后，旧的大表正式下线。

项目总结与反思

回顾这次历时数月的分表项目，我们积累了以下经验教训：

• 中间件选型需更深入：由于分表依据（交易月份）的特殊性，Sharding-JDBC的许多自动化功能未能充分利用，其对简化查询逻辑的帮助低于预期。其独立数据源特性反而带来了多数据源事务问题，增加了开发成本。未来应进行更充分的调研。
• 线程池配置需谨慎：使用多线程查询时，必须仔细分析并设置线程池核心参数，避免多个线程池并存导致机器线程资源被耗尽。
• 改造场景务必全覆盖：对已有系统进行分表改造时，必须将影响到的所有业务场景罗列清楚，并细化到具体的类和方法，确保全覆盖，无遗漏。
• 数据迁移方案要周全：迁移历史数据时，必须制定详尽的方案，并准备好数据不一致的应急预案。需综合权衡时间成本、数据准确性、对线上功能的影响等多个维度。
• 完备的回滚与降级机制：上线复杂方案前，必须设计好清晰的回滚方案和降级措施，这是系统稳定性的重要保障。

题外话：技术之外的软实力

这次长达五个多月的项目，也让我对技术之外的能力有了更深体会。项目过程中，难免需要与其他系统团队协作，这对程序员的沟通能力提出了考验。

优秀的程序员能够通过有效沟通，将协作方转化为“盟友”，让对方意识到这项工作的共同价值，从而实现心甘情愿的配合与双赢。如果说程序设计和学习能力是程序员的硬实力，那么沟通协作、项目管理等软实力则决定了职业发展的上限。在现代软件开发中，单打独斗很难成事，这些软实力愈发重要。

此外，后端开发岗位往往对综合素质要求很高。后端工程师需要兼具业务理解与技术实现能力，在项目中常常担任主负责人的角色，需要协调前端、测试等多方节奏，把控风险。这对个人的锻炼和成长是非常全面的。

希望这次真实的 亿级大表拆分 实践经验，能为你带来一些启发。更多关于分布式系统、高并发架构的讨论，欢迎在云栈社区交流。