第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回で紹介しました。
- インメモリテンポラリテーブル：TempTable ディスクテンポラリテーブル：TempTable（デフォルト）
- インメモリテンポラリテーブル：ＴempTable ディスクテンポラリテーブル：InnoDB
- インメモリテンポラリテーブル：Memory ディスクテンポラリテーブル：InnoDB
この中で、どの設定が良いのか考えてみたいと思います。
ここからは筆者の個人の意見なので、参考程度に読んでいただければと思います。
筆者の感覚では、基本的にはデフォルトの1. で良いと考えます。
いくつかテストしたところ、ほとんどの内部テンポラリテーブルを使用するクエリは、従来の仕組み（MemoryストレージエンジンやInnoDBストレージエンジンの内部テンポラリテーブル）よりも、TempTableを使用したほうがパフォーマンスが良くなりました。
しかし、MySQL 8.0の早期バージョンでは、クエリによっては従来の仕組みのほうがパフォーマンスが良いなど、複数のバグ報告がありました。
これらのバグは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をご参照ください。
