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

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 を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQLの自動増分主キーIDはこのように処理されません
  • Mysql 主キー UUID と自動増分主キーの違いと利点と欠点
  • MySQLでデータを挿入した後に自動増分IDを返す7つの方法の詳細な説明
  • MySQL の自動増分 ID (主キー) が不足した場合の解決策
  • MySQLの自動増分IDについて知っておくべきこと
  • MySQL テーブル自動増分 ID オーバーフロー障害レビュー ソリューション
  • MySQL の自動増分 ID に関するいくつかの小さな問題の要約
  • MySQL IDは1から増加し始め、不連続IDの問題を素早く解決します

<<:  Linux でジャンプ サーバー経由でリモート サーバーに接続し、ファイルを転送する方法

>>:  Vueログイン機能の実装

推薦する

JavaScriptは検証コードと検証のランダム生成を実装します

この記事では、検証コードのランダム生成と検証を実現するためのJavaScriptの具体的なコードを参...

Linuxでスクリーンショットを撮って編集するための最高のツール

メインのオペレーティング システムを Windows から Ubuntu に切り替えたとき、最初に考...

スマートCSSを使用して、ユーザーのスクロール位置に基づいてスタイルを適用します。

現在のスクロール オフセットを html 要素の属性に追加することで、現在のスクロール位置に基づいて...

MySQL5.6.31 winx64.zip インストールと設定のチュートリアル

#1. ダウンロード # #2. ローカルに解凍し、必要な構成のmy*.iniを変更します。 #3....

Win10 64ビットMySQL8.0のダウンロードとインストールのチュートリアル図

公式サイトから MySQL をダウンロードしてインストールし、クライアントにログインするにはどうすれ...

Vueモバイル端末に最適な適応ソリューションについての簡単な説明

序文: 最近の医療モバイル プロジェクトに基づいて、Vue はさまざまな画面のさまざまな画面サイズに...

今日と昨日の 0:00 タイムスタンプを取得する MySQL の例

以下のように表示されます。昨日: UNIX_TIMESTAMP(CAST(SYSDATE() AS ...

HTML タグのセマンティクス化 (H5 を含む)

導入HTML は、Web ドキュメントのコンテンツのコンテキスト構造と意味を提供します。HTML 自...

Mysql: 定義者として指定されたユーザー ('xxx@'%') が存在しません 解決策

本日のプロジェクト最適化中に、MySQL に問題が発生しました: 定義者として指定されたユーザー (...

Javascriptの基本を詳しく説明

目次変数データ型拡張ポイント要約する変数基本的な構文 var age=10; //ageという変数を...

Mysql でよく使用される時間、日付、変換関数の概要

この記事では、主に実際のアプリケーションでよく使用されるMySQLの時刻と日付、および変換関数につい...

Vue カスタム オプション時間カレンダー コンポーネント

この記事の例では、参考のためにvueカスタムオプションタイムカレンダーコンポーネントの具体的なコード...

MySQL 5.7.17 圧縮パッケージのインストール不要の構成プロセス図

MySQL データベース管理ソフトウェアには、エンタープライズ エディションとコミュニティ エディシ...

VPSサーバーでよく使われるパフォーマンステストスクリプトの概要

これは、VPS サーバー用の一般的なワンクリック パフォーマンス テスト スクリプトです。マシンの構...

HTML5で見逃せないAPIやヒントのまとめ

これまでのブログ投稿では、HTML 5 ではあまり使われていないが注目すべき API やヒントに焦点...