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 をよろしくお願いいたします。