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

一、背景

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

二、指标

1. 本文将实现如下指标:

分表数量: 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自增序列

单机最大QPS: 256000
使用寿命: 17年
订单号生成规定阐明详见惟一ID生成器

三、环境筹备

1) 根本信息

2. 数据库环境筹备

   舒适提醒：应用docker-compose疾速搭建了4主4从数据库集群，实现本地疾速一键部署(实现形式查看我公众号文章:<<浅谈工作中罕用应用软件本地docker化，一键部署它不香吗？>>)，生产环境个别由DBA同学搭建。

   具体实现请移步查看: https://gitee.com/bytearch_ad...

3. 建库 & 导入分表

• 在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.sqlmysql -uroot -pbytearch -h172.30.1.23 order_db_3<fast-cloud-mysql-sharding/doc/sql/order_db_3.sqlmysql -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_ad... 欢送点赞珍藏。

五、总结

本篇次要介绍Java版应用Mango框架实现Mysql分库分表实战，分库分表中间件也能够应用相似于ShardingJDBC，或者自研。

以上分库分表数量仅供演示参考，理论工作中分表数量、分库数量、是依据公司理论业务数据增长速度, 高峰期QPS计算。

舒适提醒

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