簡介這是一份面向MySQL中間件使用者的按月分表增強包基于MyCat 1.6.7.6正式版源碼二次開發(fā)。核心改進是新增subTables的BYMONTH分表方式支持“tableName_$202101-?”這類正則配置從設定月份開始問號自動動態(tài)代表當前月份無需手動維護完整表名子表需先在MySQL中真實存在并可借助動態(tài)建表實現(xiàn)子表按月自動增長大幅降低按月分區(qū)的維護成本對需要保留歷史流水、按月歸檔的業(yè)務場景尤其適用。壓縮包共111個文件包括53個jar依賴、18個properties配置、14個txt說明文檔、10個xml規(guī)則文件、4個sh運維腳本、2個sql初始化腳本等整體約24.86MB其中properties和xml用于調(diào)整分表與服務規(guī)則jar為運行時依賴txt和sh提供改造說明與啟停輔助資料組織完整。當前已有375人學習下載。通過該包可快速獲得按月分表的可運行實現(xiàn)參考其中的源碼改動與正則匹配思路還能進一步拓展到季度、年度等其他時間維度分表場景適合MyCat使用者與源碼二次開發(fā)人員參考。1. mycat-1.6.7.6_BYMONTH.zip把單表千萬數(shù)據(jù)按月拆開業(yè)務 SQL 幾乎不用改當一張訂單表以每天幾十萬行的速度增長單表行數(shù)沖上千萬之后即使索引全部命中磁盤 IO 和行鎖競爭也會讓延遲肉眼可見地上升。刪數(shù)據(jù)不舍得加機器沒預算業(yè)務 SQL 又改不動這是大多數(shù)團隊第一次意識到需要分庫分表的時刻。mycat-1.6.7.6_BYMONTH.zip 就是這類場景下可以直接投入的方案它是一個 MySQL 數(shù)據(jù)庫中間件發(fā)行包自帶按月分片的 PartitionByMonth 算法應用連接 Mycat 就像連接一個普通 MySQL業(yè)務 SQL 基本不用動就能把一張邏輯大表按月份拆到多個物理庫表。這個方案適合三類人單表數(shù)據(jù)量已經(jīng)告警、正在做數(shù)據(jù)庫選型評估的團隊想在不改業(yè)務代碼的前提下做冷熱數(shù)據(jù)分離的運維工程師以及被分配了把訂單庫拆了這類任務、需要快速找到落地路徑的開發(fā)者。下面我從分片模型、部署操作、BYMONTH 規(guī)則配置、常見坑和驗收技巧五個層面展開每一步都可以照著操作。2. 先搞清楚 Mycat 的分片模型三個配置文件和一條數(shù)據(jù)流先別急著解壓和啟動把 Mycat 的分片模型理清楚后面改配置時才不會一頭霧水。Mycat 本身不是一個存儲引擎它不真正保存數(shù)據(jù)只負責把 SQL 路由到正確的物理 MySQL 上執(zhí)行然后匯總結(jié)果。2.1 應用與 MySQL 之間的中間層Mycat 到底代理了什么Mycat 的本質(zhì)是一個實現(xiàn)了 MySQL 協(xié)議的代理層。后端服務把 jdbc:mysql://mycat_host:8066/LOGIC_DB 當成一個普通 MySQL 實例來連接Mycat 收到 SQL 后做三件事解析 SQL、按分片規(guī)則決定去哪個物理節(jié)點執(zhí)行、匯總結(jié)果返回給應用。對業(yè)務代碼來說它連的就是一個大 MySQL這個假象是 Mycat 最核心的價值。代理層解決了兩個實際問題。第一是連接收斂后端幾十個服務實例不會直接打爆 MySQL 的連接數(shù)所有連接都落在 Mycat 的連接池上。第二是透明分片邏輯表名在多個物理庫中真實存在Mycat 根據(jù)分片字段找到正確的物理表。分片規(guī)則越清晰代理層的性能損耗越低規(guī)則寫得太模糊Mycat 就會把所有分片都查一遍再合并結(jié)果這種廣播是最需要避免的。理解這個代理模型還有一個實際意義你的 SQL 最終會被真實執(zhí)行在某個物理節(jié)點上所以物理庫的容量、慢查詢、死鎖這些指標最終要回到 MySQL 側(cè)去看不能只盯著 Mycat 的監(jiān)控頁面。2.2 schema.xml、rule.xml、server.xml 各管一段誰也替代不了誰Mycat 的配置集中在 conf 目錄下的三個 XML 文件里。我見過不少團隊只改其中一個卻指望它生效路由不對就開始懷疑中間件有 bug實際上這三份文件的分工非常明確。server.xml 管 Mycat 自己的連接賬號、端口、系統(tǒng)參數(shù)。用什么用戶名密碼連接邏輯庫允許的最大連接數(shù)全局序列采用哪種方案都在這里定義。schema.xml 管邏輯庫和物理庫的映射邏輯庫里有幾張邏輯表、每張表對應哪幾個數(shù)據(jù)節(jié)點、每個數(shù)據(jù)節(jié)點指向哪臺 MySQL 實例、讀寫是否分離。rule.xml 管分片規(guī)則哪張表按哪個字段分片、用什么算法、算法參數(shù)怎么配。三個文件的關(guān)系可以這么記schema.xml 決定有幾張表、表在哪rule.xml 決定數(shù)據(jù)去哪張物理表server.xml 決定誰能連進來、連接資源怎么受限。改完任何一份都要重啟 Mycat 才生效因為它們都是在啟動時一次性加載的。我一般在改動 schema.xml 和 rule.xml 后會先啟動一個 console 模式的臨時實例驗證配置后再切流量避免把線上實例搞掛。2.3 分片字段的四個硬性條件缺一個后面就等著重構(gòu)分片字段是整個分片方案里唯一不能拍腦袋決定的配置。它有四個硬性條件。第一分布夠均勻。按月分片天然適合訂單、日志這類按時間累積的數(shù)據(jù)但如果是按用戶 ID 分片就要考慮高活躍用戶是否會讓某一個分片過熱。第二不允許更新。一旦某行數(shù)據(jù)的分片字段值被修改它在邏輯上需要被移動到另一個物理分片Mycat 對這種數(shù)據(jù)移動的支持非常有限生產(chǎn)上基本只能靠應用層處理。第三查詢條件必須常帶。路由能生效的前提是 SQL 的 where 條件里能提取到分片字段的值如果業(yè)務查詢經(jīng)常不帶這個字段每次都是全分片廣播性能會比單庫還差。第四類型穩(wěn)定。分片字段的類型和傳參格式必須長期不變改類型意味著所有歷史數(shù)據(jù)的路由結(jié)果全部改變這類重構(gòu)在分庫分表場景里代價極高。這四個條件在選字段階段就要全部過一遍不要等上線后再回頭改。按月分片之所以是大多數(shù)團隊的第一選擇就是因為時間字段天然滿足前三條而第四條只要約定好日期格式就能控制住。3. 把 mycat-1.6.7.6 跑起來的完整操作解壓、調(diào) JVM、配數(shù)據(jù)節(jié)點這一章從拿到 mycat-1.6.7.6_BYMONTH.zip 開始一步步把它跑起來并把三個真實 MySQL 數(shù)據(jù)節(jié)點配好。整個過程沒有需要編譯的代碼但有一些參數(shù)不調(diào)好后面會反復出問題。3.1 解壓和 JVM 參數(shù)調(diào)整啟動前必須改的兩處發(fā)行包拿到后先解壓到固定目錄。Mycat 解壓后自帶 bin、conf、lib、logs 等目錄不需要編譯只要有 Java 運行環(huán)境就能啟動。# 解壓發(fā)行包到 /opt 目錄注意 zip 內(nèi)自帶 mycat 目錄 unzip mycat-1.6.7.6_BYMONTH.zip -d /opt/ # 確認目錄結(jié)構(gòu)和 Java 環(huán)境 ls /opt/mycat/ java -version # 啟動前修改 JVM 堆內(nèi)存默認 256M 在分片場景下?lián)尾蛔?# 編輯 /opt/mycat/conf/wrapper.conf至少調(diào)整為 2G/4G # wrapper.java.initmemory2048 # wrapper.java.maxmemory4096 # 先以 console 模式啟動日志實時打到終端方便排錯 /opt/mycat/bin/mycat console # 另開一個終端查看進程狀態(tài) /opt/mycat/bin/mycat statusmycat 啟動腳本支持 start、console、stop、status 等參數(shù)。console 模式用于調(diào)試退出終端進程就停確認配置無誤后生產(chǎn)環(huán)境改用后臺守護方式啟動。wrapper.conf 里的兩個堆內(nèi)存參數(shù)分別控制初始堆和最大堆分片節(jié)點越多、單條結(jié)果集越大這兩個值就要給得越足。我見過不少部署表配置完全沒問題但因為堆內(nèi)存默認值太小運行幾周后頻繁 Full GC路由性能急劇下降誤以為是 Mycat 的 bug。這類玄學問題排查順序永遠是先看 JVM 再看配置。3.2 用三個真實 MySQL 實例配置分片數(shù)據(jù)節(jié)點這一步要準備三臺可用的 MySQL 實例。它們可以分布在三臺物理機上也可以在同一臺機器上跑三個不同端口的實例。分片的物理邊界越獨立后面的水平擴展收益越大如果只是把三個庫放在同一臺機器上那解決的只是單表鎖競爭磁盤 IO 瓶頸還在。?xml version1.0 encodingUTF-8? mycat:schema xmlns:mycathttp://io.mycat/ !-- 邏輯庫名應用 JDBC 連接時使用 -- schema nameLOGIC_DB checkSQLschematrue sqlMaxLimit100 !-- order_log 按 order_time 分片三個數(shù)據(jù)節(jié)點 -- table nameorder_log dataNodedn1,dn2,dn3 rulesharding_by_month/ /schema !-- 數(shù)據(jù)節(jié)點每個節(jié)點指定物理庫名 -- dataNode namedn1 dataHosthostA databaselogdb_01 / dataNode namedn2 dataHosthostB databaselogdb_02 / dataNode namedn3 dataHosthostC databaselogdb_03 / !-- 物理主機 Abalance0 表示先不開啟讀寫分離 -- dataHost namehostA maxCon100 minCon10 balance0 writeType0 dbTypemysql dbDrivernative heartbeatselect user()/heartbeat writeHost hostmysqlA url192.168.10.11:3306 usermcat passwordChangeMe123/ /dataHost !-- hostB 與 hostC 的 dataHost 結(jié)構(gòu)與 hostA 一致按實際 IP 替換 -- dataHost namehostB maxCon100 minCon10 balance0 writeType0 dbTypemysql dbDrivernative heartbeatselect user()/heartbeat writeHost hostmysqlB url192.168.10.12:3306 usermcat passwordChangeMe123/ /dataHost dataHost namehostC maxCon100 minCon10 balance0 writeType0 dbTypemysql dbDrivernative heartbeatselect user()/heartbeat writeHost hostmysqlC url192.168.10.13:3306 usermcat passwordChangeMe123/ /dataHost /mycat:schemaschema 標簽里聲明了邏輯庫 LOGIC_DBtable 標簽的 dataNode 屬性列出三個數(shù)據(jù)節(jié)點rule 屬性指向 rule.xml 里的規(guī)則名。dataNode 只是邏輯指針真正連接到哪臺 MySQL 由 dataHost 決定。writeHost 里的 url 指向物理 MySQL賬號需要有建表、讀寫和查詢元數(shù)據(jù)的權(quán)限。這里 balance0 表示所有讀寫都走 writeHost先把分片跑通再加 readHost排查問題時少一個變量總是好的。三個物理庫名 logdb_01、logdb_02、logdb_03 需要在 MySQL 側(cè)提前建好Mycat 不會自動建庫。建表 DDL 也必須到每個物理庫手動執(zhí)行具體內(nèi)容下一章會講到。3.3 EXPLAIN 驗證分片路由上線前必做的自檢配置是否生效不要憑感覺直接查路由。-- 在 Mycat 邏輯庫執(zhí)行觀察輸出里的節(jié)點信息 EXPLAIN SELECT * FROM order_log WHERE order_time 2025-03-12;如果配置正確EXPLAIN 輸出的節(jié)點信息會指向 dn1、dn2、dn3 中的某一個如果同時出現(xiàn)多個節(jié)點說明這條 SQL 的條件沒有被識別為分片字段Mycat 走了全分片廣播。這條命令是我每次改完 rule.xml 之后必做的第一個檢查它比任何日志都直觀展示的是 Mycat 內(nèi)部真實的路由計算結(jié)果。廣播本身在少數(shù)場景下是可以接受的比如按月分片后某些運營報表必須跨全月查詢但日常交易類 SQL 必須做到精確路由否則分片不但沒解決問題還引入了一層代理開銷。4. BYMONTH 分片規(guī)則從 rule.xml 到建表語句三處配置逐一攻破這一章進入標題的核心BYMONTH 按月分片的具體配置。很多文章只貼 rule.xml 就結(jié)束了但實際落地時物理庫的 DDL 和節(jié)點數(shù)規(guī)劃同樣決定成敗。4.1 PartitionByMonth 算法原理月份差取模一句話就能講透Mycat 自帶的按月分片算法是 PartitionByMonth它的內(nèi)部邏輯可以簡化成一句話把傳入日期與基準日期的月份差算出來對數(shù)據(jù)節(jié)點總數(shù)取模結(jié)果就是目標分片下標。具體來說算法會解析日期字符串得到年、月再與 sBeginDate 配置的起始年、月做差值。月份差等于 0 表示當月數(shù)據(jù)落在第 1 個節(jié)點等于 1 落在第 2 個節(jié)點依此類推。如果數(shù)據(jù)節(jié)點有 12 個那么一年 12 個月剛好各占一個節(jié)點第 13 個月到來時月份差對 12 取?；氐?0數(shù)據(jù)再次寫入第 1 個節(jié)點循環(huán)往復。這個算法的優(yōu)勢是簡單、計算開銷極小路由只依賴日期字段不需要查任何元數(shù)據(jù)表。代價是它不感知業(yè)務流量的波峰波谷如果業(yè)務旺季是 3 月那 3 月對應的那個分片永遠是最熱的其他分片相對空閑。理解這一點很重要因為它直接決定了下面節(jié)點數(shù)的配置策略。4.2 按月分片的 tableRule、function 和物理表 DDLrule.xml 是分片規(guī)則的唯一落點。配置分兩層tableRule 定義這張表的規(guī)則入口function 定義實際執(zhí)行分片的算法和參數(shù)。!-- tableRuleorder_log 表按 order_time 字段進行分片 -- tableRule namesharding_by_month rule columnsorder_time/columns algorithmpartbyMonth/algorithm /rule /tableRule !-- function使用 Mycat 自帶的 PartitionByMonth -- function namepartbyMonth classio.mycat.route.function.PartitionByMonth property namedateFormatyyyy-MM-dd/property property namesBeginDate2024-01-01/property /functioncolumns 是邏輯表的分片字段必須與 schema.xml 里 table 的列名一致algorithm 的名字要能對上 function 的 name 屬性。dateFormat 是日期字符串的解析格式?jīng)Q定了應用層傳參能不能被正確識別。sBeginDate 是月份差計算的基準月我強烈建議設置成業(yè)務最早可能出現(xiàn)數(shù)據(jù)的月份而不是部署當天原因在下一章的排坑里會詳細說。寫好 rule.xml 后還要到三個物理庫執(zhí)行建表語句。Mycat 分片只是路由層物理表和索引必須自己在每個庫建好一個都不能少。-- 在 logdb_01、logdb_02、logdb_03 三個物理庫分別執(zhí)行 CREATE TABLE order_log ( id BIGINT NOT NULL COMMENT 業(yè)務主鍵由全局序列生成, order_time DATETIME NOT NULL COMMENT 下單時間同時是分片字段, user_id BIGINT NOT NULL COMMENT 用戶ID, amount DECIMAL(12,2) NOT NULL COMMENT 訂單金額, PRIMARY KEY (id), KEY idx_order_time (order_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;這里有一個常見誤用有人在第一個物理庫建了臨時表就讓應用開始寫入結(jié)果同一張邏輯表在另外兩個節(jié)點上根本不存在路由過去后直接報 table doesnt exist。多分片環(huán)境里DDL 變更腳本要寫成循環(huán)執(zhí)行的形式每張物理表同步執(zhí)行。這是我最早踩過的坑也是分片環(huán)境運維和單庫運維最不一樣的地方。4.3 節(jié)點數(shù)怎么定數(shù)據(jù)分布和循環(huán)周期的權(quán)衡PartitionByMonth 的取模空間就是數(shù)據(jù)節(jié)點數(shù)所以節(jié)點數(shù)直接決定兩件事一是每個分片最多積累多少數(shù)據(jù)量二是數(shù)據(jù)循環(huán)回退的周期多長。如果節(jié)點數(shù)設為 12每個分片承載一個月的數(shù)據(jù)13 個月后新數(shù)據(jù)寫回第 1 個節(jié)點第 1 個節(jié)點里就有兩個完整月份的數(shù)據(jù)。如果業(yè)務數(shù)據(jù)量大這樣的循環(huán)會讓某些分片持續(xù)膨脹最終比單表還難維護。常見的做法是節(jié)點數(shù)設置為 12 的整數(shù)倍比如 24 或 36讓每個分片只承載半個月或三分之一月的數(shù)據(jù)或者保持一月一節(jié)點但配合歸檔任務把超過 12 個月的物理分片從邏輯配置中摘掉。另一個決策點是物理分片用節(jié)點下標還是業(yè)務月份命名。我的偏好是用 dn1、dn2 這類啞編號命名 dataNode 和物理庫把dn1 代表哪個月的數(shù)據(jù)完全交給規(guī)則計算而不是把庫名寫成 logdb_202401。這樣調(diào)整節(jié)點數(shù)或者遷移分片時不用改物理庫名只改 schema.xml 的映射即可靈活性高很多。5. 按月分片排坑與常見問題最容易翻車的 5 個場景這一章把我見到過的、以及自己踩過的坑整理成現(xiàn)象、原因、解決三步按嚴重程度排序。每一條都對應一個真實發(fā)生過的問題照著檢查能省下不少半夜回滾的時間。5.1 日期格式不匹配點查變成全分片廣播現(xiàn)象一條按天點查的 SQL 本應只訪問一個分片慢查詢?nèi)罩纠飬s出現(xiàn)三個物理庫同時執(zhí)行同一條 SQL。原因應用層傳的日期字符串是 2025/03/12rule.xml 里 dateFormat 配置的是 yyyy-MM-ddPartitionByMonth 解析失敗后被迫退回全分片廣播。解決統(tǒng)一應用傳參與 dateFormat 的格式包括時間段查詢的邊界是閉區(qū)間還是開區(qū)間在第一次對接時就約定好。改完 rule.xml 需要重啟 Mycat 生效這類約定最好寫進開發(fā)規(guī)范里讓所有團隊共用一套日期格式。5.2 sBeginDate 起始月設成部署當月歷史數(shù)據(jù)路由錯亂現(xiàn)象上線半年后補錄的歷史訂單查詢時用 explain 發(fā)現(xiàn)路由到了錯誤的節(jié)點直接查不到數(shù)據(jù)。原因sBeginDate 設成了部署當月導致所有更早日期的月份差為負數(shù)。負數(shù)取模在 Java 里可能是負下標Mycat 找不到對應數(shù)據(jù)節(jié)點報錯或者路由錯亂都出現(xiàn)過。解決sBeginDate 必須取業(yè)務最早可能出現(xiàn)數(shù)據(jù)的那個自然月寧可早一個月也不要晚。數(shù)據(jù)遷移上線時先查一下源表的最小日期再決定這個值填什么。5.3 分片字段為 NULL寫入直接報 cant find datanode現(xiàn)象某個寫入入口沒有傳 order_timeinsert 語句執(zhí)行時報 cant find any valid datanode。原因路由階段拿不到可解析的日期值PartitionByMonth 無法計算目標分片下標這條 SQL 就失去了路由依據(jù)。解決物理表 DDL 將分片字段設為 NOT NULL應用層在寫入前做默認值兜底。同時排查這個入口為什么沒傳值通常背后是一個被忽略的接口字段缺失兜底只是防御手段。5.4 多表 JOIN 分片鍵不一致跨節(jié)點聚合失敗現(xiàn)象order_log 與 order_detail 關(guān)聯(lián)查詢時Mycat 報跨節(jié)點關(guān)聯(lián)不支持或者查詢變得極慢。原因兩張表的分片字段或者分片規(guī)則不一致同一筆訂單的主表和明細可能落到不同物理節(jié)點Mycat 無法在本地完成 JOIN。解決要么把明細表的分片字段也設為 order_time保證同月份數(shù)據(jù)在同一個節(jié)點要么使用 Mycat 的 ER 關(guān)系配置把 order_detail 配置成 order_log 的 childTable。前者改動最小適合訂單這種按時間訪問的場景后者適合明細表必須獨立分片的場景。5.5 漏配全局序列三個分片的主鍵全部從 1 開始現(xiàn)象插入數(shù)據(jù)沒有報錯但后續(xù)查詢發(fā)現(xiàn)邏輯主鍵重復多個物理表中都出現(xiàn) id1 的記錄。原因每個物理 MySQL 的 auto_increment 各自獨立從 1 開始遞增分片后主鍵必然沖突。解決在 server.xml 里配置全局序列Mycat 支持本地時間戳、數(shù)據(jù)庫、自定義等多種方案。對訂單表這種需要回顯主鍵的場景我一般用數(shù)據(jù)庫方式保證嚴格遞增對日志類不關(guān)心主鍵順序的場景本地時間戳方式就夠用。要注意邏輯表的主鍵生成必須交給 Mycat 處理應用不能再自行傳 id否則序列配置形同虛設。6. 進階驗證技巧給按月分片數(shù)據(jù)做個體檢配置全部落地后別急著慶祝。我給自己定了一條規(guī)矩每套分片方案上線前都要跑完三遍體檢確認之后再讓業(yè)務流量進來。體檢第一項是路由精確性檢查。把三種代表性 SQL 用 EXPLAIN 各跑一遍按月點查、按月范圍查詢、不帶分片字段的查詢。前兩種必須路由到單節(jié)點第三種如果廣播要明確這是有意的全量查詢還是漏了條件。體檢第二項是分片數(shù)據(jù)量分布檢查。登錄三個物理庫分別執(zhí)行下面這條 SQL對比各分片的行數(shù)和容量-- 分別在三個物理庫執(zhí)行對比分片數(shù)據(jù)分布 SELECT logdb_01 AS db_name, COUNT(*) AS row_count, ROUND(SUM(data_length index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema logdb_01 AND table_name order_log UNION ALL SELECT logdb_02, COUNT(*), ROUND(SUM(data_length index_length) / 1024 / 1024, 2) FROM information_schema.tables WHERE table_schema logdb_02 AND table_name order_log UNION ALL SELECT logdb_03, COUNT(*), ROUND(SUM(data_length index_length) / 1024 / 1024, 2) FROM information_schema.tables WHERE table_schema logdb_03 AND table_name order_log;按月分片天然允許各節(jié)點數(shù)據(jù)量不均衡這里要看的是有沒有某個分片遠超容量閾值。如果 3 月是旺季dn3 數(shù)據(jù)量是 dn1 的兩倍屬于預期但如果某個分片已經(jīng)逼近物理磁盤容量就該考慮增加節(jié)點或者把歷史分片歸檔出去了。體檢第三項是一致性抽查。對近三個月的訂單從邏輯庫按 id 查一條再直連對應物理庫按相同條件查一條比對結(jié)果一致。這一步不復雜但能發(fā)現(xiàn)隱藏的配置錯誤。最后講一個自己的教訓作為收尾最早做分片時我隨手把 sBeginDate 填成部署當天三個月后補錄歷史數(shù)據(jù)全部路由錯亂那次是連夜回滾才扛過去。之后我把 rule.xml 里每個屬性的取值邏輯都寫進了上線前檢查單再也沒有因為分片配置翻過車。希望幫到你。本文還有配套的精品資源點擊獲取