MySQL 數十萬筆資料 GROUP BY 慢查詢優化：調大 tmp_table_size 即見效

資料庫調優是系統維運中極具挑戰性的工作。本文透過一個實際案例，展示如何單純調整 tmp_table_size 參數，將一個包含數十萬列的 GROUP BY 查詢從 20-30 秒降至毫秒級。

問題現場

部落格系統 pLog 0.3x，資料表 plog_articles 有數十萬筆資料。最常見的慢查詢：

SELECT category_id, COUNT(*) AS 'count'
FROM plog_articles
WHERE blog_id = 2 AND status = 'PUBLISHED'
GROUP BY category_id;

執行時間普遍超過 20 秒，甚至 30 秒。但 blog_id=1 卻極快。

索引無效？EXPLAIN 揭露真相

重建索引無效。EXPLAIN 顯示：

| table         | type | possible_keys | key             | key_len | ref       | rows | Extra                              |
| plog_articles | ref  | idx_article_blog | idx_article_blog | 5       | const,const | 4064 | Using where; Using temporary; Using filesort |

關鍵在於 Using temporary; Using filesort —— MySQL 必須建立臨時表並進行檔案排序。

根因：記憶體臨時表不足，轉寫入磁碟

觀察發現查詢 blog_id=2 時系統負載飆高、Swap 活躍。查閱文件發現：

If you create a lot of disk-based temporary tables, increase the size of tmp_table_size... Otherwise, make sure that tmpdir points to a very fast disk...

EXPLAIN 的 Using temporary 不會告訴你臨時表是在記憶體還是磁碟。當資料量超過 tmp_table_size（預設僅 16MB），MySQL 就會將臨時表轉為 MyISAM 磁碟表，導致大量 I/O。

解決方案：調大 tmp_table_size

在 my.cnf 的 [mysqld] 區段加入：

tmp_table_size = 64M
max_heap_table_size = 64M  # 必須同步調整，否則受限於較小者

重啟 MySQL 後，同一查詢瞬間完成，效能「突飛猛進」。

補充建議

• 若記憶體有限，勿盲目設過大，避免多執行緒同時配置導致 OOM
• 若無法加大記憶體，將 tmpdir 指向高速 SSD 或專用磁碟
• MySQL 5.7+ 可考慮使用 internal_tmp_mem_storage_engine=TempTable（預設）並調整 temptable_max_ram

結語

MySQL 缺乏 Materialized View、Stored Procedure 等企業級功能，參數調校往往是唯一且最高效的優化手段。臨時表寫入磁碟是 GROUP BY / ORDER BY / DISTINCT 慢查詢的常見隱形殺手，調大 tmp_table_size 與 max_heap_table_size 往往能立竿見影。