Spring boot integrates sharding JDBC to realize annual database and monthly table

Spring boot integrates sharding JDBC to realize annual database and monthly table (implementation and brief analysis)

1. Preface:

This scheme is applicable to the horizontal segmentation of data with high time dependence. If you just meet the requirements, you can take a look at this article. If you do not meet the requirements, you can also take a look. After all, sharding JDBC has a high degree of implementation of the scheme of sub database and sub table, and you can perform data segmentation according to your actual situation. If you have a common understanding of the following concepts, you can directly jump to the code link

2. Concept of warehouse and table

Before implementation, we need to know what is sub database and sub table, why we do it, and what are the advantages and disadvantages of doing so?

Due to the inherent characteristics of relational database, when the amount of data in a single table reaches a certain volume, there will be a performance bottleneck. As for the specific amount, this article does not do research and has many statements. In the later stage, if there is time, it can be tested. In order to solve the performance bottleneck problem, the pioneers in the industry put forward the idea of dividing and ruling the data to segment the data according to the actual situation, To optimize the database performance, the main segmentation methods are summarized by industry pioneers, which can also be used for reference https://www.cnblogs.com/buddy-yuan/p/12089178.html :

1. Vertical split

1) vertical sub database: vertical sub database is actually a simple logical division, which divides a single database according to business to achieve the effect of a special database and a special table. For example, the system can be divided into configuration database, historical database, user database

2) vertical split table: it is more suitable for tables with many fields, and some fields are not used for all requirements. Operate a table in the database and store some field information in the table into a new table. The split word information is not stored in the original table. For example, the unit information table can be divided into unit basic information table and unit detail information table

Vertical splitting has the following advantages:

  • It follows the business for segmentation, which is similar to the recently popular concept of microservice, which is convenient for management and expansion after decoupling.
  • In the scenario of high concurrency, vertical splitting uses the CPU, I/O and memory of multiple servers, which can improve the performance. At the same time, it also improves the number of single database connections and some resource restrictions.
  • It can realize the separation of cold and hot data.

Disadvantages of vertical splitting:

  • Some business tables cannot be join ed, and the application layer needs great transformation, which can only be realized by aggregation. It increases the difficulty of development.
  • When the amount of table data in a single database increases, it still has not been effectively solved.
  • Distributed transactions are also a challenge.
2. Horizontal split

For example, when the performance of MySQL reaches a certain level, there will be a certain watershed on a single meter. At this time, it is found that there is no way to split according to the business rules, which will lead to a bottleneck in the read and write performance of a single database. At this point, you can only split horizontally.

1) sub table in the Library: suppose that when our c_ vib_ When point expresses 50 million rows of records, it will greatly affect the reading and writing efficiency of the database. What should I do? We can consider the rank partition according to the id of the order number, that is, put the order number of 1-10 million in the order1 table, put the order number of 10-20 million in the order2 table, and so on. Each table stores 10 million data, or divide the table according to the insertion time. One table a year can also control the number of single tables, but the data of the table is still stored in a database, using the CPU, IO and memory of the host. The number of connections to a single database is also limited. It does not completely reduce the pressure of the system

2) sub database and sub table: Based on the sub table in the database, move the sub table to different hosts and databases. It can make full use of CPU, memory and IO resources of other hosts. And after the database is divided, the connection number limit of a single database is no longer a bottleneck. However, if you perform a scan without the slice key, you need to check it on each library. Just now, we divided five databases according to id, but when we query the condition of name='AAA 'without id field, it doesn't know which partition to query, then five connections will be created, and then each database will be retrieved. This broadcast query will increase the number of connections. Because it needs to create connections on each library. If it is a highly concurrent system, if this broadcast query is executed, the thread of the system will give an alarm soon, so the database operation of developers will be more strict.

Advantages of horizontal split:

  • Horizontal expansion can expand wirelessly. There is no case that a library or a table is too large.
  • It can better deal with high concurrency and disperse hot data at the same time.
  • The changes on the application side are minor and do not need to be split according to the business.

Disadvantages of horizontal splitting:

  • Routing is a problem. It needs to add a layer of routing calculation. As mentioned above, query without fragment key will produce broadcast SQL.
  • The performance of cross library join is poor.
  • We need to deal with the consistency of distributed transactions.

In my opinion, there are advantages and disadvantages of sub database and sub table. Before using sub database and sub table, we should give priority to cache technology, read-write separation, SQL optimization, index use and other methods. Sub database and sub table should be taken as the final scheme, and it is best to make forward-looking consideration at the early stage of the project. If you determine that the amount of data of the project will indeed reach a volume that will greatly affect the performance in a certain period of time in the future, Then you can consider making sub database and sub table. Of course, you can not make sub database and sub table for the time being, and then do it when the data volume comes up

3. Sharding JDBC concept

The emergence of a technology must be driven by requirements. Sharding JDBC is the product of sub database and sub table. Of course, it is not the only implementation, but this article uses sharding JDBC as the technical support to realize sub database and sub table. The following can understand its related concepts, and it is best to know why

1. Introduction

Sharding JDBC is positioned as a lightweight Java framework and provides additional services in the JDBC layer of Java. It uses the client to connect directly to the database and provides services in the form of jar package without additional deployment and dependence. It can be understood as an enhanced version of JDBC Driver and is fully compatible with JDBC and various ORM frameworks.

  • It is applicable to any ORM framework based on JDBC, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.

  • Support any third-party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.

  • Support any database that implements JDBC specification. At present, it supports MySQL, Oracle, SQLServer, PostgreSQL and any database that complies with the SQL92 standard.

    2.SQL

1) logic table

The same logic and data structure of a horizontally split database (table). For example, the chronology of order is divided into 12 tables according to time, which are order_01 to order_12, their logical table is called order

2) true table

A physical table that exists in a fragmented database. That is, order in the previous example_ 01 to order_ twelve

3) data node

The smallest unit of data fragmentation. It consists of data source name and data table, for example: datasource order_ 01

4) binding table

It refers to the main table and sub table with consistent fragmentation rules. For example: t_order table and t_order_item table, all according to order_id sharding, the two tables are bound to each other. The Cartesian product association will not appear in the multi table Association query between bound tables, and the efficiency of association query will be greatly improved. For example, if the SQL is:

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

When the binding table relationship is not configured, the sharding key order is assumed_ ID route the value 10 to slice 0 and the value 11 to slice 1, then the SQL after routing should be 4, which are presented as Cartesian Products:

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

After configuring the binding table relationship, the route SQL should be 2:

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

Where t_order is on the far left side of FROM, and ShardingSphere will use it as the main table of the whole binding table. All routing calculations will only use the policy of the main table, then t_order_ The fragment calculation of the item table will use t_ Condition of order. Therefore, the partition keys between bound tables should be exactly the same.

