Part1前言

本篇分享數(shù)據(jù)庫主從方案，案例采用springboot+mysql+mybatis演示；要想在代碼中做主從選擇，通常需要明白什么時候切換數(shù)據(jù)源，怎么切換數(shù)據(jù)源，下面以代碼示例來做闡述；

• 搭建測試環(huán)境(1個master庫2個slave庫)
• DataSource多數(shù)據(jù)源配置
• 設置mybatis數(shù)據(jù)源
• 攔截器+注解設置master和slave庫選擇
• 選出當前請求要使用的slave從庫
• 測試用例

Part2搭建測試環(huán)境

(1個master庫2個slave庫) 由于測試資源優(yōu)先在本地模擬創(chuàng)建3個數(shù)據(jù)庫，分別是1個master庫2個slave庫，里面分別都有一個tblArticle表，內(nèi)容也大致相同(為了演示主從效果，我把從庫中表的title列值增加了slave字樣)：

再來創(chuàng)建一個db.properties，分別配置3個數(shù)據(jù)源，格式如下：

spring.datasource0.jdbc-url=jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource0.username=root
spring.datasource0.password=123456
spring.datasource0.driver-class-name=com.mysql.jdbc.Driver

spring.datasource1.jdbc-url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource1.username=root
spring.datasource1.password=123456
spring.datasource1.driver-class-name=com.mysql.jdbc.Driver

spring.datasource2.jdbc-url=jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource2.username=root
spring.datasource2.password=123456
spring.datasource2.driver-class-name=com.mysql.jdbc.Driver

同時我們創(chuàng)建具有對應關系的DbType枚舉，幫助我們使代碼更已讀：

public?class?DbEmHelper?{
????public?enum?DbTypeEm?{
????????db0(0,?"db0(默認master)",?-1),
????????db1(1,?"db1",?0),
????????db2(2,?"db2",?1);

????????/**
?????????*?用于篩選從庫
?????????*
?????????*?@param?slaveNum?從庫順序編號?0開始
?????????*?@return
?????????*/
????????public?static?Optional?getDbTypeBySlaveNum(int?slaveNum)?{
????????????return?Arrays.stream(DbTypeEm.values()).filter(b?->?b.getSlaveNum()?==?slaveNum).findFirst();
????????}

????????DbTypeEm(int?code,?String?des,?int?slaveNum)?{
????????????this.code?=?code;
????????????this.des?=?des;
????????????this.slaveNum?=?slaveNum;
????????}

????????private?int?code;
????????private?String?des;
????????private?int?slaveNum;

????????//get,set省略
????}
}

Part3DataSource多數(shù)據(jù)源配置

使用上面3個庫連接串信息，配置3個不同的DataSource實例，達到多個DataSource目的；由于在代碼中庫的實例需要動態(tài)選擇，因此我們利用AbstractRoutingDataSource來聚合多個數(shù)據(jù)源；下面是生成多個DataSource代碼：

@Configuration
public?class?DbConfig?{

????@Bean(name?=?"dbRouting")
????public?DataSource?dbRouting()?throws?IOException?{
????????//加載db配置文件
????????InputStream?in?=?this.getClass().getClassLoader().getResourceAsStream("db.properties");
????????Properties?pp?=?new?Properties();
????????pp.load(in);

????????//創(chuàng)建每個庫的datasource
????????Map?targetDataSources?=?new?HashMap<>(DbEmHelper.DbTypeEm.values().length);
????????Arrays.stream(DbEmHelper.DbTypeEm.values()).forEach(dbTypeEm?->?{
????????????targetDataSources.put(dbTypeEm,?getDataSource(pp,?dbTypeEm));
????????});

????????//設置多數(shù)據(jù)源
????????DbRouting?dbRouting?=?new?DbRouting();
????????dbRouting.setTargetDataSources(targetDataSources);
????????return?dbRouting;
????}

????/**
?????*?創(chuàng)建庫的datasource
?????*
?????*?@param?pp
?????*?@param?dbTypeEm
?????*?@return
?????*/
????private?DataSource?getDataSource(Properties?pp,?DbEmHelper.DbTypeEm?dbTypeEm)?{
????????DataSourceBuilder?builder?=?DataSourceBuilder.create();

????????builder.driverClassName(pp.getProperty(JsonUtil.formatMsg("spring.datasource{}.driver-class-name",?dbTypeEm.getCode())));
????????builder.url(pp.getProperty(JsonUtil.formatMsg("spring.datasource{}.jdbc-url",?dbTypeEm.getCode())));
????????builder.username(pp.getProperty(JsonUtil.formatMsg("spring.datasource{}.username",?dbTypeEm.getCode())));
????????builder.password(pp.getProperty(JsonUtil.formatMsg("spring.datasource{}.password",?dbTypeEm.getCode())));

????????return?builder.build();
????}
}

