MySQL での一時テーブルの使用例

ここ2日間ちょっと忙しくて、公式アカウントも数日更新が止まってしまいました。その結果、何人かの読者から再度更新してほしいという声が上がりました。まあ、まだ注目してくれている人がいるという証拠です。自分にも他人にもメリットがあり、良いことです。

今日お話ししたいのは、MySQL の一時テーブルについてです。これまで一時テーブルについてはあまり調べたことがありませんでした。MySQL では、特定のシナリオでグループ化やその他の操作を支援するために一時テーブルが使用されるということだけは知っています。今日は一時テーブルについて学びましょう。

1. まず第一に。一時テーブルはセッション レベルにあります。現在のセッションで作成されたテーブルは、他のセッションでは表示されません。

セッション 1:

mysql> 一時テーブル test3 (id_tmp int) を作成します。engine=innodb;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

セッション2:

mysql> テーブル test3\G の作成を表示します
エラー 1146 (42S02): テーブル 'test.test3' が存在しません

2. セッションでは、一時テーブルの名前は正式テーブルと同じになる場合があります。

mysql> テーブル test2 (id int) を作成します。engine=innodb;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> 一時テーブル test2 (id_tmp int) を作成します。engine=innodb;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

同じ名前の test2 テーブルを作成したときにエラーが発生しなかったことがわかります。

3. データベースに物理テーブルと一時テーブルがある場合は、show create table を使用して一時テーブルの内容を表示します。

mysql> テーブル test2\G の作成を表示します
************************** 1. 行 ****************************
    表: test2
テーブルの作成: CREATE TEMPORARY TABLE `test2` (
 `id_tmp` int(11) デフォルト NULL
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

4. 一時テーブルが削除された後、show create table は物理テーブルの内容をチェックします。

mysql> "test2"のようなテーブルを表示します。
+------------------------+
| テスト内のテーブル (test2) |
+------------------------+
| テスト2 |
+------------------------+
セット内の 1 行 (0.00 秒)

mysql> テーブル test2 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> "test2"のようなテーブルを表示します。
+------------------------+
| テスト内のテーブル (test2) |
+------------------------+
| テスト2 |
+------------------------+
セット内の 1 行 (0.00 秒)

5. show tables コマンドでは一時テーブルを表示できません。

6. 異なるセッションで同じ名前の一時テーブルを作成できます。

7. 一時テーブルの保存方法

MySQL では、.frm はテーブル構造を保存するために使用され、.ibd はテーブル データを保存するために使用されます。.frm ファイルは通常、tmpdir パラメータで指定されたディレクトリに配置されます。デスクトップ Windows プラットフォーム上の MySQL は次のとおりです。

mysql> "%tmpdir%"のような変数を表示します。
+-------------------+-------------------------------------------------+
| 変数名 | 値 |
+-------------------+-------------------------------------------------+
| innodb_tmpdir | |
| スレーブロードtmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
| tmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
+-------------------+-------------------------------------------------+
セットに 3 行、警告 1 件 (0.01 秒)

MySQL 5.6 では、一時テーブルを保存するために .ibd ファイルが生成されます。

MySQL 5.7 では、一時ファイル データを格納するために一時ファイル テーブルスペースが導入されました。

異なるセッションを使用して同じ名前の一時テーブルを作成すると、一時テーブル ディレクトリに異なる名前の一時テーブル ファイルが存在することがわかります。

これらの一時テーブルは、メモリ内でリンク リストとして表されます。セッションに 2 つの一時テーブルが含まれている場合、MySQL は 2 つの一時テーブルを接続するために一時テーブルのリンク リストを作成します。実際の操作ロジックでは、SQL ステートメントを実行すると、MySQL は一時テーブルのリンク リストをトラバースして、SQL ステートメントで指定されたテーブル名の一時テーブルがあるかどうかを確認します。一時テーブルがある場合は、最初に一時テーブルが操作されます。一時テーブルがない場合は、通常の物理テーブルが操作されます。

8. マスタースレーブレプリケーションにおける一時テーブルに関する注意事項

一時テーブルはセッション レベルにあるため、セッションが終了すると削除されます。ただし、マスター ノードは一時テーブルを表示せず、セッションを閉じて削除します。では、スレーブ ノードは一時テーブルをいつ削除するかをどのようにして知るのでしょうか。

マスターノードが次の SQL を実行すると仮定します。

クレタテーブルtbl;
tbl のような一時テーブル tmp を作成します。
tmp値（0,0）に挿入します。
tbl に挿入します。tmp から * を選択します。

binlog=statement/mixed モードでは、一時テーブル関連の操作の binlog が記録されない場合、最後の挿入ステートメントでエラーが報告されます。 tmp テーブルが見つからないためです。この場合、MySQL の binlog は一時テーブルに対する操作を記録します。マスター データベースのセッションが閉じられると、マスター スレーブ データの一貫性を確保するために、一時テーブルを削除する SQL ステートメントが binlog に自動的に追加されます。

binlog=row モードでは、データのすべてのフィールドが binlog で見つかるため、一時テーブルに関連する SQL ステートメントは binlog に記録されません。最後の insert into select ステートメントの場合、binlog は (0,0) を tbl テーブルに挿入するレコードを記録します。

binlog=row モードでは、マスター データベースが drop table tmp コマンドを使用して一時テーブルを削除すると、binlog は一時テーブルに関連する操作を記録しないため、このレコードは無視されます。

9. 異なるスレッドからの同じ名前の一時テーブルがスレーブ データベースに同時に存在できるのはなぜですか?

一時テーブルはセッション レベルにあり、異なるセッション間の一時テーブルに同じ名前を付けることができることはわかっています。スレーブ ライブラリが binlog を再生するとき、スレーブ ライブラリは、同じ名前のこれらの一時テーブルがどのトランザクションに属しているかをどのように認識するのでしょうか。

この概念を理解するには、関数内の仮パラメータと実パラメータの概念を参照できます。仮パラメータと実パラメータは同じ名前を持つことができますが、割り当てられると、それらのポインタ値は異なります。したがって、同じ名前のパラメータの場合、ポインタ値が異なるため、コンパイラではエラーは発生しません。

MySQL はデータ テーブルを管理します。物理ファイルに加えて、メモリ内に異なるテーブルを区別するメカニズムもあります。各テーブルは table_def_key に対応します。 table_def_keyの値は「ライブラリ名+テーブル名+server_id+thread_id」で構成されます。thread_idが異なるため、ライブラリから操作しても競合は発生しません。

以上がMySQLのメモリ一時テーブルについての詳しい説明です。MySQLのメモリ一時テーブルについてさらに詳しく知りたい方は、123WORDPRESS.COMの他の関連記事もぜひご覧ください！