MySQLには一連のSQLを一つにまとめて登録・実行できるストアドプロシージャーとストアドファンクションがあります。複雑であったり、長いSQLであったりすると、事前に登録して実行することで、作業を効率よく進めることができます。使い方次第で効果的なストアドプロシージャーとストアドファンクションについてまとめました。
ストアドプロシージャーには複数のSQLをまとめて実行できます。また、名前をつけることができるので、関数のように扱うことができます。
機能としてストアドプロシージャーとストアドファンクションは、どちらも登録したSQLをまとめて実行する点では同様です。しかし、ストアドプロシージャーの実行には戻り値がありません。逆にストアドファンクションでは戻り値があります。
ストアドプロシージャーのメリットとしては、ストアドプロシージャー名をコールすることで、複雑なSQLも簡単に実行できる点です。しかし、便利さの反面、無暗に使うとデータベースの負荷を増大させることもあります。
ストアドプロシージャーの定義はcreate文によって実行され、beginからendまでに本体を記載します。ストアドプロシージャーの実行はcall文によって呼び出されます。
上述したようにストアドプロシージャーとストアドファンクションは、複数または一連のSQLをまとめて実行する点では似た機能といえます。
ストアドファンクションはプロシージャーと違い「戻り値」があり、実行するとテーブルにロックがかかりません。ストアドファンクションはプロシージャーより制限が多いといえますが、その分使い勝手は容易で、可読性の高いSQLとして使えます。
例えば、SQL文のなかに独自の関数として使うことができますので、特定の計算をSUMやCOUNTなどと同じように関数として処理できるのです。
ストアドプロシージャーが大きな処理を任せることができるのと比べると、ストアドファンクションでは使い勝手のよい独自関数として利用できます。
トリガーとは特殊なストアドプロシージャーで、レコードの追加、更新、削除などテーブルに対する操作の際にイベント時に実行されます。使い道としては、あるテーブルに更新されたとき、自動的にバックアップをとるとか、ログをとるとか、必ず行う自動処理として使うと便利です。
トリガーはCREATE TRIGGERで作成されます。また、トリガーを作成する際には設定するユーザにトリガー権限を与えておきます。
トリガーは便利な機能ですが、適切なシナリオを考えないと、不要な負荷を呼んでしまうこともあります。かえってパフォーマンスが低下することもあるので、よく設計を検討してください。
ストアドプロシージャーとストアドファンクション、そしてトリガーは一連の操作をまとめて実行できる、大変便利な機能です。適切な構成であればサーバ運用の際に高い効果が期待できます。一方で不用意な使い方をすると、全体に影響を及ぼすような負荷をまねくこともあります。使用方法と構成、データベースの設計をよく検討して使ってください。
ソフトエイジェンシー関連製品URL
https://www.softagency.co.jp/products/mysql
製品・サービスに関するご質問、お見積もり、お問い合わせなど、お気軽にご連絡ください。