MySQL内部一時テーブルの具体的な使用法

連合

UNIONセマンティクス: 2つのサブクエリの結果を結合し、重複行を1行だけ保持します。

テーブルの初期化

テーブル t1(id INT 主キー、a INT、b INT、インデックス(a)) を作成します。
区切り文字 ;;
CREATE PROCEDURE idata()
始める
    i INT を宣言します。

    i=1 を設定します。
    (i<= 1000) の場合
        t1 VALUES (i,i,i) に挿入します。
        i=i+1 を設定します。
    終了しながら;
終わり;;
区切り文字 ;
idata() を呼び出します。

ステートメントの実行

(SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);

mysql> EXPLAIN (SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+-------+---------+----------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+-------+---------+----------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | テーブルは使用されていません |
| 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | 後方インデックススキャン。インデックスを使用 |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | 一時を使用 |
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+-------+---------+----------------------------------+

2行目のKey=PRIMARYはUsing temporary

サブクエリの結果に対してUNION RESULT実行するときに一時テーブルが使用されることを示します。

連合の結果

一時メモリテーブルを作成します。この一時メモリテーブルには整数フィールド f が 1 つだけあり、f が主キーです。
最初のサブクエリを実行し、1000を取得してメモリ内の一時テーブルに格納します。
2番目のサブクエリを実行する
- id=1000 の最初の行を取得し、それを一時的なインメモリテーブルに挿入しようとしますが、値 1000 は一時的なインメモリテーブルに既に存在します。
  - 一意制約に違反したため、挿入に失敗しました。実行を続行します。
- 2行目のid=999を取得し、一時メモリテーブルに正常に挿入します。
メモリ内の一時テーブルから行ごとにデータを取得し、結果を返して、メモリ内の一時テーブルを削除します。結果には、id=1000 と id=999 の 2 つの行が含まれます。
一時メモリテーブルは、データを一時的に保存する役割を果たします。また、一時メモリテーブルの主キー ID の一意性制約を使用して、UNION のセマンティクスを実装します。

ユニオンオール

UNION ALLは重複排除セマンティクスがありません。サブクエリは 1 回実行され、メモリ内に一時テーブルを必要とせずに結果がクライアントに直接送信されます。

mysql> EXPLAIN (SELECT 1000 AS f) UNION ALL (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+-------------+--------+-----------+---------+-------+---------------+---------+-------+-------+--------+---------+----------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+-------+---------------+---------+-------+-------+--------+---------+----------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | テーブルは使用されていません |
| 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | 後方インデックススキャン。インデックスを使用 |
+----+-------------+--------+-----------+---------+-------+---------------+---------+-------+-------+--------+---------+----------------------------------+

グループ化

十分なメモリ

-- 16777216 バイト = 16 MB
mysql> '%tmp_table_size%' のような変数を表示します。
+----------------+----------+
| 変数名 | 値 |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

ステートメントの実行

-- MySQL 5.6 で実行mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-------------+-------+-------+-------+---------------+-------+------+------+----------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+----------------------------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | インデックスを使用; 一時を使用; ファイルソートを使用 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+----------------------------------------------+

mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+------+-----+
| m | c |
+------+-----+
| 0 | 100 |
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
+------+-----+

Using index : カバーリングインデックスが使用され、インデックス a が選択され、テーブルを返す必要がないことを示します。

一時テーブルの使用: Using temporaryことを示します

Using filesort : ソートが必要であることを示します

実行プロセス

2つのフィールドmとcを持つ一時メモリテーブルを作成します。mは主キーです。
t1のインデックスaをスキャンし、リーフノード上のid値を1つずつ取り出し、id%10を計算し、xとして記録します。
- 一時メモリテーブルに主キーxを持つ行がない場合、行(x,1)を挿入します。
- 一時メモリテーブルに主キー x を持つ行がある場合は、行 x の c 値に 1 を加算します。
トラバーサルが完了したら、フィールド m に従ってソートし、結果セットを取得してクライアントに返します。

選別プロセス

NULL による順序

-- 最終的なソート段階をスキップし、一時テーブルから直接データを取得します。mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+----+-------------+-------+-------+---------------+-------+-------+------+------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------------------------+
| 1 | SIMPLE | t1 | インデックス | PRIMARY,a | a | 5 | NULL | 1000 | インデックスを使用; 一時を使用 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------------------------+

-- t1 のデータは 1 から始まります。mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+------+-----+
| m | c |
+------+-----+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 0 | 100 |
+------+-----+

メモリ不足

tmp_table_size を 1024 に設定します。

ステートメントの実行

-- メモリ一時テーブルの上限は 1024 バイトですが、メモリ一時テーブルは 100 行のデータを完全に保持することはできません。メモリ一時テーブルはディスク一時テーブルに変換され、InnoDB エンジンがデフォルトで使用されます。 -- t1 が非常に大きい場合、このクエリに必要なディスク一時テーブルは大量のディスク領域を占有します。 mysql> SELECT id%100 AS m, count(*) AS c FROM t1 GROUP BY m ORDER BY NULL LIMIT 10;
+------+----+
| m | c |
+------+----+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
| 6 | 10 |
| 7 | 10 |
| 8 | 10 |
| 9 | 10 |
| 10 | 10 |
+------+----+

最適化計画

インデックスを最適化する

一時テーブルがメモリ内で使用されるかディスク上で使用されるかに関係なく、 GROUP BY一意のインデックスを持つテーブルを構築する必要があり、実行コストが高くなります。

一時テーブルが必要な理由: 各行のid%100は順序が正しくないため、結果を記録してカウントするには一時テーブルが必要です。

入力データが順序どおりであることを保証できる場合は、 GROUP BYを計算するときに、左から右にスキャンして 1 つずつ蓄積するだけで済みます。

最初の1に遭遇すると、X個の0が蓄積され、結果セットの最初の行は(0,X)
最初の2つに遭遇すると、Y個の1が蓄積され、結果セットの最初の行は(1,Y)
プロセス全体で一時テーブルやソートは必要ありません。

-- MySQL 5.7 で ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z) を実行します。
-- カバーリング インデックスが使用され、一時テーブルやソートは必要ありません。mysql> EXPLAIN SELECT z, COUNT(*) AS c FROM t1 GROUP BY z;
+----+-------------+--------+-----------+---------+---------------+-------+--------+----------+-----------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+-------+--------+----------+-----------+-------------+
| 1 | SIMPLE | t1 | NULL | インデックス | z | z | 5 | NULL | 1000 | 100.00 | インデックスを使用 |
+----+-------------+--------+-----------+---------+---------------+-------+--------+----------+-----------+-------------+
2

直接ソート

GROUP BYステートメントで大量のデータを一時テーブルに格納する必要がある場合は、まずメモリ内の一時テーブルに格納し、その後ディスク上の一時テーブルに縮退する方が適切です。

一時ディスクテーブルを直接使用し、 GROUP BYステートメントでSQL_BIG_RESULTを使用できます (関連するデータの量が非常に大きいことをオプティマイザーに通知するため)。

ディスク一時テーブルは元々 B+ ツリーストレージを使用しますが、これは配列ストレージほど効率的ではありません。オプティマイザーはSQL_BIG_RESULTを検出すると、配列ストレージを直接使用します。

つまり、一時テーブルの使用をあきらめて、直接ソート段階に進むということです。

実行プロセス

-- 一時テーブルは使用されませんが、ソート アルゴリズムは直接使用されます。mysql> EXPLAIN SELECT SQL_BIG_RESULT id%100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-------------+-------+-------+-------+---------------+-------+------+------+------+-----------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+------+-----------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | インデックスを使用; ファイルソートを使用 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+------+-----------------------------+

sort_bufferを初期化し、そこに整数フィールド（mと表記）を配置します。

t1のインデックスaをスキャンし、その中のid値を1つずつ取り出し、id%100の値をsort_bufferに格納します。

スキャンが完了したら、 sort_bufferのフィールド m をソートします (sort_buffer のメモリが不足している場合は、ディスク上の一時ファイルを使用してソートが行われます)

ソート後、順序付けられた配列が得られます。順序付けられた配列を走査して、各値が出現する回数を取得します (上記のインデックスを最適化する方法と同様)。

DISTINCTとの比較

-- 標準SQL、SELECT部分に集計関数COUNT(*)を追加します
SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
-- 非標準SQL
SELECT a FROM t GROUP BY a ORDER BY NULL;

SELECT DISTINCT a FROM t;

標準SQL: フィールド a でグループ化し、各グループに a が出現する回数を数える

非標準SQL: COUNT(*)は不要になり、合計計算ロジックを実行する必要がなくなりました。

フィールド a でグループ化し、同じ値 a を持つ 1 行のみを返します。これはDISTINCTセマンティクスと一致します。

集計関数が必要ない場合、 DISTINCTとGROUP BYのセマンティクス、実行フロー、パフォーマンスは同じです。

フィールド a を持つ一時テーブルを作成し、このフィールド a に一意のインデックスを作成します。
テーブルtを走査し、データを1つずつ取り出して一時テーブルに挿入します。
- 一意のキーの競合が見つかった場合はスキップします
- それ以外の場合は挿入は成功です
トラバーサルが完了すると、一時テーブルが結果セットとしてクライアントに返されます。

まとめ

内部一時テーブルが使用されるシナリオ
- ステートメントが実行中に同時にデータを読み取り、結果を取得できる場合、追加のメモリは必要ありません。
- それ以外の場合は、中間結果を保存するために追加のメモリが必要になります。
join_bufferは順序付けされていない配列、 sort_bufferは順序付けされた配列、一時テーブルは2次元のテーブル構造です。
実行ロジックで2次元テーブル機能の使用が必要な場合は、一時テーブルが推奨されます。GROUP GROUP BYステートメントの結果に明示的なソート要件がない場合は、 ORDER BY NULLを追加します（MySQL 5.6）
GROUP BYプロセスでインデックスを使用して、EXPLAIN 結果にUsing temporaryおよびUsing filesort
GROUP BYでカウントするデータの量が多くない場合は、一時メモリテーブルを使用するようにしてください（ tmp_table_size適切に増やすことができます）
データの量が大きすぎる場合は、 SQL_BIG_RESULTを使用して、オプティマイザーにソートアルゴリズムを直接使用するように指示します (一時テーブルをスキップします)。

参考文献

「MySQL実践45講義」

これで、MySQL 内部一時テーブルの具体的な使用法に関するこの記事は終了です。MySQL 内部一時テーブルに関するより詳しい情報は、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: