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

一時テーブルスペース