MySQLで複数行を一括更新するクエリの実装方法

1.はじめに

バッチ処理などで、データをまとめて扱いたいシーンがよくあります。
一括登録は知っていたのですが、一括更新もあるぞ、ということで実際のクエリやパフォーマンスについて調べてみました。

2.一括更新とは

ここでは、複数のレコードに対して、異なる値を更新する方法について、一括更新と呼ぶこととします。
通常の更新クエリにおいても、ある条件に対して複数のレコードを更新できますが、
更新する値についてはSET句で指定した特定の値で更新しかできません。
異なる値を更新を行う場合、通常の更新クエリでは、それぞれの更新する値毎にSQLを分けてクエリを実行します。
異なる値の更新を大量に行う場合、一括更新の方法で実行することで、クエリを１つずつ実行する場合に比べて、実行時間を大幅に短縮することができます。

3.通常の更新クエリ

テーブルの列を更新する場合は、以下のようなクエリを使用します。

UPDATE table_name SET column_name = new_value WHERE condition;

あるレコード3件にそれぞれの値を更新する場合、下記のように3つのクエリを実行する必要があります。

UPDATE ans SET question = ‘変更１‘, answer = ‘変更１‘ WHERE id = 80633

UPDATE ans SET question = ’変更２‘, answer = ’変更２‘ WHERE id = 80634

UPDATE ans SET question = ’変更３’, answer = ’変更３’ WHERE id = 80635

ansというテーブルの一意になる値idを条件に、questionとanswerの値を更新する。
というクエリです。

4.一括更新クエリ

上記の3つのクエリを下記のようにすることで1つのクエリで実行する事が出来ます。

UPDATE ans
SET
  question = ELT(FIELD(id, 80633,80634,80635), ‘変更１‘,’変更２‘,’変更３’),
  answer = ELT(FIELD(id, 80633,80634,80635), ‘変更１‘,’変更２‘,’変更３’)
WHERE id IN (80633,80634,80635)

基本構成

ELT、FIELDを用いることで特定のレコードに対して更新する値を表現することが出来ます。

UPDATE テーブル名
SET
  更新したいカラム = ELT(FIELD(キーカラム, 更新したい対象となるキーをカンマ区切り), 更新する値をカンマ区切り),
WHERE キーカラム IN (更新したい対象となるキーをカンマ区切り)

それぞれざっくり解説します。

ELT・・・インデックス番号位置の文字列を返します。

ELT() は、文字列リストの N 番目の要素を返します。

SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');
-> 'Aa'

SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');
-> 'Dd'

FIELD・・・後続の引数の最初の引数のインデックス (位置)

FIELD(str, str1, str2, str3, ...)
str1、str2、str3、... リスト内で str のインデックス (位置) を返します。 str が見つからない場合は、0 を返します。
1つ目の引数が2つ目以降の引数の何番目にあるかを返します。

SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
-> 2

SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
-> 0

ELT、FIELDの詳細については下記を参考にしてください。
https://dev.mysql.com/doc/refman/8.0/ja/string-functions.html

何が起きているか

関数の解説だけではなぜまとめて更新できるかイマイチわからないので、順番に分解していきます。

1. WHERE id IN (80633,80634,80635)
   WHEREのIN句によって特定のidのカラムが抽出されます。
   つまり、ここでは80633,80634,80635という3つのIDを持つレコードが更新対象に指定されたということです。

2. FIELD関数内のid
   WHERE句で抽出されたidがFIELDの第一引数のidに設定されます。
   WHERE句では3つのidが指定されているので、3つの値が順番に設定されることになります。
   感覚的にはプログラムのforeatch構文でfor文内での要素が指定した配列の順番に切り替わっているイメージです。

3. FIELD関数
   id = 80633の場合
   FIELD(id, 80633,80634,80635)　は、
   FIELD(80633, 80633,80634,80635)　となるので、
   FIELD関数の結果は 1となります。

4. ELT関数
   FIELD関数の結果が決まったので、ELT関数を解釈していきます。
   ELT(FIELD(id, 80633,80634,80635), ‘変更１‘,’変更２‘,’変更３’) は、
   ELT(1, ‘変更１‘,’変更２‘,’変更３’)　となるので、
   ELT関数の結果は、‘変更１‘となります。

5. UPDATE文
   整理すると1件目のid=80633は以下のSQLとなります。

UPDATE ans
SET
  question = ‘変更１‘,
  answer = ‘変更１‘
WHERE id = 80633

6. 2つ目、3つ目のid

1つ目と同様に、考えます。
id = 80634 の場合、

ELT(FIELD(id, 80633,80634,80635), ‘変更１‘,’変更２‘,’変更３’),

id を置き換え
⇒ ELT(FIELD(80634, 80633,80634,80635), ‘変更１‘,’変更２‘,’変更３’),

FIELD関数の結果に置き換え
⇒ ELT(2, ‘変更１‘,’変更２‘,’変更３’),

’変更２‘　となります。

id = 80635 の場合、

ELT(FIELD(id, 80633,80634,80635), ‘変更１‘,’変更２‘,’変更３’),

id を置き換え
⇒ ELT(FIELD(80635, 80633,80634,80635), ‘変更１‘,’変更２‘,’変更３’),