5) broadcast table

It refers to the table existing in all fragment data sources. The table structure and the data in the table are completely consistent in each database. It is applicable to scenarios where the amount of data is small and needs to be associated with tables with massive data, such as dictionary tables.

2. Slicing

1) slice key

The database field used for fragmentation is the key field to split the database (table) horizontally. For example: if the mantissa of the primary key in the order table is taken as modular fragment, the primary key of the order table is a fragment field. If there is no fragment field in SQL, full routing will be executed and the performance is poor. In addition to the support of single sharding field, ShardingSphere also supports sharding according to multiple fields.

2) slicing algorithm

The data is segmented through the segmentation algorithm, and the segmentation through =, > =, < =, >, <, BETWEEN and IN is supported. The slicing algorithm needs to be implemented by the application developer, and the flexibility is very high.

At present, four fragmentation algorithms are provided. Because the sharding algorithm is closely related to the business implementation, it does not provide a built-in sharding algorithm, but extracts various scenarios through the sharding strategy, provides a higher level of abstraction, and provides an interface for application developers to implement the sharding algorithm by themselves.

  • Exact slicing algorithm

    The corresponding PreciseShardingAlgorithm is used to deal with the scene where = and IN are sliced using a single key as the slicing key. It needs to be used with StandardShardingStrategy.

  • Range slicing algorithm

    Corresponding to RangeShardingAlgorithm, it is used to process the scene of BETWEEN AND, >, <, > =, < = segmentation using a single key as the segmentation key. It needs to be used with StandardShardingStrategy.

  • Composite slicing algorithm

    The corresponding complexkeys shardingalgorithm is used to deal with the scenario of using multiple keys as sharding keys. The logic of multiple sharding keys is complex and needs to be handled by the application developer. It needs to be used in conjunction with the ComplexShardingStrategy.

  • Hint slicing algorithm

    The corresponding HintShardingAlgorithm is used to process scenes that use Hint row slicing. It needs to be used with HintShardingStrategy.

    3) Partition strategy

    It includes partition key and partition algorithm. Due to the independence of partition algorithm, it is separated independently. What can be used for sharding operation is sharding key + sharding algorithm, that is, sharding strategy. At present, five fragmentation strategies are provided.

    • Standard fragmentation strategy

      Corresponding to StandardShardingStrategy. It provides fragment operation support for =, >, <, > =, < =, IN and BETWEEN AND IN SQL statements. Standard shardingstrategy only supports single sharding key and provides two sharding algorithms: precision shardingalgorithm and range shardingalgorithm. Precisesharingalgorithm is required and is used to process the tiles of = and IN. RangeShardingAlgorithm is optional. It is used to process BETWEEN AND, >, <, > =, < = fragmentation. If RangeShardingAlgorithm is not configured, BETWEEN AND IN SQL will be processed according to the full database route.

    • Composite fragmentation strategy

      Corresponds to the ComplexShardingStrategy. Composite sharding strategy. It provides fragment operation support for =, >, <, > =, < =, in and BETWEEN AND in SQL statements. Complex shardingstrategy supports multiple sharding keys. Due to the complex relationship between multiple sharding keys, it does not carry out too much encapsulation, but directly transmits the sharding key value combination and sharding operator to the sharding algorithm, which is completely realized by the application developer with maximum flexibility.

    • Row expression slicing strategy

      Corresponding to InlineShardingStrategy. Groovy's expression is used to support the fragment operation of = and IN in SQL statements, and only the single fragment key is supported. The simple slicing algorithm can be used through simple configuration, so as to avoid cumbersome Java code development, such as t_user_$-> {u_id% 8} indicates t_user table according to u_ ID module 8 is divided into 8 tables, and the table name is t_user_0 to t_user_7.

    • Hint fragmentation strategy

      Corresponding to HintShardingStrategy. The strategy of sharding by Hint specifying the shard value instead of extracting the shard value from SQL.

    • Non fragmentation strategy

      Corresponding to NoneShardingStrategy. No fragmentation strategy.

4. Code and dry

This case is the spring boot integration of mybatis + sharding JDBC + Druid, which can be replaced according to the actual situation in the project, and sharding supports it

1) Introduce dependency
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dashuai</groupId>
    <artifactId>sharding-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- unit testing  -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--  JDBC Start dependence  -->
        <!--<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                &lt;!&ndash; Eliminate HikariCP Connection pool &ndash;&gt;
                <exclusion>
                    <groupId>com.zaxxer</groupId>
                    <artifactId>HikariCP</artifactId>
                </exclusion>
            </exclusions>
        </dependency>-->
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- sharding-jdbc -->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0.M1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.1.0</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
2) Configuration file
server:
  port: 9090
  servlet:
    context-path: /sharding
spring:
  application:
    name: sharding-demo
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.dashuai.pojo
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
3) Startup class
package com.dashuai;

import io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication(exclude = {SpringBootConfiguration.class})
@MapperScan("com.dashuai.mapper")
public class ShardingApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingApplication.class, args);
    }
}

There is a pit here: if you use java to configure sharding, you need to exclude the automatic configuration of sharding. Note that the cited package is Io shardingsphere. shardingjdbc. Spring boot. SpringbootConfiguration, otherwise it will report that the data source is empty or sqlSessionFactory is empty. The main reason is that when springboot is integrated with shardingjdbc, the class SpringbootConfiguration (under sharding JDBC package) will automatically read the configuration from the configuration file by default. If it cannot be read, the data source configuration will not succeed, because here we use the configuration class for configuration, not the configuration file, So if he can't read the configuration, he will report an error. Using java configuration has certain advantages. Please look down

4) Configuration class
package com.dashuai.config;

import com.dashuai.utils.datasource.DataSourceUtil;
import com.dashuai.utils.shardingarithmetic.PreciseDatabaseShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.PreciseTableShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.RangeDatabaseShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.RangeTableShardingAlgorithm;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.context.annotation.Bean;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


@SpringBootConfiguration
public class ShardingConfig {


    /**
     * Logical table name
     */
    private final String LOGICAL_TABLE = "t_order";

    /**
     * Slice key
     */
    private final String DATABASE_SHARDING_COLUMN = "save_time_com";


