MySQL の自動増分主キーに関する詳細な説明

MySQL の主キーは自動増分が可能です。では、停電や再起動後に新しく追加された値は停電前の自動増分値を継続するのでしょうか?自動インクリメントのデフォルト値は 1 ですが、変更することはできますか? MySQL の自動インクリメントについてお話しましょう。

特徴

保存戦略

1. ストレージエンジンが MyISAM の場合、自動インクリメント値はデータファイルに保存されます。

2. InnoDB エンジンの場合、1) 5.6 より前はメモリに保存され、永続化されません。再起動後、最大のキー値を検索します。たとえば、テーブルの現在のデータ行の最大 ID が 10 の場合、AUTO_INCREMENT=11 になります。このとき、id=10 の行は削除され、AUTO_INCREMENT は 11 のままです。ただし、インスタンスをすぐに再起動すると、このテーブルの AUTO_INCREMENT は再起動後に 10 になります。

2) 8.0 以降では、自動増分値は redo ログに保存されます。再起動後、以前に保存された自動増分値が redo ログから読み取られます。

自己増加の決定

1. データを挿入するときに id フィールドが 0、null、または未指定に指定されている場合、テーブルの現在の AUTO_INCREMENT 値が自動インクリメントフィールドに入力され、auto_increment_offset が初期値として使用され、auto_increment_increment がステップサイズとして使用され、現在の自動インクリメント値より大きい最初の値が新しい自動インクリメント値として検索されます。

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

シナリオによっては、すべてのデフォルト値が使用されるわけではありません。たとえば、デュアル M マスタースレーブ構造でデュアル書き込みが必要な場合、auto_increment_increment=2 を設定すると、1 つのデータベースの自動インクリメント ID がすべて奇数になり、他のデータベースの自動インクリメント ID がすべて偶数になり、2 つのデータベースによって生成される主キーの競合を回避できます。

自動増分値の変更

入力する値が X で、現在の自動インクリメント値が Y であるとします。それで：

1. X<Yの場合、このテーブルの自動インクリメント値は変更されません。

2. X ≥ Y の場合、現在の自動インクリメント値を新しい自動インクリメント値に変更します。

実行プロセス

テーブル t があり、id が自動増分主キーであり、(1,1,1) がすでに存在する場合は行 (null,1,1) を挿入すると仮定します。この場合、実行プロセスは次のようになります。

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

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

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

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

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

問題点

上記の特性により、シナリオによっては主キーが不連続になる場合があります。

シナリオ 1: データを追加するときに一意のインデックスが繰り返される

列 c のインデックスが繰り返されると、最初に割り当てられた主キー値 2 は破棄され、次の挿入は 2 から開始され、3 になります。

シナリオ 2: トランザクションのロールバック

t 値に挿入します(null,1,1);
始める;
t値(null,2,2)に挿入します。
ロールバック;
t値(null,2,2)に挿入します。
//挿入された行は (3,2,2) です

2 番目のステートメントがロールバックされた後に割り当てられた主キー 2 も破棄されます。

シナリオ3: 特別なバッチ挿入の最適化により

ここで言及されている特別なバッチ挿入は、挿入...選択、置換...選択、およびデータロードステートメントを指します。これらの発言はなぜ起こるのでしょうか?これは自己増分ロックに関するものです。まず、自動インクリメントロックは、マルチスレッドの競合を回避するためのものです。マルチスレッドでは、複数のスレッドが同時に自動インクリメント値を取得すると、同じ自動インクリメント値が複数のレコードに割り当てられ、徐々に競合が発生する可能性があるためです。したがって、自己増分ロックが必要です。上記のバッチ挿入ステートメントによって主キーの不連続が発生する理由については、次の自己増分ロックのセクションで説明します。

質問: 自動インクリメントロックについて説明する前に、まず質問について考えてみましょう。最初の 2 つのシナリオでは、なぜ自動インクリメントの主キー値がロール可能に設定されていないのでしょうか。こうすれば不連続性は避けられるのではないでしょうか?

回答: 設計はロールバック可能であるため、パフォーマンスの低下につながります。次のシナリオを参照してください。

1. トランザクション A が id=2 を申請し、トランザクション B が id=3 を申請するとします。この場合、テーブル t の自動インクリメント値は 4 となり、実行が続行されます。

2. トランザクション B は正しく送信されますが、トランザクション A で一意キーの競合が発生します。

3. トランザクション A が自動インクリメント ID をロールバックできる場合、つまり、テーブル t の現在の自動インクリメント値を 2 に戻す場合、次の状況が発生します。テーブルにはすでに ID = 3 の行があり、現在の自動インクリメント ID 値は 2 です。

4. 次に、引き続き実行される他のトランザクションは、id=2 に適用され、次に id=3 に適用されます。このとき、挿入ステートメントは「主キーの競合」というエラーを報告します。

上記の問題を解決するには、次の 2 つの方法のいずれかを選択する必要があります。

方法 1: 毎回 ID を申請する前に、まずその ID がテーブル内に既に存在するかどうかを確認します。存在する場合は、この ID をスキップします。しかし、この方法は非常にコストがかかります。 ID の申請は非常に簡単な操作ですが、ID が存在するかどうかを判断するために主キーインデックスツリーに移動する必要があります。

