gihyo.jpサイトのロゴ

MySQL道普請便り

第129回Internal Temporary Table（内部テンポラリテーブル）について［その1］

2020-09-01

MySQLはステートメントの処理中にInternal Temporary Table（以降、内部テンポラリテーブル）を作成することがあります。

内部テンポラリテーブルは、ステートメントを処理するための最適化や結果を保持しておくために用いられる内部的に作成されるテーブルです。よって、ユーザーがこれを直接制御することはできません。内部テンポラリテーブルを使用するステートメントを実行すると内部で自動的に作成され、それが正常終了またはキャンセルされると自動的に削除されます。

内部テンポラリテーブルを必要とするステートメントの処理中の動作としては、まずメモリ内でテーブル（インメモリテンポラリテーブル）を作成します。それが大きくなりメモリ内に収まらないと自動的にディスク上のテーブル（ディスクテンポラリテーブル）に変換します。

また、以前の記事（第107回　CREATE TEMPORARY TABLEによる一時テーブルの利用）で紹介したテーブルはユーザーが制御できるテンポラリテーブルであり、今回の内部テンポラリテーブルとは異なるものであるのでご注意ください。

主に以下のような条件で、MySQLは内部テンポラリテーブルを作成する可能性があります。

  1. UNIONを使用したクエリ
  2. ORDER BY句と別のカラムのGROUP BY句を含むクエリ
  3. 複数テーブルのUPDATEステートメント
  4. GROUP_CONCAT() または COUNT(DISTINCT)評価
  5. ORDER BY句と組み合わせたDISTINCT句のクエリ
  6. 派生テーブル（FROM句内のサブクエリ）
  7. サブクエリまたはセミジョインのマテリアライゼーション用に作成されたテーブル
  8. 同一テーブル間でのINSERT..SELECTステートメント
  9. ウィンドウ関数を使用したクエリ
  10. 共通テーブル式（CTE）を使用したクエリ

確認方法について

ステートメントが内部テンポラリーテーブルを作成するか確認する方法を紹介します。

はじめに、EXPLAINを実施して、Extra列を確認します。そこにUsing temporaryが表示されると、そのステートメントは内部テンポラリテーブルを作成します。また、Using temporaryが表示されませんが、select_type列のDERIVED（上記の6に該当）やSUBQUERYとMATERIALIZE（上記の7に該当）も内部テンポラリテーブルが作成されます。

表示にはいろんなパターンがあるので、EXPLAINから正確な情報を拾うのは難しいですが、一般的に上記のように考えておけば良いと思います。

また、ステータス情報からも内部テンポラリテーブルが作成された回数などを確認できます。こちらは次回紹介したいと思います。

EXPLAIN例

UNIONを使用したクエリ
mysql> EXPLAIN SELECT id FROM t0 UNION SELECT id FROM t1;
+-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
| id  | select_type  | table      | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra           |
+-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
|  1  | PRIMARY      | t0         | NULL       | index | NULL          | id     | 5       | NULL | 491096 |   100.00 | Using index     |
|  2  | UNION        | t1         | NULL       | index | NULL          | id     | 5       | NULL | 491096 |   100.00 | Using index     |
| NULL| UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |   NULL |     NULL | Using temporary |
+-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
派生テーブル（FROM句内のサブクエリ）
mysql> EXPLAIN SELECT t0.id FROM t0 JOIN (SELECT distinct id FROM t1) t1 USING(id);
+----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key    | key_len | ref       | rows  | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL      | 80597 |   100.00 | NULL        |
|  1 | PRIMARY     | t0         | NULL       | ref   | id            | id     | 8       | t1.id     |    15 |   100.00 | NULL        |
|  2 | DERIVED     | t1         | NULL       | index | id            | id     | 8       | NULL      | 80597 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+
セミジョインのマテリアライゼーション
mysql> EXPLAIN t0.id FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.id=t0.id);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref   | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE       | t0          | NULL       | ALL    | id                  | NULL                | NULL    | NULL  | 491096 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 8       | t0.id |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t1          | NULL       | index  | id                  | id                  | 8       | NULL  |  80597 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+

ストレージエンジンについて

内部テンポラリテーブルに使用されるストレージエンジンは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に変更することをおすすめします。

  1. InnoDBの制限に引っかかり、クエリがエラーになる。
  2. 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種類から設定ができます。

  1. internal_tmp_mem_storage_engine=TempTableとtemptable_use_mmap=ON
    1. インメモリテンポラリテーブル …TempTable
    2. ディスクテンポラリテーブル …TempTable
  2. internal_tmp_mem_storage_engine=TempTableとtemptable_use_mmap=OFF
    1. インメモリテンポラリテーブル …TempTable
    2. ディスクテンポラリテーブル …InnoDB
  3. internal_tmp_mem_storage_engine=MEMORY
    1. インメモリテンポラリテーブル …MEMORY
    2. ディスクテンポラリテーブル …InnoDB

デフォルトはインメモリテンポラリテーブルとディスクテンポラリテーブル共にTempTableストレージエンジンを使用する設定の1になっています。

また、MySQL 8.0のディスクテンポラリテーブルのInnoDBですが、MySQL 5.7での注意点が解決されているものがあります。こちらもyoku0825さんのブログ"MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない" が参考になりますのでご参照ください。

まとめ

今回は「Internal Temporary Table（内部テンポラリテーブル）について［その1⁠］⁠」として、内部テンポラリテーブルのMySQLバージョン間での違いなど説明しました。次はTempTableストレージエンジンについてや監視項目についてなど紹介したいと思います。

おすすめ記事

記事・ニュース一覧