关于分库分表:浅谈Mysql分库分表之实战篇

5次阅读

共计 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, 本文重点在于分库分表实际, 只保留具备代表性字段，其它场景能够在此基础上做改良。

要求: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 计算。

欢送关注“浅谈架构”公众号，不定期分享原创文章。

正文完

分库分表

发表至：分库分表

2022-02-12

0

关于分库分表:分而治之浅谈分库分表及实践之路-京东云技术团队

关于分库分表:手把手教学分库分表平滑切换

关于分库分表:好好的系统为什么要分库分表

关于分库分表:分库分表是怎么做的

关于重构:浅谈订单系统重构之路线上真实案例分享

关于分库分表:浅谈Mysql分库分表之实战篇

一、背景

二、指标

本文将实现如下指标:

全局惟一 ID 设计

三、环境筹备

四、配置 & 实际

五、总结

舒适提醒

站内搜索