SQLer 生島勘富 のブログ

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

もうちょっと「艦これ」から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 変数 = 値;」とするのは古典的で格好は良くない。しかし、ネットワークを挟んでループさせたり、大量のデータを転送して処理するという、設計上の大問題(私的にはバグ)をやってしまう方が遙かに格好悪い。

 ストアドプロシージャの文法はどうしようもないが、設計上の問題は担当する技術者の問題に他ならない。自分の担当分野の問題は平気なのに、「文法のダサさが気になる」というのは、完全に間違ってるのです。