MySQL の自動インクリメント主キーが連続していないのはなぜですか?

MySQL の自動インクリメント主キーが連続していないのはなぜですか?

1. はじめに

この質問を提起した理由は、MySQL のユーザー テーブルの ID はデフォルトで自動的に増分されるが、データベースに保存される結果は連続していないことが仕事で分かったためです。

ユーザー テーブルの構造:

テーブル `user` を作成します ( 
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'idを増分', 
	`名前` varchar(20),
	`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻', 
	`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新時刻', 
	主キー (`id`)、一意キー `idx_name` (`name`)) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='ユーザーテーブル'

ユーザー テーブルには次のものが保存されます。

2. 自己増分ストレージの説明

1.1 MyISAM エンジンの自動インクリメント値はデータ ファイルに保存されます。

1.2 InnoDB エンジンの自己増分値は実際にはメモリに保存されます。MySQL 8.0 になって初めて、「自己増分永続性」、つまり「再起動が発生した場合、テーブルの自己増分値を MySQL 再起動前の値に復元できる」機能が実現されました。具体的な状況は次のとおりです。

  • MySQL 5.7 以前では、自動インクリメント値はメモリに保存されます。各再起動後、テーブルが初めて開かれたときに、自動インクリメント値の最大値 max(id) が検索され、その後、max(id) + 1 がテーブルの現在の自動インクリメント値として使用されます。
  • MySQL 8.0 では、自動インクリメント値の変更は redo ログに記録されます。再起動時には redo ログを使用して再起動前の値を復元します。

3つの自己付加価値修正メカニズム

MySQL では、フィールド id が AUTO_INCREMENT として定義されている場合、データ行を挿入するときに、自動インクリメントの動作は次のようになります。

  • データの挿入時に id フィールドが 0、null、または値が指定されていない場合は、テーブルの現在の AUTO_INCREMENT 値が自動インクリメント フィールドに入力されます。
  • データを挿入するときに id フィールドに特定の値が指定されている場合は、ステートメントで指定された値が直接使用されます。

自動インクリメント値を変更した結果は、挿入する値と現在の自動インクリメント値の関係によって異なります。挿入する値が X で、現在の自動インクリメント値が Y であるとします。

  • X<Y の場合、テーブルの自動インクリメント値は変更されません。
  • X≥Y の場合、現在の自動インクリメント値を新しい自動インクリメント値に変更する必要があります。

新しい自動インクリメント生成アルゴリズムは、auto_increment_offset から開始し、auto_increment_increment をステップ長として、X より大きい最初の値が新しい自動インクリメントとして見つかるまで追加を続けます。このうち、auto_increment_offset と auto_increment_increment は、それぞれ自動インクリメントの初期値とステップ サイズを表すために使用される 2 つのシステム パラメーターであり、デフォルト値は 1 です。

4. 自己評価を修正する時期

ユーザー値に挿入(null, '张三');

1 上記の SQL が実行されると、エグゼキュータは InnoDB エンジン インターフェイスを呼び出して行を書き込みます。渡されるこの行の値は (0,"张三"); です。

2 InnoDB は、SQL が自動インクリメント ID の値を指定していないことを検出し、ユーザー テーブルの現在の自動インクリメント値 2 を取得します。

3 入力行の値を (2,"张三") に変更します。

4 テーブルの自動インクリメント値を 3 に変更します。

5 データの挿入を続けます。

5. 不連続な自己増加の理由

5.1 ユニークキーの競合

SQL 実行時に、ユーザーテーブル id = 10、メモリ上の自動インクリメント id が 11 であるとします。一意キーの競合が発生し、データベースへの書き込みが失敗します。ユーザーテーブルには id = 10 のレコードがありません。その後、id が 11 から書き込まれるため、id が不連続になります。

5.2 トランザクションのロールバック

ユーザー テーブルとスタッフ テーブルを同時にデータベースに書き込む必要があるとします。SQL を実行すると、ユーザー テーブルの ID は 10、メモリ内の自動増分 ID は 11、スタッフ テーブルの ID は 20、メモリ内の自動増分 ID は 21 になります。トランザクションが失敗すると、トランザクションはロールバックされ、書き込み操作は失敗し、ユーザー テーブルには ID が 10 のレコードがなく、スタッフ テーブルには ID が 20 のレコードがありません。ユーザー テーブルは 11 から書き込みを開始し、スタッフ テーブルは 21 から書き込みを開始するため、ID が不連続になります。

5.3 バッチ書き込み操作

