避免 MySQL 使用 temporary table on disk

How MySQL Uses Internal Temporary Tables 解釋的滿清楚的, 使用 explain 可以注意 extra 欄位有無「Using temporary」, 有的話 MySQL 會用 temporary table。temporary table 可能是存在記憶體裡的 MEMORY engine, 或是存在硬碟上的 MyISAM engine。執行 SQL 時可以用「show processlist」看 state, 若有出現「Copying to tmp table on disk」就中獎了, 速度會變很慢。

除不能用 TEXT、BLOB、單一欄位不超過 512 bytes 等注意事項外, 還有兩個參數會影響到是否使用硬碟存 temporary table:

• max_heap_table_size: in-memory table 的上限 (即 MEMORY engine 的上限)。
• tmp_table_size: in-memory temporary table 的上限 (即用 MEMORY engine 當 temporary table 的上限)。

若 temporary table 需要大於 min(max_heap_table_size, tmp_table_size), 就會用硬碟存。

題外話, 大部份 MySQL 的疑問都能很快地從官網文件找到答案, MySQL 官方文件真不錯啊。《High Performance MySQL 2e》也照順序整理了不少有用資訊, 對照兩者學了不少東西。