建立記憶體表語法
CREATE TABLE `tablename` (
`columnName` VARCHAR(256) NOT NULL
) ENGINE=MEMORY
DEFAULT CHARSET=utf8mb4
MAX_ROWS=100000000;
關鍵點：`ENGINE=MEMORY`、`MAX_ROWS` 預估上限（優化器參考）、字符集建議 `utf8mb4`。
核心參數調優
1. `max_heap_table_size`（全域硬性上限）
# my.cnf
[mysqld]
max_heap_table_size = 2048M # 2GB，依實際記憶體與業務量設定
- 單位：bytes，動態可調 `SET GLOBAL max_heap_table_size = 2147483648;`
- 記憶體表資料量超過此值，**自動轉換為 MyISAM 磁碟表**，效能驟降。
- 建議設為預估最大表資料量的 1.2~1.5 倍。
2. `tmp_table_size`（臨時表上限，影響內部排序/JOIN）
tmp_table_size = 1024M # 建議與 max_heap_table_size 一致
- 內部隱式臨時表超過此值同樣落盤 MyISAM。
- 兩者取 **min(max_heap_table_size, tmp_table_size)** 作為實際上限。
MEMORY 引擎特性與限制（必讀）
| 特性 | 說明 | 影響 |
|---|
| 全局可見 | 所有連線共享同一張表資料 | 極適合緩存、查找表、會話共享 |
| 不支援 TEXT/BLOB | 僅定長/變長字串、數值、日期 | 長文本需外部存儲（檔案/物件存儲） |
| 索引類型 | 預設 HASH 索引（=、<=>），可指定 BTREE | 範圍查詢（<、>、BETWEEN、LIKE）需建 BTREE |
| 不支援 AUTO_INCREMENT | 5.6+ 已支援，舊版需應用層生成 ID | 版本確認 |
| 索引欄位不可為 NULL | 必須 `NOT NULL` | 建表時強制 `NOT NULL DEFAULT ''` |
| 重啟資料全失 | 結構保留（`.frm` 在磁碟），資料清空 | 啟動腳本需自動回填 |
| 複製表失去索引/主鍵 | `CREATE TABLE t2 ENGINE=MEMORY SELECT * FROM t1` 僅複製資料與結構 | 需額外 `ALTER TABLE` 加回主鍵/索引 |
資料持久化策略（解決重啟丟失）
方案 A：應用層懶加載（推薦，簡單通用）
// 擬代碼
function getMemoryTableData() {
$count = query("SELECT COUNT(*) FROM mem_table");
if ($count == 0) {
// 重新從源表載入
execute("DELETE FROM mem_table");
execute("INSERT INTO mem_table SELECT * FROM source_table WHERE ...");
}
return query("SELECT * FROM mem_table");
}
方案 B：MySQL 啟動初始化腳本（需系統權限）
# /etc/mysql/init_memory_tables.sql
DELETE FROM mem_table;
INSERT INTO mem_table SELECT * FROM source_table WHERE ...;
# my.cnf
[mysqld]
init_file = /etc/mysql/init_memory_tables.sql
注意：`init_file` 在啟動早期執行，若源表鎖定或尚未就緒會失敗，需加重試邏輯或改用 Systemd `ExecStartPost`。
方案 C：觸發器同步（寫入源表同步更新記憶體表）
CREATE TRIGGER tr_source_after_insert AFTER INSERT ON source_table
FOR EACH ROW BEGIN
INSERT INTO mem_table (id, name) VALUES (NEW.id, NEW.name);
END;
CREATE TRIGGER tr_source_after_update AFTER UPDATE ON source_table
FOR EACH ROW BEGIN
UPDATE mem_table SET name = NEW.name WHERE id = NEW.id;
END;
CREATE TRIGGER tr_source_after_delete AFTER DELETE ON source_table
FOR EACH ROW BEGIN
DELETE FROM mem_table WHERE id = OLD.id;
END;
優點：強一致性。缺點：增加源表寫入延遲，DDL 變更需同步修改觸發器。
常用管理 SQL 片段
-- 1. 安全刪除重建
DROP TABLE IF EXISTS `mem_cache`;
-- 2. 從源表複製結構+資料建立記憶體表
CREATE TABLE `mem_cache` ENGINE=MEMORY SELECT * FROM `source_table`;
-- 3. 補回主鍵與索引（複製會丟失）
ALTER TABLE `mem_cache` ADD PRIMARY KEY (`id`);
ALTER TABLE `mem_cache` ADD INDEX `idx_name` (`name`);
-- 4. 指定 BTREE 索引支援範圍查詢
CREATE TABLE `mem_range` (
`id` INT UNSIGNED NOT NULL,
`score` INT NOT NULL,
INDEX `idx_score` (`score`) USING BTREE
) ENGINE=MEMORY;
-- 5. 查看記憶體表大小
SELECT TABLE_NAME,
ROUND(DATA_LENGTH/1024/1024,2) AS data_mb,
ROUND(INDEX_LENGTH/1024/1024,2) AS index_mb,
TABLE_ROWS
FROM information_schema.TABLES
WHERE ENGINE='MEMORY' AND TABLE_SCHEMA='your_db';
效能對比與適用場景
| 場景 | MEMORY 表 | InnoDB + Buffer Pool | Redis |
|---|
| 單機、中小資料集（<1GB）、極高 QPS 讀取 | ✅ 最佳 | 次之（有 MVCC/鎖開銷） | 需外部依賴 |
| 跨機器共享、持久化、複雜查詢 | ❌ 不支援 | ✅ 原生支援 | ✅ 叢集模式 |
| 資料頻繁變更、需 ACID | ❌ 無事務/持久化 | ✅ 完整 ACID | ⚠️ Lua 腳本/事務有限 |
| 全文搜尋、JSON、GIS | ❌ 不支援 | ✅ 原生支援 | ⚠️ 模組支援 |
避坑總結
- 容量規劃：`max_heap_table_size` + `tmp_table_size` 必須 ≥ 預估峰值資料量，否則靜默落盤 MyISAM，效能崩塌無報錯。
- 索引選型：範圍查詢、排序、GROUP BY **必須**顯式 `USING BTREE`，預設 HASH 僅支援等值查詢。
- 字符集：`utf8mb4` 下 `VARCHAR(255)` 佔 768 bytes，記憶體消耗放大 3 倍，評估容量時按實際字節計算。
- 重啟預案：生產環境**必須**有自動回填機制（啟動腳本/應用懶加載/觸發器），否則重啟後業務直接報空指標/404。
- 並發寫入：MEMORY 表鎖粒度為**表級鎖**，高併發寫入嚴重衝突，適合「寫入少、讀取多、資料可重建」場景。
總結
MySQL MEMORY 引擎是**單機高速讀取緩存**的利器，但受限於「無持久化、表級鎖、不支援 TEXT/BLOB/範圍查詢（預設 HASH）」。落地關鍵在於：容量參數預留充足、索引按查詢模式選 BTREE/HASH、重啟回填機制落地、嚴格限定適用場景。若需跨機器、持久化、複雜查詢，請直接上 Redis Cluster 或 InnoDB + 足夠 Buffer Pool。
