search
LoginSignup
60

More than 5 years have passed since last update.

posted at

MySQLで大量のUPDATEを流したい時はELT、FIELDキーワードを用いたBULK UPDATEで高速化

タイトルの通りです。
こんなSQLを大量に流したい場合です。

UPDATE heyas SET yatin = 60000 WHERE id = 1;
UPDATE heyas SET yatin = 67000 WHERE id = 2;
UPDATE heyas SET yatin = 74000 WHERE id = 3;
.
.
.

まずINSERTにON DUPLICATE KEY UPDATEキーワードを使うやり方です。


INSERT INTO heyas (id, yatin) 
VALUES(1, 60000), (2, 67000) 
ON DUPLICATE KEY UPDATE yatin = yatin * 1.08;

REPLACE構文に近いイメージですね、しかしこれだと任意の値をセットしづらいです。
そこでELTとFIELDキーワードを使います。


UPDATE heyas
   SET yatin = ELT(FIELD(id, 1, 2), 60000, 67000)
 WHERE ID IN (1, 2)

良い感じですね、複数カラムを更新したい場合はこうなります。


UPDATE heyas
   SET yatin = ELT(FIELD(id, 1, 2), 60000, 67000), 
       kousinbi = ELT(FIELD(id, 1, 2), NOW())
 WHERE ID IN (1, 2)

BULK INSERTは結構知られてるのですが、BULK UPDATEはあまり知られてない感じです(私もでした)
数千件程度ならベタ書きで流せば良いのですが、UPDATEは結構コストがでかいので数万から数十万件になってくるとオーバーヘッドがバカにならないです。
実際に20万件程度のテーブルでクラスタリングして相互にUPDATEする様な処理で試したところ、順次UPDATEする処理に比べてBULK UPDATEは約1/50の時間で処理出来るようになりました!

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
60