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

目录

• 一、目标
• 二、环境准备
• 1、基本信息
• 2、数据库环境准备
• 3、建库 & 导入分表
• 三、配置&实践
• 1、pom文件  
• 2、常量配置
• 3、yml 配置
• 4、分库分表策略
• 5、dao层编写
• 6、单元测试
• 四、总结

一、目标

本文将完成如下目标:

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

二、环境准备

1、基本信息

项	版本	备注
springboot	2.1.10.release
mango	1.6.16	wiki地址:https://github.com/jfaster/mango
hikaricp	3.2.0
mysql	5.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<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);
    }
}

大功告成:

四、总结

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

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

到此这篇关于浅谈订单重构之 mysql 分库分表实战篇的文章就介绍到这了,更多相关mysql 分库分表内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！