tsucchi’s diary(元はてなダイアリー)

はてなダイアリー(d.hatena.ne.jp/tsucchi1022)から移行したものです

MySQL のストアドプロシージャの話

きっかけはこの記事。変数とストアドプロシージャについて - Yet Another Hackadelic

DeNA もストアド使ってるんだー!?」と、素直に驚きました。ストアドって、いい面もあると思うのですが、なんだかんだ2年くらい MySQL のストアド使ってきて嫌な面ばっかり見えたので、その話を書こうと思います。

この辺から始まる一連のtweetを見てた人は見なくてもいいかも。

なお、ウチは、ちょっと古めのバージョン(5.1.35 くらいだっけ?)を使っているので、もし「5.X以降ならそんなことないよ!」とかあれば教えていただければ幸いです。

1. あんまり早くない

ベンチマークはとっていないけど、「なんでコレがスローログ載るの」みたいなことがしばしばあります。件数の少ないマスタを一覧するだけ、とかそういうやつ。。。

2. スローログに載るのがストアドの呼び出しそのものになってしまう

普通に SQL 使った場合は SQL 単位でスローログにかかれるので、駄目さ加減がすぐわかるのですが、ストアドの場合ストアドの呼び出し(call SP_AAA('aaa', 'bbb')...みたいなかんじ)しかスローログに載りません。ストアドに複数のステートメントがある場合(ほとんどの場合そうでしょう)、どの SQL が悪いのか分からないので、致命的です。

「普通に SQL 出てくれれば explain かけるだけなのにー(怒)。つーか誰だ!コレ書いた奴!」と思って椅子を投げたくなったことは何度かあります。。。

これが本当に致命的で、「なんとかならないかなー」と思います。「新しいバージョンだと大丈夫」とか「○○って設定すれば SQL 単位でも見れるよ」とかあったらいいのになー。。。

3. コンパイルエラーがあんまり見つからない

テーブル名とかカラム名とか書き間違えても見つからない。コンパイルエラーになってほしいのだけど、実行時エラーになってしまう。困るのは主に開発時だけど、条件分岐してて、カラム名間違いしてる部分がある、とかだと最悪ですね。。。一応僕が書くときは条件の分は全部テストコード書いてるから、今のところ問題になったことはないですが。。。

あと最近、誰かが作った SQL モンスターをリファクタリングしてたときに、「コンパイラ任せ」ができなくて、泣きそうになりました。。。

4. テストが書きにくい

SQL をテストするものは基本的にないので、なんか別の手段を考える必要があります。僕は Perl からテストするツールを作ってそれでテストしています。データ周りとかでもめんどくさい話とかもあるのですが、それはもしかしたら YAPC::Asia で(話せたら。いろんな意味で。)少しだけ話すかもね。。。

軽くまとめ

1. と 3. は「普通のストアド(Oracleとか)だったら享受できそうなメリットが、MySQL だと無いよー」、程度なので、まあ別に大して困ってないのですが、やっぱりスローログの件は何とかならないかなー、と思います。

4.は SQL 直接書く場合と比べると DBD::Mock でのテストが書きにくい、っていう点はあるのですが、それ以外は仕方ない気がするので、何とかするしかないのでしょう。(めんどいけど、データ突っ込んでテストするしか無い的な意味で)

ストアドを使ったほうがいい場合

ストアドを使ったほうがいい場合もあります。開発者が未熟な場合とか DB をいろいろな部署に開放してたりする場合に、好き放題に SQL を投げられると困ります。where のない UPDATE 文投げつけられたり、意図しない DELETE を投げられたり、DELETE のつもりで TRUNCATE とか投げられると泣きそうになる、というかたぶん泣きます。

そういった場合は、テーブルに直接 Insert/Delete/Update させることを禁止して、ストアドからのみ更新を許す設定にしてあげると、だいぶ安心感がアップします。