DBサーバの負荷を軽減するためには

　複数ユーザが同時にボスキャラと対戦するというゲームがあったそうですが，SQLの採用を避けてORMで構築していたため，（⁠N＋1問題ほどではありませんが）図1左のように大量の（実際は図より多くの）SQLが実行されることとなったそうです。

　そのため，DBサーバが何度も障害を起こしダウンしてしまい，最終的には複数ユーザでの同時対戦をあきらめ疑似的な対戦に切り替えたとのことでした。同様の理由で，ほとんどの処理をクライアントで行うということが増えています。

　クライアントで処理を行うことには，「⁠ネットワークが一時的に切れてもゲームが途切れない」というメリットが，とくにスマホ向けのゲームにはありますが，同時対戦ができないということは大幅なユーザエクスペリエンスの低下になるのではないでしょうか。前回解説したとおり，DBサーバの負荷は，「⁠ORMで処理 ＞ SQLで処理 ＞ クライアントで処理」の順になります。しかし，SQLで処理する方法については，検討対象にすらなっていないようです。もちろん，ORMで処理していたものをSQLだけで処理するように変更すれば，「⁠DBサーバで障害は起きない」とは言い切れませんが，「⁠ユーザエクスペリエンスを下げてもクライアントで処理しよう」という判断をする前に，以下のようなSQLで処理する方法も検討してみませんか。

再現する仕様

　複数のユーザが同時にボスキャラに対して攻撃を行います。ユーザアクションに対してダメージポイントはAPサーバ（またはクライアント）で計算し，それをボスキャラのヒットポイントから除算して，ボスキャラのステータスを更新します。ステータスに麻痺があればダメージを1.2倍（小数点以下切り捨て）にします。このような仕様を実現するためのテーブルはリスト1になります。

CREATE TABLE boss_status(
    id        int AUTO_INCREMENT NOT NULL COMMENT 'ID'
  , boss_id   int NOT NULL DEFAULT 0      COMMENT 'ボスキャラID'
  , hit_point int NOT NULL DEFAULT 0      COMMENT 'ヒットポイント'
  , status_id int NOT NULL DEFAULT 0      COMMENT 'ステータス（ビット）'
-- …（中略）…
  , PRIMARY KEY CLUSTERED(id)
)   COMMENT = 'ボスステータス';

　状態異常時のステータスIDについては，ビットごとに設定すると良いでしょう。たとえば，次のように設定します。

• 1b(1) → スタン
• 10b(2) → 麻痺
• 100b(4) → 毒
• 1000b(8) → 火傷

実装案：単純な更新処理

　ボスキャラのステータスはビットで表現することで，ステータスの変更時にビットOR演算^注1（演算子は|）で更新すれば分岐の必要がありません。SQLはリスト2のようになります。

注1

ビットOR演算とは，2つの数値の同じ桁位置にあるビットのどちらか一方でも1の場合は1とし，それ以外の場合は0とした値になる演算処理のことです。

UPDATE boss_status
SET hit_point = hit_point  -
    CASE WHEN status_id & 2 = 2 THEN
        TRUNCATE(? * 1.2, 0)
    ELSE ? END
    , status_id = status_id | ?
WHERE ID = ?;

　特定の位置のビットが立っているかを確認するのは，ビットAND演算^注2（演算子は&）を使います。麻痺のステータスIDは10進数で2ですから，ボスキャラのステータスIDと2（麻痺）のビットAND演算の結果が2（麻痺）であれば，麻痺のビットが立っていると判断できるわけです。

注2

ビットAND演算とは，2つの数値の同じ桁位置にあるビットの両方が1の場合は1とし，それ以外の場合は0とした値になる演算処理のことです。

実装案：ストアドファンクション，ストアドプロシージャ

　ストアドファンクションとは，単体では機能せず，SQL文内で利用可能で1つの型（テーブル型のものもあります）を返すものを言い，ストアドプロシージャとは，単体のSQL文として機能する一連の処理のことを言います。

　実際のゲームのバトルはもっと複雑なのでしょうが，CASE式をネストすれば1つのSQLで処理することは基本的にできます。しかし，リスト2のCASE式でもかなり見にくいですが，それをさらに何重にもネストすることは好ましくありませんから，そのような処理が必要なときはリスト3のようにストアドファンクションにします。本稿では使っていませんが，ストアドファンクション内でランダム関数を使うことももちろんできます。

