分库分表实现方案总结

type

status

date

slug

summary

1、分库分表概述

1.1 分库分表是什么

分库分表是一种数据库性能优化的方法，它将原本存储在单个数据库中的数据分散到多个数据库中，将原本单个表中的数据分散到多个数据表中。通过这种方式提高数据库的查询性能和扩展性，同时降低数据库的负载。

为什么要做分库分表？

分库分表是数据库架构设计中常用的优化手段，主要为了解决以下几个核心问题：

查询性能问题：当单表数据达到千万级甚至亿级时，表索引层级变深，查询性能会显著下降

数据库负载压力大：单一数据库实例的 CPU、内存、IO 等资源有限，难以支撑高并发访问

存储容量限制：单个数据库服务器的存储空间有限，超大规模数据无法存放在单一物理节点上

业务扩展需求：不同业务模块可以独立扩展（如用户库、订单库分离），便于实现微服务架构下的数据隔离

分库分表带来的好处：

提升查询性能：数据分散后单表数据量减少，查询更快

提高并发处理能力：负载分散到多个数据库实例

突破单机存储限制：理论上可以无限水平扩展

增强系统可用性：单个节点故障不影响整体服务

分库分表的实现：

分库：将一个数据库中的数据拆分到多个数据库中。

分表：将一张表中的数据拆分到多张表中。

通常两者是结合使用的，即先分库，在库内再分表。

分库分表有水平拆分和垂直拆分两种方案，这两种方式同时适用于分库和分表。

1.2 垂直拆分策略

垂直拆分是指根据业务模块或表的列进行拆分。垂直拆分一般是数据库表设计之初就执行的步骤。

垂直分库：按业务模块拆分

做法：根据业务耦合性，将关联度低的不同表分布到不同的数据库上。例如，将用户相关的表放在用户库，订单相关的表放在订单库，商品相关的表放在商品库。
优点：降低单库容量和访问压力；方便对不同业务进行针对性优化和扩展；故障影响范围缩小。
挑战：跨库无法直接进行JOIN操作，需要在业务代码中处理；分布式事务问题。

垂直分表：按字段拆分

做法：将一个宽表（有很多列的表）按访问频率或是否属于“大字段”拆分成多个小表，主表只保留核心、高频访问的字段。例如：

把不常用的字段单独放在扩展表。
把大字段（如TEXT, BLOB）拆分出来放在扩展表中。
经常组合查询的字段放在一张表中。

优点：避免IO操作时加载无用的大字段，提升核心业务的查询性能；数据库页能缓存更多行数据。
挑战：需要管理多张表；查询时需要关联或多次查询。

1.3 水平切分策略

水平拆分是指将同一个表中的数据按照某种规则分散到多个结构相同的表或库中。例如 Mysql 表的行数超过 2000 万行时就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平拆分策略在后期数据量起来之后比较常用，这是分库分表最常讨论的形式。

水平分库

做法：将一张表的数据按规则切分到不同的数据库中，每个库都有相同的表结构。
例如：order_db_1, order_db_2，每个库中都有orders表。

水平分表

做法：将一张表的数据按规则切分到同一个数据库的多个表中。
例如：orders_1, orders_2, orders_3 ... 都位于同一个数据库中。

水平切分的核心在于“分片策略”（Sharding Strategy），即数据按照什么规则进行分散。我们一般通过路由字段（shardingkey）把数据分散到具体的库/表。

路由字段使用技巧：比如订单表按照用户 ID 作为 shardingkey ，这样就可以根据用户 ID 进行查询。还可以在订单号的生成规则带上 10 位用户 ID 作为 shardingkey，落具体表的时候根据订单号中的用户 ID hash 取模，这样无论根据订单号还是用户 ID 查询效果都是一样的。

水平拆分常用分片策略：

哈希分片：对 shardingkey 取模

范围分片：按ID范围、时间范围

查表法：维护分片规则表

1.3.1 哈希分片

选取一个或多个字段（如user_id）作为分片键（shardingkey），通过一个哈希函数（如CRC32, MD5）计算其哈希值（可选，例如分片键本身就是一个整数，可以不计算哈希值），然后对分片总数取模，得到目标分片。

一般表数为 2^n，hash 算法为 mod2^n，便于后期扩展。

例如 user_id%2=0 即路由到 0 库， user_id%2=1 即路由到 1 库。

优点：实现简单，数据分布均匀，不容易产生热点，负载相对均衡。

缺点：扩容困难，需要迁移历史数据。一旦增加分片数量（N变化），几乎所有数据都需要重新计算和迁移。范围查询效率很低，需要查询所有分片然后聚合。

