MySQL 学習用の一時テーブルの概要

通常のユーザー データ テーブルと比較すると、MySQL/InnoDB の一時テーブルは誰にとっても馴染みのないものです。さらに、さまざまな一時テーブルが作成されるタイミングと場所が固定されていないため、さらに謎が深まります。最もとらえどころのないことは、一時テーブルでは最初にファイルが作成され、その後何もせずにファイルが削除され、読み取りと書き込みのためのハンドルが残されることが多く、人々にドラゴンが始まりを見ても終わりを見ていないかのような印象を与えます。この記事では、MySQL のさまざまなバージョンにおける一時テーブルの処理方法を詳しく分析しますので、皆様のお役に立てれば幸いです。

概要

正確に言えば、よく話題になる一時テーブルには 2 種類あります。1 つは実際にはテーブルで、ユーザー、書き込みにはテーブル読み書きインターフェースを使用します。読み書き時にはテーブルがファイルシステム上に存在する必要があります。もう 1 つは、SQL 計算の途中処理でデータを保存するために使用される一時ファイルです。読み書きは、ファイルの読み取りと書き込みインターフェイスを通じて実行されるため、読み取りと書き込み中にファイルが削除され、操作用のファイル ハンドルが残っている可能性があります。

関連チュートリアル: mysql ビデオ チュートリアル

一時テーブル

一時テーブルは、ディスク一時テーブルとメモリ一時テーブルに分けることができます。テーブルと一時ファイルはディスク上にのみ存在し、メモリ内には存在しません。具体的には、テンポラリテーブルのメモリ形式には、MemoryエンジンとTemptableエンジンがあり、主な違いは文字型(varchar、blob、text型)の格納方法であり、前者は実際の文字数に関係なく固定長領域を使用して格納します。後者では、オペレーターは可変長スペースストレージを使用するため、メモリ内のストレージ効率が向上し、より多くのデータをディスク一時テーブルに変換する代わりにメモリ内で処理できます。 Memory エンジンは 5.6 初期から利用可能であり、Temptable は 8.0 で導入された新しいエンジンです。一方、ディスク一時テーブルには 3 つの形式があり、1 つは MyISAM テーブル、1 つは InnoDB 一時テーブル、もう 1 つは Temptable ファイル マップ テーブルです。最後の方法は 8.0 で提供されます。

5.6 以前のバージョンでは、ディスク一時テーブルはデータベース構成の一時ディレクトリに配置され、ディスク一時テーブルのアンドゥログは通常のテーブルのアンドゥと一緒に配置されます (ディスク一時テーブルはデータベースの再起動後に再起動されます。後で削除されました。クラッシュ回復を通じてトランザクションの整合性を確保するために redolog を使用する必要はないため、redolog を書き込む必要はありませんが、undolog は必要なため、依然として必要です。ロールバックをサポートするため)。

MySQL 5.7 以降、ディスク一時テーブルのデータと UNDO は分離され、別のテーブルスペース ibtmp1 に配置されます。一時テーブルを分離する理由は、主に、テーブルの作成および削除時にメタデータを維持するオーバーヘッドを軽減するためです。

