MySQL 分表策略深度解析：三大方案優劣對比與選型決策指南

為什麼要分表？

單表數據量達到百萬級時：

• B+ 樹高度增加：索引查詢 IOPS 上升，延遲惡化。
• 鎖爭用加劇：即使行鎖，熱點頁/間隙鎖導致並發下降。
• DDL 執行時間不可控：加列、加索引鎖表數小時，業務停擺。
• 備份/恢復窗口超長：單表導出導入耗時過長，RPO/RTO 不達標。

三大分表方案對比

方案一：MySQL 集群/中間件透明分片（推薦架構層面）

• 技術棧：MySQL Group Replication (MGR)、MySQL Router、ProxySQL、ShardingSphere、Vitess。
• 原理：邏輯表映射到多個物理分片，中間件自動路由 SQL。
• 優點：應用無感、擴展性最強、支持跨分片事務 (MGR/XA)、運維工具成熟。
• 缺點：架構複雜、引入中間件單點/延遲、跨分片 JOIN/事務性能損耗大、學習曲線陡峭。

方案二：應用層預分表

-- 預建 100 張表
message_00, message_01, ..., message_99

-- 路由算法 (PHP 示例)
function get_shard_table($base, $user_id) {
    $hash = crc32($user_id);
    $suffix = ($hash < 0) ? '0'.substr(abs($hash), 0, 1) : substr($hash, 0, 2);
    return $base . '_' . $suffix;
}

// 寫入
$table = get_shard_table('message', $uid);
$sql = "INSERT INTO `$table` ...";

// 讀取
$table = get_shard_table('message', $uid);
$sql = "SELECT * FROM `$table` WHERE ...";

• 優點：無中間件依賴、單表極小、查詢極快、完全可控。
• 缺點：路由算法一旦固化極難變更（算法變更需全量數據遷移）、跨分表查詢需應用層合併、運維負擔重（DDL 需批量執行 100 張表）。

方案三：MERGE 存儲引擎（MyISAM 專用，歷史遺留方案）

-- 1. 建立基礎 MyISAM 表
CREATE TABLE user_1 (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=MyISAM;
CREATE TABLE user_2 (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=MyISAM;

-- 2. 建立 MERGE 總表
CREATE TABLE all_user (
  id INT PRIMARY KEY, name VARCHAR(50)
) ENGINE=MERGE UNION=(user_1,user_2) INSERT_METHOD=LAST;

-- 3. 查詢總表 = 自動 UNION ALL 子表
SELECT * FROM all_user WHERE id = 10;

-- 4. 插入總表 = 按 INSERT_METHOD 路由到最後一張表
INSERT INTO all_user VALUES (3, 'new');  -- 實際寫入 user_2

• 優點：應用層幾乎無改動（只改表名）、利用 MyISAM 表鎖特性並發插入尚可。
• 缺點：唯一約束不跨子表生效、REPLACE 不支持、ALTER TABLE 會破壞映射、子表結構不一致極易報錯、MyISAM 無事務/行鎖，不適合核心業務。

方案對比決策矩陣

混合策略建議（生產常用）

1. 集群 + 預分表：核心大表走中間件分片，邊緣小表應用層固定分表。
2. 預分表 + 定期合併歸檔：熱數據按月/用戶分表，冷數據定期合併到歸檔大表/ClickHouse。
3. 分庫分表 + 讀寫分離：寫入主庫分片，讀取從庫/只讀實例，ProxySQL 路由。

避坑關鍵點

• 分表鍵選擇：業務查詢維度一致（如 user_id、shop_id），避免跨分表查詢。
• 預留擴容空間：預分表數量 = 預估峰值數據量 / 目標單表大小 (2000萬) × 2~3 倍。
• 全局唯一 ID：雪花算法 / 數據庫段號模式，避免分表間 ID 衝突。
• DDL 自動化：使用 pt-online-schema-change / gh-ost 在線變更，編寫批量執行腳本。