パフォーマンスの良いSQLを書く上で最低限考える事

作成日時：2025-06-20
更新日時：2025-06-20

パフォーマンスの良いSQLを書く上で最低限考える事。

最少・最少・最短・最速の原則
SQLを書いたら実行計画をとる

単語

記事中にある単語は次の意味を持つ。

AP：アプリケーションサーバ
DB：データベースサーバ

1.最少・最少・最短・最速の原則

SQLを書くときはパフォーマンスを上げるため、下記の原則を順守する。

DBから取得するデータ量は最少でなければならない
DBにSQLを発行する回数は最少でなければならない
トランザクションは最短でなければならない
SQLそのものが最速でなければならない

1-1.DBから取得するデータ量は最少でなければならない

1KBのデータと1GBのデータ、全量取得する時間が速いのはどちらか。無論、前者である。
故にパフォーマンスを向上させるため、SQLで取得するデータ量は最少にしなければならない。
必要最小限を取る。

具体的には下記の対応を行う。

SELECT句では必要最小限のカラムを指定する。
WHEREで絞る。
アプリケーション側で集約や計算をするのではなく、DB側で演算を行い取得するデータ量を減らす。
- ただし、ビジネスロジックをSQLに含めると、SQLが複雑になるため推奨されない。可読性の低下を招く。
- 計算量またはデータ量が大きすぎるため、DB上で計算すべき場合に限る。
INSERT-SELECT
- SELECTした結果を直接INSERTするやつ。
- 例えば、あるテーブルのデータを加工した結果を、別テーブルに登録する場合。
- 「APで対象データを取得⇒加工して登録」となると、大量のデータを一旦取得しなければならないことになる。
- 単純な加工であれば、INSERT-SELECTとCASEを使用して、DB内で取得/加工/登録を済ませる。
参考ワード
- GROUP BY
- CTE、または共通テーブル式(WITH)
- WINDOW関数

この原則を守らない場合、パフォーマンスの低下とネットワークコストの増大を招く。
特にクラウドにおいては、クラウドのコスト増大に繋がりかねない。

1-2.DBにSQLを発行する回数は最少でなければならない

例えば、100個の荷物をA地点からB地点に運ぶとする。
1個ずつ運んで100往復するのと、100個まとめて運んで1往復するのはどちらが速いか。

AP-DB間には物理的距離/各種ネットワーク機器が存在する。
そこにオーバーヘッドが生じるため、やり取りする回数は最少の方が良い。

100件のレコードを登録するために、100回INSERT文を発行するなどしてはならない。

参考ワード
- バルクインサート
- UPSERT
- INSERT-SELECT

1-3.トランザクションは最短でなければならない

仮にロックをした場合、他トランザクションはそのトランザクションが終わるまで”待ち”が発生しうる。
また、デッドロックが発生しかねない。
故にトランザクションが長いとパフォーマンスの低下に繋がるかねない。
他トランザクションと処理が被った場合、データ不整合の可能性も高まる。

1-4.SQLそのものが最速でなければならない

表題通り。
SQLそのものを速くすべきである。

どう書けば速くなるかは本記事では記載しないが、
いま書いたSQLが十分早いかどうかは、実行計画を確認する。

参照：2.SQLを書いたら実行計画をとる

1-5.原則を無視してもよい場合

これらの原則は必ずしも守らなければならない、というものではない。
**納得できる理由があるならば、**守らなくてもよい。
守らなければならないかどうかは、要件やドメイン、背景/文脈に依存する。
「納得できる理由」は下記など。

「1.DBから取得するデータ量は最少でなければならない」の場合

ORMの仕様で不要なカラムも取得している。
※ORMの内部で「SELECT * FROM tbl WHERE primary_key = ?」しているときなど。

しかし、1レコードしか取得しないため、不要なカラム数を取得しても影響は少ない。
また、可読性や変更容易性などを考えると、現状のORMをそのまま使用する方が良い。

「2.DBにSQLを発行する回数は最少でなければならない」の場合

100件の受注とそれに紐づく各10件の受注明細を登録する。
受注テーブルと受注明細テーブルのそれぞれにバルクインサートするので、2件のINSERT文で済む。

しかし、登録データにはDBの制約上登録できないものがあり、かつ事前チェックでは確認不可である。
顧客からは「登録できるものはそのまま登録してほしい」という要望が出た。
受注単位で整合性を取るため、受注1件とそれに紐づく受注明細を登録することになった。
つまり「トランザクション開始⇒受注登録⇒受注明細登録⇒コミット」の流れを100件分行った。

発行回数最少の原則に反しているが、要件なのでよい。

2.SQLを書いたら実行計画をとる

実行計画(またはアクセスプラン)とは、SQLを受け取ったDBが実際にどうSSD/HDD/メモリからデータを取得するかの計画である。
これを読むことにより、SQLが想定通りの最速の動きをするかを確認できる。

SQLが実行される流れ

製品によって異なるが、DBがSQLを受け取ってからデータを返す流れは下記の順序である。

パーサ：SQL文を解析する。
- SQL文が文法的に正しいか
- 指定したテーブルやカラムが存在するか
- ユーザにそのテーブルの閲覧権限があるか
- etc…
プランナ + 統計情報(カタログ)：SQLと統計情報から実行計画を立てる
- 統計情報とはDBに関する情報が格納されている
  - テーブル構造
  - インデックスの有無
  - インデックスのデータの偏り
  - etc…
- 複数パターンの実行計画を建て、コストが良い実行計画をエグゼキュータに渡す
エグゼキュータ：実行計画に基づいて、データを取得する。

実際のデータの取得方法はプランナの出す実行計画によるので、
いくら自分が最速のSQLを書いた気になっても、プランナの出した実行計画は最遅のものになっている可能性がある。
故にSQLを書いたら実行計画を読み、想定通りの取得方法になっているかを確認する。

取得方法

製品によるが、大体はクエリの先頭にEXPLAINを付与して実行する。

-- 実行計画取得
EXPLAIN SELECT * FROM tbl;
-- 実際にクエリを発行して計測
EXPLAIN ANALYZE SELECT * FROM tbl;

確認内容

表示される実行計画の内容は製品によるので、各自調べてほしい。

最低限、下記は確認する。

コストは極端に大きくないか
インデックスを使用しているか
- 使用しているインデックスは最適か
駆動表は小さいか
フルスキャンをしていないか
- フルスキャンをしていても、取得レコード数が小さいならば許容できる

注意

統計情報は常に最新であるとは限らない。
下記は統計情報が最新では無いゆえに、遅い実行計画が選定される例。

テーブルAにレコードが1件だけ登録された
統計情報が更新され、テーブルAはレコードが1件だけと記録された
テーブルAを検索
- レコードが1件だけなので、インデックスを見るまでもない。よってフルスキャンを行う。
テーブルAにレコードが1億件登録された
テーブルAを検索
- 統計情報が更新されていないので、レコードが1件だけと認識
- フルスキャンを行う実行計画が選定される
- 1億件全部見るので、遅いクエリとなる

上記の様に、誤った実行計画が選定されうる。
開発環境では最適な実行計画でも、本番では最悪な実行計画になりうる。

実行計画をとる際は、想定される本番データと同じ量/偏りのデータを登録した上でとる。

想定通りの実行計画を出す方法

主に下記など。

データ量の変動に影響を受けないクエリを書く
統計情報の凍結
ヒント句

その他個人的メモ

スロークエリログを見ろ
(PostgreSQL)AUTO VACUUMやAUTO ANALYZEの計画を立てろ
- 実行計画に響く