分庫分表實戰內容基本上很少有人去分享，在網上能夠搜出來的也大多屬于一些方法論，但大部分技術開發真正缺少的恰恰是這些實操經驗，所以后續的內容多以實踐為主，攜手諸位真正徹底悟透分庫分表相關的技術。

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

1. 源自于軟硬結合的特殊業務

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

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

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

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

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

1.1、項目的難點

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

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

這里咱們做個數學題：現在有1000臺機器，每臺機器單日就算產生500條數據：1000 * 500 = 500000，這也就意味著單日的賬單表中會新增50W條流水數據，單月整個賬單表的數據增長量為：50W * 30 = 1500W！

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

50W只是最低的賬單流水數據量，后續正式運營后可能數據量更大，此時架構的設計就成了難題！

1.2、方案的探討

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

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

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

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

因此經過一番探討后，最后決定選擇了表分區技術的進階版實現，即單庫內做水平分表，按月份對數據做分表，也就是將賬單表分為month_bills_202210、month_bills_202211、month_bills_202212.......以月份結尾的多張表，每個月的賬單流水數據最終都會插入到各自的月份表中。

最終架構定型為：業務系統使用單體架構 + 數據庫使用單庫 + 流水表按月份做水平分表。

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

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

• ①能夠自動按月創建一張月份賬單表，從而將每月的流水數據寫入進去。
• ②寫入數據時，能夠根據當前的日期，選擇對應的月份賬單表并插入數據。

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

2.1、利用存儲過程實現按月動態創建表

創建表的SQL語句大家都不陌生，按月份創建表之前，自然也需要一份原生創建表的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;

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

其中注意的幾個小細節：

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

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

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

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

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

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

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

--定義表的名稱=表前綴+月份，即month_bills_2022112這個格式
SET@tableName=CONCAT(@table_prefix,@nextMonth);
--定義創建表的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關鍵字來創建一個預備執行的SQL體
PREPAREcreate_stmtfrom@createTableSQL;
--使用 EXECUTE 關鍵字來執行上面的預備SQL體：create_stmt 
EXECUTEcreate_stmt;
--釋放掉前面創建的SQL體（減少內存占用）
DEALLOCATEPREPAREcreate_stmt;

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

--查詢一下對應的表是否已存在
SELECT@tableCount'tableCount';

END//
delimiter;

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

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

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

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

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

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

經過上述拆解之后大家應該能看的很清楚，最終每次調用該存儲過程時，都會基于當前數據庫的時間，然后向后增加一個月，同時將格式轉化為YYYYMM格式，接下來調用該存儲過程，如下：

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

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

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

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

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

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

--查看創建的定時器
showevents;
select*frommysql.event;
select*frominformation_schema.EVENTS;
--刪除一個定時器
dropevent定時器名稱;
--關閉一個定時器任務
alterevent定時器名稱onCOMPLETIONPRESERVEDISABLE;
--開啟一個定時器任務
alterevent定時器名稱onCOMPLETIONPRESERVEENABLE;

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

2.2、寫入數據時能夠根據月份插入對應表

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

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

//要操作的目標表
privateStringtargetTable;

//省略構造方法和Get/Set方法.....
}

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

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

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

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

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

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

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

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

@Mapper
@Repository
publicinterfaceMonthBillsMapper{
intdeleteByPrimaryKey(IntegermonthBillsId);

intinsertSelective(MonthBillsrecord);

MonthBillsselectByPrimaryKey(IntegermonthBillsId);

intupdateByPrimaryKeySelective(MonthBillsrecord);
}

上述是月份賬單表對應的Dao/Mapper接口，因為我這里是通過MyBatis的逆向工程文件自動生成的，所以名字就是上面那樣，我這邊未成更改，接著來看看對應的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屬性嘛？在這里會根據該字段的值動態的去操作不同月份的表，接著來寫一下Service層的接口和實現類，如下：

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

//Service實現類
@Service
publicclassMonthBillsServiceImplimplementsIMonthBillsService{
@Autowired
privateMonthBillsMapperbillsMapper;

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

在service層目前僅實現了一個插入數據的方法，其中的邏輯也非常簡單，僅僅在調用Dao層的插入方法之前，獲取了一下當前這條數據要插入的表名，最后來看看Controller/API層，如下：

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

@Autowired
privateIMonthBillsServicebillsService;

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

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

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

最終測試效果圖如下：

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

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

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

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

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

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

上述已經將最基礎的需求做了簡單實現，那么接著再分析一下這些月份賬單表還會有哪些需求呢？

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

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

3.1、根據流水號查詢數據

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

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

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

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

上面這個方法實際上很簡單，就是先解析流水號中的時間戳，然后根據時間戳得到具體的年月，最后拼接表的前綴名，得到最終需要操作的表名，接著來寫一下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實現類中撰寫具體的實現
@Override
publicMonthBillsselectBySerialNumber(MonthBillsmonthBills){
//根據流水號獲取要查詢數據的具體表名
TableTimeUtils.getTableBySerialNumber(monthBills);
//調用Dao層根據流水號查詢數據的方法
returnbillsMapper.selectBySerialNumber(monthBills);
}

這里的實現尤為簡單，僅調用了一下前面寫的工具類方法，獲取了一下要查詢數據的動態表名，接著再來寫一下API層的接口，如下：

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

接著來做一下測試，調用地址如下：

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

測試效果圖如下：

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

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

3.2、按時間范圍查詢數據

按時間范圍查詢賬單的流水數據，這是所有后臺管理系統中都支持的功能，在這個項目中也不例外，但想要實現這個功能，則必須要有先實現兩個功能：

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

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

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

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

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

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

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

3.2.2、根據表名集合生成對應的SQL語句

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

//根據日期生成SQL語句的方法
publicstaticStringgetRangeQuerySQL(StringstartTime,StringendTime){
//先獲取兩個日期之間的所有表名
Listtables=getRangeQueryByTables(startTime,endTime);
//提前創建一個字符串對象保存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循環遍歷所有表名
for(Stringtable:tables){
//對于第一張表則只需要查詢開始日期之后的數據
if(table.equals(tables.get(0))){
sql.append("select*from")
.append(table)
.append("wherebill_date>'")
.append(startTime)
.append("'unionall");
}
//對于最后一張表只需要查詢結束日期之前的數據
elseif(table.equals(tables.get(tables.size()-1))){
sql.append("select*from")
.append(table)
.append("wherebill_date)
.append(endTime)
.append("';");
//對于其他表則獲取所有數據
}else{
sql.append("select*from")
.append(table)
.append("'unionall");
}
}
}
//返回最終生成的SQL語句
returnsql.toString();
}

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

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

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

  for

  循環遍歷前面得到的表名：

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

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

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

3.2.3、實現按時間做范圍查詢的API接口

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

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

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

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

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

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

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

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

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

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

最終效果如下：

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

3.2.4、按時間范圍查詢數據小結

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

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

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

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

4. 庫內分表篇總結

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

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

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

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