MySQL 8.0 以降、ディスク一時テーブルのデータはセッション一時テーブル領域プール (#innodb_temp ディレクトリ内の ibt ファイル) に個別に配置され、一時テーブルの UNDO はグローバル テーブルに配置されます。スペースibtmp1。もう 1 つの大きな改善点は、8.0 ではディスク一時テーブル データによって占められていた領域を接続の切断後にオペレーティング システムに解放できることですが、バージョン 5.7 では解放する前に再起動する必要があることです。

現在、一時テーブルが使用される状況は 2 つあります。

ユーザーが明示的に一時テーブルを作成する

これはユーザーが明示的に実行します。コマンド createtemporary table を使用してテーブルを作成します。エンジン タイプは明示的に指定されるか、デフォルトの構成値 (default_tmp_storage_engine) が使用されます。メモリ使用量は、指定されたエンジンのメモリ管理方法に従います。たとえば、InnoDB テーブルはバッファ プールにキャッシュされ、ダーティ スレッドを通じてディスク ファイルに書き戻されます。

5.6 では、ディスク一時テーブルは tmpdir の下にあり、ファイル名は #sql4d2b_8_0.ibd のようになります。#sql は固定プレフィックスです。 4d2b はプロセス番号の 16 進表現、8 は MySQL スレッド番号 (show processlist の ID) の 16 進表現、0 は開始スレッド番号です。 0 から始まる各接続のポイント 開始増分値 ibd は、innodb のディスク一時テーブルです (パラメータ default_tmp_storage_engine によって制御されます)。 5.6 では、ディスク一時テーブルが作成された後、対応する frm ファイルとエンジン ファイルが tmpdir の下に作成され、ファイル システムの ls コマンドを通じて表示できます。接続が閉じられると、対応するファイルは自動的に削除されます。したがって、5.6 の tmpdir に類似した形式のファイル名が多数ある場合、そのファイル名を使用して、どのプロセスとどの接続が一時テーブルを使用しているかを判断できます。このテクニックは、tmpdir ディレクトリが占有する問題のトラブルシューティングに特に適用できます。かなりのスペースです。ユーザーによって明示的に作成されたこの種の一時テーブルは自動的に解放され、接続が解放されるとスペースがオペレーティング システムに解放されます。一時表のアンドゥログはアンドゥ表スペースに格納され、通常の表のアンドゥと一緒に配置されます。元に戻すロールバック セグメントを使用すると、ユーザーが作成した一時テーブルもロールバックをサポートできます。

5.7 では、一時ディスク テーブルは ibtmp ファイル内に配置され、ibtmp ファイルの場所とサイズの制御方法はパラメータ innodb_temp_data_file_path によって制御されます。明示的に作成されたテーブルのデータと取り消しは ibtmp にあります。ユーザー接続が切断された後、一時テーブルは解放されますが、ibtmp ファイル内で一時テーブルをマークしただけでは、領域がオペレーティング システムに解放されることはありません。スペースを解放したい場合は、データベースを再起動する必要があります。なお、注意点としては、5.6ではtmpdir配下に作成したファイルを直接見ることができましたが、5.7ではibtmpテーブルスペースに作成するため、特定のテーブルファイルを見ることができません。確認する必要がある場合は、INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO テーブルを確認する必要があります。テーブルには列名があります。テーブル名はここで確認できます。命名仕様は 5.6 と同様であるため、多くのスペースを占める接続もすぐに見つけることができます。

8.0 では、一時テーブルのデータと UNDO がさらに分離され、データは ibt ファイル (パラメータ innodb_temp_tablespaces_dir で制御) に保存され、UNDO は依然としてibtmp ファイル (引き続きパラメータ innodb_temp_tablespaces_dir によって制御されます。パラメータ innodb_temp_data_file_path 制御)。 ibt ファイルを保存するものはセッション一時表スペースと呼ばれ、UNDO を保存する ibtmp はグローバル一時表スペースと呼ばれます。ここでは、データを保存するセッション一時テーブルスペースについて説明します。セッション一時テーブルスペースは、ibt ファイルで構成されるファイルプールのセットとしてディスク上に表示されます。起動時に、データベースは構成されたディレクトリに再作成され、データベースが閉じるときに削除されます。起動時に、デフォルトで 10 個の ibt ファイルが作成され、各接続は最大 2 つを使用します。1 つはユーザーによって作成された一時テーブル用で、もう 1 つは後述のオプティマイザーによって作成された暗黙的な一時テーブル用です。もちろん、一時テーブルは必要な場合にのみ作成され、不要な場合は ibt ファイルが占有されることはありません。 10 個の ibts がすべて使用されると、データベースは引き続き作成され、最大 400,000 個が作成されます。接続が解放されると、接続で使用されていた ibt ファイルが自動的に解放され、スペースが再利用されます。グローバル一時テーブルスペースを再利用したい場合でも、再起動する必要があります。ただし、データを保存するファイルが分離され、動的リサイクル (つまり、接続が切断されたときにスペースが解放される) がサポートされているため、5.7 で長らく誰もが悩まされていたスペース占有の問題は大幅に軽減されました。もちろん、最適化の余地はまだあります。たとえば、接続が切断された後に領域を解放する必要があります。理論的には、特定の SQL (ユーザーが明示的に作成した一時テーブルを削除するなど) の後に多くの領域を解放できます。実行され、解放されます。さらに、テーブル名を確認する必要がある場合は、

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO

テーブルを確認してください。 8.0 では、明示的な一時テーブルを圧縮テーブルにすることはできませんが、5.6 と 5.7 では圧縮テーブルにすることができることに注意してください。

オプティマイザは暗黙的に一時テーブルを作成します

この種の一時テーブルは、複雑な SQL の実行を支援するためにデータベースによって作成される補助テーブルです。 table required? 、通常はオプティマイザによって決定されます。ユーザーが明示的に作成した一時テーブル用のディスク ファイルを直接作成する場合とは異なり、オプティマイザは SQL に一時テーブルの支援が必要であると判断した場合、まずメモリ一時テーブルを使用します。設定されたメモリ (min(tmp_table_size, max_heap_table_siz)) を超える場合は、メモリ一時テーブルが使用されます。 , それはディスク一時テーブルに変換されます。この種類のディスク一時テーブルは、ユーザーによって明示的に作成されたものと似ています。エンジンのタイプは、パラメータ

internal_tmp_disk_storage_engine

によって制御されます。一般に、order by、group by、distinct などを含む少し複雑なクエリでは、この暗黙的に作成された一時テーブルが使用されます。ユーザーは Explain コマンドを使用して、「追加」列に「一時テーブルの使用」のような単語があるかどうかを確認できます。ある場合は、一時テーブルを使用する必要があります。 5.6 では、暗黙的な一時テーブルはまだ tmpdir の下にあります。複雑な SQL の実行中に、この一時テーブルが表示されます。実行が完了すると、このテーブルは削除されます。 5.6 では、この暗黙的に作成された一時テーブルは MyISAM エンジンでのみ使用できる、つまり、制御する

internal_tmp_disk_storage_engine

パラメータがないことに注意してください。したがって、システムに innodb テーブルしかない場合、MyISAM の変化を示す兆候もいくつか見られますが、この場合、通常は暗黙的な一時テーブルが原因です。 5.7 では、暗黙的な一時テーブルが ibtmp ファイルに作成されます。SQL が完了すると、削除対象としてマークされますが、スペースはオペレーティング システムに返されません。必要な場合は、が返された場合は、データベースを再起動する必要があります。さらに、5.7 はパラメータ

internal_tmp_disk_storage_engine### をサポートしており、ユーザーはディスク一時テーブルとして InnoDB または MYISAM テーブルを選択できます。 ###

8.0 では、暗黙的な一時テーブルがセッション一時テーブルスペースに作成されます。つまり、ユーザーが明示的に作成した一時テーブルのデータと一緒に配置されます。接続に初めて暗黙的な一時テーブルが必要な場合、データベースは、接続が解放されるまで接続で使用する ibt ファイルで構成されるプールからテーブルを取得します。上で述べたように、8.0 では、ユーザーによって明示的に作成された一時テーブルにも、使用するためにプールから ibt が割り当てられることにも言及しましたが、各接続は一時テーブルを保存するために最大 2 つの ibt ファイルを使用します。 INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES をクエリして、ibt ファイルの場所を確認できます。このテーブルでは、各 ibt ファイルは 1 行であり、現在のシステムでは複数の ibt ファイルに対して複数の行が存在します。 ID という列があります。この列が 0 の場合、この ibt は使用されていないことを意味します。0 以外の場合、この ID での接続が使用中であることを意味します。たとえば、ID が 8 の場合、これは、process_id 8 の接続がこの ibt ファイルを使用していることを意味します。さらに、目的列があり、値 INTRINSIC は暗黙的な一時テーブルがこの ibt を使用していることを示し、USER は表示された一時テーブルが使用中であることを示します。さらに、現在のサイズを示す列サイズがあります。ユーザーはこのテーブルをクエリして、データベース全体での一時テーブルの使用状況を確認できます。これは非常に便利です。

5.6 および 5.7 では、メモリ一時テーブルはメモリ エンジンのみを使用できますが、8.0 では、Temptable エンジンの選択肢が追加されました。 Temptable は、ストレージ形式で可変長ストレージを使用するため、ストレージ領域を節約し、メモリ使用量をさらに改善し、ディスク一時テーブルへの変換の数を減らすことができます。ディスク一時テーブル セットが InnoDB または MYISAM の場合、変換コピーが必要です。消費量をできる限り削減するために、Temptable はオーバーフロー メカニズムを提案しています。つまり、メモリ一時テーブルが設定されたサイズを超えた場合、ディスク領域マップ方式が使用されます。つまり、ファイルが開かれてから削除され、ファイルが残されます。読み取りおよび書き込み操作用のハンドル。ファイルの読み取りおよび書き込みの形式はメモリ内の形式と同じであるため、変換手順がスキップされ、パフォーマンスがさらに向上します。この機能はまだリリースされていないバージョン 8.0.16 でのみ利用可能であり、コードはまだ表示されていないため、ドキュメントからその実装を推測することしかできません。 8.0.16 では、パラメータ internal_tmp_disk_storage_engine が削除され、ディスク一時テーブルは InnoDB 形式または TempTable のオーバーフロー形式のみを使用できます。ドキュメントによると、公式の推奨事項は新しいエンジン TempTable を使用することであるようです。結論を出す前に、コードのリリース後に特定のパフォーマンスの改善をテストする必要があります。

一時ファイル

一時テーブルに比べて、一時ファイルはあまり馴染みがないかもしれませんが、一時ファイルはデータのキャッシュやデータの並べ替えのシナリオでよく使用されます。通常、キャッシュまたはソートされたデータはまずメモリに配置されますが、メモリに格納できない場合は、ディスク上の一時ファイルが使用されます。一時ファイルの使い方は一般的なテーブルとは異なります。一般的なテーブルは作成後、データの読み書きを開始し、使用後にファイルは削除されます。ただし、一時ファイルの使い方は一般的なテーブルの作成後と異なります。 ( mkstemp システム関数を使用)、すぐに unlink を呼び出してファイルを削除しますが、ファイルを閉じずに、元のハンドルを使用してファイルを操作します。この利点は、プロセスが異常終了したときに一時ファイルが削除されないため残らないことですが、ファイル システム上で ls コマンドを使用するときにこのファイルが表示されないという欠点も明らかです。このファイルを表示するには、lsof L1 を使用してください。削除された属性を持つファイルです。

現在、主に次のシナリオで一時ファイルを使用しています。

DDL の一時ファイル

オンライン DDL を実行する過程で、多くの操作 元のテーブルを再構築する必要がある テーブルを再構築する前に、さまざまなセカンダリ インデックスをソートする必要がある 大量のデータのソートはメモリ内で完了する可能性が低く、外部ソート アルゴリズムが必要です MySQL はマージ ソートを使用しますこのプロセス中に、一時ファイルを作成する必要があります。通常、必要なスペースは元のテーブルとほぼ同じです。ただし、使用後はすぐにクリーンアップされるため、DDL を実行する場合は十分な領域を確保する必要があります。ユーザーは、innodb_tmpdir を指定することで、この並べ替えファイルへのパスを指定できます。このパラメータは動的に変更でき、通常は十分なディスク容量のあるパスに設定されます。一時ファイルの名前は通常、ibXXXXXX に似ています。ib は固定プレフィックス、XXXXXX は大文字、小文字、数字のランダムな組み合わせです。 。

オンライン DDL を実行する場合、ユーザーは元のテーブルに対して DML 操作 (追加、削除、変更、クエリ) を実行できます。元のテーブルに直接挿入することはできないため、元のテーブルの変更操作を記録し、DDL の完了後に新しいテーブルに適用する場所が必要です。これが記録される場所はオンライン ログです。もちろん、変更がほとんどない場合は、メモリに直接保存できます (パラメータ innodb_sort_buffer_size を制御でき、このパラメータはファイルのサイズも制御します)オンライン ログの各読み取りブロックと書き込みブロック)。このオンラインログは、innodb_tmpdir に作成された一時ファイルにも保存されます。最大サイズは、パラメータ innodb_online_alter_log_max_size によって制御されます。このサイズを超えると、DDL は失敗します。一時ファイルの名前も、上記のソート用一時ファイルの名前と同様です。

