SQLクエリの仕組み — 商品を探すSQLと、実行計画を見てみよう
欲しい結果と、探す方法を分ける
見積もりと、実際の処理
全組み合わせと、結合の方法
同じ結果を保って、改善する
まず、商品を一つ探す
新しい練習用の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 | 条件を満たす行か確認する |
条件を変えて、結果の順番も指定する
最初に作った2行の商品表で、次も試せます。新しい表やデータは増やしません。
SELECT name, price
FROM products_demo
WHERE price <= 200
ORDER BY price ASC;
結果は安い順に「バナナ、150」「りんご、200」です。WHEREは残す行、ORDER BYは返す順番を指定します。ORDER BYがないと、たまたま登録順に見えても、その順番を保証として扱えません。同じ価格があるデータで同順位の順まで決めたいなら、さらに一意なIDなどの並べ替え条件が必要です。
同じ結果で比べることが性能改善の前提なので、計画を見る前に「どの行を、どんな順番で返したいか」も確かめます。
推定と実測を分ける
PostgreSQLの通常のEXPLAINは計画を示し、EXPLAIN ANALYZEは実際にクエリを実行して測ります。費用の推定値はそのままミリ秒ではありません。更新文にANALYZEを付ければ更新も起こり得るため、練習用環境と内容を確認して使います。
推定行数と実際の行数、読む範囲、結合方法などを比較します。「短いSQLだから速い」「インデックスを追加すれば必ず速い」とは判断しません。
もう少し詳しく:データの量で、方法も変わる
索引は維持や書き込みにも費用があります。多くの行を読むなら全走査が適切なこともあり、商品2件の練習の結果を大規模DBへそのまま当てはめられません。必要な列・条件・統計情報を見て、同じ結果を保つ変更を実際の負荷で比べます。
ペンギン先生のまとめ
「実行計画」って出てきたら「欲しい結果を、どの道順で取り出すか」と思えばだいたいOK! まずSELECTの結果とEXPLAINの方法を、一つずつ説明してみよう。