Mysql資料庫分庫分表實踐

? 目前應用系統的最大瓶頸出現在資料庫，主要是基于資料庫的邏輯存盤結構，和磁盤的物理特性決定了隨機讀取效率低下，無法靠簡單磁盤陣列的擴展或者分布式檔案系統，來提升性能，

資料庫的瓶頸

不管是IO瓶頸，還是CPU瓶頸，最終都會導致資料庫的活躍連接數增加，進而逼近甚至達到資料庫可承載活躍連接數的閾值，在業務Service來看就是，可用資料庫連接少甚至無連接可用，接下來就可以想象了吧（并發量、吞吐量、崩潰），

• IO瓶頸

  • 由于關系型資料庫的存盤結構，不適用于分布式檔案系統，往往會出現磁盤讀IO瓶頸，熱點資料太多，資料庫快取放不下，每次查詢時會產生大量的IO，降低查詢速度 ==> 分庫和垂直分表，

  • 網路IO瓶頸，請求的資料太多，網路帶寬不夠 ==> 分庫，

• CPU瓶頸

  • SQL問題，如SQL中包含join，group by，order by，非索引欄位條件查詢等，增加CPU運算的操作 ==> SQL優化，建立合適的索引，在業務Service層進行業務計算，
  • 單表資料量太大，查詢時掃描的行太多，SQL效率低，CPU率先出現瓶頸 ==> 水平分表，

分庫分表

水平分片

概念：

以欄位為依據，按照一定策略（hash、range等），將資料拆分到多個分片中，分片多了，io和cpu的壓力自然可以成倍緩解，

場景：

• 水平分表

  系統絕對并發量并沒有上來，變更不頻繁，只是單表的資料量太多，存在冷熱資料，影響了SQL效率，加重了CPU負擔，以至于成為瓶頸，

• 水平分庫

  系統絕對并發量上來了，分表難以根本上解決問題，并且還沒有明顯的業務歸屬來垂直分庫，

垂直分片

概念：

以表為依據，按照業務歸屬不同，將資料拆分到不同的分片中，

場景：

• 垂直分表

  • 系統絕對并發量并沒有上來，表的記錄并不多，但是欄位多，并且熱點資料和非熱點資料在一起，單行資料所需的存盤空間較大，以至于資料庫快取的資料行減少，查詢時會去讀磁盤資料產生大量的隨機讀IO，產生IO瓶頸，

  • 可以用串列頁和詳情頁來幫助理解，垂直分表的拆分原則是將熱點資料（可能會冗余經常一起查詢的資料）放在一起作為主表，非熱點資料放在一起作為擴展表，這樣更多的熱點資料就能被快取下來，進而減少了隨機讀IO，拆了之后，要想獲得全部資料就需要關聯兩個表來取資料，但記住，千萬別用join，因為join不僅會增加CPU負擔并且會講兩個表耦合在一起（必須在一個資料庫實體上），關聯資料，應該在業務Service層做文章，分別獲取主表和擴展表資料然后用關聯欄位關聯得到全部資料，

• 垂直分庫

  • 系統絕對并發量上來了，并且可以抽象出單獨的業務模塊，
  • 到這一步，基本上就可以服務化了，例如，隨著業務的發展一些公用的配置表、字典表等越來越多，這時可以將這些表拆到單獨的庫中，甚至可以服務化，再有，隨著業務的發展范訓出了一套業務模式，這時可以將相關的表拆到單獨的庫中，甚至可以服務化，我們在微服務拆分的時候，其實已經完成了資料庫的垂直拆分

3. 分庫分表的基本原則

• 什么時候需要考慮分庫分表

  ? 當MySQL單表的記錄數達到500W左右時，即要考慮分庫分表，來滿足業務增長

• 分片鍵的選擇

  ? 分片鍵的選擇，要結合業務來進行，一般在SQL占比最大的陳述句中，選擇分片鍵，

  ? 首先根據需求分析，判斷系統請求最大的業務型別，并提供生產環境相應的資料支撐，

  ? 如何在生產環境查詢sql的執行次數？

  use performance_schema;
  
  SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  

  通過該陳述句可以查詢哪類的SQL執行最多

• 冷熱資料分離，大欄位分離

• 將Mysql定位于事務性資料庫（OLTP），專注于事務流水操作，發揮關系形資料庫的特長

• 結合業務避免笛卡爾乘積，盡量以小表驅動大表，來進行分庫關聯

• 分庫分表會大大提高系統設計的復雜度，需要平衡

4. 常見分片策略

