MySQL 5.7 の一時テーブルスペースを使用して落とし穴を回避する方法

MySQL 5.7 の一時テーブルスペースを使用して落とし穴を回避する方法

導入

MySQL 5.7 は、SSL/TLS と全体的なセキュリティ開発におけるいくつかの重要な変更により、これまでにリリースされた中で最も安全な MySQL サーバーになることを目指しています。

MySQL 5.7 以降では独立した一時表領域がサポートされていますが、問題が発生する場合があります。

MySQL 5.7 以降では、独立した一時テーブルスペースが使用されます (独立した UNDO テーブルスペースとは異なります)。ファイルの名前は ibtmp1 で、12 MB に初期化され、デフォルトでは上限はありません。

オプション innodb_temp_data_file_path は、一時表領域関連のパラメータを構成します。

innodb_temp_data_file_path = ibtmp1:12M:自動拡張

一時表領域に関する注意事項

  • 一時表領域は、通常の InnoDB 表領域のような RAW デバイスをサポートしません。
  • 一時表領域は動的表領域 ID を使用するため、再起動のたびに変更されます (一時表領域ファイルは再起動のたびに再初期化されます)。
  • オプション設定が正しくない場合、またはその他の理由 (権限不足など) により一時テーブルスペースを作成できない場合は、mysqld インスタンスも起動できません。
  • 一時テーブルスペースには、圧縮されていない InnoDB 一時テーブルが格納されます。圧縮された InnoDB 一時テーブルの場合は、tmpdir (/tmp) ディレクトリに格納されている独自のテーブルスペース ファイルに個別に格納する必要があります。
  • 一時テーブルのメタデータは、INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO ビューに保存されます。

SQL リクエストを実行すると、一時テーブルが生成されることがあります。極端な場合、一時テーブル スペース ファイルが突然増加することがあります。私がサポートしたケースでは、最大増加は 300G 近くで、これは私がこれまでに経験した ibdata1 ファイルの突然の増加よりもさらに深刻です...

一時テーブルの使用に関する提案

  • innodb_temp_data_file_path オプションを設定して、最大ファイルサイズを設定します。サイズが最大値を超えると、一時テーブルを生成する必要がある SQL 文は実行できなくなります (一般に、このような SQL 文の効率も比較的低いため、この機会に最適化することができます)。
  • INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO をチェックし、最大の一時テーブルに対応するスレッドを見つけて、それを強制終了して解放します。ただし、ibtmp1 ファイルは解放できません (システムを再起動しない限り)。
  • ibtmp1 ファイルを解放するには、適切なタイミングでインスタンスを再起動します。ibdata1 とは異なり、ibtmp1 は再起動時に再初期化されますが、ibdata1 は再初期化できません。
  • N 秒以上 (たとえば、N = 300) 実行されている SQL ステートメントを定期的にチェックし、ジャンク SQL ステートメントが長時間実行されてビジネスに影響を与えるのを防ぐために、それらのステートメントを強制終了することを検討してください。

添付ファイル: 一時テーブルテストケース

テーブルDDL

一時テーブル `tmp1` を作成します (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `name` varchar(50) NOT NULL デフォルト ''
 `aid` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 `nid` int(11) unsigned 常に ((`id` + 1)) VIRTUAL NOT NULL として生成されます。
 `nnid` int(11) unsigned 常に ((`id` + 1)) として生成され、NULL として保存されません。
 主キー (`aid`)、
 キー `name` (`name`),
 キー `id` (`id`),
 キー `nid` (`nid`)
) エンジン=InnoDB デフォルト文字セット=utf8

元のテーブルのサイズはわずか 120 MB です。このテーブルから直接 INSERT...SELECT を実行して、tmp1 テーブルにデータをインポートします。

-rw-r----- 1年前 imysql 120M 4月14日 10:52 /data/mysql/test/sid.ibd

一時テーブルを生成し(仮想列を削除、一時テーブルは仮想列をサポートしていないため、データを書き込みます)、さらに大きくなります(理解できません。理由は後で調べます)。

-rw-r----- 1 yejr imysql 140M 6月25日 09:55 /Users/yejinrong/mydata/ibtmp1

一時テーブルのメタデータ情報を表示する

[email protected] [テスト]>* を選択 
 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO_G
********************** 1. 行 ************************
   テーブルID: 405
    名前: #sql14032_300000005_3
    列数: 6
    スペース: 421
PER_TABLE_TABLESPACE: 偽
  IS_COMPRESSED: 偽

インデックスを再度削除すると、さらに大きくなります。

-rw-r----- 1年前 imysql 204M 6月25日 09:57 /data/mysql/ibtmp1

2回目のテストでインデックスを削除したら200Mになった(2回目のテストでは一時テーブルの最大サイズを200Mに設定していたため)

innodb_temp_data_file_path = ibtmp1:12M:自動拡張:最大:200M
-rw-r----- 1年前 imysql 200M 6月25日 10:15 /data/mysql/ibtmp1

一時テーブルを生成する低速の SQL ステートメントを実行します。

注意: MySQL 5.7 以降では、UNION ALL を実行しても一時テーブルは生成されなくなりました (追加のソートが必要な場合を除く)。

