mysqlのストアドプロシージャとEXECUTEを使ってみた話

ん〜、単純にはいかない複雑処理
しかし全ユーザーデータなめるし速度が必要...
そういえばストアドとかいう機能があったなぁ...
 

※少々専門的な話になるのでご了承ください

システムエンジニア系のお話に興味がある方はどうぞ
 
 
私、LAMP環境で仕事してるんですがDBがテーブル名でシャーディングしているちょっと不思議な作りになっていまして。

何かしらのデータを触るときはシャーディングしてる各テーブル名でアクセスしなければならず、

小分けにするなりUNIONするなり面倒なクエリを発行しないといけないんですよね。

となれば、自動化できたら楽じゃない!?

ということで、やってみました。

ストアドプロシージャなる機能とEXECUTEを使えばできそうだなぁと。

まずストアドプロシージャとはなんぞや？と思ったのですが、

調べてみて直感的に思ったのが「mysqlで作れる関数」でした(厳密には違うかも)。

事前に処理内容を記録しておいて、あとで実行できるんですね。

これの引数が使えるというのがミソで、

同じストアドプロシージャを用いて動的で柔軟なクエリを発行できます！

そして直接Mysqlサーバー上で実行するので動作が速いんですね。

これは覚えておきたいテクニック。

今回の目的としては、

シャーディングしてる各テーブルに同じクエリを発行することです。

実はもっと複雑な処理をしたい時に威力を発揮するのですが

(仕事では複雑な処理のために使いました)テストケースとして

以下のような形で紹介しておきます。

ユーザーテーブルが10つあり、(user_table0〜user_table10)
特定のカラムを特定の条件(単純なwhere句)でSELECTするプロシージャを作り、

実行する

テスト用テーブルはこんな感じの想定で
user_data(user_id, user_name, address, favorite)

 
以下テストクエリ

-- CREATE PROCEDURE内でデリミタ";"を用いるため、一時的にデリミタを"//"に変更します
DELIMITER //

-- ここで実行するクエリをストアドプロシージャとして作成しておく
CREATE PROCEDURE loop_select(IN column VARCHAR(32), IN wcolumn VARCHAR(32), IN wvalue VARCHAR(32))
BEGIN
    DECLARE sh_cnt INT DEFAULT 0; -- シャーディングのカウンタ
    WHILE sh_cnt < 10 DO
        -- 実行するクエリを文字列で変数にセット
        SET @query_str = CONCAT(
            "SELECT user_id, ", column,
            " FROM user_table", sh_cnt,
            " WHERE ", wcolumn, " = ", wvalue, ";");

        PREPARE stmtc from @query_str; -- クエリ文字列から実行準備
        EXECUTE stmtc; -- 実行
        DEALLOCATE PREPARE stmtc; -- 実行したので開放
        SET sh_cnt = sh_cnt + 1;
    END WHILE;
END
//

-- CREATE PROCEDURE完了したのでデリミタ戻し
DELIMITER ;

-- 実際の実行部分
-- 住所が東京のユーザー名を調べる
CALL loop_select("user_name", "address", "東京");
-- 好物がりんごのユーザーの住所を調べる
CALL loop_select("address", "favorite", "りんご");

-- プロシージャを削除
DROP PROCEDURE loop_select;

こんな感じで、テーブル名やカラム名を動的に入力できるので、

うまく使えば色々捗るのです。

備忘録程度ですが、もしや誰かの助けになればと。

 
はてな記法使ってみたけど若干見た目が変?