浅谈订单重构之 MySQL 分库分表实战篇

时间:2022-01-30 02:34:37

一、背景

发布上篇文章浅谈订单重构之路之后,有很多小伙伴想知道,分库分表具体是如何实现的。那么这篇文章具体介绍下,分库分表实战。

二、目标

本文将完成如下目标:

* 分表数量: 256    分库数量: 4

* 以用户ID(user_id) 为数据库分片Key

* 最后测试订单创建,更新,删除, 单订单号查询,根据user_id查询列表操作。

架构图:

浅谈订单重构之 MySQL 分库分表实战篇

表结构如下:


  1. CREATE TABLE `order_XXX` ( 
  2.   `order_id` bigint(20) unsigned NOT NULL, 
  3.   `user_id` int(11) DEFAULT '0' COMMENT '订单id'
  4.   `status` int(11) DEFAULT '0' COMMENT '订单状态'
  5.   `booking_date` datetime DEFAULT NULL, 
  6.   `create_time` datetime DEFAULT NULL, 
  7.   `update_time` datetime DEFAULT NULL, 
  8.   PRIMARY KEY (`order_id`), 
  9.   KEY `idx_user_id` (`user_id`), 
  10.   KEY `idx_bdate` (`booking_date`), 
  11.   KEY `idx_ctime` (`create_time`), 
  12.   KEY `idx_utime` (`update_time`) 
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

注:  000<= XXX <= 255, 本文重点在于分库分表实践, 只保留具有代表性字段,其它场景可以在此基础上做改进。

全局唯一ID设计

要求:1.全局唯一 2:粗略有序 3:可反解出库编号

  • 1bit + 39bit时间差 + 8bit机器号 + 8bit用户编号(库号) + 8bit自增序列

订单号组成项 保留字段 毫秒级时间差 机器数 用户编号(表编号) 自增序列
所占字节(单位bit) 1 39 8 8 8

单机最大QPS: 256000 使用寿命: 17年

订单号生成规则说明详见 浅谈分布式唯一Id生成器之最佳实践

三、环境准备

1. 基本信息

浅谈订单重构之 MySQL 分库分表实战篇

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 命令为


  1. mysql -uroot -pbytearch -h172.30.1.21 order_db_1<fast-cloud-mysql-sharding/doc/sql/order_db_1.sql; 
  2. mysql -uroot -pbytearch -h172.30.1.22 order_db_2<fast-cloud-mysql-sharding/doc/sql/order_db_2.sql; 
  3. mysql -uroot -pbytearch -h172.30.1.23 order_db_3<fast-cloud-mysql-sharding/doc/sql/order_db_3.sql; 
  4. mysql -uroot -pbytearch -h172.30.1.24 order_db_4<fast-cloud-mysql-sharding/doc/sql/order_db_4.sql;   

四、配置&实践

1. pom文件 


  1. <!-- mango 分库分表中间件 -->  
  2.       <dependency> 
  3.           <groupId>org.jfaster</groupId> 
  4.           <artifactId>mango-spring-boot-starter</artifactId> 
  5.           <version>2.0.1</version> 
  6.       </dependency> 
  7.     
  8.        <!-- 分布式ID生成器 --> 
  9.       <dependency> 
  10.           <groupId>com.bytearch</groupId> 
  11.           <artifactId>fast-cloud-id-generator</artifactId> 
  12.           <version>${version}</version> 
  13.       </dependency> 
  14.  
  15.       <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
  16.       <dependency> 
  17.           <groupId>mysql</groupId> 
  18.           <artifactId>mysql-connector-java</artifactId> 
  19.           <version>6.0.6</version> 
  20.       </dependency> 

2. 常量配置


  1. package com.bytearch.fast.cloud.mysql.sharding.common; 
  2.  
  3. /**
  4.  * 分库分表策略常用常量
  5.  */
  6. public class ShardingStrategyConstant { 
  7.     /**
  8.      * database 逻辑名称 ,真实库名为 order_db_XXX
  9.      */
  10.     public static final String LOGIC_ORDER_DATABASE_NAME = "order_db"
  11.     /**
  12.      * 分表数 256,一旦确定不可更改
  13.      */
  14.     public static final int SHARDING_TABLE_NUM = 256
  15.  
  16.     /**
  17.      * 分库数, 不建议更改, 可以更改,但是需要DBA迁移数据
  18.      */
  19.     public static final int SHARDING_DATABASE_NODE_NUM = 4

3. yml 配置

4主4从数据库配置, 这里仅测试默认使用root用户密码,生产环境不建议使用root用户。


  1. mango: 
  2.   scan-package: com.bytearch.fast.cloud.mysql.sharding.dao 
  3.   datasources: 
  4.     - name: order_db_1 
  5.       master: 
  6.         driver-class-name: com.mysql.cj.jdbc.Driver 
  7.         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
  8.         user-name: root 
  9.         password: bytearch 
  10.         maximum-pool-size: 10
  11.         connection-timeout: 3000
  12.       slaves: 
  13.         - driver-class-name: com.mysql.cj.jdbc.Driver 
  14.           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
  15.           user-name: root 
  16.           password: bytearch 
  17.           maximum-pool-size: 10
  18.           connection-timeout: 3000
  19.     - name: order_db_2 
  20.       master: 
  21.         driver-class-name: com.mysql.cj.jdbc.Driver 
  22.         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
  23.         user-name: root 
  24.         password: bytearch 
  25.         maximum-pool-size: 10
  26.         connection-timeout: 3000
  27.       slaves: 
  28.         - driver-class-name: com.mysql.cj.jdbc.Driver 
  29.           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
  30.           user-name: root 
  31.           password: bytearch 
  32.           maximum-pool-size: 10
  33.           connection-timeout: 3000
  34.     - name: order_db_3 
  35.       master: 
  36.         driver-class-name: com.mysql.cj.jdbc.Driver 
  37.         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
  38.         user-name: root 
  39.         password: bytearch 
  40.         maximum-pool-size: 10
  41.         connection-timeout: 3000
  42.       slaves: 
  43.         - driver-class-name: com.mysql.cj.jdbc.Driver 
  44.           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
  45.           user-name: root 
  46.           password: bytearch 
  47.           maximum-pool-size: 10
  48.           connection-timeout: 3000
  49.     - name: order_db_4 
  50.       master: 
  51.         driver-class-name: com.mysql.cj.jdbc.Driver 
  52.         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
  53.         user-name: root 
  54.         password: bytearch 
  55.         maximum-pool-size: 10
  56.         connection-timeout: 3000
  57.       slaves: 
  58.         - driver-class-name: com.mysql.cj.jdbc.Driver 
  59.           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
  60.           user-name: root 
  61.           password: bytearch 
  62.           maximum-pool-size: 10
  63.           connection-timeout: 300

4. 分库分表策略

1). 根据order_id为shardKey分库分表策略


  1. package com.bytearch.fast.cloud.mysql.sharding.strategy; 
  2.  
  3. import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; 
  4. import com.bytearch.id.generator.IdEntity; 
  5. import com.bytearch.id.generator.SeqIdUtil; 
  6. import org.jfaster.mango.sharding.ShardingStrategy; 
  7.  
  8. /**
  9.  * 订单号分库分表策略
  10.  */
  11. public class OrderIdShardingStrategy implements ShardingStrategy<Long, Long> { 
  12.     @Override
  13.     public String getDataSourceFactoryName(Long orderId) { 
  14.         if (orderId == null || orderId < 0L) { 
  15.             throw new IllegalArgumentException("order_id is invalid!"); 
  16.         } 
  17.         IdEntity idEntity = SeqIdUtil.decodeId(orderId); 
  18.         if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) { 
  19.             throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId()); 
  20.         } 
  21.         //1. 计算步长
  22.         int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM; 
  23.         //2. 计算出库编号
  24.         long dbNo = Math.floorDiv(idEntity.getExtraId(), step) + 1
  25.         //3. 返回数据源名
  26.         return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo); 
  27.     } 
  28.  
  29.     @Override
  30.     public String getTargetTable(String logicTableName, Long orderId) { 
  31.         if (orderId == null || orderId < 0L) { 
  32.             throw new IllegalArgumentException("order_id is invalid!"); 
  33.         } 
  34.         IdEntity idEntity = SeqIdUtil.decodeId(orderId); 
  35.         if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) { 
  36.             throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId()); 
  37.         } 
  38.         // 基于约定,真实表名为 logicTableName_XXX, XXX不足三位补0
  39.         return String.format("%s_%03d", logicTableName, idEntity.getExtraId()); 
  40.     } 

