MySQLでのバルクアップデートの性能比較

バルクインサートと比較すると要件が複雑なため、バルクアップデートの実現方法はいくつかあります。
この記事では3つの方法に対して実行速度の違いを比較します。

1. UPDATE ~ ELT + FIELD
2. UPDATE ~ CASE
3. INSERT ~ ON DUPLICATE KEY UPDATE

要件によっては上記以外の方法でも十分なケースもあるため、以下の条件を設定しています。

1. レコード毎に違う内容で更新
2. 更新前の値を使用
3. 特定のカラムのみを更新し、他のカラムは変更しない

実際にありそうなケースとしては、ユーザごとにポイントを保持するテーブルがあり定期的に集計処理を走らせてその内容に応じてポイントを加算するようなのを想定してます。

使用するテーブル

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `field2` int(11) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

UPDATE ~ ELT + FIELD

UPDATE
    `users`
SET 
    field2 = field2 + ELT(FIELD(user_id,1,2,3),1,2,3) 
WHERE user_id IN (1,2,3)

UPDATE ~ CASE

UPDATE 
    `users`
SET field2 = field2 +
CASE user_id
    WHEN 1 THEN 1
    WHEN 2 THEN 2
    WHEN 3 THEN 3
WHERE user_id IN (1,2,3)

INSERT ~ ON DUPLICATE KEY UPDATE

INSERT INTO
    users (user_id, field1, field2)
VALUES
    (1, "dummy", 1),
    (2, "dummy", 2),
    (3, "dummy", 3)
ON DUPLICATE KEY UPDATE
    field2 = field2 + VALUES(field2);

性能比較

100万レコードの内10万レコードを更新するようにそれぞれの方法ごとに3回ずつ処理時間を計測しました。
上に記述されている例は3レコードを更新する内容になっていますが、実際には10万レコード用に拡張して実行しています。

	平均	1回目	2回目	3回目
UPDATE ~ ELT + FIELD	77.6s	77s	77s	79s
UPDATE ~ CASE + THEN	215.3s	215s	215s	216s
INSERT ~ ON DUPLICATE KEY UPDATE	0.9s	1s	0.9s	0.9s

マシンのスペックは以下になります。
OS: macOS Mojave
プロセッサ: 2.4 GHz Intel Core i5
メモリ: 16 GB 2133 MHz LPDDR3

まとめ

実行時間に関してはINSERT ~ ON DUPLICATE KEY UPDATEが最速で、次にUPDATE ~ ELT + FIELD、最後がUPDATE ~ CASEという結果でした。
単なるUPDATE ~ WHERE INの実行時間は1sもかからないので、FIELDやCASEの箇所が大きく影響していそうです。FIELDとCASEでここまで差が出る理由は今度調査してみたいと思います。