最終更新:
SQLクエリの仕組み — 商品を探すSQLと、実行計画を見てみよう
欲しい商品を、計画に沿って探す
ひよこ 商品表からりんごを探すSELECTを実行したら、データベースの中では何が起きるの？
ペンギン先生 まず文を解析し、どう探すか計画して実行するよ。PostgreSQLでは解析の後に書き換えもある。本文の小さな商品表で、結果とEXPLAINの計画を比べてみよう。
ひよこ 文法チェックの次は何をするの？
ペンギン先生 ひよこ 処理にかかるコストはどうやって見積もるの？
ペンギン先生 行数や値の分布などの統計情報、利用できる索引、想定する処理の費用から見積もるよ。多い行の中から少数を探すときは索引が候補になるが、たくさんの行を読むなら全走査を選ぶ場合もある。索引があれば必ず使うとは限らないんだ。
ひよこ それで実際にデータを取りに行くの？
ペンギン先生 ひよこ JOINとかWHEREが複雑だと遅くなるのはなぜ？
ペンギン先生 ひよこ 実行計画って確認できるの？
ペンギン先生 PostgreSQLなどではEXPLAINの後にSELECT文を書いて推定の実行計画を見られるよ。PostgreSQLのEXPLAIN ANALYZEはクエリを実際に動かして測るので、負荷やデータ変更の影響にも注意が必要なんだ。
ひよこ プロのエンジニアはどうやってクエリを速くしてるの？
ペンギン先生 まず計画を見て、推定行数や結合方法などを調べるよ。大量の行を読むなら全走査が適切な場合もあるから、インデックスを足せば必ず速くなるわけではない。統計情報、絞り込み条件、取得列を確認し、同じ結果を保てる変更を実測で比べるんだ。
まず、商品を一つ探す
新しい練習用のPostgreSQLのDBで、次の表を作ります。同名の表がないことを確認してください。DBの接続をまだ用意していなければ、まずSQL入門で表と問い合わせから始められます。
CREATE TABLE products_demo (
name TEXT,
price INTEGER
);
INSERT INTO products_demo
VALUES ('りんご', 200), ('バナナ', 150);
SELECT name, price
FROM products_demo
WHERE name = 'りんご';結果は「りんご、200」です。次は同じSELECTの前にEXPLAINを付けます。
EXPLAIN
SELECT name, price
FROM products_demo
WHERE name = 'りんご';この小さな索引なしの表なら、全走査のSeq Scanと、名前を確認するFilterを探せます。表示される費用や推定行数は、バージョンや統計・設定で変わります。特定の数字を正解として暗記する練習ではありません。
SQLと実行計画は、見ているものが違う
SQLは欲しい結果を指定する文、実行計画はデータをどの方法で読むかを示します。PostgreSQLの説明では、解析、書き換え、計画、実行へ進みます。DBごとに段階や実装が異なるので、内部の箱が常に同じ数だとは考えません。
| 見るもの | 読み取ること |
|---|---|
| SELECT | 商品名と価格が欲しい |
| WHERE | 名前がりんごの行に絞る |
| Seq Scan | 表の行を順に読む方法 |
| Filter | 条件を満たす行か確認する |
推定と実測を分ける
PostgreSQLの通常のEXPLAINは計画を示し、EXPLAIN ANALYZEは実際にクエリを実行して測ります。費用の推定値はそのままミリ秒ではありません。更新文にANALYZEを付ければ更新も起こり得るため、練習用環境と内容を確認して使います。
推定行数と実際の行数、読む範囲、結合方法などを比較します。「短いSQLだから速い」「インデックスを追加すれば必ず速い」とは判断しません。
もう少し詳しく：データの量で、方法も変わる
索引は維持や書き込みにも費用があります。多くの行を読むなら全走査が適切なこともあり、商品2件の練習の結果を大規模DBへそのまま当てはめられません。必要な列・条件・統計情報を見て、同じ結果を保つ変更を実際の負荷で比べます。
ペンギン先生のまとめ
「実行計画」って出てきたら「欲しい結果を、どの道順で取り出すか」と思えばだいたいOK！ まずSELECTの結果とEXPLAINの方法を、一つずつ説明してみよう。