1000元配置

2000元配置

3000元配置

4000元配置

5000元配置

6000元配置

7000元配置

8000元配置

9000元配置

万元配置

i3配置

 i5配置

i7配置

APU配置

R5配置

R7配置

游戏攻略

软件教程

Win10教程

Win7教程

Mac教程

Linux教程

U盘教程

Server教程

首页 > 数据库 > MySQL

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

admin MySQL 2022-02-09 18:58:46 订单重构   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年

二、环境准备

1、基本信息

项 版本 备注
SpringBoot 2.1.10.RELEASE
Mango 1.6.16 wiki地址:https://github.com/jfaster/mango
HikariCP 3.2.0
Mysql 5.7 测试使用docker一键搭建

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


三、配置&实践


1、pom文件  





      
            
                org.jfaster
                mango-spring-boot-starter
                2.0.1
            
         
             
            
                com.bytearch
                fast-cloud-id-generator
                ${version}
            

            
            
                mysql
                mysql-connector-java
                6.0.6
            




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 {
    @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 {

    @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 orderListByUserId = orderPartitionByUserIdDao.getOrderListByUserId(userId);
        Assert.assertNotNull(orderListByUserId);
        Assert.assertTrue(orderListByUserId.size() == 5);
    }
}





大功告成:




四、总结




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


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


到此这篇关于浅谈订单重构之 MySQL 分库分表实战篇的文章就介绍到这了,更多相关MySQL 分库分表内容请搜索潘少俊衡以前的文章或继续浏览下面的相关文章希望大家以后多多支持潘少俊衡！
版权声明

本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。
本文地址：/shujuku/MySQL/99827.html

上一篇 : 五分钟让你快速弄懂MySQL索引下推
下一篇 : MySQL数据库主从复制原理及作用分析

相关文章

留言与评论（共有 0 条评论）
   
验证码：

热门文章

最近发表

标签列表

潘少俊衡

| 桂ICP备2023010378号-4

Powered By EmpireCMS

爱享小站

中德益农

谷姐神农

环亚肥料

桂ICP备2023010378号-4

桂公网备 45012202000125号

联系我们

商业合作

广告投放

投稿须知

使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

感谢潘少俊衡友情技术支持