サーバサイドエンジニアなら分かっておきたいPrepared文についての要点 #Database

疑問。Goの実装例を見ていて、以下のようなものがありました。

    var err error
    var res sql.Result
    query := "insert into test(text) values(?)"
    stmt, err := PrepareContext(ctx, query)
    if err != nil {
        return nil, err
    }

    res, err = stmt.ExecContext(ctx, text)
    if err != nil {
        return nil, err
    }

なぜ

func (qr *QuestionRepository) GetByID(id string) (*model.Question, error) {
    var q model.Question
    if err := qr.db.QueryRow(`select * from question where id = $1`, id).Scan(&q); err != nil {
        return nil, err
    }
    return &q, nil
}

このようにできるのにわざわざクエリをprepareしてexecuteするのかと、気になったので調べました。その理由はDBMSの内部の仕組みにも関わることでエンジニアなら知っておくべきだと思ったので記事にしたいと思います。

Prepareとは

クエリを実行するために必要な前処理を明示的に事前に行う処理。この前処理はコンパイルと呼ばれる(その内容については後述)。クエリをPrepareしたものはプリペアド文やprepared statementと呼ばれる。Prepared文は、データベースのセッションの持続時間内だけDBサーバ側で保持される。その後、同じクエリを実行する時にはPrepared文を使い回すことで、コンパイル処理に関する負荷をなくすことができる。

実際にPrepareするクエリは?や$1などを使って一部の値を未指定にする。未指定にしたままコンパイルをすることで、毎回異なるパラメータをDBMSに渡してクエリを実行することができる。ちなみに、どれほどのプリペアド文をキャッシュさせておくかはDB側で設定が可能である。

クエリが実行される時のDBMSの内部処理

クエリが実行される時、DBMSの内部では以下のような処理が行われている。

クエリをパース（文法チェック）して構文に問題がないかをチェックする
パースが済んだら、オプティマイザがカタログマネージャから統計情報を取得する
取得した統計情報をもとに、実行計画を作成する
実行計画に従ってデータにアクセスする

このように、クエリを実行するだけでも内部ではデータにアクセスするための準備をしていることがわかる。上記の1〜3までをSQL文のコンパイルと呼ぶ。
コンパイルされたクエリは実行するだけの状態にPrepareされたPrepared文としてDBMSに保存され、似たようなクエリを実行する時に再利用される。クエリのPrepareとはクエリが実際に実行されるまでの前処理を事前に前もって行っておくこととも言えるもので、precompileと表現されることもある。

用語補足

統計情報とは、テーブルやインデックスなどデータについてのデータ、つまりDBに関するメタデータのこと。

実行計画とは、データベース内のデータにアクセスする際に最も効率が良くなるようなデータアクセスの手順のこと。SQLが実行される時、DBMSの内部ではオプティマイザというDBMSの頭脳とでも言うべきものにより自動で実行計画が作られる。

統計情報と実行計画については以下の記事によくまとまっている。
実行計画？？統計情報？？って人へ

Prepared文の作成から実行の流れ

Prepared文を作成して使用する時の一連の流れは以下の通り
1. クエリをDBMSに送る。この時、パラメータを代入する部分は?(MySQL)や$1(Postgres)のようにして未指定のまま残しておく
2. DBMSがクエリをコンパイルし、出来上がったPrepared文をDBサーバのキャッシュに保存する
3. 同じクエリを実行するときはDBMSにパラメータを送り、実行する

GoでのPrepareから実行までの処理は以下のようになる。


func (qr *QuestionRepository) Save(ctx context.Context, q *model.Question) error {
    query := "INSERT INTO question(question_id, user_id, user_name, created_at, updated_at) values($1, $2, $3, $4, $5)"
    stmt, err := qr.db.PrepareContext(ctx, query)//クエリをDBMSに送り、コンパイルされる

    if err != nil {
        return err
    }
    questionId := 1
    createdAt := time.Now()
    updatedAt := time.Now()

    res, err := stmt.ExecContext(ctx, questionId, m.UserID, m.UserName, createdAt, updatedAt)//パラメータを渡し、クエリを実行する
    if err != nil {
        return err
    }
    return nil
}

Prepare文を作成するメリット

