データベース操作・制御

クエリ最適化・実行計画 くえりさいてきか・じっこうけいかく

EXPLAINオプティマイザ統計情報インデックススロークエリパフォーマンスチューニング
クエリ最適化・実行計画について教えて

簡単に言うとこんな感じ!

SQL は「何がほしいか」だけを書く言葉で、「どう探すか」はデータベースが自分で考えるんだ。その作戦を選ぶのがクエリ最適化、選んだ作戦の中身が実行計画だよ。カーナビのルート案内と同じで、遅いときはまず「どの道を通ったか」を見るってこと!


クエリ最適化・実行計画とは

SQL には「東京の顧客の今月の注文を出して」のように 結果だけ を書き、どの順番で表を読むか、索引を使うかは書きません。それを決めるのがデータベースの中の オプティマイザ(PostgreSQL では プランナー)です。同じ結果を出す方法は何通りもあるので、オプティマイザは表の行数や値の偏りといった 統計情報 をもとに手間(コスト)を見積もり、いちばん安そうな方法を選びます。これが クエリ最適化 です。

選ばれた手順書が 実行計画(Execution Plan) です。「注文の表を索引で絞り込み、顧客の表とこの方法でつなぐ」といった段取りが木の形で並びます。EXPLAIN という命令を SQL の前に付けると、この計画を見ることができます。

大事なのは、オプティマイザは 見積もりで判断している という点です。統計が古い、条件の書き方が索引を使えない形になっている、といった理由で見積もりが外れると、普段は一瞬で終わる画面が何十秒もかかることがあります。「急に遅くなった」の理由は、たいてい実行計画に出ています。

クラウド上のデータベースサービスを表すイラスト

SQL が実行されるまで

SQL が届く 文法を確かめる 計画を比べて選ぶ 計画どおりに実行 統計情報(行数など) EXPLAIN で見える部分

実行計画の見方

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;
Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=5 width=64)
  (actual time=0.02..0.03 rows=4 loops=1)
表示意味
Seq Scan表を頭から全部読む。大きな表で少しだけ取るなら遅い
Index Scanインデックス で場所を調べてから読む
Nested Loop / Hash Join / Merge Join2つの表のつなぎ方(結合)
cost見積もった手間(単位は相対的なもの)
rows見積もった行数。actual の行数と大きくずれていたら要注意

EXPLAIN ANALYZE は 実際に実行して 時間も測ります。更新の SQL に付けると本当に書き換わるので注意します。


「画面が遅い」って言われたら、発注側の私は何を頼めばいいの?

まず「どの画面の、どの操作が、何秒かかるか」を伝えるのがいちばん助かるんだ。そのうえで、開発会社には「遅い SQL を特定して、実行計画を見せてください」って頼むといいよ。

DB には遅い SQL を記録する仕組み(スロークエリログや pg_stat_statements)があるから、それが動いているかも聞いてみよう。

インデックスをたくさん付ければ、全部速くなるんじゃないの?

そこが落とし穴なんだ。インデックスは 登録や更新のたびに作り直す手間 が増えるし、場所も食う。それに、WHERE の書き方しだいでは、付けても使われないんだよ。

だから、実行計画を見て「ここで全件読んでいる」と確かめてから、必要なものだけ足すのが正しい順番なんだ。


遅くなる典型的な原因

原因何が起きているか主な手当て
索引がない大きな表を毎回全部読む条件に合う インデックス・複合インデックス を足す
列に関数をかけているWHERE date(created_at) = ... で索引が使えない範囲の条件に書き直す
統計が古い行数の見積もりが外れ、悪い計画を選ぶANALYZE で統計を更新する
N+1 問題一覧の件数ぶん SQL を繰り返し投げるORM でまとめて読む設定にする
取りすぎ使わない列や行まで読む必要な列だけ選ぶ、ページ分けする

PostgreSQL では、統計の更新は VACUUM と同じ自動の仕組み(autovacuum)が行います。大量に取り込んだ直後は、更新が追いつかず遅くなることがあります。


歴史と背景

  • 1970年代:IBM の研究用データベース System R で、統計からコストを見積もって計画を選ぶ方法が研究され、1979年にパトリシア・セリンジャーらの論文で示される
  • 1980年代〜1990年代:SQL が規格になり、商用データベースが「速く答えを返す計画を選ぶ力」で競うようになる
  • 2000年代:Oracle などで、決まった規則で計画を選ぶ方式から、コストで選ぶ方式への移行が進む
  • 2010年代以降:クラウドの DB や監視サービスで、遅い SQL と実行計画を画面から見られるようになる

速くするための他の手段との比べ方

手段効く場面注意点
SQL と索引の見直しほとんどの遅さ。まずここから実行計画で効果を確かめる
マテリアライズドビュー重い集計を何度も見る中身が少し古くなる
パーティショニング期間で絞ることが多い巨大な表設計の手間が増える
リードレプリカ読み取りの量が多すぎる1本の SQL は速くならない

実務のポイント

  • 遅い SQL を記録する設定(スロークエリログ、pg_stat_statements など)が本番で有効か
  • 主な画面の 目標の応答時間 を要件に書いているか
  • 本番に近い データ量でテスト しているか(少ない件数では遅さが出ない)
  • 性能の改善報告に、改善前と後の実行計画 が付いているか
  • 大量の取り込みの後に 統計を更新する 手順があるか

関連する規格・RFC

規格内容
ISO/IEC 9075(SQL)SQL の国際規格。結果として何を返すかを定め、どう実行するかは各製品に任せる。EXPLAIN は規格になく、製品ごとの機能

参考文献・外部リンク


関連用語

シス担のミカタ メールマガジン

セキュリティの速報とシステムの最新情報をメールで受け取る

登録すると、次の配信からお届けします。配信はメールの下のリンクから、いつでも解除できます。個人情報の取り扱い