第36期：MySQL 原生水平拆表

引言

上一章節我們探討過資料垂直拆分，今天我們來繼續討論資料拆分：水平拆分！

水平拆分和垂直拆分有些不一樣，垂直拆分最小單元是欄位，與業務有很強的關聯性，具體業務對應具體的拆分資料；而水平拆分最小單元是資料行，與具體業務關係不大，業務關聯可以是拆分後的單張表資料，也可以是拆分前的全域性資料。簡單來說，水平拆分對應用透明，應用邏輯在資料拆分後不需要大動。

正文

一般在關聯式資料庫中，水平拆分具體對應兩個方面：

第一是水平拆表。

水平拆表是基於一張表的某個欄位，以一定的拆分方法拆分為多張表的資料拆分，拆分完後需要把原來對單張表的操作轉換為對多張子表的操作。一般來講，和垂直拆分類似，需要一張全域性路由表。定義好路由表後，即可簡化對拆分表的操作。比如涉及到資料拆分後的資料同步，查詢語句下發到拆分子表等都可以直接操作路由表。

如下圖所示，表 A 按照 ID 拆分，奇數表為表 A1 ，偶數表為表 A2 ：

第二是水平分割槽。

水平分割槽和水平拆表類似，都是基於一個欄位對錶資料進行拆分。不同的是水平分割槽是資料庫內建功能，相比水平分表來講，操作上會更加簡單。不過最大的缺點是拆分後的資料不能跨例項，這也是現在大部分中介軟體或者是 NEW SQL 不直接用水平分割槽的原因。但是如果僅僅考慮在單機上進行資料拆分，應該首選水平分割槽，具體原因我們後續章節會講，今天主要是來回顧下 MySQL 的內建水平拆表方案。

MySQL 原生水平拆表

提到 MySQL 的原生水平拆表，能想到的就是 MERGE 表！MERGE 表是針對傳統 MYISAM 表做水平彙總的功能表。一提到 MYISAM ，可能好多人覺得過時了，沒必要繼續了！不過我這兒要講的是，雖然 MERGE 過時了，但是 MERGE 提供給我們未來進行水平拆分的方法是非常值得學習的，這也就是為什麼這篇要對 MERGE 做一個示例說明的原因。瞭解如何使用 MERGE 表對我們後面要講的 INNODB 表拆分有很大的借鑑意義。

MERGE 表的優點很多，羅列如下：

資料易於管理。比如一張很大的日誌表，存放10年的資料，按照月份拆分成120張表，採用 MERGE 表做匯聚，不需要對120張子表分別檢索，只需要檢索 MERGE 表即可。
降低單塊磁碟 IO 使用率。比如可以把日誌表不同月份的資料分散到不同的磁碟來避免單一磁碟 IO 使用率過高的問題。
查詢簡單。比如日誌表的查詢模式比較固定，查詢當前年份資料的請求非常頻繁，歷史資料偶爾查詢，就可以拆分成兩張表，一張當前表，一張歷史表，再用 MERGE 表來做這兩張表的統一入口，查詢 MERGE 表會自動路由到當前表。
MERGE 表零維護。MERGE 表只存放所包含的子表後設資料，所以不需要維護，建立銷燬非常快速。
MERGE 表管理的子表非常靈活。每張子表不需要侷限在單個資料庫中，可以靈活的分佈在不同的資料庫裡。

那接下來，用幾個簡單例子來了解下 MERGE 表的使用方法。

表m1 - m10，10張子表，分別按照 id 從小到大來存放，每張表10000條記錄，表結構如下：

(debian-ytt1:3500)|(ytt)>show create table m1\G
*************************** 1. row ***************************
       Table: m1
Create Table: CREATE TABLE `m1` (
  `id` int NOT NULL,
  `r1` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_r1` (`r1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>select count(*) from m1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)
...

對應的 MERGE 表結構如下：

(debian-ytt1:3500)|(ytt)>show create table m_global\G
*************************** 1. row ***************************
       Table: m_global
