news 2026/4/15 19:59:03

ShardingSphere分库分表实战：从设计到踩坑全记录

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
ShardingSphere分库分表实战：从设计到踩坑全记录

背景

去年负责的一个订单系统，单表数据量到了8000万，查询开始变慢，写入也受影响。

考虑过几个方案：

  • 归档历史数据：治标不治本，新数据还是会增长
  • 换TiDB：改动太大，风险高
  • 分库分表：业界成熟方案，可控

最后选了ShardingSphere做分库分表。这篇记录下完整的过程，包括踩的坑。

方案设计

分片策略

订单表按用户ID分片，原因：

  • 同一用户的订单在同一分片，关联查询不跨库
  • 用户ID分布均匀
  • 业务上大部分查询都带用户ID

分片规则：

  • 4个库：order_0, order_1, order_2, order_3
  • 每个库16张表：t_order_0 ~ t_order_15
  • 库分片：user_id % 4
  • 表分片：user_id % 64 / 4（保证同一用户在同一库的同一表）

数据量估算

  • 当前：8000万条
  • 预期3年后：5亿条
  • 单表上限：500万条
  • 需要分片数：5亿 / 500万 = 100个
  • 实际配置：4库 × 16表 = 64个分片
  • 后续可扩展到8库 × 16表

环境准备

数据库准备

创建4个数据库实例（或在同一实例创建4个库）：

-- 在每个实例上执行CREATEDATABASEorder_0DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATEDATABASEorder_1DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATEDATABASEorder_2DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATEDATABASEorder_3DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;

表结构

-- 在每个库执行，创建16张表CREATETABLEt_order_0(idBIGINTNOTNULL,order_noVARCHAR(32)NOTNULL,user_idBIGINTNOTNULL,statusTINYINTNOTNULLDEFAULT0,amountDECIMAL(10,2)NOTNULL,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,updated_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id),UNIQUEKEYuk_order_no(order_no),KEYidx_user_id(user_id),KEYidx_created_at(created_at))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- t_order_1 ~ t_order_15 同样

批量建表脚本：

#!/bin/bashfordbinorder_0 order_1 order_2 order_3;doforiin$(seq015);domysql -h$DB_HOST-u root -p$DB_PASS$db<<EOF CREATE TABLE t_order_$i( id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; EOFdonedone

ShardingSphere-JDBC配置

我们用的是ShardingSphere-JDBC方式，嵌入到应用中，无需额外代理层。

Maven依赖

<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core</artifactId><version>5.4.1</version></dependency>

配置文件

# application.ymlspring:datasource:driver-class-name:org.apache.shardingsphere.driver.ShardingSphereDriverurl:jdbc:shardingsphere:classpath:sharding-config.yaml# sharding-config.yamldataSources:ds_0:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-0.example.com:3306/order_0?useSSL=false&serverTimezone=Asia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5ds_1:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-1.example.com:3306/order_1?useSSL=false&serverTimezone=Asia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5ds_2:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-2.example.com:3306/order_2?useSSL=false&serverTimezone=Asia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5ds_3:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-3.example.com:3306/order_3?useSSL=false&serverTimezone=Asia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5rules:-!SHARDINGtables:t_order:actualDataNodes:ds_${0..3}.t_order_${0..15}databaseStrategy:standard:shardingColumn:user_idshardingAlgorithmName:database_modtableStrategy:standard:shardingColumn:user_idshardingAlgorithmName:table_modkeyGenerateStrategy:column:idkeyGeneratorName:snowflakeshardingAlgorithms:database_mod:type:MODprops:sharding-count:4table_mod:type:INLINEprops:algorithm-expression:t_order_${(user_id % 64).intdiv(4)}keyGenerators:snowflake:type:SNOWFLAKEprops:worker-id:1props:sql-show:truequery-with-cipher-column:false

分片算法说明

user_id = 12345 库分片：12345 % 4 = 1 → ds_1 表分片：(12345 % 64) / 4 = 57 / 4 = 14 → t_order_14 最终路由到：ds_1.t_order_14

这样设计的好处：同一用户的数据一定在同一张表里，避免跨表查询。

分布式主键

不能用数据库自增ID了，用雪花算法生成全局唯一ID。

自定义雪花算法

