MySQL の一時テーブルに重複した名前が付けられる理由は何ですか?

今日は次の質問から始めます: 一時テーブルの特徴は何ですか? どのようなシナリオに適していますか?

ここで、最初に誤解されやすい問題を明確にする必要があります。一時テーブルはメモリ テーブルであると考える人もいるかもしれません。ただし、これら 2 つの概念はまったく異なります。

• メモリ テーブルは、Memory エンジンを使用するテーブルを指します。テーブルを作成するための構文は、create table …engine です。 =メモリ 。 **この種のテーブルのデータはメモリに保存され、システムの再起動時に消去されますが、テーブル構造はまだ存在します。 **「奇妙に見える」これら 2 つの機能を除けば、他の機能から見ると、これは通常のテーブルです。

• 一時テーブル、さまざまなエンジン タイプを使用できます。 InnoDB エンジンまたは MyISAM エンジンの一時テーブルを使用する場合、データは書き込み時にディスクに書き込まれます。もちろん、一時テーブルでもメモリ エンジンを使用できます。

メモリ テーブルと一時テーブルの違いを明確にした後、一時テーブルの特性を見てみましょう。

一時テーブルの特性

理解を容易にするために、次の一連の操作を見てみましょう:

できる限り一時テーブルを参照してください。これには次のような特徴があります:

• テーブルを作成するための構文は createtemporary table … です。

• 他のスレッドはセッションによって作成された一時テーブルにアクセスできず、そのセッションにのみ表示されます。したがって、図のセッション A によって作成された一時テーブル t は、セッション B には見えません。

• 一時テーブルには、通常のテーブルと同じ名前を付けることができます。

• セッション A に一時テーブルと同名の通常テーブルが存在する場合、showcreate ステートメントと add、delete、modify、query ステートメントは一時テーブルにアクセスします。

• showtables コマンドは一時テーブルを表示しません。

一時テーブルにはそれを作成したセッションのみがアクセスできるため、一時テーブル はセッションが終了すると自動的に削除されます。

一時テーブルにはこの機能があるため、前の記事の結合最適化シナリオは一時テーブルの使用に特に適しています。なぜ？理由は主に次の 2 つの側面が含まれます:

• 異なるセッションの一時テーブルは同じ名前を持つことができます. 複数のセッションが同時に結合の最適化を実行している場合テーブル名が重複するとテーブル作成に失敗しますので、ご安心ください。

• データの削除を心配する必要はありません。通常のテーブルを使用する場合、プロセス実行中にクライアントが異常切断された場合、またはデータベースが異常再起動された場合、中間プロセスで生成されたデータテーブルをクリーンアップする必要があります。一時テーブルは自動的にリサイクルされるため、この追加の操作は必要ありません。

一時テーブルの適用

スレッド間の重複名の競合を心配する必要がないため、一時テーブルは複雑なシステムの最適化プロセスでよく使用されます。クエリ。中でも、サブデータベースとサブテーブルシステムのクロスデータベースクエリは代表的な利用シナリオです。

データベースとテーブルのシャーディングの一般的なシナリオは、論理的に大きなテーブルを異なるデータベース インスタンスに分散することです。例えば。特定のフィールド f について、大きなテーブル ht を 1024 のサブテーブルに分割し、これらのサブテーブルを 32 のデータベース インスタンスに分散します。次の図に示すように:

# 通常、この種のサブデータベースおよびサブテーブル システムには中間層プロキシがあります。ただし、クライアントがデータベースに直接接続できる、つまりプロキシ層が存在しないソリューションもいくつかあります。

このアーキテクチャでは、パーティション キーの選択は、「クロスデータベースおよびクロステーブル操作を減らす」という原則に基づいています。ほとんどのステートメントに f と同等の条件が含まれる場合は、f をパーティション キーとして使用する必要があります。 SQL ステートメントを解析したプロキシは、クエリのためにどのテーブルにルーティングするかを決定します。

たとえば、次のステートメント:

select v from ht where f=N;

このとき、サブテーブル ルール (たとえば、N 24) を使用して、必要なデータがどのサブテーブルに配置されているかを確認できます。の上。この種のステートメントは 1 つのサブテーブルにアクセスするだけで済み、サブデータベースおよびサブテーブル スキームで最も一般的なステートメント形式です。

ただし、このテーブルに別のインデックス k があり、クエリ ステートメントが次のような場合:

select v from ht where k >= M order by t_modified desc limit 100;

このとき、パーティション フィールド f はクエリ条件に使用されていないため、すべてのパーティションで条件を満たすすべての行を検索し、操作による順序付けを均一に実行することしかできません。この場合、一般的に使用されるアイデアが 2 つあります。

最初のアイデアは です。これは、プロキシ層のプロセス コードでソートを実装します。この方法の利点は処理速度が速く、サブデータベースからデータを取得した後、メモリ上の計算に直接参加できることです。ただし、このソリューションの欠点も明らかです。