    @Bean
    DataSource getShardingDataSource() throws SQLException {

        // Partition rule configuration object
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // Rule configuration
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        // Database segmentation algorithm (precision and scope), by year
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(DATABASE_SHARDING_COLUMN, new PreciseDatabaseShardingAlgorithm(), new RangeDatabaseShardingAlgorithm()));
        // Table segmentation algorithm (precision and range), table by month
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(DATABASE_SHARDING_COLUMN, new PreciseTableShardingAlgorithm(), new RangeTableShardingAlgorithm()));
        // Default library. If there are broadcast tables and binding tables, they can also be configured here
        shardingRuleConfig.setDefaultDataSourceName("default_dataSource");
        // Enable log printing
        final Properties properties = new Properties();
        properties.setProperty("sql.show", "true");

        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
    }


    TableRuleConfiguration getOrderTableRuleConfiguration() {
        
        // It is tentatively scheduled for two years. For this expression, you can see the official document https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/other-features/inline-expression/
        String inLineExpressionStr = "dataSource_2020." + LOGICAL_TABLE + "_20200${1..9}" + "," + "dataSource_2021." + LOGICAL_TABLE + "_20210${1..9}" + "," +
                "dataSource_2020." + LOGICAL_TABLE + "_20201${0..2}" + "," + "dataSource_2021." + LOGICAL_TABLE + "_20211${0..2}";
        final TableRuleConfiguration ruleConfiguration = new TableRuleConfiguration("t_order", inLineExpressionStr);
        // Set primary key generation policy
        ruleConfiguration.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
        return ruleConfiguration;
    }

    private KeyGeneratorConfiguration getKeyGeneratorConfiguration() {

        return new KeyGeneratorConfiguration("SNOWFLAKE", "id");
    }


    private Map<String, DataSource> createDataSourceMap() {

        // key is the name of the data source, which is obtained later by the fragmentation algorithm, and value is the specific data source
        final HashMap<String, DataSource> shardingDataSourceMap = new HashMap<>();
        shardingDataSourceMap.put("default_dataSource", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/sharding?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
                "root",
                "xhtest"));
        shardingDataSourceMap.put("dataSource_2020", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/order_2020?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
                "root",
                "xhtest"));
        shardingDataSourceMap.put("dataSource_2021", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/order_2021?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
                "root",
                "xhtest"));

        return shardingDataSourceMap;
    }

}
5) Tool class
  • Create data source tool class
package com.dashuai.utils.datasource;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;


public class DataSourceUtil {

    public static DataSource createDataSource(final String driverClass, final String url, String userName, String passWord) {
        final DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(driverClass);
        result.setUrl(url);
        result.setUsername(userName);
        result.setPassword(passWord);
        result.setInitialSize(5);
        result.setMinIdle(5);
        result.setMaxActive(20);
        result.setMaxWait(60000);
        result.setTimeBetweenEvictionRunsMillis(60000);
        result.setMinEvictableIdleTimeMillis(30000);

        return result;
    }

}
  • Time formatting tool class
package com.dashuai.utils.date;

import java.text.SimpleDateFormat;
import java.util.Date;


public class DateUtil {


    private static final SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy");

    private static final SimpleDateFormat monthFormat = new SimpleDateFormat("MM");

    private static final SimpleDateFormat yearJoinMonthFormat = new SimpleDateFormat("yyyyMM");


    public static String getYearByMillisecond(long millisecond) {

        return yearFormat.format(new Date(millisecond));
    }


    public static String getMonthByMillisecond(long millisecond) {

        return monthFormat.format(new Date(millisecond));
    }


    public static String getYearJoinMonthByMillisecond(long millisecond) {

        return yearJoinMonthFormat.format(new Date(millisecond));
    }

}
6) Slicing algorithm

There are many slicing algorithms, and this case only uses the standard slicing algorithm

  • Accurate segmentation algorithm of database
package com.dashuai.utils.shardingarithmetic;

import com.dashuai.utils.date.DateUtil;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;


public class PreciseDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {


