A brief discussion on order reconstruction: MySQL sharding

1. Objectives
2. Environmental Preparation

1. Basic Information
2. Database environment preparation
3. Build database & import sub-tables

3. Configuration & Practice

1. pom file
2. Constant configuration
3. yml configuration
4. Database and table sharding strategy
5. Dao layer writing
6. Unit Testing

IV. Conclusion

1. Objectives

This article will accomplish the following goals:

Number of sub-tables: 256 Number of sub-databases: 4
Use user ID (user_id) as the database sharding key
Finally, test order creation, update, deletion, single order number query, and query list operations based on user_id.

Architecture diagram:

The table structure is as follows:

CREATE TABLE `order_XXX` (
  `order_id` bigint(20) unsigned NOT NULL,
  `user_id` int(11) DEFAULT '0' COMMENT 'Order id',
  `status` int(11) DEFAULT '0' COMMENT 'Order status',
  `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;

Note: 000<= XXX <= 255. This article focuses on the practice of sharding databases and tables. Only representative fields are retained. Other scenarios can be improved on this basis.

Globally unique ID design

Requirements: 1. Globally unique 2: Roughly ordered 3: Reversible outgoing number

1bit + 39bit time difference + 8bit machine number + 8bit user number (library number) + 8bit auto-increment sequence

Order number components	Reserved Fields	Millisecond time difference	Number of machines	User ID (table ID)	Auto-increment sequence
Bytes occupied (unit: bit)	1	39	8	8	8

Maximum QPS of a single machine: 256,000 Service life: 17 years

2. Environmental Preparation

1. Basic Information

item	Version	Remark
SpringBoot	2.1.10.RELEASE
Mango	1.6.16	Wiki address: https://github.com/jfaster/mango
Hikari CP	3.2.0
Mysql	5.7	Test using docker one-click build

2. Database environment preparation

Enter mysql:

#Main database mysql -h 172.30.1.21 -uroot -pbytearch

#From the library mysql -h 172.30.1.31 -uroot -pbytearch

Entering the container

# Main docker exec -it db_1_master /bin/bash

#From docker exec -it db_1_slave /bin/bash

Check the running status

#Main docker exec db_1_master sh -c 'mysql -u root -pbytearch -e "SHOW MASTER STATUS \G"'
#From docker exec db_1_slave sh -c 'mysql -u root -pbytearch -e "SHOW SLAVE STATUS \G"'

3. Build database & import sub-tables

(1) Create databases in the MySQL master instance

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)

(2) Import the SQL commands for creating tables in sequence:

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;

3. Configuration & Practice

1. pom file

     <!-- mango database and table sharding middleware --> 
            <dependency>
                <groupId>org.jfaster</groupId>
                <artifactId>mango-spring-boot-starter</artifactId>
                <version>2.0.1</version>
            </dependency>
         
             <!-- Distributed ID Generator -->
            <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. Constant configuration

package com.bytearch.fast.cloud.mysql.sharding.common;

/**
 * Common constants for database and table sharding strategies*/
public class ShardingStrategyConstant {
    /**
     * database logical name, the actual database name is order_db_XXX
     */
    public static final String LOGIC_ORDER_DATABASE_NAME = "order_db";
    /**
     * The number of sub-tables is 256, once confirmed, it cannot be changed*/
    public static final int SHARDING_TABLE_NUM = 256;

    /**
     * The number of sub-databases is not recommended to be changed. It can be changed, but the DBA needs to migrate the data*/
    public static final int SHARDING_DATABASE_NODE_NUM = 4;
}

3. yml configuration

4 master and 4 slave database configurations. Here we only test the default root user password. It is not recommended to use the root user in a production environment.

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. Database and table sharding strategy

1). Use order_id as shardKey to divide the database and table

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;

/**
 * Order number sub-library and sub-table strategy*/
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. Calculate step length int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
        //2. Calculate the library number long dbNo = Math.floorDiv(idEntity.getExtraId(), step) + 1;
        //3. Return the data source name 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());
        }
        // Based on the convention, the actual table name is logicTableName_XXX. If XXX is less than three digits, add 0.
        return String.format("%s_%03d", logicTableName, idEntity.getExtraId());
    }
}

2). Use user_id as shardKey to shard the database and table

package com.bytearch.fast.cloud.mysql.sharding.strategy;

import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
import org.jfaster.mango.sharding.ShardingStrategy;

/**
 *Specify the sharding KEY and database/table sharding strategy*/
public class UserIdShardingStrategy implements ShardingStrategy<Integer, Integer> {

    @Override
    public String getDataSourceFactoryName(Integer userId) {
        //1. Calculate the step length, i.e. the number of tables in a single database int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
        //2. Calculate the database number long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1;
        //3. Return the data source name return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo);
    }

    @Override
    public String getTargetTable(String logicTableName, Integer userId) {
        // Based on the convention, the actual table name is logicTableName_XXX. If XXX is less than three digits, add 0.
        return String.format("%s_%03d", logicTableName, userId % ShardingStrategyConstant.SHARDING_TABLE_NUM);
    }
}

5. Dao layer writing

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. Unit Testing

@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 {
            //Prevent verification errors caused by master-slave delay Thread.sleep(1000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        List<OrderEntity> orderListByUserId = orderPartitionByUserIdDao.getOrderListByUserId(userId);
        Assert.assertNotNull(orderListByUserId);
        Assert.assertTrue(orderListByUserId.size() == 5);
    }
}

You are done:

IV. Conclusion

This article mainly introduces the practical implementation of MySQL sharding using the Mango framework in the Java version. The sharding middleware can also use something similar to ShardingJDBC, or be self-developed.

The above number of sub-databases and sub-tables is for demonstration reference only. In actual work, the number of sub-tables and sub-databases is calculated based on the company's actual business data growth rate, peak QPS, physical machine configuration and other factors.

This concludes this article on the practical application of MySQL sharding in order reconstruction. For more information on MySQL sharding, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Getting Started Guide to MySQL Sharding
MySQL sharding details
Summary of MySQL's commonly used database and table sharding solutions
Mysql database sharding and table sharding completely collapsed
Several methods of primary key processing after Mysql database and table sharding
SpringBoot+MybatisPlus+Mysql+Sharding-JDBC sharding
Several ways to shard MySQL databases and tables

<<: Nodejs global variables and global objects knowledge points and usage details

>>: Solution to the problem that the text is on the lower left and cannot be resized when the textarea is laid out