能夠看到一個DbRouting實例，其是繼承了AbstractRoutingDataSource，她里面有個Map變量來存儲多個數(shù)據(jù)源信息：

public?class?DbRouting?extends?AbstractRoutingDataSource?{

????@Override
????protected?Object?determineCurrentLookupKey()?{
????????return?DbContextHolder.getDb().orElse(DbEmHelper.DbTypeEm.db0);
????}
}

DbRouting里面主要重寫了determineCurrentLookupKey(),通過設置和存儲DataSource集合的Map相同的key，以此達到選擇不同DataSource的目的，這里使用ThreadLocal獲取同一線程存儲的key；主要看AbstractRoutingDataSource類中下面代碼：

protected?DataSource?determineTargetDataSource()?{
????????Assert.notNull(this.resolvedDataSources,?"DataSource?router?not?initialized");
????????Object?lookupKey?=?this.determineCurrentLookupKey();
????????DataSource?dataSource?=?(DataSource)this.resolvedDataSources.get(lookupKey);
????????if(dataSource?==?null?&&?(this.lenientFallback?||?lookupKey?==?null))?{
????????????dataSource?=?this.resolvedDefaultDataSource;
????????}
????????if(dataSource?==?null)?{
????????????throw?new?IllegalStateException("Cannot?determine?target?DataSource?for?lookup?key?["?+?lookupKey?+?"]");
????????}?else?{
????????????return?dataSource;
????????}
????}

Part4設置mybatis數(shù)據(jù)源

本次演示為了便利，這里使用mybatis的注解方式來查詢數(shù)據(jù)庫，我們需要給mybatis設置數(shù)據(jù)源，我們可以從上面的聲明DataSource的bean方法獲取：

@EnableTransactionManagement
@Configuration
public?class?MybaitisConfig?{
????@Resource(name?=?"dbRouting")
????DataSource?dataSource;

????@Bean
????public?SqlSessionFactory?sqlSessionFactory()?throws?Exception?{
????????SqlSessionFactoryBean?factoryBean?=?new?SqlSessionFactoryBean();
????????factoryBean.setDataSource(dataSource);
???????//?factoryBean.setMapperLocations(new?PathMatchingResourcePatternResolver().getResources("classpath:*"));
????????return?factoryBean.getObject();
????}
}

我們使用的mybatis注解方式來查詢數(shù)據(jù)庫，所以不需要加載mapper的xml文件，下面注解方式查詢sql：

@Mapper
public?interface?ArticleMapper?{
????@Select("select?*?from?tblArticle?where?id?=?#{id}")
????Article?selectById(int?id);
}

Part5攔截器+注解來選擇master和slave庫

通常操作數(shù)據(jù)的業(yè)務邏輯都放在service層，我們希望service中不同方法使用不同的庫；比如：添加、修改、刪除、部分查詢方法等，使用master主庫來操作，而大部分查詢操作可以使用slave庫來查詢；這里通過攔截器+靈活的自定義注解來實現(xiàn)我們的需求：

@Documented
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public?@interface?DbType?{
????boolean?isMaster()?default?true;
}

注解參數(shù)默認選擇master庫來操作業(yè)務(看具體需求吧)

@Aspect
@Component
public?class?DbInterceptor?{

????//全部service層請求都走這里，ThreadLocal才能有DbType值
????private?final?String?pointcut?=?"execution(*?com.sm.service..*.*(..))";

????@Pointcut(value?=?pointcut)
????public?void?dbType()?{
????}

????@Before("dbType()")
????void?before(JoinPoint?joinPoint)?{
????????System.out.println("before...");

????????MethodSignature?methodSignature?=?(MethodSignature)?joinPoint.getSignature();
????????Method?method?=?methodSignature.getMethod();
????????DbType?dbType?=?method.getAnnotation(DbType.class);
????????//設置Db
????????DbContextHolder.setDb(dbType?==?null???false?:?dbType.isMaster());
????}

????@After("dbType()")
????void?after()?{
????????System.out.println("after...");

????????DbContextHolder.remove();
????}
}

攔截器攔截service層的所有方法，然后獲取帶有自定義注解DbType的方法的isMaster值，DbContextHolder.setDb()方法判斷走master還是slave庫，并賦值給ThreadLocal：

