锐客网

  1. 首页 > it技术 > >

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

目录

  • 一、目标
  • 二、环境准备
    • 1、基本信息
    • 2、数据库环境准备
    • 3、建库 & 导入分表
  • 三、配置&实践
    • 1、pom文件
    • 2、常量配置
    • 3、yml 配置
    • 4、分库分表策略
    • 5、dao层编写
    • 6、单元测试
  • 四、总结

    一、目标 本文将完成如下目标:

    • 分表数量: 256分库数量: 4
    • 以用户ID(user_id) 为数据库分片Key
    • 最后测试订单创建，更新，删除, 单订单号查询，根据user_id查询列表操作。
    架构图:
    浅谈订单重构之|浅谈订单重构之 MySQL 分库分表实战篇
    文章图片

    表结构如下:
    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;

    【浅谈订单重构之|浅谈订单重构之 MySQL 分库分表实战篇】注: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.jfastermango-spring-boot-starter2.0.1com.bytearchfast-cloud-id-generator${version}mysqlmysql-connector-java6.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.daodatasources:- name: order_db_1master:driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.21:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-timeout: 3000slaves:- driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.31:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-timeout: 3000- name: order_db_2master:driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.22:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-timeout: 3000slaves:- driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.32:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-timeout: 3000- name: order_db_3master:driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.23:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-timeout: 3000slaves:- driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.33:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-timeout: 3000- name: order_db_4master:driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.24:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-timeout: 3000slaves:- driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://172.30.1.34:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=falseuser-name: rootpassword: bytearchmaximum-pool-size: 10connection-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 {@Overridepublic 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); }@Overridepublic 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不足三位补0return 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 {@Overridepublic 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); }@Overridepublic String getTargetTable(String logicTableName, Integer userId) {// 基于约定，真实表名为 logicTableName_XXX， XXX不足三位补0return 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")@UseMasterOrderEntity getOrderByIdFromMaster(@TableShardingBy @DatabaseShardingBy Long orderId);


    6、单元测试
    @SpringBootTest(classes = {Application.class})@RunWith(SpringJUnit4ClassRunner.class)public class ShardingTest {@AutowiredOrderPartitionByIdDao orderPartitionByIdDao; @AutowiredOrderPartitionByUserIdDao orderPartitionByUserIdDao; @Testpublic 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); }}@Testpublic void testOrderAll() {//insertint 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 masterOrderEntity orderInfo = orderPartitionByIdDao.getOrderByIdFromMaster(orderEntity.getOrderId()); Assert.assertNotNull(orderInfo); Assert.assertEquals(orderInfo.getOrderId(), orderEntity.getOrderId()); //get from slaveOrderEntity slaveOrderInfo = orderPartitionByIdDao.getOrderById(orderEntity.getOrderId()); Assert.assertNotNull(slaveOrderInfo); //updateOrderEntity updateEntity = new OrderEntity(); updateEntity.setOrderId(orderInfo.getOrderId()); updateEntity.setStatus(2); updateEntity.setUpdateTime(new Date()); int affectRows = orderPartitionByIdDao.updateOrderByOrderId(updateEntity); Assert.assertTrue( affectRows > 0); }@Testpublic 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); }}

    大功告成:
    浅谈订单重构之|浅谈订单重构之 MySQL 分库分表实战篇
    文章图片


    四、总结
    本篇主要介绍Java版使用Mango框架实现Mysql分库分表实战，分库分表中间件也可以使用类似于ShardingJDBC，或者自研。
    以上分库分表数量仅供演示参考，实际工作中分表数量、分库数量、是根据公司实际业务数据增长速度, 高峰期QPS,物理机器配置等等因素计算。
    到此这篇关于浅谈订单重构之 MySQL 分库分表实战篇的文章就介绍到这了,更多相关MySQL 分库分表内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

      推荐阅读


      上一篇：PyTorch一小时掌握之迁移学习篇

      下一篇：SpringMVC配置多个properties文件之通配符解析