Laravelで再帰クエリ + 一時テーブルの組み合わせがいい感じだった話

背景

こんにちわ、株式会社THIRD IT開発部の岩見です。

先日、僕らが開発に携わる、不動産管理SaaS「管理ロイド」では、大規模なリニューアルが行われました。

リリースにあたっては、新機能追加のほか、従来から技術的な課題が幾つかあり、その解消を行うことも目指していました。課題で特に大きいものが、データ増大に伴うパフォーマンス改善です。

管理ロイドでは、業界最大級の建物物件データを日々、蓄積しております。物件に紐づく子の設備機器の数、孫の業務項目数、ひ孫の点検結果データ・不具合データといったものが、相当に大きなデータ量となっているため、それらをパフォーマンス良くシステムで扱うためには、通常のLaravelの枠組みを使って実装するだけでは足りなくなってきています。（管理ロイドでは、バックエンドにLaravelを使用しています）

今回その辺りにスポットをあてて、どんな手段で課題に対応したかの一例をお話ししたいと思います。

課題

管理ロイドでは、A社がB社に、またB社がC社に業務を依頼するという、建物管理の業務委託の流れをデータとして保持しております。これを、次の属性を持つテーブルで管理しています。

業務依頼管理テーブル

operation_id -- 業務のID
building_id -- 物件のID
src_company_id -- 依頼元会社ID
dest_company_id -- 依頼先会社ID

このテーブルでは業務を直接依頼した・直接依頼されたの関係を、src_company_id, dest_company_idに自社IDを指定することで取得できますが、例えば「業務依頼先を末端まで辿って全て取得したい」という要件に対しては、この方法ではできません。

こんな風に繰り返し問い合わせることで取得が可能ですが、ひとつでなく複数件の依頼元に対して取得したいとなるとパフォーマンス的な問題があります。

$destCompanyIdList = [];
do {
    $destCompanyId = $業務管理依頼管理テーブル->select('dest_company_id')->where('src_company_id', $company_id)->first();
    $destCompanyIdList[] = $destCompanyId;
} while ($destCompanyId !== null);

再帰クエリでツリーデータを参照する

テーブル構造を変えずに、業務依頼のツリーを高速に参照するには、再帰クエリを使うのが最も良い選択肢となりました。
これまで管理ロイドではMySQL5.7を使用していたため、再帰参照ができなかったのですが、リニューアルに伴いMySQL8.0にアップデートしており、使えるようになりました。

業務依頼管理テーブルから、ある会社の業務依頼先の末端までツリーで取得するには、
次のような再帰クエリをヒアドキュメントで実行します。
パラメータは事前にエスケープ処理しておきます。

依頼元を起点に、依頼先を末端まで辿る

$sql = <<<SQL
WITH RECURSIVE company_chain AS(
    SELECT
        operation_id,
        src_company_id,
        dest_company_id,
        building_id,
        0 AS depth
    FROM
        業務依頼管理テーブル
    WHERE
        src_company_id = $会社ID
    UNION ALL
    SELECT
        業務依頼管理テーブル.operation_id,
        業務依頼管理テーブル.src_company_id,
        業務依頼管理テーブル.dest_company_id,
        業務依頼管理テーブル.building_id,
        cc.depth + 1
    FROM
        業務依頼管理テーブル
        INNER JOIN
            company_chain cc
        ON  業務依頼管理テーブル.src_company_id = cc.dest_company_id
        AND 業務依頼管理テーブル.operation_id = cc.operation_id
    WHERE
        depth < 10 /* 万一データが循環していた時に備え、一定の深さで打ち切る */
)
/* depth 0が依頼元の会社 */
SELECT DISTINCT operation_id, src_company_id, dest_company_id, building_id, depth FROM company_chain;

SQL;
DB::statement($sql);
)

データがもし不正な状態で、依頼元と依頼先が循環していると、再帰が終わらずDBサーバーが停止する事態になってしまうので、予防条件を指定しておきます。（検証環境で、テストデータが混じって循環しており、クエリが終わらず、焦りました😅）

その他の、再帰クエリの活用事例

再帰クエリは、ツリーを辿る他にも便利な使い方があります。
例えば、次のように日付の集合を作れます。（postgresでいうgenerate_series関数の代わり）

ある月の日付集合を作る

$startDate　= '2024-04-01';
$endDate　= '2024-04-30';
$sql = <<<SQL
WITH RECURSIVE date_ranges AS (
  SELECT $startDate AS date -- 開始日
  UNION ALL
  SELECT DATE_ADD(date, INTERVAL 1 DAY)
  FROM date_ranges
  WHERE DATE_ADD(date, INTERVAL 1 DAY) <= $endDate -- 終了日
)
SELECT * FROM date_ranges;
SQL;

DB::statement($sql);

