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