コンパイルのオーバーヘッドをなくすことができる
SQLインジェクションを防げる
毎回クエリを送信せずにパラメータだけを送信すればいいのでネットワークのトラフィックを軽減できる
バイナリプロトコルを使用するためCPUの使用が削減され、ネットワークの使用も押さえることができる。詳しくはこちら

特にコンパイルのオーバーヘッドをなくすことができるという点について、いくつもの条件があったり複数のテーブルを結合してデータを取得するなど実行計画が複雑であればあるほどパフォーマンスの向上は大きく、クエリのPrepare処理の効果は高くなる。

Prepared文の活用事例

例えば、この例のように一度プリペアド文を作成することでクエリの中の値だけを変えて繰り返し実行する際のパフォーマンス向上が見込める。

  try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO products VALUES (?, ?)")) {
        stmt.setString(1, "bike");
        stmt.setInt(2, 10900);
        stmt.executeUpdate();
        stmt.setString(1, "shoes");
        stmt.setInt(2, 7400);
        stmt.executeUpdate();
        stmt.setString(1, "phone");
        stmt.setInt(2, 29500);
        stmt.executeUpdate();
  }

引用：https://en.m.wikipedia.org/wiki/Prepared_statement　

Prepared文とコネクション

Prepared文の仕組み上、アプリケーションサーバー、DBMS間でパラメータのやりとりが発生している以上、Prepared文は基本的にコネクションに紐づいていることが分かる。

Postgresの例

PostgreSQLの公式ドキュメントを読む限り、PostgreSQLでは、Prepared文がDBMSにキャッシュされている時間はその時点でのDBのセッション持続時間内だけとなっているらしい。
Prepared文は一つのセッション内のみで有効であるということでもあり、DBを複数用意している場合、Prepared文はそれら複数のDB間で共有されることはない。よって、複数稼働している場合は一つ一つのDBサーバ内で個別にPrepared文を保持しておく必要があるということになる。

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the DEALLOCATE command.

Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g. if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.

Prepared文の注意点

注意点として3つ紹介する。

場合によっては普通にクエリを実行するよりもパフォーマンスが落ちる可能性がある
一度しか実行されない場合、結果として余分なラウンドトリップが発生し実行速度が落ちる
メモリを消費する

Prepared文の注意点の詳細

パフォーマンスが落ちる可能性がある

Prepare文を作成しても、パフォーマンスの向上が得られないばかりか普通にクエリを実行するよりもパフォーマンスが低下しすることがある。
これはPostgresの例だが、先に述べた通り、DBMSではコンパイルの段階で最も効率の良いデータアクセスのための実行計画を作成する。しかし、Prepared文を作成する場合、実行計画を作成する段階ではクエリが実行される際にパラメータとして渡される値が未指定となっている。そのため、値を指定してクエリを実行する場合と比べてパフォーマンスが落ちてしまうことがある。

一度しか実行されない場合、実行速度が落ちる

普通にクエリを実行するだけであればDBMSに一度クエリを送って結果を返してもらうだけで済むが、Prepared文を使うとまず事前にコンパイルしてもらうためにクエリを送って、その後もう一度再びDBMSにパラメータを送る必要があるため余分なラウンドトリップが発生する。そして、その結果普通にクエリを実行するよりも実行速度が落ちてしまう。

メモリを消費する

それだけでなく、コンパイルしたPrepared文をメモリにキャッシュするため、当然メモリを通常よりも多く消費する。こちらのFUJITSUのページによると、1文当たりのメモリ増加量は、約100Kbyteであるらしい。

このように、Prepare文にはどのような場合でもセキュリティ向上のメリットはあるものの、だからといって常に無条件で使えばいいというものではなく、場合に応じてその使用を判断する必要があることに注意が必要。

Prepare文を使うとどれくらい早くなるのか？

以下の記事でざっくりとベンチマークがとられていました。ちゃんと早くなっていることがわかります。

参考

http://variable.jp/2009/12/13/mysql-prepare%E3%80%80statement%E5%BE%A9%E7%BF%92%E3%81%A8%E7%A2%BA%E8%AA%8D/
https://www.sraoss.co.jp/PostgreSQL/Manual/document/9.1/html/sql-prepare.html
https://en.wikipedia.org/wiki/Prepared_statement
https://www.postgresql.org/docs/current/sql-prepare.html
https://www.sraoss.co.jp/PostgreSQL/Manual/document/9.1/html/sql-prepare.html
https://dev.mysql.com/
達人に学ぶDB設計徹底指南書