分庫(kù)分表實(shí)戰(zhàn)內(nèi)容基本上很少有人去分享，在網(wǎng)上能夠搜出來的也大多屬于一些方法論，但大部分技術(shù)開發(fā)真正缺少的恰恰是這些實(shí)操經(jīng)驗(yàn)，所以后續(xù)的內(nèi)容多以實(shí)踐為主，攜手諸位真正徹底悟透分庫(kù)分表相關(guān)的技術(shù)。

尤其是對(duì)于庫(kù)內(nèi)分表這塊的分享，當(dāng)你去搜索單表數(shù)據(jù)增長(zhǎng)過快該如何處理時(shí)，一般都會(huì)推薦你做分表處理，但你幾乎找不到較為全面的實(shí)操教學(xué)，網(wǎng)上講述分表技術(shù)更多是停留在表面的理論概念層次做闡述，而本章中則會(huì)結(jié)合自身之前接觸的一個(gè)項(xiàng)目業(yè)務(wù)，再對(duì)庫(kù)內(nèi)分表技術(shù)進(jìn)行全面闡述~

1. 源自于軟硬結(jié)合的特殊業(yè)務(wù)

在講本次主題之前，先來聊聊之前碰到的這個(gè)業(yè)務(wù)，這個(gè)業(yè)務(wù)比較特殊，相信很多小伙伴從未碰到過，這種業(yè)務(wù)本身用戶量大，甚至可以說用戶量非常非常少，因?yàn)闃I(yè)務(wù)的起源來自于一款硬件設(shè)備，但具體的設(shè)備類型由于某些緣故就不透露了，可以理解成是下面這個(gè)東東：

雖然當(dāng)時(shí)的硬件設(shè)備并不是這個(gè)，但也和它很類似，相信大家但凡在超市購(gòu)過物都認(rèn)識(shí)它，也就是超市收銀臺(tái)的收銀機(jī)，當(dāng)時(shí)我們是對(duì)外提供了一千臺(tái)設(shè)備，這種設(shè)備通常一臺(tái)只有一個(gè)用戶，所以當(dāng)時(shí)整個(gè)系統(tǒng)上線后所有的用戶加起來，涵蓋后臺(tái)管理員、超級(jí)管理員賬號(hào)在內(nèi)，也不過1200個(gè)用戶，這個(gè)用戶規(guī)模相較于常見業(yè)務(wù)而言屬實(shí)不多。

而當(dāng)時(shí)我們需要負(fù)責(zé)的就是：為這些設(shè)備開發(fā)一個(gè)操作系統(tǒng)，這里不是指Windows、Linux、Mac這類嵌入式的底層系統(tǒng)，而是給機(jī)器的操作員開發(fā)一個(gè)操作界面，就類似于諸位在超市購(gòu)物時(shí)，超市收銀員用手操作的那個(gè)界面。

因?yàn)檫@些機(jī)器本身會(huì)安裝一個(gè)帶UI的系統(tǒng)，里面也支持安裝一些軟件，我們的軟件會(huì)以GUI的形式嵌入這些設(shè)備，當(dāng)時(shí)我要干的就是直接開發(fā)API接口，然后提供給GUI界面界面調(diào)用。本質(zhì)上就屬一個(gè)前后端分離的項(xiàng)目，只不過前端從原本的Web界面變成了GUI界面。

大家聽起來這個(gè)項(xiàng)目是不是特別容易完成，用戶量又少代表不需要考慮并發(fā)，也不會(huì)存在太大的流量沖擊，性能要求也不會(huì)太高，似乎就是一個(gè)簡(jiǎn)簡(jiǎn)單單的單體增刪改查項(xiàng)目呀？但事情遠(yuǎn)沒有表面這么簡(jiǎn)單，諸位請(qǐng)接著往下看。

1.1、項(xiàng)目的難點(diǎn)

起初當(dāng)我收到通知要負(fù)責(zé)這個(gè)需求時(shí)，從表面淺顯的想了一下，似乎發(fā)現(xiàn)也不是太難，就是一個(gè)單體項(xiàng)目的CRUD工作，以我這手出神入化的CV大法，Hlod住它簡(jiǎn)直輕輕松松，因此當(dāng)時(shí)也沒想太多就直接接手了，項(xiàng)目初期由于團(tuán)隊(duì)每位成員經(jīng)驗(yàn)都很豐富，各自憑借著個(gè)人的Copy神功，項(xiàng)目的開發(fā)進(jìn)度可謂是一騎千里，但慢慢的問題來了，而且這個(gè)問題還不小！

當(dāng)時(shí)大概對(duì)外預(yù)計(jì)分發(fā)1000臺(tái)機(jī)器，每臺(tái)機(jī)器正式投入運(yùn)營(yíng)后，預(yù)估單日會(huì)產(chǎn)生500~600條數(shù)據(jù)的產(chǎn)出，套到前面的舉例中，也就是大概會(huì)向幾百個(gè)超市投放共計(jì)1000臺(tái)收銀機(jī)，每個(gè)收銀臺(tái)平均下來之后，大概單日內(nèi)會(huì)有500~600個(gè)顧客結(jié)賬！

這里咱們做個(gè)數(shù)學(xué)題：現(xiàn)在有1000臺(tái)機(jī)器，每臺(tái)機(jī)器單日就算產(chǎn)生500條數(shù)據(jù)：1000 * 500 = 500000，這也就意味著單日的賬單表中會(huì)新增50W條流水?dāng)?shù)據(jù)，單月整個(gè)賬單表的數(shù)據(jù)增長(zhǎng)量為：50W * 30 = 1500W！

單月數(shù)據(jù)增長(zhǎng)1500W的概念不言而喻，這也就代表著一年的數(shù)據(jù)增長(zhǎng)量為1500W * 12 = 1.8E，這批機(jī)器投入后預(yù)估最少會(huì)運(yùn)行三年起步，甚至十年乃至更久，同時(shí)第一批次就要投入1000臺(tái)，后面可能還會(huì)有第二批次、第三批次.....的投入。

50W只是最低的賬單流水?dāng)?shù)據(jù)量，后續(xù)正式運(yùn)營(yíng)后可能數(shù)據(jù)量更大，此時(shí)架構(gòu)的設(shè)計(jì)就成了難題！

