SymfonyによるN+1の速度比較をやってみた！

はじめに

こんにちは！

オズビジョン開発ユニット（ハピタス）の木山です。

今回はWebの開発をする上でよく耳にするN+1問題についてSymfonyとMySQLの環境で検証をしてみようと思います！

N+1問題とは？

まずはじめにN+1問題について簡単におさらいしておきます。

前提として、データベースにUsersテーブルとCommentsテーブルが存在している簡単なSNSアプリケーションを考えてみます。

ここで、全ユーザーの全コメントを表示する機能を実装したいです。

その際、UsersテーブルからUserの一覧を取得し、各ユーザーのIDを用いてCommentsテーブルのユーザーに紐づくデータも取得します。

この際、発行されるクエリに着目をすると、

// UsersテーブルからUserのid一覧を取得
select id from Users;

// Commentsテーブルから特定UserのCommentを取得 （これをUserの人数分繰り返す）
select * from Comments where user_id = ?;

上記のようなクエリになります。

例えばユーザーが10人いると、以下のようなイメージです。

この時発行されるクエリの件数に着目すると

このようになります。

出力イメージの箇所をクエリの発行回数にしています。

まず最初にUsersテーブルから全ユーザーのデータを取得するクエリを1回。

そして、Commentsテーブルからユーザーの全コメントを取得するクエリを1回づつx10ユーザー分(計10回)。

それらを足し合わせると11回のクエリが実行されることがわかるかと思います。

つまり、10人のユーザーで11回のクエリです。

是非このユーザー数を自由に変えてご自身でも考えてみてください。

何か見えてきましたか?

そうなんです、ユーザー数を仮にNとすると、発行されるクエリの件数は常にN+1になります。

これがN+1問題の由来となります！

言葉で簡単にまとめると、

UserがN人いて、そのユーザーに紐づくコメントを取得する場合、ユーザーの一覧を取得するクエリ(1回)と、ユーザーごとのコメントを取得するクエリ(N回)の、計N+1回のクエリが発行されてしまうこと

をN+1問題と言います。

何故問題？

さて、先ほどN+1問題について簡単に確認しましたが、何故"問題"と呼ばれるのでしょうか？

これには明確に答えがあり、“処理速度"が大きな問題と言われています。

Nの数が大きくなれば大きくなるほどデータベースへの問い合わせの回数が増え、その結果、サービスのパフォーマンスに甚大な影響を及ぼしてしまいます。

例えばWebサービスなら、ページへアクセスして何十秒何百秒と待つ…なんて、今のご時世では考えられないですが、このN+1が起きてしまうとそれが起きてしまうかもしれません。

対処法は？

今回問題になっているのが、N回クエリを発行してしまっている部分です。

ユーザーの一覧を取得したあと、CommentテーブルにJOINし、IN句を利用すれば問い合わせは一回ですみ、たった二回のクエリ発行で全件分のデータを取得することができます。

つまり、Commentsテーブルからのデータ取得を一人づつではなく、一括でできるように工夫してあげればOKです。

この後の検証でN+1が発生する場合、しない場合の検証も行っていきます。

N+1問題を検証してみる！

では、今からN+1問題を検証してみようと思います！

まず前提として、以下のようなテーブルで考えていきます。

Userテーブルに対して、0~n件のコメントが紐づく形でリレーションを持っているテーブルです。

ユーザーごとのコメントはどのユーザーも5件固定で、ユーザー数Nに関しては先ほどの解説に合わせ、N=10で検証をしていきます。

また、検証に用いたSymfony, PHP, MySQL周りのバージョンは以下の通りです。

 -------------------- ---------------------------------
  Symfony
 -------------------- ---------------------------------
  Version              6.3.1
  Long-Term Support    No
  End of maintenance   01/2024 (in +133 days)
  End of life          01/2024 (in +133 days)
 -------------------- ---------------------------------
  PHP
 -------------------- ---------------------------------
  Version              8.2.8
  Architecture         64 bits
  Intl locale          ja_JP
  Timezone             UTC (2023-09-20T02:23:41+00:00)
  OPcache              true
  APCu                 false
  Xdebug               false
 -------------------- ---------------------------------

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

N+1問題が起きる場合

それではまずはN+1が起きる場合を検証していきます。

まずはUserのIDを全件取得する処理です。

public function findAllIds(): array
{
    $conn = $this->getEntityManager()->getConnection();

    $sql = "SELECT id FROM user";
    
    $stmt = $conn->executeQuery($sql);
    
    $ids = [];
    while ($row = $stmt->fetchAssociative()) {
        $ids[] = $row['id'];
    }

    return $ids;
}

説明は不要かなと思いますが、“SELECT id FROM user"というクエリをSymfonyで発行する処理になっています。

条件なしで全ユーザーのIDを取得します。

次にユーザーごとのコメントを取得する処理です。