为了支持多数据中心，自定义了worker-id分配：

publicclassDataCenterAwareSnowflakeimplementsKeyGenerateAlgorithm{privatestaticfinallongEPOCH=1704067200000L;// 2024-01-01privatestaticfinallongWORKER_ID_BITS=5L;privatestaticfinallongDATACENTER_ID_BITS=5L;privatestaticfinallongSEQUENCE_BITS=12L;privatefinallongworkerId;privatefinallongdatacenterId;privatelongsequence=0L;privatelonglastTimestamp=-1L;publicDataCenterAwareSnowflake(longdatacenterId,longworkerId){this.datacenterId=datacenterId;this.workerId=workerId;}@OverridepublicsynchronizedComparable<?>generateKey(){longtimestamp=System.currentTimeMillis();if(timestamp<lastTimestamp){thrownewRuntimeException("Clock moved backwards");}if(timestamp==lastTimestamp){sequence=(sequence+1)&((1L<<SEQUENCE_BITS)-1);if(sequence==0){timestamp=waitNextMillis(lastTimestamp);}}else{sequence=0L;}lastTimestamp=timestamp;return((timestamp-EPOCH)<<(WORKER_ID_BITS+DATACENTER_ID_BITS+SEQUENCE_BITS))|(datacenterId<<(WORKER_ID_BITS+SEQUENCE_BITS))|(workerId<<SEQUENCE_BITS)|sequence;}privatelongwaitNextMillis(longlastTimestamp){longtimestamp=System.currentTimeMillis();while(timestamp<=lastTimestamp){timestamp=System.currentTimeMillis();}returntimestamp;}}

数据迁移

这是最关键的步骤，要保证业务不停服。

双写方案

阶段1：只写老库 阶段2：双写（老库 + 新分片库） 阶段3：切读到新库 阶段4：只写新库 阶段5：下线老库

历史数据迁移

用分批迁移脚本：

#!/usr/bin/env python3importpymysqlfromdatetimeimportdatetimeimporttime BATCH_SIZE=1000SLEEP_INTERVAL=0.1# 控制迁移速度defget_shard_info(user_id):db_index=user_id%4table_index=(user_id%64)//4returnf"order_{db_index}",f"t_order_{table_index}"defmigrate_batch(src_conn,dst_conns,last_id):cursor=src_conn.cursor(pymysql.cursors.DictCursor)cursor.execute(""" SELECT * FROM t_order WHERE id > %s ORDER BY id LIMIT %s """,(last_id,BATCH_SIZE))rows=cursor.fetchall()ifnotrows:returnNoneforrowinrows:db_name,table_name=get_shard_info(row['user_id'])db_index=int(db_name.split('_')[1])dst_cursor=dst_conns[db_index].cursor()dst_cursor.execute(f""" INSERT INTO{table_name}(id, order_no, user_id, status, amount, created_at, updated_at) VALUES (%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at) """,(row['id'],row['order_no'],row['user_id'],row['status'],row['amount'],row['created_at'],row['updated_at']))dst_conns[db_index].commit()returnrows[-1]['id']defmain():# 连接配置src_conn=pymysql.connect(host='old-db',user='root',password='xxx',db='order')dst_conns=[pymysql.connect(host=f'db-{i}',user='root',password='xxx',db=f'order_{i}')foriinrange(4)]last_id=0total=0whileTrue:last_id=migrate_batch(src_conn,dst_conns,last_id)iflast_idisNone:breaktotal+=BATCH_SIZEprint(f"已迁移{total}条，最后ID:{last_id}")time.sleep(SLEEP_INTERVAL)print(f"迁移完成，共{total}条")if__name__=='__main__':main()

数据校验

迁移完成后要做数据校验：

defverify_data():"""对比源库和目标库的数据"""src_cursor=src_conn.cursor()# 按用户ID抽样检查src_cursor.execute("SELECT DISTINCT user_id FROM t_order ORDER BY RAND() LIMIT 1000")user_ids=[row[0]forrowinsrc_cursor.fetchall()]errors=[]foruser_idinuser_ids:# 源库数据src_cursor.execute("SELECT COUNT(*), SUM(amount) FROM t_order WHERE user_id = %s",(user_id,))src_count,src_sum=src_cursor.fetchone()# 目标库数据db_name,table_name=get_shard_info(user_id)db_index=int(db_name.split('_')[1])dst_cursor=dst_conns[db_index].cursor()dst_cursor.execute(f"SELECT COUNT(*), SUM(amount) FROM{table_name}WHERE user_id = %s",(user_id,))dst_count,dst_sum=dst_cursor.fetchone()ifsrc_count!=dst_countorabs(src_sum-dst_sum)>0.01:errors.append({'user_id':user_id,'src':(src_count,src_sum),'dst':(dst_count,dst_sum)})returnerrors

常见问题处理

跨分片查询

不带user_id的查询会扫描所有分片：

-- 这个查询会路由到所有64个分片SELECT*FROMt_orderWHEREorder_no='ORD123456';

解决方案：

  1. 冗余索引表：维护order_no到user_id的映射
CREATETABLEt_order_index(order_noVARCHAR(32)PRIMARYKEY,user_idBIGINTNOTNULL,created_atDATETIME);
  1. 广播表：小表在所有分片复制一份
rules:-!SHARDINGbroadcastTables:-t_config-t_region

分页查询

跨分片分页是个大问题：

SELECT*FROMt_orderORDERBYcreated_atDESCLIMIT10000,20;

这个查询会从每个分片取10020条数据，然后在内存里排序取前20条。

解决方案：

  1. 禁止深度分页：产品上限制分页深度
  2. 基于游标分页：用上一页最后一条的ID作为游标
-- 改成SELECT*FROMt_orderWHEREcreated_at<'2024-12-24 10:00:00'ORDERBYcreated_atDESCLIMIT20;
  1. 异步导出：大量数据导出走离线任务

聚合查询

SELECTCOUNT(*),SUM(amount)FROMt_orderWHEREcreated_at>'2024-01-01';

这个会在每个分片执行，然后合并结果。性能还行，但要注意：

  • AVG需要先算SUM和COUNT再除
  • DISTINCT可能不准确

跨分片JOIN

ShardingSphere支持有限的跨分片JOIN，但性能很差。

解决方案：

  1. 宽表：把关联数据冗余到主表
  2. 应用层JOIN：分两次查询，应用层关联
  3. 数据同步到ES：复杂查询走ES

监控与运维

SQL审计

rules:-!SQL_AUDITauditStrategies:order_audit:auditorNames:-slow_sql_auditorallowHintDisable:trueauditors:slow_sql_auditor:type:SLOW_SQLprops:threshold:1000# ms

连接池监控

暴露HikariCP指标到Prometheus：

@BeanpublicMeterRegistryCustomizer<MeterRegistry>metricsCommonTags(){returnregistry->{HikariDataSourceds=(HikariDataSource)dataSource;ds.setMetricRegistry(registry);};}

关键指标：

  • hikaricp_connections_active
  • hikaricp_connections_pending
  • hikaricp_connections_timeout_total

慢查询告警

groups:-name:shardingrules:-alert:ShardingSlowQueryexpr:rate(shardingsphere_execute_latency_millis_sum[5m]) / rate(shardingsphere_execute_latency_millis_count[5m])>100for:5mlabels:severity:warningannotations:summary:"分片查询平均延迟超过100ms"

扩容方案

如果4个库不够了，需要扩容到8个库。

方案一：成倍扩容

从4库扩到8库，分片算法改成user_id % 8。

需要迁移一半的数据：

  • ds_0 的一半数据迁移到 ds_4
  • ds_1 的一半数据迁移到 ds_5
  • …

方案二：一致性Hash

用一致性Hash替代取模，扩容时只需要迁移部分数据。但ShardingSphere配置更复杂。

迁移时网络问题

如果新旧数据库分布在不同的网络环境，数据迁移可能受网络影响。之前遇到过跨机房迁移，延迟导致迁移速度很慢。后来用星空组网打通网络，迁移速度提升明显。

踩过的坑

1. 事务问题

分布式事务性能差，尽量避免跨分片事务。

// 这样写会跨分片@TransactionalpublicvoidcreateOrder(Orderorder,OrderItemitem){orderMapper.insert(order);orderItemMapper.insert(item);// 如果item和order不在同一分片}

解决：确保关联数据使用相同的分片键。

2. 批量插入性能

// 这样效率低for(Orderorder:orders){orderMapper.insert(order);// 每条都单独路由}

优化：按分片键分组，同一分片的数据批量插入。

3. 唯一索引冲突

order_no需要全局唯一，但分布在不同分片：

-- 这个唯一索引只在单表内生效UNIQUEKEYuk_order_no(order_no)

解决：order_no用分布式ID生成，或者在应用层去重。

4. 时钟回拨

雪花算法依赖时钟，时钟回拨会导致ID重复。

解决：

  • NTP配置要稳定
  • 代码里检测时钟回拨，等待或报错

总结

分库分表的核心要点：

  1. 分片键选择最重要：选错了后面都是坑
  2. 避免跨分片操作：JOIN、事务、聚合都要小心
  3. 分布式ID要可靠：雪花算法配置好worker-id
  4. 迁移方案要周全：双写、校验、回滚方案都要有
  5. 监控要到位：慢查询、连接池、分片分布

分库分表不是银弹，引入了不少复杂度。如果数据量不是特别大（单表千万级以内），先优化索引和SQL，实在不行再考虑分库分表。

如果要做，ShardingSphere是比较成熟的方案，社区活跃，文档也完善。

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/4/16 12:17:32

【2025最新】基于SpringBoot+Vue的协同过滤算法东北特产销售系统管理系统源码+MyBatis+MySQL

摘要 随着电子商务的快速发展&#xff0c;个性化推荐系统在提升用户购物体验和商家销售效率方面发挥了重要作用。东北特产作为具有地域特色的商品&#xff0c;其销售模式逐渐从传统线下转向线上&#xff0c;但现有电商平台在个性化推荐和用户需求匹配方面仍存在不足。协同过滤算…

作者头像 李华
网站建设 2026/4/16 13:59:18

陶瓷行业大会资讯：掌握行业动态，洞察未来趋势

陶瓷行业大会资讯&#xff1a;掌握行业动态&#xff0c;洞察未来趋势引言陶瓷行业作为传统与现代工艺相结合的重要领域&#xff0c;其发展动态一直备受关注。陶瓷行业大会作为行业内的重要交流平台&#xff0c;为我们提供了掌握行业最新动态、洞察未来发展趋势的绝佳机会。一、…

作者头像 李华
网站建设 2026/4/16 14:05:55

GPT-SoVITS与商业语音引擎费用对比分析

GPT-SoVITS与商业语音引擎费用对比分析 在内容创作、虚拟交互和个性化服务日益普及的今天&#xff0c;语音合成技术正从“能说话”迈向“像你说话”。无论是打造专属的有声书主播、构建拟人化的AI客服&#xff0c;还是为视障用户定制朗读音色&#xff0c;人们对“声音个性化”的…

作者头像 李华
网站建设 2026/4/16 14:33:36

STM32 CANFD数据段速率设置技巧：图解说明BRS机制

STM32 CANFD数据段速率设置技巧&#xff1a;图解BRS机制与实战配置在现代嵌入式通信系统中&#xff0c;CAN总线早已不是“够用”那么简单。随着新能源汽车的三电控制、工业伺服系统的多轴同步、车载传感器融合等应用对实时性和带宽提出更高要求&#xff0c;传统CAN 1 Mbps的天花…

作者头像 李华
网站建设 2026/4/7 21:31:25

2026年中科院分区表发布时间确定了？

2025年3月20日&#xff0c;2025年中科院国际期刊预警名单和期刊分区表姗姗来迟&#xff0c;终于正式发布。根据往年发布规律&#xff0c;2026年预警期刊名单最快将于本月底发布&#xff0c;但因2026年2月17日是春节&#xff0c;根据近年经验&#xff0c;2026年版中科院分区表和…

作者头像 李华
网站建设 2026/4/16 11:08:49

GPT-SoVITS语音训练避坑指南：新手常见错误汇总

GPT-SoVITS语音训练避坑指南&#xff1a;新手常见错误深度解析 在AI生成声音越来越“像人”的今天&#xff0c;个性化语音合成已不再是科研实验室的专属玩具。从虚拟主播到有声书自动播讲&#xff0c;越来越多创作者开始尝试用少量录音克隆自己的声音——而 GPT-SoVITS 正是当前…

作者头像 李华