您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

基於 MERGE (MRG_MyISAM) 引擎實現 MySQL 分表聚合查詢實戰

編輯：MySQL 綜合教學

MySQL 分表聚合利器：MERGE (MRG_MyISAM) 引擎實戰指南

面對單表數據量過大、查詢性能下降的場景，常見方案是水平分表（Sharding）。但業務常有「聚合視圖」需求：既要分表寫入性能，又要跨分表統一查詢。MySQL 原生 MERGE 存儲引擎（別名 MRG_MyISAM）恰好解決此痛點：建立一個虛擬主表，映射多個結構相同的 MyISAM 子表，查詢主表即自動聚合所有子表數據。本文詳細記錄從建表、關聯、維護到結構變更的完整實踐流程。

一、核心原理與適用場景

特性	說明
引擎類型	MERGE / MRG_MyISAM（MySQL 內建，無需額外安裝）
底層要求	所有子表必須使用 MyISAM 引擎，結構完全一致（欄位、索引、字符集）
主表特性	無實際數據，僅存儲元數據（.frm + .MRG 文件），極輕量
查詢行為	`SELECT * FROM merge_table` 等價於 `UNION ALL` 所有子表
寫入控制	`INSERT_METHOD`：`NO`（禁止寫入）、`FIRST`（寫第一張）、`LAST`（寫最後一張）
適用場景	日誌、流水、監控數據等「寫多讀少、需全量掃描聚合、無事務要求」的業務
不適用場景	需要事務（InnoDB）、高併發寫入、頻繁 DDL 變更、外鍵約束的業務

二、完整建表與關聯腳本

1. 創建主表（模板表）

CREATE TABLE `mygame_message_log` (
  `id` INT NOT NULL,  -- 注意：主鍵不能設為 AUTO_INCREMENT
  `msg_type` TINYINT NOT NULL,
  `content` VARCHAR(512),
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `create_time`)  -- 聯合主鍵建議包含分區鍵
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

2. 批量創建 20 張結構完全一致的子表

CREATE TABLE `mygame_message_log_1`  LIKE `mygame_message_log`;
CREATE TABLE `mygame_message_log_2`  LIKE `mygame_message_log`;
-- ... 重複至 _20
CREATE TABLE `mygame_message_log_20` LIKE `mygame_message_log`;

提示：生產環境可用 Shell/Python 循環生成腳本，避免手工重複。

3. 將主表引擎改為 MERGE 並關聯所有子表

ALTER TABLE `mygame_message_log` 
  ENGINE = MERGE 
  UNION = (`mygame_message_log_1`,`mygame_message_log_2`,...,`mygame_message_log_20`)
  INSERT_METHOD = NO  -- 禁止直接寫主表，業務寫入具體子表
  AUTO_INCREMENT = 1;

三、業務寫入與查詢模式

1. 寫入路由（應用層決定寫哪張子表）

-- 按日期/用戶ID哈希等路由邏輯，決定具體子表
INSERT INTO `mygame_message_log_3` (`id`,`msg_type`,`content`,`create_time`) 
VALUES (1001, 1, '測試消息', NOW());

2. 聚合查詢（直接查主表）

-- 自動掃描所有 20 張子表並合併結果
SELECT COUNT(*) FROM `mygame_message_log` WHERE `msg_type` = 1;
SELECT * FROM `mygame_message_log` WHERE `create_time` > '2024-01-01' ORDER BY `create_time` DESC LIMIT 100;

四、維護操作：結構變更的標準化流程

核心鐵律：絕對禁止直接對 MERGE 主表或子表執行 ALTER TABLE ADD/DROP/MODIFY COLUMN，否則會導致 .MRG 元數據與子表結構不一致，查詢報錯甚至數據損壞。

標準變更步驟（以新增欄位為例）

刪除 MERGE 主表（僅刪元數據，不影響子表數據）
DROP TABLE `mygame_message_log`;
逐張修改所有子表結構
ALTER TABLE `mygame_message_log_1` ADD `new_col` INT DEFAULT 0;
... 重複 20 次（建議腳本化）
重建主表模板
CREATE TABLE `mygame_message_log` LIKE `mygame_message_log_1`;
重新關聯 MERGE
ALTER TABLE `mygame_message_log` ENGINE=MERGE UNION=(...) INSERT_METHOD=NO;
驗證
SELECT * FROM `mygame_message_log` LIMIT 1;

五、性能優化與最佳實踐

子表數量控制：建議 10~50 張，過多會導致 open_tables 限制報錯（需調大 table_open_cache）。
索引一致性：所有子表索引必須完全一致，否則優化器可能選錯索引導致全表掃描。
INSERT_METHOD=NO **強制應用層路由**：避免寫入熱點集中在第一/最後一張表。
定期歸檔：舊子表（如 6 個月前）可 RENAME TABLE 移至歸檔庫，再從 MERGE UNION 列表中移除，減少掃描開銷。
監控指標：關注 Opened_tables、Select_scan、Table_locks_waited，發現異常及時擴容或調優。

六、遷移建議：從 MERGE 到現代分區/分庫分表

MySQL 5.7+ 原生 PARTITION、MySQL 8.0+ INSTANT DDL、中間件 ShardingSphere/MyCat、TiDB 等新架構均優於 MERGE。若業務出現以下信號，建議規劃遷移：

需要事務一致性（MERGE 不支持事務）
單表寫入 QPS > 5k，MyISAM 表鎖成為瓶頸
頻繁 DDL 變更（每月 > 1 次）
跨數據中心/雲廠商部署需求

七、總結

MERGE (MRG_MyISAM) 是 MySQL 早期實現「分表存儲、聚合查詢」的輕量級方案，零代碼侵入、零中間件、運維簡單。但其短板明顯：無事務、表鎖、DDL 維護痛苦、MyISAM 崩潰恢復風險。適合「日誌/流水/監控」等非核心、可容忍少量數據丟失、查詢以全表掃描聚合為主的場景。核心操作口訣：建模板 → 批量 LIKE → ALTER ENGINE=MERGE UNION=() → 業務寫子表/查主表 → 變更必走「刪主-改子-建模板-再關聯」標準流程。