public function findByUserId($userId): array
{
    $conn = $this->getEntityManager()->getConnection();

    $sql = '
        SELECT * FROM comment c
        WHERE c.user_id = :user_id
        ORDER BY c.id ASC
    ';
    
    $stmt = $conn->executeQuery($sql, ['user_id' => $userId]);

    return $stmt->fetchAllAssociative();
}

こちらは"select * from comment where comment.user_id = ?;“といったクエリを発行する処理になっています。

このuseridでユーザーに紐づくコメントを一括で取得する処理がN回呼ばれる想定です。

そして、それらの処理を呼び出すコントローラーが以下のようになっています。

// UserのID一覧の取得 
$userIds = $entityManager->getRepository(User::class)->findAllIds();

// UserIDごとにコメントを取得
$comments = [];
foreach ($userIds as $userId) {
    $comments[$userId] = $entityManager
        ->getRepository(Comment::class)
        ->findByUserId($userId);
}

最初に$userIdsとして、findAllIds関数でユーザーのID一覧を取得し、その後foreachで一件づつコメントも取得しています。

クエリの実行時間の測定はSymfonyで確認できるデバッグツールがあったので、その内容を参照します。

実行すると…?

Symfonyのデバッグツールの画面で、Database Queriesの件数が確かに11件（N=10の場合の合計クエリ数）になっていますね。

クエリの詳細も見てみると、一件目でユーザーの一覧を取得していて、それ以降はコメントを取得しているのがわかるかと思います。

実行時間は3.54msですね。

N+1が起きる場合に関しては確認できました！

N+1問題が起きない場合

では、今度はN+1問題が起きない場合も試してみます。

まずはUserのIDを全件取得する処理ですが、こちらは先ほどと同じで問題ないため、N+1が発生する場合と同様のfindAllIdsメソッドを利用します。

次にユーザーのIDをまとめて、コメント一覧を取得する処理です。

public function findByUserIds($userIds): array
{
    $conn = $this->getEntityManager()->getConnection();

    $placeholders = implode(',', array_fill(0, count($userIds), '?'));

    $sql = "
        SELECT * FROM user
        JOIN comment ON user.id = comment.user_id
        WHERE user.id IN ($placeholders)
    ";

    $stmt = $conn->executeQuery($sql, $userIds);
    
    $results = [];
    while ($row = $stmt->fetchAssociative()) {
        $results[] = $row;
    }
    
    return $results;
}

ご覧いただくとわかりますが、SQL部分は

SELECT * FROM user
JOIN comment ON user.id = comment.user_id
WHERE user.id IN ($placeholders)

となっています。

SQLインジェクションの対策をしているので、少し加工していますが、ユーザーのID一覧をIN句に渡しています。

こうすることで、コメント一覧取得はユーザーが何人いても発行されるクエリは一件になるはずです。

そして、上記処理を呼び出すコントローラーは以下の通りです。

// UserのID一覧の取得 
$userIds = $entityManager->getRepository(User::class)->findAllIds();

// UserIDの一覧からコメントを全件取得
$comments = [];
$result = $entityManager->getRepository(Comment::class)->findByUserIds($userIds);

PHP上にforのループ処理が不要になったので可読性も上がった気がします。

そして実行結果は？

発行されているクエリは2件、時間は1.64msになっていますね。

そしてクエリ詳細をみると、ユーザーを全件取得、そしてそのユーザーIDから一つのクエリで10人分のCommentを取得できていることがわかります!

比較結果

最後に、N=10の比較結果を簡単にまとめておきます。

確かにN+1が無しの方が時間もクエリ数も少ないことがわかるかと思います。

ただ、前述したように一般的にはこのNの数が増えれば増えるほどパフォーマンスへの影響は顕著に現れると言われていて、今回のようなN=10だとあまり大差ないように感じてしまいますね。

筆者はまだ半信半疑だったので、もうちょっと深掘りして件数を増やしてみました。

その場合の結果は以下のとおりです。

テストデータは簡単なテーブルにFakerで適当な値を入れたものでしたが、件数が大きくなればなるほどN+1有りの時間が増えていることがわかるかと思います。

実際、50000件の時はN+1有りだと47分近くかかっていて、無しの時と比べ4486倍近くかかってしまいます。

本心ではもうちょっと件数増やしてみたい気持ちもありましたが、ギブアップしましたw

※ N>500からは、デバッグツールの表示処理の際メモリエラーになってしまったため、コントローラーにて、測定したい処理を挟んでmicrotimeで差分を取って秒数を算出しています。

$firstStartTime = microtime(true);
// ここに測定したい処理
$firstEndTime = microtime(true);

$firstQueryTimeDiff = $firstEndTime - $firstStartTime;

まとめ

今回、N+1問題についてSymfonyとMySQLの環境で簡単に試してみました！

前々からN+1問題について聞いたことはあり、速度への影響があることも知っていました。

ただ、やはり実際に試してみると身をもってどれくらい時間がかかるのか？などが鮮明になり、大切であることを確認できたと思います。

こういった検証は好きなので、今後も良い題材があれば色々試してみようと思います！