前言

之前不少人問(wèn)我“能否分享一些分庫(kù)分表相關(guān)的實(shí)踐”，其實(shí)不是我不分享，而是真的經(jīng)驗(yàn)不多;和大部分人一樣都是停留在理論階段。

不過(guò)這次多少有些可以說(shuō)道了。

先談?wù)劚尘?，我們生產(chǎn)數(shù)據(jù)庫(kù)隨著業(yè)務(wù)發(fā)展量也逐漸起來(lái);好幾張單表已經(jīng)突破億級(jí)數(shù)據(jù)，并且保持每天 200+W 的數(shù)據(jù)量增加。

而我們有些業(yè)務(wù)需要進(jìn)行關(guān)聯(lián)查詢(xún)、或者是報(bào)表統(tǒng)計(jì);在這樣的背景下大表的問(wèn)題更加突出(比如一個(gè)查詢(xún)功能需要跑好幾分鐘)。

可能很多人會(huì)說(shuō)：為啥單表都過(guò)億了才想方案解決?其實(shí)不是不想，而是由于歷史原因加上錯(cuò)誤預(yù)估了數(shù)據(jù)增長(zhǎng)才導(dǎo)致這個(gè)局面。總之原因比較復(fù)雜，也不是本次討論的重點(diǎn)。

臨時(shí)方案

由于需求緊、人手缺的情況下，整個(gè)處理的過(guò)程分為幾個(gè)階段。

一階段應(yīng)該是去年底，當(dāng)時(shí)運(yùn)維反應(yīng) MySQL 所在的主機(jī)內(nèi)存占用很高，整體負(fù)載也居高不下，導(dǎo)致整個(gè) MySQL 的吞吐量明顯降低(寫(xiě)入、查詢(xún)數(shù)據(jù)都明顯減慢)。

為此我們找出了數(shù)據(jù)量大的幾張表，發(fā)現(xiàn)大部分?jǐn)?shù)據(jù)量在7/8000W 左右，少數(shù)的已經(jīng)突破一億。

通過(guò)業(yè)務(wù)層面進(jìn)行分析發(fā)現(xiàn)，這些數(shù)據(jù)多數(shù)都是用戶(hù)產(chǎn)生的一些日志型數(shù)據(jù)，而且這些數(shù)據(jù)在業(yè)務(wù)上并不是強(qiáng)相關(guān)的，甚至兩三個(gè)月前的數(shù)據(jù)其實(shí)已經(jīng)不需要實(shí)時(shí)查詢(xún)了。

因?yàn)榻咏甑祝M可能的不想去動(dòng)應(yīng)用，考慮是否可以在運(yùn)維層面緩解壓力;主要的目的就是把單表的數(shù)據(jù)量降低。

原本是想把兩個(gè)月之前的數(shù)據(jù)直接遷移出來(lái)放到備份表中，但在準(zhǔn)備實(shí)施的過(guò)程中發(fā)現(xiàn)一個(gè)大坑。

表中沒(méi)有一個(gè)可以排序的索引，導(dǎo)致我們無(wú)法快速的篩選出一部分?jǐn)?shù)據(jù)!這真是一個(gè)深坑，為后面的一些優(yōu)化埋了個(gè)雷;即便是加索引也需要花幾個(gè)小時(shí)(具體多久沒(méi)敢在生產(chǎn)測(cè)試)。

如果我們強(qiáng)行按照時(shí)間進(jìn)行篩選，可能查詢(xún)出 4000W 的數(shù)據(jù)就得花上好幾個(gè)小時(shí);這顯然是行不通的。

于是我們便想到了一個(gè)大膽的想法：這部分?jǐn)?shù)據(jù)是否可以直接不要了?

這可能是最有效及最快的方式了，和產(chǎn)品溝通后得知這部分?jǐn)?shù)據(jù)真的只是日志型的數(shù)據(jù)，即便是報(bào)表出不來(lái)今后補(bǔ)上也是可以的。

于是我們就簡(jiǎn)單粗暴的做了以下事情：

• 修改原有表的表名，比如加上( _190416bak)。
• 再新建一張和原有表名稱(chēng)相同的表。

這樣新的數(shù)據(jù)就寫(xiě)到了新表，同時(shí)業(yè)務(wù)上也是使用的這個(gè)數(shù)據(jù)量較小的新表。

雖說(shuō)過(guò)程不太優(yōu)雅，但至少是解決了問(wèn)題同時(shí)也給我們做技術(shù)改造預(yù)留了時(shí)間。

分表方案

之前的方案雖說(shuō)可以緩解壓力，但不能根本解決問(wèn)題。

有些業(yè)務(wù)必須得查詢(xún)之前的數(shù)據(jù)，導(dǎo)致之前那招行不通了，所以正好我們就借助這個(gè)機(jī)會(huì)把表分了。

我相信大部分人雖說(shuō)沒(méi)有做過(guò)實(shí)際做過(guò)分表，但也見(jiàn)過(guò)豬跑;網(wǎng)上一搜各種方案層出不窮。

我認(rèn)為最重要的一點(diǎn)是要結(jié)合實(shí)際業(yè)務(wù)找出需要 sharding 的字段，同時(shí)還有上線階段的數(shù)據(jù)遷移也非常重要。

時(shí)間

可能大家都會(huì)說(shuō)用 hash 的方式分配得最均勻，但我認(rèn)為這還是需要使用歷史數(shù)據(jù)的場(chǎng)景才用哈希分表。

而對(duì)于不需要?dú)v史數(shù)據(jù)的場(chǎng)景，比如業(yè)務(wù)上只查詢(xún)近三個(gè)月的數(shù)據(jù)。

這類(lèi)需求完成可以采取時(shí)間分表，按照月份進(jìn)行劃分，這樣改動(dòng)簡(jiǎn)單，同時(shí)對(duì)歷史數(shù)據(jù)也比較好遷移。

于是我們首先將這類(lèi)需求的表篩選出來(lái)，按照月份進(jìn)行拆分，只是在查詢(xún)的時(shí)候拼接好表名即可;也比較好理解。

哈希

剛才也提到了：需要根據(jù)業(yè)務(wù)需求進(jìn)行分表策略。

而一旦所有的數(shù)據(jù)都有可能查詢(xún)時(shí)，按照時(shí)間分表也就行不通了。(也能做，只是如果不是按照時(shí)間進(jìn)行查詢(xún)時(shí)需要遍歷所有的表)

因此我們計(jì)劃采用 hash 的方式分表，這算是業(yè)界比較主流的方式就不再贅述。

采用哈希時(shí)需要將 sharding 字段選好，由于我們的業(yè)務(wù)比較單純;是一個(gè)物聯(lián)網(wǎng)應(yīng)用，所有的數(shù)據(jù)都包含有物聯(lián)網(wǎng)設(shè)備的唯一標(biāo)識(shí)(IMEI)，并且這個(gè)字段天然的就保持了唯一性;大多數(shù)的業(yè)務(wù)也都是根據(jù)這個(gè)字段來(lái)的，所以它非常適合來(lái)做這個(gè) sharding 字段。

在做分表之前也調(diào)研過(guò) MyCAT 及 sharding-jdbc(現(xiàn)已升級(jí)為 shardingsphere)，最終考慮到對(duì)開(kāi)發(fā)的友好性及不增加運(yùn)維復(fù)雜度還是決定在 jdbc 層 sharding 的方式。

但由于歷史原因我們并不太好集成 sharding-jdbc，但基于 sharding 的特點(diǎn)自己實(shí)現(xiàn)了一個(gè)分表策略。

這個(gè)簡(jiǎn)單也好理解：

int index = hash(sharding字段) % 分表數(shù)量 ; 
select xx from 'busy_'+index where sharding字段 = xxx; 

其實(shí)就是算出了表名，然后路由過(guò)去查詢(xún)即可。

只是我們實(shí)現(xiàn)的非常簡(jiǎn)單：修改了所有的底層查詢(xún)方法，每個(gè)方法都里都做了這樣的一個(gè)判斷。

