🚃

Laravelのページネーションで自動発行される件数カウントクエリを削減する

wamin

2023/08/21に公開

背景

私の働く株式会社THIRDでは、不動産管理の業務効率を改善する複数のSaaSを事業展開していて、
そのうち最も歴史の長いサービスが管理ロイドです。管理ロイドのWebアプリ側では、バックエンドのフレームワークにphpのLaravelを使っています。
管理ロイドは業務アプリケーションなので、各機能のAPIで発行するクエリが複雑になりがちです。
サービス成長に伴いデータが増大することで、クエリのボトルネックが発覚し、APIのレスポンスが遅くなるケースがあります。
その際は、クエリのチューニングやアプリケーションコードの見直しが必要になってきます。今回は、適用できる場面では大きな効果の見込める手段として、件数表示ありページネーション利用時の発行クエリを削減する方法を紹介したいと思います。

Laravelのページネーション

Laravelのページネーションを使い、1〜10 / 100といった表示範囲の件数 / 総件数のデータを生成する処理は、Illuminate\Pagination\LengthAwarePaginatorによって行われます。
このクラスは、直接パラメータを渡して使うことができますが、EloquentBuilderの内部にも組み込まれているので、$builder->paginate()メソッドを介して使うことができます。

しかし、LengthAwarePaginatorのパラメータに渡す総件数は、通常、別途DBに対して件数カウントクエリを発行して取得する必要があります。また、paginate()を使う場合は、Laravelがビルダーに設定されたクエリを元に、自動的に件数カウントクエリを発行します。

つまりLaravelは、総件数つきページネーションを行う場合、基本的に件数カウントクエリの発行をなくすことはできない仕様となっています。

参考までに、フレームワーク内部の$builder->paginate()メソッドは、以下のようになっています。
※本文は全て、Laravelのバージョン9.44.0を例にしています。


public function paginate($perPage = null, $columns = ['*'], $pageName = 'page', $page = null)
{
	$page = $page ?: Paginator::resolveCurrentPage($pageName);

	// ここで件数カウントクエリを発行している
	$total = $this->toBase()->getCountForPagination();

	$perPage = ($perPage instanceof Closure
	    ? $perPage($total)
	    : $perPage
	) ?: $this->model->getPerPage();

	$results = $total
	    ? $this->forPage($page, $perPage)->get($columns)
	    : $this->model->newCollection();

	return $this->paginator($results, $total, $perPage, $page, [
	    'path' => Paginator::resolveCurrentPath(),
	    'pageName' => $pageName,
	]);
}

$this->toBase()->getCountForPagination();

は、SQLで見ると、Builderにクエリされた値を丸ごとサブクエリにして、count()を取っています。

select count(*) as aggregate from (元のクエリ)

つまり件数カウントクエリの実行コストは、元のクエリとほぼ同じです。
元が遅いクエリであるほど、2倍の実行コストは気になるところとなります。

今回の対応では、件数カウント目的のみのクエリを発行しない方法で解決策を探ってみました。^[1]

Window関数を使った解決策

アイディアは、こちらの記事を参考にさせて頂きました。

Window関数のCOUNT(*)OVER()を使って、
クエリの取得列と横持ちで件数を取ってしまおうという内容です。

Window関数は、現状、MySQL5系以外のRDBでなら、ほぼ使えると思います。（管理ロイドではMySQL8.0で使っています。）

基本的にSELECT列に

COUNT(*) OVER() as aggregate

を加えてあげるだけで、一つのクエリで件数も取れます。EloquentBuilderのコード的には以下のようになりますね。

User::addSelect(\DB::raw('COUNT(*) OVER() as aggregate'));

取得結果とLengthAwarePaginator()との組み合わせで、件数カウント取得クエリを除いたページネーションが実現できます。

ただし、現在稼働中のコードでは、LengthAwarePaginator()でなく、EloquentBuilerに組み込みのpaginate()を使っている箇所が多く、前後の部分も含めて改修することは負担に思えました。
そのため、Eloquentビルダーに新たなpaginateメソッドを追加して対処することにしました。

カスタムEloquentBuilderでpaginate()を拡張する

先述の内容通り、既存の
Illuminate\Database\Eloquent\Builder;
では、paginate()メソッド内で件数カウントクエリを発行してしまっています。

window関数を使うことで、この挙動は不要になりますので、EloquentBuilderに件数カウントクエリを発行しないpaginate()を追加したいと思います。

Buidlerに独自のメソッドを追加・拡張する方法はいくつかありますが、今回はIlluminate\Database\Eloquent\Modelクラスの
newEloquentBuilderメソッドをオーバーライドする方法を使いました。^[2]^[3]

具体的には下記のように、各ModelsでnewEloquentBuilder()を上書きしてあげればよいです。
Traitにすれば各Modelsに設定しやすくなります。

<?php

namespace App\Customize;
use Illuminate\Database\Eloquent\Model;

trait OrmBuilderTrait
{
    /**
    * @param \Illuminate\Database\Query\Builder $query
    * @return EloquentBuilder<Model>
    */
    public function newEloquentBuilder($query): EloquentBuilder
    {
        return new EloquentBuilder($query);
    }
}

ここでは同階層（App\Customize）に、件数カウントクエリを省いたカスタムのpaginate()を定義しています。名前はとりあえずpaginateWithOutCountQuery()としました。

<?php

namespace App\Customize;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Pagination\Paginator;
use Illuminate\Pagination\LengthAwarePaginator;
use Exception;

