ランキングを求める

How to Optimize Rank Data in MySQL | O'Reilly Media

データベースに記録されたスコアを元にランキングを求める際の、パフォーマンスに秀でるテーブルやSQLの設計手法に関する記事。「High Performance MySQL」の著者によるもの。

サイト上で獲得点数のランキングを表示するゲームを作ったことがこれまでに何度もあるんだけど、リアルタイムで順位を求めることがほとんどだった。この記事の中で最もよくないとされる方法。まあ、MySQLではないのだけど。
あるスコアの順位を求めるには基本的に

SELECT count(*) FROM score WHERE score.score >= スコア;

というSQLになる。Oracleのようにrank()関数が使える場合は分からないけど、例えば上位50人のデータを順位付きで取得すると、SQLはひとつで済むけど結局サブクエリで順位を計算する形になるので、パフォーマンス的にはよくなさそうな気がする。
先頭レコードの順位だけ求めて、あとはプログラム側で順位を計算する方法もあるけど、この方法を採ったことはない。この方が速いのかなぁ。
数万くらいのレコード数ならインデックスをちゃんと張れば特に問題ないのだけど、サイトによってテーブル構成が異なるので、運用後にレコード数が増えてSQLの実行時間が遅くなってきてからインデックスやSQLを見直して解決している。

この記事では、更新コストは掛かっても順位カラムをテーブルに持たせておけ、count()は使うな(InnoDBは特にcount()が遅いので)という方針をより良い設計として挙げている。そして、更新のパフォーマンスを上げるためにMySQLのユーザ変数機能を使った順位の求め方を紹介している。それでも更新コストは比較的大きいので、得点更新時に毎回やるのではなく後でバッチ処理した方がいいんだけどねと、ちょろっと述べている。

今丁度、規模の大きめなランキングシステムをMySQL使って作る設計をしているので、いろいろと悩んでいる。運用後の変更は難しそうなので最初からバシッと決めたいところ。

MySQLのユーザ変数については、この記事を読んで初めて知った。他にどういう用途で使えるのかまだ分からないけど、面白そうな機能。
ハイパフォーマンスMySQL」も読んでおきたいのだけど、第1版は結構古いので第2版の原著を読むか。ここ一年くらい、洋書の原著を買っても読む余裕がなくて、さあ読もうとなると日本語版が出てたりする。

Last updated on July 7, 2015