MySQL の 2 種類の一時テーブルの使用方法の詳細な説明

外部一時テーブル

CREATE TEMPORARY TABLE によって作成された一時テーブルは、外部一時テーブルと呼ばれます。この一時テーブルは現在のユーザーのみに表示され、現在のセッションが終了すると自動的に閉じられます。この一時テーブルは、非一時テーブルと同じ名前を持つことができます (一時テーブルが削除されるまで、非一時テーブルは現在のセッションには表示されません)。

内部一時テーブル

内部一時テーブルは、パフォーマンスの最適化のために使用される特別な軽量一時テーブルです。この一時テーブルは MySQL によって自動的に作成され、特定の操作の中間結果を保存するために使用されます。これらの操作は、最適化フェーズまたは実行フェーズに含めることができます。この内部テーブルはユーザーには表示されませんが、EXPLAIN または SHOW STATUS を使用して、MYSQL が操作を完了するために内部一時テーブルを使用しているかどうかを確認できます。内部一時テーブルは、SQL ステートメントの最適化プロセスにおいて非常に重要な役割を果たします。MySQL の多くの操作は、最適化のために内部一時テーブルに依存しています。ただし、内部一時テーブルを使用するには、テーブルの作成と中間データの保存および置換が必要になるため、ユーザーは SQL ステートメントを記述するときに一時テーブルの使用を避ける必要があります。

内部一時テーブルには 2 つの種類があります。1 つは HEAP 一時テーブルで、すべてのデータがメモリ内に格納され、このテーブルに対する操作には IO 操作は必要ありません。もう 1 つは OnDisk 一時テーブルです。名前が示すように、この一時テーブルはディスク上にデータを格納します。 OnDisk 一時テーブルは、大きな中間結果を伴う操作を処理するために使用されます。 HEAP 一時テーブルに保存されているデータが MAX_HEAP_TABLE_SIZE より大きい場合 (詳細については、MySQL マニュアルのシステム変数セクションを参照してください)、HEAP 一時テーブルは自動的に OnDisk 一時テーブルに変換されます。 5.7 では、OnDisk 一時テーブルは、INTERNAL_TMP_DISK_STORAGE_ENGINE システム変数を通じて MyISAM エンジンまたは InnoDB エンジンを使用できます。

この記事では主に、内部一時テーブルが使用される可能性がある操作について説明します。ユーザーが SQL ステートメントを記述するときに、クエリの最適化のために内部一時テーブルをできるだけ使用しない場合は、クエリ実行の効率が効果的に向上します。

まずテーブル t1 を定義します。

テーブル t1( a int, b int); t1 に値 (1,2),(3,4); を挿入します。

以下のすべての操作はテーブル t1 に基づいています。

SQL ステートメントで SQL_BUFFER_RESULT ヒントを使用する

SQL_BUFFER_RESULT は主に、MySQL がテーブルのロックをできるだけ早く解放できるようにするために使用されます。データ量が多いと、クライアントにデータを送信するのに時間がかかります。データを一時テーブルにバッファリングすることで、読み取りロックがテーブルを占有する時間を効果的に短縮できます。

例えば：

