前言

之前不少人問我“能否分享一些分庫分表相關的實踐”，其實不是我不分享，而是真的經驗不多;和大部分人一樣都是停留在理論階段。

不過這次多少有些可以說道了。

先談談背景，我們生產數據庫隨著業務發展量也逐漸起來;好幾張單表已經突破億級數據，并且保持每天 200+W 的數據量增加。

而我們有些業務需要進行關聯查詢、或者是報表統計;在這樣的背景下大表的問題更加突出(比如一個查詢功能需要跑好幾分鐘)。

可能很多人會說：為啥單表都過億了才想方案解決?其實不是不想，而是由于歷史原因加上錯誤預估了數據增長才導致這個局面。總之原因比較復雜，也不是本次討論的重點。

臨時方案

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

一階段應該是去年底，當時運維反應 MySQL 所在的主機內存占用很高，整體負載也居高不下，導致整個 MySQL 的吞吐量明顯降低(寫入、查詢數據都明顯減慢)。

為此我們找出了數據量大的幾張表，發現大部分數據量在7/8000W 左右，少數的已經突破一億。

通過業務層面進行分析發現，這些數據多數都是用戶產生的一些日志型數據，而且這些數據在業務上并不是強相關的，甚至兩三個月前的數據其實已經不需要實時查詢了。

因為接近年底，盡可能的不想去動應用，考慮是否可以在運維層面緩解壓力;主要的目的就是把單表的數據量降低。

原本是想把兩個月之前的數據直接遷移出來放到備份表中，但在準備實施的過程中發現一個大坑。

表中沒有一個可以排序的索引，導致我們無法快速的篩選出一部分數據!這真是一個深坑，為后面的一些優化埋了個雷;即便是加索引也需要花幾個小時(具體多久沒敢在生產測試)。

如果我們強行按照時間進行篩選，可能查詢出 4000W 的數據就得花上好幾個小時;這顯然是行不通的。

于是我們便想到了一個大膽的想法：這部分數據是否可以直接不要了?

這可能是最有效及最快的方式了，和產品溝通后得知這部分數據真的只是日志型的數據，即便是報表出不來今后補上也是可以的。

于是我們就簡單粗暴的做了以下事情：

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

這樣新的數據就寫到了新表，同時業務上也是使用的這個數據量較小的新表。

雖說過程不太優雅，但至少是解決了問題同時也給我們做技術改造預留了時間。

分表方案

之前的方案雖說可以緩解壓力，但不能根本解決問題。

有些業務必須得查詢之前的數據，導致之前那招行不通了，所以正好我們就借助這個機會把表分了。

我相信大部分人雖說沒有做過實際做過分表，但也見過豬跑;網上一搜各種方案層出不窮。

我認為最重要的一點是要結合實際業務找出需要 sharding 的字段，同時還有上線階段的數據遷移也非常重要。

時間

可能大家都會說用 hash 的方式分配得最均勻，但我認為這還是需要使用歷史數據的場景才用哈希分表。

而對于不需要歷史數據的場景，比如業務上只查詢近三個月的數據。

這類需求完成可以采取時間分表，按照月份進行劃分，這樣改動簡單，同時對歷史數據也比較好遷移。

于是我們首先將這類需求的表篩選出來，按照月份進行拆分，只是在查詢的時候拼接好表名即可;也比較好理解。

哈希

剛才也提到了：需要根據業務需求進行分表策略。

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

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

采用哈希時需要將 sharding 字段選好，由于我們的業務比較單純;是一個物聯網應用，所有的數據都包含有物聯網設備的唯一標識(IMEI)，并且這個字段天然的就保持了唯一性;大多數的業務也都是根據這個字段來的，所以它非常適合來做這個 sharding 字段。

在做分表之前也調研過 MyCAT 及 sharding-jdbc(現已升級為 shardingsphere)，最終考慮到對開發的友好性及不增加運維復雜度還是決定在 jdbc 層 sharding 的方式。

但由于歷史原因我們并不太好集成 sharding-jdbc，但基于 sharding 的特點自己實現了一個分表策略。

這個簡單也好理解：

 

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

其實就是算出了表名，然后路由過去查詢即可。

只是我們實現的非常簡單：修改了所有的底層查詢方法，每個方法都里都做了這樣的一個判斷。

并沒有像 sharding-jdbc 一樣，代理了數據庫的查詢方法;其中還要做 SQL解析-->SQL路由-->執行SQL-->合并結果 這一系列的流程。

