初めにお断りしておきますが、サービスの規模として「総ユーザが10万そこそこ」であることを前提としております。
そのため、100万ユーザを抱えるようなヒットしているプロダクトには適しませんのでご了承ください。
この記事はtomita@atuwebがお届けします。
Webアプリケーション開発に携わっていると、みなさんが一度は通る道がランキング機能だと思います。
数値が付けば、上を目指したくなる人が一定数いるもので、ゲーム系の開発では必ずと言っていいほどランキングという施策が入ってきます。
今回はMySQLでのランキング実装について考えてみます。
(くどいですが、小規模なサービスを対象としております。)
前提としてのMySQL
昨今、Webアプリケーションでのランキング実装はRedisを利用の実装事例がほとんどではないでしょうか。
実際、Redisを利用すれば恐ろしく簡単にランキングが実装できる、素晴らしいプロダクトだと思います。
まずRedisについて見てみます。
Redisについて
Redis
http://redis.io/
その特徴を簡単に挙げます。
- インメモリで高速
- 永続化(不揮発)
- 複数のデータ型
- アトミック
「SET 操作を毎秒約 10 万回、GET 操作を毎秒約 10 万回実行できる」というので、かなりいい感じです。
Key Value Store ですが、リスト型、セット型、ハッシュ型といったデータ型をサポートしており柔軟な表現が可能です。
そして、スコアによってソートされるソート済みセット型があり、これがランキング実装で重宝されるのです。
Setした時点でランクが変動するのですから、シンプルな簡単な操作で順位を取得できるわけです。
これはすごい。
じゃあなんでRedisを使わないの
言い訳します。
障害事例
Redis 本番障害から学んだコードレビューの勘所
http://qiita.com/haminiku/items/43bafbb9d74ef3a1f74c
上記を見て怖くなりました。
コスト
Redisは素晴らしいプロダクトですが、比較的新しい技術なだけに「使ったことがない」企業、エンジニアさんも多数存在します。
私も実際に公開プロダクト自分で実践したことはありません。
そこで問題となるのが、「限られた予算で技術検証にどれだけ時間を割くことができるか」というコスト面ですね。
「やってみたけどダメでした」なんてことがあっては、そのプロジェクトのお財布事情が一気にキツくなってしまいます。
システムとして、内側はどうであれある程度安定して動けばいいわけですから、世間で評判が良くても自分にとってチャレンジングなことは避けるのがベターと判断することもあります。
データの保証
Redisはデータを永続化できますが、完ぺきではないという認識です。
Webアプリケーションの開発では「キャッシュに乗せるデータはRDBから復元できる、消えても良いものだけにする」というセオリーがありますが、これはRedisにも当てはまりそうです。
データの永続化のためにスナップショット作成と更新系クエリを保存するAOFの出力がありますが、どちらもパフォーマンスに影響するためユーザが任意で設定できます。
簡単に言うとデータの保存処理と応答速度がトレードオフになっています。
速度を保つためにはデータの保存間隔を長めに設定しますが、そうなるとクラッシュした場合に完全にデータを復旧できない可能性があるのだと理解しておきます。
そのため、復元用に別のストレージにも元となるデータを作成しておく必要が出てきます。
それなら「RDBでいいじゃん」という結論
「追加の開発コストが少なく、データの信頼性もあるなら今使っているRDBをまず活用しましょう」と落ち着くのは妥当な判断ではないでしょうか。
RDBはバランスが良いのでやはり良い選択肢であり、多数の実績があるので選びやすい選択肢なのですね。
今回はそれほどユーザ数が多くないという前提をつけています。
もしサービスが成長して潤い、スループットが要求がシビアになってきたら、またそこで考えればよいのではないでしょうか。
大きな箱を作っても、9割余すなら無駄ですよね。
設計
前置きが長くなりました。
ざっくり仕様
- リアルタイムではなく、10分に1度ランキングを更新する
- 上位ランキングと自分(+周辺)のランクが閲覧できる
- 同順考慮
ロジック
- “更新用テーブル”と”参照用テーブル”を分離する
- 一定時間ごとに”作業用テーブル”を作成し集計処理を行う
- 集計後に”作業用テーブル”と”参照用テーブル”を入れ替える
スキーマ
ランキング（ユーザID , スコア, ランク, 連番)
更新用テーブル、参照用テーブルどちらも同等のスキーマとします。
また、ソート基準に連番用フィールドを用意しました。
ポイント1など、ユーザが多い帯域を検索する場合に活用できます。
処理詳細
1.ポイントの発生と更新
- クエストなどランキング対象ポイントの発生時に更新用テーブルに更新データを流し込む
- キューシステムがあればキューを利用するなど非同期となるよう考慮する
- 非同期に処理するする(1トランザクションで処理しない)場合は、データの欠損や不整合が発生しないよう考慮する
今回「ユーザがメインゲームを周回クリアしたときだけポイントが発生する」という条件にすることができました。
そのため、1データを複数ユーザが更新することはなく、あるユーザのスコアはシーケンシャルに処理することが可能です。
その前提に立って、私はランキング更新用テーブルとは別に「ユーザのスコアを保持するテーブル」を用意し、ポイント発生時にスコアを同期する設計としました。
データの多重管理となってしまいますが、ランキングのほうの速度を優先します。
レイドのように、1データを複数ユーザが一度に更新する場合は必ずUPDATE SET score = score + nします。
2.集計処理
作業用テーブルの作成
cronなどから集計処理を呼び出します。
“更新用テーブル”から”作業用テーブル”を作成しデータを流し込みます。
DROP TABLE IF EXISTS `i_ranking_work`;
CREATE TABLE `i_ranking_work` LIKE `i_ranking_score`;
INSERT INTO `i_ranking_work` SELECT * FROM `i_ranking_score`;
順位付け
次に”作業用テーブル”に対して順位付けのクエリを実行します。
SET @i=0, @rank=0, @previous=0;
UPDATE `i_ranking_work`
SET
`no`= (@i := @i +1),
`rank` = CASE
WHEN @previous = `score` THEN (@rank := @rank)
ELSE
CASE WHEN (@previous := `score`)
THEN (@rank := @i)
END
END
ORDER BY `score` DESC, `modified` DESC, `uid` DESC;
@previousに「前レコードのスコア」を保存し、現レコードと比較してランクを付けに利用しています。
参照テーブルの入れ替え
先に、”参照用テーブル”に回す前に連番にインデックスをつけておきます。
ALTER TABLE `i_ranking_work` ADD INDEX(`no`);
最後に、”作業用テーブル”と”参照用テーブル”を入れ替えます。
RENAME TABLE
`i_ranking` TO `i_ranking_old`,
`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で高速にランキングを求める
理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus)
楽天で検索する
Webサービス開発徹底攻略 Vol.2 (WEB+DB PRESS plus)
楽天で検索する
2016年03月05日：一部追記