PIYO - Tech & Life -

結局わからずじまいだけどMySQLのJOINが遅い気がしたので調べた

開発費をいただいて開発したRSSリーダー&情報収集サービスthrottle、の内部で使っているSQLの中に異様に遅いものがあったので、似たような環境をローカルに再現して確認することにしました。

SQLの確認なので生のSQLクエリを書いて検証すればいいのですが、いろいろと条件を揃えるためにRSSリーダーと同じくRailsを使用しました。データベースはMySQL5.5系で、エンジンはInnoDBです。

準備編

モデル

実際のRSSリーダーで使っているモデルの一部を抜き出したような形でモデルを用意しました。

それぞれ、次のような関連を持っています。UserとEntryはRelationshipを介して、多対多の関連になっています。

class User < ActiveRecord::Base
  has_many :relationships
  has_many :entries, through: :relationships
  ...
end

class Entry < ActiveRecord::Base
  has_many :relationships, dependet: :destroy
  has_many :users, through: :relationships
  ...
end

class Relationship < ActiveRecord::Base
  belongs_to :user
  belongs_to :entry
  ...
end

本当はここにRSSフィードというモデルが必要になってくるのですが簡略化のために省略しています。UserとEntryが多対多になっているのは同一Entryを複数ユーザーがSubscribeする可能性があるからです。複数ユーザーが同じ記事を購読していること表現するために関連テーブルRelationを用いています。

大量のデータを突っ込む

db/seeds.rbにデータ挿入用のコードを書いて、$ rake db:seedコマンドを実行します。

ソースコードはgithub:xoyip/jointestに置いてあります。

これによりUserが500件、Entryが100万件、Relationshipが約3000万件という検証用データができあがります。1Entryあたり10人〜50人のユーザーが関連付けられる、つまり平均30人なので100万×30で3000万件というわけです。

Entryにはpublished_atというフィールドがあります。Railsがデフォルトで用意するタイムスタンプ2つ（created_at,updated_at）とは異なり、記事が投稿された日を意味します。本来このフィールドはRSSフィード内に書かれています。

検証編

データの用意が済みました（ローカルマシンで5時間ぐらいかかったと思います。寝てたので詳細はわかりませんが。）。これでRSSリーダーthrottleのプロダクション環境と同じような環境を用意できたと言えます。

ちなみにプロダクション環境で問題となっているSQLは次のActiveRecordが生成するものです。 あるuserが購読している記事のうち、published_atが直近1日以内のもの、という意味です。

user.entries.where("published_at > ?", 1.day.ago)

SQL自体はこう。

SELECT `entries`.* FROM `entries` INNER JOIN `relationships` ON `entries`.`id` = `relationships`.`entry_id` WHERE `relationships`.`user_id` = 1 AND (published_at > '2013-11-06 06:29:20') ORDER BY entries.published_at DESC

今回のテストプログラムでもこれと同じコードを測定します。

既存のやり方

まずプロダクションと同じやり方を実行しました。複数のユーザーで複数回、同様の処理を実行したところ、実行時間は平均的にみて約0.1秒。だいたいこの前後でした。

あれ？全然遅くない！

プロダクション環境では大量の購読がある人だとこのSQLの実行に1分以上かかってしまうようなことが起こっていたのですが、このテストアプリではそんなことは無いようです。

本当はこのテストアプリでもある程度遅い状態を再現した上で、いろいろ弄って早くなるパターンを探したかったんですが…ｗｗ

残念です。

プロダクション環境がなぜ遅いのか

近い条件にはしたつもりですが、データ件数や各レコードのデータ量はプロダクション環境と異なっています。また、DBのパラメータなども違う可能性が高いので一概に比較はできないのでしょうね。

可能性がありそうな要因を挙げておきます。

  • データ量が大幅に多い
  • なんらかのボトルネックが生じている（IOなど）
  • パラメータがイマイチ
  • ストレージアクセスが遅い

などなど。

結論はでませんでした〜。