Mysql 學習筆記：分庫分表 (sharding)

當資料庫性能出現瓶頸時就需要通過擴充來提升性能，對于擴充性來說要麼加強機器本身的性能，要麼把任務分發到不同的機器上。對于資料庫來說通過強悍的機器解決成本是很大的，如Oracle。通過多個廉價的機器實作水準擴充是現代的主流解決方案，如Mysql。

資料庫水準擴充的核心是把資料拆分成不同的單元并放在不同的獨立的執行個體上，這樣就做到了負載均衡。拆分分為邏輯和實體拆分，邏輯拆分是對實體上不可分割的執行個體進行邏輯上的分割，實體拆分是拆分成多個獨立的執行個體：

• 邏輯拆分
  • 分區（Partition）
  • 分表
• 實體拆分
  • 讀寫分離
  • 垂直拆分（分庫）
  • 水準拆分（分表）

1.邏輯拆分



1.1 分區



我了解的邏輯分區：舉個例子，作業系統中的分區，是将硬碟根據大小進行邏輯分區，就是我們看到的C、D、E、F盤，邏輯分區還是在同一個作業系統中。資料庫産品的Partition分區也是一樣的道理，将資料進行邏輯分區，對資料劃分界限。

MySql 支援Range，List，Hash，Key。最常用的是Range。注意不同的版本對分區類型的支援有些不同！

Range：範圍

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30),
  hired DATE NOT NULL DEFAULT '1970-01-01',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  job_code INT NOT NULL,
  store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
  PARTITION p0 VALUES LESS THAN (6),
  PARTITION p1 VALUES LESS THAN (11),
  PARTITION p2 VALUES LESS THAN (16),
  PARTITION p3 VALUES LESS THAN (21)
);
           

LIST：清單

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);
           

Key：鍵

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
           

HASH：哈希

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;
           



例子：

資料：新聞表，2010開始記錄，假設10年到15年每年的資料為200W，總數1000W；

條件：查詢15年7月所有的新聞資料；

未分區：需要把表周遊，1000W條資料，查詢性能就不用說了；

分區：按照年份分區，當要查詢15年資料，隻會周遊15年的資料200W條，加快了查詢；

1.2 分表



當單表資料行數超過一定量級時，讀/寫 會變慢，查詢需要檢索更多資料，DML操作需要更多時間建立/更新索引；我們可以通過把這些資料分散到多個表中來提高效率，這樣隻涉及到部分資料而不是所有，最常用的分表算法是雜湊演算法。哈希函數使用除留餘數法，即取餘的方式。



建立所需要的N個表，表名：user_0 ... user_N-1，通過對ID取餘運算直接路由到所在的表

• user_0： 5%5
• user_1： 1%5 / 6%5
• user_2： 2%5
• user_3： 3%5
• user_4： 4%5

小結：邏輯分區是資料庫提供的功能，不用對應用和業務做任何改變就能實作。哈希分表實作簡單，隻需要修改少量代碼就能實作。對單表進行分表後，能夠大大提高我們讀寫的效率。

2.實體拆分



2.1 讀寫分離（主從複制）



讀寫分離的核心是把讀/寫操作路由的不同執行個體上，執行個體之間要的資料要保障一緻（通過複制實作），路由可以自己識别 Insert/Update/Delete/Selete 做路由，也可以使用代理(mysql proxy)或中間件。

一般站點的讀操作比寫操作更加密集，查詢量暴增的時候單台伺服器無法處理這麼多讀操作，我們需要增加額外的伺服器來支撐，使用主從方式，主做寫操作，從做讀操作，通過主從複制達到資料一緻性，這樣讀操作壓力會被分散。mysql使用單線程把主機資料複制到從機上實作資料一緻性，是以需要對主從進行配置。





在上面的主從架構中，如果從庫有很多個可能會出現複制延遲過大現象，原因是因為mysql複制需要在slave和master建立長連接配接，并且master需要開啟binlog dump線程進行資料推送，過多的slave會導緻複制延遲過大。可以增加複制源和開啟半同步複制解決。



1.增加複制源：





