Spingcloud下使用shardingjdbc进行读写分离，并通过注解和Aspect实现读主库

【背景】：最近生产环境上一个产品经过大半年运行，报表查询的速度变慢了，为了避免对写操作造成影响，决定进行读写分离升级，

报表查询和对主从同步延迟无特殊要求的查询走从库，不适用从库主从同步延迟的查询继续走主库。

【选型】：对比了几个主流的读写分离方案，决定选用shardingjdbc进行读写分离。主要考虑其已经被Apache收录，开源性好，并且对现有业务代码的侵入性较小，既有程序改动量较小。

【思路】：使用shardingjdbc进行读写分离。使用注解+Aspect的方法支持主库查询，并且查询完成后取消强制路由，后续查询继续到从库。

下面对开发时的几个关键步骤进行一下记录，供大家参看，自己将来回顾时也可以利用利用。

1）引入shardingspere，下面是pom的关键依赖：

    <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.30</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>

 

2)通过druid数据库连接池管理多个数据源，配置主从数据库。作为实验，1主2从都在本地机器上。yml的关键配置：

sharding:
  jdbc:
    dataSource:
      names: masterdb,slavedb01,slavedb02
      masterdb:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/mcspcsales?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
      slavedb01:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/mcspcsales1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
      slavedb02:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/mcspcsales2?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
    config:
      masterslave:
        load-balance-algorithm-type: round_robin 
        name: mcspcsalesMaster1Slave2
        master-data-source-name: masterdb
        slave-data-source-names: slavedb01,slavedb02
    props:
      sql:
        show: true

3）自定义读主库的注解类：

package com.chong.common.annotation;

import java.lang.annotation.*;

@Target(value = {ElementType.METHOD})
@Retention(value = RetentionPolicy.RUNTIME)
@Documented
public @interface MasterSelect {
}

4)aspect类，有注解MasterSelect方法，在方法执行前设置主库查询设置 HintManager.getInstance().setMasterRouteOnly();
业务方法执行后执行HintmanagerHolder.clear()，取消对主库查询的强制路由。

@Aspect
@Component
public class MasterSelectAspect {

    @Pointcut(value = "execution(* com.chong.mcspcreadwritesplit.service.*.*(..))")
    public void pointcutOnService() {
    }

    @Around(value = "pointcutOnService()")
    public Object setMasterSelect(ProceedingJoinPoint joinPoint) throws Throwable {
        Object object = null;
        Throwable currentThrowable = null;
        MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
        if (methodSignature.getMethod().isAnnotationPresent(MasterSelect.class)) {
            HintManager.getInstance().setMasterRouteOnly();
        }
        try {
            object = joinPoint.proceed();
        } catch (Throwable throwable) {
            currentThrowable = throwable;
        } finally {
            HintManagerHolder.clear();
            if (currentThrowable != null) {
                throw currentThrowable;
            }
        }
        return object;
    }
}

5）service使用，对于需要主库查询的语句，方法上增加注解MasterSelect。

@Service
public class MemberRepositoryService {

    @Autowired
    private MemberRepository memberRepository;

    @Autowired
    private IdWorker idWorker;

    @MasterSelect
    public List<BizMember> getMemberList() {
        List<BizMember> list = null;
        list = memberRepository.findAll();
        return list;
    }
  // 下略
}

6）下面是启动类，有个点需要注意，因为sharding-jdbc-spring-boot-starter和druid-spring-boot-starter都去进行datasource的自动配置，所以启动类中会提示重复的bean定义。
在启动类里，把DruidDataSourceAutoConfiure的自动配置去掉，就能正常启动了。

@SpringBootApplication(exclude={DruidDataSourceAutoConfigure.class})
@EnableDiscoveryClient
@EnableConfigurationProperties
@EnableTransactionManagement
@ComponentScan(basePackages = {"com.chong.common","com.chong.mcspcreadwritesplit"})
public class McspcreadwritesplitApplication {

    public static void main(String[] args) {
        SpringApplication.run(McspcreadwritesplitApplication.class, args);
    }

}

Controller就不贴了。有上面这几个核心部分代码，就能支持读写分离了。亲测可用。^^