MySQL innodb_autoinc_lock_mode について

innodb_autoinc_lock_mode パラメータは、auto_increment 列を持つテーブルにデータを挿入するときの関連ロックの動作を制御します。

設定することで、パフォーマンスとセキュリティ（マスターとスレーブ間のデータ一貫性）のバランスをとることができます。

【0】まずは挿入物を分類してみましょう

まず、インサートは大きく分けて3つのカテゴリに分けられます。

1. insert into t(name) values('test') のような単純な挿入

2. load data | insert into ... select .... from .... などの一括挿入

3. insert into t(id,name) values(1,'a'),(null,'b'),(5,'c'); などの混合挿入

【1】innodb_autoinc_lock_modeの説明

innodb_auto_lockmode には 3 つの値があります。

1, 0 これは伝統を意味する

2. 1 これは連続した

3.2 これはインターリーブを意味します

【1.1】tradition(innodb_autoinc_lock_mode=0)モード:

1. 下位互換性機能を提供する

2. このモードでは、すべての挿入ステートメント (「挿入のような」) は、ステートメントの開始時にテーブル レベルの auto_inc ロックを取得し、ステートメントの終了時にロックを解除する必要があります。これは、トランザクション レベルではなく、ステートメント レベルを参照することに注意してください。トランザクションには、1 つ以上のステートメントが含まれる場合があります。

3. 値の分配の予測可能性、継続性、再現性を確保できるため、挿入ステートメントがスレーブにコピーされるときにマスターと同じ値を生成できることが保証されます (ステートメントベースのレプリケーションのセキュリティが確保されます)。

4. このモードでは、auto_inc ロックがステートメントの最後まで保持されるため、同時挿入に影響します。

[1.2] 連続（innodb_autoinc_lock_mode=1）モード：

1. このモードでは、単純な挿入が最適化されます。一度に挿入される値の数がすぐに判断できるため、MySQL はこの挿入ステートメントに対して一度に複数の連続した値を生成できます。一般に、これはレプリケーションにも安全です (ステートメントベースのレプリケーションの安全性を保証します)。

2. このモードは MySQL のデフォルト モードでもあります。このモードの利点は、auto_inc ロックがステートメントの最後まで残らないことです。ステートメントが対応する値を取得する限り、ロックを事前に解除できます。

【1.3】インターリーブ(innodb_autoinc_lock_mode=2)モード

1. このモードでは auto_inc ロックがないため、このモードでのパフォーマンスは最高です。ただし、同じステートメントに対して取得される auto_incremant 値が連続しない可能性があるという問題もあります。

【2】バイナリファイル形式が混合|行の場合、これら3つの値はいずれも安全にコピーできます。

MySQL では現在、バイナリ形式を行に設定することを推奨しているため、binlog_format がステートメントでない場合は innodb_autoinc_lock_mode=2 に設定するのが最適で、これによりパフォーマンスが向上する可能性があります。

最後に、auto_incrementの例で終わりましょう。

例:理由もなく auto_increment 列の値を更新しない

ステップ1: シーンを再現する

テーブル t(x int auto_increment not null 主キー) を作成します。
t(x)に値(0),(null),(3)を挿入します。
t から * を選択します。
+---+
| バツ |
+---+
| 1 |
| 2 |
| 3 |
+---+

ステップ2: 問題の原因となったSQLを再現する

tを更新し、x=1の場合にx=4に設定します。
t から * を選択します。
+---+
| バツ |
+---+
| 2 |
| 3 |
| 4 |
+---+

ステップ3: 通常のパフォーマンスを再現する

t(x) に値(0) を挿入します。
エラー 1062 (23000): キー 'PRIMARY' のエントリ '4' が重複しています

ステップ4: 問題の概要

最初のステップを実行すると、MySQL は次の auto_increment 値が 4 であることを認識します。

2 番目のステップを実行すると、MySQL は 4 が手動で占有されたことを認識しないため、3 番目のステップを実行するとエラーが発生します。

MySQL innodb_autoinc_lock_mode について紹介したのは以上です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。