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 ライブ サーバーを実行する方法

推薦する

LinuxにNginxをインストールする詳細な手順

1. Nginxのインストール手順1.1 公式サイトの紹介http://nginx.org/en/d...

JavaScriptのイベントループの仕組みの分析

目次序文: 1. イベント ループとタスク キューの理由: 2. イベントループメカニズム: 3. ...

HTML の ReadOnly と Enabled の違い

ReadOnly 属性を持つ TextBox は、クライアント上で次のマークアップとして表示されます...

CSS でマウスの位置をマッピングし、マウスを動かしてページ要素を制御する (サンプル コード)

マウスの位置をマッピングしたり、ドラッグ効果を実装したりすることは、 JavaScriptで行うこと...

CSS3は子供のころの紙飛行機を実現する

今日は折り紙飛行機(飛べる飛行機)を作ります基本的にすべてCSSで実装されており、JSはごく一部に過...

HTMLハイパーリンクaタグのhrefジャンプとonclick間の実行順序の例

HTMLハイパーリンクaタグのhrefジャンプとonclickの実行関係htmlのaタグのhrefは...

Centos7でのパーティションのフォーマットとマウントの実装

Linux では、ハードディスクの追加やパーティションの再マウントといった状況に頻繁に遭遇します。こ...

Tomcatでcatalina.batがUTF-8に設定されている場合、コンソールに文字化けした文字が表示されます

1. catalina.bat は UTF-8 に設定する必要があります。UTF-8 に設定しないと...

Linux Dig コマンドの使用法

発掘紹介: Dig は、Unix ライクなコマンドライン モードで NS レコード、A レコード、M...

MySQL データベースのエンコーディングを utf8mb4 に変更する方法

utf8mb4 エンコーディングは utf8 エンコーディングのスーパーセットであり、utf8 と互...

Vue+Bootstrapでシンプルな学生管理システムを実現

参考までに、vueとbootstrapを使って比較的シンプルな生徒管理システムを作りました。具体的な...

HTML におけるブロックコメントの使用に関する詳細な紹介

HTML の一般的なコメント: <!--XXXXXXXX--> (XXXXXXXX はコ...

MySQL における大規模オブジェクトのマルチバージョン同時実行制御の詳細な説明

MySQL 8.0: InnoDB のラージ オブジェクトに対する MVCCこの記事では、MySQL...

docker compose の記述ルールについての簡単な説明

この記事ではクラスタの展開に関連する内容は紹介しませんバージョン制約Docker エンジン >...

MySQL 文字列分割操作 (区切り文字を含む文字列のインターセプション)

区切り文字なしの文字列抽出質問の要件データベース内のフィールド値:実装効果: 1行のデータを複数行に...