Java互連網架構-Mysql分庫分表訂單產生系統實戰分析

標籤：最好   執行   非同步寫入   核心   評價指標   能力   概述   遷移   同步   

概述

分庫分表的必要性

首先我們來瞭解一下為什麼要做分庫分表。在我們的業務（web應用）中，關係型資料庫本身比較容易成為系統效能瓶頸，單機儲存容量、串連數、處理能力等都很有限，資料庫本身的“有狀態性”導致了它並不像Web和應用伺服器那麼容易擴充。那麼在我們的業務中，是否真的有必要進行分庫分表，就可以從上面幾個條件來考慮。

· 單機儲存容量。您的資料量是否在單機儲存中碰到瓶頸。比如餓了麼一天產生的使用者行為資料就有24T，那麼在傳統的單機儲存中肯定是不夠的。

· 串連數、處理能力。在我們的使用者量達到一定程度時，特定時間的並發量又成了一個大問題，在一個高並發的網站中秒級數十萬的並發量都是很正常的。在普通的單機資料庫中秒級千次的操作問題都很大。

所以在我們進行分庫分表之前我們最好考慮一下，我們的資料量是不是夠大，並發量是不是夠大。如果您的回答是肯定的，那我們就開始做吧。

分庫分表的幾種方法

在分庫分表中，我們有幾種不同的劃分方式：垂直分表、垂直分庫、水平分表、水平分庫分表。分的方式大同小異，主要思想都是化大為小，且可以融合使用。

垂直分表

垂直分表在日常的開發和設計中比較常見，簡單來講就是“大表拆小表”，拆分是基於關係型資料庫中的列進行的。我們在基礎資料庫課程中老師就教導我們，一個表的欄位不要太多，就算資料量沒有那麼大，為了表的合理性我們也會做。比如，某個表中的欄位比較多，可以建立立一張“擴充表”，將不經常使用或者長度較大的欄位拆分出去放到“擴充表”中。

比如在一張產品表中（id, name, price, company, ），我們將常用的（id, name, price, company）欄位放在一個表中，而不常用的（）欄位放在拓展表中，那我們查詢時優先查詢常用欄位，在有必要時才查詢拓展欄位。可以有效提高效率，同時減少欄位後我們在一個表中可以容納的資料數量也提高了。

垂直分表還有一個好處，能使得我們的微服務的關注點更加明確。

Note：分表的操作最好在資料庫設計階段就做完，如果在後續開發過程中再拆分，則可能需要大量的更改SQL語句。

垂直分庫

垂直分庫的基本思路便是我們按照不同的業務模組來劃分出不同的資料庫。垂直分庫在“微服務”盛行的今天已經非常普及了。就像我們上面所說的，這能使得我們的微服務的關注點更加明確。也就是商務邏輯更加清晰。

比如在我們的業務中，User, Product, Company等都屬於不同業務模組，便可將其放在不同的資料庫。

水平分表

水平分表的思想很簡單，就相當於一摞烙餅一百個，然後我每十個放一個籃子。在資料庫中的表現就是，我一個User表有100萬條資料，那麼我0-10w放在一個表中，10-20w放一個表中，類推（當然這隻是其中一種分布規律）。這樣可以降低單表的資料量，最佳化查詢效能。

水平分表能夠降低單表的資料量，在一定程度上能夠緩解查詢效能的瓶頸。但其本質上還是在一個資料庫中，所以當查詢上升到資料庫級，其IO瓶頸並沒有得到好的解決。所以並不推薦單純的水平分表做法。

水平分庫分表

水平分庫分表與水平分表的思想相同，即將同一表中的大量資料分層次的儲存，不同的在於將分出來的表放在不同的資料庫中。在高並發和海量資料的情境下，分庫分表能夠有效緩解單機和單庫的效能瓶頸和壓力，突破IO、串連數、硬體資源的瓶頸。當然，投入的硬體成本也會更高。同時，這也會帶來一些複雜的技術問題和挑戰（例如：跨分區的複雜查詢，跨分區事務等）。

本人結合項目實踐，對水平分庫做一個系統地剖析，希望為大家水平分庫（包括去IOE）改造提供總體思路。

主要內容包括：

水平分庫說明

分庫維度-- 根據哪個欄位分庫

