MySQL 自動インクリメント ID のオーバーサイズ問題のトラブルシューティングと解決策

導入

Xiao A がコードを書いていたところ、DBA Xiao B が突然、「急いでユーザー固有情報テーブル T を見てください。自動増分 ID である主キーが 16 億に達しました。まだ少ししか経っていません。このままだとすぐに範囲外になって挿入が失敗します、バラバラ...」というメッセージを送信しました。

そんなに多かったとは覚えていませんが、おそらく1,000以上でしょう。数えてみたところ、1,100万でした。運用保守では auto_increment 値を見ている、つまりテーブルに大量の削除と挿入の操作があるのに、ほとんどの時間を更新していることが判明しました。なぜこのようなことが起こるのでしょうか?

詳しい紹介を見てみましょう。

トラブルシューティング

このテーブルは、使用されているシンプルなインターフェース サービスです。毎日、ビッグ データは大量の情報を収集し、それを Xiao A にプッシュします。Xiao A はデータベースに情報を更新します。新しいデータの場合は挿入され、古いデータの場合は以前のデータが更新されます。唯一の外部インターフェースはクエリです。

すぐに、Xiao A は自分のコードをチェックしました。削除はなく、ID のアクティブな挿入や更新もありませんでした。どうしてこんなことが起きたのでしょうか? Xiao B のせいでしょうか? それはあり得ません。DBA は多くのテーブルを管理しています。問題があったなら、ずっと前に明らかになっているはずですが、何が問題なのか私にはわかりません。

Xiao A は、挿入時間と ID を主な観察フィールドとして、1,000 万を超える既存のデータを注意深く観察しました。すぐに、問題を発見しました。毎日最初に挿入されたデータは、常に前日よりも 1,000 万以上多くなっていました。増加量は多いときもあれば、少ないときもありました。Xiao A は DBA Xiao B を非難し、Xiao B に問題を再度説明しました。

Little B は Little A に「REPLACE INTO ... ステートメントを使用しましたか? 何が起こっているのですか?」と尋ねました。REPLACE INTO ... は主キーに影響を与えることが判明しました。

主キーに対する REPLACE INTO ... の影響

テーブル t1 があると仮定します。