オンライン DDL の最終段階では、プロセス中に生成されたすべての並べ替えられたファイルと DML を中間ファイルに適用する必要があります。中間ファイルの名前は #sql-ib53-522550444.ibd# のようになります。 ##、#sql-ib は固定プレフィックス、53 は InnoDB レイヤーのテーブル ID、522550444 はランダムに生成された番号です。同時に、frm ファイルもサーバー層で生成されます (8.0 では利用できません)。ファイル名は #sql-4d2b_2a.frm のようなものになります。#sql は固定プレフィックス、4d2b はプロセス番号の 16 進表現、2a はスレッド番号 (show processlist の ID) の 16 進表現です。したがって、この命名規則を使用して、どのスレッドが DDL を実行しているかを調べることもできます。ここで注意すべき点は、ここで言及されている中間ファイルは実際には一時テーブルであり、上記の一時ファイルではないということです。これらの中間ファイルは ls を通じて表示できます。 DDL の最後のステップでは、2 つの一時ファイルの名前が元のテーブル名に戻されます。この機能により、データベースが途中でクラッシュした場合、ディスク上に無駄なファイルが残ることがあります。この場合、まず frm ファイルの名前を ibd ファイルと同じ名前に変更してから、DROP TABLE#mysql50##sql-ib53-522550444` を使用して残りのファイルをクリーンアップします。 Drop コマンドを使用せずに ibd ファイルを直接削除すると、データ ディクショナリに情報が残る可能性があり、あまり洗練されていないことに注意してください。もちろん、8.0 ではアトミック データ ディクショナリを使用しているため、そのような残留ファイルは表示されません。

