MySQL数据库的分库分表实战

理论概览和业界方案对比之后，这一篇我们来聚焦可复制的代码级实战，核心框架选用ShardingSphere-JDBC 5.x + Spring Boot，从依赖、配置、分片策略到数据迁移全流程覆盖。

分库分表的理论概述

为什么需要分库分表？

当单表数据量达到千万级，或者单库的并发连接数、磁盘IO达到上限时，会出现明显的性能下降。

• 分表：解决单表数据量大导致的查询慢问题。
• 分库：解决单个数据库实例的并发连接数、磁盘IO和CPU负载瓶颈。

需要注意的是，分库分表是最后的手段，务必先尝试索引优化、读写分离、缓存等手段。

二、核心拆分方式

主要分为垂直拆分和水平拆分两种。

1. 垂直拆分（按业务拆）

• 垂直分库：按业务模块将表拆分到不同数据库，比如把订单、商品、用户分别放在独立的库里。
  • 作用：微服务架构的基础，解决了单一数据库的连接和IO争用问题。
• 垂直分表：将一张宽表按字段拆成多张表，比如把访问频率低的字段（如商品详情）单独放在一张表。
  • 作用：减少单行数据大小，降低磁盘IO开销。

2. 水平拆分（按数据拆）—— 最核心

• 分表：将同一张表的数据，按某种规则（如哈希、范围）分散到多张同结构的表中。
• 分库：将数据分散到多个数据库实例中。
• 作用：解决单表数据量和单库写性能的瓶颈。

常用的分片算法

分库分表面临的三大核心难题

1. 分布式ID问题

分表后无法依赖数据库自增ID，需要全局唯一的ID。

• 解决方案：
  • 雪花算法(Snowflake)：最常用的方案，64位长整数，趋势递增，ID不重复。
  • Leaf（美团）：区段号段模式，需要搭建服务。
  • UUID：不推荐，占用空间大且无序导致索引频繁分裂。

2. 跨库关联查询（Join问题）

原来一条SQL能Join多张表，现在数据在不同数据库，无法直接Join。

• 解决方案：
  • 字段冗余：把常用字段（如用户名）直接存到订单表中，避免关联查询。
  • 全局表：对于一些字典表、配置表，在每一个数据库中都保存一份完整副本。
  • 应用层组装：先查A库的结果，再根据结果去B库查询，最后在代码中组装。
  • 数据异构：使用Elasticsearch等搜索引擎构建宽表索引。

3. 跨节点分页、排序、聚合问题

order by、limit、group by需要从多个节点拉取数据后在中间件中归并。

• 解决方案：
  • 尽量让查询都带上分片键。例如：查询用户订单时，带上user_id作为条件，这样可以直接定位到具体的库。
  • 对于无分片键的复杂排序，应用层或中间件需要做归并排序，这可能带来很大的性能消耗。

4. 分布式事务

操作跨多个数据库时，需要保证数据的一致性。

• 解决方案：
  • 最终一致性（强推荐）：使用可靠消息队列（如RocketMQ）实现最终一致性。
  • Seata：提供AT模式（自动补偿）和TCC模式（手动编码），根据业务对性能的要求选择。

实际执行路径

1. 评估与规划：预估未来3-5年的数据量和QPS，合理设置分片数。分片数建议设置为 2 的 N 次方，便于扩容。
2. 选型中间件：
   • ShardingSphere-JDBC：目前最主流，作为轻量级的Jar包嵌入应用，适合Java项目。
   • MyCat：独立的代理中间件，支持多语言，但多一层网络开销。
3. 制定分片键：
   • 这是最重要的一步。分片键需要能覆盖80%以上核心查询场景，比如订单表通常选user_id。
4. 确定扩容策略：
   • 双写迁移：不停机迁移。同时写旧库和新库，数据核对无误后，切读流量到新库，最后停写旧库。
   • 停机迁移：在业务低峰期公告停机，导出旧库数据，清洗后导入新分片。

分库分表是一个业务逻辑侵入性很强的架构调整。最重要的原则是：少用，慎用；如果要用，早期规划好分片键和分片数量。
一旦实施，很多SQL操作会受到限制，所以分库分表通常会配合能力下沉——也就是把复杂的查询、聚合交给Elasticsearch或大数据平台去做。
如果你正在规划方案，建议先画出业务核心流程图，确定最关键的分片键。需要我帮你分析一下具体的业务场景吗？

一个实践参数参考

• MySQL / InnoDB：
  • 单表数据量超过500万-1000万，或占用空间超过10GB，就可以考虑分表。
  • 单库实例的表总数据量超过200GB-300GB，或磁盘IO达到上限，可以考虑分库。
• TDSQL / OceanBase这类分布式数据库：虽然声称支持PB级数据，但达到TB级别时，也建议提前规划分区或拆分。
  总之，

一、依赖配置与核心版本

版本选择直接决定配置语法能否跑通。以下是经过多家公司生产验证的稳定组合：

