您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 357人已围观

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

一、目标

本文将完成如下目标:

  • 分表数量: 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)139888

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

二、环境准备

1、基本信息

项版本备注
SpringBoot2.1.10.RELEASE
Mango1.6.16wiki地址:https://github.com/jfaster/mango
HikariCP3.2.0
Mysql5.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

三、配置&实践

1、pom文件  

org.jfastermango-spring-boot-starter2.0.1com.bytearchfast-cloud-id-generator${version}mysqlmysql-connector-java6.0.6

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 { @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 / ShardingStrategyConstan
                
                

                

                                            
上一篇：CentOS7安装MySQL 8.0.26的过程_Mysql_

                                                                
下一篇：MySQL中的回表和索引覆盖示例详解_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网