LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@sousuke_neo(そうすけ)in株式会社Nint

N+1問題で1時間かかってたバッチをJSON_SET()で秒で終わらせた話

Posted at

はじめに

「ただのバッチ処理だし、そんなに重くないだろう」
──そう思っていた時期が私にもありました。

MySQLのJSONカラムに入った設定情報の一部だけを更新するバッチを任されたとき、最初は素直にループ処理で書いていました。

ところがリリース前、いざ本番データで動かしてみると、数千件の処理に1時間近くかかるという事態に。

調べてみると、処理自体は正しくても、N+1構成によるSQLの多発や、PHPのメモリ使用量の増加がボトルネックになっていました。

最終的には JSON_SET() を使って一括更新することで、処理時間は「秒単位」にまで短縮されました。

でも、そこに至るまでには、構造を壊さないことや検証をどう担保するかなど、意外と考えることが多かったのです。

この記事では、その経緯や工夫したポイントをまとめました。

環境構成

今回のバッチ処理は以下のような構成で運用されていました：

  • DB：TiDB（MySQL互換）
  • アプリケーション：PHP（Yiiフレームワーク）
  • 対象カラム：member.data（型は JSON ではなく TEXT）
  • 保存形式：JSON文字列として保存（MySQLのJSON関数は利用可能）

実際に格納されているデータの構造は以下のようなもので、複数のサービスごとの設定が1つのJSONオブジェクトにまとまっています。

{
  "serviceA": { "item_limit": 50 },
  "serviceB": { "item_limit": 0 },
  "serviceC": { "item_limit": 0 }
}

このうち、serviceB.item_limit の値だけを 0 → 5 に更新したいという要件がありました。

つまり、JSON全体のうち1キーだけを正確に変更し、他のデータは一切変更しないことが求められる処理です。

この構造と制約が、後述する実装方針やN+1構成の選択に大きく影響していきます。

初期実装（N+1構成）

最初の実装では以下のような処理を採用しました：

  1. 対象のユーザーIDをまとめて抽出
  2. 各IDについて1件ずつ
    • SELECTでdata取得
    • json_decode()して目的のキーを更新
    • json_encode()してUPDATEで保存

いわゆる N+1構成です。大量件数になると遅くなることはわかっていましたが、実はこれには明確な意図がありました。

なぜN+1だったのか？

理由は単純で、JSONの中に複数のサービス設定が同居しているからです。

バッチで更新したいのは serviceB.item_limit だけ。

JSON_SET() は指定したキーのみを変更してくれるSQL関数です。
取得して、値を更新したオブジェクトをセットして更新、という処理が必要なくなり、超便利です。

でも、実はJSON_SET() を使って一括更新してしまうと、他のサービスの構造や順序まで変わってしまう可能性があります。

To make lookups more efficient, MySQL also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases.
MySQLは検索効率を高めるため、JSONオブジェクトのキーもソートします。このソート結果は変更される可能性があり、リリース間で一貫性が保証されないことにご注意ください

たとえば、以下のようなJSONがあったとします：

{
  "serviceA": { "item_limit": 50, "shop_limit": 10 },
  "serviceB": { "item_limit": 0 },
  "serviceC": { "item_limit": 0 }
}

このうち serviceB.item_limit を5に更新するために JSON_SET() を使うと、MySQL（またはTiDB）が内部的にJSON構造を再構築するため、以下のようにキーの順番が変わることがあります：

{
  "serviceC": { "item_limit": 0 },
  "serviceA": { "shop_limit": 10, "item_limit": 50 },
  "serviceB": { "item_limit": 5 }
}

内容的には間違っていなくても、

  • 差分比較ツールで「構造が変わった」と判定されてしまう
  • JSONを文字列として管理している場合、履歴チェックでノイズになる
  • アプリ側でキー順に依存している処理があった場合、バグの温床になる

など、**想定外の問題につながる可能性があります。

エンジニア的には構造が崩れてなければ問題ないのですが、検証の手間にもなります。

そのため今回は、「更新対象のキー以外に絶対に触れない」ことを優先し、1件ずつ丁寧に処理するN+1構成を選びました。

事前検証で得られた安心感

