據(jù)源路由實(shí)戰(zhàn)：三層可控切換與分庫(kù)分表落地)
簡(jiǎn)介本資源是一份面向Java后端開(kāi)發(fā)者與數(shù)據(jù)庫(kù)架構(gòu)初學(xué)者的分庫(kù)分表實(shí)戰(zhàn)教學(xué)包聚焦企業(yè)級(jí)大數(shù)據(jù)量場(chǎng)景下的數(shù)據(jù)庫(kù)水平擴(kuò)展難題以Sharding-JDBC為核心框架實(shí)現(xiàn)多數(shù)據(jù)源動(dòng)態(tài)切換與透明化分片。資源共73個(gè)文件涵蓋18個(gè)Java業(yè)務(wù)與配置類、21個(gè)編譯后class文件、18個(gè)XML配置與映射文件含MyBatis映射及Sharding規(guī)則定義以及SQL建表腳本、Maven項(xiàng)目配置pom.xml、Eclipse工程元數(shù)據(jù).project/.classpath等關(guān)鍵開(kāi)發(fā)資產(chǎn)整體僅66KB輕量易導(dǎo)入。已有2068人學(xué)習(xí)下載適合快速搭建SSMDemo環(huán)境并理解分片路由、數(shù)據(jù)源初始化、YAML分片規(guī)則配置等核心實(shí)踐環(huán)節(jié)。讀者可直接復(fù)用項(xiàng)目結(jié)構(gòu)掌握從多數(shù)據(jù)源聲明、分庫(kù)分表策略配置到跨庫(kù)CRUD調(diào)用的完整鏈路尤其適用于SpringMyBatisSharding-JDBC技術(shù)棧的中小規(guī)模分片落地參考。1. 分庫(kù)分表多數(shù)據(jù)源的切換不是加個(gè)注解就完事而是讓每個(gè)DAO層調(diào)用都清楚自己該連哪張表、哪個(gè)庫(kù)你剛接手一個(gè)日增300萬(wàn)訂單的電商后臺(tái)MySQL單表已超2800萬(wàn)行慢查詢從“偶發(fā)”變成“每分鐘必報(bào)”DBA甩來(lái)一句“上分庫(kù)分表吧?！薄Y(jié)果你興沖沖引入ShardingSphere配置好邏輯表、分片鍵、分片算法一跑測(cè)試發(fā)現(xiàn)用戶中心查user_info正常但訂單服務(wù)里一個(gè)帶JOIN的報(bào)表SQL直接報(bào)錯(cuò)“無(wú)法路由到具體數(shù)據(jù)源”更玄學(xué)的是同一個(gè)Service方法里前半段insert走的是ds0后半段update卻連到了ds1事務(wù)根本沒(méi)法提交。這不是配置沒(méi)生效而是分庫(kù)分表的本質(zhì)從來(lái)不是“自動(dòng)拆分”而是“顯式聲明精準(zhǔn)控制”。這個(gè)項(xiàng)目就是一套可落地、可調(diào)試、可驗(yàn)證的多數(shù)據(jù)源切換實(shí)戰(zhàn)方案它不依賴黑匣子中間件的自動(dòng)路由而是用Spring原生AbstractRoutingDataSource 自定義ThreadLocal上下文 顯式數(shù)據(jù)源標(biāo)記把“哪個(gè)線程該用哪個(gè)庫(kù)”這件事從配置文件里拽出來(lái)寫進(jìn)代碼里、打在日志里、壓測(cè)時(shí)能一眼看穿。適合正在被跨庫(kù)JOIN、讀寫分離、歷史庫(kù)歸檔、灰度發(fā)布數(shù)據(jù)隔離等問(wèn)題卡住的后端開(kāi)發(fā)者尤其適合那些已經(jīng)踩過(guò)ShardingSphere規(guī)則配置陷阱、想回歸可控性的團(tuán)隊(duì)。2. 架構(gòu)設(shè)計(jì)與核心模塊拆解三層路由機(jī)制讓數(shù)據(jù)源選擇不再靠猜這個(gè)項(xiàng)目不是簡(jiǎn)單封裝了一個(gè)DynamicDataSource而是構(gòu)建了上下文驅(qū)動(dòng) 規(guī)則匹配 手動(dòng)覆蓋的三級(jí)路由體系。它把“用哪個(gè)庫(kù)”這件事拆成三個(gè)可獨(dú)立驗(yàn)證、可組合使用的環(huán)節(jié)最外層是業(yè)務(wù)語(yǔ)義標(biāo)記比如TargetDataSource(write)中間層是分片鍵自動(dòng)識(shí)別比如方法參數(shù)含userId且當(dāng)前策略為user_id分片最內(nèi)層是線程級(jí)強(qiáng)制綁定比如開(kāi)啟事務(wù)時(shí)鎖定主庫(kù)。三者優(yōu)先級(jí)從高到低沖突時(shí)以手動(dòng)標(biāo)記為準(zhǔn)。這種設(shè)計(jì)避免了“全局默認(rèn)庫(kù)”帶來(lái)的隱式耦合也繞開(kāi)了中間件對(duì)SQL語(yǔ)法的強(qiáng)約束——你寫原生MyBatis XML它照樣能路由你用JdbcTemplate手寫復(fù)雜UNION它也能按需切庫(kù)。整個(gè)結(jié)構(gòu)輕量無(wú)額外jar依賴、透明所有路由決策可打日志、可測(cè)試Mock ThreadLocal即可覆蓋全部分支。2.1 核心路由引擎AbstractRoutingDataSource的深度定制Spring原生的AbstractRoutingDataSource只提供一個(gè)determineCurrentLookupKey()抽象方法返回String類型的key。但本項(xiàng)目在此基礎(chǔ)上做了三處關(guān)鍵增強(qiáng)key不再是簡(jiǎn)單字符串而是自定義的DataSourceKey類包含type(READ/WRITE)、shardId(如shard_01)、sourceId(如ds_master_v2)三個(gè)維度determineCurrentLookupKey()內(nèi)部不直接返回key而是委托給RouterChain執(zhí)行鏈?zhǔn)脚袛嗝看蝛ey生成后自動(dòng)記錄到MDC中供日志追蹤如[ds_keyWRITE:shard_01:ds_master_v2]。public class DynamicRoutingDataSource extends AbstractRoutingDataSource { Override protected Object determineCurrentLookupKey() { // 1. 檢查線程局部變量是否已強(qiáng)制綁定 DataSourceKey forcedKey DataSourceContextHolder.getForcedKey(); if (forcedKey ! null) { log.debug(Route forced: {}, forcedKey); return forcedKey; } // 2. 嘗試從方法參數(shù)/注解提取分片上下文 DataSourceKey contextKey ContextRouter.routeByContext(); if (contextKey ! null) { log.debug(Route by context: {}, contextKey); return contextKey; } // 3. 回退到默認(rèn)策略如讀寫分離下的READ DataSourceKey defaultKey DefaultRouter.routeByDefault(); log.debug(Route by default: {}, defaultKey); return defaultKey; } }提示DataSourceKey必須實(shí)現(xiàn)equals()和hashCode()否則Spring緩存的targetDataSource Map會(huì)失效——這是血淚經(jīng)驗(yàn)曾因忘記重寫導(dǎo)致同一key反復(fù)創(chuàng)建新連接池DB連接數(shù)暴增。2.2 上下文管理器ThreadLocal MDC雙保險(xiǎn)DataSourceContextHolder是整個(gè)路由的“開(kāi)關(guān)中樞”。它不只存一個(gè)String而是維護(hù)三個(gè)ThreadLocal變量forcedKey: 用于Transactional或TargetDataSource注解觸發(fā)的強(qiáng)制綁定shardContext: 存儲(chǔ)當(dāng)前請(qǐng)求的分片標(biāo)識(shí)如userId12345 → shardIdshard_03routeTrace: 布爾值開(kāi)啟后自動(dòng)記錄每次路由決策路徑用于排查“為什么沒(méi)走預(yù)期庫(kù)”。public class DataSourceContextHolder { private static final ThreadLocalDataSourceKey FORCED_KEY_HOLDER new NamedThreadLocal(forcedDataSourceKey); private static final ThreadLocalShardContext SHARD_CONTEXT_HOLDER new NamedThreadLocal(shardContext); private static final ThreadLocalBoolean ROUTE_TRACE_HOLDER ThreadLocal.withInitial(() - false); public static void setForcedKey(DataSourceKey key) { FORCED_KEY_HOLDER.set(key); // 同步寫入MDC確保日志可追溯 MDC.put(ds_key, key.toString()); } public static void setShardContext(ShardContext context) { SHARD_CONTEXT_HOLDER.set(context); MDC.put(shard_id, context.getShardId()); MDC.put(shard_key, context.getShardKey()); } public static void enableRouteTrace() { ROUTE_TRACE_HOLDER.set(true); MDC.put(route_trace, enabled); } }NamedThreadLocal是自定義類繼承ThreadLocal并重寫toString()方便在Arthas或jstack線程dump中快速識(shí)別該ThreadLocal用途——這點(diǎn)在生產(chǎn)環(huán)境排查“某個(gè)線程死鎖在數(shù)據(jù)庫(kù)連接上”時(shí)極其關(guān)鍵。2.3 分片上下文注入從Controller到Mapper的全鏈路透?jìng)鞣制I如userId、orderId通常來(lái)自HTTP請(qǐng)求參數(shù)或RPC上下文不能靠DAO層自己猜。本項(xiàng)目采用“入口攔截顯式傳遞”模式Web層ShardParam(userId)注解標(biāo)注Controller方法參數(shù)由ShardArgumentResolver解析并注入ShardContextRPC層Dubbo Filter或gRPC Interceptor從Attachment或Metadata中提取分片信息內(nèi)部調(diào)用Service方法簽名強(qiáng)制要求傳入ShardContext杜絕“隱式上下文”。RestController public class OrderController { GetMapping(/order/{orderId}) public OrderVO getOrder( PathVariable Long orderId, ShardParam(userId) Long userId) { // ← 此注解觸發(fā)ShardContext注入 return orderService.getOrder(orderId, userId); } } Service public class OrderServiceImpl implements OrderService { Override public OrderVO getOrder(Long orderId, Long userId) { // 1. 主動(dòng)構(gòu)造ShardContext確保下游能路由 ShardContext context ShardContext.builder() .shardKey(userId) .shardValue(userId) .shardId(ShardAlgorithm.calculateShardId(userId)) // 如 userId % 4 → shard_02 .build(); DataSourceContextHolder.setShardContext(context); // 2. 執(zhí)行實(shí)際查詢此時(shí)DynamicRoutingDataSource已能獲取shardId return orderMapper.selectById(orderId); } }注意ShardAlgorithm.calculateShardId()必須是純函數(shù)無(wú)狀態(tài)、無(wú)外部依賴否則在多線程下可能產(chǎn)生不一致分片結(jié)果——這是分庫(kù)分表最隱蔽的翻車點(diǎn)之一。3. 數(shù)據(jù)源配置與初始化YAML驅(qū)動(dòng) 運(yùn)行時(shí)熱加載支持項(xiàng)目摒棄了XML硬編碼數(shù)據(jù)源的方式采用Spring Boot原生ConfigurationProperties綁定YAML配置同時(shí)預(yù)留了運(yùn)行時(shí)刷新接口如POST /actuator/datasource/refresh無(wú)需重啟即可更新數(shù)據(jù)源連接池參數(shù)。配置結(jié)構(gòu)清晰分層頂層定義邏輯組如master-slave-group中層定義物理實(shí)例如ds_master_v2底層定義JDBC屬性。這種結(jié)構(gòu)天然支持灰度發(fā)布場(chǎng)景——你可以先將新庫(kù)ds_master_v3加入配置但不啟用待壓測(cè)通過(guò)后再修改group.default-write-source: ds_master_v3。3.1 application.yml核心配置片段# 多數(shù)據(jù)源基礎(chǔ)配置 spring: datasource: dynamic: primary: master # 默認(rèn)寫庫(kù) strict: false # 非嚴(yán)格模式未匹配到數(shù)據(jù)源時(shí)回退到primary datasource: # 主庫(kù)寫 master: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-master:3306/order_db_v2?useSSLfalseserverTimezoneAsia/Shanghai username: app_rw password: ${DB_MASTER_PWD} hikari: maximum-pool-size: 20 minimum-idle: 5 # 從庫(kù)集群讀 slave-01: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-slave-01:3306/order_db_v2?useSSLfalseserverTimezoneAsia/Shanghai username: app_ro password: ${DB_SLAVE_PWD} hikari: maximum-pool-size: 15 slave-02: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-slave-02:3306/order_db_v2?useSSLfalseserverTimezoneAsia/Shanghai username: app_ro password: ${DB_SLAVE_PWD} hikari: maximum-pool-size: 15 # 分片庫(kù)歷史訂單 shard-01: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-shard-01:3306/order_history_shard_01?useSSLfalseserverTimezoneAsia/Shanghai username: app_hist password: ${DB_HIST_PWD} shard-02: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-shard-02:3306/order_history_shard_02?useSSLfalseserverTimezoneAsia/Shanghai username: app_hist password: ${DB_HIST_PWD}3.2 動(dòng)態(tài)數(shù)據(jù)源工廠支持運(yùn)行時(shí)新增/刪除數(shù)據(jù)源DynamicDataSourceFactory負(fù)責(zé)將YAML配置轉(zhuǎn)化為MapObject, DataSource并注冊(cè)到DynamicRoutingDataSource中。關(guān)鍵點(diǎn)在于使用ConcurrentHashMap存儲(chǔ)targetDataSources保證put/remove線程安全每次refresh時(shí)先關(guān)閉舊數(shù)據(jù)源調(diào)用HikariDataSource.close()再注入新實(shí)例提供getDataSource(String key)方法供其他組件如Druid監(jiān)控按需獲取特定數(shù)據(jù)源。Component public class DynamicDataSourceFactory { private final MapObject, DataSource targetDataSources new ConcurrentHashMap(); PostConstruct public void init() { loadFromConfig(); // 從YAML加載初始數(shù)據(jù)源 } public void refresh(MapString, DataSourceProperties newConfigs) { // 1. 關(guān)閉所有舊數(shù)據(jù)源 targetDataSources.values().forEach(this::closeDataSource); targetDataSources.clear(); // 2. 創(chuàng)建新數(shù)據(jù)源并注入 newConfigs.forEach((key, props) - { HikariDataSource ds createHikariDataSource(props); targetDataSources.put(key, ds); }); // 3. 通知DynamicRoutingDataSource更新 dynamicRoutingDataSource.afterPropertiesSet(); } private void closeDataSource(DataSource ds) { if (ds instanceof HikariDataSource) { ((HikariDataSource) ds).close(); } } }注意dynamicRoutingDataSource.afterPropertiesSet()必須顯式調(diào)用否則Spring不會(huì)重新初始化resolvedDataSources緩存——這是熱加載失敗最常見(jiàn)的原因。3.3 分片策略配置YAML定義規(guī)則Java實(shí)現(xiàn)算法分片邏輯不寫死在代碼里而是通過(guò)YAML聲明規(guī)則再由Java類按需加載。例如shard: rules: - table: order_info key: user_id algorithm: mod4 sources: [shard-01, shard-02, shard-03, shard-04] - table: order_detail key: order_id algorithm: hash16 sources: [shard-01, shard-02, shard-03, shard-04]對(duì)應(yīng)Java端ShardRuleLoader解析YAML后生成ShardRule對(duì)象存入內(nèi)存并由ShardAlgorithmFactory根據(jù)algorithm字段返回具體實(shí)現(xiàn)public interface ShardAlgorithm { String calculateShardId(Object shardValue); } Component public class Mod4ShardAlgorithm implements ShardAlgorithm { Override public String calculateShardId(Object shardValue) { if (!(shardValue instanceof Number)) { throw new IllegalArgumentException(Mod4 requires numeric shard value); } long value ((Number) shardValue).longValue(); int shardIndex (int) (Math.abs(value) % 4); // 避免負(fù)數(shù)取模問(wèn)題 return shard- String.format(%02d, shardIndex 1); // shard-01 ~ shard-04 } }這種解耦讓DBA可獨(dú)立修改分片規(guī)則改YAML而開(kāi)發(fā)只需維護(hù)算法實(shí)現(xiàn)雙方無(wú)需協(xié)同發(fā)布。4. 避坑指南5個(gè)真實(shí)踩過(guò)的坑省下你三天排查時(shí)間分庫(kù)分表不是配置完就能跑通的銀彈而是處處埋雷的精細(xì)活。以下是我在某高校實(shí)驗(yàn)室模擬項(xiàng)目X中連續(xù)兩周壓測(cè)時(shí)踩出的5個(gè)典型問(wèn)題每一條都附帶復(fù)現(xiàn)條件、根因分析和可立即驗(yàn)證的修復(fù)方案。4.1 現(xiàn)象事務(wù)內(nèi)跨庫(kù)操作不報(bào)錯(cuò)但部分SQL實(shí)際執(zhí)行在錯(cuò)誤庫(kù)上原因Transactional默認(rèn)傳播行為是REQUIRED當(dāng)方法A調(diào)用方法B時(shí)若B未聲明事務(wù)其數(shù)據(jù)源路由會(huì)脫離A的事務(wù)上下文導(dǎo)致B的SQL走默認(rèn)庫(kù)而非事務(wù)綁定庫(kù)。解決在Transactional方法內(nèi)所有子調(diào)用必須顯式聲明Transactional(propagation Propagation.REQUIRED)或統(tǒng)一使用TransactionTemplate手動(dòng)控制。更徹底的方案是在DataSourceInterceptor中檢測(cè)當(dāng)前是否在事務(wù)中若在則強(qiáng)制將forcedKey設(shè)為事務(wù)起始時(shí)的key。4.2 現(xiàn)象MyBatis二級(jí)緩存命中但返回的數(shù)據(jù)卻是舊庫(kù)里的臟數(shù)據(jù)原因MyBatis的CachingExecutor在查詢前會(huì)先查緩存而緩存key未包含數(shù)據(jù)源標(biāo)識(shí)如ds_key導(dǎo)致從shard-01查到的數(shù)據(jù)被緩存后續(xù)請(qǐng)求即使路由到shard-02也會(huì)命中該緩存。解決自定義CacheKey在org.apache.ibatis.cache.CacheKey.update()中加入DataSourceContextHolder.getForcedKey()的哈希值?；蛘摺扑]的做法——禁用跨庫(kù)場(chǎng)景的二級(jí)緩存在Mapper XML中添加cache evictionFIFO flushInterval60000 size1000 readOnlytrue blockingfalse/并設(shè)置useCachefalse。4.3 現(xiàn)象Druid監(jiān)控頁(yè)面顯示連接池活躍數(shù)為0但應(yīng)用日志瘋狂打印“getConnection timeout”原因DynamicRoutingDataSource的determineCurrentLookupKey()方法拋出異常如NullPointerException導(dǎo)致Spring fallback到defaultTargetDataSource而該項(xiàng)目未配置defaultTargetDataSource最終返回nullDruid拿到null后靜默失敗。解決在DynamicRoutingDataSource構(gòu)造時(shí)強(qiáng)制設(shè)置setDefaultTargetDataSource(defaultDs)并在determineCurrentLookupKey()最外層加try-catch捕獲所有異常并返回默認(rèn)key同時(shí)記錄ERROR日志“Failed to determine data source key, fallback to default”。4.4 現(xiàn)象單元測(cè)試中Test方法能正確路由但集成測(cè)試SpringBootTest總是走默認(rèn)庫(kù)原因Spring Boot Test默認(rèn)使用Mockito代理而DataSourceContextHolder的ThreadLocal在線程切換如Async、CompletableFuture時(shí)未做透?jìng)鲗?dǎo)致異步線程丟失上下文。解決在測(cè)試配置中注入TaskExecutorBean并重寫execute()方法在提交任務(wù)前拷貝ThreadLocalBean public TaskExecutor taskExecutor() { ThreadPoolTaskExecutor executor new ThreadPoolTaskExecutor(); executor.setCorePoolSize(5); executor.setBeanName(testTaskExecutor); executor.setTaskDecorator(runnable - () - { // 拷貝主線程的DataSource上下文 DataSourceKey key DataSourceContextHolder.getForcedKey(); if (key ! null) { DataSourceContextHolder.setForcedKey(key); } try { runnable.run(); } finally { DataSourceContextHolder.reset(); } }); return executor; }4.5 現(xiàn)象分庫(kù)后SELECT COUNT(*) FROM order_info返回結(jié)果比單庫(kù)少一半原因該SQL未帶WHERE條件ShardAlgorithm無(wú)法提取分片鍵路由引擎按默認(rèn)策略如READ隨機(jī)選一個(gè)庫(kù)執(zhí)行而非廣播到所有分片庫(kù)。解決對(duì)這類全量聚合SQL必須顯式使用TargetDataSource(broadcast)注解并在BroadcastRouter中實(shí)現(xiàn)遍歷所有匹配分片庫(kù)分別執(zhí)行SQL再合并結(jié)果集。注意COUNT(*)需用SUM()聚合GROUP BY需用UNION ALL去重。5. 運(yùn)行與驗(yàn)證從啟動(dòng)到壓測(cè)五步確認(rèn)路由完全受控光跑通不算成功必須驗(yàn)證每條SQL都落在預(yù)期庫(kù)上。本項(xiàng)目提供了一套開(kāi)箱即用的驗(yàn)證流水線不依賴外部工具全部基于Spring Boot Actuator和自研Endpoint實(shí)現(xiàn)。5.1 啟動(dòng)檢查確認(rèn)數(shù)據(jù)源已正確加載啟動(dòng)應(yīng)用后訪問(wèn)GET /actuator/datasource/info返回應(yīng)包含所有配置的數(shù)據(jù)源及其狀態(tài){ status: UP, details: { master: {status:UP,active:0,idle:5}, slave-01: {status:UP,active:0,idle:3}, shard-01: {status:UP,active:0,idle:2}, shard-02: {status:UP,active:0,idle:2} } }若某個(gè)數(shù)據(jù)源狀態(tài)為DOWN檢查日志中是否有HikariPool-1 - Failed to validate connection常見(jiàn)原因是密碼錯(cuò)誤或網(wǎng)絡(luò)不通。5.2 路由追蹤開(kāi)啟MDC日志實(shí)時(shí)觀察key生成過(guò)程在logback-spring.xml中配置pattern確保輸出%X{ds_key}和%X{shard_id}appender nameCONSOLE classch.qos.logback.core.ConsoleAppender encoder pattern%d{HH:mm:ss.SSS} [%thread] %-5level [%X{ds_key}|%X{shard_id}] %logger{36} - %msg%n/pattern /encoder /appender發(fā)起一次請(qǐng)求日志應(yīng)類似14:22:05.123 [http-nio-8080-exec-1] DEBUG [WRITE:shard_03:ds_master_v2|shard_03] c.e.o.s.OrderServiceImpl - getOrder called with orderId1001, userId12345 14:22:05.125 [http-nio-8080-exec-1] DEBUG [WRITE:shard_03:ds_master_v2|shard_03] c.e.o.m.OrderMapper.selectById - Preparing: SELECT * FROM order_info WHERE id ?若ds_key為空或與預(yù)期不符說(shuō)明DataSourceContextHolder.setForcedKey()未被調(diào)用。5.3 SQL攔截驗(yàn)證確認(rèn)MyBatis執(zhí)行的確實(shí)是目標(biāo)庫(kù)啟用MyBatis日志logging.level.org.apache.ibatisDEBUG觀察JDBC Connection日志Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession7a8b9c0d] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection123456789 wrapping com.mysql.cj.jdbc.ConnectionImpl987654321] will not be managed by Spring Preparing: SELECT * FROM order_info WHERE id ?關(guān)鍵看wrapping com.mysql.cj.jdbc.ConnectionImplxxxxx后的hashcode對(duì)比/actuator/datasource/pools返回的各數(shù)據(jù)源連接池中活躍連接的hashcode——必須一致才證明路由正確。5.4 壓測(cè)驗(yàn)證用JMeter模擬混合流量觀測(cè)分片傾斜度編寫JMeter腳本按比例發(fā)送請(qǐng)求70%請(qǐng)求userId在1~100000區(qū)間應(yīng)均勻分布到4個(gè)shard20%請(qǐng)求userId固定為12345應(yīng)100%落到shard_0310%請(qǐng)求無(wú)userId參數(shù)應(yīng)走默認(rèn)master庫(kù)。壓測(cè)10分鐘后執(zhí)行-- 查看各分片庫(kù)的order_info表行數(shù) SELECT COUNT(*) FROM order_info; -- 在shard-01庫(kù)執(zhí)行 SELECT COUNT(*) FROM order_info; -- 在shard-02庫(kù)執(zhí)行 ...理想結(jié)果shard-01 ~ shard-04行數(shù)比值接近70%:70%:70%:70%而非出現(xiàn)shard-01: 10w, shard-02: 500的嚴(yán)重傾斜。5.5 故障注入測(cè)試模擬單庫(kù)宕機(jī)驗(yàn)證降級(jí)能力手動(dòng)停掉shard-02數(shù)據(jù)庫(kù)容器然后發(fā)送原本應(yīng)路由到shard-02的請(qǐng)求如userId20001。預(yù)期行為若配置spring.datasource.dynamic.strictfalse應(yīng)自動(dòng)fallback到primary庫(kù)master并記錄WARN日志“Shard shard-02 unavailable, fallback to primary”若配置stricttrue應(yīng)拋出DataSourceUnavailableException由上層業(yè)務(wù)捕獲并返回友好提示。此測(cè)試驗(yàn)證了系統(tǒng)在部分分片不可用時(shí)的容錯(cuò)能力而非直接雪崩。6. 二次開(kāi)發(fā)與擴(kuò)展如何安全地接入新業(yè)務(wù)、新分片規(guī)則、新數(shù)據(jù)源類型這個(gè)項(xiàng)目的設(shè)計(jì)初衷就是“可演進(jìn)”而不是一次性交付。我把它用在模擬項(xiàng)目X的三個(gè)迭代周期中每次新增需求都只改3個(gè)類以內(nèi)從未動(dòng)過(guò)核心路由引擎。以下是我沉淀下來(lái)的四條改造鐵律以及對(duì)應(yīng)的具體操作模板。6.1 接入新業(yè)務(wù)表三步完成分片配置假設(shè)要為新表user_behavior_log添加分庫(kù)分表分片鍵為user_id按mod8分到8個(gè)歷史庫(kù)Step 1擴(kuò)展YAML配置在application-prod.yml中追加shard: rules: - table: user_behavior_log key: user_id algorithm: mod8 sources: [shard-log-01, shard-log-02, ..., shard-log-08] spring: datasource: dynamic: datasource: shard-log-01: url: jdbc:mysql://db-log-01:3306/log_db_01?... # ... 共8個(gè)數(shù)據(jù)源定義Step 2實(shí)現(xiàn)新分片算法Component public class Mod8ShardAlgorithm implements ShardAlgorithm { Override public String calculateShardId(Object shardValue) { long value Math.abs(((Number) shardValue).longValue()); int index (int) (value % 8); return shard-log- String.format(%02d, index 1); } }Step 3在Mapper接口上聲明分片意圖Mapper public interface UserBehaviorLogMapper { Insert(INSERT INTO user_behavior_log (...) VALUES (...)) TargetDataSource(shard-log) // ← 告訴路由引擎此Mapper只走shard-log系列庫(kù) void insert(UserBehaviorLog record); }注意TargetDataSource(shard-log)中的值必須與YAML中sources列表的前綴一致路由引擎會(huì)自動(dòng)匹配shard-log-01等具體實(shí)例。6.2 支持新數(shù)據(jù)源類型無(wú)縫接入PostgreSQL分片庫(kù)項(xiàng)目當(dāng)前只支持MySQL但某模塊需對(duì)接PostgreSQL歷史庫(kù)。無(wú)需修改DynamicRoutingDataSource只需新增PostgreSqlDataSourceFactory繼承AbstractDataSourceFactory重寫createDataSource()方法在DynamicDataSourceFactory.refresh()中根據(jù)driver-class-name自動(dòng)選擇工廠類PostgreSQL分片算法與MySQL完全復(fù)用Mod8ShardAlgorithm不依賴數(shù)據(jù)庫(kù)特性。public class PostgreSqlDataSourceFactory extends AbstractDataSourceFactory { Override protected DataSource createDataSource(DataSourceProperties props) { HikariConfig config new HikariConfig(); config.setJdbcUrl(props.getUrl()); config.setUsername(props.getUsername()); config.setPassword(props.getPassword()); config.setDriverClassName(org.postgresql.Driver); // PostgreSQL特有參數(shù) config.addDataSourceProperty(reWriteBatchedInserts, true); return new HikariDataSource(config); } }6.3 審計(jì)增強(qiáng)記錄每次路由決策的完整證據(jù)鏈生產(chǎn)環(huán)境需要審計(jì)“某次訂單查詢?yōu)楹巫吡藄hard-03”項(xiàng)目預(yù)留了RouteTrace擴(kuò)展點(diǎn)。啟用方式// 在Controller中 GetMapping(/order/{id}) public OrderVO getOrder(PathVariable Long id) { DataSourceContextHolder.enableRouteTrace(); // ← 開(kāi)啟追蹤 return orderService.getOrder(id); }啟用后日志自動(dòng)追加route_path字段[ds_keyREAD:shard_03:slave-01|shard_idshard_03|route_pathANNOTATION→CONTEXT→DEFAULT]其中ANNOTATION→CONTEXT→DEFAULT表示先檢查了TargetDataSource注解未找到再嘗試從參數(shù)提取shardContext成功最后未觸發(fā)默認(rèn)策略。6.4 灰度發(fā)布支持按用戶ID范圍動(dòng)態(tài)切流上線新分片庫(kù)shard-05時(shí)希望先讓userId在1000000~1000010之間的用戶走新庫(kù)其余走老庫(kù)。只需擴(kuò)展ShardAlgorithmComponent public class GrayShardAlgorithm implements ShardAlgorithm { Override public String calculateShardId(Object shardValue) { long userId ((Number) shardValue).longValue(); if (userId 1_000_000 userId 1_000_010) { return shard-05; // 灰度庫(kù) } // 兜底走原有mod4算法 return new Mod4ShardAlgorithm().calculateShardId(shardValue); } }然后在YAML中將user_id表的algorithm改為gray即可?；叶冉Y(jié)束后刪掉這段if邏輯全程無(wú)需改SQL、不發(fā)版。從那以后我每次接入新業(yè)務(wù)表都強(qiáng)制走一遍這四步模板配YAML → 寫算法 → 打注解 → 壓測(cè)驗(yàn)證。哪怕凌晨三點(diǎn)上線只要日志里看到[ds_keyWRITE:shard_05:ds_master_v3]心里就踏實(shí)。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取 Spring多數(shù)據(jù)源路由實(shí)戰(zhàn)：三層可控切換與分庫(kù)分表落地
簡(jiǎn)介本資源是一份面向Java后端開(kāi)發(fā)者與數(shù)據(jù)庫(kù)架構(gòu)初學(xué)者的分庫(kù)分表實(shí)戰(zhàn)教學(xué)包聚焦企業(yè)級(jí)大數(shù)據(jù)量場(chǎng)景下的數(shù)據(jù)庫(kù)水平擴(kuò)展難題以Sharding-JDBC為核心框架實(shí)現(xiàn)多數(shù)據(jù)源動(dòng)態(tài)切換與透明化分片。資源共73個(gè)文件涵蓋18個(gè)Java業(yè)務(wù)與配置類、21個(gè)編譯后class文件、18個(gè)XML配置與映射文件含MyBatis映射及Sharding規(guī)則定義以及SQL建表腳本、Maven項(xiàng)目配置pom.xml、Eclipse工程元數(shù)據(jù).project/.classpath等關(guān)鍵開(kāi)發(fā)資產(chǎn)整體僅66KB輕量易導(dǎo)入。已有2068人學(xué)習(xí)下載適合快速搭建SSMDemo環(huán)境并理解分片路由、數(shù)據(jù)源初始化、YAML分片規(guī)則配置等核心實(shí)踐環(huán)節(jié)。讀者可直接復(fù)用項(xiàng)目結(jié)構(gòu)掌握從多數(shù)據(jù)源聲明、分庫(kù)分表策略配置到跨庫(kù)CRUD調(diào)用的完整鏈路尤其適用于SpringMyBatisSharding-JDBC技術(shù)棧的中小規(guī)模分片落地參考。1. 分庫(kù)分表多數(shù)據(jù)源的切換不是加個(gè)注解就完事而是讓每個(gè)DAO層調(diào)用都清楚自己該連哪張表、哪個(gè)庫(kù)你剛接手一個(gè)日增300萬(wàn)訂單的電商后臺(tái)MySQL單表已超2800萬(wàn)行慢查詢從“偶發(fā)”變成“每分鐘必報(bào)”DBA甩來(lái)一句“上分庫(kù)分表吧?！薄Y(jié)果你興沖沖引入ShardingSphere配置好邏輯表、分片鍵、分片算法一跑測(cè)試發(fā)現(xiàn)用戶中心查user_info正常但訂單服務(wù)里一個(gè)帶JOIN的報(bào)表SQL直接報(bào)錯(cuò)“無(wú)法路由到具體數(shù)據(jù)源”更玄學(xué)的是同一個(gè)Service方法里前半段insert走的是ds0后半段update卻連到了ds1事務(wù)根本沒(méi)法提交。這不是配置沒(méi)生效而是分庫(kù)分表的本質(zhì)從來(lái)不是“自動(dòng)拆分”而是“顯式聲明精準(zhǔn)控制”。這個(gè)項(xiàng)目就是一套可落地、可調(diào)試、可驗(yàn)證的多數(shù)據(jù)源切換實(shí)戰(zhàn)方案它不依賴黑匣子中間件的自動(dòng)路由而是用Spring原生AbstractRoutingDataSource 自定義ThreadLocal上下文 顯式數(shù)據(jù)源標(biāo)記把“哪個(gè)線程該用哪個(gè)庫(kù)”這件事從配置文件里拽出來(lái)寫進(jìn)代碼里、打在日志里、壓測(cè)時(shí)能一眼看穿。適合正在被跨庫(kù)JOIN、讀寫分離、歷史庫(kù)歸檔、灰度發(fā)布數(shù)據(jù)隔離等問(wèn)題卡住的后端開(kāi)發(fā)者尤其適合那些已經(jīng)踩過(guò)ShardingSphere規(guī)則配置陷阱、想回歸可控性的團(tuán)隊(duì)。2. 架構(gòu)設(shè)計(jì)與核心模塊拆解三層路由機(jī)制讓數(shù)據(jù)源選擇不再靠猜這個(gè)項(xiàng)目不是簡(jiǎn)單封裝了一個(gè)DynamicDataSource而是構(gòu)建了上下文驅(qū)動(dòng) 規(guī)則匹配 手動(dòng)覆蓋的三級(jí)路由體系。它把“用哪個(gè)庫(kù)”這件事拆成三個(gè)可獨(dú)立驗(yàn)證、可組合使用的環(huán)節(jié)最外層是業(yè)務(wù)語(yǔ)義標(biāo)記比如TargetDataSource(write)中間層是分片鍵自動(dòng)識(shí)別比如方法參數(shù)含userId且當(dāng)前策略為user_id分片最內(nèi)層是線程級(jí)強(qiáng)制綁定比如開(kāi)啟事務(wù)時(shí)鎖定主庫(kù)。三者優(yōu)先級(jí)從高到低沖突時(shí)以手動(dòng)標(biāo)記為準(zhǔn)。這種設(shè)計(jì)避免了“全局默認(rèn)庫(kù)”帶來(lái)的隱式耦合也繞開(kāi)了中間件對(duì)SQL語(yǔ)法的強(qiáng)約束——你寫原生MyBatis XML它照樣能路由你用JdbcTemplate手寫復(fù)雜UNION它也能按需切庫(kù)。整個(gè)結(jié)構(gòu)輕量無(wú)額外jar依賴、透明所有路由決策可打日志、可測(cè)試Mock ThreadLocal即可覆蓋全部分支。2.1 核心路由引擎AbstractRoutingDataSource的深度定制Spring原生的AbstractRoutingDataSource只提供一個(gè)determineCurrentLookupKey()抽象方法返回String類型的key。但本項(xiàng)目在此基礎(chǔ)上做了三處關(guān)鍵增強(qiáng)key不再是簡(jiǎn)單字符串而是自定義的DataSourceKey類包含type(READ/WRITE)、shardId(如shard_01)、sourceId(如ds_master_v2)三個(gè)維度determineCurrentLookupKey()內(nèi)部不直接返回key而是委托給RouterChain執(zhí)行鏈?zhǔn)脚袛嗝看蝛ey生成后自動(dòng)記錄到MDC中供日志追蹤如[ds_keyWRITE:shard_01:ds_master_v2]。public class DynamicRoutingDataSource extends AbstractRoutingDataSource { Override protected Object determineCurrentLookupKey() { // 1. 檢查線程局部變量是否已強(qiáng)制綁定 DataSourceKey forcedKey DataSourceContextHolder.getForcedKey(); if (forcedKey ! null) { log.debug(Route forced: {}, forcedKey); return forcedKey; } // 2. 嘗試從方法參數(shù)/注解提取分片上下文 DataSourceKey contextKey ContextRouter.routeByContext(); if (contextKey ! null) { log.debug(Route by context: {}, contextKey); return contextKey; } // 3. 回退到默認(rèn)策略如讀寫分離下的READ DataSourceKey defaultKey DefaultRouter.routeByDefault(); log.debug(Route by default: {}, defaultKey); return defaultKey; } }提示DataSourceKey必須實(shí)現(xiàn)equals()和hashCode()否則Spring緩存的targetDataSource Map會(huì)失效——這是血淚經(jīng)驗(yàn)曾因忘記重寫導(dǎo)致同一key反復(fù)創(chuàng)建新連接池DB連接數(shù)暴增。2.2 上下文管理器ThreadLocal MDC雙保險(xiǎn)DataSourceContextHolder是整個(gè)路由的“開(kāi)關(guān)中樞”。它不只存一個(gè)String而是維護(hù)三個(gè)ThreadLocal變量forcedKey: 用于Transactional或TargetDataSource注解觸發(fā)的強(qiáng)制綁定shardContext: 存儲(chǔ)當(dāng)前請(qǐng)求的分片標(biāo)識(shí)如userId12345 → shardIdshard_03routeTrace: 布爾值開(kāi)啟后自動(dòng)記錄每次路由決策路徑用于排查“為什么沒(méi)走預(yù)期庫(kù)”。public class DataSourceContextHolder { private static final ThreadLocalDataSourceKey FORCED_KEY_HOLDER new NamedThreadLocal(forcedDataSourceKey); private static final ThreadLocalShardContext SHARD_CONTEXT_HOLDER new NamedThreadLocal(shardContext); private static final ThreadLocalBoolean ROUTE_TRACE_HOLDER ThreadLocal.withInitial(() - false); public static void setForcedKey(DataSourceKey key) { FORCED_KEY_HOLDER.set(key); // 同步寫入MDC確保日志可追溯 MDC.put(ds_key, key.toString()); } public static void setShardContext(ShardContext context) { SHARD_CONTEXT_HOLDER.set(context); MDC.put(shard_id, context.getShardId()); MDC.put(shard_key, context.getShardKey()); } public static void enableRouteTrace() { ROUTE_TRACE_HOLDER.set(true); MDC.put(route_trace, enabled); } }NamedThreadLocal是自定義類繼承ThreadLocal并重寫toString()方便在Arthas或jstack線程dump中快速識(shí)別該ThreadLocal用途——這點(diǎn)在生產(chǎn)環(huán)境排查“某個(gè)線程死鎖在數(shù)據(jù)庫(kù)連接上”時(shí)極其關(guān)鍵。2.3 分片上下文注入從Controller到Mapper的全鏈路透?jìng)鞣制I如userId、orderId通常來(lái)自HTTP請(qǐng)求參數(shù)或RPC上下文不能靠DAO層自己猜。本項(xiàng)目采用“入口攔截顯式傳遞”模式Web層ShardParam(userId)注解標(biāo)注Controller方法參數(shù)由ShardArgumentResolver解析并注入ShardContextRPC層Dubbo Filter或gRPC Interceptor從Attachment或Metadata中提取分片信息內(nèi)部調(diào)用Service方法簽名強(qiáng)制要求傳入ShardContext杜絕“隱式上下文”。RestController public class OrderController { GetMapping(/order/{orderId}) public OrderVO getOrder( PathVariable Long orderId, ShardParam(userId) Long userId) { // ← 此注解觸發(fā)ShardContext注入 return orderService.getOrder(orderId, userId); } } Service public class OrderServiceImpl implements OrderService { Override public OrderVO getOrder(Long orderId, Long userId) { // 1. 主動(dòng)構(gòu)造ShardContext確保下游能路由 ShardContext context ShardContext.builder() .shardKey(userId) .shardValue(userId) .shardId(ShardAlgorithm.calculateShardId(userId)) // 如 userId % 4 → shard_02 .build(); DataSourceContextHolder.setShardContext(context); // 2. 執(zhí)行實(shí)際查詢此時(shí)DynamicRoutingDataSource已能獲取shardId return orderMapper.selectById(orderId); } }注意ShardAlgorithm.calculateShardId()必須是純函數(shù)無(wú)狀態(tài)、無(wú)外部依賴否則在多線程下可能產(chǎn)生不一致分片結(jié)果——這是分庫(kù)分表最隱蔽的翻車點(diǎn)之一。3. 數(shù)據(jù)源配置與初始化YAML驅(qū)動(dòng) 運(yùn)行時(shí)熱加載支持項(xiàng)目摒棄了XML硬編碼數(shù)據(jù)源的方式采用Spring Boot原生ConfigurationProperties綁定YAML配置同時(shí)預(yù)留了運(yùn)行時(shí)刷新接口如POST /actuator/datasource/refresh無(wú)需重啟即可更新數(shù)據(jù)源連接池參數(shù)。配置結(jié)構(gòu)清晰分層頂層定義邏輯組如master-slave-group中層定義物理實(shí)例如ds_master_v2底層定義JDBC屬性。這種結(jié)構(gòu)天然支持灰度發(fā)布場(chǎng)景——你可以先將新庫(kù)ds_master_v3加入配置但不啟用待壓測(cè)通過(guò)后再修改group.default-write-source: ds_master_v3。3.1 application.yml核心配置片段# 多數(shù)據(jù)源基礎(chǔ)配置 spring: datasource: dynamic: primary: master # 默認(rèn)寫庫(kù) strict: false # 非嚴(yán)格模式未匹配到數(shù)據(jù)源時(shí)回退到primary datasource: # 主庫(kù)寫 master: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-master:3306/order_db_v2?useSSLfalseserverTimezoneAsia/Shanghai username: app_rw password: ${DB_MASTER_PWD} hikari: maximum-pool-size: 20 minimum-idle: 5 # 從庫(kù)集群讀 slave-01: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-slave-01:3306/order_db_v2?useSSLfalseserverTimezoneAsia/Shanghai username: app_ro password: ${DB_SLAVE_PWD} hikari: maximum-pool-size: 15 slave-02: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-slave-02:3306/order_db_v2?useSSLfalseserverTimezoneAsia/Shanghai username: app_ro password: ${DB_SLAVE_PWD} hikari: maximum-pool-size: 15 # 分片庫(kù)歷史訂單 shard-01: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-shard-01:3306/order_history_shard_01?useSSLfalseserverTimezoneAsia/Shanghai username: app_hist password: ${DB_HIST_PWD} shard-02: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://db-shard-02:3306/order_history_shard_02?useSSLfalseserverTimezoneAsia/Shanghai username: app_hist password: ${DB_HIST_PWD}3.2 動(dòng)態(tài)數(shù)據(jù)源工廠支持運(yùn)行時(shí)新增/刪除數(shù)據(jù)源DynamicDataSourceFactory負(fù)責(zé)將YAML配置轉(zhuǎn)化為MapObject, DataSource并注冊(cè)到DynamicRoutingDataSource中。關(guān)鍵點(diǎn)在于使用ConcurrentHashMap存儲(chǔ)targetDataSources保證put/remove線程安全每次refresh時(shí)先關(guān)閉舊數(shù)據(jù)源調(diào)用HikariDataSource.close()再注入新實(shí)例提供getDataSource(String key)方法供其他組件如Druid監(jiān)控按需獲取特定數(shù)據(jù)源。Component public class DynamicDataSourceFactory { private final MapObject, DataSource targetDataSources new ConcurrentHashMap(); PostConstruct public void init() { loadFromConfig(); // 從YAML加載初始數(shù)據(jù)源 } public void refresh(MapString, DataSourceProperties newConfigs) { // 1. 關(guān)閉所有舊數(shù)據(jù)源 targetDataSources.values().forEach(this::closeDataSource); targetDataSources.clear(); // 2. 創(chuàng)建新數(shù)據(jù)源并注入 newConfigs.forEach((key, props) - { HikariDataSource ds createHikariDataSource(props); targetDataSources.put(key, ds); }); // 3. 通知DynamicRoutingDataSource更新 dynamicRoutingDataSource.afterPropertiesSet(); } private void closeDataSource(DataSource ds) { if (ds instanceof HikariDataSource) { ((HikariDataSource) ds).close(); } } }注意dynamicRoutingDataSource.afterPropertiesSet()必須顯式調(diào)用否則Spring不會(huì)重新初始化resolvedDataSources緩存——這是熱加載失敗最常見(jiàn)的原因。3.3 分片策略配置YAML定義規(guī)則Java實(shí)現(xiàn)算法分片邏輯不寫死在代碼里而是通過(guò)YAML聲明規(guī)則再由Java類按需加載。例如shard: rules: - table: order_info key: user_id algorithm: mod4 sources: [shard-01, shard-02, shard-03, shard-04] - table: order_detail key: order_id algorithm: hash16 sources: [shard-01, shard-02, shard-03, shard-04]對(duì)應(yīng)Java端ShardRuleLoader解析YAML后生成ShardRule對(duì)象存入內(nèi)存并由ShardAlgorithmFactory根據(jù)algorithm字段返回具體實(shí)現(xiàn)public interface ShardAlgorithm { String calculateShardId(Object shardValue); } Component public class Mod4ShardAlgorithm implements ShardAlgorithm { Override public String calculateShardId(Object shardValue) { if (!(shardValue instanceof Number)) { throw new IllegalArgumentException(Mod4 requires numeric shard value); } long value ((Number) shardValue).longValue(); int shardIndex (int) (Math.abs(value) % 4); // 避免負(fù)數(shù)取模問(wèn)題 return shard- String.format(%02d, shardIndex 1); // shard-01 ~ shard-04 } }這種解耦讓DBA可獨(dú)立修改分片規(guī)則改YAML而開(kāi)發(fā)只需維護(hù)算法實(shí)現(xiàn)雙方無(wú)需協(xié)同發(fā)布。4. 避坑指南5個(gè)真實(shí)踩過(guò)的坑省下你三天排查時(shí)間分庫(kù)分表不是配置完就能跑通的銀彈而是處處埋雷的精細(xì)活。以下是我在某高校實(shí)驗(yàn)室模擬項(xiàng)目X中連續(xù)兩周壓測(cè)時(shí)踩出的5個(gè)典型問(wèn)題每一條都附帶復(fù)現(xiàn)條件、根因分析和可立即驗(yàn)證的修復(fù)方案。4.1 現(xiàn)象事務(wù)內(nèi)跨庫(kù)操作不報(bào)錯(cuò)但部分SQL實(shí)際執(zhí)行在錯(cuò)誤庫(kù)上原因Transactional默認(rèn)傳播行為是REQUIRED當(dāng)方法A調(diào)用方法B時(shí)若B未聲明事務(wù)其數(shù)據(jù)源路由會(huì)脫離A的事務(wù)上下文導(dǎo)致B的SQL走默認(rèn)庫(kù)而非事務(wù)綁定庫(kù)。解決在Transactional方法內(nèi)所有子調(diào)用必須顯式聲明Transactional(propagation Propagation.REQUIRED)或統(tǒng)一使用TransactionTemplate手動(dòng)控制。更徹底的方案是在DataSourceInterceptor中檢測(cè)當(dāng)前是否在事務(wù)中若在則強(qiáng)制將forcedKey設(shè)為事務(wù)起始時(shí)的key。4.2 現(xiàn)象MyBatis二級(jí)緩存命中但返回的數(shù)據(jù)卻是舊庫(kù)里的臟數(shù)據(jù)原因MyBatis的CachingExecutor在查詢前會(huì)先查緩存而緩存key未包含數(shù)據(jù)源標(biāo)識(shí)如ds_key導(dǎo)致從shard-01查到的數(shù)據(jù)被緩存后續(xù)請(qǐng)求即使路由到shard-02也會(huì)命中該緩存。解決自定義CacheKey在org.apache.ibatis.cache.CacheKey.update()中加入DataSourceContextHolder.getForcedKey()的哈希值?；蛘摺扑]的做法——禁用跨庫(kù)場(chǎng)景的二級(jí)緩存在Mapper XML中添加cache evictionFIFO flushInterval60000 size1000 readOnlytrue blockingfalse/并設(shè)置useCachefalse。4.3 現(xiàn)象Druid監(jiān)控頁(yè)面顯示連接池活躍數(shù)為0但應(yīng)用日志瘋狂打印“getConnection timeout”原因DynamicRoutingDataSource的determineCurrentLookupKey()方法拋出異常如NullPointerException導(dǎo)致Spring fallback到defaultTargetDataSource而該項(xiàng)目未配置defaultTargetDataSource最終返回nullDruid拿到null后靜默失敗。解決在DynamicRoutingDataSource構(gòu)造時(shí)強(qiáng)制設(shè)置setDefaultTargetDataSource(defaultDs)并在determineCurrentLookupKey()最外層加try-catch捕獲所有異常并返回默認(rèn)key同時(shí)記錄ERROR日志“Failed to determine data source key, fallback to default”。4.4 現(xiàn)象單元測(cè)試中Test方法能正確路由但集成測(cè)試SpringBootTest總是走默認(rèn)庫(kù)原因Spring Boot Test默認(rèn)使用Mockito代理而DataSourceContextHolder的ThreadLocal在線程切換如Async、CompletableFuture時(shí)未做透?jìng)鲗?dǎo)致異步線程丟失上下文。解決在測(cè)試配置中注入TaskExecutorBean并重寫execute()方法在提交任務(wù)前拷貝ThreadLocalBean public TaskExecutor taskExecutor() { ThreadPoolTaskExecutor executor new ThreadPoolTaskExecutor(); executor.setCorePoolSize(5); executor.setBeanName(testTaskExecutor); executor.setTaskDecorator(runnable - () - { // 拷貝主線程的DataSource上下文 DataSourceKey key DataSourceContextHolder.getForcedKey(); if (key ! null) { DataSourceContextHolder.setForcedKey(key); } try { runnable.run(); } finally { DataSourceContextHolder.reset(); } }); return executor; }4.5 現(xiàn)象分庫(kù)后SELECT COUNT(*) FROM order_info返回結(jié)果比單庫(kù)少一半原因該SQL未帶WHERE條件ShardAlgorithm無(wú)法提取分片鍵路由引擎按默認(rèn)策略如READ隨機(jī)選一個(gè)庫(kù)執(zhí)行而非廣播到所有分片庫(kù)。解決對(duì)這類全量聚合SQL必須顯式使用TargetDataSource(broadcast)注解并在BroadcastRouter中實(shí)現(xiàn)遍歷所有匹配分片庫(kù)分別執(zhí)行SQL再合并結(jié)果集。注意COUNT(*)需用SUM()聚合GROUP BY需用UNION ALL去重。5. 運(yùn)行與驗(yàn)證從啟動(dòng)到壓測(cè)五步確認(rèn)路由完全受控光跑通不算成功必須驗(yàn)證每條SQL都落在預(yù)期庫(kù)上。本項(xiàng)目提供了一套開(kāi)箱即用的驗(yàn)證流水線不依賴外部工具全部基于Spring Boot Actuator和自研Endpoint實(shí)現(xiàn)。5.1 啟動(dòng)檢查確認(rèn)數(shù)據(jù)源已正確加載啟動(dòng)應(yīng)用后訪問(wèn)GET /actuator/datasource/info返回應(yīng)包含所有配置的數(shù)據(jù)源及其狀態(tài){ status: UP, details: { master: {status:UP,active:0,idle:5}, slave-01: {status:UP,active:0,idle:3}, shard-01: {status:UP,active:0,idle:2}, shard-02: {status:UP,active:0,idle:2} } }若某個(gè)數(shù)據(jù)源狀態(tài)為DOWN檢查日志中是否有HikariPool-1 - Failed to validate connection常見(jiàn)原因是密碼錯(cuò)誤或網(wǎng)絡(luò)不通。5.2 路由追蹤開(kāi)啟MDC日志實(shí)時(shí)觀察key生成過(guò)程在logback-spring.xml中配置pattern確保輸出%X{ds_key}和%X{shard_id}appender nameCONSOLE classch.qos.logback.core.ConsoleAppender encoder pattern%d{HH:mm:ss.SSS} [%thread] %-5level [%X{ds_key}|%X{shard_id}] %logger{36} - %msg%n/pattern /encoder /appender發(fā)起一次請(qǐng)求日志應(yīng)類似14:22:05.123 [http-nio-8080-exec-1] DEBUG [WRITE:shard_03:ds_master_v2|shard_03] c.e.o.s.OrderServiceImpl - getOrder called with orderId1001, userId12345 14:22:05.125 [http-nio-8080-exec-1] DEBUG [WRITE:shard_03:ds_master_v2|shard_03] c.e.o.m.OrderMapper.selectById - Preparing: SELECT * FROM order_info WHERE id ?若ds_key為空或與預(yù)期不符說(shuō)明DataSourceContextHolder.setForcedKey()未被調(diào)用。5.3 SQL攔截驗(yàn)證確認(rèn)MyBatis執(zhí)行的確實(shí)是目標(biāo)庫(kù)啟用MyBatis日志logging.level.org.apache.ibatisDEBUG觀察JDBC Connection日志Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession7a8b9c0d] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection123456789 wrapping com.mysql.cj.jdbc.ConnectionImpl987654321] will not be managed by Spring Preparing: SELECT * FROM order_info WHERE id ?關(guān)鍵看wrapping com.mysql.cj.jdbc.ConnectionImplxxxxx后的hashcode對(duì)比/actuator/datasource/pools返回的各數(shù)據(jù)源連接池中活躍連接的hashcode——必須一致才證明路由正確。5.4 壓測(cè)驗(yàn)證用JMeter模擬混合流量觀測(cè)分片傾斜度編寫JMeter腳本按比例發(fā)送請(qǐng)求70%請(qǐng)求userId在1~100000區(qū)間應(yīng)均勻分布到4個(gè)shard20%請(qǐng)求userId固定為12345應(yīng)100%落到shard_0310%請(qǐng)求無(wú)userId參數(shù)應(yīng)走默認(rèn)master庫(kù)。壓測(cè)10分鐘后執(zhí)行-- 查看各分片庫(kù)的order_info表行數(shù) SELECT COUNT(*) FROM order_info; -- 在shard-01庫(kù)執(zhí)行 SELECT COUNT(*) FROM order_info; -- 在shard-02庫(kù)執(zhí)行 ...理想結(jié)果shard-01 ~ shard-04行數(shù)比值接近70%:70%:70%:70%而非出現(xiàn)shard-01: 10w, shard-02: 500的嚴(yán)重傾斜。5.5 故障注入測(cè)試模擬單庫(kù)宕機(jī)驗(yàn)證降級(jí)能力手動(dòng)停掉shard-02數(shù)據(jù)庫(kù)容器然后發(fā)送原本應(yīng)路由到shard-02的請(qǐng)求如userId20001。預(yù)期行為若配置spring.datasource.dynamic.strictfalse應(yīng)自動(dòng)fallback到primary庫(kù)master并記錄WARN日志“Shard shard-02 unavailable, fallback to primary”若配置stricttrue應(yīng)拋出DataSourceUnavailableException由上層業(yè)務(wù)捕獲并返回友好提示。此測(cè)試驗(yàn)證了系統(tǒng)在部分分片不可用時(shí)的容錯(cuò)能力而非直接雪崩。6. 二次開(kāi)發(fā)與擴(kuò)展如何安全地接入新業(yè)務(wù)、新分片規(guī)則、新數(shù)據(jù)源類型這個(gè)項(xiàng)目的設(shè)計(jì)初衷就是“可演進(jìn)”而不是一次性交付。我把它用在模擬項(xiàng)目X的三個(gè)迭代周期中每次新增需求都只改3個(gè)類以內(nèi)從未動(dòng)過(guò)核心路由引擎。以下是我沉淀下來(lái)的四條改造鐵律以及對(duì)應(yīng)的具體操作模板。6.1 接入新業(yè)務(wù)表三步完成分片配置假設(shè)要為新表user_behavior_log添加分庫(kù)分表分片鍵為user_id按mod8分到8個(gè)歷史庫(kù)Step 1擴(kuò)展YAML配置在application-prod.yml中追加shard: rules: - table: user_behavior_log key: user_id algorithm: mod8 sources: [shard-log-01, shard-log-02, ..., shard-log-08] spring: datasource: dynamic: datasource: shard-log-01: url: jdbc:mysql://db-log-01:3306/log_db_01?... # ... 共8個(gè)數(shù)據(jù)源定義Step 2實(shí)現(xiàn)新分片算法Component public class Mod8ShardAlgorithm implements ShardAlgorithm { Override public String calculateShardId(Object shardValue) { long value Math.abs(((Number) shardValue).longValue()); int index (int) (value % 8); return shard-log- String.format(%02d, index 1); } }Step 3在Mapper接口上聲明分片意圖Mapper public interface UserBehaviorLogMapper { Insert(INSERT INTO user_behavior_log (...) VALUES (...)) TargetDataSource(shard-log) // ← 告訴路由引擎此Mapper只走shard-log系列庫(kù) void insert(UserBehaviorLog record); }注意TargetDataSource(shard-log)中的值必須與YAML中sources列表的前綴一致路由引擎會(huì)自動(dòng)匹配shard-log-01等具體實(shí)例。6.2 支持新數(shù)據(jù)源類型無(wú)縫接入PostgreSQL分片庫(kù)項(xiàng)目當(dāng)前只支持MySQL但某模塊需對(duì)接PostgreSQL歷史庫(kù)。無(wú)需修改DynamicRoutingDataSource只需新增PostgreSqlDataSourceFactory繼承AbstractDataSourceFactory重寫createDataSource()方法在DynamicDataSourceFactory.refresh()中根據(jù)driver-class-name自動(dòng)選擇工廠類PostgreSQL分片算法與MySQL完全復(fù)用Mod8ShardAlgorithm不依賴數(shù)據(jù)庫(kù)特性。public class PostgreSqlDataSourceFactory extends AbstractDataSourceFactory { Override protected DataSource createDataSource(DataSourceProperties props) { HikariConfig config new HikariConfig(); config.setJdbcUrl(props.getUrl()); config.setUsername(props.getUsername()); config.setPassword(props.getPassword()); config.setDriverClassName(org.postgresql.Driver); // PostgreSQL特有參數(shù) config.addDataSourceProperty(reWriteBatchedInserts, true); return new HikariDataSource(config); } }6.3 審計(jì)增強(qiáng)記錄每次路由決策的完整證據(jù)鏈生產(chǎn)環(huán)境需要審計(jì)“某次訂單查詢?yōu)楹巫吡藄hard-03”項(xiàng)目預(yù)留了RouteTrace擴(kuò)展點(diǎn)。啟用方式// 在Controller中 GetMapping(/order/{id}) public OrderVO getOrder(PathVariable Long id) { DataSourceContextHolder.enableRouteTrace(); // ← 開(kāi)啟追蹤 return orderService.getOrder(id); }啟用后日志自動(dòng)追加route_path字段[ds_keyREAD:shard_03:slave-01|shard_idshard_03|route_pathANNOTATION→CONTEXT→DEFAULT]其中ANNOTATION→CONTEXT→DEFAULT表示先檢查了TargetDataSource注解未找到再嘗試從參數(shù)提取shardContext成功最后未觸發(fā)默認(rèn)策略。6.4 灰度發(fā)布支持按用戶ID范圍動(dòng)態(tài)切流上線新分片庫(kù)shard-05時(shí)希望先讓userId在1000000~1000010之間的用戶走新庫(kù)其余走老庫(kù)。只需擴(kuò)展ShardAlgorithmComponent public class GrayShardAlgorithm implements ShardAlgorithm { Override public String calculateShardId(Object shardValue) { long userId ((Number) shardValue).longValue(); if (userId 1_000_000 userId 1_000_010) { return shard-05; // 灰度庫(kù) } // 兜底走原有mod4算法 return new Mod4ShardAlgorithm().calculateShardId(shardValue); } }然后在YAML中將user_id表的algorithm改為gray即可?；叶冉Y(jié)束后刪掉這段if邏輯全程無(wú)需改SQL、不發(fā)版。從那以后我每次接入新業(yè)務(wù)表都強(qiáng)制走一遍這四步模板配YAML → 寫算法 → 打注解 → 壓測(cè)驗(yàn)證。哪怕凌晨三點(diǎn)上線只要日志里看到[ds_keyWRITE:shard_05:ds_master_v3]心里就踏實(shí)。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取