自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

淺談訂單重構之 MySQL 分庫分表實戰(zhàn)篇

作者：佚名
數(shù)據(jù)庫 MySQL
發(fā)布上篇文章淺談訂單重構之路之后，有很多小伙伴想知道，分庫分表具體是如何實現(xiàn)的。那么這篇文章具體介紹下，分庫分表實戰(zhàn)。

一、背景

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

二、目標

本文將完成如下目標:

* 分表數(shù)量: 256    分庫數(shù)量: 4

* 以用戶ID(user_id) 為數(shù)據(jù)庫分片Key

* 最后測試訂單創(chuàng)建，更新，刪除, 單訂單號查詢，根據(jù)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 '訂單狀態(tài)', 
  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自增序列

訂單號組成項 保留字段 毫秒級時間差 機器數(shù) 用戶編號(表編號) 自增序列
所占字節(jié)(單位bit) 1 39 8 8 8

單機最大QPS: 256000 使用壽命: 17年

訂單號生成規(guī)則說明詳見 淺談分布式唯一Id生成器之最佳實踐

三、環(huán)境準備

1. 基本信息

2. 數(shù)據(jù)庫環(huán)境準備

溫馨提示：使用docker-compose快速搭建了4主4從數(shù)據(jù)庫集群，實現(xiàn)本地快速一鍵部署，生產環(huán)境一般由DBA同學搭建。

具體實現(xiàn)請移步查看: 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.      * 分表數(shù) 256，一旦確定不可更改 
  13.      */ 
  14.     public static final int SHARDING_TABLE_NUM = 256; 
  15.  
  16.     /** 
  17.      * 分庫數(shù), 不建議更改， 可以更改，但是需要DBA遷移數(shù)據(jù) 
  18.      */ 
  19.     public static final int SHARDING_DATABASE_NODE_NUM = 4; 
  20. } 

3. yml 配置

4主4從數(shù)據(jù)庫配置， 這里僅測試默認使用root用戶密碼，生產環(huán)境不建議使用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). 根據(jù)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. 返回數(shù)據(jù)源名 
  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). 根據(jù)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. 計算步長 即單庫放得表數(shù)量 
  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. 返回數(shù)據(jù)源名 
  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框架實現(xiàn)Mysql分庫分表實戰(zhàn)，分庫分表中間件也可以使用類似于ShardingJDBC，或者自研。

以上分庫分表數(shù)量僅供演示參考，實際工作中分表數(shù)量、分庫數(shù)量、是根據(jù)公司實際業(yè)務數(shù)據(jù)增長速度, 高峰期QPS,物理機器配置等等因素計算。

 

責任編輯：張燕妮 來源： 淺談架構
數(shù)據(jù)庫工具技術
相關推薦

2022-10-10 17:37:59

分庫分表訂單業(yè)務

2022-06-30 07:34:46

分庫分表外賣訂單系統(tǒng)

2022-10-13 17:43:10

MySQL存放數(shù)據(jù)

2022-11-30 07:58:10

支付業(yè)務系統(tǒng)分庫分表

2022-10-09 18:14:31

訂單系統(tǒng)分庫分表

2019-05-21 14:33:01

2022-07-04 23:24:28

sql優(yōu)化監(jiān)控

2020-11-18 09:39:02

MySQL數(shù)據(jù)庫SQL

2022-06-22 07:32:53

Sharding分庫數(shù)據(jù)源

2018-05-08 18:26:49

數(shù)據(jù)庫MySQL性能

2020-07-30 17:59:34

分庫分表SQL數(shù)據(jù)庫

2022-01-28 08:59:59

分庫分表數(shù)據(jù)

2018-09-21 15:50:58

數(shù)據(jù)庫MySQL分庫分表

2022-07-08 08:57:36

數(shù)據(jù)優(yōu)化垂直拆分數(shù)據(jù)庫

2022-01-26 07:59:07

緩存分庫分表

2019-07-31 09:27:23

數(shù)據(jù)庫MySQLSQL

2022-06-30 14:07:10

分庫分表系統(tǒng)

2022-07-07 09:33:06

MySQL查詢數(shù)據(jù)優(yōu)化

2019-11-12 09:54:20

分庫分表數(shù)據(jù)

2022-07-03 19:01:19

磁盤IOMySQL
點贊
收藏

51CTO技術棧公眾號