mysqlの一時テーブルの用途は何ですか?

mysql 一時テーブルの役割: 1. ユーザー自身が作成した一時テーブルは、一時データを保存するために使用されます; 2. ユーザーが複雑な SQL を実行するときに、一時テーブルを使用してグループ化、並べ替え、重複排除などを実行できます。また、デフォルトでは、データベースから切断すると、一時テーブルは自動的に破棄されます。

このチュートリアルの動作環境: Windows 10 システム、MySQL バージョン 5.7、Dell G3 コンピューター。

mysql 一時テーブルの用途は何ですか?

MySQL 一時テーブルの役割

#MySQL の一時テーブルは多くのシナリオで使用されます。たとえば、ユーザー自身が作成した一時テーブルは一時データの保存に使用されます。複雑な SQL を内部で実行する場合、MySQL はグループ化、並べ替え、重複排除などの操作に一時テーブルを使用する必要があります。 。以下では、MySQL 一時テーブルのいくつかの概念、分類、および一般的な問題を整理します。

MySQL 一時テーブル タイプ

1. 外部一時テーブル、一時テーブル作成構文を通じて作成された一時テーブル、ストレージ エンジンはこのようなテーブルは、memory、innodb、myisam などとして指定され、セッション終了後に自動的に消去されます。一時テーブルが非一時テーブルと同時に存在する場合、非一時テーブルは表示されません。 show tables コマンドは、一時テーブル情報を表示しません。
外部一時テーブルに関する情報は、information_schema.INNODB_TEMP_TABLE_INFO システム テーブルを通じて表示できますが、この部分はまだ比較的まれに使用されています。

2. 内部一時テーブルは通常、group by、order by、distinct、union などの複雑な SQL を実行するために使用されます。ただし、スペースが不十分な場合、MySQL は内部で自動的に生成された一時テーブルを使用して、作業の完了を支援します。

MySQL 一時テーブル関連パラメータ

1.max_heap_table_size: ユーザーが作成したメモリ テーブルの最大値。内部一時テーブルを制限するために tmp_table_size と一緒に使用されます。メモリ内のサイズ。
2.tmp_table_size: メモリ内の内部一時テーブルの最大値は max_heap_table_size パラメータとともに決定され、その 2 つの最小値が採用されます。一時テーブルがこの値を超えると、メモリからディスクに移動されます。
3.innodb_tmpdir: テーブルを再構築するオンライン ALTER TABLE 操作 max_tmp_tables

4.default_tmp_storage_engine: 外部一時テーブル (一時テーブルの作成によって作成されたテーブル) のデフォルトのストレージ エンジン。

5.innodb_temp_data_file_path: innodb エンジンの一時ファイル属性。 innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

6.Internal_tmp_disk_storage_engine: ディスク上の内部一時テーブル ストレージ エンジン、オプションの値は myisam または innodb に制限することをお勧めします。 innodb テーブルを使用する場合、一部のシナリオでは、たとえば、一時テーブルの列が多すぎる場合や行サイズが制限を超えている場合、「行サイズが大きすぎるか列が多すぎます」というエラーが発生することがあります。一時テーブルの innodb エンジンを myisam に戻す必要があります。 tmpdir: 一時テーブルのディレクトリ。一時テーブルのサイズが特定のしきい値を超えると、メモリからディスクに転送されます。
7. tmpdir 変数は、ディスク上の一時テーブルが配置されるディレクトリを示します。

MySQL 一時テーブル関連のステータス変数

1.Created_tmp_disk_tables: SQL ステートメントの実行時に MySQL によってディスク上に作成された内部一時テーブルの数。一時テーブルに割り当てられた最大メモリ値が小さいか、SQL でソート、グループ化、重複排除などの操作が多数あり、SQL を最適化する必要があることが考えられます。

2.Created_tmp_files: 作成された一時テーブルの数

3.Created_tmp_tables: SQL ステートメントの実行時に MySQL によって作成された内部一時テーブルの数。

