2018/7/14 全面改訂
====================================================================
0. はじめに
データベースのバインド変数のメリットについて以下のとおり、お話ししてきました。
①はじめに
http://qiita.com/zd6ir7/items/499040a8e76f9c2f7aba
②メリット：SQLインジェクションの防止
http://qiita.com/zd6ir7/items/14cad311e017ade98f6c
今回は3回シリーズの最終回ということで、メリットその2を述べたいと思います。なお、ここではDBMSをOracle Databaseをメインに取り上げます。
引き続き、これまでの投稿で使ってきた以下のUSERTABLEを使います。
| id | passwrod | username | |
|---|---|---|---|
| 22222 | password2 | ルフィー | chopperlovesluffy@pirates.com |
| 55555 | password5 | 桜木花道 | sakurahana@vvvyyy.go.jp |
| 11111 | password1 | 剣桃太郎 | momo_tsurugi@jump.otoko.co.jp |
| 33333 | password3 | きんにくすぐる | oreha@muscleman.net |
1. Oracle Databaseの内部処理イメージ
メリットを一言で言うとパフォーマンスの改善が見込まれることです。
なぜパフォーマンスの改善が見込まれるのか？を述べる前に、Oracle Databaseの内部処理について語らなければいけません。SQLが発行されると、Oracle Databaseは受け取ったSQLが「解析済み」かをチェックします。「解析済み」であればそのまま実行し、そうでなければ「解析処理」を行います。この「解析処理」というのは、簡単に言うと、どうやったら最短の時間でSQLを実行できるかの計算をして、その結果を「データ」として作成することを指します。Oracle Databaseは「データ」をもとにSQLを実行します(※1)。
以下に簡単なイメージを添付しましたので、それをもとに理解を深めてもらえればと思います。
ここで言いたいのは、Oracle Databaseが受け取ったSQLが解析済であれば、そのまま実行に移るだけなのでパフォーマンスは良く、そうでなければ解析処理が入るためその分パフォーマンスが落ちうる、ということです。
2. バインド変数を使わなかった場合
これを踏まえて、バインド変数を使わなかった場合と使った場合で性能面でどのように違ってくるかを解説します。
・Oracle Databaseは立ち上がったばかり。SQLは1つも受け取っていない。
・バインド変数を宣言しない。
という前提条件のもと、以下2つのSQLを見てください。2つのSQLの違いは単にIDとPASSWORDの値が違うだけです。
1．SELECT * FROM USERTABLE WHERE ID='XXX' AND PASSWORD='YYY';
2．SELECT * FROM USERTABLE WHERE ID='ZZZ' AND PASSWORD='AAA';
1．のSQLが発行されます。そうすると1．は解析済ではないため、「解析済SQLデータ」を作成する処理を行ってSQLを実行します。
次に2．のSQLを実行します。そうなると1．で解析済なので「解析済SQLデータ」を作成する処理はスキップされてそのまま実行されるのでは？と考えるかもしれません。違います。バインド変数を宣言しない状況では、2つのSQLへ別々のものとみなされます。従って2．のSQLも「解析済SQLデータ」を作成する処理を行ってから実行されます。
このように値が違うだけのSQLを発行しているのに、都度解析処理を行うのでパフォーマンスを落とすおそれがあります。
3. バインド変数を使った場合
では、バインド変数を宣言した場合はどうなりますでしょうか？これについても
・Oracle Databaseは立ち上がったばかり。SQLは1つも受け取っていない。
・バインド変数を宣言する。
という前提条件のもと、上記2つのSQLが順に発行されたケースを考えます。1．のSQLが発行されます。同様にそうすると1．は解析済ではないため、「解析済SQLデータ」を作成する処理を行ってSQLを実行します。イメージは以下のとおりです。
次に2．のSQLを実行します。そうなると1．で解析済なので「解析済SQLデータ」を作成する処理はスキップされてそのまま実行されます。イメージは以下のとおりです。
よって、2つのSQLが別々と見なされることはありません。「解析済SQLデータ」を使ってSQLが実行されるためパフォーマンスの改善が見込まれます(※2)(※3)。
4. おわりに
いかがだったでしょうか？バインド変数を宣言することにより、セキュリティ対策にもなり、パフォーマンス改善にもつながる、ということが少しでもイメージいただけたのではないかと思います。
ここまで読んでくださりありがとうございます。
脚注
(※1)
SQLが実行されるまでの詳しい仕組みは、以下スライドのP12～15に掲載されています。
http://www.oracle.com/jp/corporate/branch/20151015-optimizer-2348097-ja.pdf
また、小生の以下の投稿にも解説していますので、よければご参照ください。
「Oracle Databaseの「実行計画」についてわかりやすく語ってみた」
(※2)
当然ながら「SELECT * FROM USERTABLE」等の、上記と異なるSQLが発行されれば、新しく解析処理が行われます。
(※3)
本記事では、「パフォーマンスの改善」が「見込まれる」と断定的な書き方はしていません。以下URLにバインド変数を使うことによるOracle Databaseの詳細な解説があります。同解説のとおり、バインド変数は必ずしも万能とは限りません。ケースによっては見極めも必要になる点留意いただきたいと思います。
http://www.oracle.com/technetwork/jp/database/articles/shibacho/index-2405696-ja.html