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は古典的なマインスイーパゲームを実装します

推薦する

Nginx でバージョン番号を隠す方法

Nginx はバージョン番号を非表示にする実稼働環境では、セキュリティ上の脆弱性の漏洩を避けるために...

JavaScript のガベージコレクションの仕組みの詳細な説明

目次ガベージコレクション (GC) はなぜ必要なのでしょうか?ガベージコレクションとは廃棄物の発生ガ...

MySQL 8.0 の降順インデックス

序文インデックスが順序付けられていることは誰もが知っていると思いますが、MySQL の以前のバージョ...

MySQLアカウントのパスワード変更方法(概要)

序文:データベースを日常的に使用すると、パスワードが単純すぎて変更する必要がある場合、パスワードの有...

Linux で rsync を使用する方法

目次1. はじめに2. インストール3. 基本的な使い方3.1、-rパラメータ3.2、-aパラメータ...

ウェブページの読み込み速度を上げる25の方法とヒント

はじめに<br />誰もが高速インターネット接続にアクセスできるわけではありません。たと...

MySQLユーザーと権限管理の詳細な説明

この記事では、例を使用して MySQL ユーザーと権限の管理について説明します。ご参考までに、詳細は...

mysql 8.0.16 winx64.zip インストールと設定方法のグラフィックチュートリアル

この記事では、MySQL 8.0.16 winx64.zipのインストールと設定方法の具体的なコード...

Dell R720 サーバーに Windows Server 2008 R2 をインストールする方法

注: この記事のすべての写真はインターネットから収集されたものであるため、DELL R720 サーバ...

ウェブサイトのBGM実装方法

個々のウェブマスターにとって、自分のウェブサイトをいかにユニークで個性あふれるものにするかは、常に絶...

無効にしてHTMLフォーム入力を送信した後にフォーム値が取得されない問題を解決する方法

フォーム入力ボックスの入力をdisable属性に設定して送信すると、入力ボックスの値を取得できなくな...

MySQL 8.0 のインストール中に発生した 3 つの小さなエラーの概要

序文これまで当社ではMySQLの5.7シリーズを使用していましたが、バージョン8.0のリリースに合わ...

HTML4.0 要素のデフォルトスタイルの配置

コードをコピーコードは次のとおりです。 html、アドレス、引用ブロック、本文、dd、div、 dl...

VMware での Linux CentOS6.9 インストール グラフィック チュートリアル

技術初心者として、初めて Linux システムをインストールするプロセスを記録しています。まず、Wi...

Linux Centos8 CA証明書作成チュートリアル

必要なファイルをインストールする Yum インストール openssl-* -yデータベースインデッ...