1.3.2 范围分片

按照某个字段的范围进行划分，例如把 id、user_id、createtime 等具备范围属性的字段作为 shardingkey，按 shardingkey 进行范围分段切割，不同段数据放到不同的库。例如 user_id 的值属于 [0, 1亿] 范围分配到 0 库，属于 [1亿, 2亿] 范围分配到 1 库。

优点：单表大小可控，天然水平扩展，方便进行范围查询。

缺点：容易产生数据热点和负载不均。最新的数据访问最频繁，但都集中在最后一个分片。

1.3.3 查表法

单独用一张表或一个配置中心来记录分片键（shardingkey）与分片的映射关系（如user_id -> db_01）。

优点：非常灵活，ID 和库的 Mapping 算法可以随意更改

缺点：引入一次额外的查询，性能和可用性依赖于这个映射表。

2、分库分表实现方案

2.1 客户端分片

实现方式：在应用层代码中实现分片逻辑

优点：简单直接，无额外依赖

缺点：侵入性强，维护成本高

代表：

自定义 Mybatis 插件：实现 Interceptor 接口，拦截 SQL 后自定义分库分表逻辑。
ShardingSphere-JDBC：ShardingSphere 的组件之一，一个轻量级的 Java 框架，以 Jar 包形式提供，直接集成在应用中，在 JDBC 层提供数据分片、读写分离、分布式事务等功能。

2.2 数据库中间件

实现方式：使用专门的分库分表中间件，处理分片逻辑

优点：功能完善，支持多种分片策略

缺点：学习成本，运维复杂度

代表：

ShardingSphere-Proxy：ShardingSphere 的组件之一，透明化的数据库代理端，是一个独立运行的进程，从客户端程序连接来说，它和 MySQL 数据库没有什么区别。
MyCat：基于阿里 Cobar 开发
MySQL Router：官方出品，轻量级，与 MySQL 生态集成好，配置简单。是 MySQL InnoDB Cluster 的一部分。
ProxySQL：高性能、高可用，功能极其丰富（查询缓存、重写、故障转移等），是社区最主流的选择。
Atlas：360 出品，基于 MySQL Proxy
TDDL：淘宝分布式数据层

2.3 分布式数据库

实现方式：使用原生支持分片的分布式数据库

优点：天然支持，无需额外开发

缺点：技术锁定，迁移成本

代表：

TiDB
CockroachDB
OceanBase

2.4 技术选型

中小规模：Sharding-JDBC（应用层集成）

大规模复杂场景：Sharding-Proxy 或 MyCat

全新项目：考虑 TiDB 等分布式数据库

云环境：优先考虑云厂商提供的分布式数据库服务

3、分库分表的实践过程

3.1 前期评估阶段

业务需求分析

确定分库分表的必要性：

能不分则不分，分库分表会带来巨大的复杂性和维护成本，优先做单机优化，考虑 SQL 优化、索引优化、读写分离、缓存（Redis）、归档历史数据等手段。
当单表数据量预计即将达到千万行级别，或磁盘、CPU、连接数等指标即将达到瓶颈时，再开始规划和实施。

分析业务特点：读写比例、查询模式、事务要求等

容量评估

评估当前数据量及增长速度

计算未来3-5年的数据量预期

评估存储资源需求

3.2 设计阶段

分库分表策略设计

先确定分库策略：先垂直分库，按业务模块拆分，这是第一步。

再确定分表策略：如果某个业务模块的单表数据量或访问量依然巨大，再针对该模块的表进行分表。

如果某个业务宽表字段较多，可以考虑进行垂直分表，把非核心字段拆出来放在扩展表。
如果业务表字段不多，但是数据量较大，考虑进行水平分表，把数据分散到几个相同数据结构的表。

新表结构设计

合并冗余表，剔除无用字段

重新设计索引，避免照搬原表索引

尽量保持原字段名称，减少业务改造

技术选型

确定分库分表中间件：优先选择技术成熟的分库分表中间件，避免自己造轮子，例如 ShardingSphere、MyCat 等

确定数据同步工具：在新旧数据库的数据迁移阶段需要用到数据同步工具，，例如 Canal、DataX 等

架构设计

设计数据同步关系：全量同步 + 增量同步

3.3 改造阶段

业务应用改造：

提供新库的数据访问层：通过 SDK 形式提供新库的数据访问层，基于 ShardingSphere-JDBC，支持分片路由。

数据库读写改造：在服务层对数据库进行增删改的地方，改造成基于新的数据库访问层进行读写。例如写操作内部需要同时写新库和老库，代码需要提供灰度功能以及热开关切换功能。

