MySQL道普請便り
第129回 Internal Temporary Table(内部テンポラリテーブル)について[その1]
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例
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 | +-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
派生テーブル
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になっています。
また,
まとめ
今回は
バックナンバー
MySQL道普請便り
- 第138回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その1]
- 第137回 MySQLTunerを使ってチューニングの足がかりを見つけてみる
- 第136回 CHECK制約を利用してみよう
- 第135回 MySQL 8.0で追加されたoptimizer_switchのフラグについて
- 第134回 DDLと暗黙的なコミットについて
- 第133回 Partial Revokesによる権限の一部剥奪について
- 第132回 Internal Temporary Table(内部テンポラリテーブル)について[その2]
- 第131回 mysqldumpslowを使ってスロークエリログを解析してみる
- 第130回 クエリをプロファイリングしてみる
- 第129回 Internal Temporary Table(内部テンポラリテーブル)について[その1]
関連記事
- 第46回 MySQL Cluster 8.0.16 DMRリリースと開発者来日セミナー開催,PostgreSQLエンタープライズ・コンソーシアムが検証成果を公開
- 第41回 MySQL& PostgreSQLの2018年の主なニュース
- 第38回 LOCONDO.jpやLINEでのMySQL最新導入事例,PostgreSQL 11ベータ4リリース,Cassandra(イベント情報のみ)
- 第37回 MySQL Server 8.0.12リリース,PostgreSQL 11ベータ3リリース
- 第36回 MySQL 8.0へのバージョンアップ方法,PostgreSQL 11ベータ2リリース,MongoDB 4.0 リリース