1.2、方案的探討

基本上當(dāng)時(shí)團(tuán)隊(duì)的成員中，沒人在此之前碰過這類需求，因此開了一個(gè)研討會(huì)，去決定該如何將具體的方案落地，這里有人也許會(huì)說，數(shù)據(jù)量這么大，快上分布式/微服務(wù)?。〉珜?shí)則解決不了這個(gè)問題，Why？因?yàn)轫?xiàng)目整體的用戶量并不大，最多同一時(shí)刻也才1000并發(fā)請(qǐng)求，就算這個(gè)并發(fā)量再增大幾個(gè)級(jí)別，這里用單體架構(gòu)優(yōu)化好了也能夠抗住，所以問題并不在業(yè)務(wù)系統(tǒng)的架構(gòu)上面，而是在數(shù)據(jù)落庫(kù)這方面。

這里直接用分庫(kù)可以嗎？答案是也不行，Why？因?yàn)檎麄€(gè)項(xiàng)目中只有賬單表才有這么大的數(shù)據(jù)量，其他的用戶表、系統(tǒng)表、功能菜單表、后臺(tái)表......，基本上不會(huì)有太大的數(shù)據(jù)量，所以直接做分庫(kù)也沒必要，屬實(shí)有些浪費(fèi)資源。

有小伙伴可能想到了！可以按月份對(duì)流水表做分區(qū)呀！乍一聽似乎像那么一回事，但依舊不行，因?yàn)榈谝慌鷻C(jī)器投入后，單月預(yù)計(jì)就會(huì)產(chǎn)生1500W條數(shù)據(jù)，后續(xù)可能會(huì)增加機(jī)器數(shù)量，因此單月的數(shù)據(jù)量達(dá)到2000W、3000W.....都有可能，如果按月做表分區(qū)，每個(gè)分區(qū)里面都有幾千萬條數(shù)據(jù)，一張賬單表的流水隨著時(shí)間推移，數(shù)據(jù)量甚至?xí)_(dá)到幾十億！

一張表中存儲(chǔ)幾十億條數(shù)據(jù)，這基本上不現(xiàn)實(shí)，雖然InnoDB在數(shù)據(jù)頁(yè)為16KB尺寸下，單表最多能存儲(chǔ)64TB數(shù)據(jù)，有可能這幾十億條數(shù)據(jù)真的能存下去，但查詢時(shí)的性能簡(jiǎn)直令人頭大，并且最關(guān)鍵的是不方便后續(xù)對(duì)數(shù)據(jù)做維護(hù)、管理、備份和遷移工作。

因此經(jīng)過一番探討后，最后決定選擇了表分區(qū)技術(shù)的進(jìn)階版實(shí)現(xiàn)，即單庫(kù)內(nèi)做水平分表，按月份對(duì)數(shù)據(jù)做分表，也就是將賬單表分為month_bills_202210、month_bills_202211、month_bills_202212.......以月份結(jié)尾的多張表，每個(gè)月的賬單流水?dāng)?shù)據(jù)最終都會(huì)插入到各自的月份表中。

最終架構(gòu)定型為：業(yè)務(wù)系統(tǒng)使用單體架構(gòu) + 數(shù)據(jù)庫(kù)使用單庫(kù) + 流水表按月份做水平分表。

2. 按月分表方案的落地實(shí)踐

在上一階段中已經(jīng)決定好了具體的方案，但又該如何將方案落地呢？首先咱們先把方案落地的思路捋清楚：

• ①能夠自動(dòng)按月創(chuàng)建一張?jiān)路葙~單表，從而將每月的流水?dāng)?shù)據(jù)寫入進(jìn)去。
• ②寫入數(shù)據(jù)時(shí)，能夠根據(jù)當(dāng)前的日期，選擇對(duì)應(yīng)的月份賬單表并插入數(shù)據(jù)。

實(shí)現(xiàn)了上面兩個(gè)需求后，整個(gè)方案近乎落地了一半，但接下來該如何去實(shí)現(xiàn)相應(yīng)功能呢？咱們一點(diǎn)點(diǎn)來動(dòng)手實(shí)現(xiàn)。

2.1、利用存儲(chǔ)過程實(shí)現(xiàn)按月動(dòng)態(tài)創(chuàng)建表

創(chuàng)建表的SQL語句大家都不陌生，按月份創(chuàng)建表之前，自然也需要一份原生創(chuàng)建表的DDL語句，如下：

CREATETABLE`month_bills_202211`(
`month_bills_id`int(8)NOTNULLAUTO_INCREMENTCOMMENT'賬單ID',
`serial_number`varchar(50)NOTNULLCOMMENT'流水號(hào)',
`bills_info`textNOTNULLCOMMENT'賬單詳情',
`pay_money`decimal(10,3)NOTNULLCOMMENT'支付金額',
`machine_serial_no`varchar(20)NOTNULLCOMMENT'收銀機(jī)器',
`bill_date`timestampNOTNULLCOMMENT'賬單日期',
`bill_comment`varchar(100)NULLDEFAULT'無'COMMENT'賬單備注',
PRIMARYKEY(`month_bills_id`)USINGBTREE,
UNIQUE`serial_number`(`serial_number`),
KEY`bill_date`(`bill_date`)
)
ENGINE=InnoDB
CHARACTERSET=utf8
COLLATE=utf8_general_ci
ROW_FORMAT=Compact;

上述的語句會(huì)創(chuàng)建一張?jiān)路葙~單表，這張表主要包含七個(gè)字段，如下：

其中注意的幾個(gè)小細(xì)節(jié)：

• ①日期字段使用的是timestamp類型，而并非datetime，因?yàn)榍罢吒】臻g。
• ②賬單詳情字段用的是text類型，因?yàn)檫@個(gè)字段可能會(huì)出現(xiàn)很多的信息。
• ③定義了一個(gè)和表沒有關(guān)系的自增字段作為主鍵，用于維護(hù)聚簇索引樹的結(jié)構(gòu)。

除開有上述七個(gè)字段外，還有三個(gè)索引：