2). 根据user_id 为shardKey分库分表策略


  1. package com.bytearch.fast.cloud.mysql.sharding.strategy; 
  2.  
  3. import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; 
  4. import org.jfaster.mango.sharding.ShardingStrategy; 
  5.  
  6. /**
  7.  * 指定分片KEY 分库分表策略
  8.  */
  9. public class UserIdShardingStrategy implements ShardingStrategy<Integer, Integer> { 
  10.  
  11.     @Override
  12.     public String getDataSourceFactoryName(Integer userId) { 
  13.         //1. 计算步长 即单库放得表数量
  14.         int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM; 
  15.         //2. 计算出库编号
  16.         long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1
  17.         //3. 返回数据源名
  18.         return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo); 
  19.     } 
  20.  
  21.     @Override
  22.     public String getTargetTable(String logicTableName, Integer userId) { 
  23.         // 基于约定,真实表名为 logicTableName_XXX, XXX不足三位补0
  24.         return String.format("%s_%03d", logicTableName, userId % ShardingStrategyConstant.SHARDING_TABLE_NUM); 
  25.     } 

5. dao层编写

1). OrderPartitionByIdDao


  1. package com.bytearch.fast.cloud.mysql.sharding.dao; 
  2.  
  3. import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; 
  4. import com.bytearch.fast.cloud.mysql.sharding.pojo.entity.OrderEntity; 
  5. import com.bytearch.fast.cloud.mysql.sharding.strategy.OrderIdShardingStrategy; 
  6. import org.jfaster.mango.annotation.*; 
  7.  
  8. @DB(name = ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, table = "order"
  9. @Sharding(shardingStrategy = OrderIdShardingStrategy.class
  10. public interface OrderPartitionByIdDao { 
  11.  
  12.     @SQL("INSERT INTO #table (order_id, user_id, status, booking_date, create_time, update_time) VALUES" + 
  13.             "(:orderId,:userId,:status,:bookingDate,:createTime,:updateTime)"
  14.     ) 
  15.     int insertOrder(@TableShardingBy("orderId"@DatabaseShardingBy("orderId") OrderEntity orderEntity); 
  16.  
  17.     @SQL("UPDATE #table set update_time = now()" + 
  18.             "#if(:bookingDate != null),booking_date = :bookingDate #end " + 
  19.             "#if (:status != null), status = :status #end" + 
  20.             "WHERE order_id = :orderId"
  21.     ) 
  22.     int updateOrderByOrderId(@TableShardingBy("orderId"@DatabaseShardingBy("orderId") OrderEntity orderEntity); 
  23.  
  24.  
  25.     @SQL("SELECT * FROM #table WHERE order_id = :1"
  26.     OrderEntity getOrderById(@TableShardingBy @DatabaseShardingBy Long orderId); 
  27.  
  28.     @SQL("SELECT * FROM #table WHERE order_id = :1"
  29.     @UseMaster
  30.     OrderEntity getOrderByIdFromMaster(@TableShardingBy @DatabaseShardingBy Long orderId); 

6. 单元测试


  1. @SpringBootTest(classes = {Application.class}) 
  2. @RunWith(SpringJUnit4ClassRunner.class
  3. public class ShardingTest { 
  4.     @Autowired
  5.     OrderPartitionByIdDao orderPartitionByIdDao; 
  6.  
  7.     @Autowired
  8.     OrderPartitionByUserIdDao orderPartitionByUserIdDao; 
  9.  
  10.     @Test
  11.     public void testCreateOrderRandom() { 
  12.         for (int i = 0; i < 20; i++) { 
  13.             int userId = ThreadLocalRandom.current().nextInt(1000,1000000); 
  14.             OrderEntity orderEntity = new OrderEntity(); 
  15.             orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); 
  16.             orderEntity.setStatus(1); 
  17.             orderEntity.setUserId(userId); 
  18.             orderEntity.setCreateTime(new Date()); 
  19.             orderEntity.setUpdateTime(new Date()); 
  20.             orderEntity.setBookingDate(new Date()); 
  21.             int ret = orderPartitionByIdDao.insertOrder(orderEntity); 
  22.             Assert.assertEquals(1, ret); 
  23.         } 
  24.     } 
  25.  
  26.     @Test
  27.     public void testOrderAll() { 
  28.         //insert
  29.         int userId = ThreadLocalRandom.current().nextInt(1000,1000000); 
  30.         OrderEntity orderEntity = new OrderEntity(); 
  31.         orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); 
  32.         orderEntity.setStatus(1); 
  33.         orderEntity.setUserId(userId); 
  34.         orderEntity.setCreateTime(new Date()); 
  35.         orderEntity.setUpdateTime(new Date()); 
  36.         orderEntity.setBookingDate(new Date()); 
  37.         int i = orderPartitionByIdDao.insertOrder(orderEntity); 
  38.         Assert.assertEquals(1, i); 
  39.  
  40.         //get from master
  41.         OrderEntity orderInfo = orderPartitionByIdDao.getOrderByIdFromMaster(orderEntity.getOrderId()); 
  42.         Assert.assertNotNull(orderInfo); 
  43.         Assert.assertEquals(orderInfo.getOrderId(), orderEntity.getOrderId()); 
  44.  
  45.         //get from slave
  46.         OrderEntity slaveOrderInfo = orderPartitionByIdDao.getOrderById(orderEntity.getOrderId()); 
  47.         Assert.assertNotNull(slaveOrderInfo); 
  48.         //update
  49.         OrderEntity updateEntity = new OrderEntity(); 
  50.         updateEntity.setOrderId(orderInfo.getOrderId()); 
  51.         updateEntity.setStatus(2); 
  52.         updateEntity.setUpdateTime(new Date()); 
  53.         int affectRows = orderPartitionByIdDao.updateOrderByOrderId(updateEntity); 
  54.         Assert.assertTrue( affectRows > 0); 
  55.     } 
  56.  
  57.     @Test
  58.     public void testGetListByUserId() { 
  59.         int userId = ThreadLocalRandom.current().nextInt(1000,1000000); 
  60.         for (int i = 0; i < 5; i++) { 
  61.             OrderEntity orderEntity = new OrderEntity(); 
  62.             orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); 
  63.             orderEntity.setStatus(1); 
  64.             orderEntity.setUserId(userId); 
  65.             orderEntity.setCreateTime(new Date()); 
  66.             orderEntity.setUpdateTime(new Date()); 
  67.             orderEntity.setBookingDate(new Date()); 
  68.             orderPartitionByIdDao.insertOrder(orderEntity); 
  69.         } 
  70.         try { 
  71.             //防止主从延迟引起的校验错误
  72.             Thread.sleep(1000); 
  73.         } catch (InterruptedException e) { 
  74.             e.printStackTrace(); 
  75.         } 
  76.         List<OrderEntity> orderListByUserId = orderPartitionByUserIdDao.getOrderListByUserId(userId); 
  77.         Assert.assertNotNull(orderListByUserId); 
  78.         Assert.assertTrue(orderListByUserId.size() == 5); 
  79.     } 

大功告成:

浅谈订单重构之 MySQL 分库分表实战篇

以上源码已开源至: https://gitee.com/bytearch_admin/fast-cloud/tree/master/fast-cloud-mysql-sharding 欢迎点赞收藏。

五、总结

本篇主要介绍Java版使用Mango框架实现Mysql分库分表实战,分库分表中间件也可以使用类似于ShardingJDBC,或者自研。

以上分库分表数量仅供演示参考,实际工作中分表数量、分库数量、是根据公司实际业务数据增长速度, 高峰期QPS,物理机器配置等等因素计算。

原文链接:https://mp.weixin.qq.com/s/auIpyX6AtpCRhyD4_r0hPQ?utm_source=tuicool&utm_medium=referral