最近データベースというかSQLについて勉強しているんですが、奥が深いですね。この前の第9回中国地方DB勉強会のときに聞いたrank関数を使って、ランキング機能をリファクタリングしよう！と思って最近頑張ってます。というのも、複雑なクエリ（遅い）を業種数分（10回くらい）呼んでいたため、Herokuだと結構ギリギリの速度になることもあったので、なんとかしなければ！と思っていたのです。

とりあえず、私の開発環境を載せておきます。

Mac Yosemite
Ruby 2.2
Rails 4.2.1
PostgreSQL 9.3.4

ひとまずrank関数を使うところまで

まず、NewRelicを使ってActiveRecordが出力しているSQLを取得し、それを0xDBEのコンソールに貼り付けて、rank関数を使って業種でパーティションしてランキングを出すところまでしてみました。rank関数は、関数名の通り、ランキングを出力する関数です。詳細については、@soudai1025がアップしている資料を見てもらえばクエリ付きでわかりやすいと思います。

この資料に、以降で説明することもほぼ書かれていますので、通読することをお勧めします。

複雑なクエリを楽にするためにDBのViewを作る

書いたクエリをActiveRecordで表現してみると、かなり複雑で読みにくくなってしまったんですが、@soudai1025から、(DBの)Viewにしてはどうですか？とアドバイスをもらったので、Viewを作ってみました（以降、ViewはDBのViewのこと）。Viewはmigrationで作りましたが、railsでDBのViewを作る機能はないので、普通にSQLを書いてクエリを実行します。

class CreateViewFooRanking < ActiveRecord::Migration
  def up
    # Viewを作るためのクエリは50行くらいあったのでダミーで…。
    execute <<-SQL
      CREATE OR REPLACE VIEW foo_rankings AS
        SELECT *
        FROM (
          SELECT
            rank()
            OVER(
              PARTITION BY category_id
              ORDER BY count DESC
            ) AS rank,
            count,
            category_id,
            foo.id AS foo_id
            ...
          FROM foos
            INNER JOIN (
              SELECT ...
              FROM (
                SELECT ...
                FROM ...
                  LEFT OUTER JOIN (
                    SELECT ..
                    FROM ..
                  ) AS ..
              ) AS ..
            ) AS ..
        ) AS base
    SQL
  end

  def down
    execute <<-SQL
      DROP VIEW foo_rankings
    SQL
  end
end

このマイグレーションを実行後、0xDBEなどのSQLエディタで対象のViewに対してクエリを発行して、思った通りのデータが取れているかを確認します。

View用のModelを作る

View用のモデルは普通に作れるので、定義してみましょう。

class FooRanking < ActiveRecord::Base
  belongs_to :foo

  scope :top10, -> { where(arel_table[:rank].lteq(10)) }
  scope :default_order, -> { order(category_id: :asc, rank: :asc) }

  default_scope { default_order }
end

データを取得してみる

では、このモデルを使ってみましょう。

トップ10のデータを取得

FooRanking.preload(:foo).top10

1つのカテゴリのランキングを取得

FooRanking.preload(:foo).where(category_id: 1)

カテゴリごとの10位までを取得してカテゴリ毎に分ける

FooRanking.preload(:foo).top10.group_by(&:category_id)

ものすごく複雑だったランキングデータの取得が、1行のコードで取れるようになりました。
あぁ、快…感…。

Viewいいね！でもまだ遅いね！

しかし、Viewは結局のところアクセスされるたびにクエリを発行して擬似的なテーブルを作っているため、複雑なビューだと遅いんですね。
200msecかかってたクエリ10回=2秒
よりは、
500msecかかるView1回
のほうが、かかる時間は短いわけですが、500msecは割と気になる遅さです。Kaminariを使ってページネーションする場合、トータル件数の取得と現ページのデータの取得で2回クエリが発行されて、結果、合計1秒かかったり。

しかし、それをさらに速くする方法があったのです。

MATERIALIZED VIEWにしてさらに高速に！

マテリアライズドビュー（以降、マテビュー）は、Viewの結果をキャッシュしておくViewです。結果を保有しているため、問い合わせが高速です。500msecかかっていたクエリが、2msecになりました！素晴らしい。
作り方は簡単です。CREATE OR REPLACE VIEWにしていたところを、CREATE MATERIALIZED VIEWにするだけです。

CREATE MATERIALIZED VIEW foo_rankings AS ...

しかし、マテビューは結果をキャッシュするがゆえの欠点もありますし、そのあたりも書いておきます。

• PostgreSQL 9.3以降のみで使える
• 定期的にデータをリフレッシュしなければならない
• リフレッシュ中はデータがないためロックされる
• RSpecでテストする場合にデータ投入後にリフレッシュ必須

私の利用シーンでは、ランキングは月に1度の更新でよかったので、マテビューを使うほうがよさそう！と思ってこちらを採用しました。PostgreSQL9.4だと、リフレッシュ中のロックを緩やかにする機能があるらしいので、9.4にできる場合は9.4のほうがよさそうです。

まとめ

ActiveRecordで表現するにはあまりにも複雑な場合はViewを作りましょう。
読みやすいコードにすることが可能です。
要件次第ではマテビューを使うとかなりの高速化になります。