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

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

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

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

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

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

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

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

1.1、項目的難點

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

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

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

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

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

1.2、方案的探討

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

這里直接用分庫可以嗎？答案是也不行，Why？因為整個項目中只有賬單表才有這么大的數(shù)據(jù)量，其他的用戶表、系統(tǒng)表、功能菜單表、后臺表......，基本上不會有太大的數(shù)據(jù)量，所以直接做分庫也沒必要，屬實有些浪費資源。

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

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

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

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

2. 按月分表方案的落地實踐

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

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

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

2.1、利用存儲過程實現(xiàn)按月動態(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'流水號',
`bills_info`textNOTNULLCOMMENT'賬單詳情',
`pay_money`decimal(10,3)NOTNULLCOMMENT'支付金額',
`machine_serial_no`varchar(20)NOTNULLCOMMENT'收銀機器',
`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;

上述的語句會創(chuàng)建一張月份賬單表，這張表主要包含七個字段，如下：

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

• ①日期字段使用的是timestamp類型，而并非datetime，因為前者更省空間。
• ②賬單詳情字段用的是text類型，因為這個字段可能會出現(xiàn)很多的信息。
• ③定義了一個和表沒有關(guān)系的自增字段作為主鍵，用于維護聚簇索引樹的結(jié)構(gòu)。

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

到這里就有了最基本的建表語句，主要是用來創(chuàng)建第一張月份賬單表，如果想要實現(xiàn)動態(tài)按照每月建表的話，還需要用到存儲過程來實現(xiàn)，接著來寫一個存儲過程。

最終撰寫出的存儲過程如下：

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

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

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

--定義表的名稱=表前綴+月份，即month_bills_2022112這個格式
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'流水號',
`bills_info`textNOTNULLCOMMENT'賬單詳情',
`pay_money`decimal(10,3)NOTNULLCOMMENT'支付金額',
`machine_serial_no`varchar(20)NOTNULLCOMMENT'收銀機器',
`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)建一個預(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;

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

END//
delimiter;

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

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

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

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

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

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

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

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

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

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

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

但定時器在使用之前，需要先查看定時器是否開啟，如下：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的配置即可。

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

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

經(jīng)過上述幾步后，就能夠讓MySQL自己按月創(chuàng)建表了，但為啥我會將定時器的時間設(shè)置為2022-11-28 0000這個時間后開始呢？因為202211這張表我已經(jīng)手動建立了，不將建立表的工作放在月初一號執(zhí)行，這是因為前面的存儲過程是創(chuàng)建下月表，而不是創(chuàng)建當(dāng)月表，同時月底提前創(chuàng)建下月表，還能提高容錯率，在MySQL定時器故障的情況下，能預(yù)留人工介入的時間。

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

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

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

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

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

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

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

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

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

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

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

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

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

@Mapper
@Repository
publicinterfaceMonthBillsMapper{
intdeleteByPrimaryKey(IntegermonthBillsId);

intinsertSelective(MonthBillsrecord);

MonthBillsselectByPrimaryKey(IntegermonthBillsId);

intupdateByPrimaryKeySelective(MonthBillsrecord);
}

上述是月份賬單表對應(yīng)的Dao/Mapper接口，因為我這里是通過MyBatis的逆向工程文件自動生成的，所以名字就是上面那樣，我這邊未成更改，接著來看看對應(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>

上述這么大一長串，其實也不是俺手敲的，依舊是MyBatis逆向工程生成的代碼，但我對其中的一處稍微做了改動，如下：

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

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

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

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

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

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

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

@Autowired
privateIMonthBillsServicebillsService;

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

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

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

最終測試效果圖如下：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

上面這個方法實際上很簡單，就是先解析流水號中的時間戳，然后根據(jù)時間戳得到具體的年月，最后拼接表的前綴名，得到最終需要操作的表名，接著來寫一下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接口中多定義一個方法
MonthBillsselectBySerialNumber(MonthBillsmonthBills);

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

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

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

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

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

測試效果圖如下：

此時會發(fā)現(xiàn)，根據(jù)流水號查詢數(shù)據(jù)的效果就實現(xiàn)啦，這里主要是得設(shè)計好流水號的組成，其中一定要包含一個時間戳在內(nèi)，這樣就能夠通過解析流水號的方式，得到具體要查詢數(shù)據(jù)的表名，否則根據(jù)流水號查詢數(shù)據(jù)的動作將異乎尋常的困難，因為需要把全部表掃描一次才能得到數(shù)據(jù)。

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

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

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

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

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

3.2.1、得到兩個日期之間的所有表名

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

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

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

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

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

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

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

這個方法看起來似乎有些長，但其實功能也非常簡單，如下：

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

• 如果兩個日期間隔了多月，則用

  for

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

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

接著做個簡單的小測試，效果如下：

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

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

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

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

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

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

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

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

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

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

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

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

最終效果如下：

因為我表中就兩條數(shù)據(jù)，所以就做了一個單月表的測試，這里單月賬單表的數(shù)據(jù)查詢無誤，大家也可以再建立一張其他月份的賬單表，效果也是照樣沒有問題的~

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

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

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

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

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

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

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

如若你存在這塊的顧慮，其實大可不必?fù)?dān)心，因為咱們既然可以按月分表，那能否按半月為周期分表呢？能否按星期分表呢？能否以三天、一天為一個維度分表呢？答案顯然是可以的，所以數(shù)據(jù)量無論有多大，都可能按不同的周期來劃分表。

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

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