到這里就有了最基本的建表語句，主要是用來創(chuàng)建第一張?jiān)路葙~單表，如果想要實(shí)現(xiàn)動(dòng)態(tài)按照每月建表的話，還需要用到存儲(chǔ)過程來實(shí)現(xiàn)，接著來寫一個(gè)存儲(chǔ)過程。

最終撰寫出的存儲(chǔ)過程如下：

DELIMITER//
DROPPROCEDUREIFEXISTScreate_table_by_month//
CREATEPROCEDURE`create_table_by_month`()
BEGIN
--用于記錄下一個(gè)月份是多久
DECLAREnextMonthvarchar(20);
--用于記錄創(chuàng)建表的SQL語句
DECLAREcreateTableSQLvarchar(5210);
--執(zhí)行創(chuàng)建表的SQL語句后，獲取表的數(shù)量
DECLAREtableCountint;
--用于記錄要生成的表名
DECLAREtableNamevarchar(20);
--用于記錄表的前綴
DECLAREtable_prefixvarchar(20);

--獲取下個(gè)月的日期并賦值給nextMonth變量
SELECTSUBSTR(
replace(
DATE_ADD(CURDATE(),INTERVAL1MONTH),
'-',''),
1,6)INTO@nextMonth;

--設(shè)置表前綴變量值為td_user_banks_log_
set@table_prefix='month_bills_';

--定義表的名稱=表前綴+月份，即month_bills_2022112這個(gè)格式
SET@tableName=CONCAT(@table_prefix,@nextMonth);
--定義創(chuàng)建表的SQL語句
set@createTableSQL=concat("createtableifnotexists",@tableName,"(
`month_bills_id`int(8)NOTNULLAUTO_INCREMENTCOMMENT'賬單ID',
`serial_number`varchar(50)NOTNULLCOMMENT'流水號(hào)',
`bills_info`textNOTNULLCOMMENT'賬單詳情',
`pay_money`decimal(10,3)NOTNULLCOMMENT'支付金額',
`machine_serial_no`varchar(20)NOTNULLCOMMENT'收銀機(jī)器',
`bill_date`timestampNOTNULLDEFAULTnow()COMMENT'賬單日期',
`bill_comment`varchar(100)NULLDEFAULT'無'COMMENT'賬單備注',
PRIMARYKEY(`month_bills_id`)USINGBTREE,
UNIQUE`serial_number`(`serial_number`),
KEY`bill_date`(`bill_date`)
)ENGINE=InnoDB
CHARACTERSET=utf8
COLLATE=utf8_general_ci
ROW_FORMAT=Compact;");

--使用PREPARE關(guān)鍵字來創(chuàng)建一個(gè)預(yù)備執(zhí)行的SQL體
PREPAREcreate_stmtfrom@createTableSQL;
--使用 EXECUTE 關(guān)鍵字來執(zhí)行上面的預(yù)備SQL體：create_stmt 
EXECUTEcreate_stmt;
--釋放掉前面創(chuàng)建的SQL體（減少內(nèi)存占用）
DEALLOCATEPREPAREcreate_stmt;

--執(zhí)行完建表語句后，查詢表數(shù)量并保存再tableCount變量中
SELECT
COUNT(1)INTO@tableCount
FROM
information_schema.`TABLES`
WHERETABLE_NAME=@tableName;

--查詢一下對(duì)應(yīng)的表是否已存在
SELECT@tableCount'tableCount';

END//
delimiter;

上述這個(gè)存儲(chǔ)過程比較長(zhǎng)，但基本上都寫好了注釋，所以閱讀起來應(yīng)該還是比較輕松的，也包括該存儲(chǔ)過程在MySQL5.1、8.0版本中都測(cè)試過，所以大家也可以直接用，主要拆解一下里面較為難理解的一句SQL，如下：

SELECTSUBSTR(
replace(
DATE_ADD(CURDATE(),INTERVAL1MONTH),
'-',''),
1,6)INTO@nextMonth;

這條語句執(zhí)行之后會(huì)生成一個(gè)202212這樣的月份數(shù)字，主要用來作為表名的后綴，以此來區(qū)分不同的表，但里面用了幾個(gè)函數(shù)組合出了該效果，下面做一下拆解，如下：

--在當(dāng)前日期的基礎(chǔ)上增加一個(gè)月，如2022-11-122311，會(huì)得到2022-12-122311
selectDATE_ADD(CURDATE(),INTERVAL1MONTH);

--使用空字符代替日期中的-符號(hào)，得到202212122311這樣的效果
selectreplace('2022-12-122311','-','');

--對(duì)字符串做截取，獲取第一位到第六位，得到202212這樣的效果
selectSUBSTR("202212122311",1,6);

經(jīng)過上述拆解之后大家應(yīng)該能看的很清楚，最終每次調(diào)用該存儲(chǔ)過程時(shí)，都會(huì)基于當(dāng)前數(shù)據(jù)庫(kù)的時(shí)間，然后向后增加一個(gè)月，同時(shí)將格式轉(zhuǎn)化為YYYYMM格式，接下來調(diào)用該存儲(chǔ)過程，如下：

callcreate_table_by_month();
+------------+
|tableCount|
+------------+
|1|
+------------+

當(dāng)返回的值為1而并非0時(shí)，就表示已經(jīng)在數(shù)據(jù)庫(kù)中查到了前面通過存儲(chǔ)過程創(chuàng)建的表，即表示動(dòng)態(tài)創(chuàng)建表的存儲(chǔ)過程可以生效！接著為了能夠每月定時(shí)觸發(fā)，可以在MySQL中注冊(cè)一個(gè)每月執(zhí)行一次的定時(shí)事件，如下：

createEVENT
`create_table_by_month_event`--創(chuàng)建一個(gè)定時(shí)器
ONSCHEDULEEVERY
1MONTH--每間隔一個(gè)月執(zhí)行一次
STARTS
'2022-11-280000'--從2022-11-280000后開始
ONCOMPLETION
PRESERVEENABLE--執(zhí)行完成之后不刪除定時(shí)器
DO
callcreate_table_by_month();--每次觸發(fā)定時(shí)器時(shí)執(zhí)行的語句

