MySQL道普請便り

第132回 Internal Temporary Table(内部テンポラリテーブル)について[その2]

この記事を読むのに必要な時間:およそ 2 分

第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. インメモリテンポラリテーブル:TempTable ディスクテンポラリテーブル:InnoDB
  3. インメモリテンポラリテーブル: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をご参照ください。

著者プロフィール

北川健太郎(きたがわけんたろう)

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで,好きなOracleDatabaseの機能はログオントリガー。

Twitter:@keny_lala