詰将棋サイトを運営してますが、タイムアタックランキングを実装してます。
昔の話ですがSQLでのスロークエリ解消法を記します。今はO/RマッパーがあるからSQLを書かなくなってきているのであまり使われないけれども備忘録を残します。
またmemcachedの利用やTokyoTyrantなどを使ったほうが早いので、ますますひねったSQLは使わなくなりました。
今回の手法の大枠は、ランキング表示時のスロークエリが出たときはJOIN時のスロークエリなどで、その場合サブクエリを使った上でのJOINをする感じです。ひねったSQLでんな。
※ちなみに、そのmemcachedなんですがどうやら自分が使っているキャッシュサーバが弱くてキャッシュヒット自体は早いのですが、連想配列などをシリアライズ化して投入しておいたら、
キャッシュサーバからのパケット容量が問題になりました。それでCPU負荷やレスポンスタイムが遅れては元も子もない。このあたりはちゃんとSQLで完結できるならSQLでなんとかしたいですよね。
簡単にテーブル定義を解説しますと※実際とは異なります
テーブル：answers
カラム：id, user_id, question_id, time
こんな感じ、どのユーザがどの問題でどれだけ秒数がかかったかというテーブルですな。
ランキングを表示するさいユーザテーブルとJOINしてユーザ名も表示させます。
テーブル：users
カラム：id, name
とあるページのランキングを表示させる際のSQL文です。？マークはプレースホルダです。
select
Answer.question_id
, Answer.time
, User.name
from
answers Answer
, users User
where
Answer.user_id = User.id
order by
Answer.time asc
limit
?, ?
これだとスロークエリが出てました。answersっていうテーブルは時間が経つとふくれあがります。
なので一旦answersをlimitで切ってからusersとJOINしましょう。
select
Answer.question_id
, Answer.time
, User.name
from
(select * from answers order by time asc limit ?, ?) Answer
, users User
where
Answer.user_id = User.id
これにてスロークエリがなくなりました。
前述の通りキャッシュサーバが強ければユーザ情報はmemcachedから拾ったほうが早いかもしれません。
しかし自分としてはDBの情報をシリアライズ化して入れておくってのは本筋じゃない気が...。
キャッシュってキーと値が1対1だから使えるんじゃないかと。
たとえばkey=1 : value="sql" みたいに単純な形。それ以上にふくれあがるなら、ちゃんとDBにいれたほうがいいように思えます。
この辺はベンチマークとって見ないとわかりませんけど。
一応JOIN構文を使ったSQL。SQL92でしたっけ？そちらが推奨しているんですけど、3つ以上JOINすると逆に見づらすぎるのが難点。
select
Answer.question_id
, Answer.time
, User.name
from
(select * from answers order by time asc limit ?, ?) Answer
join
users User
on
Answer.user_id = User.id
