springboot+mybatis实现数据库的读写分离

柔情只为你懂 2022-10-30 02:25 32阅读 0赞

![format_png][]

点击上方蓝字关注我们

##  ##

介绍

随着业务的发展，除了拆分业务模块外，数据库的读写分离也是常见的优化手段。方案使用了AbstractRoutingDataSource和mybatis plugin来动态的选择数据源 选择这个方案的原因主要是不需要改动原有业务代码，非常友好

注：
    demo中使用了mybatis-plus，实际使用mybatis也是一样的
    demo中使用的数据库是postgres，实际任一类型主从备份的数据库示例都是一样的
    demo中使用了alibaba的druid数据源，实际其他类型的数据源也是一样的

###     ###

##  ##

环境

首先，我们需要两个数据库实例，一为master，一为slave。所有的写操作，我们在master节点上操作 所有的读操作，我们在slave节点上操作

•需要注意的是：对于一次有读有写的事务，事务内的读操作也不应该在slave节点上，所有操作都应该在master节点上

先跑起来两个pg的实例，其中15432端口对应的master节点，15433端口对应的slave节点：

docker run \
        --name pg-master \
        -p 15432:5432 \
        --env 'PG_PASSWORD=postgres' \
        --env 'REPLICATION_MODE=master' \
        --env 'REPLICATION_USER=repluser' \
           --env 'REPLICATION_PASS=repluserpass' \
        -d sameersbn/postgresql:10-2
    
    docker run \
        --name pg-slave \
        -p 15433:5432 \
        --link pg-master:master \
        --env 'PG_PASSWORD=postgres' \
        --env 'REPLICATION_MODE=slave' \
        --env 'REPLICATION_SSLMODE=prefer' \
        --env 'REPLICATION_HOST=master' \
        --env 'REPLICATION_PORT=5432' \
        --env 'REPLICATION_USER=repluser' \
           --env 'REPLICATION_PASS=repluserpass' \
        -d sameersbn/postgresql:10-2

###     ###

##  ##

实现

整个实现主要有3个部分：

###     ###

##  ##

配置两个数据源

实现AbstractRoutingDataSource来动态的使用数据源 实现mybatis plugin来动态的选择数据源 配置数据源 将数据库连接信息配置到application.yml文件中

spring:
      mvc:
        servlet:
          path: /api
    
    datasource:
      write:
        driver-class-name: org.postgresql.Driver
        url: "${DB_URL_WRITE:jdbc:postgresql://localhost:15432/postgres}"
        username: "${DB_USERNAME_WRITE:postgres}"
        password: "${DB_PASSWORD_WRITE:postgres}"
      read:
        driver-class-name: org.postgresql.Driver
        url: "${DB_URL_READ:jdbc:postgresql://localhost:15433/postgres}"
        username: "${DB_USERNAME_READ:postgres}"
        password: "${DB_PASSWORD_READ:postgres}"
    
    
    mybatis-plus:
      configuration:
        map-underscore-to-camel-case: true

write写数据源，对应到master节点的15432端口 read读数据源，对应到slave节点的15433端口

将两个数据源信息注入为DataSourceProperties：

@Configuration
    public class DataSourcePropertiesConfig {
    
        @Primary
        @Bean("writeDataSourceProperties")
        @ConfigurationProperties("datasource.write")
        public DataSourceProperties writeDataSourceProperties() {
            return new DataSourceProperties();
        }
    
        @Bean("readDataSourceProperties")
        @ConfigurationProperties("datasource.read")
        public DataSourceProperties readDataSourceProperties() {
            return new DataSourceProperties();
        }
    }

###     ###

##  ##

实现AbstractRoutingDataSource

spring提供了AbstractRoutingDataSource，提供了动态选择数据源的功能，替换原有的单一数据源后，即可实现读写分离:

@Component
    public class CustomRoutingDataSource extends AbstractRoutingDataSource {
    
        @Resource(name = "writeDataSourceProperties")
        private DataSourceProperties writeProperties;
    
        @Resource(name = "readDataSourceProperties")
        private DataSourceProperties readProperties;
    
    
        @Override
        public void afterPropertiesSet() {
            DataSource writeDataSource = 
                writeProperties.initializeDataSourceBuilder().type(DruidDataSource.class).build();
            DataSource readDataSource = 
                readProperties.initializeDataSourceBuilder().type(DruidDataSource.class).build();
    
            setDefaultTargetDataSource(writeDataSource);
    
            Map<Object, Object> dataSourceMap = new HashMap<>();
            dataSourceMap.put(WRITE_DATASOURCE, writeDataSource);
            dataSourceMap.put(READ_DATASOURCE, readDataSource);
            setTargetDataSources(dataSourceMap);
    
            super.afterPropertiesSet();
        }
    
        @Override
        protected Object determineCurrentLookupKey() {
            String key = DataSourceHolder.getDataSource();
    
            if (key == null) {
                 // default datasource
                return WRITE_DATASOURCE;
            }
    
            return key;
        }
    
    }

