Clever Geek Handbook

緊急時のINSERT ... ON DUPLICATE KEY UPDATEの動作

数週間前、私は、約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; +----+----------+---------+---------------------+ | id | username | host_id | last_modified | +----+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:36:30 | +----+----------+---------+---------------------+


普通のことは何もありませんよね？ 空のテーブルに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; +----+----------+---------+---------------------+ | id | username | host_id | last_modified | +----+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | +----+----------+---------+---------------------+ 1 row in set (0.00 sec)


そして今、新しい行を挿入しなかった場合でも、 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 *** +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_rollback | 0 | | Handler_update | 1 | | Handler_write | 1 | +----------------------------+-------+


この時点で、「だから何？」と思うかもしれません。 顧客に戻りましょう。 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 *** +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_rollback | 1 | | Handler_write | 1 | +----------------------------+-------+


そのため、行を挿入しようとしましたが、うまくいきませんでした。 AUTO_INCREMENT



すでに最大値があり、要求は失敗しました。 しかし、 INSERT ODKU



を実行しようとするとどうINSERT ODKU



ますか？ まず、テーブルにあるものを見てみましょう。 



 (root@localhost) [test]> select * from update_test; +------------+----------+---------+---------------------+ | id | username | host_id | last_modified | +------------+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | | 4294967295 | bar | 10 | NULL | +------------+----------+---------+---------------------+ 2 rows in set (0.00 sec) (root@localhost) [test]> INSERT INTO update_test (username, host_id) VALUES ('foo', 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW(); Query OK, 2 rows affected (0.00 sec)


元気そう？ 2行が変更されましたusername = "foo"



条件にhost_id



するシリーズについては、 host_id



とlast_modified



が更新されたことは明らかであり、喜ぶことができます。 残念ながら、これはそうではありません。 



 (root@localhost) [test]> select * from update_test; +------------+----------+---------+---------------------+ | id | username | host_id | last_modified | +------------+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | | 4294967295 | bar | 7 | 2012-10-05 23:24:49 | +------------+----------+---------+---------------------+ 2 rows in set (0.00 sec)


おっと、最後の行が更新されました。その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







ここで何を学びましたか？





More articles:


All Articles