1, Background

After the release of the last article on the road of order reconstruction, many small partners want to know how to realize the sub database and sub table. Then this article specifically introduces the actual combat of sub database and sub table.

2, Target

1. This paper will accomplish the following objectives:

Number of sub tables: 256 number of sub libraries: 4

Take the user ID(user_id) as the database partition Key

Finally, test the order creation, update, deletion and order number query according to user_id query list operation.

The structure of the table 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 paper focuses on the practice of sub database and sub table, and only representative fields are retained. Other scenarios can be improved on this basis.

Globally unique ID design

Requirements: 1 Globally unique 2: rough order 3: cancellable issue number

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

Maximum QPS of single machine: 256000
Service life: 17 years
See unique ID generator for details of order number generation rules

3, Environmental preparation

1) Basic information

1. Prepare database environment

   Warm reminder: using docker-compose to quickly build 4 main 4 from the database cluster, to achieve a quick deployment of the local key (to achieve the official account of my public number: "on the work of commonly used software local docker, a key deployment is not fragrant?"), the production environment is generally built by DBA students.

   Please check the specific implementation step by step: https://gitee.com/bytearch_ad...

1. Create database & import sub table

• Create databases 172.30.1.21 (order_db_1), 172.30.1.22 (order_db_2), 172.30.1.23 (order_db_3) and 172.30.1.24 (order_db_4) on the mysql master instance

• The SQL command for importing and creating tables by tables is

  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 
  

4, Configuration & Practice

1) pom file

            <!-- mango Database and table 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>

1. constant configuration

   package com.bytearch.fast.cloud.mysql.sharding.common;
   
   /**
    * Common constants of database and table splitting strategy
    */
   public class ShardingStrategyConstant {
       /**
        * database Logical name, and the real library name is order_db_XXX
        */
       public static final String LOGIC_ORDER_DATABASE_NAME = "order_db";
       /**
        * The number of sub tables is 256, which cannot be changed once it is determined
        */
       public static final int SHARDING_TABLE_NUM = 256;
   
       /**
        * The number of sub databases is not recommended to be changed. It can be changed, but DBA migration data is required
        */
       public static final int SHARDING_DATABASE_NODE_NUM = 4;
   }

1. yml configuration

   4. Master and slave database configuration. Only the default password of root user is tested here. Root user is not recommended for 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

2. Sub database and sub table strategy

   1). According to order_id is shardKey sub database and sub table policy

   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;
   
   /**
    * Strategy of order number warehouse and table
    */
   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. Calculation step
           int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
           //2. Calculate the issue number
           long dbNo = Math.floorDiv(idEntity.getExtraId(), step) + 1;
           //3. Return 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 real table name is logictablename_ 30. If XXX is less than three digits, make up 0
           return String.format("%s_%03d", logicTableName, idEntity.getExtraId());
       }
   }

   2). According to user_id is shardKey sub database and sub table policy

   package com.bytearch.fast.cloud.mysql.sharding.strategy;
   
   import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
   import org.jfaster.mango.sharding.ShardingStrategy;
   
   /**
    * Specify partition KEY, database and table policies
    */
   public class UserIdShardingStrategy implements ShardingStrategy<Integer, Integer> {
   
       @Override
       public String getDataSourceFactoryName(Integer userId) {
           //1. The calculation step is the number of tables placed in a single warehouse
           int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
           //2. Calculate the issue number
           long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1;
           //3. Return 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 real table name is logictablename_ 30. If XXX is less than three digits, make up 0
           return String.format("%s_%03d", logicTableName, userId % ShardingStrategyConstant.SHARDING_TABLE_NUM);
       }
   }

3. 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);

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

be accomplished:

The above source code has been opened to: https://gitee.com/bytearch_ad... Welcome to like collection.

5, Summary

This article mainly introduces the actual combat of using Mango framework to realize Mysql database and table in Java version. The database and table middleware can also be similar to ShardingJDBC or self-developed.

The above number of sub databases and sub tables is only for demonstration reference. In actual work, the number of sub tables and sub databases is calculated according to the growth rate of the company's actual business data and QPS in peak periods.

reminder

Welcome to pay attention to "official account" and share original articles.