数週間前、私は、約4週間ごとに発生するデータベースパフォーマンスの低下や障害に直面するクライアントの問題に取り組んでいました。 環境、ハードウェア、または問い合わせにおいて特別なことはありません。 本質的に、データベースのほとんどは、特に
INT AUTO_INCREMENT PRIMARY KEYと
UNIQUE KEYが存在する単一のテーブルでした。
このテーブルで動作するクエリは、ほぼすべて
INSERT ... ON DUPLICATE KEY UPDATEタイプ（以降
INSERT ODKUます）で、
INSERTリストされた列は
UNIQUE KEY列に対応していました。 また、1秒あたり約1500〜2000リクエストの頻度で、24時間連続して実行されました。 数学が得意であれば、おそらく何が問題なのかすでに推測しているでしょう。
議論のために、状況の説明として次の表を使用します。
CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB;
次の一連のイベントを想像してください。
(root@localhost) [test]> INSERT INTO update_test (username, host_id, last_modified) VALUES ('foo',3,NOW()); Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> select * from update_test; +
普通のことは何もありませんよね？ 空のテーブルに1つの行を挿入し、
SHOW CREATE TABLEを実行すると、
AUTO_INCREMENTカウンターの値が
2なったことがわかります。 このテーブルで
INSERT ODKUを実行すると、次のように表示されます。
(root@localhost) [test]> insert into update_test (username,host_id) values ('foo',1) on duplicate key update last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) (root@localhost) [test]> select * from update_test; +
そして今、新しい行を挿入しなかった場合でも、
AUTO_INCREMENTカウンターは
3増えました。 これは実際、予想される動作です。 InnoDBは、定義された順序で制約をチェックし、
PRIMARY KEY常に最初に移動します。 したがって、MySQLは
INSERTチェックし、次の
AUTO_INCREMENT値が使用可能であることを確認して使用しますが、
UNIQUE KEYをチェックして違反を見つけるため、
INSERTではなく
UPDATEます。
handler statusカウンターを見ると、失敗した挿入要求が1つ、成功した更新要求が1つあることがわかります（これは、1行ではなく2行が変更された理由です）。
(root@localhost) [test]> show status like 'handler%'; *** some rows omitted *** +
この時点で、「だから何？」と思うかもしれません。 顧客に戻りましょう。 1秒あたり1,500の
INSERT ODKU 1日24時間継続的に。 それらのテーブルの
PRIMARY KEY 、デモテーブルで使用したものと同じです（
INT UNSIGNED 。 数えます。
INT UNSIGNEDの最大値は
4294967295です。 1秒あたり1500リクエストで割り、86400で割ります。これは1日の秒数であり、33.1日、つまり4週間強になります。 偶然？ そうは思いません それでは、意味を超えたときに正確に何が起こるのでしょうか？ いくつかの行動はあなたを驚かせるかもしれません。 デモテーブルに戻って、
AUTO_INCREMENT列の最大値を持つ行を挿入し、別の行を挿入します。
(root@localhost) [test]> insert into update_test (id,username,host_id) values (4294967295, 'bar', 10); Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> flush status; (root@localhost) [test]> insert into update_test (username,host_id) values ('baz', 10); ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY' (root@localhost) [test]> show status like 'handler%'; *** some rows omitted *** +
そのため、行を挿入しようとしましたが、うまくいきませんでした。
AUTO_INCREMENTすでに最大値があり、要求は失敗しました。 しかし、
INSERT ODKUを実行しようとするとどう
INSERT ODKUますか？ まず、テーブルにあるものを見てみましょう。
(root@localhost) [test]> select * from update_test; +
元気そう？ 2行が変更されました
username = "foo"条件に
host_idするシリーズについては、
host_idと
last_modifiedが更新されたことは明らかであり、喜ぶことができます。 残念ながら、これはそうではありません。
(root@localhost) [test]> select * from update_test; +
おっと、最後の行が更新されました。その
idは
AUTO_INCREMENT最大値に等しく、
username列の
UNIQUE KEYは無視されました。
データベースがこの投稿のインスピレーションとなったクライアントの問題を簡単に理解できるようになりました。 同じ行をブロックして更新しようとする1秒あたり1500件の要求は、何の改善にもつながりません。 もちろん、簡単な解決策があります
AUTO_INCREMENTカラムのデータ型を
INTから
BIGINTます。
この動作は
文書化されていることが
わかります 。 マニュアルには、いくつかの一意のインデックスを持つテーブルの
INSERT ODKUは
UPDATE update_test SET host_id = 7, last_modified = NOW() WHERE id = 4294967295 OR username = "foo" LIMIT 1と同等であり、もちろんオプティマイザーは
PRIMARYではなく
PRIMARYを選択すると述べています
UNIQUEここで何を学びましたか？
AUTO_INCREMENTよりもずっと簡単です。 実際の顧客テーブルには、50万行未満が含まれていました。AUTO_INCREMENT SIGNED型をAUTO_INCREMENTは、ほとんど常に悪い考えです。 使用可能な値の範囲の半分が失われます。- 物理学の法則のように、直観はしばしば極端な状況で失敗します。