DELIMITER $$
CREATE FUNCTION get_hit_point(
      p_status_id int     -- ボスの現在のステータスID
    , p_hit_point int     -- ボスの現在のヒットポイント
    , p_given_status int  -- 与えられたステータス
    , p_given_damage int  -- 与えられたダメージ
) RETURNS int DETERMINISTIC
BEGIN
    DECLARE v_Ratio float(3, 2);
    SET v_Ratio := 1;
    IF (p_status_id | p_given_status) & 2 = 2 THEN  -- 2は麻痺
        SET v_Ratio := 1.2;
    END IF;
    RETURN p_hit_point - TRUNCATE(p_given_damage * v_Ratio, 0);
END;
$$

　ただし，MySQLのストアドファンクション（プロシージャ）はプリコンパイルされませんから，CASE式のままのほうが高速に処理できます。つまり，ストアドファンクションは速度と読み易さのトレードオフの関係にあります。さらに速度を追求したければ，UDF（User Defined Function）という方法があります。具体的にはCかC++でファンクションを作り，MySQLに登録するというものです。残念ながら，これもAmazon RDSでは利用できません。

　さらに，図1右の一連の処理をストアドプロシージャにしたものがリスト4で，APサーバのSQLはリスト5のようにストアドプロシージャを呼び出すだけで，ボスのステータスを更新しています。

DELIMITER $$
CREATE PROCEDURE set_damage(
      p_id           int  -- ボスステータスID
    , p_given_status int  -- 与えられたステータス
    , p_given_damage int  -- 与えられたダメージ
)
BEGIN
    UPDATE boss_status
    SET
          hit_point = get_hit_point(  -- 作成したストアドファンクション
              status_id, hit_point  -- boss_statusテーブルのカラム
            , p_given_status, p_given_damage  -- パラメータ
           )
         , status_id = status_id | p_given_status
    WHERE ID = p_id;

-- ログなどの処理は省略

-- 現在の状態を返す
    SELECT * FROM boss_status WHERE ID = p_id;
END;
$$
DELIMITER ;

CALL set_damage(?, ?, ?);

　これで，ログを追記し，最新の状態を取得するところまで，一連の処理を一気に実行できます。図1左のように，ORMではSQLを実行するたびに非常に遅いネットワーク処理が必要になりますが，ストアドプロシージャにすることで遅いネットワークの利用を最小限にできるため，システム全体の負荷を大幅に下げることができます。

　ストアドプロシージャは，RPC（Remote Procedure Call）やAPIの一種と呼んで良いでしょう。

　実際の処理を作ると，ストアドファンクションの中には多数のIF文（CASE式も同じ）が入ることになるでしょう。それを見て，なんとなく「複雑だから重そう」と感じる読者がいらっしゃるかもしれませんが，それは勘違いです。第3回で試みたように実行計画を確認して，手続き型のソースに直して考えてみてください。ファンクションの多数のIF文は，第4回の図2には書き表せられないほど小さな誤差程度のものです。APサーバとの通信回数や，SQLの実行回数を1回でも減らせるなら，誤差を気にする必要はありません。

ソースが分散する？

　これらの実装案では，ソースが分散するという批判があるとは思います。現在取られている開発手法では，本来，SQLで行うべき内容をORMを使うことでAPサーバに1本化しているため一元管理できています。筆者は，この「ソースが分散するからダメ（一元管理したい⁠）⁠」という発想がたいへんな弊害だと考えています。

　というのも，一元管理をするために，「⁠ORMを使ってまったく異なる指向の2つの言語を密結合にしてしまっている」とも言えるわけです。SQLに慣れている筆者でも，発想が異なるオブジェクト指向言語とSQL（集合指向）を同時に考えなくてはならない現在の開発手法は苦痛ですから，開発担当者に発想が違うSQLを意識して書きなさいというのは，いささか無理があると考えています。今は理解していたとしても，同時に考える必要があればオブジェクト指向言語に流されて（逃げて）しまうもので，時間が経って担当者が代われば，さらにSQLへの意識が薄れるということが，この20年で何度も起きています。

　しかし，ストアドプロシージャをRPCやAPIのように使うことで，オブジェクト指向言語とSQLを完全に疎結合（オブジェクト指向言語側に入るのはリスト5だけ）にすることが可能になります。つまり，担当者や開発フェーズを分けることができ，それぞれ専門性を発揮できるようになるわけです。

　「⁠ソースが分散するから，まったく違う2つの言語を密結合にする⁠」⁠，「⁠違う言語は疎結合にすべき，当然，ソースも分かれる」という2つの考え方があります。SQL以外の言語のとき前者を選ぶ人はいませんし，仮に選べば猛烈に批判されます。しかし，SQLになると前者が選択されてしまいます。後者を主張する筆者が極端に見えるのは，SQLに対する苦手意識が影響しているだけではないでしょうか？