SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

もうちょっと「艦これ」からSQLを考えてみる１

艦これ

　かなり前に下書きをしていたんだけれど、バタバタしていてほったらかしになっていたものです。タイミングがズレたけど、まだ「艦これ」をやっている人がいるみたいなのでアップします。

私が「艦これ」の設計をするなら

　「軍艦を擬人化する」という発想は、生まれ変わっても私からは出てこないし、企画書を見せられても反応すらできないとは思う。ですから、「企画した人は凄いな〜」とは思うけれど、そんな分からないことを考えても意味ないので、遊んでいる半分以上の時間は、「自分ならどう設計するか」を考えていました。

　一番気になったのが、

　「何でこんなに遅いのか？」
　「10万同時アクセスで落ちるとしたらどんな構造か？」

ということでした。

　MySQL Cluster を利用しているとして、10分ほど遊んで考えたのが次のようなモノでした。

　APサーバは分割するけれど、DBは分割しない。
　更新系はNoSQL（API）を利用する。
　非同期処理。

　　　　# で、もうちょっとやってみて……
　　　　# ネット対戦型じゃないパチンコレベルと理解して……
　　　　# ナメとるのかと……
　　　　# こんなしょうもないシステムならクライアントでせいよ！って……

　まあ、それはさておき、私が考える設計なら、コネクションプールやメモリーの設定で余程のヘマをしない限り10万同時アクセス程度では止まらない。

　DBをサーバ群毎に分割して独立させているのは意外で、それならもっと落ちにくくなるはずです。しかし、そこまでしても落ちるというのは、どんなことをしているのか？とイロイロと考えてみました。ベターを予測するよりヘボい方は無限にパターンがあるので難しい。難しいから面白いのでやめられない。

シーケンシャルでSQLを流す

　止まった理由を考えるのに、私の感覚では派手なアニメーションは負荷分散のために存在する（システム側の人は普通そうでしょう）と思っているので、シーケンシャルな処理はわざわざ考えないと出てこない。

　しかし、画面が切り替わらずにしばらくしてエラー画面が出るということがあったので、もしかしてシーケンシャルに処理をしているんじゃないか？とまず想像しました。ところが、シーケンシャルに処理をしたとしても、その程度じゃあ、そうは落ちない。

「じゃあ、NoSQL（NDB API）は使ってないんじゃないか？……それでも、そう簡単には落ちないな〜。何が問題なんだろう？」

　どうも不安定になったとき、みんなが「F5 連打する」ことの影響が大きそう。

……前回の記事を書いたときに、「資源増加」を思い出し、
「そのタイミングでアップデートしているんじゃないか？」
「いや、それならアクティブユーザ以外の情報も更新が必要になる……」
「まさか、それはないだろう」

「あっ！SELECT前後に UPDATE 流してるな！」

　と思い至りました。（もちろん、予想でしかない）そう言えば、修理する画面に遷るときに時間が掛かり、落とされることもあったし、SELECTだけでそんなことが起きるとは考えにくいけれど、SELECTするときに、資源が増えるタイミングを超えていたら、増えた資源分を UPDATE する。という仕様なら、DBサーバの負荷は劇的に上がる。

私ならこんなテーブルレイアウト

　「艦これ」は時間経過で資源が増えてきます。私がその後も遊んでいる「Clash of Clans」も、時間経過で資源が増えてきますから、恐らく、これは無料でも遊べて、もっと遊ぶには課金をするというタイプのゲームにとって一般的な考え方なのでしょう。（オンライン麻雀とか将棋でも似た概念がありますしね）

　「艦これ」にそういう考え方があったのかは分かりませんが、「Clash of Clans」ではレベルに応じて資源の増え方が速くなり、課金してアイテムを購入すれば、更に、一時的に増える速度を上げられる……。そんな仕様変更は起こり得るイメージで設計します。

　単純でアクセス数の多いシステムなので非正規化したくなるけれど、そういう仕様変更に対する許容度は非正規化すると小さくなる。ですから、正規化して、恐らくサロゲートキーは利用せず、ナチュラルキーでこんな感じになります。（最大数などを別テーブルにするかも知れませんがその辺は読み替えて！）

　資源が増えるのが一律（確か、現状そうでしょう）で、レベルの最大が999だとしたら（知らんけど）、増加マスタ（ネーミング下手くそでゴメン）に1〜999までの999件を同じデータで埋めておく。（あるいは、1件だけ作ってJOINしない）

　まあ、パフォーマンスや負荷にはほとんど影響しないのですが、それでもアクセスが増えたら効いてくる可能性もある。増加マスタはAPサーバにキャッシュしてそちらで計算しても良いけれど、UPDATEを流すことと比べたら誤差です。

具体的なSQL

　SQLで全部計算するとすると、こんな感じのSQLになります。
　※ 本当はこちらにあるようにストアドファンクションを利用すべきです。

