MySQL の主キーの自動インクリメントで遭遇する落とし穴を解決する方法

1. UUID を使用しない理由

したがって、UUID 文字列を主キーとして使用する場合、データが挿入されるたびに、B ツリー内で独自の位置を検索する必要があります。 (配列にレコードを挿入するのと同じように) 後続ノードを移動することは可能ですが、後続ノードを移動するとページ分割が発生する可能性があり、挿入効率が低下します。

一方、非クラスター化インデックスでは、リーフ ノードに主キーの値が格納されます。主キーが長い UUID 文字列の場合、(int および In と比較して) より大きな記憶領域を占有します。言い換えると)、同じリーフ ノードで保存できる主キー値の数が減り、ツリーが高くなる可能性があります。これは、クエリ中の IO 数が増加し、クエリ効率が低下することを意味します。

上記の分析に基づいて、MySQL では UUID を主キーとして使用しないようにしています。UUID がないと、自動インクリメントに主キーを使用できるのではないかと考える人もいるかもしれません。

主キーの自動インクリメントにより、UUID を主キーとして使用するときに発生する 2 つの問題が明らかに解決されます。主キーは自動インクリメントされます。毎回ツリーの最後に追加するだけで済みます。基本的に、ページ分割の問題は発生しません。主キーの自動インクリメントとは、主キーが数値であり、占有されるストレージ領域は比較的小さく、非クラスター化の場合、インデックス作成の影響も小さくなります。

では、主キーの自動インクリメントが最善の解決策なのでしょうか?主キーを自動でインクリメントする場合に注意すべき点はありますか?

2. 主キーの自動インクリメントの問題

次の内容には、テーブルに主キーの自動インクリメントがあるという共通の前提があります。

一般に、主キーの自動インクリメントには問題はありません。ただし、同時実行性の高い環境では問題が発生します。

まず第一に、最も簡単に考えられるのは、大量の同時挿入中に発生するテール ホットスポットの問題です。同時挿入中、全員がこの値をクエリしてから、独自の主キー値を計算する必要があります。次に、上位キーの値を計算する必要があります。主キーの境界はホットデータとなり、同時挿入時にここでロック競合が発生します。

この問題を解決するには、適切な

innodb_autoinc_lock_mode を選択する必要があります。

2.1 データ挿入の 3 つの形式

まず、データ テーブルにデータを挿入するときは、通常、次の 3 つの異なる形式があります。

• insert into user(name)values('javaboy') または replace into user(name)values('javaboy')、入れ子になったクエリはありません。挿入する行数を決定できます挿入は simple insert と呼ばれますが、INSERT ... ON DUPLICATE KEY UPDATE は としてカウントされないことに注意してください。単純にを挿入します。

• load data

  または insert into user select ... from ....、これらは bulk と呼ばれるバッチ挿入ですinsert、この一括挿入の特徴の 1 つは、挿入されるデータの数が最初は不明であることです。

• user(id,name) に挿入 value(null,'javaboy'),(null,'江南一宜店')

  、これもバッチ挿入です, しかし、2 番目のものとは異なります。この型には、いくつかの自動的に生成された値 (この場合の主キーは自動インクリメントされます) が含まれており、合計で何行挿入されるかを決定できます。この型は と呼ばれます。混合挿入、上記の最初の点で述べた INSERT ... ON DUPLICATE KEY UPDATE については、混合挿入とみなすこともできます。

データ挿入は主に主キーが自動インクリメントされる場合、ロック処理スキームが異なるため、これらの 3 つのカテゴリに分類されます。

2.2 innodb_autoinc_lock_mode

主キーが自動インクリメントされるときの MySQL ロック処理のアイデアは、innodb_autoinc_lock_mode 変数の値を制御することで制御できます。

innodb_autoinc_lock_mode 変数には 3 つの異なる値があります:

0: これは従来のモードを表します。このモードでは、SQL を挿入するときに、上記の 3 つの異なる値が使用されます。自動インクリメント ロックのソリューションも同じで、挿入された SQL ステートメントの先頭でテーブル レベルの AUTO-INC ロックが取得され、挿入された SQL の実行が完了するとロックが解放されます。これは、バッチ挿入中に自動インクリメントされる主キーが連続していることを保証できるためです。
1: これは連続を意味します。このモードでは、
• simple insert

  (挿入される行の特定の数を決定でき、上記の 2 つの状況 1 と 3 に対応します) simple insert は挿入する行数を簡単に計算できるため、複数の連続した値を一度に生成し、対応する挿入 SQL ステートメントで使用できるため、AUTO- INC ロックによりロック待ちが軽減され、同時挿入効率が向上します。