分庫策略-- 記錄如何分配到不同庫

分庫數量-- 初始庫數量及庫數量如何增長

路由透明-- 如何?庫路由，支援應用透明

分頁處理-- 跨多個庫的分頁case如何處理

Lookup映射—非分庫欄位對應到分庫欄位，實現單庫訪問

整體架構-- 分庫的整體技術架構

上線步驟-- 分庫改造實施上線

項目總結

水平分庫說明

資料庫拆分有兩種：

1) 垂直分庫

資料庫裡的表太多，拿出部分到新的庫裡，一般是根據業務劃分表，關係密切的表放同一資料庫，應用修改資料庫連接即可，比較簡單。

2) 水平分庫

某張表太大，單個資料庫儲存不下或訪問效能有壓力，把一張表拆成多張，每張表存放部分記錄，儲存在不同的資料庫裡，水平分庫需要對系統做大的改造。

訂單表格儲存體在Oracle資料庫，記錄有上億條，欄位有上百個，訪問的模式也是複雜多樣，隨著業務快速增長，無論儲存空間或訪問效能都面臨巨大挑戰，特別在大促時，訂單庫已成為系統瓶頸。

通常有兩種解決辦法：

Scale up，升級Oracle資料庫所在的物理機，提升記憶體/儲存/IO效能，但這種升級費用昂貴，並且只能滿足短期需要。

Scale out，把訂單庫拆分為多個庫，分散到多台機器進行儲存和訪問，這種做法支援水平擴充，可以滿足長遠需要。

採取後一種做法，它的訂單庫主要包括訂單主表/訂單明細表（記錄商品明細）/訂單擴充表，水平分庫即把這3張表的記錄分到多個資料庫中，訂單水平分庫效果如所示：

原來一個Oracle庫被多個MySQL庫取代，支援1主多備和讀寫分離，主備之間通過MySQL內建的資料同步機制（SLA<1秒），所有應用通過訂單服務訪問訂單資料。

分庫維度

水平分庫首先要考慮根據哪個欄位作為分庫維度，選擇標準是盡量避免應用代碼和SQL效能受影響，這就要求當前SQL在分庫後，訪問盡量落在單個庫裡，否則單庫訪問變成多庫掃描，讀寫效能和應用邏輯都會受較大影響。

對於訂單拆分，大家首先想到的是按照使用者Id拆分，結論沒錯，但最好還是資料說話，不能拍腦袋。好的做法是首先收集所有SQL，挑選where語句最常出現的過濾欄位，比如使用者Id/訂單Id/商家Id，每個欄位在SQL中有三種情況：

單Id過濾，如使用者Id=？

多Id過濾，如使用者Id IN (?,?,?)

該Id不出現

然後進一步統計，假設共有500個SQL訪問訂單庫，3個過濾欄位出現情況如下：

過濾欄位單Id過濾多Id過濾不出現

使用者Id12040330

訂單Id6080360

商家Id150485

結論明顯，應該選擇使用者Id進行分庫。

等一等，這隻是靜態分析，每個SQL訪問的次數是不一樣的，因此還要分析每個SQL的訪問量。我們分析了Top15執行最多的SQL (它們佔總執行次數85%)，如果按照使用者Id分庫，這些SQL 85%落到單個資料庫, 13%落到多個資料庫，只有2%需要遍曆所有資料庫，明顯優於使用其他Id進行分庫。

通過量化分析，我們知道按照使用者Id分庫是最優的，同時也大致知道分庫對現有系統的影響，比如這個例子中，85%的SQL會落到單個資料庫，這部分的訪問效能會最佳化，堅定了各方對分庫的信心。

分庫策略

分庫維度確定後，如何把記錄分到各個庫裡呢？一般有兩種方式：

根據數值範圍，比如使用者Id為1-9999的記錄分到第一個庫，10000-20000的分到第二個庫，以此類推。

根據數值模數，比如使用者Id mod n，餘數為0的記錄放到第一個庫，餘數為1的放到第二個庫，以此類推。

兩種分法的優劣比較如下：

評價指標按照範圍分庫按照Mod分庫

庫數量前期數目比較小，可以隨使用者/業務按需增長前期即根據mode因子確定庫數量，數目一般比較大

