初めにお断りしておきますが、サービスの規模として「総ユーザが10万そこそこ」であることを前提としております。
そのため、100万ユーザを抱えるようなヒットしているプロダクトには適しませんのでご了承ください。


Webアプリケーション開発に携わっていると、みなさんが一度は通る道がランキング機能だと思います。
数値が付けば、上を目指したくなる人が一定数いるもので、ゲーム系の開発では必ずと言っていいほどランキングという施策が入ってきます。

今回はMySQLでのランキング実装について考えてみます。

設計

ざっくり仕様

  • リアルタイムではなく、10分に1度ランキングを更新する
  • 上位ランキングと自分(+周辺)のランクが閲覧できる
  • 同順考慮

ロジック

  • “更新用テーブル”と”参照用テーブル”を分離する
  • 一定時間ごとに”作業用テーブル”を作成し集計処理を行う
  • 集計後に”作業用テーブル”と”参照用テーブル”を入れ替える

スキーマ

ランキング(ユーザID, スコア, ランク, 連番)

更新用テーブル、参照用テーブルどちらも同等のスキーマとします。

また、ソート基準に連番用フィールドを用意しました。 ポイント1など、ユーザが多い帯域を検索する場合に活用できます。

処理詳細

1.ポイントの発生と更新

  • クエストなどランキング対象ポイントの発生時に更新用テーブルに更新データを流し込む
  • キューシステムがあればキューを利用するなど非同期となるよう考慮する
  • 非同期に処理するする(1トランザクションで処理しない)場合は、データの欠損や不整合が発生しないよう考慮する

今回「ユーザがメインゲームを周回クリアしたときだけポイントが発生する」という条件にすることができました。
そのため、1データを複数ユーザが更新することはなく、あるユーザのスコアはシーケンシャルに処理することが可能です。

その前提に立って、私はランキング更新用テーブルとは別に「ユーザのスコアを保持するテーブル」を用意し、ポイント発生時にスコアを同期する設計としました。

データの多重管理となってしまいますが、ランキングのほうの速度を優先します。

レイドのように、1データを複数ユーザが一度に更新する場合は必ずUPDATE SET score = score + nします。

2.集計処理

作業用テーブルの作成

cronなどから集計処理を呼び出します。
“更新用テーブル”から”作業用テーブル”を作成しデータを流し込みます。

1DROP TABLE IF EXISTS `i_ranking_work`;
2CREATE TABLE `i_ranking_work` LIKE `i_ranking_score`;
3INSERT INTO `i_ranking_work` SELECT * FROM `i_ranking_score`;

順位付け

次に”作業用テーブル”に対して順位付けのクエリを実行します。

 1SET @i=0, @rank=0, @previous=0;
 2UPDATE `i_ranking_work`
 3SET
 4  `no`= (@i := @i +1),
 5  `rank` = CASE
 6  WHEN @previous = `score` THEN (@rank := @rank)
 7  ELSE
 8    CASE WHEN  (@previous := `score`)
 9      THEN (@rank := @i)
10    END
11  END
12ORDER BY `score` DESC, `modified` DESC, `uid` DESC;

@previousに「前レコードのスコア」を保存し、現レコードと比較してランクを付けに利用しています。

参照テーブルの入れ替え

先に、”参照用テーブル”に回す前に連番にインデックスをつけておきます。

1ALTER TABLE `i_ranking_work` ADD INDEX(`no`);

最後に、”作業用テーブル”と”参照用テーブル”を入れ替えます。

1RENAME TABLE
2  `i_ranking` TO `i_ranking_old`,
3  `i_ranking_work` TO `i_ranking`;

RENAME TABLEはアトミックな処理のため、サービス側にエラーが出ることはありません。
安心して処理できます。

また、旧”参照用テーブル”のリネームで作成した”i_ranking_old”はそのままDROPしても良いですし、次の集計まで残しておいて「前回の順位」を確認できるようにしてもよいと思います。

速度

仮に50万件程度のデータを作成し、上記のSQLを実行した結果トータルで10秒弱とそこそこの数値が出ました。
ランキング更新頻度は10分としていますので、問題が出ることはそうそうないと考えられます。

おわりに

リアルタイムランキングにこだわり、すべてを同期処理しようとすると、パフォーマンスを犠牲にする必要が出てきます。

今回のランキングの実装例は、ポイント発生も非同期、ランキング更新も非同期で間隔をおいていますので、負荷の分散もある程度期待できます。
また、レコード数と処理時間は比例しますので、レコードが少なければもっと早く処理できるでしょう。

課金など、きっちり同期する必要のあるものはそのように対応し、いくらか遅れても問題ないものは多少ルーズに実装すると、サービス全体のパフォーマンスを維持できると考えています。

どなたかの一助になれば幸いです。

参考

MySQL 5.6 リファレンスマニュアル / 言語構造 / ユーザー定義変数
https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html

MySQLで高速にランキングを求める
http:// hatena.phalusamil.com/entry/2015/09/23/094536
現在は閉鎖

Webサービス開発徹底攻略 Vol.2 (WEB+DB PRESS plus)

鶴原翔夢,小野侑一,中村俊介,佐藤春旗,青山公士,佐々木大輔,横路隆,加来純一,山本伶,大平武志,米川健一,坂本登史文,若原祥正,和久田龍,平栗遵宜,城倉和孝,安達俊雄,Akira,川嶋賢一,安詮院康広,山口良平,尾上忠輔,大川高志,坂本寛樹,栗林健太郎,柴田博志,黒田良,常松伸哉,安宅啓,舘野祐一
出版社:技術評論社  発売日:2016-02-16

Amazonで詳細を見る