MySQL5.1版本中除開引入了存儲(chǔ)過程/函數(shù)、觸發(fā)器的支持外，還引入了定時(shí)器的技術(shù)，也就是支持定時(shí)執(zhí)行一條SQL，此時(shí)咱們可借助MySQL自帶的定時(shí)器來定時(shí)調(diào)用之前的存儲(chǔ)過程，最終實(shí)現(xiàn)按月定時(shí)創(chuàng)建表的需求！

但定時(shí)器在使用之前，需要先查看定時(shí)器是否開啟，如下：show variables like 'event_scheduler';如果是OFF關(guān)閉狀態(tài)，需要通過set global event_scheduler = 1 | on;命令開啟。如果想要永久生效，MySQL8.0以下的版本可找到my.ini/my.conf文件，然后找到[mysqld]的區(qū)域，再里面多加入一行event_scheduler = ON的配置即可。

這里再附上一些管理定時(shí)器的命令：

--查看創(chuàng)建的定時(shí)器
showevents;
select*frommysql.event;
select*frominformation_schema.EVENTS;
--刪除一個(gè)定時(shí)器
dropevent定時(shí)器名稱;
--關(guān)閉一個(gè)定時(shí)器任務(wù)
alterevent定時(shí)器名稱onCOMPLETIONPRESERVEDISABLE;
--開啟一個(gè)定時(shí)器任務(wù)
alterevent定時(shí)器名稱onCOMPLETIONPRESERVEENABLE;

經(jīng)過上述幾步后，就能夠讓MySQL自己按月創(chuàng)建表了，但為啥我會(huì)將定時(shí)器的時(shí)間設(shè)置為2022-11-28 0000這個(gè)時(shí)間后開始呢？因?yàn)?code style="font-size:14px;font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;padding:2px 4px;margin-right:2px;margin-left:2px;color:rgb(233,105,0);background:rgb(248,248,248);">202211這張表我已經(jīng)手動(dòng)建立了，不將建立表的工作放在月初一號(hào)執(zhí)行，這是因?yàn)榍懊娴拇鎯?chǔ)過程是創(chuàng)建下月表，而不是創(chuàng)建當(dāng)月表，同時(shí)月底提前創(chuàng)建下月表，還能提高容錯(cuò)率，在MySQL定時(shí)器故障的情況下，能預(yù)留人工介入的時(shí)間。

2.2、寫入數(shù)據(jù)時(shí)能夠根據(jù)月份插入對(duì)應(yīng)表

作為一個(gè)后端項(xiàng)目，必然還需要搭建客戶端，這里用SpringBoot+MyBatis來快速構(gòu)建一個(gè)單體項(xiàng)目（最后會(huì)給出完整源碼），這里需要注意，月份賬單表對(duì)應(yīng)的實(shí)體類中要多出一個(gè)targetTable字段，如下：

publicclassMonthBills{
//月份賬單表ID
privateIntegermonthBillsId;
//賬單流水號(hào)
privateStringserialNumber;
//支付金額
privateBigDecimalpayMoney;
//收銀機(jī)器
privateStringmachineSerialNo;
//賬單日期
privateDatebillDate;
//賬單詳情
privateStringbillsInfo;
//賬單備注
privateStringbillComment;

//要操作的目標(biāo)表
privateStringtargetTable;

//省略構(gòu)造方法和Get/Set方法.....
}

上述的實(shí)體類與之前的表字段結(jié)構(gòu)幾乎完全相同，但會(huì)多出一個(gè)targetTable屬性，后續(xù)會(huì)用來記錄要操作的目標(biāo)表，接著再撰寫一個(gè)工具類，如下：

publicclassTableTimeUtils{
/*
*使用ThreadLocal來確保線程安全，或者可以使用Java8新引入的DateTimeFormatter類：
* monthTL：負(fù)責(zé)將一個(gè)日期處理成 YYYYMM 格式
*/
privatestaticThreadLocalmonthTL=
ThreadLocal.withInitial(()->
newSimpleDateFormat("YYYYMM"));

//表的前綴
privatestaticStringtablePrefix="month_bills_";

//將一個(gè)日期格式化為YYYYMM格式
publicstaticStringgetYearMonth(Datedate){
returnmonthTL.get().format(date);
}

//獲取目標(biāo)數(shù)據(jù)的表名（操作單條數(shù)據(jù)公用的方法：增刪改查）
publicstaticvoidgetDataByTable(MonthBillsmonthBills){
//獲取傳入對(duì)象的時(shí)間
DatebillDate=monthBills.getBillDate();
//根據(jù)該對(duì)象中的時(shí)間，計(jì)算出要操作的表名后綴
StringyearMonth=getYearMonth(billDate);
//將表前綴和后綴拼接，得到完整的表名，如：month_bills_202211 
monthBills.setTargetTable(tablePrefix+yearMonth);
}
}

這個(gè)工具類主要負(fù)責(zé)處理日期的時(shí)間格式，以及用來定位要操作的目標(biāo)表名，對(duì)于日期格式化類：SimpleDateFormat由于是線程不安全的，所以使用ThreadLocal來確保線程安全！上述工具類中主要提供了兩個(gè)基礎(chǔ)方法：

• getYearMonth()：將一個(gè)日期格式化成YYYYMM格式。
• getDataByTable()：獲取單條數(shù)據(jù)操作時(shí)的表名。

有了工具類后，接著來撰寫Dao、Mapper層的代碼，如下：

@Mapper
@Repository
publicinterfaceMonthBillsMapper{
intdeleteByPrimaryKey(IntegermonthBillsId);

intinsertSelective(MonthBillsrecord);

MonthBillsselectByPrimaryKey(IntegermonthBillsId);

intupdateByPrimaryKeySelective(MonthBillsrecord);
}

上述是月份賬單表對(duì)應(yīng)的Dao/Mapper接口，因?yàn)槲疫@里是通過MyBatis的逆向工程文件自動(dòng)生成的，所以名字就是上面那樣，我這邊未成更改，接著來看看對(duì)應(yīng)的xml文件，如下：

"insertSelective"parameterType="com.zhuzi.dbMachineSubmeter.entity.MonthBills">
insertinto${targetTable}
"("suffix=")"suffixOverrides=",">
<iftest="monthBillsId!=null">
month_bills_id,
if>
<iftest="serialNumber!=null">
serial_number,
if>
<iftest="payMoney!=null">
pay_money,
if>
<iftest="machineSerialNo!=null">
machine_serial_no,
if>
<iftest="billDate!=null">
bill_date,
if>
<iftest="billComment!=null">
bill_comment,
if>
<iftest="billsInfo!=null">
bills_info,
if>

