MySQL 自動インクリメント ID 枯渇の例

MySQL 自動インクリメント ID 枯渇の例

ディスプレイ定義ID

テーブルに定義された自動増分IDが上限に達した場合、次のIDを申請する際に得られる値は変更されません

-- (2^32-1) = 4,294,967,295
-- BIGINT UNSIGNEDの使用をお勧めします
テーブル t を作成します (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295;
t VALUES (null) に挿入します。

-- AUTO_INCREMENT は変更されていません mysql> SHOW CREATE TABLE t;
+-------+------------------------------------------------------+
| テーブル | テーブルの作成 |
+-------+------------------------------------------------------+
| t | テーブル `t` を作成する (
 `id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 デフォルト CHARSET=utf8 |
+-------+------------------------------------------------------+

mysql> t VALUES (null) に INSERT INTO します。
エラー 1062 (23000): キー 'PRIMARY' のエントリ '4294967295' が重複しています

InnoDB 行ID

1. 作成された InnoDB テーブルで主キーが指定されていない場合、InnoDB は 6 バイトの長さの非表示の row_id を作成します。

2. InnoDB は、主キーのないすべての InnoDB テーブルに対してグローバル dict_sys.row_id 値を維持します。

  • 現在のdict_sys.row_idの値は、データを挿入するためのrow_idとして使用され、その後dict_sys.row_id + 1の値が挿入されます。

3. コード実装では、row_idは8バイトのBIGINT UNSIGNEDです。

  • ただし、InnoDB が設計されたとき、row_id 用に予約されたスペースは 6 バイトのみであり、データ テーブルに書き込むときには最後の 6 バイトのみが格納されていました。
  • row_idの値の範囲は0〜2^48-1です。
  • 上限に達した後の次の値は0です

4. InnoDBでは、row_id=Nを適用した後、このデータ行をテーブルに書き込みます。

  • テーブル内に row_id=N の行がすでに存在する場合、新しく書き込まれた行によって元の行が上書きされます。

5. 自動増分主キーを作成することをお勧めします

  • テーブルの自動インクリメント ID が上限に達すると、データの挿入時に主キーの競合エラーが報告され、可用性に影響します。
  • データを上書きするとデータが失われ、信頼性に影響します。
  • 一般的に言えば、信頼性は可用性よりも優れている

XID

1. redologとbinlogを一緒に使用すると、トランザクションに対応する共通フィールドXIDが存在する。

2. ロジックを生成する

  • MySQLは内部的にグローバル変数global_query_idを維持している
  • ステートメントが実行されるたびに、global_query_idがQuery_idに割り当てられ、次にglobal_query_id+1が割り当てられます。
  • 現在のステートメントがこのトランザクションによって実行される最初のステートメントである場合、Query_id をこのトランザクションの XID に割り当てます。

3. global_query_idは純粋なメモリ変数であり、再起動後にクリアされます。

  • したがって、同じデータベース インスタンス内で、異なるトランザクションの XID が同じになる可能性があります。
  • MySQLが再起動すると、新しいbinlogが再生成されます。
    • 保証: XID は同じ binlog ファイル内で一意です
  • global_query_idが上限に達すると、0からカウントを続けます。
    • したがって、理論上は同じ XID が同じバイナリログに表示されますが、その確率は極めて低くなります。

4. global_query_idは8バイトで、上限は2^64-1です。

  • XIDがAであると仮定してトランザクションを実行する
  • 次に、クエリステートメントを2^64回実行し、global_query_idをAに戻します。
  • 別のトランザクションを開始します。このトランザクションのXIDもAです。

InnoDB の trx_id

1. XIDはサーバー層によって管理される

2. InnoDB は内部的に trx_id を使用して、InnoDB トランザクションをサーバー層に関連付けます。

3. InnoDBは内部的にグローバル変数max_trx_idを維持している

  • 新しいtrx_idを申請する必要があるたびに、max_trx_idの現在の値を取得し、次にmax_trx_id+1を取得します。

4. InnoDBデータ可視性の核となる考え方

  • 各データ行には、それを更新したtrx_idが記録されます。
  • トランザクションがデータ行を読み取るとき、データの可視性を決定する方法
    • トランザクションの一貫性のあるビューをこのデータ行のtrx_idと比較します

5. 実行中のトランザクションについては、information_schema.innodb_trx を通じてトランザクションの trx_id を確認できます。

操作手順

時間セッションAセッションB
T1始める;
t LIMIT 1 から * を選択;
T2 information_schema を使用します。
innodb_trx から trx_id、trx_mysql_thread_id を選択します。
T3 t VALUES (null) に挿入します。
T4 innodb_trx から trx_id、trx_mysql_thread_id を選択します。

-- T2 では、mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 281479812572992 | 30 |
+-----------------+---------------------+

-- T4 で、mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 7417540 | 30 |
+-----------------+---------------------+

mysql> プロセスリストを表示します。
+----+-----------------+-----------+--------------------+---------+------------------------+------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+-----------------+-----------+--------------------+---------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | デーモン | 344051 | 空のキューを待機中 | NULL |
| 30 | ルート | ローカルホスト | テスト | スリープ | 274 | | NULL |
| 31 | root | localhost | information_schema | クエリ | 0 | 開始 | SHOW PROCESSLIST |
+----+-----------------+-----------+--------------------+---------+------------------------+------------------+

1. trx_mysql_thread_id=30 はスレッドID、つまりセッションAが配置されているスレッドです。

2. T1では、trx_idの値は実際には0であり、大きな値は表示用のみです(通常の読み取りおよび書き込みトランザクションとは異なります)。

3. T2 の時点では、trx_id は大きな数値になっています。これは、T1 の時点でセッション A に更新操作が含まれず、読み取り専用トランザクションであったためです。

  • 読み取り専用トランザクションの場合、InnoDBはtrx_idを割り当てません。

4. セッションAがT3の時点でINSERT文を実行すると、InnoDBは実際にtrx_idを割り当てる。

読み取り専用トランザクション

1. 上記の時間T2で、大きなtrx_idがシステムによって一時的に計算されます。

  • 現在のトランザクションのtrx変数のポインタアドレスを整数に変換し、2^48を加算します。

2. 同じ読み取り専用トランザクションの実行中、そのポインタアドレスは変更されません。

  • innodb_trxテーブルでもinnodb_locksテーブルでも、同じ読み取り専用トランザクションで見つかったtrx_idは同じです。

3. 複数の並列読み取り専用トランザクションがある場合、各トランザクションのtrx変数のポインタアドレスは異なる必要があります。

  • 同時実行されている読み取り専用トランザクションごとに trx_id が異なります。

4. 2^48 を追加する目的は、読み取り専用トランザクションによって表示される trx_id 値が比較的大きくなり、通常の読み取り/書き込みトランザクションを区別するために使用されるようにすることです。

5. trx_idのロジックはrow_idに似ており、長さは8バイトと定義されています。

  • 理論的には、読み取り/書き込みトランザクションで読み取り専用トランザクションと同じ trx_id を表示することが可能です。
  • しかし、その可能性は極めて低く、実害はない。

6. 読み取り専用トランザクションにtrx_idを割り当てないことの利点

  • トランザクションビューでアクティブ配列のサイズを縮小できます
    • 現在実行中の読み取り専用トランザクションは、データの可視性に影響を与えません。
    • したがって、トランザクションの一貫したビューを作成するときは、読み取りおよび書き込みトランザクションの trx_id をコピーするだけで済みます。
  • trx_idアプリケーションの数を減らすことができます
    • InnoDB では、通常の SELECT 文を 1 つだけ実行した場合でも、実行プロセス中は読み取り専用トランザクションに該当します。
    • 通常のクエリ ステートメントが trx_id に適用されない場合は、trx_id を適用する同時トランザクションによって発生するロックの競合を大幅に削減できます。
    • 読み取り専用トランザクションはtrx_idを割り当てないため、trx_idの増加率は遅くなります。

7. max_trx_id は永続的に保存され、再起動後も 0 にリセットされません。上限の 2^48-1 に達した後にのみ 0 にリセットされます。

スレッドID

1. SHOW PROCESSLISTの最初の列はthread_idです

2. システムは環境変数thread_id_counterを保存します。

  • 新しい接続が作成されるたびに、thread_id_counter が新しい接続のスレッド変数に割り当てられます。

3. thread_id_counterは4バイトとして定義されているため、2^32-1に達すると0にリセットされます。

  • しかし、SHOW PROCESSLISTでは2つの同一のthread_idは表示されません。
  • MySQLは新しいスレッドにthread_idを割り当てるための独自の配列ロジックを設計しているため、ロジックコードは次のようになります。
する {
  新しいID = スレッドIDカウンタ++;
} while (!thread_ids.insert_unique(new_id).second);

参考文献

「MySQL実践45講義」

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません:
  • MySQLの自動増分IDの開始値を変更する方法
  • MySQL の自動増分 ID を 0 に戻す方法
  • MySQLテーブルにおける自己増分IDの問題の解決
  • MySQL 自動インクリメント ID のオーバーサイズ問題のトラブルシューティングと解決策
  • MySQLの自動増分IDについて知っておくべきこと
  • MySQL の自動増分 ID (主キー) が不足した場合の解決策
  • MySQL の自動増分 ID に関するいくつかの小さな問題の要約
  • MySQLの自己増分IDがなくなったらどうするか

<<:  動的なテーブル効果を実現するJavaScript

>>:  JavaScript は自由に移動するウィンドウのマウス制御を実装します

推薦する

Vue+Element UI でサマリーポップアップウィンドウを実装するプロセス全体

シナリオ: 検査文書には n 個の検査詳細があり、検査詳細には n 個の検査項目があります。実装効果...

Nodejs プラグインと使用方法の概要

このチュートリアルの動作環境: Windows 7 システム、nodejs バージョン 12.19....

Linux curl フォームのログインまたは送信と Cookie の使用に関する詳細な説明

序文この記事では主に、curl を介してフォーム送信ログインを実装する方法について説明します。単一の...

Vueはマーキースタイルのテキストの水平スクロールを実装します

この記事では、マーキースタイルのテキストの水平スクロールを実現するためのVueの具体的なコードを参考...

PostgreSQL正規表現の一般的な機能の概要

PostgreSQL正規表現の一般的な機能の概要正規表現は、複雑なデータ処理を必要とするプログラムに...

Mac OS 10.11 での MySQL 5.7.12 のインストールと設定のチュートリアル

Mac OS 10.11 に MySQL をインストールして設定する方法を、主に写真を使って手順を簡...

JavaScriptの原理と方向性

これが何を指しているのかをどのように判断するのでしょうか? ①グローバル環境で呼び出された場合はwi...

CSS3 の transition、transform、translate の違いと機能の簡単な分析

変換して翻訳するTransform は、変換と変形を意味します。他の幅属性や高さ属性と同様に、CSS...

Vueは複数の画像の追加、表示、削除を実装します

この記事では、Vueで複数の画像を追加、表示、削除するための具体的なコードを参考までに紹介します。具...

Tomcat プロジェクトを展開する一般的な方法のいくつか [テスト済み]

1 / Webプロジェクトファイルをwebappsディレクトリに直接コピーするこれは最も一般的に使...

CSS マルチレベルメニュー実装コード

これは、Web ページを Windows のスタート メニューなどのデスクトップ プログラムのように...

CSS はスクロールバーを非表示にしてコンテンツをスクロールする効果を実現します (3 つの方法)

フロントエンド開発では、スクロールバーを非表示にしながらスクロールをサポートしなければならないという...

負荷分散と動的および静的分離操作を実現するDocker NginxコンテナとTomcatコンテナ

Tomcat8 イメージをダウンロード [root@localhost ~]# docker sea...

mysql8.0.21 のダウンロードとインストールに関する詳細なチュートリアル

公式ウェブサイトアドレス: https://www.mysql.com/インストールの提案: インス...

MySQLマスタースレーブ遅延現象と原理の詳細な分析

1. 現象早朝、オンライン テーブルにインデックスが追加されました。テーブル内のデータ量が大きすぎた...