実装に着手する前に、N＋1問題のSQLの実行計画と処理時間を事前に検証しました。
特にN+1構成で懸念されるパフォーマンスのボトルネックを明確化するために、EXPLAINを活用しています。

SELECT の実行計画と実測

EXPLAIN SELECT mid, data FROM member WHERE mid = 1;
id estRows task access object operator info
Point_Get_1 1.00 root table:member handle:1

これは主キーに対する Point Get（最速アクセス） を示しており、インデックス効率に問題がないことがわかります。

実際に5回計測した平均実行時間は以下の通りです：

SELECT mid, data FROM member WHERE mid = 1;
SELECT mid, data FROM member WHERE mid = 2;
SELECT mid, data FROM member WHERE mid = 3;
SELECT mid, data FROM member WHERE mid = 4;
SELECT mid, data FROM member WHERE mid = 5;
回数 時間（秒）
1 0.095
2 0.179
3 0.146
4 0.298
5 0.215

平均：(0.095 + 0.179 + 0.146 + 0.298 + 0.215) / 5 = 約0.1866秒

2000件のレコードを処理すると仮定すると：

0.1866秒 × 2000件 / 60 = 約6.22分

UPDATE の実行計画

EXPLAIN UPDATE member SET data = :data WHERE mid = 90193;
id estRows task access object operator info
Update_3 N/A root N/A
└─Point_Get_1 1.00 root table:member handle:90193

更新も主キーアクセスで最適。1行ずつの更新でボトルネックはないことが確認できました。

検証から得られた確信

  • 主キーアクセスによるボトルネックはない
  • 処理時間の大部分は 「SQLが多すぎる（N+1）」という構造の問題

この検証によって、「バルク更新への改善は効果的」という仮説を裏付けることができました。

見えてきた問題

N+1による処理時間の増大

  • SELECTとUPDATEを各IDごとに繰り返すため、数千件処理するだけでもSQL発行回数が膨大
  • 実測では、1件あたり平均 0.1866 秒ほどかかっており、2000件で6分以上（+更新含めて10分程度）

メモリ負荷の懸念

  • 一括でデータを取得・json_decode()した場合、PHP側のメモリ消費が増大
  • 件数が増えるとOOM（Out Of Memory）リスクも無視できない

改善：バルク更新へ

JSON_SET() による一括更新を決断

パフォーマンス改善のため、JSON_SET() を使って 対象ユーザーを一括で更新するバルク処理に切り替えました。

UPDATE member
SET data = JSON_SET(data, '$.serviceB.item_limit', 5)
WHERE id IN (...);
  • 対象IDをあらかじめまとめて取得し、SQL1本で更新
  • 処理時間が劇的に改善（数千件でも数秒〜十数秒）

注意点・落とし穴

IN (...)句の上限

  • RDBMSによっては IN (...) の要素数に制限があります（例：MySQLではデフォルト65,535）
  • 実行時にコケる可能性があるため、件数が多い場合は分割して処理するのが無難

JSON構造のキー順が保証されない

  • JSON_SET() はMySQL側でJSONオブジェクトを再構築するため、キーの順番が変更される
  • 構文的には問題なくても、ログや差分比較で混乱の原因になることがあります

検証の重要性

「構造が壊れていないか？」「意図したキー以外が変わっていないか？」を確かめるために、差分比較スクリプトを作って検証を行いました。

チェック内容：

  • json_decode() で正常にパースできるか
  • item_limit 以外のキーに変更がないか
  • 構造の破損や型変換が発生していないか

この検証によって、バルク処理でも安全性を確保できたことが確認できました。

結論と学び

観点 学び
パフォーマンス JSON_SET() による一括更新は圧倒的に速い
安全性 差分検証で意図しない変化を防げる
検証体制 更新スクリプトには検証コードの併設が不可欠
設計判断 「あえて遅い実装を選ぶ」ことにも意味がある

おわりに

「ただのバッチ処理」だと思って始めた作業でも、実際には構造の制約・安全性・パフォーマンス・検証体制など、考慮すべきことがたくさんありました。

今回のように、設計意図と改善プロセスをしっかり検証・記録しておくことが、次の実装やチーム共有に必ず活きると実感しました。

似たような状況で悩む方の参考になれば幸いです 🙌

0
0
0

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
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?