MySQL FAQ シリーズ: 一時テーブルを使用する場合

一時テーブルの概要

一時テーブルとは: MySQL は中間結果セットを保存するために使用されます。一時テーブルは現在の接続でのみ表示されます。接続が閉じられると、Mysql は自動的にテーブルを削除し、すべてのスペースを解放します。一時テーブルが生成される理由: 一般的に、複雑なSQLでは多数の一時テーブルが作成されます。

一時テーブルには、メモリ一時テーブルとディスク一時テーブルの 2 種類があります。メモリ一時テーブルはメモリ ストレージ エンジンを使用し、ディスク一時テーブルは myisam ストレージ エンジンを使用します (ディスク一時テーブルは innodb ストレージ エンジンも使用できます。internal_tmp_disk_storage_engine パラメータは、使用するストレージ エンジンを制御します。MySQL 5.7.6 以降ではデフォルトは innodb ストレージ エンジンであり、それ以前のバージョンではデフォルトは myisam ストレージ エンジンです)。 Created_tmp_disk_tables および Created_tmp_tables パラメータを使用して、生成されるディスク一時テーブルの数と、生成されるすべての一時テーブル (メモリとディスク) を表示します。

MySQL は次の状況で一時テーブルを作成します。

1. UNIONクエリ

2. UNION クエリで TEMPTABLE アルゴリズムまたはビューを使用します。

3. ORDER BY 句と GROUP BY 句が異なる場合。

4. テーブル結合では、ORDER BY 列が駆動テーブルにありません。

5. DISTINCT クエリを実行し、ORDER BY を追加します。

6. SQL で SQL_SMALL_RESULT オプションが使用されている場合。

7. FROM 内のサブクエリ

8. サブクエリまたはセミ結合中に作成されたテーブル。

EXPLAIN 実行プラン結果の Extra 列を確認します。Using Temporary が含まれている場合は、一時テーブルが使用されることを意味します。

もちろん、一時テーブルに格納する必要があるデータの量が上限 (tmp-table-size または max-heap-table-size のいずれか大きい方) を超える場合は、ディスクベースの一時テーブルを生成する必要があります。

ディスク一時テーブルは、次の状況で作成されます。

1. データ テーブルには BLOB/TEXT 列が含まれています。

2. GROUP BY または DSTINCT 列に、512 文字を超える文字型の列 (または 512 バイトを超えるバイナリ型の列。5.6.15 より前は、512 バイトを超えているかどうかのみが考慮されます) があります。

3. SELECT、UNION、および UNION ALL クエリには、最大長が 512 を超える列があります (文字列型の場合は 512 文字、バイナリ型の場合は 512 バイト)。

4. SHOW COLUMNS/FIELDS や DESCRIBE などの SQL コマンドを実行します。これらのコマンドの実行結果には BLOB 列型が使用されるためです。

5.7.5 以降では、ディスク一時テーブルのエンジン タイプを InnoDB として定義するための新しいシステム オプション internal_tmp_disk_storage_engine が追加されました。これより前は、MyISAM のみが使用できました。 5.6.3 以降に追加された新しいシステム オプション default_tmp_storage_engine は、CREATE TEMPORARY TABLE によって作成される一時テーブルのエンジン タイプを制御します。以前のデフォルトは MEMORY でした。これら 2 つを混同しないでください。

次の例を参照してください

mysql> default_tmp_storage_engine を "InnoDB" に設定します。
-rw-rw---- 1 mysql mysql 8558 Jul 7 15:22 #sql4b0e_10_0.frm -- InnoDB エンジンの一時テーブル -rw-rw---- 1 mysql mysql 98304 Jul 7 15:22 #sql4b0e_10_0.ibd
-rw-rw---- 1 mysql mysql 8558 7月7日 15:25 #sql4b0e_10_2.frm

mysql> default_tmp_storage_engine = "MyISAM" を設定します。
-rw-rw---- 1 mysql mysql 0 Jul 7 15:25 #sql4b0e_10_2.MYD -- MyISAM エンジンの一時テーブル -rw-rw---- 1 mysql mysql 1024 Jul 7 15:25 #sql4b0e_10_2.MYI

mysql> default_tmp_storage_engine = "MEMORY" を設定します。
-rw-rw---- 1 mysql mysql 8558 Jul 7 15:26 #sql4b0e_10_3.frm -- MEMORYエンジンの一時テーブル

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。