How to achieve read-write separation, Spring Boot project, the database is MySQL, and the persistence layer uses MyBatis.

In fact, it is very simple to implement this. First think about a question:

In a high-concurrency scenario, everything about the database What optimization methods are there?

The following implementation methods are commonly used: read-write separation, caching, master-slave architecture cluster, sub-database and sub-table, etc.

In Internet applications, most of the scenarios involve more reading and less writing. Two libraries are set up, the main library and the reading library.

The main library is responsible for writing, and the slave library is mainly responsible for reading. A reading library cluster can be established to reduce read and write conflicts and relieve database load by isolating the read and write functions on the data source. , the purpose of protecting the database. In actual use, any part involving writing is directly switched to the main library, and the reading part is directly switched to the reading library. This is a typical read-write separation technology.

This article will focus on the separation of reading and writing and explore how to achieve it.

Limitations of master-slave synchronization: This is divided into master database and slave database. The master database and slave database maintain the same database structure. The master database Responsible for writing. When writing data, the data will be automatically synchronized to the slave database. The slave database is responsible for reading. When a read request comes, the data is read directly from the reading database, and the master database will automatically copy the data to the slave database. However, this blog does not introduce this part of the configuration knowledge, because it is more focused on operation and maintenance work.

There is a problem involved here:

The delay problem of master-slave replication. When writing to the main database, a read request suddenly comes, and the data is still there. Without complete synchronization, there will be situations where the read requested data cannot be read or the data read is less than the original value. The simplest specific solution is to temporarily point the read request to the main library, but at the same time it also loses part of the meaning of master-slave separation. That is to say, in the strict sense of data consistency scenarios, read-write separation is not completely suitable. Pay attention to the timeliness of updates as a shortcoming of the use of read-write separation.

Okay, this part is just for understanding. Next, let’s look at how to achieve read and write separation through Java code:

Note: This project needs to introduce the following dependencies: Spring Boot, spring-aop, spring-jdbc, aspectjweaver, etc.

Program Yuan: Only 30 days, what should I do? Prepare?

1: Configuration of master-slave data source

We need to configure the master-slave database, master-slave Database configuration is generally written in the configuration file. Through the @ConfigurationProperties annotation, the properties in the configuration file (generally named: application.Properties) can be mapped to specific class properties, so that the written values ??can be read and injected into the specific code configuration. , in accordance with the principle that custom is greater than agreement, we all mark the main library as master and the slave library as slave.

This project uses Alibaba's druid database connection pool and uses the build builder mode to create DataSource objects. DataSource is the data source abstracted at the code level. Then you need to configure sessionFactory, sqlTemplate, transaction manager, etc.

/**
 * 主從配置 
 */
@Configuration
@MapperScan(basePackages = "com.wyq.mysqlreadwriteseparate.mapper", sqlSessionTemplateRef = "sqlTemplate")
public class DataSourceConfig {

    /**
     * 主庫
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource master() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 從庫
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaver() {
        return DruidDataSourceBuilder.create().build();
    }


    /**
     * 實(shí)例化數(shù)據(jù)源路由
     */
    @Bean
    public DataSourceRouter dynamicDB(@Qualifier("master") DataSource masterDataSource,
                                      @Autowired(required = false) @Qualifier("slaver") DataSource slaveDataSource) {
        DataSourceRouter dynamicDataSource = new DataSourceRouter();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceEnum.MASTER.getDataSourceName(), masterDataSource);
        if (slaveDataSource != null) {
            targetDataSources.put(DataSourceEnum.SLAVE.getDataSourceName(), slaveDataSource);
        }
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        return dynamicDataSource;
    }


    /**
     * 配置sessionFactory
     * @param dynamicDataSource
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sessionFactory(@Qualifier("dynamicDB") DataSource dynamicDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*Mapper.xml"));
        bean.setDataSource(dynamicDataSource);
        return bean.getObject();
    }


    /**
     * 創(chuàng)建sqlTemplate
     * @param sqlSessionFactory
     * @return
     */
    @Bean
    public SqlSessionTemplate sqlTemplate(@Qualifier("sessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


    /**
     * 事務(wù)配置
     *
     * @param dynamicDataSource
     * @return
     */
    @Bean(name = "dataSourceTx")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dynamicDB") DataSource dynamicDataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dynamicDataSource);
        return dataSourceTransactionManager;
    }
}

2: Configuration of data source routing

Routing is very important in master-slave separation, basically The core of read-write switching. Spring provides AbstractRoutingDataSource to select the current data source according to user-defined rules. Its function is to set the data source used before executing the query, implement dynamic routing data source, and execute it before each database query operation. The abstract method determineCurrentLookupKey() determines which data source to use.

In order to have a global data source manager, we need to introduce the DataSourceContextHolder database context manager, which can be understood as a global variable and can be accessed at any time (see detailed introduction below). Its main function is Save the current data source.

public class DataSourceRouter extends AbstractRoutingDataSource {

