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

目录

• 一、目标

• 二、环境准备

• 1、基本信息

• 2、数据库环境准备

• 3、建库 & 导入分表

• 三、配置&实践

• 1、pom文件  

• 2、常量配置

• 3、yml 配置

• 4、分库分表策略

• 5、dao层编写

• 6、单元测试

• 四、总结

一、目标

本文将完成如下目标:

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

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

二、环境准备

1、基本信息

2、数据库环境准备

进入mysql:

#主库
mysql -h 172.30.1.21 -uroot -pbytearch

#从库
mysql -h 172.30.1.31 -uroot -pbytearch

进入容器

#主
docker exec -it db_1_master /bin/bash

#从
docker exec -it db_1_slave /bin/bash

查看运行状态

#主
docker exec db_1_master sh -c 'mysql -u root -pbytearch -e "SHOW MASTER STATUS \G"'
#从
docker exec db_1_slave sh -c 'mysql -u root -pbytearch -e "SHOW SLAVE STATUS \G"'

3、建库 & 导入分表

（1）在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 )

（2）依次导入建表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);
   }
}

大功告成:

四、总结

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

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

来源：https://mp.weixin.qq.com/s/auIpyX6AtpCRhyD4_r0hPQ?utm_source=tuicool&utm_medium=referral