淺談訂單重構(gòu)之 MySQL 分庫分表實戰(zhàn)篇
發(fā)布日期：2022-02-07 13:52 | 文章來源：源碼中國
一、目標(biāo)
本文將完成如下目標(biāo):
- 分表數(shù)量: 256 分庫數(shù)量: 4
- 以用戶ID(user_id) 為數(shù)據(jù)庫分片Key
- 最后測試訂單創(chuàng)建，更新，刪除, 單訂單號查詢，根據(jù)user_id查詢列表操作。
架構(gòu)圖:
表結(jié)構(gòu)如下:
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 '訂單狀態(tài)', `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, 本文重點在于分庫分表實踐, 只保留具有代表性字段，其它場景可以在此基礎(chǔ)上做改進。
全局唯一ID設(shè)計
要求:1.全局唯一 2:粗略有序 3:可反解出庫編號
- 1bit + 39bit時間差 + 8bit機器號 + 8bit用戶編號(庫號) + 8bit自增序列
| 訂單號組成項 | 保留字段 | 毫秒級時間差 | 機器數(shù) | 用戶編號(表編號) | 自增序列 |
|---|---|---|---|---|---|
| 所占字節(jié)(單位bit) | 1 | 39 | 8 | 8 | 8 |
單機最大QPS: 256000 使用壽命: 17年
二、環(huán)境準(zhǔn)備
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、數(shù)據(jù)庫環(huán)境準(zhǔn)備
進入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
查看運行狀態(tài)
#主 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、建庫 & 導(dǎo)入分表
（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）依次導(dǎo)入建表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";
/**
* 分表數(shù) 256，一旦確定不可更改
*/
public static final int SHARDING_TABLE_NUM = 256;
/**
* 分庫數(shù), 不建議更改， 可以更改，但是需要DBA遷移數(shù)據(jù)
*/
public static final int SHARDING_DATABASE_NODE_NUM = 4;
}
3、yml 配置
4主4從數(shù)據(jù)庫配置， 這里僅測試默認使用root用戶密碼，生產(chǎn)環(huán)境不建議使用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). 根據(jù)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. 返回數(shù)據(jù)源名
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). 根據(jù)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. 計算步長 即單庫放得表數(shù)量
int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
//2. 計算出庫編號
long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1;
//3. 返回數(shù)據(jù)源名
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);
}
}
大功告成:
四、總結(jié)
本篇主要介紹Java版使用Mango框架實現(xiàn)Mysql分庫分表實戰(zhàn)，分庫分表中間件也可以使用類似于ShardingJDBC，或者自研。
以上分庫分表數(shù)量僅供演示參考，實際工作中分表數(shù)量、分庫數(shù)量、是根據(jù)公司實際業(yè)務(wù)數(shù)據(jù)增長速度, 高峰期QPS,物理機器配置等等因素計算。
到此這篇關(guān)于淺談訂單重構(gòu)之 MySQL 分庫分表實戰(zhàn)篇的文章就介紹到這了,更多相關(guān)MySQL 分庫分表內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信