【Laravel】【MySQL】バルクアップデートで高速に大量のレコードを更新する

　後付けで定まる値――例えば集計結果の値を持つカラムを持つテーブルを作る時があります（正規化に反してでも速度が要求される時など）。こういったカラムを持つテーブルについて整合性を保ったテストデータを作る時、あるいは集計結果を定期的に更新する時などに大量のレコードを一度に更新する必要が出てきます。ここでは MySQL と Laravel を使った大量のレコードを一気に高速に更新する方法を紹介します。

　例としていいねとコメントの総数を持つ次の投稿テーブルを考えます。

　使う SQL 文は次の様な CASE 文です。

UPDATE posts
SET `good_count`    = CASE `post_id`
                          WHEN 1 then ?
                          WHEN 2 then ?
                          WHEN 3 then ?
                          WHEN 4 then ?
                          WHEN 5 then ? END,
    `comment_count` = CASE `post_id`
                          WHEN 1 then ?
                          WHEN 2 then ?
                          WHEN 3 then ?
                          WHEN 4 then ?
                          WHEN 5 then ? END
WHERE `post_id` in (1, 2, 3, 4, 5)
# ?には以下を割り当て
# [post_id=1のいいね総数、post_id=2のいいね総数、...、post_id=5のいいね総数、post_id=1のコメント総数、...]

　主キーである post_id についてのケース文を更新対象の各カラムについて構築します。これで一クエリ（REPLACE の様なエセでなくきっちり一トランザクションです）で一気に更新できます。
　Laravel のデフォルトのクエリビルダでこれを構築する機能はありません。ここでは生 SQL を構築して投げます（テストデータ生成時なら安全性が気にならないのでこれでも十分です。そういう時でない時は都度テストして実装することになります）。

        // 主キー => いいね総数を呼べるオブジェクト となる連想配列的 Collecion を構築
        $goodCounts = PostGood::select(['post_id', DB::raw('count(*) as agg')])
            ->groupBy('post_id')
            ->orderBy('post_id')
            ->get()
            ->keyBy('post_id');
        // 主キー => コメント総数を呼べるオブジェクト となる連想配列的 Collecion を構築
        $commentCounts = PostComment::select(['post_id', DB::raw('count(*) as agg')])
            ->groupBy('post_id')
            ->orderBy('post_id')
            ->get()
            ->keyBy('post_id');
        // 更新対象の投稿を取得
        $posts     = Post::withoutGlobalScopes()->get();

        $chunkSize = 5000;
        $postId    = 1;
        // チャンク機能で 5000 件ずつ処理
        $posts->chunk($chunkSize)->each(
            static function ($collect) use ($commentCounts, $goodCounts, $chunkSize, &$postId) {
                $cases = []; // CASE の各 id についての部分を構築
                $ids = []; // 更新対象の id を格納
                $goodCountParams = []; // バインドするいいね総数パラメータを格納
                $commentCountParams = []; // バインドするコメント総数パラメータを格納
                // チャンクの数だけ回してパラメータを構築
                // 主キーの欠けがない前提ならばキーを用意せずに id 相当の変数をインクリメントするだけで十分
                $collect->each(static function () use (&$postId, $goodCounts, $commentCounts, &$cases, &$ids, &$goodCountParams, &$commentCountParams) {
                    $cases[] = "WHEN {$postId} then ?";
                    $goodCountParams[] = $goodCounts[$postId]->agg ?? 0;
                    $commentCountParams[] = $commentCounts[$postId]->agg ?? 0;
                    $ids[] = $postId;
                    ++$postId;
                });

                // クエリの文面になる様に配列を結合
                $idsStr = implode(',', $ids);
                $casesStr = implode(' ', $cases);

                // sql を構築
                $sql = <<<SQL
UPDATE posts SET
     `good_count` = CASE `post_id` {$casesStr} END,
     `comment_count` = CASE `post_id` {$casesStr} END
WHERE `post_id` in ({$idsStr})
SQL;
                // 更新を実行。パラメータはスプレッド演算子（PHP7.4の機能）で一つの配列に結合
                \DB::update($sql, [...$goodCountParams,  ...$commentCountParams]);
            }
        );

　こんな感じで CASE を使った UPDATE を使うと一レコードずつ UPDATE するより速く処理を実行できます。

　一旦テーブル内の全レコードを削除するクエリを使って次の様にすることもできます。

// あらかじめ posts テーブルの中を PHP 内の $posts 変数に展開
        // 外部キー制約を無効化
        DB::statement('SET FOREIGN_KEY_CHECKS = 0');
        // posts テーブルの中のレコードを全削除
        DB::statement('truncate posts');
        $posts->chunk($chunkSize)->each(
            static function ($collect) use ($commentCounts, $goodCounts, $chunkSize, &$bar, &$postId) {
                $collect = $collect->map(static function (Post $post) use (&$postId, $goodCounts, $commentCounts) {
                    // PHP の中の posts のレコード情報を更新
                    $post->good_count = $goodCounts[$postId]->agg ?? 0;
                    $post->comment_count = $commentCounts[$postId]->agg ?? 0;
                    $post->setHidden([]);
                    ++$postId;

                    return $post->toArray();
                })->toArray();
                // バルクインサート機能でまとめてインサート
                Post::insert($collect);
            }
        );
        // 外部キー制約を有効化
        DB::statement('SET FOREIGN_KEY_CHECKS = 1');

　この辺りを上手く使うと素朴に一レコードずつ更新するより格段に速くデータの更新をできます。