H2 數據庫 8000 萬數據大表刪除優化：分批提交與索引策略

場景痛點

H2 數據庫單表 8000 萬行，需刪除 `name LIKE 'abc%'` 的小部分數據。普通 `DELETE` 執行極慢、頻繁 OOM（OutOfMemory）。

優化方案組合

1. 分批刪除（核心）

-- 每次刪 5000 行，提交事務，釋放鎖與日誌空間
WHILE (1=1) {
    DELETE TOP (5000) FROM big_table WHERE name LIKE 'abc%';
    IF @@ROWCOUNT = 0 BREAK;
    COMMIT; -- 或自動提交模式下無需顯式 COMMIT
    WAITFOR DELAY '00:00:00.1'; -- 降低鎖衝突
}

H2 支持 `LIMIT`：`DELETE FROM big_table WHERE name LIKE 'abc%' LIMIT 5000;` 循環執行直到影響行數為 0。

2. 索引加速定位

確保 `name` 列有索引：`CREATE INDEX idx_name ON big_table(name);` 無索引全表掃描 8000 萬行極其緩慢。

3. 避免長事務

關閉自動提交，手動控制批次提交，防止事務日誌膨脹導致內存耗盡。

4. 替代方案：新表插入保留數據

CREATE TABLE new_table AS SELECT * FROM big_table WHERE name NOT LIKE 'abc%';
DROP TABLE big_table;
ALTER TABLE new_table RENAME TO big_table;
-- 重建索引、約束

若保留數據佔比小（如 <10%），此法比逐行刪除快數量級，但需鎖表時間較長。

5. H2 專用設置

• 增大緩存：`SET CACHE_SIZE 65536;`（64MB，視內存調整）。
• 使用 `TRUNCATE TABLE` 清空整表（不適用條件刪除）。
• 啟用 `MVCC`（多版本併發控制）減少鎖爭用：`SET MVCC TRUE;`

驗證腳本

-- 測試分批刪除耗時
SET @start = CURRENT_TIMESTAMP;
WHILE (1=1) {
    DELETE FROM big_table WHERE name LIKE 'abc%' LIMIT 10000;
    IF ROW_COUNT() = 0 BREAK;
}
SELECT TIMESTAMPDIFF(SECOND, @start, CURRENT_TIMESTAMP) AS elapsed_seconds;