一、背景

發布上篇文章淺談訂單重構之路之后，有很多小伙伴想知道，分庫分表具體是如何實現的。那么這篇文章具體介紹下，分庫分表實戰。

二、目標

本文將完成如下目標:

* 分表數量: 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自增序列

單機最大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