MySQLはステートメントの処理中にInternal Temporary Table
内部テンポラリテーブルは、
内部テンポラリテーブルを必要とするステートメントの処理中の動作としては、
また、
主に以下のような条件で、
- UNIONを使用したクエリ
- ORDER BY句と別のカラムのGROUP BY句を含むクエリ
- 複数テーブルのUPDATEステートメント
- GROUP_
CONCAT() または COUNT(DISTINCT)評価 - ORDER BY句と組み合わせたDISTINCT句のクエリ
- 派生テーブル
(FROM句内のサブクエリ) - サブクエリまたはセミジョインのマテリアライゼーション用に作成されたテーブル
- 同一テーブル間での
INSERT..
ステートメントSELECT - ウィンドウ関数を使用したクエリ
- 共通テーブル式
(CTE) を使用したクエリ
確認方法について
ステートメントが内部テンポラリーテーブルを作成するか確認する方法を紹介します。
はじめに、Using temporary
が表示されると、Using temporary
が表示されませんが、DERIVED
SUBQUERY
とMATERIALIZE
表示にはいろんなパターンがあるので、
また、
EXPLAIN例
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 | +-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
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.
- インメモリテンポラリテーブル …MEMORYストレージエンジン
- ディスクテンポラリテーブル …MyISAMストレージエンジン
これは従来からの仕組みなので、tmp_
とmax_
パラメータのどちらかの最小値になります。この値を超えると、
その操作中は、SHOW PROCESSLIST
のState列にCopying to tmp table on disk
といった表示がされます。遅いクエリがある時に、
MySQL 5.7
MySQL 5.
- インメモリテンポラリテーブル …MEMORYストレージエンジン
- ディスクテンポラリテーブル …MyISAMまたはInnoDBストレージエンジン
ディスクテンポラリテーブルにInnoDBを選択できるようになりました。デフォルトはInnoDBになっています。internal_
パラメータをMyISAMにすると、
筆者の感覚では、
- InnoDBの制限に引っかかり、
クエリがエラーになる。 - InnoDBテンポラリテーブル用のテーブルスペースが肥大化する。
1.はInnoDBページサイズが16KBの場合、Row size too large
エラーや列数の最大値を超えるToo many columns
エラーでクエリが失敗することがあります。
2.は、
これらの回避策はディスクテンポラリテーブルをMyISAMに変更することです。オンラインでの変更も可能なので、SET GLOBAL internal_
を実施します。
MySQL 8.0
MySQL 8.
主に以下のような変更点があります。
- TempTableストレージエンジンの登場
- ディスクテンポラリテーブルのMyISAM廃止
MySQL 8.internal_
とtemptable_
を制御することで、
-
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になっています。
また、
まとめ
今回は