并沒(méi)有像 sharding-jdbc 一樣，代理了數(shù)據(jù)庫(kù)的查詢(xún)方法;其中還要做 SQL解析-->SQL路由-->執(zhí)行SQL-->合并結(jié)果 這一系列的流程。

如果自己再做一遍無(wú)異于重新造了一個(gè)輪子，并且并不專(zhuān)業(yè)，只是在現(xiàn)有的技術(shù)條件下選擇了一個(gè)快速實(shí)現(xiàn)達(dá)成效果的方法。

不過(guò)這個(gè)過(guò)程中我們節(jié)省了將 sharding 字段哈希的過(guò)程，因?yàn)槊恳粋€(gè) IMEI 號(hào)其實(shí)都是一個(gè)唯一的整型，直接用它做 mod 運(yùn)算即可。

還有一個(gè)是需要一個(gè)統(tǒng)一的組件生成規(guī)則，分表后不能再依賴(lài)于單表的字段自增了;方法還是挺多的：

• 比如時(shí)間戳+隨機(jī)數(shù)可滿(mǎn)足大部分業(yè)務(wù)。
• UUID，生成簡(jiǎn)單，但沒(méi)法做排序。
• 雪花算法統(tǒng)一生成主鍵ID。

大家可以根據(jù)自己的實(shí)際情況做選擇。

業(yè)務(wù)調(diào)整

因?yàn)槲覀儾](méi)有使用第三方的 sharding-jdbc 組件，所有沒(méi)有辦法做到對(duì)代碼的低侵入性;每個(gè)涉及到分表的業(yè)務(wù)代碼都需要做底層方法的改造(也就是路由到正確的表)。

考慮到后續(xù)業(yè)務(wù)的發(fā)展，我們決定將拆分的表分為 64 張;加上后續(xù)引入大數(shù)據(jù)平臺(tái)足以應(yīng)對(duì)幾年的數(shù)據(jù)增長(zhǎng)。

這里還有個(gè)小細(xì)節(jié)需要注意：分表的數(shù)量需要為 2∧N 次方，因?yàn)樵谌∧５倪@種分表方式下，即便是今后再需要分表影響的數(shù)據(jù)也會(huì)盡量的小。

再修改時(shí)只能將表名稱(chēng)進(jìn)行全局搜索，然后加以修改，同時(shí)根據(jù)修改的方法倒推到表現(xiàn)的業(yè)務(wù)并記錄下來(lái)，方便后續(xù)回歸測(cè)試。

當(dāng)然無(wú)法避免查詢(xún)時(shí)利用非 sharding 字段導(dǎo)致的全表掃描，這是所有分片后都會(huì)遇到的問(wèn)題。

因此我們?cè)谛薷姆直矸椒ǖ牡讓硬樵?xún)時(shí)同時(shí)也會(huì)查看是否有走分片字段，如果不是，那是否可以調(diào)整業(yè)務(wù)。

比如對(duì)于一個(gè)上億的數(shù)據(jù)是否還有必要存在按照分頁(yè)查詢(xún)、日期查詢(xún)?這樣的業(yè)務(wù)是否真的具有意義?

我們盡可能的引導(dǎo)產(chǎn)品按照這樣的方式來(lái)設(shè)計(jì)產(chǎn)品或者做出調(diào)整。

但對(duì)于報(bào)表這類(lèi)的需求確實(shí)也沒(méi)辦法，比如統(tǒng)計(jì)表中某種類(lèi)型的數(shù)據(jù);這種我們也可以利用多線程的方式去并行查詢(xún)?nèi)缓髤R總統(tǒng)計(jì)來(lái)提高查詢(xún)效率。

有時(shí)也有一些另類(lèi)場(chǎng)景：

比如一個(gè)千萬(wàn)表中有某一特殊類(lèi)型的數(shù)據(jù)只占了很小一部分，比如說(shuō)幾千上萬(wàn)條。

這時(shí)頁(yè)面上需要對(duì)它進(jìn)行分頁(yè)查詢(xún)是比較正常的(比如某種投訴消息，客戶(hù)需要一條一條的單獨(dú)處理)，但如果我們按照 IMEI 號(hào)或者是主鍵進(jìn)行分片后再分頁(yè)查詢(xún)那就比較難受了。