AbstractRoutingDataSource内部维护了一个Map<Object, Object>的Map 在初始化过程中，我们将write、read两个数据源加入到这个map 调用数据源时：determineCurrentLookupKey()方法返回了需要使用的数据源对应的key

当前线程需要使用的数据源对应的key，是在DataSourceHolder类中维护的：

public class DataSourceHolder {
    
        public static final String WRITE_DATASOURCE = "write";
        public static final String READ_DATASOURCE = "read";
    
        private static final ThreadLocal<String> local = new ThreadLocal<>();
    
    
        public static void putDataSource(String dataSource) {
            local.set(dataSource);
        }
    
        public static String getDataSource() {
            return local.get();
        }
    
        public static void clearDataSource() {
            local.remove();
        }
    
    }

###     ###

##  ##

实现mybatis plugin

上面提到了当前线程使用的数据源对应的key，这个key需要在mybatis plugin根据sql类型来确定 MybatisDataSourceInterceptor类：

@Component
    @Intercepts({
            @Signature(type = Executor.class, method = "update",
                    args = {MappedStatement.class, Object.class}),
            @Signature(type = Executor.class, method = "query",
                    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
            @Signature(type = Executor.class, method = "query",
                    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class,
                            CacheKey.class, BoundSql.class})})
    public class MybatisDataSourceInterceptor implements Interceptor {
    
        @Override
        public Object intercept(Invocation invocation) throws Throwable {
    
            boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
            if(!synchronizationActive) {
                Object[] objects = invocation.getArgs();
                MappedStatement ms = (MappedStatement) objects[0];
    
                if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                    DataSourceHolder.putDataSource(DataSourceHolder.READ_DATASOURCE);
                }
            }
    
            return invocation.proceed();
        }
    
        @Override
        public Object plugin(Object target) {
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) {
        }
    }

复制代码仅当未在事务中，并且调用的sql是select类型时，在DataSourceHolder中将数据源设为read 其他情况下，AbstractRoutingDataSource会使用默认的write数据源

至此，项目已经可以自动的在读、写数据源间切换，无需修改原有的业务代码 最后，提供demo使用依赖版本

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> 
    </parent>
    
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatisplus-spring-boot-starter</artifactId>
            <version>1.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>2.1.9</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
        </dependency>
    
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

[![format_png 1][]][format_png 1 1]

[ZooKeeper典型应用场景][format_png 1 1]

[![format_png 2][]][format_png 2 1]

[用Redis实现一个轻量级的搜索引擎][format_png 2 1]

[![format_png 3][]][format_png 3 1]

[Spring Boot如何利用AOP巧妙记录操作日志？][format_png 3 1]

[![format_png 4][]][format_png 4 1]

[Spring Boot接口如何做限流][format_png 4 1]

![format_png 5][]

![format_png 6][]

戳这儿

[format_png]: /images/20221024/ed8ce81b3b884f5b93d6d1ea59b19b84.png
[format_png 1]: /images/20221024/994d185d5fdd424bba81f7fc1891254b.png
[format_png 1 1]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44fe49cb33775f115a52aa902244c0eb00095c50e940f0102b72437cad11a8e4dcc3a5d667&idx=1&mid=2247486320&scene=21&sn=5e7685fa64ac20fef4d9dca35f62e241#wechat_redirect
[format_png 2]: /images/20221024/349097a1eaa147f9a57e244d4ee3c51a.png
[format_png 2 1]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44fe70cb337766a85ad8eee20d7d861e534a528780fd53beaf852dfd261e004b5acaeb6258&idx=1&mid=2247486281&scene=21&sn=afd288baec01209f80c7e9d6eeb1c1ee#wechat_redirect
[format_png 3]: /images/20221024/564f637eda154de5af8c64205f7e5f42.png
[format_png 3 1]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44fe15cb33770355a0874139f407def9e1093c88dfd5b9a7011222f9988de37d2d90d8963a&idx=1&mid=2247486252&scene=21&sn=5cf32346e7bc4d6a325a4b7fbb2bf96d#wechat_redirect
[format_png 4]: /images/20221024/66f06d6f2c924a5f9d45cf05d8a5a1a0.png
[format_png 4 1]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44ffc8cb3376de37b629bd408e5d034af66e6c1e238e0f15fcf73e56d696684207dd02c236&idx=1&mid=2247486193&scene=21&sn=ca6455758cdaaf65e56e614825d768d0#wechat_redirect
[format_png 5]: /images/20221024/d9c9663c56c54380a7e14fc6f5f3defc.png
[format_png 6]: /images/20221024/e87d1f2c0a5541f3bb21c741f35812af.png