錯誤現象

Out of memory (Needed 1046596 bytes)

本質：內部臨時表/堆表超過記憶體限制，MySQL 嘗試分配失敗。

核心參數

參數	預設	作用	關係
tmp_table_size	16MB	內部內存臨時表最大值，超過轉 MyISAM 磁碟表	實際上限 = MIN(tmp_table_size, max_heap_table_size)
max_heap_table_size	16MB	用戶建立 MEMORY 表最大值	同上
query_cache_limit	1MB	單查詢結果緩存上限	獨立

調優實戰記錄

調整前

SHOW VARIABLES LIKE 'tmp_table_size';        -- 16777216 (16MB)
SHOW VARIABLES LIKE 'max_heap_table_size';   -- 16777216 (16MB)

修改 my.cnf

[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_limit = 32M
max_allowed_packet = 1G
key_buffer_size = 768M
sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 512M

調整後驗證

SHOW VARIABLES LIKE 'tmp_table_size';        -- 268435456 (256MB)
SHOW VARIABLES LIKE 'max_heap_table_size';   -- 268435456 (256MB)

根因深度分析

1. 存儲過程大量預處理語句：結果集大，臨時表超 16MB
2. GROUP BY/ORDER BY/DISTINCT/UNION 觸發內部臨時表
3. VARCHAR/TEXT/BLOB 列導致臨時表無法用 MEMORY 引擎，強制 MyISAM 磁碟表，但轉換前仍需記憶體緩衝

系統級內存預算檢查（必做）

-- 粗略估算總內存佔用
innodb_buffer_pool_size
+ key_buffer_size
+ (read_buffer_size + sort_buffer_size + join_buffer_size) * max_connections
+ max_heap_table_size (tmp_table_size 取較大者)
+ query_cache_size
+ 系統預留 (1-2GB)
< 物理內存 * 80%

盲目調大 tmp_table_size 可能導致 OOM Killer 殺死 mysqld 進程。務必按上述公式核算。

SQL 層面優化（治本）

• 避免 SELECT *，只取需要列，減少臨時表行寬度
• 優化 GROUP BY/ORDER BY 利用索引避免臨時表
• 大結果集分頁改用 WHERE id > last_id LIMIT N 避免 OFFSET
• 適當增加 join_buffer_size 配合 Block Nested Loop / Batched Key Access

監控指標

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_disk_tables / Created_tmp_tables > 10% 表明臨時表頻繁落盤，需調優