淺談訂單重構之 MySQL 分庫分表實戰篇

作者：佚名 2021-09-08 09:48:39

數據庫 MySQL

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

一、背景

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

二、目標

本文將完成如下目標:

* 分表數量: 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, 本文重點在于分庫分表實踐, 只保留具有代表性字段，其它場景可以在此基礎上做改進。

全局唯一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: 300

4. 分庫分表策略

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,物理機器配置等等因素計算。

責任編輯：張燕妮來源：淺談架構

數據庫工具技術