"values("suffix=")"suffixOverrides=",">
<iftest="monthBillsId!=null">
#{monthBillsId,jdbcType=INTEGER},
if>
<iftest="serialNumber!=null">
#{serialNumber,jdbcType=VARCHAR},
if>
<iftest="payMoney!=null">
#{payMoney,jdbcType=DECIMAL},
if>
<iftest="machineSerialNo!=null">
#{machineSerialNo,jdbcType=VARCHAR},
if>
<iftest="billDate!=null">
#{billDate,jdbcType=TIMESTAMP},
if>
<iftest="billComment!=null">
#{billComment,jdbcType=VARCHAR},
if>
<iftest="billsInfo!=null">
#{billsInfo,jdbcType=LONGVARCHAR},
if>

上述這么大一長(zhǎng)串，其實(shí)也不是俺手敲的，依舊是MyBatis逆向工程生成的代碼，但我對(duì)其中的一處稍微做了改動(dòng)，如下：

--原本生成的代碼是：
insertintomonth_bills_202211
--然后被我改成了：
insertinto${targetTable}

還記得最開始的實(shí)體類中，咱們多添加的那個(gè)targetTable屬性嘛？在這里會(huì)根據(jù)該字段的值動(dòng)態(tài)的去操作不同月份的表，接著來寫一下Service層的接口和實(shí)現(xiàn)類，如下：

//Service接口（目前里面只有一個(gè)方法）
publicinterfaceIMonthBillsService{
intinsert(MonthBillsmonthBills);
}

//Service實(shí)現(xiàn)類
@Service
publicclassMonthBillsServiceImplimplementsIMonthBillsService{
@Autowired
privateMonthBillsMapperbillsMapper;

@Override
publicintinsert(MonthBillsmonthBills){
//獲取要插入數(shù)據(jù)的表名
TableTimeUtils.getDataByTable(monthBills);
//返回插入數(shù)據(jù)的狀態(tài)
returnbillsMapper.insertSelective(monthBills);
}
}

在service層目前僅實(shí)現(xiàn)了一個(gè)插入數(shù)據(jù)的方法，其中的邏輯也非常簡(jiǎn)單，僅僅在調(diào)用Dao層的插入方法之前，獲取了一下當(dāng)前這條數(shù)據(jù)要插入的表名，最后來看看Controller/API層，如下：

@RestController
@RequestMapping("/bills")
publicclassMonthBillsAPI{

@Autowired
privateIMonthBillsServicebillsService;

//賬單結(jié)算的API
@RequestMapping("/settleUp")
publicStringsettleUp(MonthBillsmonthBills){
//設(shè)置賬單交易時(shí)間為當(dāng)前時(shí)間
monthBills.setBillDate(newDate(System.currentTimeMillis()));
//使用UUID隨機(jī)生成一個(gè)流水號(hào)
monthBills.setSerialNumber(monthBills.getMachineSerialNo()
+System.currentTimeMillis());
//調(diào)用新增賬單數(shù)據(jù)的service方法
if(billsService.insert(monthBills)>0){
return">>>>賬單結(jié)算成功<<<<";
}
return">>>>賬單結(jié)算失敗<<<<";
}
}

在API層主要對(duì)外提供了一個(gè)賬單結(jié)算的接口，這里為了方便測(cè)試，所以對(duì)于請(qǐng)求方式的處理就沒那么嚴(yán)謹(jǐn)了，在調(diào)用該接口后，會(huì)先獲取一下當(dāng)前系統(tǒng)時(shí)間作為賬單時(shí)間，接著會(huì)隨機(jī)生成一個(gè)UUID作為流水號(hào)，最后就會(huì)調(diào)用service層的insert()方法。

到這里為止就搭建出了一個(gè)最簡(jiǎn)單的WEB接口，接著來做一個(gè)小小的測(cè)試，這里為了方便就不用專門的PostMan工具了，就通過瀏覽器簡(jiǎn)單的調(diào)試一下，接口如下：http://localhost:8080/bills/settleUp?billsInfo=白玉竹子*3：9999.999&payMoney=9999.999&machineSerialNo=NF-002-X

最終測(cè)試效果圖如下：

效果很明顯，確實(shí)做到了咱們需要的效果，接著來看看控制臺(tái)輸出的SQL日志，如下：

主要可以觀察到，原本xml中的動(dòng)態(tài)表名，最終會(huì)根據(jù)月份被替換為具體的表名，最后再來看看數(shù)據(jù)庫(kù)中的表是否真正插入了數(shù)據(jù)，如下：

因?yàn)橹皽y(cè)試過一次，因此表中早有了一條數(shù)據(jù)，主要觀察第二條，的確是咱們剛剛測(cè)試時(shí)插入的數(shù)據(jù)，這也就意味著咱們按月動(dòng)態(tài)插入的需求已經(jīng)實(shí)現(xiàn)。

但看到這里估計(jì)絕大部分小伙伴略微有些懵，畢竟一通代碼下來看起來，尤其是不在IDEA工具里面，沒那么方便調(diào)試，因此最后畫一個(gè)執(zhí)行流程圖，提供給諸位來梳理整體思路！

• ①客戶端調(diào)用結(jié)算接口，傳入相關(guān)的賬單數(shù)據(jù)，即賬單詳情、賬單金額、收銀機(jī)器。
• ②API層會(huì)先獲取當(dāng)前系統(tǒng)時(shí)間作為賬單交易的時(shí)間，然后調(diào)用Service層的插入方法。
• ③Service層會(huì)先根據(jù)賬單交易時(shí)間，獲取到數(shù)據(jù)具體要插入的表名，接著調(diào)用Dao層接口。
• ④Dao層會(huì)根據(jù)上層傳遞過來的表名，生成具體的SQL語句，然后執(zhí)行插入數(shù)據(jù)的操作。

3. 按月分表后要解決的問題

上述已經(jīng)將最基礎(chǔ)的需求做了簡(jiǎn)單實(shí)現(xiàn)，那么接著再分析一下這些月份賬單表還會(huì)有哪些需求呢？