2.開啟半同步複制：主庫送出事務時，将事件寫入它的二進制日志，而從庫在準備就緒時請求它們。主庫無需等待從庫的ACK回複，直接送出事務并傳回用戶端。異步複制不確定所有事件都能到達從庫，無法保證資料完整性



2.2 垂直拆分（分庫）



讀寫分離不能解決寫操作頻繁帶來的性能瓶頸，比如主庫寫操作占80%，這時需要把寫操作拆分到獨立的執行個體上，垂直拆分是按照業務相關度把資料拆分到不同的DB上，這樣寫操作自然就被拆分開來。



拆分了之後還可以繼續做讀寫分離進一步提升性能，但垂直拆分也帶來了問題，原本在一個事務中的資料操作，在拆分之後就無法在同一個事務中完成，這使得我們業務應用需要額外的成本去解決，如通過引入分布式事務 或 最終一緻來解決。

2.3 水準拆分（分表）



對資料庫做了垂直切分和讀寫分離可以解決大部分站點的問題，但是在體量巨大的應用中主資料庫寫操作壓力依然會達到極限，這時需要對表進行水準拆分并分布在不同機器上面。



水準拆分最簡單的方式就是用雜湊演算法，一個表隻能根據一個字段sharding。下面列舉了一些常用的拆分方法：



1.簡單hash算法

建立所需要的N個表，表名：user_0 ... user_N-1，通過對ID取餘運算直接路由到所在的表：

• user_0： 5%5
• user_1： 1%5 / 6%5
• user_2： 2%5
• user_3： 3%5
• user_4： 4%5



優點：

• 查詢分片位置的時間複雜度為O(1)，簡單有效。

缺點：

• 動态擴容有局限：當容量不足需要增加分片數量來擴容，哈希值會發生改變，涉及全量資料遷移。
• 熱點資料集中：活躍使用者分到了同一個片上，這個執行個體壓力非常大可能會過載。

2.一緻性hash算法

在擴容時簡單hash算法需要全量資料遷移成本和風險很高，一緻性hash算法對該算法進行了優化，通過對固定值2^32-1進行取餘保證hash結果不變，再通過範圍把環拆分成N份，增加節點時隻影響新節點到逆時針第一個節點之間的資料。

整體擴容：如果分片數量不足需要擴容，因為要保證資料分布均勻，是以受影響的節點會占總量的一半。



局部擴容：一緻性hash通過在局部增加節點實作靈活擴容，而不必每次都翻倍擴容，可以對熱點資料表進行再拆分，隻影響新節點到逆時針第一個節點之間的資料，但是需要額外再維護映射表保證其他節點還映射到舊表。



優點：

• 可以靈活選擇局部還是整體擴容，局部擴容可以對某個熱點資料的節點再拆分而不影響其他節點。

缺點：

• 在節點過多的情況下查詢效率較低，表映射實作複雜。

3.動态映射



熱點資料集中可能是由于某個ID産生的資料過多造成的，通過配置指定到具體的分片上可以過熱問題。



優點：可以做局部擴容解決熱點資料問題。

缺點：實作比較複雜，每次都需要查詢擷取對應分片性能比簡答hash差，會影響查詢效率。

2.4 拆分帶來的問題



實體拆分帶來好處的同時也帶來的一些問題：



• 跨庫事務
  • 通過分布式事務 或 最終一緻解決
• 跨庫Join
  • 把Join操作拆分成多次查詢并在應用中做聚合
  • 使用搜尋引擎做資料聚合和查詢
  • 使用CQRS做資料聚合
  • 使用流式處理，通過MapReduce模型并行執行再做合并，比串行查詢快很多，不過依賴中間件實作該能力。
• 跨表分頁和排序：
  • 由中間件去所有分片聚合資料，再做分頁和排序

接下來講一下CQRS是怎麼做的。

CQRS是對應用做讀寫職責分離，每次寫操作都會以類似日志的形式記錄在Event Store中，并不是直接修改字段值到期望值，再由Event Bus把事件同步到讀服務，讀服務對讀庫資料進行修改，所有查詢都會走讀服務。在該架構模式中讀服務可以把想要的業務資料聚合到讀庫中，其實就是通過備援資料的方式避免應用去多庫中查詢和聚合資料，以空間換時間。