概要
PreparedStatement はSQLを実行する際に「SQL文の構造」と「パラメータ」を分離して扱える仕組みです。
それによって
- パフォーマンスの向上
- セキュリティの向上
- コードの可読性・保守性の向上
を図ることが可能です。
今回はその理由を説明します。
PreparedStatementのメリット
例えばMySQLの場合、通常のStatementでは、
SELECT * FROM users WHERE email = 'alice@example.com'; SELECT * FROM users WHERE email = 'bob@example.com';
であるのに対し、PreparedStatementでは次のように使います。
-- 1. 準備 (Prepare) PREPARE stmt FROM 'SELECT * FROM users WHERE email = ?'; -- 2. パラメータをセットして実行 SET @email = 'alice@example.com'; EXECUTE stmt USING @email; SET @email = 'bob@example.com'; EXECUTE stmt USING @email; -- 3. 不要になったら解放 DEALLOCATE PREPARE stmt;
こうすることでどんなメリットがあるかを紹介します。
セキュリティの向上
例えば次のようにログインのSQLがあったとして、正常系は次のようになります。
| パラメータ | 値 |
|---|---|
alice@example.com |
|
| password | s3cr3t |
SELECT id, email FROM users WHERE email = 'alice@example.com' AND password = 's3cr3t';
Statement
しかしSQLインジェクション脆弱性がある場合は次のようなリクエストが投げられた際に
| パラメータ | 値 |
|---|---|
alice@example.com |
|
| password | ' OR '1'='1 |
SELECT id, email FROM users WHERE email = 'alice@example.com' AND password = '' OR '1'='1';
このように空文字に置換され、OR '1'='1' が常に真なため全件取得や不正ログインが可能になります。
PreparedStatement
しかしPreparedStatementの場合、
PREPARE login_stmt FROM 'SELECT id, email FROM users WHERE email = ? AND password = ?'; SET @p1 = 'alice@example.com'; SET @p2 = ''' OR ''1''=''1'; EXECUTE login_stmt USING @p1, @p2;
と、あくまで「文字列」として扱われるため、条件は真になりません。
コードの可読性・保守性の向上
Statement
Statementの場合、
String sql = "SELECT * FROM users WHERE email = '" + email + "' AND status = '" + status + "'"; Statement stmt = conn.createStatement(); stmt.executeQuery(sql);
このようにどこがSQL構造で、どこが値なのかが混ざっていて読みにくいですし、値の数が増えるほど + 連結が煩雑になります。なので拡張性も低いです。
PreparedStatement
一方PreparedStatementの場合、
String sql = "SELECT * FROM users WHERE email = ? AND status = ?"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, email); pstmt.setString(2, status);
SQL構造が固定で読みやすく、値の部分がメソッド呼び出しで明示的になります。
パフォーマンス向上
とはいえ今はクエリビルダーのSDK（ORM含む）を使うことが一般的なため、PreparedStatementを使わずとも先ほどのSQLインジェクションや可読性の問題は解決できます。
しかしパフォーマンス面はサーバ側の仕組みに乗っかる必要があります。
なぜパフォーマンスが向上するかは、処理フローを理解すると分かります。
一般的な実行フロー
通常のStatementは下図のように、
- Parse
- Optimize
- Execute
のステップを踏みます。
PreparedStatementの場合
PreparedStatementの場合は、まずPREPAERで実行計画（Execution Plan）までを保存します。
その後EXECUTEで実行をしますが、その際にキャッシュした実行計画を利用します。
この図の比較からも分かるように、通常のStatementでは毎回Parse, Optimizeしていた部分をスキップすることが可能になるので、パラメータ部の値のみが変化するSQLが繰り返し実行されるケースではパフォーマンスが向上します。
パフォーマンス検証
では実際に効果を試してみます。
- 単一メールアドレスでStatement
- 様々なメールアドレスでStatement
- 単一メールアドレスでPreparedStatement
- 様々なメールアドレスでPreparedStatement
というベンチパターンです。
コード
こんな感じでまず5万件ほどのデータを用意します。
func setupTable(db *sql.DB, n int) { _, _ = db.Exec(`DROP TABLE IF EXISTS users`) _, _ = db.Exec(`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, email TEXT, status TEXT)`) stmt, _ := db.Prepare(`INSERT INTO users (name, email, status) VALUES ($1, $2, $3)`) defer stmt.Close() for i := 0; i < n; i++ { _, _ = stmt.Exec( fmt.Sprintf("User%d", i), fmt.Sprintf("user%d@example.com", i), []string{"active", "disabled"}[i%2], ) } }
indexも張っておきます。
Statement
Statementではこのようにテストを用意します。
func BenchmarkSelectVariousWithQuery(b *testing.B) { db := connectTestDB(b) defer db.Close() b.ResetTimer() for i := 0; i < b.N; i++ { // email を変化させる email := fmt.Sprintf("user%d@example.com", i%numRecords) rows, err := db.Query(`SELECT id, name FROM users WHERE email = $1 AND status = 'active'`, email) if err != nil { b.Fatal(err) } var id int var name string for rows.Next() { rows.Scan(&id, &name) } rows.Close() } }
PreparedStatement
PreparedStatementでは
func BenchmarkSelectVariousWithPrepared(b *testing.B) { db := connectTestDB(b) defer db.Close() stmt, err := db.Prepare(`SELECT id, name FROM users WHERE email = $1 AND status = 'active'`) if err != nil { b.Fatal(err) } defer stmt.Close() b.ResetTimer() for i := 0; i < b.N; i++ { // email を変化させる email := fmt.Sprintf("user%d@example.com", i%numRecords) rows, err := stmt.Query(email) if err != nil { b.Fatal(err) } var id int var name string for rows.Next() { rows.Scan(&id, &name) } rows.Close() } }
動作確認
$ make bench go test -bench . -benchtime 5s -benchmem goos: darwin goarch: arm64 pkg: github.com/jun06t/go-sample/preparedstatement cpu: Apple M1 Max BenchmarkSelectSingleWithQuery-10 18319 332106 ns/op 833 B/op 23 allocs/op BenchmarkSelectVariousWithQuery-10 16330 374037 ns/op 868 B/op 24 allocs/op BenchmarkSelectSingleWithPrepared-10 33865 169847 ns/op 592 B/op 17 allocs/op BenchmarkSelectVariousWithPrepared-10 38262 184316 ns/op 627 B/op 18 allocs/op PASS
このようにParseやOptimizeが省かれていることで、倍以上の速度が出ました。
その他
サンプルコード
今回のサンプルコードはこちらです。
常にPreparedStatementを使った方が良い？
いえ、以下のようなケースではむしろ遅くなることがあります。
ロジックでリクエスト毎にPREPAREしている
PreparedStatementの場合、ステートメントキャッシュを使えなければリクエストは２回発生しますし、ParseやOptimizeも実行されるのでオーバーヘッドが生まれます。
キャッシュ自体はサーバに保持されますが、そのキャッシュが使えるかどうかのハンドリングは、pgx/pgxpoolなどのSDKではクライアント側でステートメントキャッシュがあり、同じSQLなら透過的に再利用してくれます。
クエリビルダーでバインド変数以外のSQLも動的にステートメントを作るケース
例えばSortの条件やOrder、Where句をプログラミング上で動的に組み替えるロジックがある場合です。
この場合はそのバリエーションにも依りますが、動的に変わることで再利用されない（一度しか実行されない）可能性があります。
そうなるとPREPARE 文のオーバーヘッドだけ増えます。
PgBouncerなどProxy型コネクションプーリングと一緒に使えない
MySQLやPostgreSQLのPreparedStatement はセッション単位です。
なのでそのコネクションが閉じられたりリセットされる使えなくなります。
クライアントサイドコネクションプーリングであれば、トランザクション終了時にプールに戻してもPreparedStatementは破棄されません。なので再利用が可能です。
一方でProxy型のコネクションプーリングの場合、少数のコネクションを多数のクライアントで使うため、トランザクション終了でプールに戻すときにセッションをリセット（＝PreparedStatementを破棄）します。 なので再利用ができません。
プランの柔軟性が下がる場合がある
PostgreSQLはバインド値の多様性が高いとgeneric planへ切替えることがあります。 特定のパラメータに最適化したプランが選ばれず、かえって遅くなることがあります。
PostgreSQLのプリペアドステートメント、6回目で実行計画が変わる件についての備忘録 #preparedStatement - Qiita
例：ある値なら index を使う方が速いのに、全体平均のプランだと sequential scan を選んでしまう
まとめ
PreparedStatement のメリットと、なぜそれが実現できるかを説明しました。
また、Webアプリケーションでは適さないケースもあるため、実際に比較検証することで採用するかどうかを選ぶのが良いでしょう。
