一次分表踩坑實踐的探討

來自：crossoverJie（微訊號：crossoverJie）

前言

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

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

先談談背景，我們生產資料庫隨著業務發展量也逐漸起來；好幾張單表已經突破億級資料，並且保持每天 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、建立時間），整個過程由於沒有這個欄位導致耽擱了很長時間。

• 分表欄位需要謹慎，要全盤的考慮業務情況，儘量避免出現查詢掃表的情況。

最後歡迎留言討論。