アピリッツ コンテンツデザイン部の金井と申します。今回は膨大なレコード数を保有するテーブルからのランダム抽出に関して書いていきます。
結論
元々ある莫大なテーブルから愚直にクエリを叩くのではなくて、別にテーブルを作ってそこから効率的に参照するようにすれば良いと思います。
良くある仕様相談
プランナー「ゲーム始めたてのユーザーにもフレンド機能を積極的に活用して欲しいから、以下の条件でお勧めユーザーが出るようにしてくれない？」
- チュートリアル完了済み
- フレンドでない
- BANユーザーでない
- フレンド数がMAXでない
- 3日以内にログインしている
- ユーザーとのプレイヤーランクの差が-5~+5以内
- お勧めユーザーとして出る事を許容している
サーバーエンジニア「りょーかい。(クエリ叩いて該当するユーザーを検索して指定数絞り込むだけだから……うん、そんな時間掛からないかな)1~2営業日で実装できますね」
はい、落とし穴です。
愚直に実装した結果
例えば、以下のように愚直にクエリを叩くような実装にしたら、
FRIEND_COUNT_MAX = 30
RECOMMEND_SEARCH_DATE_RANGE = 3
RECOMMEND_SEARCH_RANK_RANGE = 5
RECOMMEND_SEARCH_LIMIT_COUNT = 100
RECOMMEND_SEARCH_SAMPLE_COUNT = 30
def search_recommend_users(time = Time.current)
ban_user_ids = BannedUser.current(time).pluck(:user_id)
friend_user_ids = Friend.where(status: Friend.status_value(:friend), user_id: self.id)
except_user_ids = ban_user_ids | friend_user_ids
recommend_users =
User \
.where("? < last_login_at", time - RECOMMEND_SEARCH_DATE_RANGE.day) \
.where(rank: (self.rank - RECOMMEND_SEARCH_RANK_RANGE)..(self.rank + RECOMMEND_SEARCH_RANK_RANGE)) \
.where.not(id: except_user_ids) \
.where(is_allow_recommend_search: true, is_tutorial_finished: true) \
.order(:last_login_at) \
.limit(RECOMMEND_SEARCH_LIMIT_COUNT)
.to_a
friend_counts =
Friend \
.where(status: Friend.status_value(:friend), user_id: recommend_users.pluck(:user_id)) \
.group(:user_id).count
recommend_users.select! { |user| friend_counts[user.id].to_i < MAX_FRIEND_COUNT }
recommend_users.sample(RECOMMEND_SEARCH_SAMPLE_COUNT)
end幾らインデックスをきちんと張ってようとも、QA検証まで恙無く通っても、負荷試験で莫大なユーザー数を登録するような試験を行うと、死ぬと思います。
もし、そのままリリースしてしまったら、その機能で数秒待たされるとか、そんな事が頻発したりだとか……。
原因としては、上記の仕様においてはユーザーを一意に絞り込めるような条件が存在しないので、ユーザーが増えれば増えるほど、クエリの負荷が増大していく訳ですね。
じゃあ、どうしたら良いんでしょうか。
個人としての現状のベストとしては、以下です。
改善への考察
まず、現状の実装の問題点や、仕様でもうちょっと絞り込めるようにして良いような部分を見つけましょう。
現状の実装の問題点としては、愚直にクエリ検索をする一番のデメリットは、ゲームが繁盛するに連れて検索量が莫大になっていってしまう、って事ですね。
また、仕様を見返してみてみると、一番優先度の低いと言うか、もっと厳しくして良いものが一つありますね。
- 3日以内にログインしている
繁盛しているゲームならこれを1時間以内、とかにしても良いと思いますし、それで検索量も一気に減って解決！ とかなると思うんですけど、繁盛していないゲームになると、深夜にアクセスされた時には、工夫しないと何も検索結果として出てこないとか有り得そうだったり……。
まあ、この2点から考えると、最後に何らかのアクションを起こしたユーザー群から絞り込む、で良いんじゃない？ って発想が浮かんできました。
要するに、別にお勧めユーザー検索用のテーブルを作り、そこにトリガーとなるアクションを起こしたユーザーを入れて、その末尾のレコード数十件を引っ張ってくるって形ですね。
そういう形ならば、幾らユーザーが増えようとも参照量は一定ですし、時間帯で検索に引っ掛からなくなるような不安もなくなります。
ただ……この手法にも欠点がない事もなくて。
ゲームが過疎ってしまうと、同じユーザーばっかり抽選されるようになります。
この手法はお勧めユーザー抽選以外でも、アクティブなデータからの効率的な参照方法として使えると思いますが、ゲームそのものが過疎らなくても、その機能が余り使われなくなるとか、そういう要因でこの事象が発生してしまう可能性はぼちぼちあります。
そういう点も留意した上で、プランナーに今のままの仕様じゃサーバーが死ぬ事と、これこれこういう理由で、ここの仕様をこう変えて貰いたいという事を伝えて、首を縦に振って貰って、改修しましょう(振ってくれない？ ……その時は青い空の広さをぼんやりと眺めましょう)。
書き換えたら多分こんな感じのソースになる
FRIEND_COUNT_MAX = 30
RECOMMEND_SEARCH_RANK_RANGE = 5
RECOMMEND_SEARCH_LIMIT_COUNT = 100
RECOMMEND_SEARCH_SAMPLE_COUNT = 30
# フレンド数が増減した、お勧めユーザーランクが上下した、ログインした、などの各種トリガーの度に呼び出す
def check_user_recommend
# lastで参照する為、古いデータは削除する
UserRecommend.where(user_id: self.id).delete_all
return unless self.friends.where(status: Friend.status_value(:friend)).count < FRIEND_COUNT_MAX
return unless self.is_allow_recommend_search
return unless self.is_tutorial_finished
UserRecommend.create!(
user_id: self.id,
user_rank: self.rank,
...
)
end
end
def search_recommend_users(time = Time.current)
ban_user_ids = BannedUser.current(time).pluck(:user_id)
friend_user_ids = Friend.where(status: Friend.status_value(:friend), user_id: self.id)
except_user_ids = ban_user_ids | friend_user_ids
rank_range = (self.rank - RECOMMEND_SEARCH_RANK_RANGE)..(self.rank + RECOMMEND_SEARCH_RANK_RANGE)
recommends =
UserRecommend \
.where(user_rank: rank_range) \
.where.not(user_id: except_user_ids) \
.last(RECOMMEND_SEARCH_LIMIT_COUNT)
User.where(recommends.sample(RECOMMEND_SEARCH_SAMPLE_COUNT).pluck(:user_id))
end対象ユーザーを抽出する部分がとってもスッキリしましたね！
没案
ユーザー単位に1~100くらいでランダムな数字を振ったりして、その値を参照される度にランダムに指定し、数値が合致するユーザーだけを抽出する。
=> QA検証的にも分かりづらいし、本番で不具合が起きても検証出来ない
検索を全ユーザーDBからではなく、自分の属するユーザーDBのみからにする。
=> ……妥協案で敗北した気分になるから嫌だ。
connpass
Peatix
Wantedly
デザインチームnote