所以這類(lèi)型的數(shù)據(jù)建議單獨(dú)新建一張表來(lái)維護(hù)，不要和其他數(shù)據(jù)混合在一起，這樣不管是做分頁(yè)還是 like 都比較簡(jiǎn)單和獨(dú)立。

驗(yàn)證

代碼改完，開(kāi)發(fā)也單測(cè)完成后怎么來(lái)驗(yàn)證分表的業(yè)務(wù)是否正常也比較麻煩。

一個(gè)是測(cè)試麻煩，再一個(gè)是萬(wàn)一哪里改漏了還是查詢(xún)的原表，但這樣在測(cè)試環(huán)境并不會(huì)有異常，一旦上線產(chǎn)生了生產(chǎn)數(shù)據(jù)到新的 64 張表后想要再修復(fù)就比較麻煩了。

所以我們?nèi)×藗€(gè)巧，直接將原表的表名修改，比如加一個(gè)后綴;這樣在測(cè)試過(guò)程中觀察前后臺(tái)有無(wú)報(bào)錯(cuò)就比較容易提前發(fā)現(xiàn)這個(gè)問(wèn)題。

上線流程

測(cè)試驗(yàn)收通過(guò)后只是分表這個(gè)需求的80%，剩下如何上線也是比較頭疼。

一旦應(yīng)用上線后所有的查詢(xún)、寫(xiě)入、刪除都會(huì)先走路由然后到達(dá)新表;而老數(shù)據(jù)在原表里是不會(huì)發(fā)生改變的。

數(shù)據(jù)遷移

所以我們上線前的第一步自然是需要將原有的數(shù)據(jù)進(jìn)行遷移，遷移的目的是要分片到新的 64 張表中，這樣才會(huì)對(duì)原有的業(yè)務(wù)無(wú)影響。

因此我們需要額外準(zhǔn)備一個(gè)程序，它需要將老表里的數(shù)據(jù)按照分片規(guī)則復(fù)制到新表中;

在我們這個(gè)場(chǎng)景下，生產(chǎn)數(shù)據(jù)有些已經(jīng)上億了，這個(gè)遷移過(guò)程我們?cè)跍y(cè)試環(huán)境模擬發(fā)現(xiàn)耗時(shí)是非常久的。而且我們老表中對(duì)于 create_time 這樣用于篩選數(shù)據(jù)的字段沒(méi)有索引(以前的技術(shù)債)，所以查詢(xún)起來(lái)就更加慢了。

沒(méi)辦法，我們只能和產(chǎn)品協(xié)商告知用戶(hù)對(duì)于之前產(chǎn)生的數(shù)據(jù)短期可能會(huì)查詢(xún)不到，這個(gè)時(shí)間最壞可能會(huì)持續(xù)幾天(我們只能在凌晨遷移，白天會(huì)影響到數(shù)據(jù)庫(kù)負(fù)載)。

總結(jié)

這便是我們這次的分表實(shí)踐，雖說(shuō)不少過(guò)程都不優(yōu)雅，但受限于條件也只能折中處理。

但我們后續(xù)的計(jì)劃是，修改我們底層的數(shù)據(jù)連接(目前是自己封裝的一個(gè) jar 包，導(dǎo)致集成 sharding-jdbc 比較麻煩)最終逐漸遷移到 sharding-jdbc .

最后得出了幾個(gè)結(jié)論：

• 一個(gè)好的產(chǎn)品規(guī)劃非常有必要，可以在合理的時(shí)間對(duì)數(shù)據(jù)處理(不管是分表還是切入歸檔)。
• 每張表都需要一個(gè)可以用于排序查詢(xún)的字段(自增ID、創(chuàng)建時(shí)間)，整個(gè)過(guò)程由于沒(méi)有這個(gè)字段導(dǎo)致耽擱了很長(zhǎng)時(shí)間。
• 分表字段需要謹(jǐn)慎，要全盤(pán)的考慮業(yè)務(wù)情況，盡量避免出現(xiàn)查詢(xún)掃表的情況。

最后歡迎留言討論。