4.Slave_open_temp_tables ステートメントまたはミックス モードは使用中のみ表示されます。
smile_open_temp_tables の値は、レプリケーションを通じて現在のスレーブによって作成された一時テーブルの数を示します。Binlog_format はステートメントおよび混合下でのみ有効です。
注: stop スレーブは役に立ちません。メイン ライブラリを手動で削除するか、セッションは終了する必要があります。終了できます。
以下はデータベースからの binlog レコード情報です:

MySQL 一時テーブルの注意事項

1. MySQL 一時テーブルにより、利用可能なディスク領域が減少する可能性があります:
MySQL バージョン 5.7 より前では、一時テーブルのストレージ エンジンはデフォルトで myisam に設定されていました。myisam 一時テーブルは、 SQL の実行が完了しました。ただし、バージョン 5.7 からは、一時テーブルのデフォルトのストレージ エンジンが innodb に変更され、パフォーマンスはある程度向上しましたが、innodb エンジンの一時テーブルはテーブル スペース ibtmp1 を共有するため、複数のセッションで一時テーブルが作成されます。同時に、高い同時実行性のテーブルでは、テーブルスペースが非常に大きくなり、動的に削減できず、MySQL を再起動しない限り解放できません。

次のように、一時テーブルスペースの最大値 (10G など) を設定できます。
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
一時テーブル領域が最大値に達しました。値が 10G の場合、SQL の実行でエラーが報告され、アプリケーションの通常の実行に影響します。
過剰な一時テーブル領域の問題については、通常、次のような他の方法で問題を解決できます:
一時テーブルのストレージ エンジンを myisam に設定します。パフォーマンス上の問題が発生する可能性がありますが、問題は発生しません。ディスク容量の問題を引き起こします。

2.SQL ステートメント:
(1) 適切なインデックスを追加します
(2) where 条件でさらにデータをフィルターします
(3) SQL を書き換えて実行計画を最適化します
(4) ) 一時テーブルを使用する必要がある場合は、同時実行性を下げる必要があります。 SSDハードドライブの使用をお勧めします。

3.undo 関連
1) innodb_rollback_segments 構成オプションを使用して、ロールバック セグメントの数を定義します。デフォルト設定は 128 で、これは最大値でもあります。 1 つのロールバック セグメントは常にシステム表スペースに割り当てられ、32 のロールバック セグメントは一時表スペース (ibtmp1) 用に予約されます。したがって、テーブルスペースを元に戻すためにロールバック セグメントを割り当てるには、innodb_rollback_segments を 33 より大きい値に設定します。別の UNDO 表領域を構成すると、システム表領域内のロールバック セグメントが非アクティブになります。

つまり、ロールバック セグメントが 128 を超える場合、緊急救済のために一時テーブルが必要になります。

tablespace -> segment -> extent(64个page，1M) -> page（16kb）

2) truncate undo
innodb_undo_log_truncate がトリガーされると、UNDO テーブルスペースの切り捨て操作によって、サーバーのログディレクトリに一時的な undo_space_number_trunc.log ファイルが作成されます。このファイルは、innodb_log_group_home_dir によって定義されます。切り捨て操作中にシステム障害が発生した場合、起動プロセスは一時ログ・ファイルによって切り捨てられたUNDO表領域を認識し、操作を続行できます。

4.binlog キャッシュ関連
バイナリ ログ キャッシュを使用すると、値が binlog_cache_size で設定された値に達し、トランザクションの変更によるトランザクション数を保存するために一時ファイルを使用します。 Binlog_stmt_cache_disk_use ステータス変数を通じて個別に追跡できます。

概要

1. 上記の理解から、日常的な監視と最適化において、MySQL 一時テーブルは避けて通れません。 ＃＃＃＃２ また、中間テーブルとして少量の情報を一時的に保存するなど、業務実装においても適切に活用することができます。
3. レプリケーション プロセス中に binlog_format が ROW モードに等しい場合、一時テーブルに関連する binlog ログは記録されません (drop コマンドを除く)。この部分には注意が必要です。
【関連する推奨事項:
mysql ビデオ チュートリアル ]

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