バッチでデータを挿入するステートメントの場合、MySQL にはバッチで自動インクリメント ID を適用する戦略があります。

1. ステートメント実行中に、初めて自動インクリメント ID を申請すると、1 が割り当てられます。

2. 1 が使い果たされた後、このステートメントは自動インクリメント ID に 2 回目に適用され、2 が割り当てられます。

3. 2 つが使い果たされた後、同じステートメントを使用して 3 番目の自己増分 ID を適用し、4 が割り当てられます。

同様に、同じステートメントを使用して自動インクリメント ID を適用する場合、適用される自動インクリメント ID の数は、毎回前の数の 2 倍になります。

4 つのレコードがバッチでユーザー テーブルに書き込まれると仮定すると、これらの 4 つのレコードは 3 つのアプリケーション ID に分割されます。

1回目はid=1に割り当てられ、2回目はid=2、3に割り当てられ、3回目はid=4、5、6、7に割り当てられます。4つのレコードが一括で書き込まれた後、id=1、2、3、4は保存されますが、id=5、6、7は破棄され、次のidは8から始まります。

6. 参考資料

https://time.geekbang.org/column/intro/139

MySQL の自動インクリメント主キーが連続していない理由に関するこの記事はこれで終わりです。MySQL の自動インクリメント主キーに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL の主キーとその自動増分の設定に関するチュートリアル
  • MySQL で自動増分主キーの型を int から char に変更する例
  • MySQL の自動増分 ID (主キー) が不足した場合の解決策
  • MySQL の自動増分主キーが使い果たされた場合の対処方法
  • MySQL 8 の新機能: 自動増分主キーの永続性に関する詳細な説明
  • MySQL の非主キー自己増分使用例の分析
  • MySQLの自動増分主キーIDはこのように処理されません
  • MySQLの自動増分主キーの実装の詳細な説明

<<:  HTML 要素 noscript の使用の紹介

>>:  Nginx を使用して rtmp ライブ サーバーを実行する方法

推薦する

フロントエンドJavaScriptのクラス

目次1. クラス1.1 コンストラクタ() 1.2 ゲッターとセッター1.3 これ1.4 静的プロパ...

MySQL ダーティ ページ フラッシュとテーブル スペースの縮小の原理の分析

mysql ダーティページWAL メカニズムにより、InnoDB はステートメントを更新するときに、...

Vue3.0 は虫眼鏡効果のケーススタディを実装します

達成される効果は、固定ズームが 2 倍になり、マウスが左側の画像領域に入るとマスク レイヤーが表示さ...

Hタグの定義と注意事項について簡単に説明します

結果から判断すると、タイトルを定義するための固定パターンはなく、すべてむしろランダムな感じがします。...

Windows Server 2019 のインストール (グラフィカル チュートリアル)

Windows Server 2019 は、Microsoft が公式にリリースした最新のサーバー...

CSS3 引用のソースと出典をマークする方法

疫病のせいで家にこもりきりで、頭がおかしくなりそうなので、パソコンを起動して頭を働かせてみました。今...

Ansible を使用した Nginx のバッチ デプロイのサンプル コード

1.1 nginxインストールパッケージとインストールスクリプトをクライアントにコピーし、スクリプト...

HTMLは角丸四角形を簡単に実装します

質問: div+css と配置を使用して角丸四角形を実現するにはどうすればよいですか?ソリューション...

MySQL データ型の詳細

目次1. 数値型1.1 数値型の分類1.1.1 浮動小数点数1.1.2 ビットタイプ1.1.3 時間...

グリッド共通レイアウトの実装

両側に隙間なし、各列間に隙間あり 幅: 100%; 表示: グリッド; グリッドテンプレート列: r...

インデックスは MySQL クエリ条件で使用されますか?

雇用主から MySQL クエリ条件でインデックスが使用されるかどうかを尋ねられた場合、どのように答え...

MySQL 5.7.9 シャットダウン構文例の詳細な説明

mysql-5.7.9 では、ついにシャットダウン構文が提供されます。以前は、MySQL データベー...

MySQL5.7.21 解凍版インストール詳細チュートリアル図

私は頻繁にシステムをインストールするので、インストールのたびにいくつかのソフトウェアを再インストール...

Dockerコンテナでユーザーを切り替えるときに権限が不足する問題を解決する方法

Docker コンテナでユーザーを切り替えると、権限が不十分であるというメッセージが表示されます。解...

CocosCreatorでJSZip圧縮を使用する方法

CocosCreator バージョン: 2.4.2 jszipの実践的なプロジェクトアプリケーション...