一、背景

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

二、目标

本文将完成如下目标:

* 分表数量: 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. 基本信息

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;

四、配置&实践

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);     } }

大功告成:

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

五、总结

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

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