共计 11425 个字符,预计需要花费 29 分钟才能阅读完成。
一、背景
公布上篇文章浅谈订单重构之路之后,有很多小伙伴想晓得,分库分表具体是如何实现的。那么这篇文章具体介绍下,分库分表实战篇。
二、指标
-
本文将实现如下指标:
分表数量: 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 年
订单号生成规定阐明详见惟一 ID 生成器
三、环境筹备
1) 根本信息
项 | 版本 | 备注 |
---|---|---|
SpringBoot | 2.1.10.RELEASE | |
Mango | 1.6.16 | wiki 地址:https://github.com/jfaster/mango |
HikariCP | 3.2.0 | |
Mysql | 5.7 |
-
数据库环境筹备
舒适提醒:应用 docker-compose 疾速搭建了 4 主 4 从数据库集群,实现本地疾速一键部署(实现形式查看我公众号文章:<< 浅谈工作中罕用应用软件本地 docker 化,一键部署它不香吗?>>),生产环境个别由 DBA 同学搭建。
具体实现请移步查看: https://gitee.com/bytearch_ad…
- 建库 & 导入分表
- 在 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.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>
-
常量配置
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; }
-
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
-
分库分表策略
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); } }
-
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);
-
单元测试
@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); } }
功败垂成:
以上源码已开源至: https://gitee.com/bytearch_ad… 欢送点赞珍藏。
五、总结
本篇次要介绍 Java 版应用 Mango 框架实现 Mysql 分库分表实战,分库分表中间件也能够应用相似于 ShardingJDBC,或者自研。
以上分库分表数量仅供演示参考,理论工作中分表数量、分库数量、是依据公司理论业务数据增长速度, 高峰期 QPS 计算。
舒适提醒
欢送关注“浅谈架构”公众号,不定期分享原创文章。