MySQL 結合テーブルと ID 自動増分の例の分析

結合の書き方

左結合を使用する場合、左側のテーブルが必ず駆動テーブルになりますか? 2 つのテーブルの結合に複数の等しい一致条件が含まれている場合、それらすべてを for に記述する必要がありますか、それとも 1 つだけを記述して残りを where 部分に記述する必要がありますか?

テーブル a(f1 int, f2 int, index(f1)) を作成します。engine=innodb;
 テーブル b(f1 int, f2 int) を作成します。engine=innodb;
 値(1,1),(2,2),(3,3),(4,4),(5,5),(6,6)を挿入します。
 b 値に (3,3)、(4,4)、(5,5)、(6,6)、(7,7)、(8,8) を挿入します。
a の左から * を選択し、b を (a.f1=b.f1) かつ (a.f2=b.f2) で結合します。/*Q1*/
 a の左から * を選択し、b を on(a.f1=b.f1) で結合し、(a.f2=b.f2) で結合します。/*Q2*/

実行結果:

テーブル b にはインデックスがないため、Block Nexted Loop Join (BNL) アルゴリズムが使用されます。

• テーブル a の内容を join_buffer に読み込みます。select * により、フィールド f1 と f2 の両方が join_buffer に格納されます。
• b を順番にスキャンします。データの各行について、結合条件が満たされているかどうかを判断します。条件を満たすレコードは、結果セットの行として取得されます。where 句がある場合は、返す前に where 部分が条件を満たしているかどうかを判断します。
• テーブル b のスキャンが完了すると、一致するものがないテーブル a の行は null で埋められ、結果セットに格納されます。

Q2 のステートメントで、結果を説明してください。

b は駆動テーブルです。ステートメントの EXTRA フィールドに何もない場合は、Index Nested_Loop Join アルゴリズムなので、プロセスは次のようになります。

b を順番にスキャンし、b.f1 を使用して a の各行をチェックし、a.f2=b.f2 が満たされているかどうかを照合し、結果セットとして返します。

Q1 と Q2 の実行プロセスの違いは、オプティマイザが Q2 のクエリ セマンティクスに基づいて最適化を行うためです。MySQL では、null と任意の値との等価性および不等価性の判定の結果は null であり、select null = null も null を返します。

Q2 で、a.f2 = b.f2 は、クエリ結果に b.f2 が null である行が含まれないことを意味します。左結合のセマンティクスは、f1 と f2 が互いに対応する 2 つのテーブルで同じ行を見つけることです。a は存在するが b が一致しない場合は、中止します。したがって、オプティマイザはこのステートメントの左結合を結合に書き換えます。a の f1 にはインデックスがあるため、b が駆動テーブルとして使用され、NLJ アルゴリズムを使用できます。したがって、左結合を使用する場合、左側のテーブルが必ずしも駆動テーブルであるとは限りません。

左結合のセマンティクスが必要な場合、駆動テーブルのフィールドは、等価性または不等価性の判断のための where 条件に配置することはできません。on 条件に記述する必要があります。

ネストループ結合によるパフォーマンスの問題

BLNアルゴリズム実行ロジック

• ドライバー テーブル内のすべてのデータを、順序付けられていない配列である join_buffer に読み取ります。
• 駆動テーブルのすべての行を順番に走査し、各行を join_buffer と照合し、成功した場合は結果セットの一部として返します。

シンプル ネスト ループ結合アルゴリズムのロジックは、駆動テーブル内の各データ行を順番に削除し、駆動テーブル内で完全なテーブル マッチングを実行することです。

両者の違い:

駆動テーブルでフルテーブルスキャンを実行するときに、データがバッファプールにない場合は、一部のデータがディスクから読み取られるまで待機する必要があります。これは、通常の業務のバッファ プールのヒット率に影響を与え、駆動テーブルへの複数回のアクセスを行うため、これらのデータ ページをバッファ プールの先頭に配置することが容易になります。したがって、BNL アルゴリズムのパフォーマンスは向上します。自動増分ID

MySQL の自動増分 ID は初期値を定義します。この値は増加し続けますが、上限は 2^32-1 です。自動増分 ID が使い果たされるとどうなるでしょうか?

テーブルに定義された自動増分値が上限に達した場合、次のIDを申請する際に取得される値は変更されません。再度挿入すると、主キーの競合エラーが報告されます。したがって、テーブルを作成するときに、追加、削除、変更が頻繁に行われる場合は、8 バイトの bigint unsigned を作成する必要があります。

Innodbシステムはrow_idを自動的に増加させる

主キーを指定せずに Innodb テーブルを作成すると、Innodb は 6 バイトの長さの非表示の row_id を作成します。主キーのないすべての Innodb テーブルでは、データ行が挿入されるたびに、現在の dict_sys.row_id 値が挿入されるデータの row_id として使用され、1 ずつ増加します。

実際、コードが実装されると、row_id は長さが 8 バイトの符号なし長整数になりますが、innodb の設計では row_id の長さは 6 バイトのみであるため、データを書き込むときには最後の 6 バイトのみが配置されます。それで：

• テーブルに書き込まれる row_id の範囲は 0 から 2^48-1 です。
• 最大値に達した場合、row_id に適用するデータを挿入する別の行為があると、最後の 6 バイトは取得後に 0 になり、サイクルが継続されます。
• InnoDB のロジックでは、最大ループに達すると、新しいデータによって既存のデータが上書きされます。

この観点から、上限に達した場合にデータ挿入時にエラーが報告されるように、自動インクリメント主キーを積極的に作成する必要があります。データの信頼性がより保証されます。

XID

redo ログと binlog が連携して動作する場合、それらには MySQL のトランザクションに対応する xid と呼ばれる共通フィールドがあります。 xid の最大値は 2^64 であり、理論上はそれが使い果たされた場合にのみ存在します。

スレッドID

システムはグローバル変数 thread_id_counter を保存します。新しい接続が作成されるたびに、thread_id_counter が新しい接続のスレッド変数に割り当てられます。 thread_id_counter のサイズは 4 バイトとして定義されているため、2^32-1 に達すると 0 にリセットされ、その後増加し続けます。ただし、show processlist には 2 つの同一の thread_ids は表示されません。これは、MySQL が新しいスレッドに thread_ids を割り当てるための独自の配列ロジックを設計しているためです。

する {
 		新しいID = スレッドIDカウンタ++;
 } while (!thread_ids.insert_unique(new_id).second);

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。