首页 数据库技术 正文
浅谈订单重构之 MySQL 分库分表实战篇

 2022-10-27    400  

一、背景

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

二、目标

本文将完成如下目标:

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

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

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

架构图:

表结构如下:

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) 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(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);
}
}

大功告成:

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

五、总结

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

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

  •  标签：  
  • MySQL
    •  

发表评论：

原文链接：https://77isp.com/post/10479.html

=========================================

https://77isp.com/ 为 “云服务器技术网” 唯一官方服务平台，请勿相信其他任何渠道。

热门文章
热门标签
最新留言
友情链接

首页 IT运维空间 网云互联 1818IP网

本站资源均来自原创及互联网，仅供学习与参考，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。

苏ICP备2022008373号-1