- 当前位置：首页 >休闲 >浅谈订单重构之 MySQL 分库分表实战篇
浅谈订单重构之 MySQL 分库分表实战篇
发布时间：2025-11-04 23:34:45 来源：创站工坊 作者：应用开发
一、浅谈背景
发布上篇文章浅谈订单重构之路之后，订单有很多小伙伴想知道，重构之分库分表具体是分库分表如何实现的。那么这篇文章具体介绍下，实战分库分表实战。浅谈
二、订单目标
本文将完成如下目标:
* 分表数量: 256 分库数量: 4
* 以用户ID(user_id) 为数据库分片Key
* 最后测试订单创建，重构之更新，分库分表删除,实战 单订单号查询，根据user_id查询列表操作。浅谈
架构图:
表结构如下:
CREATE TABLE `order_XXX` ( `order_id` bigint(20) unsigned NOT NULL,订单 `user_id` int(11) DEFAULT 0 COMMENT 订单id, `status` int(11) DEFAULT 0 COMMENT 订单状态, `booking_date` datetime DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`order_id`), KEY `idx_user_id` (`user_id`), KEY `idx_bdate` (`booking_date`), KEY `idx_ctime` (`create_time`), KEY `idx_utime` (`update_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;注: 000<= XXX <= 255, 本文重点在于分库分表实践, 只保留具有代表性字段，其它场景可以在此基础上做改进。IT技术网重构之
全局唯一ID设计
要求:1.全局唯一 2:粗略有序 3:可反解出库编号
1bit + 39bit时间差 + 8bit机器号 + 8bit用户编号(库号) + 8bit自增序列
订单号组成项 保留字段 毫秒级时间差 机器数 用户编号(表编号) 自增序列 所占字节(单位bit) 1 39 8 8 8单机最大QPS: 256000 使用寿命: 17年
订单号生成规则说明详见 浅谈分布式唯一Id生成器之最佳实践
三、分库分表环境准备
1. 基本信息
2. 数据库环境准备
温馨提示：使用docker-compose快速搭建了4主4从数据库集群，实战实现本地快速一键部署，生产环境一般由DBA同学搭建。
具体实现请移步查看: https://gitee.com/bytearch_admin/docker-app/tree/main/mysql-cluster
3. 建库 & 导入分表
* 在mysql master实例分别建库
172.30.1.21( o rder_db_ 1) , 172.30.1.22( order_db_2) ,
172.30.1.23( ord er_db_3) , 172.30.1.24( order_db_4 )
* 依次导入建表SQL 命令为
mysql -uroot -pbytearch -h172.30.1.21 order_db_1<fast-cloud-mysql-sharding/doc/sql/order_db_1.sql; mysql -uroot -pbytearch -h172.30.1.22 order_db_2<fast-cloud-mysql-sharding/doc/sql/order_db_2.sql; mysql -uroot -pbytearch -h172.30.1.23 order_db_3<fast-cloud-mysql-sharding/doc/sql/order_db_3.sql; mysql -uroot -pbytearch -h172.30.1.24 order_db_4<fast-cloud-mysql-sharding/doc/sql/order_db_4.sql;四、配置&实践
1. pom文件
<!-- mango 分库分表中间件 --> <dependency> <groupId>org.jfaster</groupId> <artifactId>mango-spring-boot-starter</artifactId> <version>2.0.1</version> </dependency> <!-- 分布式ID生成器 --> <dependency> <groupId>com.bytearch</groupId> <artifactId>fast-cloud-id-generator</artifactId> <version>${version}</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency>2. 常量配置
package com.bytearch.fast.cloud.mysql.sharding.common; /** * 分库分表策略常用常量 */ public class ShardingStrategyConstant { /** * database 逻辑名称 ，真实库名为 order_db_XXX */ public static final String LOGIC_ORDER_DATABASE_NAME = "order_db"; /** * 分表数 256，一旦确定不可更改 */ public static final int SHARDING_TABLE_NUM = 256; /** * 分库数, 不建议更改， 可以更改，但是需要DBA迁移数据 */ public static final int SHARDING_DATABASE_NODE_NUM = 4; }3. yml 配置
4主4从数据库配置， 这里仅测试默认使用root用户密码，生产环境不建议使用root用户。
mango: scan-package: com.bytearch.fast.cloud.mysql.sharding.dao datasources: - name: order_db_1 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.21:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.31:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 - name: order_db_2 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.22:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.32:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 - name: order_db_3 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.23:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.33:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 - name: order_db_4 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.24:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.34:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3004. 分库分表策略
1). 根据order_id为shardKey分库分表策略
package com.bytearch.fast.cloud.mysql.sharding.strategy; import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; import com.bytearch.id.generator.IdEntity; import com.bytearch.id.generator.SeqIdUtil; import org.jfaster.mango.sharding.ShardingStrategy; /** * 订单号分库分表策略 */ public class OrderIdShardingStrategy implements ShardingStrategy<Long, Long> { @Override public String getDataSourceFactoryName(Long orderId) { if (orderId == null || orderId < 0L) { throw new IllegalArgumentException("order_id is invalid!"); } IdEntity idEntity = SeqIdUtil.decodeId(orderId); if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) { throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId()); } //1. 计算步长 int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM; //2. 计算出库编号 long dbNo = Math.floorDiv(idEntity.getExtraId(), step) + 1; //3. 返回数据源名 return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo); } @Override public String getTargetTable(String logicTableName, Long orderId) { if (orderId == null || orderId < 0L) { throw new IllegalArgumentException("order_id is invalid!"); } IdEntity idEntity = SeqIdUtil.decodeId(orderId); if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) { throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId()); } // 基于约定，站群服务器真实表名为 logicTableName_XXX， XXX不足三位补0 return String.format("%s_%03d", logicTableName, idEntity.getExtraId()); } }2). 根据user_id 为shardKey分库分表策略
package com.bytearch.fast.cloud.mysql.sharding.strategy; import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; import org.jfaster.mango.sharding.ShardingStrategy; /** * 指定分片KEY 分库分表策略 */ public class UserIdShardingStrategy implements ShardingStrategy<Integer, Integer> { @Override public String getDataSourceFactoryName(Integer userId) { //1. 计算步长 即单库放得表数量 int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM; //2. 计算出库编号 long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1; //3. 返回数据源名 return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo); } @Override public String getTargetTable(String logicTableName, Integer userId) { // 基于约定，真实表名为 logicTableName_XXX， XXX不足三位补0 return String.format("%s_%03d", logicTableName, userId % ShardingStrategyConstant.SHARDING_TABLE_NUM); } }5. dao层编写
1). OrderPartitionByIdDao
package com.bytearch.fast.cloud.mysql.sharding.dao; import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; import com.bytearch.fast.cloud.mysql.sharding.pojo.entity.OrderEntity; import com.bytearch.fast.cloud.mysql.sharding.strategy.OrderIdShardingStrategy; import org.jfaster.mango.annotation.*; @DB(name = ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, table = "order") @Sharding(shardingStrategy = OrderIdShardingStrategy.class) public interface OrderPartitionByIdDao { @SQL("INSERT INTO #table (order_id, user_id, status, booking_date, create_time, update_time) VALUES" + "(:orderId,:userId,:status,:bookingDate,:createTime,:updateTime)" ) int insertOrder(@TableShardingBy("orderId") @DatabaseShardingBy("orderId") OrderEntity orderEntity); @SQL("UPDATE #table set update_time = now()" + "#if(:bookingDate != null),booking_date = :bookingDate #end " + "#if (:status != null), status = :status #end" + "WHERE order_id = :orderId" ) int updateOrderByOrderId(@TableShardingBy("orderId") @DatabaseShardingBy("orderId") OrderEntity orderEntity); @SQL("SELECT * FROM #table WHERE order_id = :1") OrderEntity getOrderById(@TableShardingBy @DatabaseShardingBy Long orderId); @SQL("SELECT * FROM #table WHERE order_id = :1") @UseMaster OrderEntity getOrderByIdFromMaster(@TableShardingBy @DatabaseShardingBy Long orderId);6. 单元测试
@SpringBootTest(classes = {Application.class}) @RunWith(SpringJUnit4ClassRunner.class) public class ShardingTest { @Autowired OrderPartitionByIdDao orderPartitionByIdDao; @Autowired OrderPartitionByUserIdDao orderPartitionByUserIdDao; @Test public void testCreateOrderRandom() { for (int i = 0; i < 20; i++) { int userId = ThreadLocalRandom.current().nextInt(1000,1000000); OrderEntity orderEntity = new OrderEntity(); orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); orderEntity.setStatus(1); orderEntity.setUserId(userId); orderEntity.setCreateTime(new Date()); orderEntity.setUpdateTime(new Date()); orderEntity.setBookingDate(new Date()); int ret = orderPartitionByIdDao.insertOrder(orderEntity); Assert.assertEquals(1, ret); } } @Test public void testOrderAll() { //insert int userId = ThreadLocalRandom.current().nextInt(1000,1000000); OrderEntity orderEntity = new OrderEntity(); orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); orderEntity.setStatus(1); orderEntity.setUserId(userId); orderEntity.setCreateTime(new Date()); orderEntity.setUpdateTime(new Date()); orderEntity.setBookingDate(new Date()); int i = orderPartitionByIdDao.insertOrder(orderEntity); Assert.assertEquals(1, i); //get from master OrderEntity orderInfo = orderPartitionByIdDao.getOrderByIdFromMaster(orderEntity.getOrderId()); Assert.assertNotNull(orderInfo); Assert.assertEquals(orderInfo.getOrderId(), orderEntity.getOrderId()); //get from slave OrderEntity slaveOrderInfo = orderPartitionByIdDao.getOrderById(orderEntity.getOrderId()); Assert.assertNotNull(slaveOrderInfo); //update OrderEntity updateEntity = new OrderEntity(); updateEntity.setOrderId(orderInfo.getOrderId()); updateEntity.setStatus(2); updateEntity.setUpdateTime(new Date()); int affectRows = orderPartitionByIdDao.updateOrderByOrderId(updateEntity); Assert.assertTrue( affectRows > 0); } @Test public void testGetListByUserId() { int userId = ThreadLocalRandom.current().nextInt(1000,1000000); for (int i = 0; i < 5; i++) { OrderEntity orderEntity = new OrderEntity(); orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); orderEntity.setStatus(1); orderEntity.setUserId(userId); orderEntity.setCreateTime(new Date()); orderEntity.setUpdateTime(new Date()); orderEntity.setBookingDate(new Date()); orderPartitionByIdDao.insertOrder(orderEntity); } try { //防止主从延迟引起的校验错误 Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } List<OrderEntity> orderListByUserId = orderPartitionByUserIdDao.getOrderListByUserId(userId); Assert.assertNotNull(orderListByUserId); Assert.assertTrue(orderListByUserId.size() == 5); } }大功告成:
以上源码已开源至: https://gitee.com/bytearch_admin/fast-cloud/tree/master/fast-cloud-mysql-sharding 欢迎点赞收藏。
五、总结
本篇主要介绍Java版使用Mango框架实现Mysql分库分表实战，分库分表中间件也可以使用类似于ShardingJDBC，或者自研。
以上分库分表数量仅供演示参考，实际工作中分表数量、分库数量、是根据公司实际业务数据增长速度, 高峰期QPS,物理机器配置等等因素计算。云南idc服务商
- 设计你的个性电脑——煲音箱电脑贴纸教程（从零开始，打造独一无二的音箱电脑）
- 以威的电器品质如何？（全面评估以威电器的性能、价格和用户评价）
- 为什么不适合在厕所玩手机？（为什么不适合在厕所玩手机？）
- 电脑联接错误代码103的解决方法（探究电脑联接错误代码103的原因及解决方案）
- Win764位安装教程（轻松学会Win764位系统的安装与设置）
- 电脑关闭程序后发生错误的原因与解决方法（程序异常退出、错误代码和修复措施一览）
- 电脑判卷子错误的挑战与解决（探索考试电脑判卷子错误的原因，提出有效解决方案）
- 盛行机箱的特点及使用体验（打造高效、安全、个性化的电脑装机之选）
- 电脑磁盘显示错误的原因及解决方法（探究电脑磁盘显示错误的常见原因及如何解决）
- 以欧恩的MP3音质如何？（深入探讨以欧恩MP3音质表现，让您音乐体验升级！）
IT资讯网亿华云IT技术网源码库源码下载服务器租用香港云服务器企商汇益华科技益强科技益强编程舍码力社亿华灵动编程之道益强前沿资讯智能时代益强智囊团码上建站思维库益华IT技术论坛亿华科技益强资讯优选益华科技亿华云计算亿华云益华科技益强编程堂技术快报科技前瞻云站无忧亿华智慧云亿华智造创站工坊极客编程益强科技全栈开发亿华互联益强智未来益强数据堂极客码头云智核运维纵横
- Copyright © 2025 Powered by 浅谈订单重构之 MySQL 分库分表实战篇,创站工坊 滇ICP备2023006006号-44sitemap