MySQL MEMORY (HEAP) 表完全指南：特性、限制、重啟數據丟失應對策略

MEMORY (HEAP) 表核心特性

硬性限制 (必須遵守)

• ❌ **不支援** BLOB/TEXT 類型欄位
• ❌ **不支援** <、>、<=、>= 範圍查詢 (僅 = 和 <=>)
• ❌ **不支援** AUTO_INCREMENT (5.0 版本限制，5.1+ 已支援)
• ❌ **索引欄位不能為 NULL** (必須 NOT NULL)
• ❌ 表級鎖 (無行鎖)，高並發寫入爭用嚴重

關於 <=> 運算子 (NULL-safe equal)

SELECT 1 <=> 1;      -- 1
SELECT NULL <=> NULL; -- 1 (普通 = 返回 NULL)
SELECT 1 <=> NULL;    -- 0

在 MEMORY 表唯一允許的比較中，<=> 正確處理 NULL 比較。

重啟數據丟失：三大應對策略

策略 A：查詢前檢查 + 重建 (通用、簡單)

function ensure_heap_data($table, $source_sql) {
    $count = $db->get_var("SELECT COUNT(*) FROM `$table`");
    if ($count == 0) {
        $db->query("TRUNCATE TABLE `$table`");
        $db->query($source_sql);  -- 如：INSERT INTO heap SELECT * FROM source
        // 或重建索引
        $db->query("ALTER TABLE `$table` ADD PRIMARY KEY (`id`), ADD INDEX `idx_name` (`name`)");
    }
}
// 每次查詢前調用，或封裝在 Model 層

⚠️ 額外一次 COUNT(*) 查詢開銷，可接受。

策略 B：首次查詢結果判斷 + 延遲重建 (省一次查詢)

function get_heap_data($table, $source_sql) {
    $data = $db->get_results("SELECT * FROM `$table` LIMIT 1");
    if (empty($data)) {
        $db->query("TRUNCATE TABLE `$table`");
        $db->query($source_sql);
        return $db->get_results("SELECT * FROM `$table`");
    }
    return $db->get_results("SELECT * FROM `$table`");
}
// 僅在首次為空時重建，後續直接讀取

策略 C：服務啟動腳本自動重建 (最透明、需運維權限)

# /etc/init.d/mysql 或 systemd service 啟動後執行
# 或 MySQL init_file 參數
# my.cnf
[mysqld]
init_file=/usr/local/mysql/init_heap.sql

# init_heap.sql 內容
TRUNCATE TABLE `cache_table`;
INSERT INTO `cache_table` SELECT * FROM `source_table`;
ALTER TABLE `cache_table` ADD PRIMARY KEY (`id`), ADD INDEX `idx_name` (`name`);

常用 SQL 片段速查

-- 安全刪除重建
DROP TABLE IF EXISTS `heap_cache`;

-- 從源表複製結構+數據建立 HEAP 表
CREATE TABLE `heap_cache` ENGINE=MEMORY SELECT * FROM `source_table`;

-- 補充主鍵與索引 (複製後丟失)
ALTER TABLE `heap_cache` ADD PRIMARY KEY (`id`);
ALTER TABLE `heap_cache` ADD INDEX `idx_username` (`username`);

-- 指定 BTREE 索引支援範圍查詢
CREATE TABLE `heap_cache` (
  `id` INT NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_name` (`name`) USING BTREE
) ENGINE=MEMORY;

適用場景與避坑指南

✅ 適合場景

• **熱點數據緩存**：配置表、字典表、權限表、會話數據
• **中間結果集**：複雜報表臨時聚合、多步驟 ETL 中間表
• **高頻讀、極少寫**：查詢 QPS 萬級、更新頻率極低
• **數據可重建**：源表存在，丟失可從源恢復

❌ 禁用場景

• 持久化業務數據 (訂單、用戶、財務)
• 大表 (> max_heap_table_size，會轉 MyISAM 導致性能崩塌)
• 高並發寫入 (表鎖瓶頸)
• 需範圍查詢但未建 BTREE 索引
• 含 BLOB/TEXT/NULL 索引欄位

現代替代方案對比

配置調優

[mysqld]
max_heap_table_size = 256M      -- 單表最大內存 (需配合 tmp_table_size)
tmp_table_size = 256M           -- 內部臨時表最大內存
# 實際限制取兩者較小值

MEMORY 表是把「雙刃劍」：**用對是加速器，用錯是炸彈**。認清限制、做好重啟恢復、控制規模、監控命中率，才能穩穩發揮價值。現代架構下，**Redis + InnoDB Buffer Pool 雙層緩存** 往往是更優解。