• は比較的大量の開発作業を必要とします。例として挙げたステートメントは比較的単純ですが、グループ化や結合などの複雑な操作が含まれる場合、中間層の開発能力は比較的高くなります。

• 对proxy端的压力比较大，尤其是很容易出现内存不够用和CPU瓶颈的问题。

另一种思路就是，把各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作。

比如上面这条语句，执行流程可以类似这样：

• 在汇总库上创建一个临时表temp_ht，表里包含三个字段v、k、t_modified；

• 在各个分库上执行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

• 把分库执行的结果插入到temp_ht表中；

• 执行select v from temp_ht order by t_modified desc limit 100;

得到结果。 这个过程对应的流程图如下所示：

在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库中的某一个上。

为什么临时表可以重名？

你可能会问，不同线程可以创建同名的临时表，这是怎么做到的呢？

我们在执行

create temporary table temp_t(id int primary key)engine=innodb;

这个语句的时候，MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_ {线程id}_ 序列号”。

从文件名的前缀规则，我们可以看到，其实创建一个叫作t1的InnoDB临时表，MySQL在存储上认为我们创建的表名跟普通表t1是不同的，因此同一个库下面已经有普通表t1的情况下，还是可以再创建一个临时表t1的。

先来举一个例子。

进程号为1234的进程，它的线程id分别为4和5，分别属于会话A和会话B。因此，可以看出，session A和session B创建的临时表在磁盘上的文件名不会冲突。

MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。

• 一个普通表的table_def_key的值是由“库名+表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。

• 而对于临时表，table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”。

也就是说，session A和session B创建的两个临时表t1，它们的table_def_key不同，磁盘文件名也不同，因此可以并存。

在实现上，每个线程都维护了自己的临时表链表。这样每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束的时候，对链表里的每个临时表，执行 “DROPTEMPORARY TABLE +表名”操作。

你会注意到，在binlog中也有DROP TEMPORARY TABLE命令的记录。你一定会觉得奇怪，临时表只在线程内自己可以访问，为什么需要写到binlog里面？这，就需要说到主备复制了。

临时表和主备复制

既然写binlog，就意味着备库需要。 你可以设想一下，在主库上执行下面这个语句序列：

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录，那么在备库就只有create table t_normal表和insert intot_normal select * fromtemp_t这两个语句的binlog日志，备库在执行到insert into t_normal的时候，就会报错“表temp_t不存在”。

你可能会说，如果把binlog设置为row格式就好了吧？因为binlog是row格式时，在记录insert intot_normal的binlog时，记录的是这个操作的数据，即：write_rowevent里面记录的逻辑是“插入一行数据（1,1)”。

确实是这样。如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog里。也就是说，只在binlog_format=statment/mixed的时候，binlog中才会记录临时表的操作。

在这种情况下，执行创建临时表语句的操作会被传递到备用数据库进行处理，从而触发备用数据库的同步线程创建相应的临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。因此，我们需要在主数据库中再运行一个DROP TEMPORARY TABLE命令以便备用数据库执行。

メイン データベース上の別のスレッドが同じ名前の一時テーブルを作成しても問題ありませんが、実行のためにスタンバイ データベースへの転送をどのように処理すればよいでしょうか?

ここで例を示します。次のシーケンスでは、インスタンス S が M のスタンバイ データベースです。

#メイン データベース M 上の 2 つのセッションで、同じ名前の一時テーブル t1 が作成されました。これら 2 つの一時テーブル t1 作成ステートメントは、スタンバイ データベース S に送信されます。

ただし、スタンバイ データベースのアプリケーション ログ スレッドは共有されるため、アプリケーション スレッドで create ステートメントを 2 回実行する必要があります。マルチスレッドのレプリケーションにもかかわらず、実行のためにスレーブ ライブラリ内の同じワーカーに割り当てられる可能性はあります。では、これにより同期スレッドはエラーを報告するのでしょうか?

明らかにそうではありません。そうでない場合、一時テーブルはバグになります。つまり、バックアップ スレッドは、実行中の処理のために 2 つの t1 テーブルを独立した一時テーブルとして扱う必要があります。これはどのようにして達成されるのでしょうか? MySQL はバイナリログを記録するときに、このステートメントを実行するメイン ライブラリのスレッド ID をバイナリログに書き込みます。このようにして、スタンバイ データベースのアプリケーション スレッドは、各ステートメントを実行するメイン データベース スレッド ID を認識し、このスレッド ID を使用して一時テーブルの table_def_key を構築できます:

• temporaryセッション A t1 のテーブル、スタンバイ データベースの table_def_key は次のとおりです: ライブラリ名 t1 "M のサーバー ID" "セッション A のスレッド ID";

• セッション B の一時テーブル t1、スタンバイ データベースの table_def_keyスタンバイ データベースは次のとおりです: ライブラリ名 t1 "M のサーバー ID" "セッション B の thread_id"。

table_def_key が異なるため、これら 2 つのテーブルはスタンバイ データベースのアプリケーション スレッドで競合しません。

以上がMySQL の一時テーブルに重複した名前が付けられる理由は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。