Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
59
Help us understand the problem. What are the problem?

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の時間で処理出来るようになりました!

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
59
Help us understand the problem. What are the problem?