SELECT
	……
	, CASE WHEN 
		sub.弾薬数 
		+ (b.弾薬増加数 * sub.通常分数) 
		+ (b.弾薬増加数 * c.弾薬増加倍数 * sub.ブースト分数) > b.弾薬最大値 
		THEN b.弾薬最大値 
		ELSE sub.弾薬数
			+ (b.弾薬増加数 * sub.通常分数) 
			+ (b.弾薬増加数 * c.弾薬増加倍数 * sub.ブースト分数)
	END	AS 現在弾薬数 
	, CURRENT_TIMESTAMP
	, ……
FROM
	(SELECT 
		a.*
		, CASE WHEN a.ブースト終了時間 > a.前回更新時間 THEN -- ブースト中
			CASE WHEN a.ブースト終了時間 > CURRENT_TIMESTAMP THEN 
				TIMESTAMPDIFF(MINUTE, a.前回更新時間, CURRENT_TIMESTAMP)
			ELSE
				TIMESTAMPDIFF(MINUTE, a.前回更新時間, a.ブースト終了時間)
			END  
		ELSE 0 END AS ブースト分数
		, CASE WHEN a.ブースト終了時間 > a.前回更新時間 THEN -- ブースト中
			CASE WHEN a.ブースト終了時間 > CURRENT_TIMESTAMP THEN
				0
			ELSE
				TIMESTAMPDIFF(MINUTE, a.ブースト終了時間, CURRENT_TIMESTAMP)
			END  
		ELSE TIMESTAMPDIFF(MINUTE, a.前回更新時間, CURRENT_TIMESTAMP) END AS 通常分数
	FROM 
		ユーザステータス AS a
	WHERE 
		a.id = ?
	) AS sub
	INNER JOIN 増加マスタ AS b
		ON sub.ユーザレベル = b.ユーザレベル
	INNER JOIN ブーストマスタ AS c
		ON sub.ブーストレベル = c.ブーストレベル;

　SQLのダサさが目一杯出た形ですね。
　ブーストマスタの概念がなく、レベルによって増加スピードに差がないなら、例えば、増加マスタテーブルに「ユーザレベル＝ 0」のデータを1件だけレコードを作っておき、

SELECT 
	-- カラムの羅列（省略）
	, CURRENT_TIMESTAMP
FROM ユーザステータス AS u
	, 増加マスタ AS z
WHERE
	u.ユーザID = ?;

　としておいてもDBサーバの負荷は変わりません。増加スピードをマジックナンバでハードコーディングするよりはよっぽどマシです。APサーバ側で定数にするのではなく、全てのマスタデータはDBで管理するべきでしょう。

　ブーストという概念があろうとなかろうと、APサーバ側で必要な情報は変わらないから、SQLで処理していれば直さないと行けないのはSQLだけとなります。

APサーバでやってると勘違いが起きる？

　SQLで処理すると、ストアドファンクションを使わなければかなり格好の悪いコーディングになります。先ほどのSQLではいくつかCASE式を使っていますが、これは単純な導出項目ですからDBサーバで計算しようが、APサーバで計算しようが負荷という意味では誤差です。

　ですから、

SELECT 
	-- カラムの羅列（省略）
	, CURRENT_TIMESTAMP
FROM 
	ユーザステータス AS a
	INNER JOIN 増加マスタ AS b
		ON a.ユーザレベル = b.ユーザレベル
	INNER JOIN ブーストマスタ AS c
		ON a.ブーストレベル = c.ブーストレベル
WHERE 
		a.id = ?;

　として、APサーバで計算してもそれで良いのですけれど……。

　DBサーバにとって何が負荷が大きいか分からない人が、APサーバで処理をやっていると、「せっかく計算したし実データを更新しておこう」という発想が生まれている様な気がするのです。
　いくらなんでも、そんなことはないか……。
　でも、それぐらいのことをしないと、計算上は止まらないんですけどね……。

シナリオ変更も予想（提案）する

　「艦これ」のようなシステムであれば、グラフィック担当、声優さんの制約があるので、システム側の一存で私がDB周りとプログラム担当で参加したとしてら、システム側の人間として、

　「資源の増え方を変えることができる」

という提案はします。
最低でも上ぐらいの準備をしておきます。

　例えば、「艦これ」であれば

　課金アイテムを買えば、大本営に「xxxを攻略のため、しばらくの時間、資源を多く廻して欲しい」という交渉を行える。というシナリオに変えて、500円払えば、24時間の間、資源の増え方が2倍になる。交渉のときに連れていく秘書？とレベルによっては、x倍になることも（レアイベント）もある。

　とすれば、課金で資源を買うよりも、「課金をしたくなる」という鬼の設定（笑）にもできるわけです。

　もちろん、新たなイベントを作るのは、声優さんをもう一度招集したり、グラフィックを増やしたりすることになるので現実的には難しいでしょうが、「システム的には簡単なのでいつでもできますよ」ぐらいのことは、システム側から提案すべきです。

　つづく。