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

一、背景

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

二、目标

本文将完成如下目标:

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

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

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

架构图:

表结构如下:

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. 基本信息

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; 
20. } 

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.     } 
41. } 

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.     } 
26. } 

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.     } 
80. } 

大功告成:

以上源码已开源至: 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