分布式 ID 问题：引入雪花算法解决分布式 ID 生成问题。

分布式事务问题：改造事务处理，引入分布式事务框架（Seata）或最终一致性方案。

制定数据迁移方案

全量迁移：使用 DataX 完成旧数据全量迁移到新的数据库。

增量同步：使用 Canal 监听增量变更的 Binlog 数据。

数据校验程序：用于校验新库和老库的数据是否一致，对比 MD5 校验。

3.4 上线阶段

我们采用常用的应用层双写方案，实现数据库不停机数据迁移：

开启双写。任何对数据库的更改操作（增/删/改）都要同时写入老库和新库。如果新库没有相关记录，需要先从老库查出记录，将更新后的记录写入新库。为了保证写入性能，老库写完后，可以采用消息队列异步写入新库。

开启旧数据全量迁移：将某一时间戳之前的老数据迁移到新库。

时间戳一定要选择第 2 步开启双写后的时间点，比如开启双写后 10 分钟的时间点，避免部分老数据被漏掉。
迁移过程遇到主键冲突直接忽略，因为第 1 步的更新操作，已经把记录拉到了新库。
迁移过程一定要记录日志，尤其是错误日志，如果有双写失败的情况，我们可以通过日志恢复数据，以此来保证新老库的数据一致。

开启校验数据脚本：等待第 3 步完成后，开启校验程序脚本，校验新库数据的完整性和准确性。

流量切换，开启双读：数据校验没问题后，开启双读，起初使用灰度功能给新库放少部分流量，新库和老库同时读取。由于延时问题，新库和老库可能会有少量数据记录不一致的情况，所以新库读不到时需要再读一遍老库，期间遇到问题可以及时把读流量切回老库。

停止老库读流量：逐渐提高新库的读流量比例，逐渐将全部读流量切到新库。

停止老库写流量：读流量全部切到新库后，写流量还是双写新旧数据库，这时候需要关闭老库写入，只写新库。

迁移完成：此时老库已经没有读写流量，进行持续的监控观察，后续可以考虑回收老库资源，并去掉双写双读相关无用代码。

3.5 后期优化与运维

监控体系建设

监控分片存储空间使用率、跨分片查询比例等指标

设置报警阈值，如存储 >80%、跨分片查询 >5% 等

性能优化

处理热点分片：动态调整路由策略

查询优化：强制指定分片键

引入缓存减轻数据库压力

扩容准备

设计时考虑未来扩容，如使用 2^n 分片数

准备数据迁移和路由调整方案

4、分库分表带来的挑战

分库分表的技术难点：

分布式唯一主键

跨库事务

跨库JOIN问题

跨库分页排序问题

非shardingkey的查询问题

数据迁移

4.1 分布式唯一主键

详见分布式ID：实现汇总

4.2 跨库事务

常规的数据库事务都是在单个连接（Session）里面实现，如果要跨库，就要涉及多个数据库连接，常规的数据库事务不支持这种场景，这就是跨库事务问题。

💡

尽量避免跨库事务

数据设计时尽量让需要事务的操作在同一个分片，避免在一个事务中修改 db0 中的表的时候同时修改 db1 中的表。

如果一定要实现跨库事务，就需要使用分布式事务框架，参考这一篇文章 https://www.mcbilla.com/article/86e9cb68-7041-4732-a44c-3385aec608cd

4.3 跨库JOIN问题

在分库分表架构中，因为数据被分散在不同的数据库或表中，传统的 SQL Join 操作无法直接使用，这就是跨库 JOIN 问题。

💡

尽量避免跨库 Join

在设计时优先考虑单库操作，让需要 Join 的数据尽量在同一个分片。如果一定要实现跨库 JOIN，目前常见的技术方案有：

技术方案	实现	优点	缺点
应用层Join	先查询一个表的数据，根据结果再去查询另一个表，在应用内存中完成数据关联	• 实现简单直接 • 不需要中间件支持	• 需要多次查询，性能较差 • 网络开销大 • 应用代码复杂度高
全局表/广播表	将需要频繁Join的小表在所有分库中都保存一份，这些表数据通常很少变更	• Join操作可以在单个库内完成 • 性能较好	• 只适用于小表 • 数据同步有延迟问题
字段冗余	在表中冗余存储需要Join的字段，避免Join操作	• 查询性能好 • 实现简单	• 数据一致性维护困难 • 存储空间增加
使用中间件	使用ShardingSphere、MyCat等中间件，解析SQL并重写查询	• 对应用透明 • 可以支持部分跨库Join	• 性能开销 • 复杂Join可能不支持
分布式查询引擎	使用Presto、Doris等分布式查询引擎从各分片获取数据并合并	• 支持复杂查询 • 对应用透明	• 系统复杂度高 • 资源消耗大