テーブル `t1` を作成します (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID、自動増分'、
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'ユーザーuid',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT 'ユーザーニックネーム',
主キー (`id`)、
ユニークキー `u_idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Test replace into';

このテーブルを作成し、次のステートメントを実行すると、最終的なデータ レコードはどのようになるでしょうか?

t1 に値 (NULL、100、"test1")、(NULL、101、"test2") を挿入します。
t1 の値に置き換えます (NULL、100、"test3")。 

挿入が行われるたびに REPLACE INTO ... を使用すると、一意のインデックスに対応するデータがすでに存在する場合、元のデータが削除されて新しいデータが再挿入されるため、ID が増加しますが、実際にはデータが更新される可能性があることがわかります。

Xiao A は言いました。「replace はこういうものだと知っていたので、使いませんでした。」しかし、彼は再度確認し、それが自分の問題ではないことを知りました。彼は REPLACE INTO を使用していませんでした...。

Xiao A は再度注意深く確認しましたが、問題は見つからなかったため、Xiao B に binlog ログをチェックして何かおかしな点がないか確認するように依頼しました。確認したところ、問題は見つかりませんでした。確かにジャンプはありましたが、実質的な問題はありませんでした。

下の図では、@1 の値が自動インクリメント主キー ID に対応し、@2、@3 が一意のインデックスとして使用されます。

しばらくして、Xiao B は Xiao A に指示を出しました。Xiao A は挿入更新ステートメント INSERT ... ON DUPLICATE KEY UPDATE ... に疑問を抱き始めました。長い間調べた後、確かにここに問題があることがわかりました。

INSERT ... ON DUPLICATE KEY UPDATE ... の主キーへの影響

このステートメントは REPLACE INTO ... に似ていますが、レコードの主キーは変更されません。上記の t1 テーブルのままです。次のステートメントを実行した場合の結果はどうなるでしょうか。

重複キーの t1 に値 (NULL、100、「test4」) を挿入し、name = values(name) を更新します。 

はい、Xiao A の予想通り、主キーは追加されず、名前フィールドは目的の値に更新されましたが、実行結果に Xiao A の注意を引くプロンプトが表示されました。

エラーなし。2行が影響を受け、10.7ミリ秒かかりました。

明らかに 1 つのデータが更新されたのに、影響を受けるレコードの数が 2 なのはなぜですか? Xiao Aは現在のテーブルのauto_incrementをもう一度確認しました

テーブル `t1` を作成します (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID、自動増分'、
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'ユーザーuid',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT 'ユーザーニックネーム',
主キー (`id`)、
ユニークキー `u_idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Test replace into';

実際には 5 フィートですが、4 フィートのはずです。

つまり、上記のステートメントは、REPLACE INTO ... と同様に自動増分 ID を 1 増やしますが、実際のレコードは追加されません。これはなぜでしょうか。

情報を確認した後、Xiao A は MySQL の主キー自動増分用の innodb_autoinc_lock_mode パラメータがあることを発見しました。0、1、2 の 3 つの値があります。これは MySQL 5.1 以降に追加されました。デフォルト値は 1 です。それ以前のバージョンは 0 とみなすことができます。

現在どのモードになっているかを確認するには、次の文を使います。

@@innodb_autoinc_lock_mode を選択します。

Xiao A が使用するデータベースのデフォルト値も 1 です。単純な挿入 (挿入行数が判別可能) を行う場合、テーブルをロックせずに auto_increment が直接 1 増加するため、パフォーマンスが向上します。 insert into select ... のような複雑なステートメントを挿入する場合、挿入される行数は事前にわかりません。この場合、auto_increment が正確になり、ステートメントが終了したときにのみロックが解除されるように、テーブルをロックする必要があります (AUTO_INC と呼ばれる特別なテーブル ロック)。また、INSERT INTO t1 (c1,c2) ​​VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d') などの混合モード挿入と呼ばれるタイプの挿入もあり、挿入の一部は自動インクリメント主キー値を明示的に指定し、一部は指定しません。ここで説明している INSERT ... ON DUPLICATE KEY UPDATE ... もこのタイプに属します。この時点で、ステートメントが分析され、可能な限り多くの状況に応じて auto_incrementid が割り当てられます。これをどのように理解すればよいでしょうか。次の例を見てみましょう。

テーブル t1 を切り捨てます。
t1 に値 (NULL、100、「test1」)、(NULL、101、「test2」)、(NULL、102、「test2」)、(NULL、103、「test2」)、(NULL、104、「test2」)、(NULL、105、「test2」) を挿入します。

-- この時点で、データテーブル内の次の自動増分IDは7です

idが(2,3,4)のt1から削除します。

-- この時点で、データテーブルには 1、5、6 のみが残っており、自動増分 ID は 7 のままです。

t1に値(2, 106, "test1"),(NULL, 107, "test2"),(3, 108, "test2")を挿入します。

-- ここでの自動インクリメント ID とは何ですか?

上記の例を実行すると、テーブルの次の自動増分 ID は 10 になります。正しく理解できましたか? 最後に実行されたステートメントは混合モードの挿入ステートメントであるため、InnoDB はステートメントを分析して 3 つの ID を割り当てます。この時点で、次の ID は 10 ですが、割り当てられた 3 つの ID がすべて使用されるわけではありません。 @いつも遅いですが、公式ドキュメントを誤解していたことを指摘してくれてありがとう

モード0は、どのような状況であってもテーブルロックが追加され、ステートメントが実行されると解除されることを意味します。実際にレコードが追加されると、auto_incrementが1増加します。

モード2については、いかなる状況でもAUTO_INCロックが追加されないため、セキュリティ上の問題が生じます。binlog形式をステートメントモードに設定すると、スレーブデータベースを同期したときに実行結果がマスターデータベースと一致しない可能性があり、大きな問題となります。複雑な挿入がある場合があり、実行中に別の挿入が入ることがあります。復元時に1つずつ実行され、この同時実行の問題が再現できず、レコードIDが一致しない可能性があります。

この時点でIDジャンプの問題は解析済みです。innodb_autoinc_lock_modeの値が1なので、INSERT...ON DUPLICATE KEY UPDATE...は単純な文であり、影響を受ける行数は事前に計算できます。そのため、更新の有無に関わらずauto_incrementは1増加します（行が複数ある場合は1より大きくなります）。

innodb_autoinc_lock_mode の値を 0 に変更して INSERT ... ON DUPLICATE KEY UPDATE ... を再度実行すると、このモードでは AUTO_INC ロックが直接追加され、ステートメントの実行時に解放されるため、auto_increment が増加しないことがわかります。行数が増加していないことが判明した場合、自動増分 ID は増加しません。

INSERT ... ON DUPLICATE KEY UPDATE ... は、影響を受ける行数が 1 の場合に 2 を返します。

なぜこのようなことが起こるのでしょうか? 論理的には、影響を受ける行数は 1 です。詳細については、公式ドキュメントを参照してください。

ON DUPLICATE KEY UPDATE では、行が新しい行として挿入された場合、行ごとの影響を受ける行の値は 1 になり、既存の行が更新された場合は 2 になり、既存の行が現在の値に設定された場合、行ごとの影響を受ける行の値は 0 になります。

公式の声明では、挿入は 1 行、更新は 2 行に影響し、0 の場合は値が存在し、更新前後の値が同じであることを意味すると明記されています。分かりにくいですか？

実際には、このように考える必要があります。これは、挿入されたのか更新されたのかを区別するためです。1 を返すと挿入が成功したことを意味し、2 を返すと更新が成功したことを意味します。

解決

innodb_autoinc_lock_mode を 0 に設定すると、問題は確実に解決されますが、この場合、挿入の同時実行性に大きな影響が出る可能性があるため、Xiao A は DBA が同意しないだろうと考えています。検討の結果、現在 2 つの解決策が準備されています。

ビジネスロジックを変更する

ビジネス ロジックを変更し、INSERT ... ON DUPLICATE KEY UPDATE ... ステートメントを分離します。最初にクエリを実行してから更新します。これにより、主キーが制御不能に増加することがなくなりますが、複雑さが増します。元の 1 つのリクエストは、最初にクエリを実行してから更新するという 2 つのリクエストになる可能性があります。

テーブルの自動増分主キーを削除する

自動増分主キーを削除し、一意のインデックスを主キーとして使用します。この方法では、基本的に変更は必要ありません。現在の自動増分主キーが実用的ではないことを確認するだけです。この場合、挿入と削除中に効率が影響を受ける可能性があります。ただし、クエリが多い状況では、Xiao A は 2 つを比較した後、後者を好みます。

結論

実際、INSERT ... ON DUPLICATE KEY UPDATE ... の影響を受ける行数は 2 です。Xiao A はこれを非常に早く発見しましたが、好奇心を維持せず、真剣に受け止めませんでした。彼は問題を深く掘り下げませんでした。そうすれば、多くの新しい知識が明らかになるでしょう。Xiao A は外の世界に対する好奇心と感受性を維持して、前進する必要があるようです。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。