BinLog でのキャッシュ操作

BinLog は、トランザクションが送信されるときにのみファイルに書き込まれます。送信前に、メモリに配置されます (パラメーターによって制御されます)

binlog_cache_size)、メモリが遅くなると一時ファイルが作成されるので、mkstempで一時ファイルを作成し、直接リンクを解除して読み書き用のハンドルを残すという使い方になります。一時ファイル名は MLXXXXXX に似ています。ML は固定プレフィックス、XXXXXX は大文字、小文字、数字のランダムな組み合わせです。 1 つのトランザクションの BinLog が大きすぎると、BinLog 全体のサイズが大きくなりすぎて同期に影響を与える可能性があるため、トランザクション サイズを可能な限り制御する必要があります。

作成された一時ファイルの最適化

複雑な SQL の計算を支援するために、エンジン層での暗黙的な一時テーブルに依存するだけでなく、一部の操作も実行されます。サーバー層で作成される 一時ファイルは、filesort 関数を呼び出す操作による順序付けなどを支援するために使用されます。この関数も、最初にメモリ (sort_buffer_size) を使用して並べ替えを行いますが、十分ではない場合は、並べ替えを支援するための一時ファイルを作成します。ファイル名は

MYXXXXXX に似ています。MY は固定プレフィックス、XXXXXX は大文字、小文字、数字のランダムな組み合わせです。

