springboot+mysql读写分离（一）

系统在设计之初就应该对业务数据的耦合松紧进行考量，从而进行垂直分库、垂直分表，使数据层架构清晰明了。 若非必要，无需进行水平切分，应先从缓存技术着手降低对数据库的访问压力。如果缓存使用过后，数据库访问量还是非常大，可以考虑数据库读、写分离原则。若当前数据库压力依然大，且业务数据持续增长无法估量，最后可考虑水平分库、分表，单表拆分数据控制在1000万以内。
读写分离？读一个库，写一个库，依赖于mysql主从模式，可理解成是分库的一种业务场景（分库策略）。
读写分离可以通过sping的路由数据源和切面方式实现，也可以通过数据库中间件的方式实现，如sharding方式、MyCat方式，下面实战spring路由数据源+切面的方式，直接上代码。
pom.xml文件

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

application.yml文件

server:
  port: 9001

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://localhost:3306/write
      username: root   # 读写账户
      password: 
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave1:
      jdbc-url: jdbc:mysql://localhost:3306/read1
      username: root   # 只读账户
      password: 
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave2:
      jdbc-url: jdbc:mysql://localhost:3306/read2
      username: root   # 只读账户
      password: 
      driver-class-name: com.mysql.cj.jdbc.Driver

读写分离核心代码

注解
public @interface Master {
}
切面
@Aspect
@Component
public class DataSourceAop {

    @Pointcut("!@annotation(com..annotation.Master) " +
            "&& (execution(* com.service..*.find*(..)) " +
            "|| execution(* com.service..*.get*(..)))")
    public void readPointcut() {

    }

    @Pointcut("@annotation(com.ldc.springboot_mysql1.annotation.Master) " +
            "|| execution(* com.service..*.insert*(..)) " +
            "|| execution(* com.service..*.add*(..)) " +
            "|| execution(* com.service..*.update*(..)) " +
            "|| execution(* com.service..*.edit*(..)) " +
            "|| execution(* com.service..*.delete*(..)) " +
            "|| execution(* com.service..*.remove*(..))")
    public void writePointcut() {

    }

    @Before("readPointcut()")
    public void read() {
        DataSourceContextHolder.setSlave();
    }

    @Before("writePointcut()")
    public void write() {
        DataSourceContextHolder.setMaster();
    }

}
枚举
public enum DataSourceTypeEnum {

    MASTER, SLAVE1, SLAVE2;

}
数据源
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                          @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        RoutingDataSource routingDataSource = new RoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DataSourceTypeEnum.SLAVE1, slave1DataSource);
        targetDataSources.put(DataSourceTypeEnum.SLAVE2, slave2DataSource);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        routingDataSource.setTargetDataSources(targetDataSources);
        return routingDataSource;
    }

}
事务
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {

    @Resource(name = "routingDataSource")
    private DataSource routingDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(routingDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(routingDataSource);
    }
}
数据源上下文
public class DataSourceContextHolder {

    private static final ThreadLocal<DataSourceTypeEnum> contextHolder = new ThreadLocal<>();

    private static final AtomicInteger counter = new AtomicInteger(-1);

    public static void set(DataSourceTypeEnum dbType) {
        contextHolder.set(dbType);
    }

    public static DataSourceTypeEnum get() {
        return contextHolder.get();
    }

    public static void setMaster() {
        set(DataSourceTypeEnum.MASTER);
        System.out.println("切换到master");
    }

    public static void setSlave() {
        //  轮询
        int index = counter.getAndIncrement() % 2;
        if (counter.get() > 9999) {
            counter.set(-1);
        }
        if (index == 0) {
            set(DataSourceTypeEnum.SLAVE1);
            System.out.println("切换到slave1");
        }else {
            set(DataSourceTypeEnum.SLAVE2);
            System.out.println("切换到slave2");
        }
    }

}
路由数据源
public class RoutingDataSource extends AbstractRoutingDataSource {

    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }

}

业务类及测试类

@Data
public class Student {

    private int id;

    private int num;

    private String name;

}
public interface StudentMapper {

    int insert(Student student);

    List<Student> findAll();
}
@Service
public class StudentService {

    @Autowired
    private StudentMapper studentMapper;

    @Transactional
    public int save(Student student) {
        return studentMapper.insert(student);
    }

    @Master
    public int insert(Student student) {
        return studentMapper.insert(student);
    }

    public List<Student> findAll() {
        return studentMapper.findAll();
    }

}
@Test
    public void testWrite() {
        Student student = new Student();
        student.setId(001);
        student.setNum(001);
        student.setName("caocao");
        studentService.insert(student);
    }

    @Test
    public void testRead() {
        for (int i = 0; i < 5; i++) {
            List<Student> studentList = studentService.findAll();
            System.out.println(studentList.get(0).getName());
        }
    }

    @Test
    public void testSave() {
        Student student = new Student();
        student.setName("testSave");
        studentService.save(student);
    }

测试结果
日志如：
切换到master
切换到slave1
切换到slave2