我所理解的MySQL(六)分庫分表與主從同步_Planeswalker23

你好，有幸相見。

從九月開始，我決定發起「每週一博」的目標：每週至少釋出一篇部落格，可以是各種原始碼分析研讀，也可以是記錄工作中遇到的難題。

在經過了一段時間漫無目的的學習之後，我發現那樣用處好像不大，看過的東西過段時間就忘了，而且也沒有做什麼筆記。

“凡所學，必有所輸出。”我認為這才是最適合我的學習方式，這也是「每週一博」活動的來由，朋友們，如果你也覺得經常會忘記以前看過的東西，一起加入這個活動吧。

這是十一月的第二篇部落格，同時也是 MySQL 系列的第六篇。

本文首發於個人部落格: javageekers.club
本文收錄於個人語雀知識庫: 我所理解的後端技術

MySQL 系列的第六篇，主要內容是分庫分表與主從同步。

1. 分庫分表

隨著公司業務的快速發展，資料量也隨之快速增加，你是否會經常接到來自運營、測試、產品對系統查詢響應緩慢的抱怨？是的，我經常會聽到。

不管是由於查詢邏輯複雜還是因為資料量的硬傷，當優化索引後也無法再縮短查詢時間時，這意味著資料庫的效能達到了一個瓶頸，你可以嘗試下面幾種優化方案：

分割槽表
讀寫分離
NoSQL 資料庫
提升硬體效能（CPU、記憶體等）

如果這些還沒用，那麼或許你該考慮通過分庫分表來優化查詢效率了。

1.1 分庫分表為什麼能提高查詢效率

當索引、分割槽表、快取、提升硬體之後查詢還是很慢時，意味著查詢語句的命中行數，也就是說資料量實在是太大了，這才導致單個數據庫例項的效能達到瓶頸。

所以通過分庫分表，將本來讓單個數據庫例項來承受的壓力分攤給多個數據庫例項，這樣每個資料庫例項就承受了小很多的壓力，從而達到提升效能的效果。

分表也是一樣的道理，將單張表的資料分解為多張表，解決了由於列數過多導致的效能問題。

這就像是分治演算法，將一個大問題，分解為若干個相同的小問題。

1.2 分庫分表的方式

說完了分庫分表提高效能的原理，再來說說實現分庫分表的兩種方式：水平切分（Horizental Sharding）和垂直切分（Vertical Sharding），下面分別進行討論。

假設我們有一張使用者表，它的資料量已經達到5000W行或者100G以上：