方法 2: 自動インクリメント ID のロック範囲を拡張します。トランザクションが完了してコミットされた後にのみ、次のトランザクションが自動インクリメント ID を適用できます。この方法の問題点は、ロックの粒度が大きすぎるため、システムの同時実行能力が大幅に低下することです。

したがって、全体として、たとえば、自己評価値のロールバック機能をキャンセルすることができます。

自動増分ロック

自動インクリメントロックの目的は、マルチスレッド環境で複数のスレッドが同じ主キー値を取得し、主キーの競合が発生するのを防ぐことです。

ロック戦略

バージョン 5.0: スコープはステートメントであり、ステートメントが実行されるまで解放されません。

5.1.22 以降: innodb_autoinc_lock_mode パラメータが導入され、異なるパラメータ値に応じて異なる戦略が実装されます。デフォルトは 1 です。

1. パラメータが 0 の場合、前の戦略が採用され、実行後にステートメントが解放されることを意味します。

2. パラメータが 1 の場合、一般的な挿入ステートメントでは、自動インクリメントロックは適用後すぐに解除されます。

insert...select など、データをバッチで挿入するステートメントの場合、ステートメントが実行されるまでデータは解放されません。ロック範囲は、選択に関係する範囲とギャップです。

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

質問: insert...select などのバッチ操作では、なぜデフォルトでステートメントレベルのロックが使用されるのですか?デフォルトのパラメータが 2 ではないのはなぜですか?

回答: insert...select などのデータを一括して挿入するステートメントにより、マスターとスレーブの間で不整合が発生する可能性があるためです。

sessionB が「create table t2 like t」を実行した後、sessionA と sessionB は同時に t2 を操作します。ロックがない場合、実行プロセスは次のようになります。

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

これは問題ではないように見えますが、クラスター内でマスターがこのように実行し、binlog がステートメント形式であることを要求した場合、スレーブの実行順序がマスターと一致しなくなり、最終的にマスターとスレーブの間で不整合が発生する可能性があります。そのため、バッチ挿入時にはロックが必要となります。 2 に設定すると、binlog が行でない場合に、マスターデータとスレーブデータの間に不整合が発生します。

したがって、データの一貫性とシステムの同時実行性を保証するには、次の 2 つの解決策があります。

解決策 1: binlog 形式をステートメントに設定し、innodb_autoinc_lock_mode を 1 に設定します。

解決策 2: binlog 形式を row に設定し、innodb_autoinc_lock_mode を 2 に設定します。通常、MySQL の高可用性を確保するために、binlog を row に設定するため、通常は 2 番目のオプションを選択します。

バッチ挿入の最適化

バッチで挿入する場合、一度に挿入されるステートメントの数がわからないため、レコードが数千万、あるいは数億ある場合は、各挿入に自動増分値を割り当てる必要があり、非常に非効率的になります。そのため、MySQL はバッチ操作を最適化します。

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

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

3. 2 が使い果たされた後、同じステートメントを使用して 3 番目の自己増分 ID を適用し、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 回適用されています。最初に適用されたときは、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) になります。これは、前述の主キーが不連続になる 3 番目のケースです。

挿入...同じテーブルの前後を選択すると一時テーブルが使用されます

テーブル構造があると仮定する

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

t 値に挿入します (null、1,1)。
t値(null, 2,2)に挿入します。
t値(null, 3,3)に挿入します。
t値(null, 4,4)に挿入します。

t のようなテーブル t2 を作成する

実行されるステートメントが次の場合:

t2(c,d) に挿入 (t から c+1、d を選択、force index(c)、order by c desc limit 1);

スローログをクエリすると、

スキャンされた行数は 1 です。これは、レコードがインデックスを通じて t 上で直接見つかり、その後 t2 テーブルに挿入されることを意味します。

この文を次のように変更すると

t(c,d) に挿入します (t から c+1、d を選択し、インデックス (c) を強制し、c で並べ替え、降順で制限 1 にします)。

この時のスローログを確認すると、5になっていることがわかります。これはなぜでしょうか？すべてチェックしても4つしかありません。このとき、走査線数の変化を確認します。

前後の変更が 4 行であることがわかったので、一時テーブルが使用されていると判断し、次のように処理を決定できます。

1. 2 つのフィールド c と d を持つ一時テーブルを作成します。

2. インデックス c に従ってテーブル t をスキャンし、c = 4、3、2、1 を順に取得してからテーブルに戻り、c と d の値を読み取り、一時テーブルに書き込みます。この時点で、Rows_examined=4 です。

3. セマンティクスに制限 1 があるため、一時テーブルの最初の行のみが取得され、テーブル t に挿入されます。このとき、Rows_examined の値は 1 増加して 5 になります。

一時テーブルが必要な理由は、読み取り時に挿入されたばかりの値を読み取らないようにするためです。

最適化

select によって返されるレコードの数が少ないため、一時メモリテーブルを使用して最適化できます。

一時テーブルを作成します temp_t(c int,d int) engine=memory;
temp_t に挿入します (t から c+1、d を選択し、force index(c)、order by c desc limit 1)。
t に挿入します。temp_t から * を選択します。
テーブル temp_t を削除します。

スキャンされる行の合計数は、選択からの 1 行と一時テーブルからの 1 行のみです。