ど素人から毛を生やす。<延>

[MySQL]複数行のUPDATEを1回のSQL実行で済ませたい。

Web > Other 2018年12月14日(最終更新:30日前)

どもです。

MySQLで複数の行を更新したい場合、多くの場合は

foreach($array as $id => $value){
	$sql[] = 'UPDATE `table` SET `value` = {$value} WHERE `id` = {$id}';
}

などの方法で、UPDATEを複数回実行することになると思います。

が、当たり前ですが、これだと都度MySQLにアクセスすることになるため、大量の更新を行いたい場合だとDBへの負担とか所要時間とかが大変なことになります。

こういったとき、できるだけDBの負担を軽くしたい。
できれば1回のSQL実行で事を収めたい。

この1回の実行で複数行を更新することを、bulk(バルク) updateというそうです。

bulk insert

このバルク(大量・一括の意)処理は、INSERTの場合はまさにそのもののやり方がサポートされています。

INSERT INTO `table` (`id`, `text`) VALUES (1, 'textA'), (2, 'textB');

#カラム名を省略しても可能。(先頭のカラムから順に埋める)
INSERT INTO `table` (1, 'textA'), (2, 'textB');

しかし、UPDATEの場合はクリティカルなサポートがありません。

参考サイト:[おぷさブログ][TIPS][MYSQL]複数レコードのinsertを1回で!ステキな バルクインサート

bulk update ①INSERT ... ON DUPLICATE KEY UPDATE 構文

ON DUPLICATE KEY UPDATEを使うことで、データがあればUPDATE、無ければINSERTができます。

INSERT INTO `table` (`id`, `text`) VALUES (1, 'textA'), (2, 'textB')
ON DUPLICATE KEY UPDATE `text` = VALUES(text);

このVALUES()がミソ。INSERTに使いたかった値をそのまま使用できます。
また、INSERTの方にプライマリキーやユニークキーを指定する必要があります。
全体的に、ちょっと慣れない書き方といった印象。

ただこれ、実質バルクアップデートですが、厳密には違うんですよね。
UPDATE対象が確実に存在するなら良いのですが、場合によっては宙ぶらりんな行を生み出すことに。

参考サイト:[Qitta]MySQL: INSERT...ON DUPLICATE KEY UPDATEまとめ

bulk update ②SETの中でCASEを使う

UPDATE `table` SET `text` = 
	CASE `id` 
		WHEN 1 THEN 'textA' 
		WHEN 2 THEN 'textB' 
	END
WHERE `id` IN (1, 2);

僕たちの知ってるUPDATEの基本形を壊さないまま、バルクアップデートを実現できました。やったぜ。

ただ、これって、速度的にどうなの?(;´・ω・)

と思う方がいると思います。僕は思いました。
いちいちCASE判定を挟んでいるということは、WHENの数だけチェックを試行してるわけだし。

ここで少し調べてみたのですが、なんとこの記述、見た目に反してかなり速いそうです。
少なくとも膨大な件数を一括でアップデートする場合、1件ずつ実行と比べてその速度は5倍以上なのだとか。

では、少ない件数の場合は?
これはGoogle先生、教えてくれない。それなら自分で調べるしかないね。検証環境はMySQL5.0.10。

測定方法は前回のと同じ。
PHPを経由しているので純粋なMySQLの実行時間ではないですが、まぁ実務に近い方が参考になるということで。

●更新1件の場合(5,000回実行)
 
[1件ずつ]
1回目⇒24.17 [s]
2回目⇒22.83 [s]
3回目⇒23.84 [s]
 
[SETの中でCASEを使う]
1回目⇒20.16 [s]
2回目⇒23.73 [s]
3回目⇒22.05 [s]

ほぼ同じ…どころか、CASEの方が誤差の範疇レベルだが速い!?まじか!?

どうやら複数行の単純な更新は問答無用でSETの中でCASE、で問題なさそうですね。

参考サイト:[hikage's 雑記blog]超倍速!?複数のレコードの更新を1回で実施するbulk update!

この記事は役に立ちましたか?
  • (・∀・) 参考になった (0)
  • (`・ω・´) 役に立った (0)
  • _(:3」∠)_ 面白かった (1)