4.4 跨库分页排序问题

分页排序要解决的问题是：跨越多个水平切分数据库，且分库依据与排序依据为不同属性，并需要进行分页，实现 select * from T order by time offset X limit Y 的跨库分页 SQL。

这个问题目前仍是业界难题，常用有以下解决方案：

4.4.1 全局视野法

实现方案：

将 order by time offset X limit Y，改写成 order by time offset 0 limit X+Y

服务层将改写后的 SQL 语句发往各个分库

假设共分为 N 个库，服务层将得到 N*(X+Y) 条数据

服务层对得到的 N*(X+Y) 条数据进行内存排序，内存排序后再取偏移量 X 后的 Y 条记录，就是全局视野所需的一页数据

优点：

通过服务层修改SQL语句，扩大数据召回量，能够得到全局视野，业务无损，精准返回所需数据。

缺点：

网络消耗增加：每个分库需要返回更多的数据，增大了网络传输量（耗网络）；

CPU 消耗增加：除了数据库按照 time 进行排序，服务层还需要进行二次排序，增大了服务层的计算量（耗CPU）；

性能下降明显：这个算法随着页码的增大，性能会急剧下降，这是因为 SQL 改写后每个分库要返回 X+Y 行数据。假如要返回第 100页，offset 中的 X=9900，即每个分库要返回 100 页数据，数据量和排序量都将大增，性能平方级下降。

4.4.2 业务折衷法-禁止跳页查询

为了解决全局视野法的性能问题，很多产品并不提供“直接跳到指定页面”的功能，而只提供“下一页”的功能。这种设计可以大大降低难度：

用全局视野法取得第一页数据，并得到第一页记录的 time_max。

每次翻页，将 order by time offset X limit Y，改写成 order by time where time > $time_max limit Y，以保证每次每个库只返回一页数据，性能为常量。

优点：

数据的传输量和排序的数据量不会随着不断翻页而导致性能下降。

缺点：

需要业务配合

4.4.3 业务折衷法-允许模糊数据

原理是：使用 shardingkey 进行分库，在数据量较大，数据分布足够随机的情况下，各分库所有非 patition key 属性，在各个分库上的数据分布，统计概率情况是一致的。

在允许数据精度损失的前提下。利用这一原理：

将 order by time offset X limit Y，改写成 order by time offset X/N limit Y/N

每个库返回的都是精度不够准确的数据。

优点：

大大降低了技术方案的复杂度，既不需要返回更多的数据，也不需要进行服务内存排序了。

缺点：

返回的数据不够准确

4.4.4 二次查询法

这个方法要求数据均摊到各分表，就是说不会有一个分表的数据特别多或特别少。这个方案不适用数据分段切分的表。实现思路：

将 order by time offset X limit Y，改写成 order by time offset X/N limit Y

找到最小值 time_min

between 二次查询， order by time between $time_min and $time_i_max，第二次查询条件放宽了，故第二次查询会返回比第一次查询结果集更多的数据

找到 time_min 在各个分库的 offset，从而得到 time_min 在全局的 offset

得到了 time_min 在全局的 offset，自然得到了全局的 offset X limit Y

优点：

即能够满足业务的精确需要，无需业务折衷，又高性能

缺点：

需要二次查找数据库

4.5 非shardingkey的查询问题

一般情况下我们使用的 sharding key 都是针对 C 端的用户，这是数据库的主要流量。而非 shardingkey 一般都是针对B端用户的，例如来自商户端或者后台的查询，这种查询流量不大而且可以接受少量延迟，一般解决方案有：

1、双写。双写就是下单的数据落两份，C 端和 B 端的各自保存一份，C 端用你可以用单号、用户 ID 做 shardingkey 都行，B端就用商家卖家的 ID 作为 shardingkey 就好了。对于 B 端来说轻微的延迟是可以接受的，所以可以采取异步的方式去落 B 端订单。

2、走离线数仓或者ES查询。订单数据落库之后，不管你通过 Binlog 还是 MQ 消息的都形式，把数据同步到数仓或者 ES，他们支持的数量级对于这种查询条件来说就很简单了。同样这种方式肯定是稍微有延迟的，但是这种可控范围的延迟是可以接受的。这种解决方案还可以解决比如运营、业务、产品等复杂的查询条件。

4.6 数据迁移

详见 3、分库分表的实践过程