• ①除去最基本的新增操作外，還會(huì)有刪除、修改、查詢賬單的需求。
• ②一般賬單表中的流水?dāng)?shù)據(jù)，都會(huì)支持按時(shí)間進(jìn)行范圍查詢操作。

上述這兩個(gè)需求會(huì)是賬單表中還會(huì)存在的操作，對(duì)于第一點(diǎn)也比較容易實(shí)現(xiàn)，就是要求客戶端在修改、刪除、查詢數(shù)據(jù)時(shí)，都必須攜帶上對(duì)應(yīng)的時(shí)間，一般客戶端的修改、刪除操作都是基于先查詢出數(shù)據(jù)的基礎(chǔ)之上的，而一般查詢數(shù)據(jù)都會(huì)按照月份進(jìn)行查詢，或者根據(jù)流水號(hào)進(jìn)行查詢。

3.1、根據(jù)流水號(hào)查詢數(shù)據(jù)

還記得前面對(duì)于流水號(hào)的設(shè)計(jì)嘛？前面沒有太過說明，這里咱們單獨(dú)擰出來聊一聊：

setSerialNumber(monthBills.getMachineSerialNo()+System.currentTimeMillis());

這里使用了收銀機(jī)器序列號(hào)+時(shí)間戳作為賬單流水號(hào)，因?yàn)橥慌_(tái)機(jī)器在同一時(shí)間內(nèi)，絕對(duì)只能對(duì)一個(gè)賬單進(jìn)行結(jié)算，所以再結(jié)合遞增的時(shí)間戳，就能夠得到一個(gè)全局唯一的流水號(hào)。System.currentTimeMillis()獲取到的時(shí)間戳是13位數(shù)字，會(huì)放在機(jī)器序列號(hào)的后面，那接下來如果客戶端要根據(jù)流水號(hào)查詢賬單數(shù)據(jù)，又該如何定位具體的表呢？首先需要在工具類中撰寫一個(gè)新的方法：

//根據(jù)流水號(hào)得到表名
publicstaticvoidgetTableBySerialNumber(MonthBillsmonthBills){
//獲取流水號(hào)的后13位（時(shí)間戳）
StringtimeMillis=monthBills.getSerialNumber().
substring(monthBills.getSerialNumber().length()-13);
//將字符串類型的時(shí)間戳轉(zhuǎn)換為long類型
longmillis=Long.parseLong(timeMillis);
//調(diào)用getYearMonth()方法獲取時(shí)間戳中的年月
StringyearMonth=getYearMonth(newDate(millis));
//用表的前綴名拼接年月，得到最終要操作的表名
monthBills.setTargetTable(tablePrefix+yearMonth);
}

上面這個(gè)方法實(shí)際上很簡(jiǎn)單，就是先解析流水號(hào)中的時(shí)間戳，然后根據(jù)時(shí)間戳得到具體的年月，最后拼接表的前綴名，得到最終需要操作的表名，接著來寫一下Dao層代碼，如下：

"ResultMapMonthBills"type="com.zhuzi.dbMachineSubmeter.entity.MonthBills">

"month_bills_id"jdbcType="INTEGER"javaType="java.lang.Integer"/>
"serial_number"jdbcType="VARCHAR"javaType="java.lang.String"/>
"pay_money"jdbcType="DECIMAL"javaType="java.math.BigDecimal"/>
"machine_serial_no"jdbcType="VARCHAR"javaType="java.lang.String"/>
"bill_date"jdbcType="TIMESTAMP"javaType="java.util.Date"/>
"bill_comment"jdbcType="VARCHAR"javaType="java.lang.String"/>
"bills_info"jdbcType="LONGVARCHAR"javaType="java.lang.String"/>




"Base_Column_List">
month_bills_id,serial_number,bills_info,pay_money,machine_serial_no,
bill_date,bill_comment



"selectBySerialNumber"resultMap="ResultMapMonthBills"
parameterType="com.zhuzi.dbMachineSubmeter.entity.MonthBills">
select
"Base_Column_List"/>
from${targetTable}
whereserial_number=#{serial_number,jdbcType=VARCHAR}

接著來寫一下Service層的代碼，如下：

//在IMonthBillsService接口中多定義一個(gè)方法
MonthBillsselectBySerialNumber(MonthBillsmonthBills);

//在MonthBillsServiceImpl實(shí)現(xiàn)類中撰寫具體的實(shí)現(xiàn)
@Override
publicMonthBillsselectBySerialNumber(MonthBillsmonthBills){
//根據(jù)流水號(hào)獲取要查詢數(shù)據(jù)的具體表名
TableTimeUtils.getTableBySerialNumber(monthBills);
//調(diào)用Dao層根據(jù)流水號(hào)查詢數(shù)據(jù)的方法
returnbillsMapper.selectBySerialNumber(monthBills);
}

這里的實(shí)現(xiàn)尤為簡(jiǎn)單，僅調(diào)用了一下前面寫的工具類方法，獲取了一下要查詢數(shù)據(jù)的動(dòng)態(tài)表名，接著再來寫一下API層的接口，如下：

//根據(jù)流水號(hào)查詢數(shù)據(jù)的API
@RequestMapping("/selectBySerialNumber")
publicStringselectBySerialNumber(MonthBillsmonthBills){
//調(diào)用Service層根據(jù)流水號(hào)查詢數(shù)據(jù)的方法
MonthBillsresult=billsService.selectBySerialNumber(monthBills);
if(result!=null){
returnresult.toString();
}
return">>>>未查詢到流水號(hào)對(duì)應(yīng)的數(shù)據(jù)<<<<";
}

接著來做一下測(cè)試，調(diào)用地址如下：

• http://localhost:8080/bills/selectBySerialNumber?serialNumber=NF-002-X1668494222684

測(cè)試效果圖如下：

此時(shí)會(huì)發(fā)現(xiàn)，根據(jù)流水號(hào)查詢數(shù)據(jù)的效果就實(shí)現(xiàn)啦，這里主要是得設(shè)計(jì)好流水號(hào)的組成，其中一定要包含一個(gè)時(shí)間戳在內(nèi)，這樣就能夠通過解析流水號(hào)的方式，得到具體要查詢數(shù)據(jù)的表名，否則根據(jù)流水號(hào)查詢數(shù)據(jù)的動(dòng)作將異乎尋常的困難，因?yàn)樾枰讶勘頀呙枰淮尾拍艿玫綌?shù)據(jù)。