データのロードで使用される一時ファイル

BinLog レプリケーションで、データのロード コマンドがメイン データベースで使用される場合、つまりデータがファイルからインポートされる場合、データベースはファイル全体を RelayLog に書き込み、スタンバイ データベースに転送します。スタンバイ データベースは RelayLog を解析し、対応するロード ファイルを抽出して、スタンバイ データベースに適用します。このファイルがスタンバイ データベースに保存される場所は、パラメータ

slave_load_tmpdir によって制御されます。このドキュメントでは、このディレクトリを物理マシンのメモリ ディレクトリや再起動後に削除されるディレクトリに設定しないことを推奨しています。レプリケーションはこのファイルに依存しているため、このファイルを誤って削除すると、レプリケーションが中断されます。

その他

上記のいくつかの場所に加えて、一時ファイルが使用される場所もいくつかあります。

• InnoDB レイヤーでは、起動時に複数の一時ファイルが作成され、最後の外部キーまたは一意キーのエラー、最後のデッドロック情報、最後の innodb ステータス情報が保存されます。メモリの代わりに一時ファイルを使用する理由は、これらのインジケーターの書き込みによってメモリ使用量が変動しないためです。
• サーバー層では、パーティション テーブルに show create table が使用されるときに一時ファイルが使用されます。さらに、一時ファイルは、MYISAM テーブルで内部的にソートするときにも使用されます。