この日付集合テーブルと対象データをLeft Jonすると、例えばカレンダーの日付ごとの集計を、SQL上で行うことができます。Laravel側でコレクションを用いて日付ごとにグルーピングすることをしなくて済み、パフォーマンスも良好になります。

再帰クエリの課題を一時テーブルで対処する

このように便利な再帰クエリですが、LaravelのEloquentビルダーがサポートしていません😢
現状はヒアドキュメント等で生SQLを書く形となります。これをサブクエリとして既存のEloquentのビルダーに組み込もうとすると、複雑怪奇な記述を強いられ、大変になります。
addBindings, mergeBindingsという、良くわからない仕組みもあるので、できれば避けたいところです。（複雑な組み立てになると、パラメータが想定外のところにバインドされていたり、バグも怖い。全体をヒアドキュメントで書き直した方がずっとマシだと感じます...）

しかし、再帰クエリを一時テーブル（CREATE TEMPORARY TABLE）に登録するようにすると、Eloquent上で扱いやすくなります🙌

一時テーブルとは、MySQLのメモリ上にリクエスト中にだけ有効なテーブルを作成するものです。
WITH RECURSIVEの結果をここに登録してしまえば、その後処理では、LaravelのEloquentビルダー上で普通のテーブルと同じように使える感じです。

さきほどの業務依頼管理テーブルでいうと、こんな形で登録します。

一時テーブルに再帰の結果を格納

$sql = <<<SQL
CREATE TEMPORARY TABLE primary_company_chain_temporaries
(
    operation_id INT,
    src_company_id INT,
    dest_company_id INT,
    building_id INT,
    depth INT,
    INDEX op_idx (operation_id), -- インデックスも貼れる
　　　　　　　　INDEX src_op_idx (src_company_id, operation_id)
);
DB::statement($sql);

$sql = <<<SQL
INSERT INTO chain_temporaries_primary_starts (operation_id, src_company_id, dest_company_id, building_id, depth)
WITH RECURSIVE ...（省略）

これで、問題の結合の記述もらくに行えます。

Eloquentでの利用

Operation::join('chain_temporaries_primary_starts', 'id', '=', 'chain_temporaries_primary_starts.operation_id')

効果はばつぐんだ、と言いたいところですが、一時テーブルには気をつけるべきところも幾つかありましたのでその点はご注意ください💦

• 同じクエリ内で、一時テーブルを複数回参照することはできない（サブクエリや、UNIONもNG）
• 処理前にテーブルインサートするので、結果セットが大きいものに対しては不向き ^[1]

レプリケーション環境での注意事項

レプリケーション環境での一時テーブル利用は、さらに注意が必要です。
MySQL本番環境コンソールで、グローバルパラメータのBINLOG_FORMATを確認してみてください。

BINLOG_FORMATの確認

SHOW VARIABLES LIKE 'binlog_format';

もし、BINLOG_FORMAT=STATEMENTとなっていれば、一時テーブルの構築・削除のクエリがレプリケーションされるようになっています。
この状態だと、もし一時テーブルが削除される前にサーバーが途中で停止することがあれば、一時テーブルがレプリカサーバに残ってしまい、障害につながる可能性があるようです。
BINLOG_FORMAT=ROWだと一時テーブルのレプリケーションはされないようなので、その環境で使用するのが安全かと思います。（これでしか使っていません...）

また、一時テーブルはマスタサーバにしかないので、一時テーブル作成後、もし同じリクエスト中でレプリカサーバに対して一時テーブルを参照しに行くことがあると、エラーになってしまいます。
これを回避するには、次のような対処が必要になります。

・　一時テーブルを作成したトランザクションと同じトランザクションでのみ参照する^[2]
・　一時テーブルが含まれるクエリを参照する際に、接続先を明示的に指定する

接続先の指定

DB::connection('mysql::write')->table('users')->get();

User::on('mysql::write')->get();
//別の方法
$user = new User();
$user->setConnection('mysql::write');

まとめ

今回は既存のツリー参照のパフォーマンスが良くない課題を再帰クエリを使って解決し、その再帰クエリがLaravelのEloquentで扱いにくい課題については、一時テーブルを使って解決できることを見ていきました。
正直一時テーブルは、複雑なクエリはRawクエリで書いてしまえばいいので、、現状無理して使うことはないかと思うのですが、選択肢として使えることに慣れておくと、色んな場面でより良い対応ができるのかなと思います！

脚注

1. 今回挙げたような再帰処理後の結果セットは、小さいことが多いと思うので適していると思います。データが大きい場合でもバルクインサートすれば、インデックスが貼れることもあり、問題ないかもしれません。その場合、RDBのメモリをどれだけ使用するかの確認は必要かと思います。今回の実装箇所では、見積リクエスト数と、できるテーブルのサイズをかけたメモリ使用量は、少ない数値に収まっていました。

2. writeトランザクション中は、参照もマスター接続で行われる認識