<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>3.2.5</version></parent><properties><shardingsphere.version>5.5.2</shardingsphere.version><mybatis-plus.version>3.5.15</mybatis-plus.version></properties><dependencies><!-- ShardingSphere-JDBC Starter（核心依赖） --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId><version>${shardingsphere.version}</version></dependency><!-- 数据库驱动和连接池 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>com.zaxxer</groupId><artifactId>HikariCP</artifactId></dependency><!-- 与 MyBatis-Plus 的组合使用 --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-spring-boot3-starter</artifactId><version>${mybatis-plus.version}</version></dependency></dependencies>

版本避坑：Spring Boot 3.x 必须使用 ShardingSphere 5.3+ 版本，且需兼容 Jakarta EE（jakarta包名而非javax）。千万不要回退到 4.x 版本，否则配置语法完全不兼容，会走很多弯路。

二、库表准备：先建物理表

分库分表运行时，ShardingSphere 不负责自动建表，物理库表必须提前手动创建，且同一张逻辑表对应的所有物理表结构必须完全一致。

-- 1. 创建分库（假设分2个库）CREATEDATABASEIFNOTEXISTSorder_db_0;CREATEDATABASEIFNOTEXISTSorder_db_1;-- 2. 在每个库中创建物理表（每个库2张表，共4张表）USEorder_db_0;CREATETABLE`t_order_0`(`id`bigintNOTNULLCOMMENT'订单主键（分布式ID）',`order_no`varchar(64)NOTNULLCOMMENT'订单编号',`user_id`bigintNOTNULLCOMMENT'用户ID（分片键）',`order_amount`decimal(10,2)DEFAULTNULLCOMMENT'订单金额',`create_time`datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='订单表';CREATETABLE`t_order_1`LIKE`t_order_0`;-- 3. 同样在 order_db_1 中执行上面两条建表语句USEorder_db_1;CREATETABLE`t_order_0`LIKEorder_db_0.t_order_0;CREATETABLE`t_order_1`LIKEorder_db_0.t_order_0;

库表命名规范：物理表名须遵循统一模式，以便 ShardingSphere 通过表达式自动路由。如t_order_$->{0..1}表示t_order_0、t_order_1两张表。

另外，如果原表缺少关键索引，分表时务必补上。有团队在实际分表过程中发现原表更新时间字段没有索引，分表时增加了该索引，显著提升了查询效率。

三、核心配置实战（application.yml）

以下配置实现水平分库 + 水平分表的混合分片方案：user_id决定数据落在哪个库，order_id决定数据落在库内的哪张表。

spring:shardingsphere:datasource:names:ds0,ds1ds0:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://localhost:3306/order_db_0?useSSL=false&serverTimezone=UTCusername:rootpassword:123456ds1:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://localhost:3306/order_db_1?useSSL=false&serverTimezone=UTCusername:rootpassword:123456rules:sharding:tables:t_order:# 逻辑表名（代码中操作的表名）actual-data-nodes:ds$->{0..1}.t_order_$->{0..1}# 物理节点映射：2库 × 2表 = 4个节点# 分库策略：按 user_id 取模database-strategy:standard:sharding-column:user_idsharding-algorithm-name:db_inline# 分表策略：按 order_id 取模table-strategy:standard:sharding-column:order_idsharding-algorithm-name:table_inline# 分片算法定义sharding-algorithms:db_inline:type:INLINEprops:algorithm-expression:ds$->{user_id % 2}table_inline:type:INLINEprops:algorithm-expression:t_order_$->{order_id % 2}props:sql-show:true# 开发环境打印改写后的 SQL，生产环境建议关闭

配置的底层逻辑是：当你执行INSERT INTO t_order (user_id, order_id, ...) VALUES (...)时，ShardingSphere 会根据user_id的哈希值把数据分配到ds0或ds1库，再根据order_id的哈希值分配到该库下的t_order_0或t_order_1表中。

代码层完全无感知，依然操作逻辑表t_order，MyBatis-Plus 等 ORM 框架像平常一样使用即可。

四、分片键选择策略：决定 80% 的查询性能

分片键是分库分表最关键的决策，需要遵循以下核心原则：

实战中的经典方案：用户订单系统采用user_id分库 +order_id分表的混合策略

• 按user_id分库：保证同一用户的所有订单落在同一数据库，避免按用户查询时的跨库扫描。
• 按order_id分表：在库内均匀分布数据到多张物理表，解决单表数据量过大的问题。

采用user_id作为分库键后，按用户查询订单的 SQL（WHERE user_id = xxx）能够精准路由到单一库，性能大幅提升。

五、分布式 ID 生成：解决跨库主键唯一性

分库分表后，数据库自增 ID 在不同库中会重复，必须使用分布式 ID 生成方案。

ShardingSphere-JDBC 提供了多种内置策略，最推荐雪花算法（Snowflake）：

spring:shardingsphere:rules:sharding:tables:t_order:# 配置主键生成策略key-generate-strategy:column:id# 主键字段名key-generator-name:snowflake# 使用的生成器名称# 主键生成器定义key-generators:snowflake:type:SNOWFLAKEprops:worker-id:1# 工作节点 ID（分布式环境下各节点不同）max-vibration-offset:0# 关闭抖动，保证严格递增

其中，雪花算法生成的ID是一个64位的长整型（Long，对应Java中的long），在数据库中占用8字节。它的每一位都有明确的含义，整体分为符号位（1bit）+ 时间戳（41bit）+ 机器ID（10bit）+ 序列号（12bit）。

Snowflake 生成的 64 位 Long 型 ID 由时间戳、工作机器 ID、序列号三部分组成，全局唯一且趋势递增，既满足唯一性也对数据库 B+ 树索引友好。

高级技巧——基因分片：对于订单系统这类同时需要按user_id和order_id查询的场景，可将分库基因嵌入到 Snowflake ID 中。生成订单 ID 时，把user_id的哈希值编码到订单 ID 的特定 bit 位，后续通过订单 ID 也能直接计算出分片位置，无需二次查表定位。

六、跨节点分页查询：分页归并的深层原理

分页查询（ORDER BY ... LIMIT m, n）在分库分表场景下会演变成复杂的多结果集归并问题，理解其原理对性能优化至关重要。

核心原理图解：

原始 SQL: SELECT * FROM t_order ORDER BY create_time LIMIT 5, 10 ↓ SQL 解析与改写 ↓ ┌───────────┼───────────┐ ↓ ↓ ↓ ds0.t_order_0 ds0.t_order_1 ds1.t_order_0 ds1.t_order_1 ↓ ↓ ↓ ↓ 返回 15 条 返回 15 条 返回 15 条 返回 15 条 └───────────┴───────────┴───────────┘ ↓ 归并排序 ↓ 取合并后的第 5-15 条 → 最终返回 10 条

改写原理：当 SQL 包含LIMIT 5, 10时，ShardingSphere 会将5加上10，向每个分片下发LIMIT 0, 15的查询，从每个分片拉取 15 条数据。然后在应用层进行归并排序，从 4 个分片共 60 条数据中筛选出目标 10 条。随着分页深度增加（如LIMIT 10000, 10），ShardingSphere 会向每个分片下发LIMIT 0, 10010，性能急剧下降。

实战优化策略：

七、数据迁移不停机方案

从单库单表迁移到分库分表，关键挑战是在不影响业务的情况下完成过渡。业界最成熟的方案是双写 + 灰度切换。

实施步骤概览

准备阶段 → 双写阶段 → 历史数据迁移 → 数据校验 → 灰度切流 → 停用旧库

具体实施方案：

1. 准备阶段：设计好分库分表方案（分片键、分片规则），创建新库表结构，同时修改应用程序代码，写入数据时同时写旧库和新库，但读请求仍从旧库进行。

2. 历史数据迁移：使用数据同步工具（如 DataX、Canal、Percona XtraBackup），将存量数据按分片规则批量导入新库。如果新库规模较大，推荐使用支持断点续传和限流的分布式同步工具。

3. 数据校验与灰度切流：确认新旧库数据一致后，按比例将读流量从旧库切换到新库，建议灰度比例 1% → 10% → 50% → 100%。

4. 停用旧库：观察一段时间无异常后，终止双写逻辑，正式下线旧库。

避坑提醒：双写阶段可能引入分布式事务问题——两个数据库的一致性如何保证？生产实践中通常采用最终一致性方案：写旧库成功后，通过消息队列异步写入新库，配合定时任务扫描补偿缺失数据。务必在切换前做全量数据对账，这是上线前最后一道也是最重要的一道关卡。

八、分片数量设定

过度分片不仅没有实际收益，还会带来运维复杂度和性能损耗。以下是行业参考值：

九、生产环境踩坑清单

以下是在多个生产项目中踩过的坑，值得提前关注：

十、最佳实践建议

1. 尽早规划，但不必过早实施：当单表达到 2000 万行且仍有高速增长时，启动分库分表预案最为适宜。

2. 优先通过优化避免分片：不要为了分片而分片。在数据量可控的范围内，索引优化、读写分离、冷热数据分离往往是成本更低的解决方案。

3. 分片键就是兵家必争之地：选错分片键，分库分表就是白做。订单系统用user_id分库、order_id分表的混合策略是最经典的选择。

4. 从简单方案起步：初期能只分表不分库就不要一开始就分库分表，架构越复杂，维护成本越高。

5. 配置即文档：将分片规则（分片算法、actual-data-nodes映射表等）以配置文件形式版本化管理，形成团队知识沉淀。配合props.sql-show: true观察 SQL 路由情况，及时发现异常。

如果你正在规划某个具体业务模块的分库分表，可以把你的核心查询场景和数据增长预估发出来，我帮你评估分片方案是否合理。