如果自己再做一遍無異于重新造了一個輪子，并且并不專業，只是在現有的技術條件下選擇了一個快速實現達成效果的方法。

不過這個過程中我們節省了將 sharding 字段哈希的過程，因為每一個 IMEI 號其實都是一個唯一的整型，直接用它做 mod 運算即可。

還有一個是需要一個統一的組件生成規則，分表后不能再依賴于單表的字段自增了;方法還是挺多的：

• 比如時間戳+隨機數可滿足大部分業務。
• UUID，生成簡單，但沒法做排序。
• 雪花算法統一生成主鍵ID。

大家可以根據自己的實際情況做選擇。

業務調整

因為我們并沒有使用第三方的 sharding-jdbc 組件，所有沒有辦法做到對代碼的低侵入性;每個涉及到分表的業務代碼都需要做底層方法的改造(也就是路由到正確的表)。

考慮到后續業務的發展，我們決定將拆分的表分為 64 張;加上后續引入大數據平臺足以應對幾年的數據增長。

這里還有個小細節需要注意：分表的數量需要為 2∧N 次方，因為在取模的這種分表方式下，即便是今后再需要分表影響的數據也會盡量的小。

再修改時只能將表名稱進行全局搜索，然后加以修改，同時根據修改的方法倒推到表現的業務并記錄下來，方便后續回歸測試。

當然無法避免查詢時利用非 sharding 字段導致的全表掃描，這是所有分片后都會遇到的問題。

因此我們在修改分表方法的底層查詢時同時也會查看是否有走分片字段，如果不是，那是否可以調整業務。

比如對于一個上億的數據是否還有必要存在按照分頁查詢、日期查詢?這樣的業務是否真的具有意義?

我們盡可能的引導產品按照這樣的方式來設計產品或者做出調整。

但對于報表這類的需求確實也沒辦法，比如統計表中某種類型的數據;這種我們也可以利用多線程的方式去并行查詢然后匯總統計來提高查詢效率。

有時也有一些另類場景：

比如一個千萬表中有某一特殊類型的數據只占了很小一部分，比如說幾千上萬條。

這時頁面上需要對它進行分頁查詢是比較正常的(比如某種投訴消息，客戶需要一條一條的單獨處理)，但如果我們按照 IMEI 號或者是主鍵進行分片后再分頁查詢那就比較難受了。

所以這類型的數據建議單獨新建一張表來維護，不要和其他數據混合在一起，這樣不管是做分頁還是 like 都比較簡單和獨立。

驗證

代碼改完，開發也單測完成后怎么來驗證分表的業務是否正常也比較麻煩。

一個是測試麻煩，再一個是萬一哪里改漏了還是查詢的原表，但這樣在測試環境并不會有異常，一旦上線產生了生產數據到新的 64 張表后想要再修復就比較麻煩了。

所以我們取了個巧，直接將原表的表名修改，比如加一個后綴;這樣在測試過程中觀察前后臺有無報錯就比較容易提前發現這個問題。

上線流程

測試驗收通過后只是分表這個需求的80%，剩下如何上線也是比較頭疼。

一旦應用上線后所有的查詢、寫入、刪除都會先走路由然后到達新表;而老數據在原表里是不會發生改變的。

數據遷移

所以我們上線前的第一步自然是需要將原有的數據進行遷移，遷移的目的是要分片到新的 64 張表中，這樣才會對原有的業務無影響。

因此我們需要額外準備一個程序，它需要將老表里的數據按照分片規則復制到新表中;

在我們這個場景下，生產數據有些已經上億了，這個遷移過程我們在測試環境模擬發現耗時是非常久的。而且我們老表中對于 create_time 這樣用于篩選數據的字段沒有索引(以前的技術債)，所以查詢起來就更加慢了。

沒辦法，我們只能和產品協商告知用戶對于之前產生的數據短期可能會查詢不到，這個時間最壞可能會持續幾天(我們只能在凌晨遷移，白天會影響到數據庫負載)。

總結

這便是我們這次的分表實踐，雖說不少過程都不優雅，但受限于條件也只能折中處理。

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

最后得出了幾個結論：

• 一個好的產品規劃非常有必要，可以在合理的時間對數據處理(不管是分表還是切入歸檔)。
• 每張表都需要一個可以用于排序查詢的字段(自增ID、創建時間)，整個過程由于沒有這個字段導致耽擱了很長時間。
• 分表字段需要謹慎，要全盤的考慮業務情況，盡量避免出現查詢掃表的情況。

最后歡迎留言討論。