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

发布上篇文章浅谈订单重构之路之后,有很多小伙伴想知道,分库分表具体是如何实现的。那么这篇文章具体介绍下,分库分表实战。

一、背景

发布上篇文章浅谈订单重构之路之后,有很多小伙伴想知道,分库分表具体是如何实现的。那么这篇文章具体介绍下,分库分表实战。

二、目标

本文将完成如下目标:

* 分表数量: 256 分库数量: 4

* 以用户ID(user_id) 为数据库分片Key

* 最后测试订单创建,更新,删除, 单订单号查询,根据user_id查询列表操作。

架构图:

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

表结构如下:

  • CREATETABLE`order_XXX`(
  • `order_id`bigint(20)unsignedNOTNULL,
  • `user_id`int(11)DEFAULT'0'COMMENT'订单id',
  • `status`int(11)DEFAULT'0'COMMENT'订单状态',
  • `booking_date`datetimeDEFAULTNULL,
  • `create_time`datetimeDEFAULTNULL,
  • `update_time`datetimeDEFAULTNULL,
  • PRIMARYKEY(`order_id`),
  • KEY`idx_user_id`(`user_id`),
  • KEY`idx_bdate`(`booking_date`),
  • KEY`idx_ctime`(`create_time`),
  • KEY`idx_utime`(`update_time`)
  • )ENGINE=InnoDBDEFAULTCHARSET=utf8;
  • 注: 000<= XXX <= 255, 本文重点在于分库分表实践, 只保留具有代表性字段,其它场景可以在此基础上做改进。

    全局唯一ID设计

    要求:1.全局唯一 2:粗略有序 3:可反解出库编号

    • 1bit + 39bit时间差 + 8bit机器号 + 8bit用户编号(库号) + 8bit自增序列

    订单号组成项

    保留字段

    毫秒级时间差

    机器数

    用户编号(表编号)

    自增序列

    所占字节(单位bit)139888

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

    订单号生成规则说明详见浅谈分布式唯一Id生成器之最佳实践

    三、环境准备

    1. 基本信息

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

    2. 数据库环境准备

    温馨提示:使用docker-compose快速搭建了4主4从数据库集群,实现本地快速一键部署,生产环境一般由DBA同学搭建。

    具体实现请移步查看:https://gitee.com/bytearch_admin/docker-app/tree/main/mysql-cluster

    3. 建库 & 导入分表

    * 在mysql master实例分别建库

    172.30.1.21(order_db_1),172.30.1.22(order_db_2),

    172.30.1.23(order_db_3), 172.30.1.24(order_db_4)

    * 依次导入建表SQL命令为

  • mysql-uroot-pbytearch-h172.30.1.21order_db_1<fast-cloud-mysql-sharding/doc/sql/order_db_1.sql;
  • mysql-uroot-pbytearch-h172.30.1.22order_db_2<fast-cloud-mysql-sharding/doc/sql/order_db_2.sql;
  • mysql-uroot-pbytearch-h172.30.1.23order_db_3<fast-cloud-mysql-sharding/doc/sql/order_db_3.sql;
  • mysql-uroot-pbytearch-h172.30.1.24order_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. 常量配置

  • packagecom.bytearch.fast.cloud.mysql.sharding.common;
  • /**
  • *分库分表策略常用常量
  • */
  • publicclassShardingStrategyConstant{
  • /**
  • *database逻辑名称,真实库名为order_db_XXX
  • */
  • publicstaticfinalStringLOGIC_ORDER_DATABASE_NAME="order_db";
  • /**
  • *分表数256,一旦确定不可更改
  • */
  • publicstaticfinalintSHARDING_TABLE_NUM=256;
  • /**
  • *分库数,不建议更改,可以更改,但是需要DBA迁移数据
  • */
  • publicstaticfinalintSHARDING_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分库分表策略

  • packagecom.bytearch.fast.cloud.mysql.sharding.strategy;
  • importcom.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
  • importcom.bytearch.id.generator.IdEntity;
  • importcom.bytearch.id.generator.SeqIdUtil;
  • importorg.jfaster.mango.sharding.ShardingStrategy;
  • /**
  • *订单号分库分表策略
  • */
  • publicclassOrderIdShardingStrategyimplementsShardingStrategy<Long,Long>{
  • @Override
  • publicStringgetDataSourceFactoryName(LongorderId){
  • if(orderId==null||orderId<0L){
  • thrownewIllegalArgumentException("order_idisinvalid!");
  • }
  • IdEntityidEntity=SeqIdUtil.decodeId(orderId);
  • if(idEntity.getExtraId()>=ShardingStrategyConstant.SHARDING_TABLE_NUM){
  • thrownewIllegalArgumentException("shardingtableNumisinvalid,tableNum:"+idEntity.getExtraId());
  • }
  • //1.计算步长
  • intstep=ShardingStrategyConstant.SHARDING_TABLE_NUM/ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
  • //2.计算出库编号
  • longdbNo=Math.floorDiv(idEntity.getExtraId(),step)+1;
  • //3.返回数据源名
  • returnString.format("%s_%s",ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME,dbNo);
  • }
  • @Override
  • publicStringgetTargetTable(StringlogicTableName,LongorderId){
  • if(orderId==null||orderId<0L){
  • thrownewIllegalArgumentException("order_idisinvalid!");
  • }
  • IdEntityidEntity=SeqIdUtil.decodeId(orderId);
  • if(idEntity.getExtraId()>=ShardingStrategyConstant.SHARDING_TABLE_NUM){
  • thrownewIllegalArgumentException("shardingtableNumisinvalid,tableNum:"+idEntity.getExtraId());
  • }
  • //基于约定,真实表名为logicTableName_XXX,XXX不足三位补0
  • returnString.format("%s_%03d",logicTableName,idEntity.getExtraId());
  • }
  • }
  • 2). 根据user_id 为shardKey分库分表策略

  • packagecom.bytearch.fast.cloud.mysql.sharding.strategy;
  • importcom.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
  • importorg.jfaster.mango.sharding.ShardingStrategy;
  • /**
  • *指定分片KEY分库分表策略
  • */
  • publicclassUserIdShardingStrategyimplementsShardingStrategy<Integer,Integer>{
  • @Override
  • publicStringgetDataSourceFactoryName(IntegeruserId){
  • //1.计算步长即单库放得表数量
  • intstep=ShardingStrategyConstant.SHARDING_TABLE_NUM/ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
  • //2.计算出库编号
  • longdbNo=Math.floorDiv(userId%ShardingStrategyConstant.SHARDING_TABLE_NUM,step)+1;
  • //3.返回数据源名
  • returnString.format("%s_%s",ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME,dbNo);
  • }
  • @Override
  • publicStringgetTargetTable(StringlogicTableName,IntegeruserId){
  • //基于约定,真实表名为logicTableName_XXX,XXX不足三位补0
  • returnString.format("%s_%03d",logicTableName,userId%ShardingStrategyConstant.SHARDING_TABLE_NUM);
  • }
  • }
  • 5. dao层编写

    1). OrderPartitionByIdDao

  • packagecom.bytearch.fast.cloud.mysql.sharding.dao;
  • importcom.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
  • importcom.bytearch.fast.cloud.mysql.sharding.pojo.entity.OrderEntity;
  • importcom.bytearch.fast.cloud.mysql.sharding.strategy.OrderIdShardingStrategy;
  • importorg.jfaster.mango.annotation.*;
  • @DB(name=ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME,table="order")
  • @Sharding(shardingStrategy=OrderIdShardingStrategy.class)
  • publicinterfaceOrderPartitionByIdDao{
  • @SQL("INSERTINTO#table(order_id,user_id,status,booking_date,create_time,update_time)VALUES"+
  • "(:orderId,:userId,:status,:bookingDate,:createTime,:updateTime)"
  • )
  • intinsertOrder(@TableShardingBy("orderId")@DatabaseShardingBy("orderId")OrderEntityorderEntity);
  • @SQL("UPDATE#tablesetupdate_time=now()"+
  • "#if(:bookingDate!=null),booking_date=:bookingDate#end"+
  • "#if(:status!=null),status=:status#end"+
  • "WHEREorder_id=:orderId"
  • )
  • intupdateOrderByOrderId(@TableShardingBy("orderId")@DatabaseShardingBy("orderId")OrderEntityorderEntity);
  • @SQL("SELECT*FROM#tableWHEREorder_id=:1")
  • OrderEntitygetOrderById(@TableShardingBy@DatabaseShardingByLongorderId);
  • @SQL("SELECT*FROM#tableWHEREorder_id=:1")
  • @UseMaster
  • OrderEntitygetOrderByIdFromMaster(@TableShardingBy@DatabaseShardingByLongorderId);
  • 6. 单元测试

  • @SpringBootTest(classes={Application.class})
  • @RunWith(SpringJUnit4ClassRunner.class)
  • publicclassShardingTest{
  • @Autowired
  • OrderPartitionByIdDaoorderPartitionByIdDao;
  • @Autowired
  • OrderPartitionByUserIdDaoorderPartitionByUserIdDao;
  • @Test
  • publicvoidtestCreateOrderRandom(){
  • for(inti=0;i<20;i++){
  • intuserId=ThreadLocalRandom.current().nextInt(1000,1000000);
  • OrderEntityorderEntity=newOrderEntity();
  • orderEntity.setOrderId(SeqIdUtil.nextId(userId%ShardingStrategyConstant.SHARDING_TABLE_NUM));
  • orderEntity.setStatus(1);
  • orderEntity.setUserId(userId);
  • orderEntity.setCreateTime(newDate());
  • orderEntity.setUpdateTime(newDate());
  • orderEntity.setBookingDate(newDate());
  • intret=orderPartitionByIdDao.insertOrder(orderEntity);
  • Assert.assertEquals(1,ret);
  • }
  • }
  • @Test
  • publicvoidtestOrderAll(){
  • //insert
  • intuserId=ThreadLocalRandom.current().nextInt(1000,1000000);
  • OrderEntityorderEntity=newOrderEntity();
  • orderEntity.setOrderId(SeqIdUtil.nextId(userId%ShardingStrategyConstant.SHARDING_TABLE_NUM));
  • orderEntity.setStatus(1);
  • orderEntity.setUserId(userId);
  • orderEntity.setCreateTime(newDate());
  • orderEntity.setUpdateTime(newDate());
  • orderEntity.setBookingDate(newDate());
  • inti=orderPartitionByIdDao.insertOrder(orderEntity);
  • Assert.assertEquals(1,i);
  • //getfrommaster
  • OrderEntityorderInfo=orderPartitionByIdDao.getOrderByIdFromMaster(orderEntity.getOrderId());
  • Assert.assertNotNull(orderInfo);
  • Assert.assertEquals(orderInfo.getOrderId(),orderEntity.getOrderId());
  • //getfromslave
  • OrderEntityslaveOrderInfo=orderPartitionByIdDao.getOrderById(orderEntity.getOrderId());
  • Assert.assertNotNull(slaveOrderInfo);
  • //update
  • OrderEntityupdateEntity=newOrderEntity();
  • updateEntity.setOrderId(orderInfo.getOrderId());
  • updateEntity.setStatus(2);
  • updateEntity.setUpdateTime(newDate());
  • intaffectRows=orderPartitionByIdDao.updateOrderByOrderId(updateEntity);
  • Assert.assertTrue(affectRows>0);
  • }
  • @Test
  • publicvoidtestGetListByUserId(){
  • intuserId=ThreadLocalRandom.current().nextInt(1000,1000000);
  • for(inti=0;i<5;i++){
  • OrderEntityorderEntity=newOrderEntity();
  • orderEntity.setOrderId(SeqIdUtil.nextId(userId%ShardingStrategyConstant.SHARDING_TABLE_NUM));
  • orderEntity.setStatus(1);
  • orderEntity.setUserId(userId);
  • orderEntity.setCreateTime(newDate());
  • orderEntity.setUpdateTime(newDate());
  • orderEntity.setBookingDate(newDate());
  • orderPartitionByIdDao.insertOrder(orderEntity);
  • }
  • try{
  • //防止主从延迟引起的校验错误
  • Thread.sleep(1000);
  • }catch(InterruptedExceptione){
  • e.printStackTrace();
  • }
  • List<OrderEntity>orderListByUserId=orderPartitionByUserIdDao.getOrderListByUserId(userId);
  • Assert.assertNotNull(orderListByUserId);
  • Assert.assertTrue(orderListByUserId.size()==5);
  • }
  • }
  • 大功告成:

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

    以上源码已开源至: https://gitee.com/bytearch_admin/fast-cloud/tree/master/fast-cloud-mysql-sharding 欢迎点赞收藏。

    五、总结

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

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

    原文链接:https://mp.weixin.qq.com/s/auIpyX6AtpCRhyD4_r0hPQ?utm_source=tuicool&utm_medium=referral

    版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/6213/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

    (0)
    上一篇 2022-07-28 12:08:18
    下一篇 2022-07-28 12:08:36

    软件定制开发公司

    相关阅读

    发表回复

    登录后才能评论
    通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!