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

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

转载作者：qq735679552 更新时间：2022-09-29 22:32:09

29

4

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章浅谈订单重构之 MySQL 分库分表实战篇由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

1、背景

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

2、目标

本文将完成如下目标

* 分表数量: 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;

注: 000<= XXX <= 255, 本文重点在于分库分表实践, 只保留具有代表性字段，其它场景可以在此基础上做改进.

全局唯一ID设计。

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

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

订单号组成项	保留字段	毫秒级时间差	机器数	用户编号(表编号)	自增序列
所占字节(单位bit)	1	39	8	8	8

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

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

3、环境准备

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( 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 ) 。

* 依次导入建表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;

4、配置&实践

1. pom文件。

<dependency>
<groupId>org.jfaster</groupId>
<artifactId>mango-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>com.bytearch</groupId>
<artifactId>fast-cloud-id-generator</artifactId>
<version>${version}</version>
</dependency>
<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);
}
}

大功告成

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

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

5、总结

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

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

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

最后此篇关于浅谈订单重构之 MySQL 分库分表实战篇的文章就讲到这里了,如果你想了解更多关于浅谈订单重构之 MySQL 分库分表实战篇的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

29

4

0

文章推荐： Python项目实战篇-常用验证码标注和识别

文章推荐：分享Wordpress整站的优化指南

文章推荐：点评SEO行业中三类SEO人员

文章推荐：你需要了解的五大智能家居设备

浅谈：服务架构进化论
服务架构进化论原始分布式时代　　一直以来，我可能和大多数的人认知一样，认为我们的服务架构的源头是单体架构，其实不然，早在单体系
浅谈.Net中的序列化和反序列化
序列化和反序列化相信大家都经常听到，也都会用，然而有些人可能不知道：.net为什么要有这个东西以及.net frameword如何为我们实现这样的机制，在这里我也是简单谈谈我对序列化和反序列化的
浅谈：站长怎样才能编辑高质量的网站内容
　　内容，是网站的核心所在。要打造一个受用户和搜索引擎关注的网站，就必须从网站本身的内容抓起。在时下这个网络信息高速发展的时代，许多低质量的信息也在不断地充斥着整个网络，而搜索引擎对一些高质量的内容
浅谈.Net并行计算之数据并行
从第一台计算机问世到现在计算机硬件技术已经有了很大的发展。不管是现在个人使用的PC还是公司使用的服务器。双核，四核,八核的CPU已经非常常见。这样我们可以将我们程序分摊到多个计算机CPU中去计算，在
浅谈.net平台下深拷贝和浅拷贝
基本概念：浅拷贝：指对象的字段被拷贝，而字段引用的对象不会被拷贝，拷贝对象和原对象仅仅是引用名称有所不同，但是它们共用一份实体。对任何一个对象的改变，都会影响到另外一个对象。大部分的引用类型，实
浅谈.NET中加密和解密的实现方法分享
.NET将原来独立的API和SDK合并到一个框架中，这对于程序开发人员非常有利。它将CryptoAPI改编进.NET的System.Security.Cryptography名字空间，使密码服务摆脱
浅谈“文件与文件流”的区别
文件与文件流的区别（自己的话）：在软件开发过程中，我们常常把文件的 “读写操作” ，与 “创造、移动、复制、删除操作” 区分开来
浅谈.Net Core后端单元测试的实现
1. 前言单元测试一直都是"好处大家都知道很多,但是因为种种原因没有实施起来"的一个老大难问题。具体是否应该落地单元测试，以及落地的程度, 每个项目都有自己的情况。本篇为
浅谈 javascript 事件处理
事件处理 1、事件源：任何一个HTML元素（节点），body、div、button 2、事件：你的操作 &
浅谈 Java 反射技术
1、什么是反射? 反射 (Reflection) 是 Java 的特征之一，它允许运行中的 Java 程序获取自身的信息，并且可以操作类或对象的内部属性。 Oracle 官方对
浅谈@RequestParam 参数是否必须传的问题
1、源码展示 ? 1
浅谈 JDBC 元数据
Java 通过JDBC获得连接以后，得到一个Connection 对象，可以从这个对象获得有关数据库管理系统的各种信息，包括数据库中的各个表，表中的各个列，数据类型，触发器，存储过程等各方面的信息。
浅谈.NET反射机制的性能优化附实例下载
可能大家谈到反射面部肌肉都开始抽搐了吧！因为在托管语言里面，最臭名昭著的就是反射！它的性能实在是太低了，甚至在很多时候让我们无法忍受。不过不用那么纠结了，老陈今天就来分享一下如何来优化反射！&nbs
浅谈 Windows 驱动开发，你会了吗？
1. 前言最近一段时间一直在研究windows 驱动开发，简单聊聊。对比 linux，windows 驱动无论是市面上的书籍，视频还是社区，博文以及号主，写的人很少，导
浅谈#ifndef,#define,#endif的作用和用法
问题：ifndef/define/endif”主要目的是防止头文件的重复包含和编译 ========================================================
浅谈.Net Core 认证系统源码解析
不知不觉.Net Core已经推出到3.1了,大多数以.Net为技术栈的公司也开始逐步的切换到了Core,从业也快3年多了,一直坚持着.不管环境
浅谈 java中ArrayList、Vector、LinkedList的区别联系
以前面试的时候经常会碰到这样的问题.，叫你写一下ArrayList.LinkedList.Vector三者之间的区别与联系：原先一直搞不明白，不知道这三者之间到底有什么区别？哎，惭愧，基础太差啊，木
浅谈@RequestParam(required = true)的误区
目录 @RequestParam(required = true)的误区先说结论参数总结 @RequestParam(r
浅谈 FTP、FTPS 与 SFTP的区别
目录 FTP、FTPS 与 SFTP 简介 FTP FTPS SFTP FTP 软件的主动模式和被动模式的区别
浅谈 C++17 里的 Visitor 模式
1、Visitor Pattern 访问者模式是一种行为模式，允许任意的分离的访问者能够在管理者控制下访问所管理的元素。访问者不能改变对象的定义（但这并不是强制性的，你可以约定为允许改变）。对管

首页

博学

6Ren·AI

商城