在使用 MySQL 時,若發現狀態顯示 Copying to tmp table on disk,代表查詢處理過程中產生的臨時表超過記憶體限制,被迫寫入磁碟。磁碟 I/O 速度遠慢於記憶體,會嚴重拖慢查詢效能。
MySQL 透過 tmp_table_size 和 max_heap_table_size 兩個參數控制記憶體臨時表的大小上限。實際生效的限制是兩者中的較小值。當臨時表大小超過此限制,MySQL 會自動將其轉換為磁碟上的 MyISAM 表,導致效能驟降。
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SET GLOBAL max_heap_table_size = 512 * 1024 * 1024; -- 設為 512MB
SET GLOBAL tmp_table_size = 512 * 1024 * 1024; -- 同步調整
注意: 設定過大可能導致記憶體耗盡,建議根據伺服器實際記憶體與併發數評估適當值,並持久化至 my.cnf。
調大參數只能緩解症狀,無法根治。以下慢查詢日誌片段揭示了典型問題:
# Query_time: 2266.887211 Rows_examined: 71501657
SELECT a.Pid, count(a.id) as count, p.ProductId, p.ProductName, ...
FROM `Issuer` as i, `Count` as a
LEFT JOIN Product as p ON a.Pid = p.ProductId
WHERE a.Stime > 'time() - 3600*24*30'
AND p.IssuerId = i.IssuerId
AND p.IsDel = 0
GROUP BY a.Pid
ORDER BY count(a.id) DESC
LIMIT 0, 17;
'time() - 3600*24*30' 字串無法利用索引,應使用 DATE_SUB(NOW(), INTERVAL 30 DAY)。INNER JOIN 替代舊式逗號連接,讓優化器更好判斷驅動表。Count(Stime, Pid)、Product(ProductId, IssuerId, IsDel) 建立複合索引,減少回表。GROUP BY 與 ORDER BY 欄位不一致會觸發排序,嘗試調整索引順序或使用子查詢先聚合再排序。遇到 Copying to tmp table on disk 時,先調大臨時表參數應急,但必須結合 EXPLAIN 分析執行計畫,從索引設計、SQL 改寫、架構拆分等層面系統性優化,才能從根本上解決效能瓶頸。