第129回　Internal Temporary Table（内部テンポラリテーブル）について［その1］ では内部テンポラリーテーブルを使用するステートメントやその確認方法，またMySQLの各バージョンでの内部テンポラリテーブルの違いについて紹介しました。

今回はその続きで，主にMySQL 8.0で追加された内部テンポラリテーブル用のストレージエンジン，TempTableストレージエンジンについて紹介したいと思います。

今回も前回同様MySQL 8.0.21を基にして紹介します。

TempTableストレージエンジン

MySQL 8.0は，デフォルトでインメモリテンポラリテーブルとディスクテンポラリテーブルがTempTableとなっています。

TempTableストレージエンジンの特徴として以下があります。

• 可変長データ型の効率的なストレージの提供
• バイナリラージオブジェクト型のサポート
• グローバルメモリでの管理
• mmapファイルによるオーバーフローメカニズム

それぞれについて簡単に紹介します。

以後，従来のインメモリテンポラリテーブルはMemoryストレージエンジンのインメモリテンポラリテーブルとして，従来のディスクテンポラリテーブルはInnoDBストレージエンジンのディスクテンポラリテーブルとして表します。

可変長データ型の効率的なストレージの提供

まず，従来のインメモリテンポラリテーブルでは，VARCHARやVARBINARY型といった可変長のデータ型であっても，カラムに指定された文字数分の値を格納します。よって，ストレージを無駄に消費してしまう問題がありました。

これはInnoDBストレージエンジンにおけるCHAR型と同等の処理になってしまいます（参考： 第41回　MySQLのCHAR型とVARCHAR型との違いを理解する⁠）⁠。しかし，TempTableでは可変長のデータ型は実際の値のみを格納するため，ストレージを効率的に消費できるようになりました。

バイナリラージオブジェクト型のサポート

次に，従来のインメモリテンポラリテーブルでは，BLOB，TEXTやJSON型といったバイナリラージオブジェクト型をサポートしてませんでした。よって，これらの型がある場合はかならずディスクテンポラリテーブルを使用するようになっていました。TempTableではそれらの型もサポートされ，インメモリテンポラリテーブル上で処理できるようになりました。

グローバルメモリ領域での管理

第129回の記事で説明しましたが，従来のインメモリテンポラリテーブルではスレッドごとにメモリ最大値を設定し，スレッドごとにメモリを確保します。TempTableでは，ストレージエンジンが占有できるメモリの最大値を設定することができ，すべてのスレッドがそれを共有して使用します。

最大値はtemptable_max_ramパラメータから設定可能で，デフォルトは1GBです。全体でこのパラメータの使用量を超えると，従来のディスクテンポラリテーブル，またはmmapファイルをディスク上に確保します。

mmapファイルによるオーバーフローメカニズム

従来のインメモリテンポラリテーブルは，メモリ内に収まらないと，従来のディスクテンポラリテーブルへのコンバートが発生します。その時，インメモリテンポラリテーブルに格納されたデータをすべてディスクテンポラリテーブルにコピーするという処理が発生していました。

それが，temptable_use_mmap=ONであると，TempTableストレージエンジンがmmapテンポラリファイルとしてインメモリテンポラリテーブルのためのスペースを割り当てるため，データコピーなどの処理は発生しません。

temptable_use_mmap=OFFであると，従来のディスクテンポラリテーブルを使用するので，データコピーは発生します。

設定について

MySQL 8.0では内部テンポラリテーブルを以下の3種類から設定できると第129回で紹介しました。

1. インメモリテンポラリテーブル：TempTable ディスクテンポラリテーブル：TempTable（デフォルト）
2. インメモリテンポラリテーブル：ＴempTable ディスクテンポラリテーブル：InnoDB
3. インメモリテンポラリテーブル：Memory ディスクテンポラリテーブル：InnoDB

この中で，どの設定が良いのか考えてみたいと思います。

ここからは筆者の個人の意見なので，参考程度に読んでいただければと思います。

筆者の感覚では，基本的にはデフォルトの1. で良いと考えます。

いくつかテストしたところ，ほとんどの内部テンポラリテーブルを使用するクエリは，従来の仕組み（MemoryストレージエンジンやInnoDBストレージエンジンの内部テンポラリテーブル）よりも，TempTableを使用したほうがパフォーマンスが良くなりました。

しかし，MySQL 8.0の早期バージョンでは，クエリによっては従来の仕組みのほうがパフォーマンスが良いなど，複数のバグ報告がありました。

• Bug #97920 Aggregation function [sum()] return random numbers
• Bug #98739 TempTable storage engine slow if mmap is required
• Bug #98782 Using TempTable engine for GROUP BY is slower than using MEMORY engine
• Bug #99136 TempTable wastes 1MB for each connection in thread cache

これらのバグはMySQL 8.0.21で改修されたものが多いため，MySQL 8.0.20以下での場合は安定性を考えて3を選択しても良いと思います。TempTableを導入する際は，MySQL 8.0.21以降のバージョンであることが望ましいと思います。

また，2．について設定を考慮する場合もあります。

それは1．の場合，temptable_max_ramパラメータを超えるとmmapテンポラリファイルを作成します。その際に大きな内部テンポラリテーブルを作成するクエリがあると，大量にメモリを消費する可能性があります。そのようなクエリが多く，およびメモリ使用量で問題なるケースでは2．を選択することもあると考えます。

まとめ

TempTableストレージエンジンにより，より効率的にクエリを処理できるようになりました。内部テンポラリテーブルの設定もさまざまありますので，各設定でベンチマークをとってみたり，クエリ速度を比較してみると良いと思います。

内部テンポラリテーブルについて，詳しく知りたい方は，マニュアルの8.4.4 Internal Temporary Table Use in MySQLをご参照ください。