[email protected] [テスト]> select * from tmp1 union の説明 
 sid\G から id、name、aid を選択
************************** 1. 行 ****************************
   id: 1
 選択タイプ: プライマリ
  テーブル: tmp1
 パーティション: NULL
   タイプ: すべて
可能なキー: NULL
   キー: NULL
  キー長さ: NULL
   参照: NULL
   行数: 3986232
  フィルター: 100.00
  追加: NULL
************************** 2. 行 ****************************
   id: 2
 選択タイプ: UNION
  テーブル: sid
 パーティション: NULL
   タイプ: すべて
可能なキー: NULL
   キー: NULL
  キー長さ: NULL
   参照: NULL
   行数: 802682
  フィルター: 100.00
  追加: NULL
************************** 3. 行 ****************************
   id: NULL
 select_type: UNION 結果
  テーブル: <union1,2>
 パーティション: NULL
   タイプ: すべて
可能なキー: NULL
   キー: NULL
  キー長さ: NULL
   参照: NULL
   行: NULL
  フィルター: NULL
  追加: 一時的な使用

ファイルサイズが588Mに増加しましたが、まだ完了していません。

-rw-r----- 1年前 imysql 588M 6月25日 10:07 /data/mysql/ibtmp1

2 回目のテストでは、一時表領域ファイルの最大サイズが 200 MB に設定され、再度実行したときにエラーが報告されました。

[email protected] [テスト]>tmp1 ユニオンから * を選択 
 sid から id、name、aid を選択します。
エラー 1114 (HY000): テーブル '/var/folders/bv/j4tjn6k54dj5jh1tl8yn6_y00000gn/T/#sql14032_5_8' がいっぱいです

要約する

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

参照する

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

FAQ シリーズ | ibdata1 ファイルのサイズが急上昇するのを防ぐ方法

一時ファイル

内部一時テーブル

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path

一時テーブルスペース

以下もご興味があるかもしれません:
  • Mysql の一時テーブルとパーティションテーブルの違いの詳細な説明
  • Mysql一時テーブルの原理と作成方法の分析
  • mysql 一時テーブルの使用状況の分析 [クエリ結果は一時テーブルに保存できます]
  • MySQL FAQ シリーズ: 一時テーブルを使用する場合
  • JDBC および MySQL 一時テーブルスペースの詳細な分析
  • MySQL 一時テーブルの簡単な使用法
  • MySQL における単一テーブルと複数テーブル、およびビューと一時テーブルに対する Update と Select の違い
  • MySQL の 2 種類の一時テーブルの使用方法の詳細な説明
  • MySQL の一時テーブルと派生テーブルについての簡単な説明
  • MySQL の一時テーブルの基本作成と使用のチュートリアル
  • MySQL の一時テーブルの基本的な使用方法
  • 一時テーブルを使用して MySQL クエリを高速化する方法
  • MySQL での一時テーブルの使用例

<<:  Nginx で limit_req_zone を使用して同じ IP へのアクセスを制限する方法

>>:  jsは古典的なマインスイーパゲームを実装します

推薦する

MySQLデータベースのロック機構の分析

同時アクセスの場合、非反復読み取りやその他の読み取り現象が発生する可能性があります。高い同時実行性に...

WeChatアプレットが左右連携を実現

この記事では、WeChatアプレットの左右連動を実現するための具体的なコードを参考までに紹介します。...

CSS3 は本当に SCSS に取って代わるのでしょうか?

Web ページのスタイル設定に関しては、プロジェクトで純粋な CSS または SCSS (および他...

MySQL 5.7.21 のインストールと設定方法のグラフィックチュートリアル (ウィンドウ)

ウィンドウ環境にmysql5.7.21をインストールします。詳細は次のとおりです。 1. MySQL...

...

IdeaでMySQLデータベースに接続すると中国語の文字化けが発生する問題

問題: JDBCを使用してMySQLデータベースに接続すると、中国語の文字を挿入すると文字化けした文...

Linux は n 日前のログとサンプルコマンドを自動的に削除します

1. ファイル削除コマンド:対応するディレクトリを検索します -mtime + 日数 -name &...

sshとは何ですか?使い方は?どのような誤解があるのでしょうか?

目次序文SSHとは何かssh は何に使用されますか? sshの使い方ssh 再修正要約する序文ssh...

WeChatアプレットがシンプルな計算機機能を実装

WeChatアプレット:シンプルな計算機、参考までに、具体的な内容は次のとおりです。ミニプログラムに...

MySQL の異常なエラー ERROR: 2002 を解決する方法

最近、MySQL の起動中にエラーが発生しました。エラー メッセージは次のとおりです。 エラー 20...

UDP DUP タイムアウト UPD ポート状態検出コード例

以前、単純な UDP サーバーとクライアントの例を書きましたが、その中で、自分自身をクライアントと見...

スクロールラグの問題を解決するためのオーバーフロースクロールの詳細な説明

序文div またはモジュールに overflow: scroll 属性を使用すると、iOS フォンで...

Dockerコンテナのログ処理の詳細な説明

Docker には多くのログ プラグインがあります。デフォルトでは json-file を使用します...

Dockerコンテナでルート権限を取得する方法

まず、コンテナが稼働している必要がありますコンテナのCONTAINER IDは、sudo docke...