Why separate tables

Mysql is a very widely used relational database in the current Internet system, which has the characteristics of ACID.

However, the single-table performance of mysql is limited by the amount of data in the table. The main reason is that the index in the B+ tree is too large. As a result, all indexes cannot be loaded into the memory during query. The number of reads to the disk increases, and each read to the disk has a significant impact on performance.

At this time, a simple and feasible solution is to split the table (of course, the rich can also heap hardware), to split the data of a large table into multiple tables, which also reduces the size of the B+ tree index, reduces the disk read times, and improves the performance.

Two basic table logic

With that said, let’s talk about two basic types of table logic that are common in business development.

Table by date

This method usually adds the year, month and day to the end of the table name, and is mainly applicable to statistics or operational records divided by date. Only the data in the latest table is displayed online in real time, and other data is used for offline statistics.

Select module subtable by ID

This approach requires an ID generator, such as the Snowflake ID or distributed ID service. It ensures that the data with the same ID are in a table, which is mainly suitable for saving user basic information, resource information in the system, purchase records and so on. Of course, this kind of table partition method has poor scalability. After the data continues to increase in the later period, it is necessary to divide the database into tables according to the ID size.

Let’s look at the implementation of the two types of table logic in Mybatis – Plus.

Mybatis-plus sub-table implementation

When it comes to Java sub-table middleware, some people may think of Sharding-JDBC, as a widely used sub-table middleware, its functions are relatively complete, but using it requires the introduction of additional JAR packages and increased learning costs.

In fact, Mybatis – Plus itself provides a sub-table solution, configuration and use are very simple, suitable for rapid development system.

Dynamic table name processor

Mybatis – Plus provides a dynamic table name processor interface (TableNameHandler). You only need to implement this interface in your system and load it into Mybatis – Plus as a plug-in to use it.

Prior to version 3.4, the dynamic table name interface was ITableNameHandler and needed to be used in conjunction with the paging plug-in.

Version 3.4 added TableNameHandler and removed MetaObject from method arguments. Using the latest version as an example, there is little difference in how it is used.

Suppose there are two types of table division in our system, table division by date and module division by ID. Take a look at an example through four steps.

1. Create a date table name handler

After implementing the TableNameHandler interface, implement the logic of dynamically generating table names in the dynamicTableName method. The return value of the method is the table name to be used in the query.

/** ** ** /
public class DaysTableNameParser implements TableNameHandler {

    @Override
    public String dynamicTableName(String sql, String tableName) {
        String dateDay = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        return tableName + "_"+ dateDay; }}Copy the code
2. Create an ID fetch table name processor

Let’s look at the implementation of the modular table name processor by ID. This processor is a little more complicated than the date processing, mainly because it needs to dynamically pass in the ID value for the table.

In previous versions, you can obtain the values used by sub-tables in methods by parsing SQL query information in MetaObject. However, this method is more complex, and different QueryMapper analysis methods are different, which is more prone to error. The MetaObject argument is removed from the method in the new version and needs to be passed in another way.

It is important to note that the table name handler is instantiated at project startup as a plug-in for Mybatis – Plus. This means that there is only one object in the running process, and in multi-threading, changes made by one thread can affect other threads. To solve this problem, you can use ThreadLocal to define parameters.

Most frameworks today use thread pools, such as Tomcat in the SpringBoot Web project. Therefore, you need to manually clear the data after each use to prevent impact on thread reuse.

The concrete implementation is as follows:

/** * select */ by id
public class IdModTableNameParser implements TableNameHandler {
    private Integer mod;

    // Use ThreadLocal to prevent multiple threads from affecting each other
    private static ThreadLocal<Integer> id = new ThreadLocal<Integer>();

    public static void setId(Integer idValue) {
        id.set(idValue);
    }

    IdModTableNameParser(Integer modValue) {
        mod = modValue;
    }

    @Override
    public String dynamicTableName(String sql, String tableName) {
        Integer idValue = id.get();
        if (idValue == null) {
            throw new RuntimeException("Please set the ID value");
        } else {
            String suffix = String.valueOf(idValue % mod);
            // Clear the value of ThreadLocal to prevent thread reuse problems
            id.set(null);
            return tableName + "_"+ suffix; }}}Copy the code
3. Load the table name processor

The table name handler is actually a plug-in for Mybatis – Plus that needs to be created and loaded at initialization time. Because there are two sub-table types in the system, the table name processor used by each table can be specified at initialization. The concrete implementation is as follows:

@Configuration
@MapperScan(basePackages = "com.yourcom.proname.repository.mapper.mainDb*", sqlSessionFactoryRef = "mainSqlSessionFactory")
public class MainDb {
    @Bean(name = "mainDataSource")
    @ConfigurationProperties(prefix = "dbconfig.maindb")
    public DataSource druidDataSource(a) {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "mainTransactionManager")
    public DataSourceTransactionManager masterTransactionManager(@Qualifier(value = "mainDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mainSqlSessionFactory")
    @ConfigurationPropertiesBinding()
    public SqlSessionFactory sqlSessionFactory(@Qualifier(value = "mainDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
      	// Load the plug-in
        factoryBean.setPlugins(mybatisPlusInterceptor());
        return factoryBean.getObject();
    }

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        HashMap<String, TableNameHandler> map = new HashMap<String, TableNameHandler>();

        // Set table name handlers for different tables
        map.put("user_daily_record".new DaysTableNameParser());
        map.put("user_consume_flow".new IdModTableNameParser(10));

        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        returninterceptor; }}Copy the code
4. Use it in controller

The following three interfaces in controller show the usage mode:

@RestController
public class TableTestController {
    @Resource
    IUserDailyRecordService userDailyRecordService;

    @Resource
    IUserConsumeFlowService userConsumeFlowService;

    @GetMapping("user/record/today")
    public CommonResVo<UserDailyRecord> getRecordToday(Integer userId) throws Exception {
        // The table name of the current day is automatically generated based on the current system time
        UserDailyRecord userDailyRecord = userDailyRecordService.getOne(new LambdaQueryWrapper<UserDailyRecord>().eq(UserDailyRecord::getUserId, userId));
        return CommonResVo.success(userDailyRecord);
    }

    @GetMapping("user/consume/flow")
    public CommonResVo<List<UserConsumeFlow>> getConsumeFlow(Integer userId) throws Exception {
        // Set the id value for the sub-table
        IdModTableNameParser.setId(userId);
        List<UserConsumeFlow> userConsumeFlowList = userConsumeFlowService.list(new LambdaQueryWrapper<UserConsumeFlow>().eq(UserConsumeFlow::getUserId, userId));
        return CommonResVo.success(userConsumeFlowList);
    }

    /** * New data */
    @PostMapping("user/consume/flow")
    public CommonResVo<Boolean> addConsumeFlow(@RequestBody UserConsumeFlow userConsumeFlow) throws Exception {
        Integer userId = userConsumeFlow.getUserId();
        // Set the id value for the sub-table
        IdModTableNameParser.setId(userId);
        userConsumeFlowService.save(userConsumeFlow);
        return CommonResVo.success(true); }}Copy the code

This article on mybatis-plus dynamic table name processor introduction, through the implementation of TableNameHandler interface, can flexibly define table name generation rules according to the actual situation, I hope to help you.

Project Complete example Address: gitee.com/dothetrick/…

The above content is a personal learning summary, if there is any inappropriate, welcome to point out in the comments