-- 使用者表
CREATE TABLE `user`  (
  `user_id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NULL DEFAULT NULL,
  `age` int(12) NULL DEFAULT NULL,
  PRIMARY KEY (`record_id`) USING BTREE,
) ENGINE = InnoDB AUTO_INCREMENT = 1;

-- 演出記錄表
CREATE TABLE `show_record`  (
  `show_id` int(12) NOT NULL AUTO_INCREMENT,
  `user_id` int(12) NULL DEFAULT NULL,
  `show_name` varchar(36) NULL DEFAULT NULL,
  `show_date` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`show_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;

insert into user values (5,'重塑',17),(10,'達達',24),(15,'刺蝟',15),(20,'五條人',12);
複製程式碼

現在要對這張表進行分庫分表。

1.2.1 垂直切分

垂直切分是指將資料按照業務進行拆分（比如按照熱點列與非熱點列進行垂直切分），如果拆分後的資料不在同一個資料庫例項中，就是垂直分庫，反之就是垂直分表。

1.2.1.1 垂直分庫

垂直分庫的核心思想是專庫專用，比如將使用者資訊放在 DB1，作為使用者庫；將演出記錄資訊放在 DB2，作為記錄庫。

垂直分庫帶來的好處是：

通過專庫專用的方式，優化了由於單庫資料量過大而產生的效能問題（IO、連線數、記憶體、磁碟硬體資源等）
將不同的業務進行故障隔離

1.2.1.2 垂直分表

垂直分表就是**將一張表按照欄位拆分成多表，每個表儲存其中一部分欄位。**比如將 show_record 表根據 show_id 拆分成 show_detail 和 record 兩張表。

垂直分錶帶來的好處是：

可以減少IO衝突、鎖表、死鎖機率
將不同的業務進行故障隔離

1.2.2 水平切分

水平切分是指將同一張表中的資料按照一定的規則拆分到多張表中進行儲存，如果這多張表不在同一個資料庫例項中，就是水平分庫，反之就是水平分表。

1.2.2.1 水平分庫

可以根據使用者的ID進行水平分庫，ID為奇數的使用者放在 user1 表，ID為偶數的使用者放在 user2 表，同時庫中其他表與ID關聯的資料（比如 show_record 樂隊演出記錄表）也隨之拆分，放在不同的庫中。

這相當於是進行搭建私有云（本地化）的操作：既然所有使用者的資料實在太大，那麼以使用者為單位，一個使用者的資料放在一個數據庫中，那資料量肯定減少許多（如果伺服器夠多的話）。

水平分庫帶來的好處是：

優化了由於單庫資料量過大而產生的效能問題（IO、連線數、記憶體、磁碟硬體資源等）
提高系統可用性，單資料庫例項宕機並不會影響所有業務
可以減少IO衝突、鎖表、死鎖機率

1.2.2.2 水平分表

同樣可以根據使用者的ID進行水平分表，ID為奇數的使用者放在 user1 表，ID為偶數的使用者放在 user2 表，同時庫中其他表與ID關聯的資料（比如 show_record 樂隊演出記錄表）也隨之拆分，放在不同的表中。

水平分表帶來的好處是：

優化了由於單表資料量過大而產生的效能問題
可以減少IO衝突、鎖表、死鎖機率

1.2.3 切分策略

垂直切分更注重業務性，而在水平切分中，我們可以根據下面的幾種方式進行切分：

雜湊：將ID進行雜湊，這樣的好處是使得ID充分雜湊，資料分佈更均勻
ID取模：跟雜湊是相似的原理
範圍：ID範圍或時間範圍
對映表：對映表的方式就是自定義切分策略，更具主觀性，但由於需要通過對映表找到切分後的資料所在區域，所以會影響查詢效率

1.3 分庫分表後帶來的問題

前文說過，要是在優化索引、分割槽表、讀寫分離、NoSQL 資料庫之後還是沒能提高查詢效率，那麼才考慮通過分庫分表來優化，這是因為分庫分表事實上也會帶來許多比查詢效能更嚴重的問題。

1.3.1 分散式事務

在進行分庫之後，資料就存在於兩個資料庫例項中，在進行業務寫操作時就需要通過分散式事務來保證跨庫事務的完整性。

分散式事務解決方案：

全域性事務（如兩階段提交 Two-Phase Commit, 2PC）
補償事務（Try-Confirm-Cancel, TCC）
本地訊息表
MQ事務訊息
開源框架（如 Seata）

1.3.2 跨庫查詢

第二個問題是關於查詢，在進行分庫分表之後，原先業務中的一些查詢語句可能需要重寫，比如：跨庫關聯查詢、跨庫分頁查詢。

先來說說跨庫關聯查詢，這應該是需要儘量避免的，一般禁止進行跨庫關聯查詢。如果實在避免不了，常見的做法是通過冗餘表或者冗餘欄位來實現跨庫關聯查詢，但這也會帶來新的問題：若原始資料被修改了，冗餘欄位是否需要同步修改？

還有一種方法是將多個單表查詢在返回給使用者前通過程式碼進行手動關聯。

再來談談跨庫分頁查詢，這被稱為“業界難題”，58的沈劍大佬曾寫過一篇文章對這個問題進行討論：業界難題-“跨庫分頁”的四種方案，建議詳讀。

除了上面分享分文章中所提及的幾種方法，還可以通過中介軟體來實現跨庫分頁查詢。

1.3.3 全域性主鍵的唯一性

通常我們建議使用自增ID來作為主鍵，但進行分庫分表之後，這種做法會造成主鍵重複問題，所以我們需要使用全域性唯一的主鍵來避免這個問題。

常用的方法有：

UUID：最簡便的唯一性ID生成方案，但是UUID較佔空間，且不連續易導致頁分裂，同時還會影響查詢效能
基於分散式鎖實現自增ID：缺點是分散式鎖會影響效能
分散式自增ID生成器：雪花演算法（Snowflake）

2. 主從同步

主從同步部分主要圍繞 MySQL 實現主從同步的原理來講述，還會涉及到 bin log 的三種日誌模式等內容。

2.1 主從同步實現原理

在本系列第一篇基礎架構一文中曾提到 MySQL 是通過服務層的歸檔日誌 bin log 來實現主從同步的，下面就來聊聊它的實現原理。

我們已經知道在 statement 級別下 bin log 中儲存的是 SQL 本身，下圖就展示了主庫與從庫進行同步的主要節點與流程。

當主庫中有資料更新時，主庫會按照配置的 binlog_format 的值來將日誌寫入 bin_log 中，之後主庫會通過 Log Dump Thread 執行緒來通知從庫有資料需要同步。
從庫中的 I/O Thread 執行緒就是負責與主庫建立連線的，它接收到主庫的新資料通知後，將向 Log Dump Thread 執行緒發起讀取日誌資料的請求，然後將讀取到的日誌資料寫入從庫的中轉日誌 Relay log 中。
最後，從庫中的 SQL Thread 執行緒檢測到中轉日誌 Relay log 有更新後，會將這些發生在主庫的日誌資料解析為 SQL 語句全部執行一遍，以此來完成主從同步。

2.2 binlog_format 三種模式

在 MySQL5.7.7 版本以後，binlog_format 的預設值已經由 statement 改為 row 了，這樣修改的原因也就是接下來要討論的 binlog_format 三種模式下，bin log 都儲存了些什麼內容。

binlog_format 的三種模式分別是：

statement : 客戶端提交的原始 SQL
row : 經過優化後，精確到行的 SQL（不會導致主從不一致，但日誌空間也更大）
mixd : 混合模式，易引起主從不一致的語句使用 row 模式，其他語句使用 statement 模式

關於這部分的論證大家可以自行實踐，或者可以看看《MySQL實戰45講》的 24 | MySQL是怎麼保證主備一致的？這一講，講述的非常詳細。

2.3 為什麼 statement 模式易導致主從不一致

我們已經知道 binlog_format 為 statement 時下，bin log 中記錄的是客戶端提交的原始 SQL。

我們做一個實驗來說明這個問題，首先目前 user 表中現有的資料是：

mysql> select * from user;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    5 | 重塑   |   17 |
|   10 | 達達   |   24 |
|   15 | 刺蝟   |   15 |
+------+--------+------+
3 rows in set (0.00 sec)
複製程式碼

然後假設主庫提交了下面這條刪除語句：

delete from user where id > 1 and age>10 limit 1;
複製程式碼

這條刪除語句的意思是刪除一條主鍵ID大於1並且age>10的資料，但是單從這條 SQL 來看我並不能確定哪一條資料被刪除了，因為我不知道查詢條件走的是哪個索引，再來看下它的執行計劃：

mysql> explain delete from user where id > 1 and age>10 limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
|  1 | DELETE      | user  | NULL       | range | age           | age  | 5       | const |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
複製程式碼

從 key 列可以看到，這條語句使用的是 age 索引，所以在執行刪除操作時，刪除的必定是 age 索引樹上第一個age>10 且 id>1 的資料，也就是(20, '五條人', 12)這一行。

我們知道 MySQL 的優化器選擇索引時會考慮很多因素，比如掃描行數、是否排序等。

單憑 delete from user where id > 1 and age>10 limit 1 這樣一句 SQL 並不能確定最終執行時到底使用的是主鍵索引還是 age 欄位索引，也就無法確定這條語句刪除的到底是哪一行了。

所以在進行主從同步時，如果 binlog_format 為 statement ，很可能會導致下面的情況發生：

主庫使用 age 索引，刪除的是(20, '五條人', 12)這一行
從庫使用主鍵索引，刪除的是(5, '重塑', 17)這一行

即導致主從不一致。

3. 溫故知新

分庫分表的方式？
垂直切分和水平切分有什麼不同？
分庫分表之後可能會遇到哪些問題？
如何保證全域性主鍵的唯一性？
如何實現分散式事務？
雪花演算法的原理
主從同步的原理？
binlog_format 有幾種模式？有什麼不同？
為什麼 statement 模式易導致主從不一致？

我所理解的MySQL(六)分庫分表與主從同步