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 は自由に移動するウィンドウのマウス制御を実装します

推薦する

データベースのインデックス作成に関する知識ポイントのまとめ。必要な情報はすべてここにあります。

データベースインデックスについては皆さんもよくご存知だと思います。 インデックスは、データベース テ...

MySQLのインデックスシステムがB+ツリーを使用する理由の分析

目次1. インデックスとは何ですか? 2. インデックスはなぜ必要なのでしょうか? 3. インデック...

Navicat を使用して MySQL データベースをエクスポートおよびインポートする方法

MySql は、私たちが頻繁に使用するデータ ソースです。開発者が練習、小規模なプライベート ゲーム...

Nginx リバースプロキシの例の詳細な説明

1. リバースプロキシの例1 1. 効果を達成する(1)ブラウザを開き、www.123.comと入力...

nginx プロキシ サーバーで双方向証明書検証を構成する方法

証明書チェーンを生成するスクリプトを使用して、ルート証明書、中間証明書、および 3 つのクライアント...

7つのMySQL JOINタイプのまとめ

始める前に、これから紹介する JOIN タイプを示すために 2 つのテーブルを作成します。テーブルを...

JavaScript が Jingdong のカルーセル効果を模倣

この記事では、JD.comのカルーセル効果の表示を実現するためのJavaScriptの具体的なコード...

mysql における mydumper と mysqldump の比較

いくつかのテーブルまたは単一のデータベースのみをバックアップする場合は、innobackup よりも...

MYSQL 5.6 スレーブレプリケーションの展開と監視

MYSQL 5.6 スレーブレプリケーションの展開と監視MYSQL 5.6 のインストールと展開 #...

PostgreSQL データベースにおける varchar、char、text の比較に関する簡単な説明

以下のように表示されます。名前説明する文字可変(n)、varchar(n)長さ制限あり、可変長文字(...

ラベルとスパンの幅設定が無効である問題の解決

デフォルトでは、ラベルとスパンの幅の設定は無効です。一般的に、表示属性は必須ですコードをコピーコード...

Echarts は 1 つのグラフ内で異なる X 軸を切り替える機能を実装します (サンプル コード)

レンダリング下の画像のような効果を実現したい場合は、読み続けてアニメーション画像に直接進んでください...

MySQLで判定文を書く方法のまとめ

MySQL で判断文を書く方法:方法1. CASE関数case関数の構文: CASE条件 値1の場合...

MySQL 分離レベル操作プロセスの詳細説明 (cmd)

コミットされていない読み取りの例の操作プロセス - コミットされていない読み取り1. 2 つの My...

React 高階コンポーネント HOC 使用方法の概要

HOCを紹介する一文高階コンポーネント (HOC) とは何ですか? 公式ドキュメントによると、「高階...