浅谈订单重构之 MySQL 分库分表实战篇
作者：公众账号 发布时间：2024-01-13 00:53:36
目录
一、目标
二、环境准备
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自增序列
| 订单号组成项 | 保留字段 | 毫秒级时间差 | 机器数 | 用户编号(表编号) | 自增序列 |
|---|---|---|---|---|---|
| 所占字节(单位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<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
0
投稿猜你喜欢
微信小程序 HTTPS报错常见问题及解决方案微信小程序开放公测已经一个多月了，因官方需求文档要求后台使用HTTPS请求进行网络通信，不满足条- 在许多情况下，对外键使用更复杂的逻辑表达式是非常有用的。 此外，在某些情况下能够在索引视图创建约束也将非常实用。 我将举例说明，同时我希望针
- 本文实例讲述了PHP实现绘制二叉树图形显示功能。分享给大家供大家参考，具体如下：前言：最近老师布置了一个作业：理解并实现平衡二叉树和红黑树，
- 最近在看廖老师的python教程，在看到关于文件的操作时，廖老师的其中一段关于查找电脑里的python文件，突然想把之前写的python代码
- 点击进入Lombok官网下载Lombok jar包使用Lombok可能需要注意的地方（1）、当你的IDE是Idea时，要注意你的Idea是支
- Django是一种基于Python的Web开发框架一、在命令行中创建Django项目（Windows平台、python3.6）1、建立虚拟环
- 这些问题可能导致数据值的改变。一般而言，上述问题可能在你使用非二进制串数据类型（如char，varchar，text等数据类型）的情况下发生
- 模块a.py 想用 b.py中公有数据 cntb的python文件#!/usr/bin/env python# coding:utf8fro
- 正常开发的时候，我们都手动去写要引入到包，有过java开发的同事，用过快捷键ctrl + alt + o 会自动引入所有的依赖包，pycha
- 一、引入必要文件下载地址：(https://github.com/nghuuphuoc/bootstrapvalidator/archive
- enumerate函数用于遍历序列中的元素以及它们的下标。enumerate函数说明：函数原型：enumerate(sequence, [s
- 目录1、every2、some3、none1、everydef every(lst, fn=lambda x: x): retu
- 首先我们有一个接口可以获取动态的vkeyhttps://vv.video.qq.com/getinfo?otype=json&app
- 下载a.我这里用的是 ZIP Archive版的，win8 64位的机器都支持 这个所以我建议都用这个。还有这个比较简单而且还很
- 本文实例讲述了PHP函数extension_loaded()用法。分享给大家供大家参考。具体分析如下：extension_loaded —
- 前言验证码的种类有很多，它是常用的一种反爬手段，包括：图片验证码，滑块验证码，等一些常见的验证码场景。识别验证码的python 库有很多，用
- 静态页面是蜘蛛喜欢的，会得到蜘蛛经常光顾的，以至于网站上的内容会得到搜索引擎更多的收录。这里介绍一个asp伪静态的程序实现方法数据库是acc
- SQL Server执行动态SQL的话,应该如何实现呢?下面就为您介绍SQL Server执行动态SQL两种正确方式,希望可以让您对SQL
- 假设你已经做好了如下配置和尝试在Extensions中安装好了Remote -SSH添加了你要访问的服务器ip地址等信息，并拥有了一个con
- 本文实例讲述了JavaScript数据库TaffyDB用法。分享给大家供大家参考。具体如下：TaffyDB 是一个免费开源的 JavaScr