• 基于范圍分片

  • 時間：按照年、月、日去切分，例如order_2020、order_202005、order_20200501
  • 地域：按照省或市去切分，例如order_beijing、order_shanghai、order_chengdu
  • 大小：從0到1000000一個表，例如1000001-2000000放一個表，每100萬放一個表

  優點：新的資料可以落在新的存盤節點上，如果集群擴容，資料無需遷移，
  缺點：資料熱點分布不均，資料冷熱不均勻，導致節點負荷不均，

• Hash取模分片

  整型的Key可直接對設備數量取模，其他型別的欄位可以先計算Key的哈希值，然后再對設備數量取模，假設有n臺設備，編號為0 ~ n-1，通過Hash(Key) % n就可以確定資料所在的設備編號，該模式也稱為離散分片，

  優點：實作簡單，資料分配比較均勻，不容易出現冷熱不均，負荷不均的情況，
  缺點：擴容時會產生大量的資料遷移，比如從n臺設備擴容到n+1，絕大部分資料需要重新分配和遷移，（有優化方案）

• 一致性Hash+虛擬節點

  通過一致性Hash + 虛擬節點 可以在保證資料冷熱均勻的基礎上，大大減少資料遷移的作業量

  

  ?

  擴容后：

  

• Jump Consistent Hash

  參考1：https://opensource.actionsky.com/20200213-hash/

  參考2：https://opensource.actionsky.com/20201223-dble/

  參考3： https://arxiv.org/ftp/arxiv/papers/1406/1406.2294.pdf

  參考4：https://opensource.actionsky.com/20190910-dble/