/**
 * @template TModelClass of \Illuminate\Database\Eloquent\Model
 * @extends Builder<TModelClass>
 */
class EloquentBuilder extends Builder
{
    /**
     * 件数用にクエリを発行しない独自のpaginateメソッド。
     * 件数は各クエリで設定したaggregate列から取得している
     * @param $perPage
     * @param $columns
     * @param $pageName
     * @param $page
     * @return LengthAwarePaginator
     */
    public function paginateWithOutCountQuery($perPage = null, $columns = ['*'], $pageName = 'page', $page = null): LengthAwarePaginator
    {
        $page = $page ?: Paginator::resolveCurrentPage($pageName);

        $perPage = $perPage ?: $this->model->getPerPage();
        $results = $this->forPage($page, $perPage)->get($columns);
        $total = 0;
        if ($data = $results->first()) {
	    #SELECT列に持たせたaggregateから件数を設定
            $total = $data->aggregate;
        }
        return $this->paginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
            'pageName' => $pageName,
        ]);
    }
}

これで、

$builder->paginate()

以外に、

$builder->paginateWithOutCountQuery()

が使用できるようになります。

QueryBuilderのpaginationは？

クエリビルダは、Illuminate\Database\Eloquent\Modelを使わないクエリ発行の構文で、

DB::table('users')->get()

のような書き方をします。使用するクラスはIlluminate\Database\Query\Builderになります。
Laravel9.4には、このクラスにもpaginate()が含まれていますが、こちらを拡張しようとすると、手順はかなり複雑になるようです。

結果的に、クエリビルダに関しては、既存のコードでpaginate()を使っている箇所が見当たらなかったため、特に拡張を行わず、ページネーションの必要が生じた場合は前述したようにaddSelect()とLengthAwarePaginator()を使って対処することにしました。
むやみにフレームワークを拡張すると、Laravelコアのアップデート時に注意しないといけない問題もあるので...。

UNION, DISTINCTクエリにはそのまま使えない

ひとまずこれで件数カウント処理を省くことができた、と喜んだのですが、落とし穴がありました。
$builder->addSelect()だけでは、UNION, DISTINCTの時にうまくいきません。
SQLで考えれば、その原因はわかりやすいと思います。

UNIONであれば、次のような形になり列数不一致のエラー、

(
SELECT * COUNT(*)OVER() as aggregate FROM t1 
UNION 
SELECT * FROM t2
)

DISTINCTでは、COUNT()OVER()が、重複除外前の件数でカウントしてしまうため、件数が一致しない結果になります。

SELECT DISTINCT c1, c2, COUNT(*)OVER() as aggregate FROM t1

UNION, DISTINCTを使うクエリに適用する場合は、サブクエリにしてあげると正しい結果となります。

SELECT *, COUNT(*)OVER() as aggregate
(
	SELECT * FROM t1 
	UNION 
	SELECT * FROM t2
) as tmp

SELECT c1, c2, COUNT(*)OVER() as aggregate
(
	SELECT DISTINCT c1, c2 FROM t1
) as tmp

上記のような形になるよう、$builder->fromSub()などを活用して、ケースに応じて組み立てることで対処します。

ここまでBuilderの拡張パターンを紹介しましたが、個人的には、Laravelのサブクエリは触りにくく、可読性も悪いので、複雑なクエリを作る場合は、$builder->paginate()を使わず、DB::select() + LengthAwarePaginator()でもいい気がしています...。ただ、Builderの機能を使いたい場面では役に立つかなと。

まとめ

今回取り上げたのは、Laravelを普通に使っていると避けられないコストの部分ですが、やり方によっては削減できることが確認できました。フレームワーク内部の拡張は、気がひけるところもありますが、DB側など低レイヤーの知識で課題解決できそうであれば、フレームワーク側でそれを使えるようにするべきかと思います。
実際、今回の成果としては、特定のAPIの応答速度を半減させる効果がありました。お手軽に試せるので、ページネーションありのAPI・画面の遅さに悩んでいる方は、導入してみてはいかがでしょうか🙋‍♂️

脚注

LengthAwarePaginator()を使えば件数カウントクエリは自前で作れるので、その実行速度をチューニングする方法で対処できないかと思いましたが、中々難しそうでした。
ページネーションを利用する画面では多くの場合で検索機能があり、その関連部分を削れなかったり、Join自体にコストがかかっていたりする場合があるためです。 ↩︎
newEloquentBuilderでなくnewBaseQueryBuilderメソッドを差し替えた場合、オーバライドはIlluminate\Database\Eloquent\QueryBuilderに影響します。
ややこしいですが、Eloquent\QueryBuilderは、記事内のクエリビルダのことではなく、EloquentBuilderの継承元として、グローバルスコープの追加や、独自の構文メソッドの追加などに用いられるクラスのようです。とりあえず今回追加したいページネーションメソッドは、高レイヤーに該当する処理なので、newEloquentBuilder()を使うことで事足りる感じです。 ↩︎
builder::macroでも拡張できることを確認しましたが、エディタの静的解析で警告が激しく出てしまい、対策が分からなかったので止めました...。 ↩︎

株式会社THIRD エンジニアブログPublication

IT化が遅れがちなレガシー産業の一つである不動産管理に対して、原価削減・業務効率化・利益創出を実現するSaaSプロダクトを開発しています。実際に利用している技術を中心に投稿していく予定です