訪問效能前期庫數量小，全庫查詢消耗資源少，單庫查詢效能略差前期庫數量大，全庫查詢消耗資源多，單庫查詢效能略好

調整庫數量比較容易，一般只需為新使用者增加庫，老庫拆分也隻影響單個庫困難，改變mod因子導致資料在所有庫之間遷移

資料熱點新舊使用者購物頻率有差異，有資料熱點問題新舊使用者均勻到分布到各個庫，無熱點

實踐中，為了處理簡單，選擇mod分庫的比較多。同時二次分庫時，為了資料移轉方便，一般是按倍數增加，比如初始4個庫，二次分裂為8個，再16個。這樣對於某個庫的資料，一半資料移到新庫，剩餘不動，對比每次只增加一個庫，所有資料都要大規模變動。

補充下，mod分庫一般每個庫記錄數比較均勻，但也有些資料庫，存在超級Id，這些Id的記錄遠遠超過其他Id，比如在廣告情境下，某個大廣告主的廣告數可能佔總體很大比例。如果按照廣告主Id模數分庫，某些庫的記錄數會特別多，對於這些超級Id，需要提供單獨庫來儲存記錄。

分庫數量

分庫數量首先和單庫能處理的記錄數有關，一般來說，Mysql 單庫超過5000萬條記錄，Oracle單庫超過1億條記錄，DB壓力就很大(當然處理能力和欄位數量/訪問模式/記錄長度有進一步關係)。

在滿足上述前提下，如果分庫數量少，達不到分散儲存和減輕DB效能壓力的目的；如果分庫的數量多，好處是每個庫記錄少，單庫訪問效能好，但對於跨多個庫的訪問，應用程式需要訪問多個庫，如果是併發模式，要消耗寶貴的線程資源；如果是串列模式，執行時間會急劇增加。

最後分庫數量還直接影響硬體的投入，一般每個分庫跑在單獨物理機上，多一個庫意味多一台裝置。所以具體分多少個庫，要綜合評估，一般初次分庫建議分4-8個庫。

路由透明

分庫從某種意義上來說，意味著DB schema改變了，必然影響應用，但這種改變和業務無關，所以要盡量保證分庫對應用代碼透明，分庫邏輯盡量在資料訪問層處理。當然完全做到這一點很困難，具體哪些應該由DAL負責，哪些由應用負責，這裡有一些建議：

對於單庫訪問，比如查詢條件指定使用者Id，則該SQL只需訪問特定庫。此時應該由DAL層自動路由到特定庫，當庫二次分裂時，也只要修改mod 因子，應用代碼不受影響。

對於簡單的多庫查詢，DAL負責匯總各個資料庫返回的記錄，此時仍對上層應用透明。

對於帶彙總運算的多庫查詢，如帶groupBy/orderby/min/max/avg等關鍵字，建議DAL匯總單個庫返回的結果，上層應用做進一步處理。一方面DAL全面支援各種case，實現很複雜；另一方面，從1號店實踐來看，這樣的例子不多，在上層應用作針對性處理，更加靈活。

DAL可進一步細分為JDBC和DAL兩層，基於JDBC層面實現分庫路由，系統開發難度大，靈活性低，目前也沒有很好的成功案例；一般是基於持久層架構進一步封裝成DDAL(分布式資料訪問層)，實現分庫路由，1號店DAL即基於iBatis進行上層封裝而來。

分頁處理

分庫後，有些分頁查詢需要遍曆所有庫，這些case是分庫最大的受害者L。

舉個分頁的例子，比如要求按時間順序展示某個商家的訂單，每頁100條記錄，由於是按商家查詢，需要遍曆所有資料庫，假設庫數量是8，我們來看下分頁處理邏輯：

如果取第1頁資料，則需要從每個庫裡按時間順序取前100條記錄，8個庫匯總後有800條，然後對這800條記錄在應用裡進行二次排序，最後取前100條。

如果取第10頁資料，則需要從每個庫裡取前1000(100*10)條記錄，匯總後有8000條記錄，然後對這8000條記錄二次排序後取（900,1000）條記錄。

分庫情況下，對於第k頁記錄，每個庫要多取100*(k-1)條記錄，所有庫加起來，多取的記錄更多，所以越是靠後的分頁，系統要耗費更多記憶體和執行時間。

