MERGE 引擎適用場景
- 海量日誌/流水表：按天/月分表
iplog_20240101、iplog_20240102...
- 需求：跨分表統一查詢（
SELECT * FROM iplog_merge WHERE ip='1.2.3.4'）、零應用改造（View 同義）、分表獨立維護（歸檔/刪除單表不影響整體）。
- 底層：MyISAM 子表集合，MERGE 表本身不儲存資料，僅存
.frm 定義與 .MRG 映射檔。
自動化建立 MERGE 表腳本
#!/bin/bash
# 每日跑批，合併 iplog_202401* 到 iplog_merge
DB_HOST="jill"
DB_NAME="fb"
PREFIX="iplog_202401"
# 1. 取得符合前綴的子表
TABLES=$(mysql -h $DB_HOST $DB_NAME --skip-column-names -e "SHOW TABLES LIKE '${PREFIX}%';" | tr '\n' ',' | sed 's/,$//')
# 2. 刪除舊 MERGE 表
mysql -h $DB_HOST $DB_NAME -e "DROP TABLE IF EXISTS iplog_merge;"
# 3. 建立新 MERGE 表（結構必須與子表完全一致，含索引順序）
SQL="CREATE TABLE iplog_merge (
id INT(11) NOT NULL,
ip VARCHAR(30) COLLATE utf8_bin NOT NULL,
time DATETIME NOT NULL,
type TINYINT(4) NOT NULL DEFAULT '0',
ip_num INT(10) UNSIGNED DEFAULT NULL,
KEY id (id),
KEY ip_id (ip,id),
KEY ipnum_id (ip_num,id)
) ENGINE=MERGE UNION=($TABLES) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;"
mysql -h $DB_HOST $DB_NAME -e "$SQL"
echo "MERGE table rebuilt with: $TABLES"
三大核心坑點與解法
| 坑點 | 錯誤現象 | 根因 | 解法 |
|---|
| 索引順序不一致 | ERROR 1168 (HY000): Unable to open underlying table which is differently defined | 子表索引定義順序不同（如 KEY id, KEY ip_id vs KEY ip_id, KEY id） | 統一建表腳本，強制索引順序；或用 pt-table-sync 修正 |
| 壓縮表與非壓縮表混用 | ERROR 1030 (HY000): Got error 124 from storage engine | 部分子表 myisampack 壓縮，部分未壓縮 | MERGE 表所有子表必須同為壓縮或同為非壓縮；歸檔壓縮前先從 MERGE 移除 |
| DDL 操作限制 | 執行 DROP TABLE/TRUNCATE/ALTER 子表後，MERGE 仍引用舊檔案 | MERGE 表 "open" 狀態下禁止子表結構變更 | 操作前 FLUSH TABLES iplog_merge; 關閉 MERGE；操作後 FLUSH TABLES 重新開啟 |
手動修改 .MRG 檔案（緊急調整子表清單）
# 1. 關閉 MERGE 表
mysql -e "FLUSH TABLES iplog_merge;"
# 2. 編輯映射檔
vi /var/lib/mysql/fb/iplog_merge.MRG
# 內容格式：每行一個子表名
iplog_20240101
iplog_20240102
# 新增/刪除行即可
# 3. 重新開啟
mysql -e "FLUSH TABLES;"
壓縮歸檔最佳實踐
# 1. 從 MERGE 移除要歸檔的表
# 編輯 .MRG 移除 iplog_20240101，或重建 MERGE 排除該表
FLUSH TABLES iplog_merge;
# 2. 壓縮子表 (唯讀、極大壓縮比)
myisampack /var/lib/mysql/fb/iplog_20240101
# 產生 .MYD 壓縮、.MYI 重建
# 3. 修復索引 (myisampack 後必須)
myisamchk -rq /var/lib/mysql/fb/iplog_20240101
# 4. 壓縮表不可寫入，僅供查詢。如需再次寫入須解壓 (myisamchk -d)
效能與替代方案對比
| 方案 | 查詢效能 | 維護複雜度 | 寫入支援 | 適用版本 |
|---|
| MERGE (MyISAM) | 單執行緒掃描，無分區剪枝 | 中 (需同步結構、.MRG) | 僅插入底層子表 | 全版本 |
| Partitioning (InnoDB) | 分區剪枝、並行查詢 (5.6+) | 低 (DDL 線上、自動管理) | 全支援 | 5.1+ |
| 分表 + 中間件 (ShardingSphere/ProxySQL) | 路由精準、可跨庫 | 高 (需中介軟體) | 全支援 | 任意 |
| ClickHouse / Apache Doris | OLAP 極致、列式壓縮 | 異構遷移 | 批次導入 | 外部系統 |
MERGE 是 MyISAM 時代的產物，適合唯讀歸檔日誌的零改造查詢。新架構首選 InnoDB Partitioning 或專用 OLAP 引擎，獲得事務支援、並行查詢、線上 DDL 與更好的壓縮比。