FIELD関数の結果に置き換え
⇒ ELT(3, ‘変更１‘,’変更２‘,’変更３’),

’変更３‘`　となります。

5.一括更新の実装例

以下は上記のクエリをGo言語で実装する一例です。

for j := 0; j < len(updEntities); j++ {
    ids[j] = updEntities["id"]
    qes[j] = "'" + updEntities["q"] + "'"
    ans[j] = "'" + updEntities["a"] + "'"
}
idss := strings.Join(ids, ",")
qess := strings.Join(qes, ",")
anss := strings.Join(ans, ",")

query := fmt.Sprintf(`
UPDATE %s
SET
  question = ELT(FIELD(id, %s), %s),
  answer  = ELT(FIELD(id, %s), %s)
WHERE id IN (%s)
`, r.TableName, idss, qess, idss, anss, idss)

updEntities・・・更新するレコードの更新後の値が入ったデータの配列
更新するidと更新する値をそれぞれカンマ区切りの文字列として整形して、クエリに埋め込むような実装です。

6.実際どのくらい早くなるのか

上記の実装を踏まえて実際に更新した際の処理時間をそれぞれ示します。

全体件数: 10万件
更新対象：72,674件
上記の更新対象を1トランザクション内で更新処理を実行し、コミット完了するまでの時間を計測
何度か実行を試した中での平均的な値です。

パターン	ミリ秒	秒	分
１：UPDATEを1レコード単位で実行	1,176,080ms	約1,176秒	20分弱
２：パターン１をゴルーチンで並列実行	584,434ms	約584秒	10分弱
３：ELT、FIELDを使った一括更新	14,075ms	約14秒	-

※パターン2であまり早くないのは、対象件数の更新を1トランザクションで行っているため。
プログラムからクエリの実行までは並列になるので早いのですが、MySQL側の実行でスタックされていると思われます。
1件更新でトランザクションを分ければもう少し早くなりそうですが、今回は条件を合わせるため、1トランザクションで実施しています。

※100万件中の70万件更新の場合でも、1分程でした

7.一括更新の注意点

ELT、FILELDを用いた更新はパフォーマンス向上に有用ではありますが、いくつか注意点があります。

プレースホルダが使いづらい。

Go言語の実装例でも示しましたが、クエリの実行のためにロジックを記述する必要がります。

出来上がるSQLが巨大になり評価しづらい。

複数の値を1度に更新するため、1つのクエリがかなり巨大になります。
クエリの不備がある場合、どこに不備があるかが分かりづらくなります。

あまり大量に更新しようとするとクエリサイズ上限に引っかかる。

更新する値によって件数が異なりますがある程度の件数を超えるとMySQLのクエリサイズの上限に引っかかり、エラーが返されます。

Update Error: packet for query is too large. Try adjusting the 'max_allowed_packet' variable on the serverUpdate Error: result is nilBulkUpdate Error: Rollback Error: sql: transaction has already been committed or rolled backexit status 1

システム変数の変更は可能であれば、max_allowed_packetの値を変更することで実行可能な件数を増やすことが可能です。
システム変数を増やすだけでは、更新するカラムの文字数によっては特定の件数を一度に更新できない場合もあります。
今回の記事で実行速度を検証した際は5,000件単位でクエリを区切って更新をかけています。

おまけ：再帰クエリを用いた更新

こちらはおまけですが、先日書いた再帰クエリにUPDATE文を繋げることで、
階層関係を持つデータについて一律に更新掛ける事も可能です。

WITH RECURSIVE search_tree(id, name, tid, tname, depth) AS (
    SELECT g.id, g.name, gt.id AS tid, gt.name AS tname, 1
      FROM `groups` g INNER JOIN group_types gt ON g.group_type_id = gt.id
     WHERE g.id = 'base'
UNION ALL
    SELECT g.id, g.name, gt.id AS tid, gt.name AS tname, search_tree.depth + 1
      FROM `groups` g INNER JOIN group_types gt ON g.group_type_id = gt.id
           , search_tree
     WHERE g.parent_id = search_tree.id
)
UPDATE resources r SET r.status = '3'
WHERE EXISTS (
  SELECT 1 FROM search_tree s
     INNER JOIN edges e ON e.end_node = s.id AND e.relation = 'joined'
   WHERE e.start_node = r.id
)

こちらのクエリは、階層関係にあるデータを対象に特定の値に更新する際には有効ですが、
それぞれのレコードに更新する値を変えたい場合、やはり一括更新のクエリで実行するのが効率的になります。

再帰クエリについてはこちら
https://zenn.dev/rescuenow/articles/c7d7291f2deed8#４．再帰的なクエリについて

まとめ

• 大量のレコードを更新する際は、一括更新を使うことでパフォーマンスを向上させることができる。
• ELT、FIELD関数を用いることで一括更新が可能
• 一括更新をする際には、注意点がある。例えば、プレースホルダが使いづらくなる、SQLが巨大になる、更新するレコードの件数が多すぎるとクエリサイズ上限に引っかかるなど。
• 再帰クエリを使用すると、階層関係を持つデータについて一律に更新することができる。
• 再帰クエリを使用する場合も、特定の値に更新する場合には一括更新がより効率的である。