2: これはインターリーブを意味します。この場合、AUTO-INC ロックはありません。それぞれが 1 つずつ処理されます。バッチで挿入する場合、プライマリキーがインクリメントされますが、存在しません。質問の連続です。

上記の紹介からわかるように、実際には 3 番目のタイプ、つまり innodb_autoinc_lock_mode の値が 2 の場合、同時実行効率が最も高くなります。そのため、innodb_autoinc_lock_mode を設定するのはどうでしょうか。 =2?

状況によります。

Songge は以前記事を書き、MySQL binlog ログ ファイルの 3 つの形式を友人に紹介しました。

• row: binlog に記録されるのは特定の値です。元の SQL の代わりに、簡単な例を挙げると、テーブル内のフィールドが UUID で、ユーザーによって実行される SQL が insert into user(username,uuid) names('javaboy',uuid() であると仮定します。 )、最終的に binlog に記録された SQL は insert into user(username,uuid)values('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’) になります。

• ステートメント: binlog に記録されるのは元の SQL です。行内の SQL を例に取ると、最終的に binlog に記録されるのは insert into user(username) ,uuid) value( 'javaboy',uuid()).

• mixed: このモードでは、MySQL は特定の SQL ステートメントに基づいてログ形式を決定します。つまり、ステートメントと行の間で 1 つを選択します。

これら 3 つの異なるモードでは、ステートメント モードがマスター/スレーブ レプリケーション中にマスター/スレーブ データに不整合を引き起こす可能性があることは明らかなので、現在、MySQL のデフォルトのバイナリ ログ形式は行です。

質問に戻ります:

• binlog 形式が行の場合、データの同時実行性を最大限に高めるために、innodb_autoinc_lock_mode の値を 2 に設定できます。エクステント挿入機能により、マスターとスレーブのデータの不整合の問題は発生しません。

• binlog 形式がステートメントの場合は、simple insert の同時挿入機能が向上するように、innodb_autoinc_lock_mode の値を 1 に設定することをお勧めします。最初に AUTO-INC ロックを取得し、挿入が成功したらロックを解除します。これにより、マスターとスレーブのデータの不整合を回避し、データ レプリケーションのセキュリティを確保することもできます。

• 上記の 2 点は主に InnoDB ストレージ エンジンに関するものですが、MyISAM ストレージ エンジンの場合は、最初に AUTO-INC ロックが取得され、挿入が完了したら解放されます。これは、innodb_autoinc_lock_mode 変数の値のペアに相当します。MyISAM は機能しません。

2.3 実践

次に、簡単な SQL を使用して、innodb_autoinc_lock_mode のさまざまな値がさまざまな結果にどのように対応するかを友人に示してみましょう。

次の SQL クエリを使用して、現在の innodb_autoinc_lock_mode 設定を表示できます:

ご覧のとおり、バージョン 8.0.32 の現在のデフォルト値は、私が使っているのは2です。

最初にこれを 0 に変更します。変更方法は、/etc/my.cnf## に innodb_autoinc_lock_mode=0:

という行を追加することです。

変更を加えた後、次のように再起動して確認します:

変更されたことがわかります。

ここで、次のテーブルがあるとします。

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

この増分は 100 から始まります。次に、次の SQL 挿入があるとします。

insert into user(id,username) values(1,'javaboy'),(null,'江南一点雨'),(3,'www.javaboy.org'),(null,'lisi');

挿入が完了したら、クエリ結果を見てみましょう:

以前の紹介によると、この状況は説明可能なはずなので、ここでは詳しく説明しません。

次に、次のように、innodb_autoinc_lock_mode の値を 1 に変更しました。

上記と同じ SQL をもう一度実行してみましょう。実行が完了すると、結果は上記と同じになります。 ＃＃＃＃＃＃しかし！ ！ ！ **上記の SQL を実行した後、再度データを挿入する場合、新しく挿入された ID に値が指定されていない場合、自動生成される ID 値は 104 であることがわかります。 **これは、innodb_autoinc_lock_mode=1 に設定したためです。このとき、

simple insert

を実行して挿入すると、システムは 4 つのレコードを挿入したいことを認識し、事前に 4 つの ID を直接取り出しました。それぞれ 100、101、102、103 です。その結果、SQL では実際に 2 つの ID のみが使用され、残りの 2 つは役に立ちませんが、次の挿入は引き続き 104 から始まります。

以上がMySQL の主キーの自動インクリメントで遭遇する落とし穴を解決する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。