もうちょっと「艦これ」からSQLを考えてみる4
DBサーバの処理を肩代わりして負荷を減らせるとしても、
最後のソート（これは問題になるなら禁止すれば良い）
IF文
四則演算
だけです。
「IF文、四則演算をAPサーバやクライアントで処理すべき」と考えられるのは、設計段階にSQLでどこまでできて、どんな記述をするか、全体の処理、負荷のバランスが完全に理解できてないと無理です。そういう技術者は少ないから、その様な設計をすることは難しい。
全部を俯瞰できる優秀な技術者を集めて突き詰めて考えたとしても、IF文、四則演算をAPサーバなどで行うには、ほとんどの場合、転送量かSQLの発行回数が増えるため、減る負荷よりも遙かに大きな負荷が掛かります。つまり、「IF文、四則演算をAPサーバやクライアントで処理した方が良い」というパターンは本当に少ないので、全体を細かく考えられないなら「とにかくSQLでやる」と決める方がマシです。
しかし、複雑な処理をSQLで行えば、ここにある様にとても醜いプログラムになる。
その理由は、SQLに分岐がなく無理矢理 CASE式で行うからためです。しかし、こういう場合は、他の言語と同様に Funciton にすれば良い。
求めている現在値は、
前回更新時間
前回の数
増加数
ブースト終了時間
ブースト倍数
最大値
があれば導出できることが理解できれば、ストアドプロシージャ（ファンクション）だからといってどうということはないでしょう。私もMySQLでは実務で使ったことはないけれど、肩肘張らずに他の言語の関数を作るのと同じように、機能と引数を考えれば、別に何も難しくないと分かるはずです。
計算するだけのストアドファンクション
delimiter // -- ストアドプロシージャの中でも終端のデリミターが必要なので、デリミターを変更 DROP FUNCTION IF EXISTS fc現在値 // CREATE FUNCTION fc現在値( 前回更新時間 TIMESTAMP , 前回の数 INT , 増加数 INT , ブースト終了時間 TIMESTAMP , ブースト倍数 FLOAT , 最大値 INT) RETURNS INT BEGIN DECLARE _ブースト分数 INT; DECLARE _通常分数 INT; DECLARE _ret INT; IF ブースト終了時間 > 前回更新時間 THEN -- ブースト中 IF ブースト終了時間 > CURRENT_TIMESTAMP THEN SET _ブースト分数 = TIMESTAMPDIFF(MINUTE, 前回更新時間, CURRENT_TIMESTAMP); SET _通常分数 = 0; ELSE SET _ブースト分数 = TIMESTAMPDIFF(MINUTE, 前回更新時間, ブースト終了時間); SET _通常分数 = TIMESTAMPDIFF(MINUTE, ブースト終了時間, CURRENT_TIMESTAMP); END IF; ELSE -- ブーストなし SET _ブースト分数 = 0; SET _通常分数 = TIMESTAMPDIFF(MINUTE, 前回更新時間, CURRENT_TIMESTAMP); END IF; SET _ret = (増加数 * ブースト倍数 *_ブースト分数) + (増加数 * _通常分数); -- CASTは自動で行われる。嫌な人は明記すれば良い。 IF _ret > 最大値 THEN RETURN 最大値; ELSE RETURN _ret; END IF; END; // delimiter ; -- デリミターを戻す。
SQLをラップするストアドプロシージャ
delimiter // DROP PROCEDURE IF EXISTS prユーザステータス // CREATE PROCEDURE prユーザステータス(IN p_id INT) -- INタイプのパラメータを指定 BEGIN SELECT …… , fc現在値(a.前回更新時間, a.弾薬数, b.弾薬増加数, a.ブースト終了時間, c.弾薬増加倍数, b.弾薬最大値) AS 弾薬数 , fc現在値(a.前回更新時間, a.石油数, b.石油増加数, a.ブースト終了時間, c.石油増加倍数, b.石油最大値) AS 石油数 …… FROM ユーザステータス AS a INNER JOIN 増加マスタ AS b ON a.ユーザレベル = b.ユーザレベル INNER JOIN ブーストマスタ AS c ON a.ブーストレベル = c.ブーストレベル WHERE a.id = p_id; END; // delimiter ; -- デリミターを戻す。
大した差はない
ストアドファンクションは、CASE式をキレイに書くために切り出しただけ。他の言語でも同じように作るんじゃないの？
ストアドプロシージャは単純にSELECT文をストアー（保存）するだけのもの。何も難しいことはしてないし、SQL文（SELECT文）のままでも悪いわけではないけれど、ストアドプロシージャを作れば、APサーバでは、SQL文の代わりに
CALL prユーザステータス(?);
だけで良い。これでAPサーバ（オブジェクト指向言語）から見ても、異物として紛れ込むSQL文を最小限にすることができる。完全な疎結合になるので本当は一番キレイです。
MySQLはストアドプロシージャにしてもプリコンパイルしない（らしい）ので、実行時のコンパイルを避けるということはできないようですけれど、SQL文をDBサーバで管理するのはメンテナンス面からも、本来はメリットがある。
文法としては、代入するのにいちいち「SET 変数 = 値;」とするのは古典的で格好は良くない。しかし、ネットワークを挟んでループさせたり、大量のデータを転送して処理するという、設計上の大問題（私的にはバグ）をやってしまう方が遙かに格好悪い。
ストアドプロシージャの文法はどうしようもないが、設計上の問題は担当する技術者の問題に他ならない。自分の担当分野の問題は平気なのに、「文法のダサさが気になる」というのは、完全に間違ってるのです。
もうちょっと「艦これ」からSQLを考えてみる3
前回のパターンであれば、全体を（パフォーマンス・サーバの負荷・開発工数・メンテナンス性）を鑑みて、IF文と四則演算は、DBサーバより、APサーバ。APサーバより、クライアントにある程度キャッシュさせて、処理もクライアントで行った方が良いです。
そう判断するには、設計段階でSQLで処理したときどうなるか、SQLで処理しなかったときどうなるか。が正確に分かってないと判断できません。
例えば、「艦これ」からは随分と離れますが、あるテストの男子と女子、全体の平均と最高点・最低点を求めたいとする。（面倒なので性別は非正規化されているとする）
テーブルは以下の通り
成績テーブル
テストID
生徒番号
性別（1:男子、2:女子）
得点
1.SQLで処理する
SELECT テストID , AVG(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子平均 , AVG(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子平均 , AVG(得点) AS 全体平均 , MAX(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子最高点 , MAX(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子最高点 , MAX(得点) AS 全体最高点 , MIN(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子最低点 , MIN(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子最低点 , MIN(得点) AS 全体最低点 FROM 成績テーブル WHERE テストID = ? GROUP BY テストID
実行計画をとれば分かりますが、図の様に全ての集計が1回のループ内で処理されます。
2.単純な集計のみSQLで行う
SELECT テストID , AVG(得点) AS 平均 , MAX(得点) AS 最高点 , MIN(得点) AS 最低点 FROM 成績テーブル WHERE テストID = ? AND (性別 = ? OR ? = 0) GROUP BY テストID
上のSQLを0〜2までループするか、SQL文を動的生成し、全体を取得するときは
AND 性別 = ?
を付けない形もあるかと思いますが、こういうプログラムを書く人は多いでしょう。
この処理は、DBサーバ、APサーバ共に余分な処理が増えているだけで、最終的な処理時間も長くなりますから、メモリーを掴んでいる時間も長くなります。
1.のSQLが掛けない人に配慮する以外にメリットはあり得ません。
3.全件送信してAPサーバで計算
集計関数が分からないという人は、以下の様な処理を選ぶかも知れません。
SELECT テストID ,性別 , 得点 FROM 成績テーブル WHERE テストID = ?
DBサーバの処理はIF文と四則演算が減っただけ（誤差）。その代わりに大量のデータ送信が行われます。当然、最終的な処理時間も長くなりますから、メモリーも長時間掴んだままになります。
こちらも、1.のSQLが掛けない人に配慮する以外にメリットはあり得ません。
ソースのキレイさで選んではいけない
APサーバ側のソースのキレイさで選んでしまう人にとっては、SQLを使いきる1.が一番違和感があって汚いと感じるでしょう。3.が一番キレイに感じるかも知れません。
全体の処理フローから考えれば、2.3.の
ネットワークを挟んでループする。
無駄なデータを転送して、APサーバで四則演算する。
という処理はあり得ないほど非効率ですから、もちろん、2.3.で「DBサーバの負荷が下がる」と考えるのはプロと呼べないレベルです。馬鹿のカスのと言うけど、それでは足りないほどお粗末なレベルです。
SQLでなくオブジェクト指向言語で書くプログラムをDBサーバに置くなら 2.3.の処理を書く馬鹿は少ないし、もし、いても設計担当からハズされるでしょう。
2.3.はシステムが複雑になっただけで、依存関係が強くなり、言語のスパゲティ（SQLとOO言語）になっている。DBサーバの負荷も、APサーバの負荷も減らず、とてもプロが設計したシステムとは思えません。
しかし、こんなお粗末なレベルのモノも現場で普通に存在します。それが平気なのは処理フローが全く見えてない、DBサーバをブラックボックスとして考えているのでしょう。ですから、SQLになれば突然、ほとんどの人が理解できなくなって、「2.3.の処理が正しい」と主張し出してしまう。SQLとオブジェクト指向言語が等価と考えてない（何度も繰り返しますが、SQLをコンパイルした後は等価）等価と考えてないということは、SQLを魔法か何かと勘違いしている訳です。
全体の処理フローを突き詰めて考えていけば、APサーバでDBサーバの処理を肩代わりして負荷を下げることができるのは、最後のソートと、IF文、四則演算しかありません。
ソート以外は誤差で、ほとんどの場合その誤差を減らすために返って処理量が増えますから、「RDBMSを使う以上、SQLで書くしかない」という結論になります。
※ ただ、SQLが苦手なシーケンシャルな処理は（ストアドプロシージャか）APサーバでやるべきですが。
分かってなくてもできるから良いか？
結論的には、業務システムで今のサーバなら気にしないでもいいと思う。下手糞とは思うけど、下手糞しか居ないんだから仕方ない。ハードが良くなりすぎて、業務上トラブルになるほどの負荷、パフォーマンスになることはほとんどないでしょう。取りあえず下手糞が設計しても動きます。今のサーバは止めようと思っても、そう簡単には止めるほどヘボいプログラムは作れない（苦笑）
まあ、スラスラSQLが書ければ工数も下がるのですが、できない馬鹿が多すぎてそうも言ってられないからね。
しかし、ソーシャル系はユーザの増加は予測困難ですから、「取りあえず動く」は拙い。
APサーバのプログラムとDBサーバのプログラム（SQL）を機能単位に同じ人に割り振っている現状の人員配置、開発手法では、全員にオブジェクト指向言語からSQLまで分かっていなければ、真っ当なものは出来ないということになります。
ぶっちゃけ、それだけ人員を集めるのは難しいでしょう。
ですから、APサーバのプログラムを担当する人、DBサーバのプログラムを担当する人を完全に分けるべき。ほとんどの人はできているつもりで、RDBMS（SQL）をブラックぼっくとして中途半端に扱っているのですから、本当にブラックボックスにしてしまえば良いわけです。
「馬鹿な考え休むに似たり」「馬鹿は考えるな」ってことです。
そのためにはSQLはストアドプロシージャでラップするのが一番良いのです。後からどうとでもできます。普段から「1.しかあり得ない」という人が「ストアドプロシージャなんて」というなら聞く意味はありますが、「2.や3.で何が悪い！」っていう馬鹿が「ストアドプロシージャなんて」という意見には何の価値もない。基礎も分かってない人間が応用部分のメリットが理解できるわけがないからね。（ここが悩ましいところ）
「艦これ」は、カタログスペックの1000分の1以下で止まっている訳ですから、どんな失敗が重なった設計なのかは私には見当もつかない。余程失敗が重ならないと、1000分の1が出せないってことはないでしょう。
WEBサーバ、APサーバが何台で運用されているかは分かりませんから、その台数によってはシステムは簡単に落ちるでしょうけど、DBサーバが原因になるのは、設計に余程の問題を抱えている（いた）はずで、それはSQLの無理解から来ているはずです。
もうちょっと「艦これ」からSQLを考えてみる2
前回の続き。
「艦これ」で起きた障害は、カタログスペックの100分の1以下の処理量で発生ししていると思われる。その原因はDBサーバに対する無理解、SQLに対する無理解から起きているのでは？という考察です。
もちろん、中身は知らないけれどね。
どこで処理するか？
少し昔話から。コンピュータシステムはざっくりと、
汎用システム
クライアントサーバシステム
Webシステム
と発展してきました。
汎用システムの時代は、端末に処理能力がありませんでしたから、あらゆる処理をホストコンピュータが担っていました。これがクライアントサーバシステムになったとき、どちらで処理を行うかを考える必要が出てきました。
Webシステムになると、DBサーバ、APサーバ、クライアントのいずれもが処理能力を持ち、多くの処理をそれぞれが代替することができる。
開発時はDBサーバ・APサーバ・クライアントが1台のPCで行われることも少なくなく、クライアントサーバシステムの頃、Exeファイルを置いただけのファイルサーバのことをAPサーバと呼んでいた馬鹿者も少なくなかったので、今でも分かってない人は多いのじゃないかと想像する。
どのコンピュータで処理するかはシステム設計の根幹で、これを理解していたら、RDBMSを使う以上、「SQLを使いきるべき」と理解できるはずですが……。
前回のSQLはこういう流れになる。
赤い部分は、APサーバでも、クライアントでも代替できる処理で、そこで何をやっているかというと、
ブーストの時間内か判断（IF文）
経過秒数と増加数、前回の保有量から現在値を算出（四則演算）
最大容量を超えないか判断（IF文）
数個のIF文と四則演算を、どのコンピュータにやらせるかによってシステムダウンするかどうかが変わる。それほどシビアなチューニングがなされたシステムであれば、カタログスペックに近い性能が出ているでしょう。
赤い部分、つまりIF文と四則演算は、どこで処理してもシステムに与える負荷は誤差レベルです。
本来はこうすべき
それぞれのサーバの負荷、パフォーマンス、コーディングを考えて設計すればこうあるべきでしょう。
SELECT -- カラムの羅列（省略） , CURRENT_TIMESTAMP FROM ユーザステータス AS a INNER JOIN 増加マスタ AS b ON a.ユーザレベル = b.ユーザレベル INNER JOIN ブーストマスタ AS c ON a.ブーストレベル = c.ブーストレベル WHERE a.id = ?;
DBサーバ、APサーバのどちらで処理を行っても、データの転送量も転送回数も大差はないため、「どこでやっても同じ」なら「キレイなコーディングになる方が良い」と判断できます。
データはDBサーバにしか存在しませんから、これ以上、DBサーバの処理を減らすことはできません。つまり、JOINを避けるなどSQLを分割しても、DBサーバの処理は増えるだけで減ることはないのです。
SQLでどこまでできるか？
それはどんな流れになるか？
コーディングはどちらが分かりやすいか？
などが設計段階で見えてないと本来は設計できませんが、そういう人はほとんどいないでしょう。
もちろん、SQLが苦手な人が設計してもこうなることはありますが、それは偶然に過ぎないのです。分かってないんですから……。
SQLを除けて考えれば理解できる
データを保存しているデータ処理を、SQLではなくオブジェクト指向言語などで可能なコンピュータ（データサーバ）があったとします。
データサーバがデータを読み込むときは、メモリー上に展開する必要があります。
データサーバのメモリー上に展開したデータを、別のコンピュータに転送。
別のコンピュータでも、当然、メモリー上に展開し、データ処理（IF文、四則演算など）を行う……。
どう考えても無駄ですよね？
ですから、データサーバが扱う言語がSQLではなくオブジェクト指向言語で処理が可能だった場合、ほとんどの人が、わざわざ別のコンピュータにデータを送って処理しようとはしないはずです。メモリー上にデータを展開しながら、データ処理（IF文、四則演算など）を行って、結果だけを別のコンピュータに転送するでしょう。
しかし、「SQLは訳が分からない」「分かったつもりになっている」人達は、平気でそれをやろうとしますし、それが正しいと主張します。おかしいでしょう？
「SQLで処理したら遅い」
「SQLで処理したらDBサーバの負荷が高まる」
などは、全く意味を分かってない発言なんです。
SQLを魔法か何かと勘違いしているからでしょうが、SQLを受け取って、SQLをコンパイルして処理するDBエンジン（オプティマイザ）のほとんどは C++ で書かれたプログラムです。つまり、SQLは仕様書にあたり、プログラムとは等価です。実行時には C++ で書かれたプログラムがデータにループしながらアクセスするわけですから、本来、設計する人は
SQL → プログラム（オブジェクト指向言語）
プログラム（オブジェクト指向言語）→ SQL
のどちらも自在にできないと、どこまでをSQLで処理するのが良いのか決めることはできません。
SQLとオブジェクト指向言語が一致しない人達は、SQL（RDBMS）をブラックボックスとして考えて、何となく設計ができた気でいるだけです。もちろん、そんなやり方でも動く動かないで言えば動きます。オブジェクト指向言語で処理が可能なデータサーバがあったとして、どちらで処理をしても同じ結果が出るのは当たり前です。
ただし、分からない人が適当にやってる設計ですから、巧くいくかは運次第です。場合によっては「カタログスペックの1000分の1も出せない」という結果になってしまいます。
コンパイルが通っただけ
私に絡んで来るような人は、ヘボいとは思うけれど、それでも、業界的にはマシな部類でしょう。取りあえず、手続き型言語（オブジェクト指向言語）はそこそこに使っているはずです。
新人や後輩、部下の指導もしていることでしょう。
例えば、新人を指導していて、「コンパイル（ビルド）は通りましたが、よく分かりません」と言ってくることはあるし、そんなことは言ってこなくても「コンパイルが通っているだけで、頭を抱えるスパゲッティコード」は多数存在します。
何がしたいか分かってないからスパゲッティになるわけで、
まあ、新人のうちは、コンパイルを通すだけでも必死になるのは仕方がないことですから、それなりの指導すればよいことですが、もちろん、「コンパイルが通っただけ」ではプログラミングができたとは言わない。それを理解して、「今はコンパイルを通すだけで必死だけれど、ソースの意味が分かって書けるように頑張ります！」と努力している人は、どんなに時間が掛かっても、バカともカスとも言わない。
全力でサポートします。
じゃあ、新人を指導していて、「コンパイルが通ってるんですから、何が悪いんですか！動くんですから何が悪いんですか！」と口答えしてきたらどう答える？
そんな奴をプロとは呼べないでしょう。
……しかし、「INとEXISTSの違いが分かってない（MySQLはちょっと違うけど）」という人が、かなりの数います。そのレベルは、つまり、「コンパイルは通っているけれど意味が分かってない」状態な訳で、そのレベルのシステムを客に売ってるってことです。
そのレベルの人間（技術者じゃない）がド偉そうに掛かって来るから「カスのバカの」と言うのです。まあ、意味が分かってないから掛かってこれるんでしょうけどね……。
「分かってないことは拙い」
「早く理解しないと」
と思っている人にはそんなこと言いませんが、分かった気でいっぱしのプロみたいなことを言われてもね……。
サーバの性能が上がっているから、サーバが助けてくれるなら良いんじゃないの？と思うことも増えてきて、説得するのが面倒になって流されることも多くなってきましたが、まあ、理解できてないと「艦これ」程度のアクセスで止めてしまう。
プロならできるべきですね。
つづく
シェルが使えないレンタルサーバで Facebook API を使う
PHP 5.2 なら JSON は標準で入っていますからこんな苦労はないですが、レンタルサーバで PHP 5.1.6、シェルは使えない。という環境で Facebook API を使うのに嵌まった。シェルが使えないサーバで開発ってこんなに大変なのね（苦笑）
もう二度とやりたくないけど、せっかくだから備忘録です。
手順
Facebook SDK for PHP をダウンロードしてクライアントで解凍。
facebook.php
base_facebook.php
をパスが通るところに置く。
class-json.php をダウンロードしてパスが通るところに置く。
class-json.php
if (!function_exists('json_decode')) {
throw new Exception('Facebook needs the JSON PHP extension.');
}こんな風に書き換える。
if ( !function_exists('json_decode') ) {
function json_decode( $string, $assoc_array = false ) {
global $wp_json;
if ( !is_a($wp_json, 'Services_JSON') ) {
require_once( 'class-json.php' ); //パス通は適当に指定する
$wp_json = new Services_JSON();
}
$res = $wp_json->decode( $string );
if ( $assoc_array )
$res = _json_decode_object_helper( $res );
return $res;
}
function _json_decode_object_helper($data) {
if ( is_object($data) )
$data = get_object_vars($data);
return is_array($data) ? array_map(__FUNCTION__, $data) : $data;
}
}あとは、ソーシャルメディアAPI リファレンス この辺の通りにすれば多分できると思います。
WordPress は JSON を使って動いているはずなので、その辺を参考にしました（笑）
SDKを書き換えているので自己責任でお願いします。
こんなことに嵌まるとは、もう歳やね……。
もうちょっと「艦これ」から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倍になることも（レアイベント）もある。
とすれば、課金で資源を買うよりも、「課金をしたくなる」という鬼の設定（笑）にもできるわけです。
もちろん、新たなイベントを作るのは、声優さんをもう一度招集したり、グラフィックを増やしたりすることになるので現実的には難しいでしょうが、「システム的には簡単なのでいつでもできますよ」ぐらいのことは、システム側から提案すべきです。
つづく。
「艦これ」もう少し思い出して考えてみた
「艦これ」はあんまり関係ないのですが、スケールアウトについて。
「艦これ」は既に MySQL Cluster を使っているなら関係ないのですが、何度も書きますけれど、「JOIN禁止」はスケールアウトと関係ない。
Twitter規模になれば別の考え方が必要になりますが、取りあえず今は DBサーバ は1台だけれど、「スケールアウトのためにJOIN禁止」は「究極のバッドテクニック」ですのでやらないように！
単純だから良いも、複雑だから良いもない。
必要なのは効率的かどうかを考えることだけで、それはどんなシステムでも同じ。
「艦これ」は……
「艦これ」は MySQL Cluster を使っているとのこと。私が始めた頃は、4・5個の鎮守府（サーバ群）があった様に記憶しています。
MySQL Clusterと聞いて、DBは1つのクラスタ構成で処理されていると思ったのですが、障害は鎮守府（サーバ群）単位で起きている様で、「DBサーバの不調」である鎮守府で障害が起きている。というアナウンスがしばしばあった様です。
つまり、図の様に
鎮守府（サーバ群）毎に、DBサーバクラスタを配置して、それぞれのDBサーバは連携してないというイメージのようで、全体が落ちないということは、DBサーバがシングルポイントになっている訳ではないということですね。
ユーザ数60万、アクティブユーザ35万、同時ユーザ10万、更に増え続けていると言われていますが、鎮守府（サーバ群）単位で見ればもっと少ない。ということは、MySQL Clusterは、負荷分散ではなく、フォールトトレランスのために使っているのかな？
私の記憶の範囲では、ゲーム内で他のユーザのデータが必要になる処理は、「ランキング」と「演習」しかなかったように思います。
ランキングが鎮守府（サーバ群）単位なのか、全体なのか分かりませんし、リアルタイムなのか、ディレイあり（バッチ処理）なのかは分かりませんが、まず間違いなくバッチ処理でしょう。バッチ処理なら、鎮守府（サーバ群）を跨いでも全く大したことはない。
また、「演習」では1日2回対戦相手を5名選んでいる（つまりバッチ処理やね）ようですが、リアルタイムで対戦するわけではなく、自分が誰かの対戦相手になったかどうかも分かりません。更に、鎮守府（サーバ群）を超えての割り当てはないのでは？
一般的なネット対戦型ゲームなら、ユーザのデータが変化すると同時に対戦相手のデータも変化しますが、対戦相手のデータに変化のない「艦これ」は非常に単純で、基本的なゲーム部分はスタンドアローンでも十分可能でしょう。
演習の相手は架空でも分からない（？）からね……。
更新処理はどうなってる？
コネクション数や、それに関わるメモリーがボトルネックにならないように調整したら、次に来るのは「データ更新時の問題」になります。
これはLOCKの問題や、I/Oの問題など様々なことが起こりますが、それは個別に見なければ分かりませんのでおくとして、更新については、亜種はいくつもありますが、「艦これ」のようなシステムなら、主に以下の様な処理になるでしょう。
私なら、APサーバの負荷を下げるためにクライアントに抽選をさせてしまうかな……。まあ、抽選の元になるデータをクライアントに送信するのは嫌がる人が多いでしょうが、解析したり、改造したりする違法な人達は、ピックアップして退会させるなり、訴えるなりすればいいしね……。
＃ 将来、対戦型にバージョンアップするために……。
＃ まあ、そりゃそうかもな……。
逆に、もし、ログが必要なぐらい厳格なシステムであれば、ログは、UPDATE より軽い INSERTだけ（書き換えたらログじゃない）になりますから、ログだけ書き込んだら処理が続けられるようにします。ログが必要なシステムで、ログのINSERT とデータの UPDATE が終わるまで次に進まないシステムでは、大量アクセスになればそれだけ厳しくなる。
余談ですが、似たような処理で、例えば、ECサイトであれば、注文ボタンでログ（というか仮注文）を書いて確認画面に遷移、確定ボタンでログのIDと共に実際のテーブルに書き込む。という処理もありますね。1回のユーザアクションで2回書き込むのは厳しいですが、ページ遷移が挟まれば、コネクションを掴みっぱなしの処理とは違い、DBサーバにとって大変な時間稼ぎになります。
尤も、確定ボタン押下時のボトルネックは、大抵確認メールの方ですけど（苦笑）
個人的な感想
私が無課金ユーザ（初回入会ボーナスの500円分は使いましたが）だからかも知れませんが、「艦これ」って待ち時間ばっかりで、激しくサーバにアクセスしようとしてもできない。同時オンラインのユーザが10万いても、一人一人は、1時間に何回も更新してないと思うので、外から見る限りそんなにシビアとは思えないのです。
MySQL Clusterのベンチマーク http://www-jp.mysql.com/why-mysql/benchmarks/mysql-cluster/ を見る限り、2台のサーバで1秒間に100万回の UPDATE文を流せている。
もちろん、これは理論値に近いテストで、現実にはそのままのパフォーマンスは出ないでしょう。しかし、10万の同時ユーザが平均10秒に1回の更新（そんなにできる！？）が行われるとしても、1秒1万回です。
つまり、鎮守府を分けないで2台で組んだ最小のクラスタ構成でも、NoSQL の API を使っていれば理論上は100倍以上、現実的にはあと10倍ぐらいは対応できてもおかしくない。
それでも「艦これ」が、
同時ユーザ10万 ÷ 鎮守府の数
でトラブルるなら……。スタートラインのコネクション数のパンクの可能性も高いわけです。
「艦これ」のようなシステムであればアニメーションの間は、APサーバも、DBサーバも別の処理が可能です。それをシーケンシャルに処理していたら、いずれも待ち時間が発生し、パンクの可能性も、ユーザのストレスも高まることになるでしょう。
（私はイラチなので滅茶苦茶ストレスが溜まった……）何度か落とされたタイミングのイメージでは、シーケンシャルな処理にして、NoSQLではなく、UPDATE の SQL を流す最悪のパターンの可能性すらあるんじゃないかと……。
# 時間経過毎に資源が増えますが、まさか、その度にUPDATEを流すとか……。
# まさか……、そんな……設計にはなってないよね。
まあ、私には調べようがないけれど、例えば「艦これ」の対戦シーンなら、ユーザの1アクションに対して数回のUPDATE文を流すことになるハズです。ここで NoSQL(API) を使えるかどうかは非常に大きい。Oracle などの SQL をショートカットするような API がない場合は、この通信が1回で終わるように引数に詰め込んだストアドプロシージャにするべきでしょう。
いずれにしても、「艦これ」に限らず、似たようなソーシャル系のゲームであればアニメーションという非常に長い余裕を作ることができます。「艦これ」ユーザはそのアニメーションを楽しんでくれる訳です。ですから、そのユーザを飽きないように何種類かアニメーションを用意して、クライアントでランダム（レアも用意）にアニメを流して時間稼ぎして、非同期処理すれば DBサーバに対する流量調節は十分にできるでしょう。
私なら、「ここでx秒飽きさせないアニメーションをよろしく！」とか、厚かましく求めるけどね（苦笑）尤も、センスがないので「何をもって飽きさせない」かは、全く分からないのですが……。
当たり前の話が……
プログラムをやったことのない人は、パチンコでリーチが掛かって祈ったり、叩いたりする人達が結構います（苦笑）「艦これ」もパチンコと同じように、演出（アニメーション）の途中で抽選をしていると感じる人もいるかも知れないけれど……。
まあ、どのタイミングで抽選していても、祈ろうが、叩こうが関係ないですが（苦笑）
パチンコでも「艦これ」と同じように派手な演出がありますが、もちろん、演出のほぼ全て（最近は演出を追加するためのボタンがついてたり）がチェッカーに球が通った瞬間に決まっています。
「艦これ」もパチンコと同じで、「艦娘」の製造に20分掛かっても、クリックした瞬間（正確にはAPサーバに行ってからでしょうが）に何ができるかは確定しているハズです。戦闘画面では陣形を決定した瞬間に、「何が、どの順番に、どれだけのダメージを受けるか」（次のユーザアクションの）夜戦の選択に入るまでの全ての演出が決まっています。（違ったら腰抜かすほど驚くわ……）
それが分かっていて、「艦これ」の待ち時間に耐えられるというのは……。イラチの私には理解できません……。
「それを言っちゃぁ、お終めぇよ〜」ってことなんでしょうが……、もしかして、分かってない人の方が多いんじゃないかと……。
「艦これ」から、ソーシャル系のサーバ構成を考える
私は、ソーシャル系とは縁遠い仕事ばっかりしているのですが、そういう依頼も若干増えてきたので話題になっている「艦これ」をお盆にやってみた。
残念ながら、「艦これ」の魅力は分からなかった。しかし、ミッションを用意されると、「クリアーしたい」という欲求から意地になるのは、何となく理解できました。それより、同時に始めた「Clash of Clans」には嵌まりました。気になっていた「ゲームの中に如何に自然に課金システムを取り入れるか」という課題についても、個人的には「Clash of Clans」の方が上手に解決しているように思います。
「艦これ」は、同時アクセスが10万以上あって、何度かシステム障害があったとのこと（そりゃあるでしょうが……）。私の興味の方向性は、課金システムであったり、システム構成にあるので、「艦これ」のシステム障害の方が強い興味の対象になります（苦笑）
というわけで、「ソーシャル系で起こりがちな問題」というのを考えてみたい。
「艦これ」は既に辞めて、参加待ちの人に席を譲りましたので、記憶の範囲になってますし、あくまで想像ですのでご了承ください。
SQLを使うか、NoSQLを使うか
SIer系の技術者でも SQL が苦手な人が多いのですが、ソーシャル系の技術者ではもっと SQL が苦手な人が多いようです。そんな状況で、なぜ RDBMS を選択するのか私にはイマイチ理解できないのですが、「艦これ」に必要なデータは（画像、音声ファイルを除けば）非定型なものがなく、マスターデータは、攻略Wikiにほぼ再現されている程度です。整理すればエクセルでも事足りるレベルですので、確かに RDBMS を使った方が楽に開発できると思います。
噂によると、「艦これ」では、MySQL Cluster を使っているとのことです。MySQL Cluster は良くできていて、SQL と NoSQL（SQLをショートカットしたAPI）のいずれでも利用可能ですから、「艦これ」のシステムを考えれば、参照系はSQL、更新系はNoSQL(API)で使うのが妥当でしょう。
もし、更新系でNoSQL(API)を使用していなければ、かなり「痛い設計」ですが、有料オプション（のはず）
※ CGE(Carrier Grade Edition)に含まれていて、CGEは一部有料ですが、NoSQL(API)は無料のようです。
なので、大人の事情で NoSQL(API)を使ってない可能性もありますね。しかし、変更点は10人日程度あればできるでしょうから、当初、SQLを使っていたとしても、もう直している頃でしょう（ですよね！）
JOINを避ける？
MySQL Cluster を使うなら、これまで書いてきたように効率的に SQL を書かないと意味がない。JOIN を避けたりしたら返ってDBサーバの負荷が掛かります。
しかし、MySQL Cluster を使わないと考えて、「（お手製の）スケールアウトに備えて JOIN を禁止する」という話をよく聞きます。（例えばこんな記事 http://d.hatena.ne.jp/iad_otomamay/20110808/1312805917）
「艦これ」は直接関係ないのですが、「JOINの禁止」が本当に正しいか考えてみます。
結論から言うと、JOIN を避けスケールアウトしたところで DBサーバのパンクの可能性は減らないどころか、逆に増えることになります。
RDBMS は、大量のデータを処理することを目的に作られて、現在のハードウェア環境であれば CPU を使いきることはかなり難しく、メモリーが足りなくなることがほとんどです。特に、一つ一つの処理は単純で、大量のアクセスを捌くことが重要なソーシャル系のシステムでは、「メモリーを効率的に利用することだけを気にしていれば問題ない」と言い切っても過言ではありません。
そのメモリーの使い方ですが、データを毎回ハードディスクから取り出していては間に合わないので、メモリーの7〜8割をデータのキャッシュに割り当て、残りを作業エリアに割り当てるのが一般的でしょう。
残りの作業エリアがどのように利用されるかは、コネクションとセッションについて理解する必要があります。APサーバとDBサーバは、まず、コネクションを確立します。コネクションを確立する処理は非常に重く、処理の度に行うわけにはいかないので、通常、接続したままにして使い回します（コネクションプール）
図の様になり、DBサーバはコネクションの中にセッションを作り、セッションの中でクエリーを発行します。
クエリーを処理するためにセッション毎に利用するワークメモリーの大きさは、初期パラメータの Sort_Buffer_Size で指定します。（動的にも変更可能）
通常、Sort_Buffer_Size は 128KByte〜2MByteぐらいで指定します。
一連の処理で、別々にコミットしたい処理などのときにセッションを分けることがありますが、ソーシャル系のシステムなら、ほぼ、1コネクションに対して、1セッションで十分でしょう。しかし、同時アクセスが10万でその通りDBサーバで処理するなら、小さめの 128KByte の Sort_Buffer_Size を指定しても、
128KByte × 100,000コネクション（セッション） ＝ 12.8GByte の作業メモリーが必要になります。
MySQL の設計上の最大コネクション数は10万ですが、1つのコネクションにつき OS のスレッドを1つ必要とし、OS もスレッド毎にメモリーを確保しますので、現実的には DBサーバは10万もの同時アクセスはなかなかこなせません。ですから、APサーバがコネクションプールを作って、DBサーバに対する最大のアクセス数をコントロールするわけです。そのため、大量アクセス時のボトルネックは コネクション数か、それに対するメモリー量 となることがほとんどです。
※ DWH（Data Ware House）などのシステムは、同時接続ユーザ数が数名〜数十名となるので、コネクション数がボトルネックになることはまずなく、1つの処理でスワップするような大きな処理の場合は、分割が必要になります。
JOINを避けてDBサーバをスケールアウト
「艦これ」のようなシステムの場合、一人のユーザ情報が2KByteで100万ユーザいたとしても 2GByteですから、マスター系は一切スケールアウトは必要ありません。そのようなシステムでJOINを避けてスケールアウトしたとすると、図の様になるでしょう。
JOINをするような処理の場合、1処理が終わるまでの間、どちらのコネクションも解放できません（しません）から、スケールアウトしてサーバ数を増やしても、スケールアウトしなかったDBサーバのコネクション数を増やすことができず、スケールアウトする前と同じ状態のボトルネックになってしまう（Sort_Buffer_Size を小さくできれば話は別ですが……）3台にスケールアウトすれば、スケールアウトしないDBサーバへのコネクションプール数は、スケールアウトしたDBサーバへのコネクションプール数の3分の1にしなくてはならないのですが、全部、スケールアウト前のコネクションプール数にしたままにして、スケールアウトした方が不安定になるなんてことも、見たことがあるような、ないような……（苦笑）
JOINに限らず、APサーバで処理をすることによって遅くなるなら、DBサーバに対するコネクションを掴んだままになるため、結果的にDBサーバに負荷を掛けてしまうのです。
つまり、無駄に遅いだけで、ユーザにも、システムにも何も良いことはありません。
「複雑だから負荷が高い」と思うのは大きなお世話！
複雑だろうが、単純だろうが、シビアな要件になればなるほど、「速いことが正義」になります。
速ければ、たとえその瞬間の負荷が高くても、すぐにリソースを解放してくれるので問題ないし、複雑なことで負荷が限界を超えるなら、その処理でディスクソートが入るということになりますから、極端に遅くなるためすぐに分かります。（その処理が行われるセッションだけ、動的に Sort_Buffer_Size を大きくしても良いのですが、ご利用は計画的に！）
つまり、書いてはいけないほど複雑かどうかは、「メンテできるか」を除けば、「速いかどうか」で判断すればよい。「メンテできるか」は要するに要員のスキルの問題ですから、RDBMSを使いながら（APIを使わずに）SQLを避けて得られるメリットは、「下手糞に合わせる」以外にないということになる訳です。
くれぐれも、プロなら、「遅いけれどサーバの負荷は下がる」という世迷い言はいわないように！
感想
今回、20年ぶりぐらいに「艦これ」と「Clash of Clans」と2つのゲームをやってみました。
私はソーシャル系のシステムは未経験なので、実際、どれぐらいの負荷が掛かっているかは、外から見たシステムの状況から理論値で予想するしかありません。
「Clash of Clans」も、それなりに同時ユーザがいてそうで、システムの複雑さ、そのパフォーマンスと安定性をみると、Flashゲームというハンデを差し引いても、「Clash of Clans」の方が何倍も上ですね。
尤も、「Clash of Clans」は何らかの NoSQL（多分、MongoDBでしょう）を使っているはずですが（苦笑）
クライアント処理のアニメーションなんて、レスポンスが遅いことの隠れ蓑（スプラッシュ画面と同じ）ですからね。「艦これ」なら数秒は余裕がある。
最近のハードウェア環境で、「艦これ」の状態が良いときのレスポンスで良いなら、10万同時アクセスぐらい捌けそうなものなんですけどね……。
もちろん、「艦これ」がこんな幼稚な理由でトラブっているのではないと信じていますけれど、MySQL を使いながら SQL を避けたときに起きる挙動に似てるな〜。とは、個人的に思っています。（違うことを祈る！）
とにかく、RDBMS は SQL が最大のボトルネックになるのです。
SQLを使わないことでそのボトルネックを避けてるつもりでも、NoSQL の API を使わない限り、必ず最大のボトルネックの SQL を通ってしまいますから、RDBMS を使うなら、シビアなシステムになればなるほど、SQL を極めなければいけない。
年々、RDBMS を使いながら SQL を避ける人が増えていますが、SQL を使いきれば数倍〜数十倍のレスポンスが出せる場合がほとんどです。ということは、難しく考えなくても、同じハードウェア環境で数倍〜数十倍のアクセスに耐えられる。
実に単純なことなんですけどね……。
