パフォーマンス改善の工夫(SQL編)


いいね

こんにちは。

アプリケーションエンジニアの藤巻です。


私は普段サーバサイドの実装をメインで行っていますが、「あれ、このページSQL発行しすぎじゃない?」なんて場面に出くわしたりすることがあります。

発行するSQLが多くなればなるほど、WebサーバとDBサーバとのやり取りが多くなりその分ページの表示スピードが遅くなってしまいます。表示スピードが遅くなると、せっかくサイトを訪れてくれたユーザーも離脱してしまうかもしれませんし、SEOの観点でも良くありません。

そこで、今回はやりがちなN+1問題の解説と、その改善方法について紹介したいと思います。

N+1問題とは?

この問題は、例えば何かの一覧ページを作成したときに

  • 一覧で表示するためのデータを取得するためにデータを1回取得
  • そのデータに関連するデータを取得するためにデータをN回取得

そのため、合計N+1回のデータベースアクセスが実行されるというものです。


文章だけだとわかりにくいので、以下で具体的に見てみましょう。


例えば本を紹介するWebサイトを作っていたとして、本の一覧ページを作成するとします。

こんな見た目にしたいとして

こんなER図を作ったとしましょう。

そしてビューはこのようなプログラムで表示するとしましょう。

foreach (Book::getRowset() as $BookRow) {
  echo $BookRow->getImageUrl();
  echo $BookRow->getTitle();
  echo $BookRow->getAuthorRow()->getName();
  echo $BookRow->getCategoryRow()->getName();
  echo count($BookRow->getGoodRowset());
}


このとき本の一覧ページにアクセスしたとき、以下のようなSQLが発行されてしまいます。

SELECT * FROM book limit 20;
// 一番目の本に関連するデータ
SELECT * FROM author WHERE id = 1;
SELECT * FROM category WHERE id = 1;
SELECT * FROM good WHERE  book_id = 1;
// 二番目の本に関連するデータ 
SELECT * FROM author WHERE id = 2;
SELECT * FROM category WHERE id = 2;
SELECT * FROM good WHERE  book_id = 2;



本の一覧を取得するSQLが一回と、本に関連するデータを取得するためのSQLが本ごとに毎回実行されています。これがN+1問題と言われる問題です。

もし一覧に20個の本を表示したいとすると、1 + 20 * 3 = 61回のSQLが実行されてしまいます。もしこの一覧とは別に、同じ見た目で「人気の本TOP5」なんてパーツを作ったらさらにSQLの数は増えるでしょう。

では、どんな工夫でこの問題を解消できるかをいくつか紹介したいと思います。

1. 関連データの取得はIN句で行う

あらかじめ必要なデータを最初に取得する方法です。

先程の例では著者やカテゴリのデータは、本ごとに取得していましたが、これはIN句を使って予め取得できます。

具体的には

SELECT * FROM book LIMIT 10;
SELECT * FROM author WHERE id IN (1, 2, 3, ..., 20);
SELECT * FROM category WHERE id IN (1, 2, 3, ..., 20);
SELECT * FROM good WHERE book_id IN (1, 2, 3, ..., 20);

こんな感じです。これなら合計4回のクエリで本の一覧表示に必要なデータを取得できます。

また、この方法はフレームワークを使っていればEagerロードという機能で提供されていることもあります。

Ruby on RailsでもCakePHPでもLaravelでもN+1問題の解決策としてこのEagerロードが用意されていますので、基本的にはこの機能を活用することによって発行されるSQLの数はかなり抑えられます。

N+1問題については1で紹介した方法でほぼ解決できますが、それ以外の方法も少し紹介します。

2. キャッシュを活用する

今回の例で言うと、「カテゴリー」データは更新される頻度がそこまで多くないことが予想されます。そういったデータについてはSQLで取得したデータをキャッシュとして保存しておき、2回目のデータ取得以降はキャッシュからデータを取得してくるようにすると、DBにアクセスする必要がなくなるので発行するSQLの数は減らせます。


ただし、カテゴリーデータを更新した場合には、キャッシュデータは更新されませんのでキャッシュを消してDBに再アクセスする必要が出てきます。

3. 非正規化テーブルを作成する

さらに別の方法としては、表示用のデータとして非正規化bookテーブルを作成する方法があります。

本の関連データとして表示したい著者名、カテゴリー名、いいね数をあらかじめDBに保存しておきます。

こうすることで、著者テーブル、カテゴリテーブル、いいねテーブルにアクセスすることなく本の一覧ページを表示することができます。

ただし、この場合は非正規化テーブルを作成する処理を別途作成する必要があります。また、いいね数などリアルタイムで変動させたいデータについては、いいねされるたびに非正規化データを更新するか、いいねだけDBにアクセスして取得してくるなどの工夫が必要になってきます。

まとめ

今回は一覧ページを実装するときにやってしまいがちなN+1問題の紹介と解決方法を簡単に紹介しました。

上記のような工夫は実際に美容の口コミ広場でも実装されています。今後も様々な工夫を通して、よりよいサービスを提供していけたらと思います。




アカウント登録をするとコメント・質問を書くことができます


採用検索 - 詳細条件の設定