設(shè)計(jì)好根據(jù)流水號(hào)查詢數(shù)據(jù)后，對(duì)于修改和刪除的操作則不再重復(fù)撰寫啦！因?yàn)檫^程也大致相同，就是在修改、刪除時(shí)，同樣先根據(jù)流水號(hào)定位到具體要操作的表，接著再去對(duì)應(yīng)表中做相應(yīng)操作即可。

3.2、按時(shí)間范圍查詢數(shù)據(jù)

按時(shí)間范圍查詢賬單的流水?dāng)?shù)據(jù)，這是所有后臺(tái)管理系統(tǒng)中都支持的功能，在這個(gè)項(xiàng)目中也不例外，但想要實(shí)現(xiàn)這個(gè)功能，則必須要有先實(shí)現(xiàn)兩個(gè)功能：

• ①能夠根據(jù)用戶輸入的兩個(gè)時(shí)間范圍，得到兩個(gè)日期之間的所有表名。
• ②能夠根據(jù)第①步中得到的表名，生成對(duì)應(yīng)的查詢語句，能夠在單張表、多張表中通用。

上述這兩個(gè)需求實(shí)際上實(shí)現(xiàn)起來也并不難，接著來一起做一下！

3.2.1、得到兩個(gè)日期之間的所有表名

想要實(shí)現(xiàn)這個(gè)功能，那必然需要再在工具類中撰寫一個(gè)方法，如下：

//獲取按時(shí)間范圍查詢時(shí)，兩個(gè)日期之間，所有月份賬單表的表名
publicstaticListgetRangeQueryByTables(StringstartTime,StringendTime){
//聲明一個(gè)日期格式化類
SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM");
//聲明保存表名的集合
Listtables=newArrayList<>();
try{
//將兩個(gè)傳入的字符日期轉(zhuǎn)換成日期類型
DatestartDate=sdf.parse(startTime);
DateendDate=sdf.parse(endTime);

//用Calendar進(jìn)行日期比較判斷
Calendarcalendar=Calendar.getInstance();
while(startDate.getTime()<=?endDate.getTime()){??
????????????//?把生成的月份拼接表前綴名，加入到集合中??
????????????tables.add(tablePrefix?+?monthTL.get().format(startDate));??
????????????//?設(shè)置日期，并把比對(duì)器的日期增加一月??
????????????calendar.setTime(startDate);??
????????????calendar.add(Calendar.MONTH,?1);??
????????????//?獲取增加后的日期??
????????????startDate?=?calendar.getTime();??
????????}??
????}?catch?(ParseException?e)?{??
????????e.printStackTrace();??
????}??
????//?返回兩個(gè)日期之間的所有表名??
????returntables;
}

該方法需要傳入兩個(gè)參數(shù)，即兩個(gè)字符串類型的時(shí)間，接著會(huì)通過Calendar工具類，對(duì)兩個(gè)日期的大小做判斷，當(dāng)開始日期小于結(jié)束日期時(shí)，則會(huì)直接將表前綴名與年月拼接，得到一張?jiān)路葙~單表的表名，接著會(huì)對(duì)開始日期加一個(gè)月，然后繼續(xù)重復(fù)上一步......，直至得到兩日期之間的所有表名。

3.2.2、根據(jù)表名集合生成對(duì)應(yīng)的SQL語句

想要實(shí)現(xiàn)這個(gè)功能其實(shí)也非常簡(jiǎn)單，只需要做一堆判斷即可，再在工具類中寫一個(gè)方法：

//根據(jù)日期生成SQL語句的方法
publicstaticStringgetRangeQuerySQL(StringstartTime,StringendTime){
//先獲取兩個(gè)日期之間的所有表名
Listtables=getRangeQueryByTables(startTime,endTime);
//提前創(chuàng)建一個(gè)字符串對(duì)象保存SQL語句
StringBuffersql=newStringBuffer();
//如果查詢的兩個(gè)日期是同一張表，則直接生成BETWEENAND的SQL語句
if(tables.size()==1){
sql.append("select*from")
.append(tables.get(0))
.append("wherebill_dateBETWEEN'")
.append(startTime)
.append("'AND'")
.append(endTime)
.append("';");
//如果本次范圍查詢的兩個(gè)日期之間有多張表
}else{
//則用for循環(huán)遍歷所有表名
for(Stringtable:tables){
//對(duì)于第一張表則只需要查詢開始日期之后的數(shù)據(jù)
if(table.equals(tables.get(0))){
sql.append("select*from")
.append(table)
.append("wherebill_date>'")
.append(startTime)
.append("'unionall");
}
//對(duì)于最后一張表只需要查詢結(jié)束日期之前的數(shù)據(jù)
elseif(table.equals(tables.get(tables.size()-1))){
sql.append("select*from")
.append(table)
.append("wherebill_date)
.append(endTime)
.append("';");
//對(duì)于其他表則獲取所有數(shù)據(jù)
}else{
sql.append("select*from")
.append(table)
.append("'unionall");
}
}
}
//返回最終生成的SQL語句
returnsql.toString();
}

這個(gè)方法看起來似乎有些長(zhǎng)，但其實(shí)功能也非常簡(jiǎn)單，如下：

• ①如果兩個(gè)日期在一個(gè)月內(nèi)，則生成BETWEEN AND的查詢語句。

• 如果兩個(gè)日期間隔了多月，則用

  for

  循環(huán)遍歷前面得到的表名：

  • 如果是第一張表，則只需要查詢開始日期之后的數(shù)據(jù)，再用union all拼接后面的語句。
  • 如果是最后一張表，則只需要查詢結(jié)束日期之前的數(shù)據(jù)，以;分號(hào)結(jié)尾即可。
  • 如果是中間的表，則查詢對(duì)應(yīng)的所有數(shù)據(jù)，接著繼續(xù)用union all拼接其他語句。

接著做個(gè)簡(jiǎn)單的小測(cè)試，效果如下：

很明顯，通過這兩個(gè)方法，可以實(shí)現(xiàn)最初咱們提出的兩個(gè)需求，實(shí)現(xiàn)這兩個(gè)基礎(chǔ)功能后，接著套入到前面的項(xiàng)目中~

3.2.3、實(shí)現(xiàn)按時(shí)間做范圍查詢的API接口

依舊按照之前的步驟，先定義Dao層的接口和.xml文件，如下：

//定義一個(gè)返回多條數(shù)據(jù)的接口
ListrangeQueryByDate(@Param("sql")Stringsql);

"rangeQueryByDate"resultMap="ResultMapMonthBills"
parameterType="java.lang.String">
${sql}

主要觀察xml文件中的代碼，因?yàn)檫@里需要實(shí)現(xiàn)自定義SQL的執(zhí)行，所以將SQL語句的生成工作放在了外部完成，在xml中僅需將對(duì)應(yīng)的SQL語句發(fā)給MySQL執(zhí)行，并接收返回結(jié)果即可，接著來寫一下Service層的接口和實(shí)現(xiàn)：

