MySQLの自動増分主キーの実装の詳細な説明

1. 自己増分値はどこに保存されますか?

エンジンによって、自動増分値を保存するための戦略が異なります。

1. MyISAMエンジンの自己増分値はデータファイルに保存されます

2. InnoDB エンジンの自己増分値はメモリに保存され、MySQL 5.7 以前のバージョンでは永続的ではありません。各再起動後、テーブルが初めて開かれたときに、自動インクリメント値の最大値 max(id) が検索され、その後、max(id) + ステップ サイズがテーブルの現在の自動インクリメント値として使用されます。

更新のためにtable_nameからmax(ai_col)を選択します。

MySQL 8.0 では、自動インクリメント値の変更は redo ログに記録されます。再起動時には redo ログを使用して再起動前の値を復元します。

2. 自己価値修正メカニズム

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

1. データを挿入するときにidフィールドが0、null、または未指定に指定されている場合、テーブルの現在のAUTO_INCREMENT値が自動増分フィールドに入力されます。

2. データを挿入するときにidフィールドに特定の値が指定されている場合は、ステートメントで指定された値が直接使用されます。

挿入する値がXで、現在の自動増分値がYであるとします。

1. X<Yの場合、テーブルの自動増分値は変更されません。

2. X>=Yの場合、現在の自動増分値を新しい自動増分値に変更する必要があります。

新しい自動増分生成アルゴリズムは、auto_increment_offset（初期値）から開始し、auto_increment_increment（ステップ長）をステップ長として、Xより大きい最初の値が新しい自動増分値として見つかるまで追加を続けます。

3. 自動増分値を変更するタイミング

テーブル t を作成します。ここで、id は自動増分主キー フィールド、c は一意のインデックスです。テーブル作成ステートメントは次のとおりです。

テーブル `t` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) デフォルト NULL,
  `d` int(11) デフォルト NULL,
  主キー (`id`)、
  ユニークキー `c` (`c`)
)ENGINE=InnoDB;

テーブル t にすでにレコード (1,1,1) があると仮定します。次に、別のコマンドを実行してデータを挿入します。

t 値に挿入します(null, 1, 1); 

実行フローは以下のとおりです。

1. エグゼキュータはInnoDBエンジンインターフェースを呼び出して行を書き込みます。渡される行の値は(0,1,1)です。

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

3. 入力行の値を(2,1,1)に変更します。

4. テーブルの自動増分値を3に変更します

5. データの挿入を続けます。c=1のレコードがすでに存在するため、重複キーエラーが報告され、ステートメントは戻ります。

対応する実行フローチャートは次のとおりです。

その後、新しいデータ行が挿入されると、取得される自動インクリメント ID は 3 になります。自動増分主キーは不連続です

一意キーの競合とトランザクションのロールバックにより、自動増分主キー ID に不連続が生じる可能性があります。

4. 自己インクリメントロックの最適化

自動増分 ID ロックはトランザクション ロックではありませんが、各アプリケーションの直後に解除され、他のトランザクションが再度適用できるようになります。

しかし、MySQL バージョン 5.0 では、自動インクリメント ロックの範囲はステートメント レベルです。つまり、ステートメントがテーブルの自動インクリメント ロックに適用される場合、ステートメントが実行されるまでロックは解除されません。

MySQLバージョン5.1.22では、新しい戦略、新しいパラメータinnodb_autoinc_lock_modeが導入され、デフォルト値は1です。

1. このパラメータを 0 に設定すると、以前の MySQL 5.0 バージョンの戦略が採用され、ステートメントが実行された後にのみロックが解除されます。

2. このパラメータは1に設定されます

• 通常の挿入ステートメントの場合、自動インクリメント ロックは適用後すぐに解除されます。
• insert ... select など、データをバッチで挿入するステートメントの場合、ステートメントが完了した後も自動インクリメント ロックは解除されます。

3. このパラメータを 2 に設定すると、自動インクリメント主キーに適用されるすべてのアクションは適用後にロックを解除します。