    /**
     * Exact slicing algorithm
     *
     * @param availableTargetNames List of all configured Libraries
     * @param shardingValue        Slice value, i.e. save_ time_ Value of COM
     * @return                     Results of matched Libraries
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {

        Long value = shardingValue.getValue();
        // Library suffix
        String yearStr = DateUtil.getYearByMillisecond(value);

        if (value <= 0) throw new UnsupportedOperationException("preciseShardingValue is null");

        for (String availableTargetName : availableTargetNames) {
            if (availableTargetName.endsWith(yearStr)) {
                return availableTargetName;
            }
        }
        throw new UnsupportedOperationException();
    }

}
  • Database range slicing algorithm
package com.dashuai.utils.shardingarithmetic;

import com.dashuai.utils.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.ArrayList;
import java.util.Collection;


public class RangeDatabaseShardingAlgorithm implements RangeShardingAlgorithm<Long> {


    /**
     * Range slicing algorithm
     *
     * @param availableTargetNames List of all configured Libraries
     * @param rangeShardingValue   Slice value, i.e. save_time_com, the range slicing algorithm must provide the start time and end time
     * @return                     Results of matched Libraries
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) {

        final ArrayList<String> result = new ArrayList<>();
        Range<Long> range = rangeShardingValue.getValueRange();
        long startMillisecond = range.lowerEndpoint();
        long endMillisecond = range.upperEndpoint();

        // Start year and end year
        int startYear = Integer.parseInt(DateUtil.getYearByMillisecond(startMillisecond));
        int endYear = Integer.parseInt(DateUtil.getYearByMillisecond(endMillisecond));

        return startYear == endYear ? theSameYear(String.valueOf(startYear), availableTargetNames, result) : differentYear(startYear, endYear, availableTargetNames, result);
    }

    // In the same year, only one library is required
    private Collection<String> theSameYear(String startTime, Collection<String> availableTargetNames, ArrayList<String> result) {

        for (String availableTargetName : availableTargetNames) {
            if (availableTargetName.endsWith(startTime)) result.add(availableTargetName);
        }
        return result;
    }


    // straddle old and new years
    private Collection<String> differentYear(int startYear, int endYear, Collection<String> availableTargetNames, ArrayList<String> result) {

        for (String availableTargetName : availableTargetNames) {
            for (int i = startYear; i <= endYear; i++) {
                if (availableTargetName.endsWith(String.valueOf(i))) result.add(availableTargetName);
            }
        }
        return result;
    }

}

  • Table exact partition algorithm
package com.dashuai.utils.shardingarithmetic;

import com.dashuai.utils.date.DateUtil;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;


public class PreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {


    /**
     * Exact slicing algorithm
     *
     * @param availableTargetNames List of all configured tables, which represents all tables matched to the library
     * @param shardingValue        Slice value, i.e. dau_ Value of ID
     * @return                     Results of matched tables
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {

        long value = shardingValue.getValue();

        if (value <= 0) throw new UnsupportedOperationException("preciseShardingValue is null");

        final String yearJoinMonthStr = DateUtil.getYearJoinMonthByMillisecond(value);
        for (String availableTargetName : availableTargetNames) {
            if (availableTargetName.endsWith(yearJoinMonthStr)) {
                return availableTargetName;
            }
        }
        throw new UnsupportedOperationException();
    }

}
  • Table range slicing algorithm
package com.dashuai.utils.shardingarithmetic;

import com.dashuai.utils.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.ArrayList;
import java.util.Collection;


public class RangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {


    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) {

        final ArrayList<String> result = new ArrayList<>();
        final Range<Long> range = rangeShardingValue.getValueRange();
        long startMillisecond = range.lowerEndpoint();
        long endMillisecond = range.upperEndpoint();

        // Start year and end year
        int startYear = Integer.parseInt(DateUtil.getYearByMillisecond(startMillisecond));
        int endYear = Integer.parseInt(DateUtil.getYearByMillisecond(endMillisecond));
        // Start month and end month
        int startMonth = Integer.parseInt(DateUtil.getMonthByMillisecond(startMillisecond));
        int endMonth = Integer.parseInt(DateUtil.getMonthByMillisecond(endMillisecond));

        int startYearJoinMonth = Integer.parseInt(DateUtil.getYearJoinMonthByMillisecond(startMillisecond));
        int endYearJoinMonth = Integer.parseInt(DateUtil.getYearJoinMonthByMillisecond(endMillisecond));
        return startYear == endYear ? theSameYear(startMonth, endMonth, availableTargetNames, result)
                : differentYear(startYear, endYear, startMonth, endMonth, startYearJoinMonth, endYearJoinMonth, availableTargetNames, result);
    }


    // Same year, but may be different months
    private Collection<String> theSameYear(int startMonth, int endMonth, Collection<String> availableTargetNames, ArrayList<String> result) {

        return startMonth == endMonth ? theSameMonth(startMonth, availableTargetNames, result) : differentMonth(startMonth, endMonth, availableTargetNames, result);
    }

    // Same year and same month
    private Collection<String> theSameMonth(int startMonth, Collection<String> availableTargetNames, ArrayList<String> result) {

        String startMonthStr = String.valueOf(startMonth);
        if (startMonthStr.length() == 1) startMonthStr = "0" + startMonthStr;
        for (String availableTargetName : availableTargetNames) {
            if (availableTargetName.endsWith(startMonthStr)) result.add(availableTargetName);
        }
        return result;
    }

    // Different months of the same year
    private Collection<String> differentMonth(int startMonth, int endMonth, Collection<String> availableTargetNames, ArrayList<String> result) {

        for (String availableTargetName : availableTargetNames) {
            for (int i = startMonth; i <= endMonth; i++) {
                String monthStr = String.valueOf(i);
                if (monthStr.length() == 1) monthStr = "0" + monthStr;
                if (availableTargetName.endsWith(monthStr)) result.add(availableTargetName);
            }
        }
        return result;
    }


    // Different years, cross years, at least two months, need to consider the situation of more than two years
    private Collection<String> differentYear(int startYear, int endYear, int startMonth, int endMonth, int startYearJoinMonth, int endYearJoinMonth, Collection<String> availableTargetNames, ArrayList<String> result) {

        return endYear - startYear == 1 ? twoYears(startYear, endYear, startMonth, endMonth, startYearJoinMonth, endYearJoinMonth, availableTargetNames, result) : moreThanTwoYears(startYear, endYear, startMonth, endMonth, availableTargetNames, result);
    }


    // two years
    private Collection<String> twoYears(int startYear, int endYear, int startMonth, int endMonth, int startYearJoinMonth, int endYearJoinMonth, Collection<String> availableTargetNames, ArrayList<String> result) {

        int endCondition;
        endCondition = Integer.parseInt(startYear + "12");
        for (int i = startYearJoinMonth; i <= endCondition; i++) {
            for (String availableTargetName : availableTargetNames) {
                // If there are multiple databases, this algorithm sharding will match twice, which needs to be judged by year and month. If only month is used, there is a problem
                if (availableTargetName.endsWith(String.valueOf(i))) result.add(availableTargetName);
            }
        }

        endCondition = Integer.parseInt(endYear + "01");
        for (int i = endYearJoinMonth; i >= endCondition; i--) {
            for (String availableTargetName : availableTargetNames) {
                if (availableTargetName.endsWith(String.valueOf(i))) result.add(availableTargetName);
            }
        }
        return result;
    }


    // For more than two years, if there is a large amount of data, it is not recommended to span too many databases
    private Collection<String> moreThanTwoYears(int startYear, int endYear, int startMonth, int endMonth, Collection<String> availableTargetNames, ArrayList<String> result) {
        return null;
    }

}

7) Entity

package com.dashuai.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;


@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {

    private long id;

    private Integer aId;

    private Integer bId;

    private Integer cId;

    private Integer dId;

    private Integer eId;

    private Date saveTime;

    private Long saveTimeCom;

    private Integer param1;

    private Integer param2;

    private Integer param3;

    private Integer param4;

    private Integer param5;

    private Integer param6;

    private Float param7;

    private Float param8;

    private Float param9;

    private Float param10;

    private Float param11;

    private Float param12;

    private Float param13;

    private Float param14;

    private Float param15;

    private Float param16;

    private Float param17;

    private Float param18;

    private Float param19;

    public Order(int aId, int bId, int cId, int dId, int eId, Date saveTime, long saveTimeCom, int param1, int param2, int param3, int param4, int param5, int param6, float param7, float param8, float param9, float param10, float param11, float param12, float param13, float param14, float param15, float param16, float param17, float param18, float param19) {
        this.aId = aId;
        this.bId = bId;
        this.cId = cId;
        this.dId = dId;
        this.eId = eId;
        this.saveTime = saveTime;
        this.saveTimeCom = saveTimeCom;
        this.param1 = param1;
        this.param2 = param2;
        this.param3 = param3;
        this.param4 = param4;
        this.param5 = param6;
        this.param6 = param6;
        this.param7 = param7;
        this.param8 = param8;
        this.param9 = param9;
        this.param10 = param10;
        this.param11 = param11;
        this.param12 = param12;
        this.param13 = param13;
        this.param14 = param14;
        this.param15 = param15;
        this.param16 = param16;
        this.param17 = param17;
        this.param18 = param18;
        this.param19 = param19;
    }
}
7)controller

If it's just for testing, the controller and service can not be used, but I wrote it at the beginning, so post it

package com.dashuai.controller;

import com.dashuai.pojo.Order;
import com.dashuai.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;


@RestController
@RequestMapping("/order")
public class OrderController {


    @Autowired
    private OrderService orderService;

    @GetMapping("/{id}")
    public Order fingById(@PathVariable("id") Long orderId) {
        Order order = orderService.findById(orderId);
        return order;
    }

    @GetMapping("/fingBySaveTimeCom/{saveTimeCom}")
    public Order fingBySaveTimeCom(@PathVariable("saveTimeCom") Long saveTimeCom) {
        Order order = orderService.findBySaveTimeCom(saveTimeCom);
        return order;
    }

    @GetMapping("/createTable")
    public String createTable() {
        try {
            orderService.createTable();
        } catch (Exception e) {
            e.printStackTrace();
            return "Failed to create table";
        }
        return "Table creation succeeded";
    }

}
8)service
package com.dashuai.service;

import com.dashuai.pojo.Order;


public interface OrderService {
    Order findById(Long orderId);

    Order findBySaveTimeCom(Long saveTimeCom);

    void createTable();

}
package com.dashuai.service.impl;

import com.dashuai.mapper.OrderMapper;
import com.dashuai.pojo.Order;
import com.dashuai.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


@Service
public class OrderServiceImpl implements OrderService {


    @Autowired
    private OrderMapper orderMapper;

    @Override
    public Order findById(Long orderId) {

        return orderMapper.findById(orderId);
    }

    @Override
    public Order findBySaveTimeCom(Long saveTimeCom) {

        Order order = orderMapper.findBySaveTimeCom(saveTimeCom);
        return order;
    }

    @Override
    public void createTable() {
        orderMapper.createTable();
    }

}
9)mapper
package com.dashuai.mapper;

import com.dashuai.pojo.Order;
import org.apache.ibatis.annotations.Param;

import java.util.ArrayList;
import java.util.List;


public interface OrderMapper {

    Order findById(@Param("id") Long orderId);

    Order findBySaveTimeCom(@Param("saveTimeCom") Long saveTimeCom);

    void createTable();

    void insert(Order order);

    void insertBatch(@Param("orders") ArrayList<Order> orders);

    List<Order> findByOrderBytemporalInterval(@Param("startTime") long startTime, @Param("endTime") long endTime);
}

xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dashuai.mapper.OrderMapper">

    <!-- General query mapping results -->
    <resultMap id="BaseResultMap" type="com.dashuai.pojo.Order">
        <id column="id" property="id" />
        <result column="a_id" property="aId" />
        <result column="b_id" property="bId" />
        <result column="c_id" property="cId" />
        <result column="d_id" property="dId" />
        <result column="e_id" property="eId" />
        <result column="save_time" property="saveTime" />
        <result column="save_time_com" property="saveTimeCom" />
        <result column="param1" property="param1" />
        <result column="param2" property="param2" />
        <result column="param3" property="param3" />
        <result column="param4" property="param4" />
        <result column="param5" property="param5" />
        <result column="param6" property="param6" />
        <result column="param7" property="param7" />
        <result column="param8" property="param8" />
        <result column="param9" property="param9" />
        <result column="param10" property="param10" />
        <result column="param11" property="param11" />
        <result column="param12" property="param12" />
        <result column="param13" property="param13" />
        <result column="param14" property="param14" />
        <result column="param15" property="param15" />
        <result column="param16" property="param16" />
        <result column="param17" property="param17" />
        <result column="param18" property="param18" />
        <result column="param19" property="param19" />
    </resultMap>

    <insert id="insert">
        INSERT INTO t_order(`a_id`, `b_id`, `c_id`, `d_id`, `e_id`, `save_time`, `save_time_com`, `param1`, `param2`, `param3`, `param4`, `param5`, `param6`, `param7`, `param8`, `param9`, `param10`, `param11`, `param12`, `param13`, `param14`, `param15`, `param16`, `param17`, `param18`, `param19`)
            VALUES
            (#{aId}, #{bId}, #{cId}, #{dId}, #{eId}, #{saveTime}, #{saveTimeCom}, #{param1}, #{param2}, #{param3}, #{param4}, #{param5}, #{param6}, #{param7}, #{param8}, #{param9}, #{param10}, #{param11}, #{param12}, #{param13}, #{param14}, #{param15}, #{param16}, #{param17}, #{param18}, #{param19})
    </insert>

    <insert id="insertBatch">
        INSERT INTO t_order(`a_id`, `b_id`, `c_id`, `d_id`, `e_id`, `save_time`, `save_time_com`, `param1`, `param2`, `param3`, `param4`, `param5`, `param6`, `param7`, `param8`, `param9`, `param10`, `param11`, `param12`, `param13`, `param14`, `param15`, `param16`, `param17`, `param18`, `param19`)
            VALUES
            <foreach collection="orders" item="order" separator=",">
                (#{order.aId}, #{order.bId}, #{order.cId}, #{order.dId}, #{order.eId}, #{order.saveTime}, #{order.saveTimeCom}, #{order.param1}, #{order.param2}, #{order.param3}, #{order.param4}, #{order.param5}, #{order.param6}, #{order.param7}, #{order.param8}, #{order.param9}, #{order.param10}, #{order.param11}, #{order.param12}, #{order.param13}, #{order.param14}, #{order.param15}, #{order.param16}, #{order.param17}, #{order.param18}, #{order.param19})
            </foreach>
    </insert>


    <update id="createTable">
        CREATE TABLE `t_order`  (
          `id` bigint(20) NOT NULL COMMENT 'Primary key id',
          `a_id` int(11) NULL DEFAULT NULL COMMENT 'Affiliated buildings id',
          `b_id` int(11) NULL DEFAULT NULL COMMENT 'Data collector id',
          `c_id` tinyint(3) NULL DEFAULT NULL COMMENT 'Card slot id',
          `d_id` tinyint(3) NULL DEFAULT NULL COMMENT 'passageway id',
          `e_id` tinyint(3) NULL DEFAULT NULL COMMENT 'Measuring point id',
          `save_time` datetime(0) NULL DEFAULT NULL COMMENT 'Save time',
          `save_time_com` bigint(20) NOT NULL DEFAULT 0 COMMENT 'time stamp',
          `param1` smallint(6) NULL DEFAULT NULL COMMENT 'Data type: 0: real-time storage data 1: timing storage data 2: alarm storage data (stored only when the state changes) 3: black box storage data',
          `param2` tinyint(3) NULL DEFAULT NULL COMMENT 'Unit 1:\"┬Ám\", 2:\"mm\", 3:\"mil\", 4:\"mm/s\", 5:\"inch/s\",  6:\"m/s2\", 7:\"g\",10: "KPa" 11:"MPa" 12:dB',
          `param3` tinyint(3) NULL DEFAULT NULL COMMENT 'data type,  0 : RMS ;1 :P ; 2 : P-P;',
          `param4` int(11) NULL DEFAULT NULL COMMENT 'Characteristic value high alarm status flag, by bit, 1 indicates alarm ; Bit0: Effective value Bit 1: peak value Bit 2: Peak to peak Bit 3: Kurtosis Bit 4: Crest factor Bit 10: Direct frequency value Bit 11: 1X amplitude  Bit 12: 1X phase Bit 13: 2X amplitude  Bit 14: 2X phase',
          `param5` int(11) NULL DEFAULT NULL COMMENT 'Characteristic value high alarm status flag, by bit, 1 indicates alarm ;',
          `param6` tinyint(3) NULL DEFAULT NULL COMMENT 'Comprehensive alarm sign, the highest alarm sign among all characteristic values;',
          `param7` float NULL DEFAULT NULL COMMENT 'speed',
          `param8` float NULL DEFAULT NULL COMMENT 'Effective value',
          `param9` float NULL DEFAULT NULL COMMENT 'peak value',
          `param10` float NULL DEFAULT NULL COMMENT 'Peak to peak',
          `param11` float NULL DEFAULT NULL COMMENT 'Kurtosis',
          `param12` float NULL DEFAULT NULL COMMENT 'Crest factor',
          `param13` float NULL DEFAULT NULL COMMENT 'Direct frequency value',
          `param14` float NULL DEFAULT NULL COMMENT '1 Frequency doubling amplitude',
          `param15` float NULL DEFAULT NULL COMMENT '1 Frequency doubling phase',
          `param16` float NULL DEFAULT NULL COMMENT '2 Frequency doubling amplitude',
          `param17` float NULL DEFAULT NULL COMMENT '2 Frequency doubling phase',
          `param18` float NULL DEFAULT NULL COMMENT 'average value',
          `param19` float NULL DEFAULT NULL COMMENT 'Gap voltage in V',
          PRIMARY KEY (`id`, `save_time_com`) USING BTREE,
          INDEX `saveTimeCom`(`save_time_com`) USING BTREE
        ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Vibration/Historical data storage table of pressure fluctuation characteristic value' ROW_FORMAT = Compact;
    </update>

    <select id="findById" resultMap="BaseResultMap">
        select * from t_order where id = #{id}
    </select>

    <select id="findBySaveTimeCom" resultMap="BaseResultMap">
        select * from t_order where save_time_com = #{saveTimeCom}
    </select>

    <select id="findByOrderBytemporalInterval" resultMap="BaseResultMap">
        select * from t_order where save_time_com between #{startTime} and #{endTime}
    </select>
</mapper>
10) Database and table building

The name of the library can be anything, as long as it corresponds to createDataSourceMap() in the configuration class. This case is the year of order + for example, now 2021, then the library in 2021 is called order_2021, please note that the database name here can be taken arbitrarily, but the key of the map built in createDataSourceMap() cannot be taken arbitrarily!!!

You can't take the table name casually. Here, the table is divided by year and by month, so the month is used as the suffix of the table name. For example, the table in January 2020 of the order table is order_ There are 24 tables in 202101 and 2022. Of course, you don't need to build them. You can give them to sharding JDBC

11) Test class
package com.dashuai.service;

import com.dashuai.mapper.OrderMapper;
import com.dashuai.pojo.Order;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ThreadLocalRandom;


@RunWith(SpringRunner.class)
@SpringBootTest
public class OrderServiceTest {


    @Autowired
    private OrderMapper orderMapper;


    @Test
    public void createTable() {
        try {
            orderMapper.createTable();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    public void insertOrder() {
        final Date saveTime = new Date();
        final long saveTimeCom = saveTime.getTime();
        System.out.println("The current time in milliseconds is:" + saveTimeCom);
        final Order order = new Order(1, 2, 3, 4, 5, saveTime, saveTimeCom, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
        orderMapper.insert(order);
    }


    @Test
    public void insertBatch() throws InterruptedException {
        final ArrayList<Order> orders = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            final Date saveTime = new Date();
            final long saveTimeCom = saveTime.getTime();
            System.out.println("saveTimeCom = " + saveTimeCom);
            final Order order = new Order(1, 2, 3, 4, 5, saveTime, saveTimeCom, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
            orders.add(order);
            Thread.sleep(100);
        }
        orderMapper.insertBatch(orders);
    }

    @Test
    public void insertBatch2() {

        // The random time is used for testing. The time interval is from January 1, 2020 to December 31, 2121. After conversion, the starting milliseconds of 2020 are 157780800000021 and the ending milliseconds are 1640966399000
        final ThreadLocalRandom current = ThreadLocalRandom.current();

        final ArrayList<Order> orders = new ArrayList<>();
        final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < 10; i++) {
            final long randomTime = current.nextLong(1577808000000L, 1640966399000L);
            final Date saveTime = new Date(randomTime);
            System.out.println("The time of random generation is:" + format.format(saveTime) + "Milliseconds are:" + randomTime);

            final Order order = new Order(1, 2, 3, 4, 5, saveTime, randomTime, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
            orders.add(order);
        }
        orderMapper.insertBatch(orders);
    }

    @Test
    public void fingOrderById() {
        final Order order = orderMapper.findById(564130608058466311L);
        System.out.println("order = " + order);
    }


    @Test
    public void findOrderBySaveTimeCom() {
        final Order order = orderMapper.findBySaveTimeCom(1582496739530L);
        System.out.println("order = " + order);
    }


    @Test
    public void findByOrderBytemporalInterval() {
        // Single warehouse and single table
//        List<Order> orders = orderMapper.findByOrderBytemporalInterval(1609713297565L, 1611479203727L);
//        orders.forEach(order -> System.out.println("order = " + order));

        // Cross database range query
        List<Order> orders = orderMapper.findByOrderBytemporalInterval(1592404225183L, 1618528709850L);
        orders.forEach(order -> System.out.println("order = " + order));
    }


}
12) Test analysis
Project startup log

You can see what your default library is, partition algorithm, partition key, row expression, logical table, primary key generation strategy and other information

Build table
@Test
    public void createTable() {
        try {
            orderMapper.createTable();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

After configuring the configuration of sub database and sub table, there is no need to manually create the table. Just let sharding JDBC execute the table creation sql of the logical table. It can be found through the log. After creating the logical table, sharding JDBC will create the real table for each node

Insert a piece of data
@Test
    public void insertOrder() {
        final Date saveTime = new Date();
        final long saveTimeCom = saveTime.getTime();
        System.out.println("The current time in milliseconds is:" + saveTimeCom);
        final Order order = new Order(1, 2, 3, 4, 5, saveTime, saveTimeCom, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
        orderMapper.insert(order);
    }

Here, we take the current time. The current time is February 2021. Then, this data should be inserted into the database table in February 2021. We can check whether the SQL route is correct by breaking the point in the fragmentation algorithm. If the data is inserted, we use the accurate fragmentation algorithm

You can see that the save passed in from our SQL is obtained in the accurate segmentation algorithm of the database_ time_ Value of COM

Through this millisecond, we can convert the time to 2021

If you look at these two figures again, do you have a familiar feeling? The available targetnames collection is the key that you create the data source Map in the configuration class, and sharding uses this key to locate the specific actual data source

Now that we have the year of the transmission time and the key of all our data sources, we can use it to locate the specific database to be operated. Naturally, we have found the database in 2021

After the library is obtained, the available targetnames in the table fragmentation algorithm are all the actual table names of the determined library

If you get the month by the time of transmission, you can get the specific operation table. After that, sharding JDBC will operate it accordingly

Check the sharding JDBC execution log. Logical SQL is the SQL you write. According to your logical SQL, get the actual SQL through sharding configuration and fragmentation algorithm to perform specific operations

As a result, this data is indeed inserted into the February table of the library in 2021

Batch insert
@Test
    public void insertBatch() throws InterruptedException {
        final ArrayList<Order> orders = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            final Date saveTime = new Date();
            final long saveTimeCom = saveTime.getTime();
            System.out.println("saveTimeCom = " + saveTimeCom);
            final Order order = new Order(1, 2, 3, 4, 5, saveTime, saveTimeCom, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
            orders.add(order);
            Thread.sleep(100);
        }
        orderMapper.insertBatch(orders);
    }

Time is still the key to achieving the present

When we insert ten pieces of data at one time, we can see that we will go through the database fragmentation algorithm ten times and get the route of each data one by one

The operation through time is the library in 2021

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-mAZCj7Iu-1612505210191)(springboot integrates sharding JDBC to realize annual database and monthly table. assets/image-20210204164001613.png)]

Look at logical SQL, which is a batch insert statement

SQL sharding is also a bit smart. Please look at the next test of SQL sharding

According to the results, the data is successfully inserted into the corresponding database

Batch insertion at different times
@Test
    public void insertBatch2() {

        // The random time is used for testing. The time interval is from January 1, 2020 to December 31, 2121. After conversion, the starting milliseconds of 2020 are 157780800000021 and the ending milliseconds are 1640966399000
        final ThreadLocalRandom current = ThreadLocalRandom.current();

        final ArrayList<Order> orders = new ArrayList<>();
        final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < 10; i++) {
            final long randomTime = current.nextLong(1577808000000L, 1640966399000L);
            final Date saveTime = new Date(randomTime);
            System.out.println("The time of random generation is:" + format.format(saveTime) + "Milliseconds are:" + randomTime);

            final Order order = new Order(1, 2, 3, 4, 5, saveTime, randomTime, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
            orders.add(order);
        }
        orderMapper.insertBatch(orders);
    }

At the beginning, we only allocated the libraries in 2020 and 2021, so we only conducted random tests between these two years

This is the time we randomly generated. We can see the data of February 2020, and there are exactly two data of January 2021

It is the same here. Each data will be processed into the database fragmentation algorithm once, so that sharding JDBC can get all the libraries that need to be operated

Logical SQL, no problem

However, the actual SQL executed is now 8. Sharding JDBC disassembles the time operation library differently for separate execution. We insert 10 pieces of data, and 4 operations are the same, so the actual SQL should be 8, which is correct

There are two new data in February 2020

Accurate query based on time
@Test
    public void findOrderBySaveTimeCom() {
        final Order order = orderMapper.findBySaveTimeCom(1582496739530L);
        System.out.println("order = " + order);
    }

Let's just find one of the data we just inserted

Get the time of transmission

Get years

Get the library to query

Get a month's time

Get the table to query

Logical SQL

Actual SQL

Check the results and see if they are correct

Query by time range
@Test
    public void findByOrderBytemporalInterval() {
        // Single warehouse and single table
        List<Order> orders = orderMapper.findByOrderBytemporalInterval(1609713297565L, 1611479203727L);
        orders.forEach(order -> System.out.println("order = " + order));
    }

Take the data of January 2021 as an example

You can see that the value passed in is in the valueRange object

After getting the range object, take the time and calculate the start year and end year

Get the library to query

The table segmentation algorithm is the same. Get the range object, get the time, and calculate the year, month and other information

Get the table to query

Logical SQL

Looking at the actual SQL, the query is the table of January in the library in 2021, and the results are just three

Cross database query based on time range
    @Test
    public void findByOrderBytemporalInterval() {
        // Single warehouse and single table
//        List<Order> orders = orderMapper.findByOrderBytemporalInterval(1609713297565L, 1611479203727L);
//        orders.forEach(order -> System.out.println("order = " + order));

        // Cross database range query
        List<Order> orders = orderMapper.findByOrderBytemporalInterval(1592404225183L, 1618528709850L);
        orders.forEach(order -> System.out.println("order = " + order));
    }

We choose the time from June 2020 to April 2021

Come in and get the time and calculate the year

Get two libraries to query

Table slicing algorithm is the same

One thing to note here is that this time, in fact, what comes in is the table information of the 2020 database

Get the table to be queried in 2020, from June to December, that's right

Let go of the breakpoint again. It will come in again, but this time it comes in 2021

Get the table to be queried in 2021

Logical SQL

From the actual SQL, we can see that because it is cross database and cross table, sharding JDBC executes this SQL in the database and table to be queried respectively. Finally, sharding JDBC will merge the results of all the queried data

Check the 16 results and count them in the database. They are indeed 16, correct

The fragment key is not used as the query condition
@Test
    public void fingOrderById() {
        final Order order = orderMapper.findById(564130608058466311L);
        System.out.println("order = " + order);
    }

Just find a piece of data and query by id here

Put the breakpoint in the fragment algorithm and find that it doesn't come in at all, because the query statement doesn't take the fragment key as the query condition. Look at the logical SQL

Looking at the actual SQL and results, it is found that if the partition key is not used as the query condition, the whole database and table routing will be carried out. Although the data can be queried, the efficiency is not high. Therefore, if the database and table are divided, the partition key must be brought in the query!!!

About default libraries

Although it is configured here, I'm not sure. It's very simple. If the table you query is not a logical table, sharding JDBC will find and execute the corresponding SQL in the default database you configured

[the transfer of external chain pictures fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the pictures and upload them directly (img-pWpcM6ye-1612505210220)(springboot integrates sharding JDBC to realize annual database and monthly table. assets/image-20210205101439925.png)]

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-RIJOTLRp-1612505210220)(springboot integrates sharding JDBC to realize annual database and monthly table. assets/image-20210205101452835.png)]

5. Long winded words

1) : about technology selection

Sharding JDBC only supports java. If you use Hal in other languages, you can consider mycay or sharding proxy or others. Here is a brief comparison of the three for reference:

Sharding-JDBCSharding-ProxyMyCat
databasearbitrarilyMySQL/PostgreSQLMySQL
Connection consumptionhighlowhigh
Heterogeneous languageJava onlyarbitrarilyarbitrarily
performanceLow lossSlightly higher losscommonly
Decentralizationyesnono
Do you support custom sharding routingyesyesyes
Whether independent deployment is requirednoyesyes

As for how to choose, it depends on your actual project situation

2) Some concepts about sharding JDBC

First of all, strictly speaking, it should not be considered as a middleware, so it is relatively lightweight. In fact, it is not difficult to find the whole case, which is mainly implemented around the configurations of logical table, fragment key, data source Map, table name, row expression, fragment algorithm, strategy and logical SQL

  • Logical table: you need to divide the table into databases and tables. For example, the order table here is the logical table. Note that when writing SQL, you can write the name of the logical table instead of the real table name, because if sharding JDBC finds that it is not a logical table, it will directly go to the default database

  • Fragment key: in your data, you need to use the data of what field to determine the segmentation of your data, such as millisecond save here_ time_ COM, in this case, only one is used. You can use multiple, one database partition key, one table partition key, or composite partition... In your SQL, sharding JDBC also determines whether to follow the sharding JDBC partition algorithm according to whether you have brought the partition key. Therefore, the query must be brought, otherwise the whole database and whole table routing. Another point to pay attention to here is the type problem. Here, the type is strict, That is to say, if your sharding key is of Long type in your sharding algorithm, it must also be of Long type when you transfer it into SQL. If you don't do database and table segmentation, maybe mysql will help you convert it automatically, but sharding JDBC won't. It needs to be strictly observed.

  • Data source Map and table name: the key of data source Map is very important. We can also see from the above interruption point that his key has actually been transferred to the library fragmentation algorithm and has become a condition for routing. Therefore, don't take it indiscriminately. You'd better make it yourself. How to name it in the front and how to use it in the back, and the table name is the same. How to name it and how to use it

  • Line expression: I remember this expression should not be necessary, but it can make it more convenient for you to express the data you want first. It is used to express the data node and is the basis for sharding JDBC initialization. It also needs to correspond to the key of the data source Map

Explain

dataSource_2020.t_order_20200${1..9},dataSource_2021.t_order_20210${1..9},dataSource_2020.t_order_20201${0..2},dataSource_2021.t_order_20211${0..2}

dataSource_2020.t_order_20200${1..9}: dataSource_2020 Need and data sources Map of key Correspondingly, it means that there are 9 in this database in 2020 order Table, 202001 to 202009 respectively
dataSource_2021.t_order_20210${1..9}: It's the same here, but it's 2021
dataSource_2020.t_order_20201${0..2}: It means that there will be three in this library in 2020 order Table, 202010 to 202012 respectively
dataSource_2021.t_order_20211${0..2}: ditto
  • Slicing algorithm and Strategy: in fact, the degree of implementation here is very high. The official only provides interfaces. You can write the specific implementation according to the scheme you specify. The data you can use here include the key, table name and slicing key value of the data source Map
  • Logical SQL: in fact, it's your normal SQL. It's just that sharding jdbc parses and routes you and merges the results. However, when writing SQL, you should pay attention to some unsupported items of sharding jdbc. You can check the unsupported items of jdbc and SQL on the official website and try to avoid the unsupported items when writing SQL. If you do sub database and sub table at the beginning of your project, you need to pay special attention. It's recommended to read and recite them by heart, If it is done in the later stage, it can only be changed. Almost all SQL has to go through... If you don't want to go through it all, here is a recommended mybatis plus plug-in dynamic datasource spring boot starter, which is a springboot based starter for rapid integration of multiple data sources. It realizes the function of data source separation, so that sharding jdbc only focuses on sub database and sub table, and other operations are left to the original data source, The advantage is that you only need to change the relevant SQL of the logical table, but the disadvantage is that you can't perform joint query. Of course, joint query is not appropriate when the amount of data is large. In addition, if you use sharding jdbc, try not to use extensions such as mybatis plus and general mapper. After all, they actually help you build an SQL that meets your expectations through your entity or condition constructor, However, the specific content of this SQL is unknown to the developer. It may be certain that the SQL he built for you can help you achieve your goals. However, when it comes to sharding jdbc, he needs to parse the SQL he just built. In this case, some uncontrollable errors may occur. Therefore, write your own SQL honestly
3) : some improvements about this case

At the beginning, the configuration mode of java was selected because it is more flexible. When configuring row expressions and building data source maps, these configuration information can be read from the database. This configuration can also be managed in the background. By reloading sharding JDBC or restarting the project in some ways, it can be more flexible and not dead. Two tables are provided for reference:

CREATE TABLE `ly_sharding_data_source`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment primary key',
  `rule_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Routing data source name',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Connection address',
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Data source user name',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Data source password',
  `driver_class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Drive full class name',
  `sharding_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Column of sub Library',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
CREATE TABLE `ly_sharding_data_source_detail`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Self increasing id',
  `data_source_id` int(11) NULL DEFAULT NULL COMMENT 'data source id',
  `logical_table` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Logical table name',
  `sharding_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Sub table column',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 160 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
4) Last few sentences

The expansion has been tested in my project and is feasible. I originally wanted to integrate it into this case, but I didn't do it for the time being... I'll add it later if I have time, and query performance test...

, the configuration information can be read from the database, and the configuration can also be managed in the background. By reloading sharding JDBC or restarting the project in some ways, it can be more flexible and not dead. Two tables are provided for reference:

CREATE TABLE `ly_sharding_data_source`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment primary key',
  `rule_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Routing data source name',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Connection address',
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Data source user name',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Data source password',
  `driver_class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Driver full class name',
  `sharding_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Column of sub Library',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
CREATE TABLE `ly_sharding_data_source_detail`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Self increasing id',
  `data_source_id` int(11) NULL DEFAULT NULL COMMENT 'data source id',
  `logical_table` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Logical table name',
  `sharding_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Sub table column',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 160 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
4) Last few sentences

The expansion has been tested in my project and is feasible. I originally wanted to integrate it into this case, but I didn't do it for the time being... I'll add it later if I have time, and query performance test...

Finally, I wish you all a happy new year and a happy holiday!

Tags: Java Database MySQL Spring Boot sharding

Posted by NDF on Mon, 25 Apr 2022 07:15:59 +0300