This is the 8th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

background

When the database table keeps increasing and the data queried by an interface has exceeded 500ms, it is necessary to carry out sub-database sub-table to ensure the query efficiency

The problem

It is worth noting that when the database table is very large and the primary key ID is different from the library table, the primary key ID uniqueness problem needs to be considered. There is also the issue of cross-library joins

plan

At present, there are a lot of very good schemes

  • Framework layer: by implementing some interceptors (e.gMybatistheInterceptorInterface), adding some custom parsing to control the flow of data
  • The driver layer includes:TDDL,ShardingJDBC
  • The agent layer:MySQL Router,MyCat

The following code implementation of ShardingJDBC is used to see how the sub-library sub-table is used in the project.

Code implementation

Introduction of depend on

First you need to introduce the maven dependency for ShardingJDBC

   <dependency>
       <groupId>com.dangdang</groupId>
       <artifactId>sharding-jdbc-core</artifactId>
       <version>1.5.4</version>
   </dependency>
Copy the code

Exclude default data sources

Since ShardingJDBC is a sub-database sub-table on the data source, the original default data source is excluded

@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
Copy the code

Set branch rules

Set the database allocation policy to db0 if the key value is greater than 30000 and DB1 if the key value is smaller than 30000

@Component
public class DbSharding implements SingleKeyDatabaseShardingAlgorithm<Integer> {

    @Autowired
    private Db0Config db0Config;

    @Autowired
    private Db1Config db1Config;

    @Override
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
        Long value = shardingValue.getValue();
        if (value > 30000) {
            return db0Config.getDatabaseName();
        } else {
            returndb1Config.getDatabaseName(); }}}Copy the code

Set table rules

Set the table rules, according to odd and even points

@Component
public class TbSharding implements SingleKeyTableShardingAlgorithm<Integer> {

    @Override
    public String doEqualSharding(final Collection<String> tableNames, final ShardingValue<Integer> shardingValue) {
        for (String table : tableNames) {
            if (table.endsWith(shardingValue % 2 + "")) {
                returntable; }}throw newIllegalArgumentException(); }}Copy the code

Configure database and table policies

The uniform distribution mode is adopted, and the final data distribution is as follows:

Db0 ├ ─ ─ user_0 └ ─ ─ user_1 db1 ├ ─ ─ user_0 └ ─ ─ user_1Copy the code

The most critical code! Set the database and table division policy for the data source

@Configuration
public class DataSourceConfig {

    @Autowired
    private Db0Config db0Config;

    @Autowired
    private Db1Config db1Config;

    @Autowired
    private DbSharding  dbSharding;

    @Autowired
    private TbSharding tbSharding;

    @Bean
    public DataSource getDataSource(a) throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource(a) throws SQLException {

        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        // Add data sources db0 and db1
        dataSourceMap.put(db0Config.getDatabaseName(), db0Config.dataSource());
        dataSourceMap.put(db1Config.getDatabaseName(), db1Config.dataSource());
        // set db0 as the default source
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, db0Config.getDatabaseName());

        // Set the sub-table to map the query virtual table user to the real table according to the rules
        TableRule userTableRule = TableRule.builder("user")
                .actualTables(Arrays.asList("user_0"."user_1"))
                .dataSourceRule(dataSourceRule)
                .build();

        // Separate database and table
        ShardingRule shardingRule = ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Arrays.asList(userTableRule))
                .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", dbSharding))
                .tableShardingStrategy(new TableShardingStrategy("id", tbSharding)).build();
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
        return dataSource;
    }


    @Bean
    public KeyGenerator keyGenerator(a) {
        return newDefaultKeyGenerator(); }}Copy the code

Concept to explain

TableRule

Table rule configuration object, created by embedding TableRuleBuilder object.

Data unit

  • DataNode

Static subdatabase subtable data unit The smallest unit of a data fragment, consisting of a data source name and a data table. Example: ds_1 t_order_0. By default, the table structure of each shard database is the same. You can directly configure the mapping between logical tables and real tables.

  • DynamicDataNode

Dynamic table sub-library sub-table data unit logical tables and real tables do not necessarily need to be statically configured in configuration rules. For example, in the case of sharding by date, the names of real tables change over time

TableRuleBuilder

TableRuleBuilder calls the #build() method to create a TableRule

Split database/table strategy

  • databaseShardingStrategy: branch library policy
  • tableShardingStrategy: table division policy

ShardingRule

Sub-database sub-table rule configuration object, embedded ShardingRuleBuilder object to create.

dataSourceRule

DataSourceRule, data source configuration object. ShardingRule requires that the data source be configured correctly. This is different from a TableRule. TableRule uses only the data source name for dataSourceRule, and finally executes SQL to get the data source connection from ShardingRule using the data source name

Primary key generation

  • generateKeyColumn: Primary key field
  • keyGenerator: Primary key generator

summary

This article simply explains why the use of sub-database sub-table, as well as sub-database sub-table scheme, with ShardingJDBC as an example to give a part of the code implementation, and explained the concept, part of the content of the article reference you to the understanding of sub-database sub-table to sort out.

reference

  • “Separate inventory and separate table”? Selection and process should be careful, otherwise it will be out of control
  • Sharding-JDBC source code analysis
  • “SpringBoot using Sharing-JDBC sub-library sub-table”