MySQLはステートメントの処理中にInternal Temporary Table(以降、内部テンポラリテーブル)を作成することがあります。
内部テンポラリテーブルは、ステートメントを処理するための最適化や結果を保持しておくために用いられる内部的に作成されるテーブルです。よって、ユーザーがこれを直接制御することはできません。内部テンポラリテーブルを使用するステートメントを実行すると内部で自動的に作成され、それが正常終了またはキャンセルされると自動的に削除されます。
内部テンポラリテーブルを必要とするステートメントの処理中の動作としては、まずメモリ内でテーブル(インメモリテンポラリテーブル)を作成します。それが大きくなりメモリ内に収まらないと自動的にディスク上のテーブル(ディスクテンポラリテーブル)に変換します。
また、以前の記事(第107回 CREATE TEMPORARY TABLEによる一時テーブルの利用)で紹介したテーブルはユーザーが制御できるテンポラリテーブルであり、今回の内部テンポラリテーブルとは異なるものであるのでご注意ください。
主に以下のような条件で、MySQLは内部テンポラリテーブルを作成する可能性があります。
- UNIONを使用したクエリ
- ORDER BY句と別のカラムのGROUP BY句を含むクエリ
- 複数テーブルのUPDATEステートメント
- GROUP_CONCAT() または COUNT(DISTINCT)評価
- ORDER BY句と組み合わせたDISTINCT句のクエリ
- 派生テーブル(FROM句内のサブクエリ)
- サブクエリまたはセミジョインのマテリアライゼーション用に作成されたテーブル
- 同一テーブル間での
INSERT..SELECT
ステートメント
- ウィンドウ関数を使用したクエリ
- 共通テーブル式(CTE)を使用したクエリ
確認方法について
ステートメントが内部テンポラリーテーブルを作成するか確認する方法を紹介します。
はじめに、EXPLAINを実施して、Extra列を確認します。そこにUsing temporary
が表示されると、そのステートメントは内部テンポラリテーブルを作成します。また、Using temporary
が表示されませんが、select_type列のDERIVED
(上記の6に該当)やSUBQUERY
とMATERIALIZE
(上記の7に該当)も内部テンポラリテーブルが作成されます。
表示にはいろんなパターンがあるので、EXPLAINから正確な情報を拾うのは難しいですが、一般的に上記のように考えておけば良いと思います。
また、ステータス情報からも内部テンポラリテーブルが作成された回数などを確認できます。こちらは次回紹介したいと思います。
EXPLAIN例
ストレージエンジンについて
内部テンポラリテーブルに使用されるストレージエンジンはMySQLのバージョンによって異なります。各バージョンごとにインメモリテンポラリテーブルとディスクテンポラリテーブルでどのストレージエンジンが使用されるか紹介します。
MySQL 5.6とそれ以前
MySQL 5.6とそれ以前では以下のようになります。
- インメモリテンポラリテーブル …MEMORYストレージエンジン
- ディスクテンポラリテーブル …MyISAMストレージエンジン
これは従来からの仕組みなので、ご存知の方も多いと思います。インメモリテンポラリテーブルの最大値は、tmp_table_size
とmax_heap_table_size
パラメータのどちらかの最小値になります。この値を超えると、ディスクテンポラリテーブルのMyISAMに変換されます。
その操作中は、SHOW PROCESSLIST
のState列にCopying to tmp table on disk
といった表示がされます。遅いクエリがある時に、この表示を見たことがある方は多いと思います。
MySQL 5.7
MySQL 5.7では以下のようになります。
- インメモリテンポラリテーブル …MEMORYストレージエンジン
- ディスクテンポラリテーブル …MyISAMまたはInnoDBストレージエンジン
ディスクテンポラリテーブルにInnoDBを選択できるようになりました。デフォルトはInnoDBになっています。internal_tmp_disk_storage_engine
パラメータをMyISAMにすると、ディスクテンポラリテーブルをMyISAMに変更できます。
筆者の感覚では、ディスクテンポラリテーブルはInnoDBのほうがパフォーマンスは良いことが多かったです。しかし、いくつか注意点があり、その際はMyISAMに変更することをおすすめします。
- InnoDBの制限に引っかかり、クエリがエラーになる。
- InnoDBテンポラリテーブル用のテーブルスペースが肥大化する。
1.はInnoDBページサイズが16KBの場合、最大行サイズは約8000バイトとなり、その制限を超えるためRow size too large
エラーや列数の最大値を超えるToo many columns
エラーでクエリが失敗することがあります。
2.は、yoku0825さんのブログが参考になりますので、"MySQL 5.7.6以降では暗黙のテンポラリーテーブルがあふれると死ぬ"をご確認ください。
これらの回避策はディスクテンポラリテーブルをMyISAMに変更することです。オンラインでの変更も可能なので、緊急時は SET GLOBAL internal_tmp_disk_storage_engine=MyISAM
を実施します。
MySQL 8.0
MySQL 8.0からは内部テンポラリテーブルについて、大きな変更がありました。MySQL 8.0のマイナーバージョン間でも仕様が変わったりしていますが、今回は2020年8月現在で最新のMySQL 8.0.21を基にして紹介します。
主に以下のような変更点があります。
- TempTableストレージエンジンの登場
- ディスクテンポラリテーブルのMyISAM廃止
MySQL 8.0はパラメータ internal_tmp_mem_storage_engine
とtemptable_use_mmap
を制御することで、どのストレージエンジンを使用するか以下の3種類から設定ができます。
-
internal_tmp_mem_storage_engine=TempTableとtemptable_use_mmap=ON
- インメモリテンポラリテーブル …TempTable
- ディスクテンポラリテーブル …TempTable
-
internal_tmp_mem_storage_engine=TempTableとtemptable_use_mmap=OFF
- インメモリテンポラリテーブル …TempTable
- ディスクテンポラリテーブル …InnoDB
-
internal_tmp_mem_storage_engine=MEMORY
- インメモリテンポラリテーブル …MEMORY
- ディスクテンポラリテーブル …InnoDB
デフォルトはインメモリテンポラリテーブルとディスクテンポラリテーブル共にTempTableストレージエンジンを使用する設定の1になっています。
また、MySQL 8.0のディスクテンポラリテーブルのInnoDBですが、MySQL 5.7での注意点が解決されているものがあります。こちらもyoku0825さんのブログ"MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない" が参考になりますのでご参照ください。
まとめ
今回は「Internal Temporary Table(内部テンポラリテーブル)について[その1]」として、内部テンポラリテーブルのMySQLバージョン間での違いなど説明しました。次はTempTableストレージエンジンについてや監視項目についてなど紹介したいと思います。