データの一貫性を保つため、デフォルト設定は1です。

セッション B が自動インクリメントを申請した直後に自動インクリメント ロックを解除すると、次の状況が発生する可能性があります。

• セッションBは最初に2行のデータ（1,1,1）と（2,2,2）を挿入します。
• セッションAは自動増分IDを申請し、ID=3を取得し、(3,5,5)を挿入します。
• その後、セッションBは実行を継続し、2つのレコード（4,3,3）と（5,4,4）を挿入します。

binlog_format=statement の場合、2 つのセッションが同時にデータ挿入コマンドを実行するため、binlog 内のテーブル t2 の更新ログの状況は、sessionA が最初に記録されるか、sessionB が最初に記録されるかの 2 つだけになります。方法に関係なく、このバイナリログはスレーブデータベースに渡されて実行されるか、一時インスタンスを復元するために使用されます。スレーブデータベースと一時インスタンスでは、sessionB ステートメントが実行され、生成された結果の ID は連続しています。現時点では、このライブラリでデータの不整合が発生しています

この問題を解決するためのアイデア:

1) 元のデータベースのバッチ挿入ステートメントで連続した ID 値を生成するようにします。したがって、この目的を達成するために、ステートメントが実行されるまで自己増分ロックは解放されません。

2) データ挿入のすべての操作は、バイナリログに正確に記録されます。スタンバイ データベースが実行されると、データを生成するために自動増分プライマリ キーに依存することはなくなります。つまり、innodb_autoinc_lock_modeを2に設定し、binlog_formatをrowに設定する。

バッチデータ挿入のシナリオ（挿入...選択、置換...選択、データのロード）がある場合、同時データ挿入のパフォーマンスの観点から、innodb_autoinc_lock_mode を 2 に、binlog_format を row に設定することをお勧めします。これにより、データの一貫性の問題を引き起こすことなく同時実行を実現できます。

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

1. ステートメント実行中に、自動増分IDを初めて適用すると、1が割り当てられます。

2. 最初の ID が使い果たされた後、このステートメントは、2 番目の自動インクリメント ID に適用される場合、さらに 2 つの ID を割り当てます。

3. 2が使い果たされた後、同じステートメントが再び使用されます。自動増分IDが3回目に要求されると、4が割り当てられます。

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

t 値に挿入します (null、1,1)。
t値(null, 2,2)に挿入します。
t値(null, 3,3)に挿入します。
t値(null, 4,4)に挿入します。
t のようにテーブル t2 を作成します。
t2(c,d)に挿入し、tからc,dを選択します。
t2に値(null, 5,5)を挿入します。

insert ... select は実際には 4 行のデータをテーブル t2 に挿入します。ただし、これらの 4 行のデータには、自動増分 ID が 3 回適用されています。1 回目は ID=1 に適用され、2 回目は ID=2 と ID=3 に割り当てられ、3 回目は ID=4 から ID=7 に割り当てられました。

このステートメントでは実際には 4 つの ID しか使用されないため、id=5 から id=7 は無駄になります。その後、 insert into t2 values(null, 5,5)実行すると、実際に挿入されるデータは (8,5,5) になります。

これが主キー ID が連続していない 3 番目の理由です。

5. 自動増分主キーが使い果たされる

自動増分主キー フィールドが定義されたタイプの上限に達した後に別のレコード行を挿入すると、主キー競合エラーが報告されます。

符号なし整数（4バイト、上限は2 32 − 1 2^{32}-1 232−1）を例にとり、次のステートメントシーケンスで検証します。

テーブル t を作成します ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
t VALUES(NULL) に挿入します。
t VALUES(NULL) に挿入します。

最初の挿入ステートメントでデータが正常に挿入された後、このテーブルの AUTO_INCREMENT は変更されず (4294967295 のまま)、2 番目の挿入ステートメントで同じ自動増分 ID 値が取得されます。挿入ステートメントを再度試行すると、主キーの競合エラーが報告されます。

推奨素材：

https://time.geekbang.org/column/article/80531

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