Part1前言

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

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

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

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

再來(lái)創(chuàng)建一個(gè)db.properties，分別配置3個(gè)數(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

同時(shí)我們創(chuàng)建具有對(duì)應(yīng)關(guān)系的DbType枚舉，幫助我們使代碼更已讀：

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

????????/**
?????????*?用于篩選從庫(kù)
?????????*
?????????*?@param?slaveNum?從庫(kù)順序編號(hào)?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個(gè)庫(kù)連接串信息，配置3個(gè)不同的DataSource實(shí)例，達(dá)到多個(gè)DataSource目的；由于在代碼中庫(kù)的實(shí)例需要?jiǎng)討B(tài)選擇，因此我們利用AbstractRoutingDataSource來(lái)聚合多個(gè)數(shù)據(jù)源；下面是生成多個(gè)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)建每個(gè)庫(kù)的datasource
????????Map?targetDataSources?=?new?HashMap<>(DbEmHelper.DbTypeEm.values().length);
????????Arrays.stream(DbEmHelper.DbTypeEm.values()).forEach(dbTypeEm?->?{
????????????targetDataSources.put(dbTypeEm,?getDataSource(pp,?dbTypeEm));
????????});

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

????/**
?????*?創(chuàng)建庫(kù)的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();
????}
}

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

public?class?DbRouting?extends?AbstractRoutingDataSource?{

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

DbRouting里面主要重寫了determineCurrentLookupKey(),通過設(shè)置和存儲(chǔ)DataSource集合的Map相同的key，以此達(dá)到選擇不同DataSource的目的，這里使用ThreadLocal獲取同一線程存儲(chǔ)的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設(shè)置mybatis數(shù)據(jù)源

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

@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注解方式來(lái)查詢數(shù)據(jù)庫(kù)，所以不需要加載mapper的xml文件，下面注解方式查詢sql：

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

Part5攔截器+注解來(lái)選擇master和slave庫(kù)

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

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

注解參數(shù)默認(rèn)選擇master庫(kù)來(lái)操作業(yè)務(wù)(看具體需求吧)

@Aspect
@Component
public?class?DbInterceptor?{

????//全部service層請(qǐng)求都走這里，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);
????????//設(shè)置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庫(kù)，并賦值給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();
????}

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

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

????????//排除master，選出當(dāng)前線程請(qǐng)求要使用的db從庫(kù)?-?從庫(kù)算法
????????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("從庫(kù)未匹配");
????????}
????}
}

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

Part6選出當(dāng)前請(qǐng)求要使用的slave從庫(kù)

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

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

????????//排除master，選出當(dāng)前線程請(qǐng)求要使用的db從庫(kù)?-?從庫(kù)算法
????????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("從庫(kù)未匹配");
????????}
????}

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

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

Part7測(cè)試用例

完成上面操作后，我們搭建個(gè)測(cè)試?yán)樱珹rticleService中分別如下3個(gè)方法，不同點(diǎn)在于@DbType注解的標(biāo)記：

@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;
????}
}

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