8.0 のGA以降、Internal Temporary Table まわりは改良が重ねられきました・・・ ネットの記事では既に古い内容になってしまっているものもあり、改めて最新の挙動を確認して整理してみました*1。
なお、MySQL 8.0 の Internal Temporary Table の動作は @kentarokitagawa さんの以下の記事も詳しいです。 こちらの記事を読んでから本エントリーを読むとより理解しやすいでしょう。
Internal Temporary Table
MySQL は、ORDER BY や UNION を含むクエリを処理するため、内部的に一時的なテーブル（Internal Temporary Table) を作成します。 Internal Temporary Table はクエリの実行が終わると、自動的に削除されます。
Internal Temporary Table のストレージエンジンは、Temp Table エンジン もしくは、InnoDB です。 どちらのストレージエンジンが利用されるかは、後述の条件によって動的に決まります。
TempTable エンジン
TempTable エンジンは MySQL 8.0 で登場した Internal Temporary Table 専用のストレージエンジンです。 Internal Temporary Table を効率よく処理するための工夫がされています。
割り当て可能なメモリがある限りは、メモリ上に領域を確保し、足りない分はファイル（ディスク）上に領域を拡張します。つまり、１つの Internal Temporary Table がメモリとファイル両方にまたがって、確保され得るのです。
メモリ上に確保した領域にファイル上に確保した領域を足さなければなりません。
そのため、ファイルをメモリのように扱うために、mmap を用いて実装されています。賢いですねぇ。
ファイル上に確保する領域の最大サイズは temptable_max_mmap パラメータで設定します。
このように、メモリとファイルを使ったハイブリッドな構成である旨は、公式マニュアルの以下の記述からも読み取ることができます。
Data is never moved between RAM and temporary files, within RAM, or between temporary files. (一度 RAM上に格納したデータは、ファイルには書き出されない)
--
New data is stored in RAM if space becomes available within the limit defined by temptable_max_ram. Otherwise, new data is stored in temporary files. (新たなデータは、
temptable_max_ramに収まっていれば、RAM上に格納される。そうでなければ、ファイルに格納される。)
--
If space becomes available in RAM after some of the data for a table is written to temporary files, it is possible for the remaining table data to be stored in RAM. (いくつかのデータをファイルに書いたのち、RAMに空き領域ができた場合、残りのデータはRAM上に格納される。)
InnoDB
以下で説明する特定の条件に該当した場合、Internal Temporary Table では InnoDB が使われます。 InnoDB が使われると、全てディスク上に確保されるため、パフォーマンス面ではマイナスです。
InnoDB で作られた、Internal Temporary Table はクエリ実行中に #innodb_tempディレクトリにファイルとして見えます。
$ sudo ls -ahl /var/lib/mysql/#innodb_temp total 4.1G drwxr-x---. 2 mysql mysql 187 Apr 19 17:07 . drwxr-x--x. 8 mysql mysql 4.0K Apr 19 17:07 .. -rw-r----- 1 mysql mysql 4.1G Apr 22 12:46 temp_10.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_1.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_2.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_3.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_4.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_5.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_6.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_7.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_8.ibt -rw-r----- 1 mysql mysql 80K Apr 19 17:07 temp_9.ibt
関連するパラメータと各ストレージエンジンが使われる条件
temptable_max_ram
temptable_max_ram は Temp Table エンジンに割り当てるメモリ領域のサイズです。
1つ1つのInternal Temporary Tableのサイズも重要ですが、同時に作成される、Internal Temporary Table の数も重要です。
クライアントが同時にたくさんのクエリを実行すると、複数の Internal Temporary Table が同時に作成され、割り当てた領域を使い切る可能性があります。
temptable_max_ram で割り当てたメモリ領域を使いきると、Temp Tableエンジンは mmap によりファイル（ディスク）上に領域を確保していきます。
temptable_max_mmap
Temp Table エンジンに割り当てる、ファイル上の領域の最大サイズです。
Temp Table エンジンが temptable_max_ram によって割り当てたメモリも使い切り、さらにtemptable_max_mmap で指定したファイル上の領域も使い切ると、InnoDB で処理されるようになります。
この制限は MySQL 8.0.23 で追加されました。8.0.23 まで、ファイル上に確保する領域のサイズに制限はありませんでした。
上記の @kentarokitagawa さんの記事には、このような記述がありますが、現在は temptable_max_mmap パラメータにより制限ができるようになっています。
temptable_max_ramパラメータを超えるとmmapテンポラリファイルを作成します。その際に大きな内部テンポラリテーブルを作成するクエリがあると，大量にメモリを消費する可能性があります。
tmp_table_size
Internal Temporary Table のサイズが、tmp_table_size を超えると、InnoDB で処理されます。デフォルトは 16M です。こちらは、temptable_max_ram や temptable_max_mmap とは異なり、Internal Temporary Table １つ１つのサイズに対する制限です。Temp Table エンジンの領域に空きがあったとしても、tmp_table_size の制限に該当すると、InnoDB に落ちます。
巨大な Internal Temporary Table を作るクエリによって、Temp Table エンジンの領域を食い潰さないようキャップがかけられるようになってます。 この制限は MySQL 8.0.28 で加わりました。
まとめ
MySQL 8.0.28 時点での挙動：
tmp_table_sizeの制限にひっかかると、InnoDB になるtemptable_max_ramの制限を超えると、ファイル上に領域が確保され始める- さらに、
temptable_max_mmapで指定したサイズも超えると、InnoDB になる
パフォーマンスを意識するなら、tmp_table_size や temptable_max_ram を引き上げて、可能な限りメモリ上で処理されるようにすると良いでしょう。
*1:version 8.0.28 時点では、今回記載した挙動ですが、今後のバージョンでまた挙動が変わってるかもしれません