對比沒分庫的情況，無論取那一頁，都只要從單個DB裡取100條記錄，而且無需在應用內部做二次排序，非常簡單。

那如何解決分庫情況下的分頁問題呢？有以下幾種辦法：

如果是在前台應用提供分頁，則限定使用者只能看前面n頁，這個限制在業務上也是合理的，一般看後面的分頁意義不大（如果一定要看，可以要求使用者縮小範圍重新查詢）。

如果是後台批處理任務要求分批擷取資料，則可以加大page size，比如每次擷取5000條記錄，有效減少分頁數（當然離線訪問一般走備庫，避免衝擊主庫）。

分庫設計時，一般還有配套大資料平台匯總所有分庫的記錄，有些分頁查詢可以考慮走大資料平台。

Lookup映射

分庫欄位只有一個，比如這裡是使用者Id，但訂單表還有其他欄位可唯一區分記錄，比如訂單Id，給定一個訂單Id，相應記錄一定在某個庫裡。如果盲目地查詢所有分庫，則帶來不必要的開銷，Lookup映射可根據訂單Id，找到相應的使用者Id，從而實現單庫定位。

可以事先檢索所有訂單Id和使用者Id，儲存在Lookup表裡，Lookup表的記錄數和訂單庫記錄總數相等，但它只有2個欄位，所以儲存和查詢效能都不是問題。實際使用時，一般通過分布式緩衝來最佳化Lookup效能。對於新增的訂單，除了寫訂單表，同時要寫Lookup表。

整體架構

訂單產生水平分庫的總體技術架構如所示：

上層應用通過訂單服務/分庫代理和DAL訪問資料庫。

代理對訂單服務實現功能透明，包括彙總運算，非使用者Id到使用者Id的映射。

Lookup表用於訂單Id/使用者Id映射，保證按訂單Id訪問時，可以直接落到單個庫，Cache是Lookup的記憶體資料映像，提升效能，cache故障時，直接存取Lookup表。

DAL提供庫的路由，根據使用者Id定位到某個庫，對於多庫訪問，DAL支援可選的並發訪問模式，並支援簡單記錄匯總。

Lookup表初始化資料來自於現有分庫資料，新增記錄時，直接由代理非同步寫入。

上線步驟

訂單表是核心業務表，它的水平分割影響很多業務，本身的技術改造也很大，很容易出紕漏，上線時，必須謹慎考慮，整個方案實施過程如下：

首先實現Oracle和MySQL兩套庫並行，所有資料訪問指向Oracle庫，通過資料同步程式把資料從Oracle拆分到多個MySQL分庫，比如3分鐘增量同步處理一次。

按照上述架構圖搭建整個體系，選擇幾個對資料即時性不高的訪問例子（如訪問曆史訂單），轉向MySQL分庫訪問，然後逐漸增加更多非即時case，以檢驗整套體系可行性。

如果效能和功能都沒問題，再一次性把所有即時讀寫訪問轉向MySQL，廢棄Oracle。

這個上線步驟多了資料同步程式的開發（大約1人周工作量，風險很低），但分散了風險，把第一步的技術風險（Lookup/DAL等基礎設施改造）和第二步的業務功能風險（Oracle改MySQL文法）分開。兩階段上線都是一次性成功，特別是第二階段上線，100多個依賴方應用簡單重啟即完成升級，中間沒有出現一例較大問題。

總結

到這裡，Mysql分庫分表訂單產生系統實戰分析就結束了，，不足之處還望大家多多包涵！！覺得收穫的話可以點個關注收藏轉寄一波喔，謝謝大佬們支援。（吹一波，233~~）

下面和大家交流幾點編程的經驗：

1、多寫多敲代碼，好的代碼與紮實的基礎知識一定是實踐出來的

2丶 測試、測試再測試，如果你不徹底測試自己的代碼，那恐怕你開發的就不只是代碼，可能還會聲名狼藉。

3丶 簡化編程，加快速度，代碼風騷，在你完成編碼後，應回頭並且最佳化它。從長遠來看，這裡或那裡一些的改進，會讓後來的技術服務人員更加輕鬆。

Java互連網架構-Mysql分庫分表訂單產生系統實戰分析