mysql> format=json を説明し、t1 から SQL_BUFFER_RESULT * を選択します。 
説明する 
{ 
 "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "2.00" 
}, 
"buffer_result": { 
 "using_temporary_table": true、 
 "テーブル"： { 
"テーブル名": "t1", 
"アクセスタイプ": "すべて", 
...

SQL ステートメントに DERIVED_TABLE が含まれている場合。

5.7 では、新しい最適化方法の採用により、派生テーブルが外部クエリにマージされるのを禁止するために、set optimizer_switch='derived_merge=off' を使用する必要があります。

例えば：

mysql> explain format=json select * from (select * from t1) as tt; 
説明する 
{ 
 "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "2.40" 
}, 
"テーブル"： { 
 "テーブル名": "tt", 
 "アクセスタイプ": "すべて", 
 ... 
 "サブクエリからマテリアライズ": { 
"using_temporary_table": true、 
...

システムテーブルをクエリすると、システムテーブル内のデータは内部の一時テーブルに保存されます。

現在、EXPLAIN を使用してシステムテーブルデータの読み取りに内部一時テーブルが必要かどうかを確認することはできませんが、SHOW STATUS を使用して内部一時テーブルが使用されているかどうかを確認することはできます。

例えば：

mysql> information_schema.character_sets から * を選択します。 
mysql> 'CREATE%' のようなステータスを表示します。

DISTINCT ステートメントが最適化されていない場合、つまり、DISTINCT ステートメントが最適化されて GROUP BY 操作に変換されるか、または DISTINCT が UNIQUE INDEX を使用して削除される場合は、内部一時テーブルが使用されます。

mysql> explain format=json select distinct a from t1; 
説明する 
{ 
{ 
 "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "1.60" 
}, 
「重複の削除」: { 
 "using_temporary_table": true、 
...

クエリに ORDER BY 句があり、最適化によって削除できない場合。以下の場合、内部一時テーブルを使用して中間データをキャッシュし、中間データをソートします。

1) 接続テーブルがBNL（バッチネストループ）/BKA（バッチキーアクセス）を使用している場合

例えば：

1)BNLはデフォルトでオンになっています

mysql> explain format=json select * from t1, t1 as t2 order by t1.a; 
説明する 
{ 
 "クエリブロック": { 
 "select_id": 1, 
 「コスト情報」: { 
"クエリコスト": "22.00" 
 }, 
 「順序操作」: { 
"using_temporary_table": true、 
 ...

2)) BNL をオフにすると、ORDER BY は filesort を直接使用します。

mysql> optimizer_switch='block_nested_loop=off' を設定します。 
クエリは正常、影響を受けた行は 0 行 (0.00 秒) 
mysql> explain format=json select * from t1, t1 as t2 order by t1.a; 
説明する 
{ 
  "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "25.00" 
}, 
「順序操作」: { 
 "using_filesort": true、 
...

2) ORDER BY 列は、実行プラン内の最初の結合テーブルの列に属していません。

例えば：

mysql> explain format=json select * from t as t1, t as t2 order by t2.a; 
説明する 
{ 
  "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "25.00" 
}, 
「順序操作」: { 
 "using_temporary_table": true、 
...

3) ORDER BY 式が複雑な式である場合。

では、MySQL ではどのような ORDER BY 式が複雑な式とみなされるのでしょうか?

1)) ソート式が SP または UDF の場合。

例えば：

存在する場合は関数を削除します func1; 
区切り文字 | 
関数func1(x int)を作成する 
決定論的にintを返す 
始める 
z1、z2をintとして宣言します。 
z1 = x と設定します。 
z2 = z1+2 と設定します。 
z2を返します。 
終了| 
区切り文字 ; 
format=json select * from t1 order by func1(a); を説明します。 
{ 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "2.20" 
}, 
「順序操作」: { 
 "using_temporary_table": true、 
...

2)ORDER BY列に集計関数が含まれている

実行プランを簡素化するために、INDEX を使用して GROUP BY ステートメントを最適化します。

例えば：

 t1(a)にインデックスidx1を作成します。 
 説明 format=json SELECT a FROM t1 group by a order by sum(a); 
 | { 
  "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "1.20" 
}, 
「順序操作」: { 
 "using_temporary_table": true、 
 "using_filesort": true、 
 「グループ化操作」: { 
"using_filesort": false, 
... 
 t1 のインデックス idx1 を削除します。

3) ORDER BY 列に SCALAR SUBQUERY が含まれていますが、SCALAR SUBQUERY は最適化されていません。

例えば：

format=json select (select rand() from t1 limit 1) as a from t1 order by a; を説明します。 
| { 
 "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "1.20" 
}, 
「順序操作」: { 
 "using_temporary_table": true、 
 "using_filesort": true、 
...

4) クエリに ORDER BY ステートメントと GROUP BY ステートメントの両方があるが、2 つのステートメントで使用される列が異なる場合。

注意: 5.7 の場合は、sql_mode を non-only_full_group_by モードに設定する必要があります。そうしないと、エラーが報告されます。

また、実行プランを簡素化するために、INDEX を使用して GROUP BY ステートメントを最適化します。

例えば：

sql_mode='' を設定します。 
t1(b) にインデックス idx1 を作成します。 
説明 format=json select t1.a from t1 group by t1.b order by 1; 
| { 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "1.40" 
}, 
「順序操作」: { 
 "using_temporary_table": true、 
 "using_filesort": true、 
 「グループ化操作」: { 
"using_filesort": false, 
... 
t1 のインデックス idx1 を削除します。

クエリに GROUP BY 句があり、最適化によって削除できない場合。以下の場合、内部一時テーブルを使用して中間データをキャッシュし、中間データに対して GROUP BY を実行します。

1) 接続テーブルが BNL (Batched Nestloop)/BKA (Batched Key Access) を使用している場合。

例えば：

format=json の説明: t1 から t2.a を選択し、t1 を t2 としてグループ化し、t1.a でグループ化します。 
| { 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "8.20" 
}, 
「グループ化操作」: { 
 "using_temporary_table": true、 
 "using_filesort": true、 
 「コスト情報」: { 
"ソートコスト": "4.00" 
...

2) GROUP BY 列が実行プラン内の最初の結合テーブルに属していない場合。

例えば：

format=json を説明してください。t1 から t2.a を選択し、t1 を t2 としてグループ化し、t2.a でグループ化します。 
| { 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "8.20" 
}, 
「グループ化操作」: { 
 "using_temporary_table": true、 
 "using_filesort": true、 
 "ネストされたループ": [ 
...

3) GROUP BY ステートメントで使用される列が ORDER BY ステートメントで使用される列と異なる場合。

例えば：

sql_mode='' を設定します。 
説明 format=json select t1.a from t1 group by t1.b order by t1.a; 
| { 
  "クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "1.40" 
}, 
「順序操作」: { 
 "using_filesort": true、 
 「グループ化操作」: { 
"using_temporary_table": true、 
"using_filesort": false, 
...

4) GROUP BY に ROLLUP が含まれ、複数テーブルの外部結合に基づいている場合。

例えば：

説明 format=json select sum(t1.a) from t1 left join t1 as t2 on true group by t1.a with rollup; 
| { 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "7.20" 
}, 
「グループ化操作」: { 
 "using_temporary_table": true、 
 "using_filesort": true、 
 「コスト情報」: { 
"ソートコスト": "4.00" 
 }, 
...

5) GROUP BY ステートメントで使用される列が SCALAR SUBQUERY からのものであり、最適化されていない場合。

例えば：

フォーマット=json select (select avg(a) from t1) as a from t1 group by a; を説明します。 
| { 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "3.40" 
}, 
「グループ化操作」: { 
 "using_temporary_table": true、 
 "using_filesort": true、 
 「コスト情報」: { 
"ソートコスト": "2.00" 
 }, 
...

最適化のためにIN式をセミ結合に変換する

1) セミ結合実行モードがマテリアライゼーションの場合

例えば：

optimizer_switch='firstmatch=off,duplicateweedout=off' を設定します。 
format=json select * from t1 where a in (select b from t1); を説明します。 
| { 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "5.60" 
}, 
"ネストされたループ": [ 
 { 
「スキャンごとに検査される行数」: 1, 
 "サブクエリからマテリアライズ": { 
"using_temporary_table": true、 
"クエリブロック": { 
 "テーブル"： { 
"テーブル名": "t1", 
"アクセスタイプ": "すべて", 
...

2) セミジョイン実行モードが重複除去の場合

例えば：

optimizer_switch='firstmatch=off' を設定します。 
format=json select * from t1 where a in (select b from t1); を説明します。 
| { 
"クエリブロック": { 
"select_id": 1, 
「コスト情報」: { 
 "クエリコスト": "4.80" 
}, 
「重複の削除」: { 
 "using_temporary_table": true、 
 "ネストされたループ": [ 
{ 
...

クエリステートメントに UNION が含まれている場合、MySQL は UNION 操作による重複の排除を支援するために内部一時テーブルを使用します。

例えば：

format=json select * from t1 union select * from t1; を説明します。 
| { 
"クエリブロック": { 
"union_result": { 
 "using_temporary_table": true、 
 "テーブル名": "", 
...

クエリステートメントで複数のテーブル更新を使用する場合。

ここで、Explain では内部一時テーブルが使用されていることを表示できないため、ステータスを確認する必要があります。

例えば：

t1 を更新し、t1 を t2 として設定します。t1.a=3 に設定します。 
「CREATE%」のようなステータスを表示します。

集計関数に以下の関数が含まれている場合は、内部一時テーブルも使用されます。

1) count(個別*) 
例えば： 
format=json select count(distinct a) from t1; を説明します。 
2) グループ連結

例えば：

format=json を説明し、t1 から group_concat(b) を選択します。

まとめると、上記の 10 の状況では、MySQL は内部一時テーブルを使用して中間結果をキャッシュします。データ量が多い場合、内部一時テーブルはデータをディスクに保存するため、パフォーマンスに影響が及ぶことは明らかです。パフォーマンスの低下を最小限に抑えるためには、上記の状況をできるだけ避ける必要があります。

要約する

以上が、2 つの MySQL 一時テーブルの詳細な使用方法に関するこの記事の内容のすべてです。皆様のお役に立てれば幸いです。興味のある方は、いくつかの重要な MySQL 変数、MySQL の準備原則の詳細な説明、MySQL テーブルデータの削除方法などを参照してください。ご質問がある場合は、メッセージを残してください。どなたでもコミュニケーションや議論を歓迎します。

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