MySQLのTemporary table

現場のSQLの中に、CREATE TEMPORARY という文をいくつか見かけたので、「一時テーブル」について勉強してみました。

Temporary Tableはセッションを抜けると自動的にDropされる。
異なるセッションのTemporary Tableは別々のものと扱われる。

Temporary Table(一時テーブル)について - 十番目のムーサ
*1

テンポラリテーブルはそのサイズがtmp_table_size以下であればMEMORYテーブルとしてメモリ上に作成され、tmp_table_sizeを超えるとISAMテーブルとしてディスクに書き出されます。

MySQLの「temporary table (一時テーブル)」 と「tmp file(テンポラリファイル)」の違いと「Copying to tmp table」と「copy to tmp table」の違い | 田舎に住みたいエンジニアの日記
*2

MEMORYテーブルの大きさはmax_heap_table_sizeによっても制約されるため、tmp_table_sizeを大きくする場合は同時にmax_heap_table_sizeも大きくします。

MySQLの「temporary table (一時テーブル)」 と「tmp file(テンポラリファイル)」の違いと「Copying to tmp table」と「copy to tmp table」の違い | 田舎に住みたいエンジニアの日記
*3

クエリが最適化されていない場合は、大きなテンポラリテーブルが作られてしまいます。テンポラリテーブルがtmp_table_sizeやmax_heap_table_sizeに割当のサイズを超える場合は、「Created_tmp_tables」のメモリ上で収まらずに「Created_tmp_disk_tables」としてディスクに書かれてしまいます。

MySQLの「temporary table (一時テーブル)」 と「tmp file(テンポラリファイル)」の違いと「Copying to tmp table」と「copy to tmp table」の違い | 田舎に住みたいエンジニアの日記
*4

CREATE TEMPORARY TABLE tmp1
SELECT * FROM test_dt
WHERE item_name like '%検索文字列%'

テンポラリテーブルの作成MySQL｜プログラムメモ
*5

*1:他のセッションでも利用できなくていいくらい、本当に一時的な結果を保持するテーブルなんですね。集計の途中結果、アドホックな軸での集計、などを格納するのでしょうね。

*2:このディスク書き出しがかなり遅い、とも書いてありました。まあメモリサイズは有限ですから、このしくみが悪いという訳ではないです、ただチューニングは必要なんですね。実際、どれくらい遅くなるんだろう？

*3:なるほど

*4:ちなみにCreated...関係は、SHOW STATUS;で取得可能とのことでした。

*5:CREATEするのにselect文がダイレクトに使えるってことですね。