//在IMonthBillsService接口中多定義一個(gè)方法
ListrangeQueryByDate(StringstartTime,StringendTime);

//在MonthBillsServiceImpl實(shí)現(xiàn)類中撰寫具體的實(shí)現(xiàn)
@Override
publicListrangeQueryByDate(StringstartTime,StringendTime){
//獲取范圍查詢時(shí)的SQL語句
Stringsql=TableTimeUtils.getRangeQuerySQL(startTime,endTime);
returnbillsMapper.rangeQueryByDate(sql);
}

其實(shí)核心工作已經(jīng)在之前的工具類中完成了，這里僅需調(diào)用工具類中，生成兩個(gè)日期之間的查詢語句即可，接著再寫一下API層的對(duì)外接口，就大功告成啦！如下：

//按照范圍查詢兩個(gè)日期之間的所有賬單數(shù)據(jù)
@RequestMapping("/rangeQueryByTime")
publicStringrangeQueryByTime(@RequestParam("start")Stringstart,
@RequestParam("end")Stringend){
//調(diào)用Service層根據(jù)流水號(hào)查詢數(shù)據(jù)的方法
Listbills=billsService.rangeQueryByDate(start,end);
if(bills!=null){
returnbills.toString();
}
return">>>>指定的日期中沒有賬單數(shù)據(jù)<<<<";
}

在這里面僅僅只是調(diào)用了Service層的方法而已，接下來測(cè)試一下，測(cè)試地址為：

• localhost:8080/bills/rangeQueryByTime?start=2022-11-01&end=2022-11-30

最終效果如下：

因?yàn)槲冶碇芯蛢蓷l數(shù)據(jù)，所以就做了一個(gè)單月表的測(cè)試，這里單月賬單表的數(shù)據(jù)查詢無誤，大家也可以再建立一張其他月份的賬單表，效果也是照樣沒有問題的~

3.2.4、按時(shí)間范圍查詢數(shù)據(jù)小結(jié)

其實(shí)這里的做法僅僅只是為了給大家演示效果，之前的實(shí)際業(yè)務(wù)中遠(yuǎn)比這更加復(fù)雜，因?yàn)槊繌堅(jiān)路葙~單表會(huì)有上千萬條數(shù)據(jù)，不可能一次性查詢幾張、幾十張的月份賬單表，這樣對(duì)于網(wǎng)絡(luò)、資源的開銷太大。

實(shí)際業(yè)務(wù)中，一方面會(huì)限制查詢的日期范圍，最多只允許客戶查詢近六月的賬單流水。另一方面還會(huì)結(jié)合數(shù)據(jù)分頁(yè)，也就是每頁(yè)僅顯示20條數(shù)據(jù)，隨著用戶的翻頁(yè)動(dòng)作觸發(fā)后，才會(huì)對(duì)每張不同的月份賬單表做查詢。

對(duì)于這種會(huì)批量查詢所有賬單表的業(yè)務(wù)，基本上是查詢一些流水交易金額的統(tǒng)計(jì)數(shù)據(jù)，而且也僅是提供給后臺(tái)系統(tǒng)操作，用于定時(shí)跑批去生成統(tǒng)計(jì)數(shù)據(jù)，如近一周、一月、一季、半年、一年的交易金額、賬單總量.....等這類需求。

這里給大家實(shí)現(xiàn)這個(gè)需求的目的在于：讓大家理解按月做了水平分表后，該如何查詢多張表的數(shù)據(jù)。

4. 庫(kù)內(nèi)分表篇總結(jié)

看到這里，對(duì)于庫(kù)內(nèi)分表篇的內(nèi)容也接近了尾聲，有小伙伴也許會(huì)疑惑：那如果我每月的數(shù)據(jù)量更大怎么辦呢？比如前面的例子中，如果再投入了多批機(jī)器怎么辦？每月的數(shù)據(jù)量達(dá)到3000W、6000W.....甚至上億怎么辦？

如若你存在這塊的顧慮，其實(shí)大可不必?fù)?dān)心，因?yàn)樵蹅兗热豢梢园丛路直?，那能否按半月為周期分表呢？能否按星期分表呢？能否以三天、一天為一個(gè)維度分表呢？答案顯然是可以的，所以數(shù)據(jù)量無論有多大，都可能按不同的周期來劃分表。

不過一般對(duì)于庫(kù)內(nèi)分表的場(chǎng)景會(huì)很少用到，畢竟庫(kù)中只有某些表的數(shù)據(jù)量較大時(shí)，才會(huì)選用這種方案，如果整庫(kù)的數(shù)據(jù)量較大、訪問壓力較高，則會(huì)直接采用分庫(kù)方案（不過本篇的內(nèi)容，對(duì)于一些身處東南亞的朋友，應(yīng)該用的還是比較頻繁的~）。

其實(shí)庫(kù)內(nèi)分表除開本文講解的方式外，大家通過整合Sharding-JDBC框架來實(shí)現(xiàn)會(huì)更加輕松，但那樣會(huì)導(dǎo)致依賴變多，所以如果你項(xiàng)目中不需要用到太多的分表，則可采用本文這種方式實(shí)現(xiàn)。