Create Table: CREATE TABLE `m_global` (
  `id` int NOT NULL,
  `r1` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_r1` (`r1`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT_METHOD=LAST UNION=(`m1`,`m2`,`m3`,`m4`,`m5`,`m6`,`m7`,`m8`,`m9`,`m10`)
1 row in set (0.00 sec)

表定義中包含的子表用 union 來定義，Insert_method 表示插入時往最後一張表插入。

用 merge 表來查詢這10張表記錄總數:

(debian-ytt1:3500)|(ytt)>select count(*) from m_global;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

簡化查詢：

比如要在m1,m2,m3這三張表分別查詢 id 為1，10001，20001的記錄，需要每張表查詢出來後再 UNION

(debian-ytt1:3500)|(ytt)>select * from m1 where id = 1
    -> union all
    -> select * from m2 where id = 10001
    -> union all
    -> select * from m3 where id = 20001;
+-------+------+
| id    | r1   |
+-------+------+
|     1 |    1 |
| 10001 |    1 |
| 20001 |    1 |
+-------+------+
3 rows in set (0.00 sec)

使用 merge 表來簡化查詢，只需查詢一次即可。

(debian-ytt1:3500)|(ytt)>select * from m_global where id in (1,10001,20001);
+-------+------+
| id    | r1   |
+-------+------+
|     1 |    1 |
| 10001 |    1 |
| 20001 |    1 |
+-------+------+
3 rows in set (0.00 sec)

MERGE 表最大的問題是插入記錄：MERGE 表屬性 insert_method 有三個選項，NO/FIRST/LAST。

表m_global設定的是LAST，也就是說插入新的記錄會往最後一張表裡插入。比如插入一條記錄（1000001，100），會往子表m10裡插入。

(debian-ytt1:3500)|(ytt)>insert into m_global values (1000001,100);
Query OK, 1 row affected (0.00 sec)

(debian-ytt1:3500)|(ytt)>select * from m_global where id = 1000001;
+---------+------+
| id      | r1   |
+---------+------+
| 1000001 |  100 |
+---------+------+
1 row in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>select * from m10 where id = 1000001;
+---------+------+
| id      | r1   |
+---------+------+
| 1000001 |  100 |
+---------+------+
1 row in set (0.00 sec)

同樣，如果設定 insert_method=first ，則只會往第一張表插入。這會造成資料分佈非常不均勻，後期需要對資料增大的表再次手動拆分。所以 MERGE 表提供了第三個選項：insert_method=no 。設定後，不允許對 MERGE 表寫入，只允許讀取。

(debian-ytt1:3500)|(ytt)>alter table m_global insert_method=no;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

(debian-ytt1:3500)|(ytt)>insert into m_global values (1000002,100);
ERROR 1036 (HY000): Table 'm_global' is read only
(debian-ytt1:3500)|(ytt)>

設定 MERGE 表為只讀後，子表資料的分佈就得靠非 MySQL 原生方法來保證。

上面我只列了 MERGE 表的優點，缺點也有很多：

MERGE 表的子表是 MYISAM 引擎，並且不能基於 MERGE 表建立全文索引。
MERGE 表使用更多的檔案描述符。
對 MERGE 表索引的讀取會更慢。MERGE 表會掃描底下的每張表索引來看看哪個合適。
針對第三個缺點，比如之前的查詢，來對比下兩條 SQL 的執行計劃：

   (debian-ytt1:3500)|(ytt)>explain format=tree  select * from m1 where id = 2 union all select * from m2 where id = 10002 union all select * from m3 where id = 20002\G
   *************************** 1. row ***************************
   EXPLAIN: -> Append
       -> Stream results
           -> Rows fetched before execution
       -> Stream results
           -> Rows fetched before execution
       -> Stream results
           -> Rows fetched before execution
   
   1 row in set (0.00 sec)
   
   (debian-ytt1:3500)|(ytt)>explain format=tree select * from m_global where id in (2,10002,20002)\G
   *************************** 1. row ***************************
   EXPLAIN: -> Filter: (m_global.id in (2,10002,20002))  (cost=2.11 rows=3)
       -> Index range scan on m_global using PRIMARY  (cost=2.11 rows=3)
   
   1 row in set (0.00 sec)

結果很明顯，對三張子表的 UNION 查詢效率比 MERGE 表查詢效率高。

對 MERGE 表的刪除不會刪除底下的子表。MERGE 表就是一張路由表，刪除路由表不會對底下子表有影響。

   (debian-ytt1:3500)|(ytt)>drop table m_global;
   Query OK, 0 rows affected (0.01 sec)
   
   (debian-ytt1:3500)|(ytt)>show tables like 'm%';
   +--------------------+
   | Tables_in_ytt (m%) |
   +--------------------+
   | m1                 |
   | m10                |
   | m2                 |
   | m3                 |
   | m4                 |
   | m5                 |
   | m6                 |
   | m7                 |
   | m8                 |
   | m9                 |
   +--------------------+
   10 rows in set (0.00 sec)

所以 MERGE 表的應用場景僅僅侷限於以下：

日誌表，並且已經按照定義好的拆分鍵，對錶進行了拆分。比如按照日期，按照使用者 ID 等。
不常更新的表，可以對錶進行壓縮。
資料可靠性要求不高的表。比如新聞資訊類等。

總結

這篇我們討論了 MySQL 的原生水平拆表，雖然 MERGE 表已經過時，但熟悉 MERGE 表的使用可以擴充我們後續的水平拆表思路。

關於 MySQL 的技術內容，你們還有什麼想知道的嗎？趕緊留言告訴小編吧！