    /**
     * 最終的determineCurrentLookupKey返回的是從DataSourceContextHolder中拿到的,因此在動態(tài)切換數(shù)據(jù)源的時(shí)候注解
     * 應(yīng)該給DataSourceContextHolder設(shè)值
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();

    }
}

三：數(shù)據(jù)源上下文環(huán)境

數(shù)據(jù)源上下文保存器,便于程序中可以隨時(shí)取到當(dāng)前的數(shù)據(jù)源，它主要利用 ThreadLocal 封裝，因?yàn)?ThreadLocal 是線程隔離的，天然具有線程安全的優(yōu)勢。這里暴露了 set 和 get、clear 方法，set 方法用于賦值當(dāng)前的數(shù)據(jù)源名，get 方法用于獲取當(dāng)前的數(shù)據(jù)源名稱，clear 方法用于清除 ThreadLocal 中的內(nèi)容，因?yàn)?ThreadLocal 的 key 是 weakReference 是有內(nèi)存泄漏風(fēng)險(xiǎn)的，通過 remove 方法防止內(nèi)存泄漏。

/**
 * 利用ThreadLocal封裝的保存數(shù)據(jù)源上線的上下文context
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<String> context = new ThreadLocal<>();

    /**
     * 賦值
     *
     * @param datasourceType
     */
    public static void set(String datasourceType) {
        context.set(datasourceType);
    }

    /**
     * 獲取值
     * @return
     */
    public static String get() {
        return context.get();
    }

    public static void clear() {
        context.remove();
    }
}

四：切換注解和 Aop 配置

首先我們來定義一個(gè)@DataSourceSwitcher 注解,擁有兩個(gè)屬性

① 當(dāng)前的數(shù)據(jù)源② 是否清除當(dāng)前的數(shù)據(jù)源，并且只能放在方法上，(不可以放在類上，也沒必要放在類上，因?yàn)槲覀冊谶M(jìn)行數(shù)據(jù)源切換的時(shí)候肯定是方法操作)，該注解的主要作用就是進(jìn)行數(shù)據(jù)源的切換，在 dao 層進(jìn)行操作數(shù)據(jù)庫的時(shí)候，可以在方法上注明表示的是當(dāng)前使用哪個(gè)數(shù)據(jù)源。

@DataSourceSwitcher 注解的定義:

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSourceSwitcher {
    /**
     * 默認(rèn)數(shù)據(jù)源
     * @return
     */
    DataSourceEnum value() default DataSourceEnum.MASTER;
    /**
     * 清除
     * @return
     */
    boolean clear() default true;

}

DataSourceAop配置：

為了賦予@DataSourceSwitcher 注解能夠切換數(shù)據(jù)源的能力，我們需要使用 AOP，然后使用@Aroud 注解找到方法上有@DataSourceSwitcher.class 的方法，然后取注解上配置的數(shù)據(jù)源的值，設(shè)置到 DataSourceContextHolder 中，就實(shí)現(xiàn)了將當(dāng)前方法上配置的數(shù)據(jù)源注入到全局作用域當(dāng)中。

@Slf4j
@Aspect
@Order(value = 1)
@Component
public class DataSourceContextAop {

    @Around("@annotation(com.wyq.mysqlreadwriteseparate.annotation.DataSourceSwitcher)")
    public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {
        boolean clear = false;
        try {
            Method method = this.getMethod(pjp);
            DataSourceSwitcher dataSourceSwitcher = method.getAnnotation(DataSourceSwitcher.class);
            clear = dataSourceSwitcher.clear();
            DataSourceContextHolder.set(dataSourceSwitcher.value().getDataSourceName());
            log.info("數(shù)據(jù)源切換至：{}", dataSourceSwitcher.value().getDataSourceName());
            return pjp.proceed();
        } finally {
            if (clear) {
                DataSourceContextHolder.clear();
            }

        }
    }

    private Method getMethod(JoinPoint pjp) {
        MethodSignature signature = (MethodSignature) pjp.getSignature();
        return signature.getMethod();
    }

}

五：用法以及測試

在配置好了讀寫分離之后,就可以在代碼中使用了，一般而言我們使用在 service 層或者 dao 層，在需要查詢的方法上添加@DataSourceSwitcher(DataSourceEnum.SLAVE)，它表示該方法下所有的操作都走的是讀庫。在需要 update 或者 insert 的時(shí)候使用@DataSourceSwitcher(DataSourceEnum.MASTER)表示接下來將會走寫庫。

其實(shí)還有一種更為自動的寫法,可以根據(jù)方法的前綴來配置 AOP 自動切換數(shù)據(jù)源，比如 update、insert、fresh 等前綴的方法名一律自動設(shè)置為寫庫。select、get、query 等前綴的方法名一律配置為讀庫，這是一種更為自動的配置寫法。缺點(diǎn)就是方法名需要按照 aop 配置的嚴(yán)格來定義，否則就會失效。

@Service
public class OrderService {

    @Resource
    private OrderMapper orderMapper;


    /**
     * 讀操作
     *
     * @param orderId
     * @return
     */
    @DataSourceSwitcher(DataSourceEnum.SLAVE)
    public List<Order> getOrder(String orderId) {
        return orderMapper.listOrders(orderId);

    }

    /**
     * 寫操作
     *
     * @param orderId
     * @return
     */
    @DataSourceSwitcher(DataSourceEnum.MASTER)
    public List<Order> insertOrder(Long orderId) {
        Order order = new Order();
        order.setOrderId(orderId);
        return orderMapper.saveOrder(order);
    }
}

六：總結(jié)

還是畫張圖來簡單總結(jié)一下：

This article introduces how to achieve database read-write separation. Note that the core point of read-write separation is data routing. You need to inherit AbstractRoutingDataSource and overwrite its determineCurrentLookupKey. ()method. At the same time, you need to pay attention to the global context manager DataSourceContextHolder, which is the main class that saves the data source context and is also the data source value found in the routing method. It is equivalent to a transfer station for data sources, and combined with the bottom layer of jdbc-Template to create and manage data sources, transactions, etc., our database read-write separation is perfectly realized.