関連パラメータ

*** tmpdir: *** このパラメータは一時ディレクトリの構成です。5.6 以前のバージョンでは、一時テーブル/ファイルは次のようにしてここに配置されます。デフォルト。このパラメータは複数のディレクトリで構成できるため、一時テーブル/ファイルを異なるディレクトリに順番に作成できます。異なるディレクトリが異なるディスクを指す場合、オフロードの目的を達成できます。
*** innodb_tmpdir: *** このパラメーターは、DDL で一時ファイルをソートする場合にのみ使用されます。多くのスペースを必要とするため、個別に設定することをお勧めします。このパラメータは動的に設定でき、セッション変数でもあります。
*** smile_load_tmpdir: *** このパラメータは主に、ロード データが BinLog でレプリケートされるときに、バックアップ ライブラリの一時ファイルの場所を構成するときに使用されます。データベースはクラッシュ後もデータ ファイルのロードに依存する必要があるため、再起動後にデータを削除するディレクトリを構成しないことをお勧めします。
*** external_tmp_disk_storage_engine: *** 暗黙的一時テーブルがディスク一時テーブルに変換されるときに使用されるエンジンは、デフォルトは MyISAM と InnoDB のみです。バージョン 5.7 以降でのみサポートされます。このパラメータはバージョン 8.0.16 以降キャンセルされました。
*** external_tmp_mem_storage_engine: *** 暗黙的一時テーブルがメモリ内にあるときに使用されるストレージ エンジン。メモリ エンジンまたは Temptable エンジンを選択できます。新しい Temptable エンジンを選択することをお勧めします。
***default_tmp_storage_engine:*** デフォルトの明示的一時テーブル エンジン、つまり、SQL ステートメントを通じてユーザーが作成した一時テーブルのエンジンです。
*** tmp_table_size: *** min(tmp_table_size,max_heap_table_size) は暗黙的一時テーブルのメモリサイズで、この値を超える場合はディスク一時テーブルに変換されます。
*** max_heap_table_size: *** ユーザーが作成した Memory メモリ テーブルのメモリ制限サイズ。
*** big_tables: *** メモリ一時テーブルをディスク一時テーブルに変換するには、異なるエンジン形式に変換する必要がある変換操作が必要です。これは消費されます。特定の SQL を実行するにはディスク一時テーブルが必要であることが事前にわかっている場合、つまりメモリが明らかに十分ではない場合は、オプティマイザがメモリ一時テーブルの使用をスキップしてディスクを直接使用するようにこのパラメータを設定できます。一時テーブルを使用してオーバーヘッドを削減します。
*** temptable_max_ram: *** このパラメータは 8.0 以降でのみ利用可能です。主に Temptable エンジンのメモリ サイズを指定するために使用されます。これを超えると、ディスク一時テーブルに変換されるか、オーバーフロー機構を内蔵。
*** temptable_use_mmap: *** Temptable のオーバーフロー メカニズムを使用するかどうか。

要約と提案

MySQL の一時テーブルと一時ファイルは実際には比較的複雑なトピックであり、多くのモジュールが関与しており、発生のタイミングを把握するのがより困難であるため、通常のテーブルと比較してトラブルシューティングの問題が発生します。テーブル、なかなか難しいですね。オンラインで発生する可能性のある難しい問題を見つけるために、コードを注意深く調べることをお勧めします。

以上がMySQL 学習用の一時テーブルの概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。