• 分組法

  Hash分片是可以解決資料均勻的問題，范圍法`可以解決資料遷移問題，那我們可以不可以兩者相結合呢？利用這兩者的特性呢？

  考慮在hash分片的基礎，加上一個Group的概念，組內遵循hash分片，組與組之間，采用范圍法

  設計方案如圖：

  

  

  因為組內采用了hash分片，這樣落在每一個分片的資料是基本平均的，每一個資料庫關聯不同的分片，而且資料庫也可以關聯不同分組的分片，這樣就可以保證每個資料庫的流量平均，并且擴容只需要新增一個組就可以了，

  • 資料結構設計：

可以將這個映射關系快取起來，這樣不會影響性能

5. 分庫分表后帶來的問題

非磁區鍵查詢，需要遍歷多個庫

• 場景一（一對一的場景）

  用戶表采用 user_id 哈希取模進行了水平分庫，分散了單庫的壓力，但是這里可能會出現一些問題，一個是說，用戶在登錄的時候，可能不是根據userid登陸的，可能是根據用戶名，手機號之類的來登錄的，此時你又沒有userid，怎么知道去哪個表里找這個用戶的資料判斷是否能登錄呢？

  • 解決方案：冗余雙寫映射法

    創建一張userId 和手機號的映射表（該映射表也可以通過手機號分片），在往用戶表插入的時候，同時維護映射表，這樣通過手機號和 userId 都可以快速定位到某個分片，這張映射表還可以考慮放在快取中

    • 冗余會帶來一致性問題，跨庫雙寫事務如何處理

      詳見分布式事務小節

      

• 場景二（一對多的場景）

  用戶表和訂單表是一對多的情況，通過訂單id查詢訂單詳情流量占比為60%，通過userId查詢訂單串列的訪問量占30%，這時候如果通過訂單id分片，則查詢用戶的訂單串列，就需要遍歷所有的分片；如果通過userId 分片，在查詢訂單id的詳情時，就需要遍歷所有的分片

  • 解決方案：基因法（ER分片）

• 場景三（多對多的場景）

  典型的場景，用戶之間的相互關注，需要查詢我的關注和關注我的流量各占50%，一旦資料量爆炸以后，需要分片，如果采用 user_id 分片，則查詢“關注我的人”就需要遍歷多個分片；如果采用follower_id分片，則查詢“我的關注”還是需要遍歷多個分片

  • 解決方案：異構表冗余

    

  • 資料冗余勢必會帶來資料一致性的問題

    詳見分布式事務小節

• 場景四：復合場景（多分片鍵組合）

  銷售中心訂單表，包含 用戶ID(user_id), 訂單ID(order_id), 商戶ID( merchant_id) , 主要的訪問方式有

  1. 通過 user_id 查詢我的訂單 (30%)
  2. 通過 order_id 查詢訂單詳情（60%）
  3. 通過 merchant_id 查詢商戶的訂單量 （10%）

  那么在資料量很大的時候，單表無法支撐時，應該如何進行拆分呢？

  碰到復雜的問題，可以把他拆解成已知的問題：

  • user_id 和 order相當于是一對多的場景，可以采用基因法
  • user_id和merchant_id相當于多對多的場景，可以采用異構表冗余法

  可以通過兩者結合的方法來處理

• 場景五

  • 訊息流水表（用戶動態，app訊息推送, 訂單表）：記錄數量隨著時間的推移而不斷增長，資料存在冷熱，離當前時間越近的資料訪問頻度越高，流水表在原有分庫的基礎上，最好要再按時間分表，這樣可以防止隨著時間推移出現資料爆炸，

  • 在流水表的訪問流量中，經常會碰到查詢最近1個月，最近1年的資料，如果單單只存盤一個月的資料，那么在應用層就需要查詢兩次，為了減少一次查詢，給應用層代理便利，可以適當的冗余，例如：

    1. 訊息推送表，按月分表，可以在存盤當月資料的同時，冗余上個月的資料，每張表存盤2個月的資料，這樣在查詢最近一個月的資料時，就可以只查詢一次，就回傳從上個月的今天到現在的所有資料

    2. 訂單表分片的策略，按照用戶ID的基因分庫，按照年分表，每張表存盤2年的資料（存盤今年的資料并冗余上去年的資料），這樣在查詢最近一年的資料時，就可以只查詢一次，

    

• 場景六

  有些時候，有一些工具表，比如資料字典，手機歸屬地表，省份表等，每一個分片都有可能會使用到

  • 解決方案：全域表（變化少，并發低，可以考慮 XA 兩階段事務）

    像資料字典，省份表，廠商表這些變動不是非常頻繁的表可以，考慮采用全域表，在多個庫中都包含該表

• 復雜搜索場景

  如在運營管理界面上，有一個用戶管理模塊，需要對用戶按照性別，住址，年齡，職業等各種條件進行各種組合的復雜搜索

  解決方案：對用戶資料進行binlog監聽，把需要搜索的所有欄位同步到Elasticsearch中去，建立好搜索的索引

  

• 簡單的OLAP場景

  有些時候我們還會有一些簡單報表統計的需求，如：需要統計銷量前100的商品

  解決方案：單獨創建一個報表庫，在其中創建中間表，通過訂閱變更的訊息，來實作增量同步

  創建報表庫，在報表庫中維護一個商品銷量表 rpt_sales_volume，當用戶下單后，發布訊息，報表服務通過消費該訊息，更新商品的銷量，當資料量很大時可以考慮分表（報表資料的查詢一般提供給運營，并發不會特別大）

  注意：應該將Mysql定位于事務性資料庫（OLTP），專注于事務流水操作，發揮關系形資料庫的特點，盡量通過離線或者流式計算的方式來處理OLAP，

分頁查詢問題

• 分頁需要確認排序欄位，在分庫中查詢出來以后，進行二次組裝，需要在每一個分片上先查詢出來pageSize*pageNum數量的記錄，然后在應用層進行組裝（效率比較低）
• 監聽binlog, 將變更發送到訊息佇列，同步到Elasticsearch中，在ES中進行分頁查詢

分布式事務

分布式事物的技術方案很多，這里只介紹適合做資料冗余的分布式事務技術

• **實時性的要求不是非常高：**通過最終一致性來保證分布式事務

  • 方案一：binlog監聽

    通過Canal監聽binlog, 增量同步到Elasticsearch中，為了防止并發過大，以及每一條日志都可以正確同步，保證資料的最終一致性，在Canal和Elasticsearch之間加了一層MQ（一般使用kafka），通過mq的ack機制保證每一條binlog日志的正常同步，同時給大并發提供緩沖

    • 注意： 在處理同一行資料時，需要保證MQ的有序消費

    

    資料對比

    為了給資料同步上一個保險，還可以利用有序佇列，進行資料比對，我們可以利用有序佇列的特性，讓其第一條訊息堆積十分鐘，那么后續訊息基本上也會堆積十分鐘，然后就可以消費這個訊息進行資料拉取，拿到最新的資料進行資料對比，如圖所示：

    

    • 其他同步工具選型
      • logstash_jdbc（全量同步）
      • kafka_connector

  • 方案二：本地訊息表

    本地訊息表這個方案最初是 ebay 架構師 Dan Pritchett 在 2008 年發表給 ACM 的文章，該方案中會有訊息生產者與消費者兩個角色，假設系統 A 是訊息生產者，系統 B 是訊息消費者，其大致流程如下：

    

    1. 當系統 A 被其他系統呼叫發生數據庫表更操作，首先會更新資料庫的業務表，其次會往相同資料庫的訊息表中插入一條資料，兩個操作發生在同一個事務中

    2. 系統 A 的腳本定期輪詢本地訊息往 mq 中寫入一條訊息，如果訊息發送失敗會進行重試

    3. 系統 B 消費 mq 中的訊息，并處理業務邏輯，如果本地事務處理失敗，會在繼續消費 mq 中的訊息進行重試，如果超過重試次數，記錄錯誤記錄和日志，人工介入（當錯誤記錄超過100條時，所有事務全部回滾，防止產生大量需要人工處理的資料）

       這里沒有采用失敗后回滾的邏輯，主要還是考慮事務一的資料，一旦入庫，后續的事務必須成功，這樣不會產生臟資料，這就需要在事務開啟時，對需要的資源進行預留和鎖定，

    本地訊息表實作的條件：

    1. 消費者與生成者的介面都要支持冪等
    2. 生產者需要額外的創建訊息表
    3. 事務開啟時，對需要的資源進行預留和鎖定（分布式鎖）
    4. 需要建立錯誤監控體系，及時人工介入，錯誤記錄要保留完整的資料，方便資料恢復

    容錯機制：

    1. 步驟 1 失敗時，事務直接回滾
    2. 步驟 2、3 寫 mq 與消費 mq 失敗會進行重試
    3. 超過失敗重試的次數，記錄錯誤記錄和日志，人工介入（當錯誤記錄超過100條時，所有事務全部回滾，防止產生大量需要人工處理的資料）

    此方案的核心是將需要分布式處理的任務通過訊息日志的方式來異步執行，訊息日志可以存盤到本地文本、資料庫或訊息佇列，再通過業務規則自動或人工發起重試，人工重試更多的是應用于支付場景，通過對賬系統對事后問題的處理，

• **實時性，和準確性要求高：**本地訊息表+實時查詢

  在原資料庫中插入完以后，先在相同的庫添加一條映射記錄，然后記錄快取，表示映射表有資料還沒有同步，按異步方案進行同步，如果此時已經需要查詢該映射記錄，則先按映射表的分片表的記錄查詢出來，并加上快取中記錄的這條映射，

• 使用支持ACID的分布式資料庫（Mysql Cluster）

  • 實作原理

    分布式資料庫實作分布式事務的主流方法還是2PC, 過多副本（Multi-Paxos）,解決了2PC單點，阻塞和資料不一致的問題

    

    如上圖所示，當分布式事務提交時，會選擇其中的一個資料分片作為協調者在所有資料分片上執行兩階段提交協議，由于所有資料分片都是通過 Paxos 復制日志實作多副本高可用的，當主副本發生宕機后，會由同一資料分片的備副本轉換為新的主副本繼續提供服務，所以可以認為參與者和協調者都是保證高可用不宕機的（多數派存活），繞開了協調者宕機的問題，

    在參與者高可用的實作前提下，可以對協調者進行了“無狀態”的優化，在標準的兩階段提交中，協調者要通過記錄日志的方法持久化自己的狀態，否則如果協調者和參與者同時宕機，協調者恢復后可能會導致事務提交狀態不一致，但是如果我們認為參與者不會宕機，那么協調者并不需要寫日志記錄自己的狀態，

    所以在第一階段所有參與者都回復prepare完成以后，即可以反饋事務提交成功，提升了2PC的效率
    由于存在多副本，只要保證在prepare階段，驗證事務執行沒有錯誤，協調者發出commit指令后，就可以樂觀的認為，事務執行成功并反饋給事務發起者，相信commit訊息會被多數副本收到，多數副本收到訊息以后，剩下的就交給他們自己同步
    

    在上圖中（綠色部分表示寫日志的動作），左側為標準兩階段提交協議，用戶感知到的提交時延是4次寫日志耗時以及2次 RPC 的往返耗時；由于少了協調者的寫日志耗時以及提前了應答客戶端的時機，用戶感知到的提交時延是1次寫日志耗時以及1次 RPC 的往返耗時，

分片的擴容問題

• 成倍擴容法

  1. 首先添加一個從庫，實作和主庫同步

2. 同步完成后，修改資料源，成倍擴容

• 時間斷+Hash分片

  根據時間斷來進行分片，（比如在2020-01-01前創建的用戶id，取Hash值后，按100取模；2020-01-01至2020-12-31創建的用戶id,取Hash值后，按1000取模；）好處：擴容不需要對資料遷移，

6. 最佳實踐

? 可以通過用戶手機的歸屬地進行分庫，然后基于用戶ID 的Hash值進行分表，其他和用戶相關的表（比如訂單和內容），打入歸屬地和用戶ID 的基因，進行ER分片，

Sharding-Sphere實踐

DBLE實踐

Canal, XData + Elasticsearch 實踐