【スポンサーリンク】

MySQLで高速にランキングを求める

例えば下記のようなテーブルがあったとして、ハイスコアの上位ランキングや指定idのランキングを取得したい場合がある。

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `highscore` int(11),
  PRIMARY KEY (`id`),
  KEY `highscore` (`highscore`)
);

いつもはバッチ処理してランキング用のテーブルを用意していたが、ふとMySQLだけで高速にランキングを求められないものかと思ったので色々探してみた。

まずは、こちらの記事の早い版と書かれている方法が高速で良い感じだった。

MySQL - SQL 合計値からランキングを取得する例 - Qiita

しかし同点（同順位）は考慮されていなかったので、今度はこちらを参考にしたクエリを使ってみた。

mysqlだけでランキングしてみる(同率考慮) - Qiita

この方法で約130万件のテーブルに対して上位100件を取得してみると約1秒かかった。規模が小さいうちなら問題なさそう。
しかし、アプリやサービスの規模が大きくなってくるとバッチ処理を作らねばならない可能性がある結果となった。

というわけでかなり無茶なやり方だけど高速にランキングを求めるクエリを書いてみた。
ユーザー定義変数の処理順に注目し、一つ前のハイスコアの値を参照することによって同順位かどうかをチェックしている。

select id, highscore, rank from (
  select
  @p:=@a,
  case
    when @p = highscore then @c
    else @c:=@c+@s
  end as rank,
  case
    when @p = highscore then @s:=@s+1
    else @s:=1
  end as S,
  id, highscore, @a:=highscore from (
    select @c := 1, @a := null, @s := 0)
  AS dummy,
  test order by highscore desc limit 100
) as tmp;

この方法だと約130万件のテーブルに対する上位100件の取得にかかる時間は0.01秒未満となった。満足。

また、特定のidのランキングを調べたい場合は、

select id, highscore, (
  select count(*)+1 from test as t2
  where t2.highscore > t1.highscore) as rank
from test as t1
where id = ○○;

とやると速い。こちらの方法は

順位の取得 - MySQL | 教えて！goo

を参考にした。

テストデータについては

MySQLで簡単にランダムなテストデータを作成する方法 - Qiita

を参考にして作成した。

余談

上位ランキングを取得する高速なクエリを指定idのランク取得クエリに書き換えたり、指定idのランクを取得する高速なクエリを上位ランキングを取得するクエリに書き換えると、なぜかとても遅くなる。謎。

【スポンサーリンク】