public?class?DbContextHolder?{
????private?static?final?ThreadLocal>?dbTypeEmThreadLocal?=?new?ThreadLocal<>();
????private?static?final?AtomicInteger?atoCounter?=?new?AtomicInteger(0);

????public?static?void?setDb(DbEmHelper.DbTypeEm?dbTypeEm)?{
????????dbTypeEmThreadLocal.set(Optional.ofNullable(dbTypeEm));
????}

????public?static?Optional?getDb()?{
????????return?dbTypeEmThreadLocal.get();
????}

????public?static?void?remove()?{
????????dbTypeEmThreadLocal.remove();
????}

????/**
?????*?設置主從庫
?????*
?????*?@param?isMaster
?????*/
????public?static?void?setDb(boolean?isMaster)?{
????????if?(isMaster)?{
????????????//主庫
????????????setDb(DbEmHelper.DbTypeEm.db0);
????????}?else?{
????????????//從庫
????????????setSlave();
????????}
????}

????private?static?void?setSlave()?{
????????//累加值達到最大時，重置
????????if?(atoCounter.get()?>=?100000)?{
????????????atoCounter.set(0);
????????}

????????//排除master，選出當前線程請求要使用的db從庫?-?從庫算法
????????int?slaveNum?=?atoCounter.getAndIncrement()?%?(DbEmHelper.DbTypeEm.values().length?-?1);
????????Optional?dbTypeEm?=?DbEmHelper.DbTypeEm.getDbTypeBySlaveNum(slaveNum);
????????if?(dbTypeEm.isPresent())?{
????????????setDb(dbTypeEm.get());
????????}?else?{
????????????throw?new?IllegalArgumentException("從庫未匹配");
????????}
????}
}

這一步驟很重要，通過攔截器來到達選擇master和slave目的，當然也有其他方式的；

Part6選出當前請求要使用的slave從庫

上面能選擇出master和slave走向了，但是往往slave至少有兩個庫存在；我們需要知道怎么來選擇多個slave庫，目前最常用的方式通過計數(shù)器取余的方式來選擇：

private?static?void?setSlave()?{
????????//累加值達到最大時，重置
????????if?(atoCounter.get()?>=?100000)?{
????????????atoCounter.set(0);
????????}

????????//排除master，選出當前線程請求要使用的db從庫?-?從庫算法
????????int?slaveNum?=?atoCounter.getAndIncrement()?%?(DbEmHelper.DbTypeEm.values().length?-?1);
????????Optional?dbTypeEm?=?DbEmHelper.DbTypeEm.getDbTypeBySlaveNum(slaveNum);
????????if?(dbTypeEm.isPresent())?{
????????????setDb(dbTypeEm.get());
????????}?else?{
????????????throw?new?IllegalArgumentException("從庫未匹配");
????????}
????}

這里根據(jù)余數(shù)來匹配對應DbType枚舉，選出DataSource的Map需要的key，并且賦值到當前線程ThreadLocal中；

/**
*?用于篩選從庫4??????????*?@param?slaveNum?從庫順序編號?0開始
*?@return
*/
public?static?Optional?getDbTypeBySlaveNum(int?slaveNum)?{
??return?Arrays.stream(DbTypeEm.values()).filter(b?->?b.getSlaveNum()?==?slaveNum).findFirst();
}

Part7測試用例

完成上面操作后，我們搭建個測試例子，ArticleService中分別如下3個方法，不同點在于@DbType注解的標記：

@Service
public?class?ArticleService?{

????@Autowired
????ArticleMapper?articleMapper;

????@DbType
????public?Article?selectById01(int?id)?{
????????Article?article?=?articleMapper.selectById(id);
????????System.out.println(JsonUtil.formatMsg("selectById01：{}?--- title：{}",?DbContextHolder.getDb().get(),?article.getTitle()));
????????return?article;
????}

????@DbType(isMaster?=?false)
????public?Article?selectById02(int?id)?{
????????Article?article?=?articleMapper.selectById(id);
????????System.out.println(JsonUtil.formatMsg("selectById02：{}?--- title：{}",?DbContextHolder.getDb().get(),?article.getTitle()));
????????return?article;
????}

????public?Article?selectById(int?id)?{
????????Article?article?=?articleMapper.selectById(id);
????????System.out.println(JsonUtil.formatMsg("selectById：{}?--- title：{}",?DbContextHolder.getDb().get(),?article.getTitle()));
????????return?article;
????}
}

在同一個Controller層接口方法中去調(diào)用這3個service層方法，按照正常邏輯來講，不出意外得到的結(jié)果是這樣：請求了兩次接口，得到結(jié)果是：selectById01方法：標記了@DbType，但默認走isMaster=true，實際走了db0(master)庫 selectById02方法：標記了@DbType(isMaster = false)，實際走了db1(slave1)庫 selectById方法：沒有標記了@DbType，實際走了db2(slave2)庫，因為攔截器中沒有找到DbType注解，讓其走了slave方法；因為selectById02執(zhí)行過一次slave方法，計數(shù)器+1了，因此余數(shù)也變了所以定位到了slave2庫(如果是基數(shù)調(diào)用，selectById02和selectById方法來回切換走不同slave庫)；