こんにちは。日焼けしやすいラクマの豊永です。
いきなりですが、MySQL の INSERT ... ON DUPLICATE KEY UPDATE 構文
をご存知でしょうか。
大量のデータを1度に INSERT, UPDATE するという構文です。
ラクマのあるシステムで、1件ずつの処理だと処理時間的に厳しい箇所で、INSERT ... ON DUPLICATE KEY UPDATE 構文
を利用していました。
しかし、この構文を使っている箇所について、下記の事象が発生しました。
- 意図せず AUTO_INCREMENT が進む
- int で保存できる最大値に達する (カンスト)
- 新しくデータが保存できなくなる
今回は、1. のところについての対処方法の共有をしたいと思います。
INSERT ... ON DUPLICATE KEY UPDATE 構文
について
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文
この構文を使えば、1本のSQLで、INSERT、UPDATE を実行することができます。
意図せず AUTO_INCREMENT が進んでしまった理由を調べていたところ、
UPDATE 文になった場合でも、AUTO_INCREMENT が進むらしいということが分かりました。
それでは、検証を始めます。
確認環境
$ mysql --version mysql Ver 14.14 Distrib 5.6.43, for osx10.13 (x86_64) using EditLine wrapper
検証
準備
CREATE DATABASE test;
CREATE TABLE `tmp_a` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uniq` varchar(20) NOT NULL, `cnt` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `idx01` (`uniq`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
現在の AUTO_INCREMENT を確認します。
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec)
INSERT 文が発行される場合 (id カラムに値を指定しない)
変更前
mysql> SELECT * FROM tmp_a; Empty set (0.00 sec)
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec)
実行
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (null,'u1', 1), (null,'u2', 2), (null,'u3', 3) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
変更後
データが登録されたことが確認できます。
mysql> SELECT * FROM tmp_a; +----+------+-----+ | id | uniq | cnt | +----+------+-----+ | 1 | u1 | 1 | | 2 | u2 | 2 | | 3 | u3 | 3 | +----+------+-----+ 3 rows in set (0.00 sec)
AUTO_INCREMENT も 3 進みました。
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec)
UPDATE 文が発行される場合 (id カラムに値を指定する)
変更前
mysql> SELECT * FROM tmp_a; +----+------+-----+ | id | uniq | cnt | +----+------+-----+ | 1 | u1 | 1 | | 2 | u2 | 2 | | 3 | u3 | 3 | +----+------+-----+ 3 rows in set (0.00 sec)
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec)
実行
cnt を 1000 倍にして、更新します。
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (1,'u1', 1000), (2,'u2', 2000), (3,'u3', 3000) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 6 rows affected (0.01 sec) Records: 3 Duplicates: 3 Warnings: 0
変更後
cnt が更新されました。
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1000 | | 2 | u2 | 2000 | | 3 | u3 | 3000 | +----+------+------+ 3 rows in set (0.00 sec)
AUTO_INCREMENT も変更されずそのままでした。
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.01 sec)
これは意図通り。
UPDATE 文が発行される場合 (id カラムに値を指定しない)
変更前
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1000 | | 2 | u2 | 2000 | | 3 | u3 | 3000 | +----+------+------+ 3 rows in set (0.00 sec)
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.01 sec)
実行
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (null,'u1', 1111), (null,'u2', 2222), (null,'u3', 3333) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 6 rows affected (0.01 sec) Records: 3 Duplicates: 3 Warnings: 0
変更後
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1111 | | 2 | u2 | 2222 | | 3 | u3 | 3333 | +----+------+------+ 3 rows in set (0.01 sec)
AUTO_INCREMENT が先に進んでしまいました。
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 7 | +----------------+ 1 row in set (0.00 sec)
ここで少し休憩です。
INSERT ... ON DUPLICATE KEY UPDATE 構文
を使うとき UPDATE する場合、AUTO_INCREMENT を進めないためには、下記カラムの値を指定する必要があることが分かりました。
id
(AUTO_INCREMENT)uniq
(DUPLICATE KEY)
INSERT, UPDATE 文が発行される場合
今回の問題発生箇所での使われ方です。
変更前
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1111 | | 2 | u2 | 2222 | | 3 | u3 | 3333 | +----+------+------+ 3 rows in set (0.01 sec)
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 7 | +----------------+ 1 row in set (0.00 sec)
実行
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (1,'u1', 1000), (2,'u2', 2000), (3,'u3', 3000), -> (null,'u4', 4), (null,'u5', 5), (null,'u6', 6) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 9 rows affected (0.02 sec) Records: 6 Duplicates: 3 Warnings: 0
変更後
登録、更新されたデータは意図通りでした。
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1000 | | 2 | u2 | 2000 | | 3 | u3 | 3000 | | 7 | u4 | 4 | | 8 | u5 | 5 | | 9 | u6 | 6 | +----+------+------+ 6 rows in set (0.00 sec)
6 つ進んでしまいました!!!
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 13 | +----------------+ 1 row in set (0.01 sec)
どうやら、登録しようとしているデータ全部を更新しなければ、AUTO_INCREMENT が進むようです。
対処方法
id カラムを削除する
DB を利用している側の制約上、削除できない可能性もありますが、
id カラムを削除すれば AUTO_INCREMENT のことを考えなくて良くなります。
INSERT文とUPDATE文を分ける
UPDATE 文のみであれば、AUTO_INCREMENT が更新されないことが分かったので、
SQL を分割するのが良さそうです。
所感
サービスとして、取り扱うデータ量が多くないと本件のような、何かしらの上限に達して発生する問題に立ち会うことが中々できないので、貴重な体験だと思いました。
それでは、失礼します。