場合によっては、サーバーはステートメントの処理中に内部一時テーブルを作成します。 これが発生した場合、ユーザーは直接的に制御できません。
サーバーは、次のような条件下で一時テーブルを作成します:
UNIONステートメントの評価 (ただし、後で説明するいくつかの例外があります)。TEMPTABLEアルゴリズム、UNIONまたは集計を使用するビューなど、一部のビューの評価。導出テーブルの評価 (セクション13.2.11.8「導出テーブル」 を参照)。
共通テーブル式の評価 (セクション13.2.15「WITH (共通テーブル式)」 を参照)。
サブクエリーまたは準結合の実体化用に作成されたテーブル (セクション8.2.2「サブクエリー、導出テーブル、ビュー参照および共通テーブル式の最適化」 を参照)。
ORDER BY句と異なるGROUP BY句を含むステートメント、または結合キューの最初のテーブル以外のテーブルのカラムがORDER BYまたはGROUP BYに含まれるステートメントの評価。ORDER BYと組み合せたDISTINCTの評価には、一時テーブルが必要になる場合があります。SQL_SMALL_RESULT修飾子を使用するクエリーの場合、ディスク上の記憶域を必要とする要素 (後述) もクエリーに含まれていないかぎり、MySQL はインメモリー一時テーブルを使用します。同じテーブルから選択して同じテーブルに挿入する
INSERT ... SELECTステートメントを評価するために、MySQL はSELECTの行を保持する内部一時テーブルを作成し、それらの行をターゲットテーブルに挿入します。 セクション13.2.6.1「INSERT ... SELECT ステートメント」を参照してください。複数テーブルの
UPDATEステートメントの評価。GROUP_CONCAT()またはCOUNT(DISTINCT)式の評価。ウィンドウ関数の評価 (セクション12.21「ウィンドウ関数」 を参照) では、必要に応じて一時テーブルが使用されます。
ステートメントに一時テーブルが必要かどうかを判断するには、EXPLAIN を使用し、Extra カラムをチェックして、Using temporary と表示されているかどうかを確認します (セクション8.8.1「EXPLAIN によるクエリーの最適化」 を参照)。 EXPLAIN では、導出一時テーブルまたは実体化一時テーブルの場合、必ずしも Using temporary とは言えません。 ウィンドウ関数を使用するステートメントの場合、EXPLAIN と FORMAT=JSON では常にウィンドウステップに関する情報が提供されます。 ウィンドウ関数で一時テーブルが使用されている場合は、ステップごとに示されます。
一部のクエリー条件では、インメモリー一時テーブルを使用できません。この場合、サーバーは代わりにディスク上のテーブルを使用します:
テーブル内の
BLOBまたはTEXTカラムの存在 ただし、MySQL 8.0 のインメモリー内部一時テーブルのデフォルトのストレージエンジンであるTempTableストレージエンジンは、MySQL 8.0.13 の時点でバイナリラージオブジェクト型をサポートしています。 内部一時テーブルストレージエンジンを参照してください。UNIONまたはUNION ALLが使用された場合に、SELECTリスト内の 512 (バイナリ文字列の場合はバイト数、非バイナリ文字列の場合は文字数) より大きい最大長を持つ文字列カラムの存在。SHOW COLUMNSおよびDESCRIBEステートメントでは、一部のカラムのタイプとしてBLOBが使用されるため、結果に使用される一時テーブルはディスク上のテーブルです。
サーバーは、特定の条件を満たす UNION ステートメントに一時テーブルを使用しません。 かわりに、結果カラムの型キャストの実行に必要なデータ構造のみが一時テーブルの作成から保持されます。 テーブルは完全にはインスタンス化されず、テーブルに対する書込みまたは読取りは行われません。行はクライアントに直接送信されます。 最後のクエリーブロックが実行されるまでサーバーが待機する必要がないため、結果としてメモリーおよびディスクの要件が削減され、最初の行がクライアントに送信されるまでの遅延が短くなります。 EXPLAIN およびオプティマイザのトレース出力には、この実行計画が反映されます: UNION RESULT クエリーブロックは、一時テーブルから読み取る部分に対応しているため、存在しません。
次の条件は、一時テーブルを使用しない UNION の評価に適しています:
共用体は、
UNIONやUNION DISTINCTではなく、UNION ALLです。グローバル
ORDER BY句がありません。共用体は、
{INSERT | REPLACE} ... SELECT ...ステートメントのトップレベルのクエリーブロックではありません。
内部一時テーブルはメモリー内に保持され、TempTable または MEMORY ストレージエンジンによって処理されるか、InnoDB ストレージエンジンによってディスクに格納されます。
インメモリー内部一時テーブルのストレージエンジン
internal_tmp_mem_storage_engine セッション変数は、インメモリー内部一時テーブルのストレージエンジンを定義します。 許可される値は、TempTable (デフォルト) および MEMORY です。
TempTable ストレージエンジンは、VARCHAR および VARBINARY カラム、および MySQL 8.0.13 の時点でのその他のバイナリラージオブジェクト型の効率的なストレージを提供します。
temptable_max_ram 変数は、TempTable ストレージエンジンがメモリーマップ一時ファイルまたは InnoDB ディスク上の内部一時テーブルの形式でディスクから領域の割り当てを開始する前に占有できる RAM の最大量を定義します。 デフォルトの temptable_max_ram 設定は 1GiB です。 temptable_use_mmap 変数 (MySQL 8.0.16 で導入) は、temptable_max_ram の制限を超えた場合に、TempTable ストレージエンジンがメモリーマップされたファイルまたは InnoDB ディスク上の内部一時テーブルを使用するかどうかを制御します。 デフォルト設定は temptable_use_mmap=ON です。 MySQL 8.0.23 で導入された temptable_max_mmap 変数は、TempTable ストレージエンジンが内部一時テーブルデータの InnoDB ディスク上の内部一時テーブルへの格納を開始する前に、メモリーマップされたファイルから割り当てることができるメモリーの最大量を定義します。 temptable_max_mmap=0 設定では、メモリーマップファイルからの割当てが無効化され、temptable_use_mmap 設定に関係なく、使用が効率的に無効化されます。
temptable_max_ram 設定では、TempTable ストレージエンジンを使用する各スレッドに割り当てられたスレッドローカルメモリーブロックは考慮されません。 スレッドローカルメモリーブロックのサイズは、スレッドの最初のメモリー割当てリクエストのサイズによって異なります。 リクエストが 1MB 未満の場合 (ほとんどの場合)、スレッドローカルメモリーブロックサイズは 1MB です。 リクエストが 1MB を超える場合、スレッドローカルメモリーブロックは初期メモリーリクエストとほぼ同じサイズになります。 スレッドローカルメモリーブロックは、スレッドが終了するまでスレッドローカル記憶域に保持されます。
TempTable ストレージエンジンによる内部一時テーブルのオーバーフローメカニズムとしてのメモリーマップ一時ファイルの使用は、次の規則によって制御されます:
一時ファイルは、
tmpdir変数で定義されたディレクトリに作成されます。一時ファイルは、作成して開いた直後に削除されるため、
tmpdirディレクトリには表示されません。 一時ファイルが占有する領域は、一時ファイルが開いている間はオペレーティングシステムによって保持されます。 領域は、一時ファイルがTempTableストレージエンジンによって閉じられたとき、またはmysqldプロセスがシャットダウンされたときに再利用されます。RAM と一時ファイル間、RAM 内、または一時ファイル間でデータが移動されることはありません。
temptable_max_ramで定義された制限内に領域が使用可能になると、新しいデータが RAM に格納されます。 それ以外の場合、新しいデータは一時ファイルに格納されます。テーブルの一部のデータが一時ファイルに書き込まれた後に RAM で領域が使用可能になった場合、残りのテーブルデータを RAM に格納できます。
TempTable ストレージエンジンが InnoDB ディスク上の内部一時テーブルをオーバーフローメカニズムとして使用するように構成されている場合 (temptable_use_mmap=OFF または temptable_max_mmap=0)、temptable_max_ram 制限を超えるインメモリーテーブルは InnoDB ディスク上の内部一時テーブルに変換され、そのテーブルに属する行はメモリーから InnoDB ディスク上の内部一時テーブルに移動されます。 MySQL 8.0.16 で削除された internal_tmp_disk_storage_engine 設定は、TempTable ストレージエンジンオーバーフローメカニズムには影響しません。
MySQL 8.0.23 より前は、TempTable ストレージエンジンが temptable_max_ram の制限を超え、メモリーマップされたファイル用に一時ディレクトリ内の過剰な領域を使用することが多い場合、TempTable オーバーフローメカニズムとして InnoDB のディスク上の内部一時テーブルが推奨されていました。 MySQL 8.0.23 の時点で、temptable_max_mmap 変数は TempTable ストレージエンジンがメモリーマップされたファイルから割り当てるメモリー量の制限を定義するため、これらのファイルが使用する領域が多すぎるリスクに対処します。 通常、temptable_max_ram の制限を超えるのは、大規模な内部一時テーブルの使用または内部一時テーブルの広範囲な使用が原因です。 InnoDB のディスク上の内部一時テーブルは、デフォルトでデータディレクトリに存在するセッション一時テーブルスペースに作成されます。 詳細は、セクション15.6.3.5「一時テーブルスペース」を参照してください。
インメモリー一時テーブルに MEMORY ストレージエンジンを使用する場合、MySQL はインメモリー一時テーブルが大きすぎると、インメモリー一時テーブルをディスク上のテーブルに自動的に変換します。 インメモリー一時テーブルの最大サイズは、tmp_table_size または max_heap_table_size のいずれか小さい方の値によって定義されます。 これは、CREATE TABLE で明示的に作成される MEMORY テーブルとは異なります。 このようなテーブルの場合、max_heap_table_size 変数のみがテーブルの大きさを決定し、ディスク上の形式への変換はありません。
オンディスク内部一時テーブルのストレージエンジン
MySQL 8.0.16 以降、サーバーは常に InnoDB ストレージエンジンを使用してディスク上の内部一時テーブルを管理します。
MySQL 8.0.15 以前では、internal_tmp_disk_storage_engine 変数を使用して、ディスク上の内部一時テーブルに使用されるストレージエンジンを定義していました。 この変数は MySQL 8.0.16 で削除され、この目的に使用されるストレージエンジンはユーザーが構成できなくなりました。
MySQL 8.0.15 以前: 共通テーブル式 (CTE) の場合、ディスク上の内部一時テーブルに使用されるストレージエンジンを MyISAM にすることはできません。 internal_tmp_disk_storage_engine=MYISAM の場合、ディスク上の一時テーブルを使用して CTE を実体化しようとすると、エラーが発生します。
MySQL 8.0.15 以前: internal_tmp_disk_storage_engine=INNODB を使用している場合、InnoDB row or column limits を超えるディスク上の内部一時テーブルを生成するクエリーは、「行サイズが大きすぎます」または「カラムが多すぎます」エラーを返します。 回避策は、internal_tmp_disk_storage_engine を MYISAM に設定することです。
インメモリー内部一時テーブルが TempTable ストレージエンジンによって管理される場合、VARCHAR カラム、VARBINARY カラムおよびその他のバイナリラージオブジェクト型のカラム (MySQL 8.0.13 時点でサポートされています) を含む行は、セルの配列によってメモリー内にテーブルされ、各セルには NULL フラグ、データ長およびデータポインタが含まれます。 カラム値は、配カラムの後の単一のメモリー領域に、パディングなしで連続した順序で配置されます。 配列内の各セルは 16 バイトの記憶域を使用します。 TempTable ストレージエンジンが temptable_max_ram 制限を超え、メモリーマップされたファイルまたは InnoDB ディスク上の内部一時テーブルとしてディスクから領域の割り当てを開始した場合も、同じストレージ形式が適用されます。
インメモリー内部一時テーブルが MEMORY ストレージエンジンによって管理される場合、固定長の行形式が使用されます。 VARCHAR および VARBINARY のカラム値は最大カラム長に埋め込まれ、実質的には CHAR および BINARY のカラムとして格納されます。
MySQL 8.0.16 より前は、ディスク上の内部一時テーブルは InnoDB または MyISAM ストレージエンジンによって管理されていました (internal_tmp_disk_storage_engine の設定によって異なります)。 どちらのエンジンも、動的幅の行形式を使用して内部一時テーブルを格納します。 カラムには必要な記憶域のみが必要です。これにより、固定長の行を使用するディスク上のテーブルと比較して、ディスク I/O,領域要件および処理時間が短縮されます。 MySQL 8.0.16 以降、internal_tmp_disk_storage_engine はサポートされず、ディスク上の内部一時テーブルは常に InnoDB によって処理されます。
MEMORY ストレージエンジンを使用する場合、ステートメントは最初にインメモリー内部一時テーブルを作成し、テーブルが大きすぎる場合はそれをディスク上のテーブルに変換できます。 このような場合は、変換をスキップし、ディスク上に内部一時テーブルを作成して開始することで、パフォーマンスが向上する可能性があります。 big_tables 変数を使用して、内部一時テーブルのディスク記憶域を強制できます。
内部一時テーブルがメモリーまたはディスクに作成されると、サーバーは Created_tmp_tables 値を増分します。 内部一時テーブルがディスク上に作成されると、サーバーは Created_tmp_disk_tables 値を増分します。 ディスク上に作成される内部一時テーブルが多すぎる場合は、tmp_table_size および max_heap_table_size の設定を増やすことを検討してください。
既知の制限のため、Created_tmp_disk_tables ではメモリーマップファイルに作成されたディスク上の一時テーブルはカウントされません。 デフォルトでは、TempTable ストレージエンジンオーバーフローメカニズムは、メモリーマップされたファイルに内部一時テーブルを作成します。 この動作は、temptable_use_mmap および temptable_max_mmap 変数によって制御されます。
memory/temptable/physical_ram および memory/temptable/physical_disk パフォーマンススキーマインストゥルメントを使用すると、メモリーおよびディスクからの TempTable 領域割り当てをモニターできます。memory/temptable/physical_ram では、割り当てられた RAM の量がレポートされます。メモリーマップされたファイルが TempTable オーバーフローメカニズムとして使用されている場合、memory/temptable/physical_disk はディスクから割り当てられた領域の量を報告します。 physical_disk インストゥルメントが 0 以外の値を報告し、メモリーマップされたファイルが TempTable オーバーフローメカニズムとして使用される場合、temptable_max_ram しきい値にはある時点で到達しました。 データは、memory_summary_global_by_event_name などのパフォーマンススキーマメモリーサマリーテーブルでクエリーできます。 セクション27.12.18.10「メモリーサマリーテーブル」を参照してください。