Based on the principles of AOP and HashMap, develop Mysql sub-database and sub-table routing components!

Author: Xiao Fu Ge
Blog: https://bugstack.cn

Precipitate, share, and grow, so that you and others can gain something! 😄

I. Introduction

what? Java interview is like building a rocket🚀

simple! past I have always wanted a good Java interview to interview chant, Gaha it total number of test work is also less than stuff, will use Spring , MyBatis , Dubbo , MQ , the business needs to achieve a not on the line!

But when I needed to improve myself after working for a few years ( would add money to ), I started to feel that I was just a tool man who called API to build an interface. There is no knowledge breadth, no technical depth, nor can I think of it, and I am not aware of it. The common logic in the daily development business code is extracted and developed into common components, and there is no thinking about the technology implementation of some of the components used daily. of.

So sometimes you say that interviews seem to be building rockets. These techniques are not used in daily life. In fact, many times it is not that this technique is not used, but because you are useless ( , I didn't use ). When you have this idea and want to break through your salary and treatment bottleneck, you need to go to to understand the necessary data structure, to learn the algorithm logic of Java, familiar with common design patterns, and then combine with Spring, ORM, RPC , Such a source code implementation logic, empower the corresponding technical solutions to your daily business development, and solve the common problems in a focused and refined way. These are the manifestations of your ability outside of CRUD ( salary increase Chips ).

about 1611db425bf1fe? seems to make sense, so let’s take a chestnut , and let’s take a look at the requirements analysis and logic implementation of database routing!

2. Demand analysis

If you want to do a database routing, what technical points are required?

First of all, we need to know why we need to use sub-databases and tables. In fact, due to the large business volume and rapid data growth, we need to split user data into different database tables to reduce database pressure.

The sub-database sub-table operations mainly include vertical split and horizontal split:

• Vertical split: It refers to classifying tables according to business and distributing them to different databases, so that the pressure of data is shared among different databases. In the end, a database is composed of many tables, and each table corresponds to a different business, that is, a dedicated database.
• Horizontal splitting: If you encounter a stand-alone bottleneck after vertical splitting, you can use horizontal splitting. Compared with vertical splitting, the difference is: vertical splitting is to split different tables into different databases, while horizontal splitting is to split the same table into different databases. Such as: user_001, user_002

And what we want to achieve in this chapter is also a horizontal split routing design, as shown in Figure 1-1

So, what technical knowledge points should such a database routing design include?

• It is about the use of AOP aspect interception. This is because the method of using database routing needs to be marked to facilitate the processing of the sub-database and sub-table logic.
• The data source switching operation, since there is a sub-database, it will involve link switching among multiple data sources in order to distribute the data to different databases.
• Database table addressing operations, which database and table a piece of data is allocated to, require index calculations. In the process of method call, it is finally recorded through ThreadLocal.
• In order to distribute the data evenly to different database tables, it is also necessary to consider how to perform data hashing operations. After the database cannot be divided into tables, the data is concentrated in a certain table in a certain library, so that it will be lost. The meaning of sub-library and sub-table.

In summary, you can see that the data storage is completed under the data structure of the database and the table. The technologies I need to use include: AOP , data source switching, hash algorithm, hash addressing, ThreadLoca l and SpringBoot Starter development method and other technologies. And like hash hashing, addressing, data storage, in fact, this technology has too many similarities with HashMap, then the opportunity to learn the source code to build rockets is here if you have in-depth analysis and learning HashMap source code, Spring source code, middleware development, then there must be many ideas when designing such a database routing component. Next, let's try to learn from the source code to build a rocket!

Three, technical research

In the JDK source code, the data structure design included: array, linked list, queue, stack, red-black tree, specific implementations are ArrayList, LinkedList, Queue, Stack, and these are all sequential storage in data storage, and are not used The hash index is used for processing. The two functions of HashMap and ThreadLocal use hash index, hash algorithm, zipper addressing and open addressing when data is expanded, so what we want to analyze and learn from will also focus on these two functions.

1. ThreadLocal

@Test
public void test_idx() {
    int hashCode = 0;
    for (int i = 0; i < 16; i++) {
        hashCode = i * 0x61c88647 + 0x61c88647;
        int idx = hashCode & 15;
        System.out.println("斐波那契散列：" + idx + " 普通散列：" + (String.valueOf(i).hashCode() & 15));
    }
} 

斐波那契散列：7 普通散列：0
斐波那契散列：14 普通散列：1
斐波那契散列：5 普通散列：2
斐波那契散列：12 普通散列：3
斐波那契散列：3 普通散列：4
斐波那契散列：10 普通散列：5
斐波那契散列：1 普通散列：6
斐波那契散列：8 普通散列：7
斐波那契散列：15 普通散列：8
斐波那契散列：6 普通散列：9
斐波那契散列：13 普通散列：15
斐波那契散列：4 普通散列：0
斐波那契散列：11 普通散列：1
斐波那契散列：2 普通散列：2
斐波那契散列：9 普通散列：3
斐波那契散列：0 普通散列：4

• data structure : the array structure of the hash table
• hashing algorithm : Fibonacci (Fibonacci) hashing method
• addressing mode : Fibonacci hashing method can make the data more scattered, open addressing in the event of data collision, find the location from the collision node backward to store the element. Formula: f(k) = ((k * 2654435769) >> X) << Y For a common 32-bit integer, that is, f(k) = (k * 2654435769) >> 28, the golden section point: (√5 - 1) / 2 = 0.6180339887 1.618:1 == 1:0.618
• What : You can refer to the addressing mode and hash algorithm, but this data structure is quite different from the structure to be designed and implemented on the database, but ThreadLocal can be used to store and transfer data index information.

2. HashMap

public static int disturbHashIdx(String key, int size) {
    return (size - 1) & (key.hashCode() ^ (key.hashCode() >>> 16));
}

• data structure : hash bucket array + linked list + red-black tree
• hash algorithm : disturbance function, hash index, can make the data more hash distribution
• addressing mode : Solve data collisions through zipper addressing. When data is stored, index addresses will be used. When collisions are encountered, a data linked list will be generated. When a certain capacity exceeds 8 elements, the capacity will be expanded or treeed.
• What : You can apply hash algorithms and addressing methods to the design and implementation of database routing, as well as the entire array + linked list method. In fact, the library + table method has similarities.

Four, design and implementation

1. Define routing annotations

definition

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
public @interface DBRouter {

    String key() default "";

}

uses

@Mapper
public interface IUserDao {

     @DBRouter(key = "userId")
     User queryUserInfoByUserId(User req);

     @DBRouter(key = "userId")
     void insertUser(User req);

}

• First, we need to customize an annotation to be placed on the method that needs to be routed by the database.
• Its use is to configure annotations through methods, which can be intercepted by our designated AOP aspect. After interception, the corresponding database routing calculation and judgment will be performed, and the corresponding operation data source will be switched to.

2. Resolve routing configuration

• The above is a data source configuration after we have implemented the database routing component. In the use of data sources under sub-databases and sub-tables, it is necessary to support the information configuration of multiple data sources, so as to meet the expansion of different needs.
• For this kind of customized information configuration, it is necessary to use the org.springframework.context.EnvironmentAware interface to obtain the configuration file and extract the required configuration information.

data source configuration extraction

@Override
public void setEnvironment(Environment environment) {
    String prefix = "router.jdbc.datasource.";    

    dbCount = Integer.valueOf(environment.getProperty(prefix + "dbCount"));
    tbCount = Integer.valueOf(environment.getProperty(prefix + "tbCount"));    

    String dataSources = environment.getProperty(prefix + "list");
    for (String dbInfo : dataSources.split(",")) {
        Map<String, Object> dataSourceProps = PropertyUtil.handle(environment, prefix + dbInfo, Map.class);
        dataSourceMap.put(dbInfo, dataSourceProps);
    }
}

• prefix is the beginning information of the data source configuration, you can customize the beginning content you need.
• dbCount, tbCount, dataSources, and dataSourceProps are all extractions of configuration information and store them in dataSourceMap for subsequent use.

3. Data source switching

In the Starter developed with SpringBoot, a DataSource instantiation object needs to be provided, then this object is implemented in DataSourceAutoConfig, and the data source provided here can be dynamically transformed, that is, it supports dynamic switching of data sources.

Create data source

@Bean
public DataSource dataSource() {
    // 创建数据源
    Map<Object, Object> targetDataSources = new HashMap<>();
    for (String dbInfo : dataSourceMap.keySet()) {
        Map<String, Object> objMap = dataSourceMap.get(dbInfo);
        targetDataSources.put(dbInfo, new DriverManagerDataSource(objMap.get("url").toString(), objMap.get("username").toString(), objMap.get("password").toString()));
    }     

    // 设置数据源
    DynamicDataSource dynamicDataSource = new DynamicDataSource();
    dynamicDataSource.setTargetDataSources(targetDataSources);
    return dynamicDataSource;
}

• Here is a simplified creation case that instantiates creation based on the data source information read from the configuration information.
• After the data source is created DynamicDataSource . It is an implementation class that inherits AbstractRoutingDataSource. This class can store and read the corresponding specific call data source information.

4. Cross-cutting

It needs to be completed in the aspect interception of AOP; database routing calculation, perturbation function enhanced hashing, library table index calculation, set to ThreadLocal to transmit data source, the overall case code is as follows:

@Around("aopPoint() && @annotation(dbRouter)")
public Object doRouter(ProceedingJoinPoint jp, DBRouter dbRouter) throws Throwable {
    String dbKey = dbRouter.key();
    if (StringUtils.isBlank(dbKey)) throw new RuntimeException("annotation DBRouter key is null！");

    // 计算路由
    String dbKeyAttr = getAttrValue(dbKey, jp.getArgs());
    int size = dbRouterConfig.getDbCount() * dbRouterConfig.getTbCount();

    // 扰动函数
    int idx = (size - 1) & (dbKeyAttr.hashCode() ^ (dbKeyAttr.hashCode() >>> 16));

    // 库表索引
    int dbIdx = idx / dbRouterConfig.getTbCount() + 1;
    int tbIdx = idx - dbRouterConfig.getTbCount() * (dbIdx - 1);   

    // 设置到 ThreadLocal
    DBContextHolder.setDBKey(String.format("%02d", dbIdx));
    DBContextHolder.setTBKey(String.format("%02d", tbIdx));
    logger.info("数据库路由 method：{} dbIdx：{} tbIdx：{}", getMethod(jp).getName(), dbIdx, tbIdx);
   
    // 返回结果
    try {
        return jp.proceed();
    } finally {
        DBContextHolder.clearDBKey();
        DBContextHolder.clearTBKey();
    }
}

• The simplified core logic implementation code is as above. First, we extract the number of library table products and use it as the same length as the HashMap.
• Next, use the same perturbation function logic as HashMap to make the data more scattered and hashed.
• After calculating an index position on the total length, you need to convert this position into the library table to see which library and table the overall length index falls into.
• Finally, the calculated index information is stored in ThreadLocal, which is used to transfer the index information that can be extracted during method invocation.

5. Test Verification

5.1 Library table creation

create database `bugstack_01`;
DROP TABLE user_01;
CREATE TABLE user_01 ( id bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID', userId varchar(9) COMMENT '用户ID', userNickName varchar(32) COMMENT '用户昵称', userHead varchar(16) COMMENT '用户头像', userPassword varchar(64) COMMENT '用户密码', createTime datetime COMMENT '创建时间', updateTime datetime COMMENT '更新时间', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE user_02;
CREATE TABLE user_02 ( id bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID', userId varchar(9) COMMENT '用户ID', userNickName varchar(32) COMMENT '用户昵称', userHead varchar(16) COMMENT '用户头像', userPassword varchar(64) COMMENT '用户密码', createTime datetime COMMENT '创建时间', updateTime datetime COMMENT '更新时间', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE user_03;
CREATE TABLE user_03 ( id bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID', userId varchar(9) COMMENT '用户ID', userNickName varchar(32) COMMENT '用户昵称', userHead varchar(16) COMMENT '用户头像', userPassword varchar(64) COMMENT '用户密码', createTime datetime COMMENT '创建时间', updateTime datetime COMMENT '更新时间', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE user_04;
CREATE TABLE user_04 ( id bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID', userId varchar(9) COMMENT '用户ID', userNickName varchar(32) COMMENT '用户昵称', userHead varchar(16) COMMENT '用户头像', userPassword varchar(64) COMMENT '用户密码', createTime datetime COMMENT '创建时间', updateTime datetime COMMENT '更新时间', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• Create multiple inventory information with the same table structure, bugstack_01, bugstack_02

5.2 Statement configuration

<select id="queryUserInfoByUserId" parameterType="cn.bugstack.middleware.test.infrastructure.po.User"
        resultType="cn.bugstack.middleware.test.infrastructure.po.User">
    SELECT id, userId, userNickName, userHead, userPassword, createTime
    FROM user_${tbIdx}
    where userId = #{userId}
</select>               

<insert id="insertUser" parameterType="cn.bugstack.middleware.test.infrastructure.po.User">
    insert into user_${tbIdx} (id, userId, userNickName, userHead, userPassword,createTime, updateTime)
    values (#{id},#{userId},#{userNickName},#{userHead},#{userPassword},now(),now())
</insert>

• In the statement usage of MyBatis, the only change needs to add a placeholder after the table name, ${tbIdx} used to write the current table ID.

5.3 Annotation configuration

@DBRouter(key = "userId")
User queryUserInfoByUserId(User req);   

@DBRouter(key = "userId")
void insertUser(User req);

• Add a comment to the method that needs to use the sub-database and sub-table. After adding the comment, this method will be managed by the AOP aspect.

5.4 Unit testing

22:38:20.067  INFO 19900 --- [           main] c.b.m.db.router.DBRouterJoinPoint        : 数据库路由 method：queryUserInfoByUserId dbIdx：2 tbIdx：3
22:38:20.594  INFO 19900 --- [           main] cn.bugstack.middleware.test.ApiTest      : 测试结果：{"createTime":1615908803000,"id":2,"userHead":"01_50","userId":"980765512","userNickName":"小傅哥","userPassword":"123456"}
22:38:20.620  INFO 19900 --- [extShutdownHook] o.s.s.concurrent.ThreadPoolTaskExecutor  : Shutting down ExecutorService 'applicationTaskExecutor'1

• The above is a log message when we use our own database routing component to execute. You can see that it contains routing operations, in 2 database 3 tables: database routing method: queryUserInfoByUserId dbIdx: 2 tbIdx: 3

Five, summary

summary, means that we learned the inner principle of the technology from source code learning such as HashMap, ThreadLocal, and Spring, and used this technology in a database routing design. If you have not experienced these technology precipitation that is always said to be the rocket , then it is almost impossible to successfully develop such a middleware. In many cases, it is not that the technology is useless, but it is useless to use it. That's it. Don't always worry about the repeated CRUD, and see what other knowledge can really improve your personal ability! Reference materials: https://codechina.csdn.net/MiddlewareDesign

Sixth, series recommendation

• "Hand Lu Spring" PDF, 260 pages of 65 thousand words, sorted and shared
• Develop a distributed IM instant messaging system!
• Spring Bean IOC, AOP circular dependency interpretation
• 1611db425c0659 wrote 200,000 lines of code before graduation, which made me become a face