MySQL の tmp_table_size と max_heap_table_size を調整する

GROUP BY、DISTINCT、サブクエリなどを使うと、MySQL は処理の途中で一時テーブルを作成することがあります。この一時テーブルがメモリに収まるか、ディスクに書き出されるかで、パフォーマンスは大きく変わります。

一時テーブルが作られる場面

以下のようなクエリで一時テーブルが使われることがあります。

GROUP BY でインデックスが使えない場合

ORDER BY と GROUP BY で異なるカラムを使う場合

DISTINCT を使う場合

UNION（UNION ALL 除く）を使う場合

一部のサブクエリ

EXPLAIN の Extra 列に Using temporary と表示されていれば、一時テーブルが作成されています。

メモリ一時テーブル vs ディスク一時テーブル

一時テーブルはまずメモリ上に作成されますが、サイズが上限を超えるとディスクに変換されます。

メモリ一時テーブル

MEMORY エンジンを使用。高速だが、BLOB/TEXT は格納不可。

ディスク一時テーブル

InnoDB または MyISAM に変換。ディスク I/O が発生して遅い。

ディスク一時テーブルの発生状況を確認

以下のステータス変数で、一時テーブルの作成状況を確認できます。

SHOW STATUS LIKE 'Created_tmp%tables';

変数	意味
Created_tmp_tables	作成された一時テーブルの総数
Created_tmp_disk_tables	ディスクに作成された一時テーブル数

ディスク一時テーブルの割合が高い場合、パラメータの引き上げを検討します。

$ディスク比率 = \frac{Created_tmp_disk_tables}{Created_tmp_tables} \times 100$

パラメータの調整

tmp_table_size と max_heap_table_size を引き上げる場合は、両方を同じ値に設定します。

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

動的に変更する場合は以下のようにします。

SET GLOBAL tmp_table_size = 67108864;  -- 64MB
SET GLOBAL max_heap_table_size = 67108864;

注意点

単純にサイズを大きくすれば良いというわけではありません。

接続ごとに確保される可能性

一時テーブルは接続ごとに作成されるため、多数の接続が同時に大きな一時テーブルを作ると、メモリを大量消費します。

BLOB/TEXT を含むと即ディスク

一時テーブルに BLOB や TEXT 型のカラムが含まれると、サイズに関係なくディスクに作成されます。

根本的な解決にはならない

一時テーブルが作られる原因（インデックス不足など）を解決するほうが効果的です。

クエリの改善が優先

パラメータ調整の前に、そもそも一時テーブルを作らないようにクエリを改善できないか検討しましょう。

GROUP BY / ORDER BY にインデックスを活用

SELECT で BLOB/TEXT を避ける

DISTINCT の代わりに適切な JOIN を使う

サブクエリを JOIN に書き換える

たとえば、GROUP BY するカラムにインデックスがあれば、一時テーブルなしでグルーピングできる場合があります。

MySQL 8.0 での変更点

MySQL 8.0 では、内部一時テーブルのストレージエンジンが TempTable に変更されました。

SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';
-- TempTable (MySQL 8.0のデフォルト)

TempTable エンジンは可変長データ型をより効率的に扱え、オーバーフロー時にはまず mmap ファイルを使用します。

関連パラメータも追加されています。

SHOW VARIABLES LIKE 'temptable_max_ram';
SHOW VARIABLES LIKE 'temptable_max_mmap';

監視のポイント

継続的に以下を監視しましょう。

Created_tmp_disk_tables の増加傾向を確認

ディスク比率が高いクエリを特定

EXPLAIN で Using temporary を確認

インデックス追加またはクエリ改善

一時テーブルは必ずしも悪ではありませんが、ディスクへの変換が頻発するなら対処が必要です。まずはクエリの最適化を優先し、それでも改善しない場合にパラメータ調整を検討してください。

tmp_table_size	一時テーブルの最大サイズ
max_heap_table_size	MEMORY テーブルの最大サイズ