Posted inMySQL

MySQLのStored ProcedureとFunctionの使い方：ビジネスロジックの最適化とパフォーマンス向上

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

なぜロジックはデータベース側で実行すべきなのか？

ECサイトを構築していた頃、自分がやらかしたミスがある。月次売上を計算するたびに、バックエンドのコードがordersテーブル全体を引っ張ってきて処理していたのだ。テーブルが1000万行を超えた頃、ダッシュボードの読み込みに8秒近くかかるようになった——ユーザーはとっくに別のタブを開いていた。

解決策はサーバーのスペックアップではなかった。Stored ProcedureとFunctionを使えば、複雑なSQL処理の連鎖を一つの名前にまとめられる。アプリから10本のクエリを個別に送る代わりに、一度呼び出すだけ——データベースが内部で処理し、最終結果だけを返してくれる。

クイックスタート：5分で最初のProcedureを作る

最初の一行を書く前に、DELIMITERキーワードが必要な理由を理解しておこう。MySQLはデフォルトで;をコマンドの終端として扱う。Procedureの中には;が多数登場するため、//に一時変更してMySQLが途中で処理しないようにする。

特定の顧客の注文合計金額を計算するプロシージャの例：

DELIMITER //

CREATE PROCEDURE GetCustomerTotal(IN customerId INT, OUT totalAmount DECIMAL(10,2))
BEGIN
    SELECT SUM(amount) INTO totalAmount 
    FROM orders 
    WHERE customer_id = customerId;
END //

DELIMITER ;

実行してみよう：

CALL GetCustomerTotal(1, @total);
SELECT @total;

これだけだ。ロジックはカプセル化された——DBに接続するどんなアプリからでも、何度でも呼び出せる。

ProcedureとFunction：どちらを選ぶべきか？

これは初心者がよく迷うポイントだ。一番わかりやすい見分け方：

  • Function（関数）：値を1つだけ返す。SELECTの中で直接使える。例：SELECT format_price(price) FROM products;
  • Stored Procedure（ストアドプロシージャ）：複数の結果セットを返したり、何も返さなかったりできる。INSERT・一括UPDATE・複数ステップのトランザクションなど複雑なロジックが得意。SELECTの中では使えない。

実務でのFunctionの使用例

自分のプロジェクトではVAT計算によくFunctionを使う——各クエリに直接数式を埋め込むよりずっとスッキリする：

DELIMITER //

CREATE FUNCTION GetVatPrice(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 1.1;
END //

DELIMITER ;

クエリの中でそのまま使える：SELECT name, GetVatPrice(price) as price_with_vat FROM products;

複雑なロジックとエラーハンドリング

実務では単純なSELECTだけでは済まない。20万件のユーザーデータを新システムへ移行する際、エラーのある行はスキップしながら1件ずつ処理する必要があった——バッチ全体をロールバックさせるわけにはいかなかった。そのとき助けてくれたのがDECLARE CONTINUE HANDLERだ：

DELIMITER //

CREATE PROCEDURE ProcessDailyInvoices()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE invoiceId INT;
    
    DECLARE cur CURSOR FOR SELECT id FROM invoices WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO invoiceId;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE invoices SET status = 'processed', processed_at = NOW() WHERE id = invoiceId;
        
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

重要な注意点：カーソルは1行ずつ処理するためリソースを相当消費する。UPDATE ... WHERE status = 'pending'という一文で解決できる問題なら、素のSQLを使うべきだ——カーソルのループより数十倍速い。

実務経験から学んだ4つの原則

1. ラウンドトリップをまとめてレイテンシを削減する

アプリがシンガポール、DBが東京——クエリ1本あたり約70msのネットワークレイテンシがかかる。5本を個別に実行すると、ネットワークだけで350msを無駄にする。1つのProcedureにまとめれば、それが70msになる。この方法でとあるAPIのレスポンスタイムを1.2秒から300msに改善した——サーバー増強もコード全体のリファクタリングもなしで。

2. アクセス権限をより細かくコントロールできる

特定のProcedureのEXECUTE権限だけをユーザーに付与し、テーブルへの直接SELECTやDELETEは与えないようにできる。さらに、ProcedureのパラメータはMySQLがSQLステートメントとは切り離してバインドするため、パラメータ経由のSQLインジェクションはほぼ完全に防げる。

3. 複雑なビジネスロジックをDBに詰め込まない

代理店の手数料計算ロジックをまるごとProcedureに入れたことがある——300行のSQL、ネストした条件分岐、ユニットテストなし。小さなバグ1つのデバッグに丸一日かかった。PHP/JavaのコードにはIDE、デバッガー、テストスイートがある。MySQLのProcedureにはSELECT文で値を画面に出力するくらいしかない。得た教訓：DBに落とすのは、データと密接に結びついたもの——集計や変換だけにして、ビジネスルール全体は持ち込まない。

4. Procedureをバージョン管理に入れる

バックエンドのコードはGitで管理されている。ProcedureはDBの中に住んでいる。チームのルール：すべてのProcedureには対応する.sqlファイルをリポジトリに置く。ファイル名はsp_procedure_name_v1.sql形式で。MySQL Workbenchで直接編集してそのままにしない——1か月後には何を変更したか誰も覚えていない。

Stored Procedureが本当に有効なのはどんな場面か？

現実的な答えはこうだ：明確なボトルネックがあるとき。多数のラウンドトリップによる高レイテンシ、データと密接に結びついた計算ロジック、テーブルへの直接アクセスを制限したい場合——そういった場面でProcedureは真価を発揮する。適切に使えば、システムはシンプルかつ高速になる。使い方を誤れば、自分しか運用方法を知らない巨大なDBの出来上がりだ。

Share:
Tags: