はじめに
経験が浅い開発者向けにSQL実行計画の読み方を説明した記事を書いて欲しいという要望を受け、本記事を寄稿しています。
ある程度の経験を積んだ技術者にとっては物足りない内容かと思いますが、現場での教育にあたっての参考ドキュメントとして利用していただければ幸いです。
SQLプラン(実行計画)を読む前に
SQL実行計画の読み方を説明する前に、以下の用語を解説します。
Index(B*Tree Index)
Indexとは文字通り"目次"です。本の目次をイメージして頂ければ分かると思いますが、自分の知りたい情報を探す際は目次を頼りにページをめくる事で、比較的簡単にその情報に辿り着けます。
DBのテーブルにもIndex(目次)を作成する事ができます。SQLチューニングの第一歩は、SQLがIndexを利用してデータにアクセスしているかどうかを確認する事です。
DBのテーブル全体の中で、特定のデータにピンポイントでアクセスしたい場合は、Index(目次)の利用が圧倒的に高速です。私たちがちょっとした調べ事をする際に、目次で示されているページ以外には目もくれないのと同じで、DBも不要なデータにアクセスしません。
Indexに関しての詳細はまた別のエントリで詳述しようと思います。
Table Full(Oracle)/Seq(PostgreSQL) Scan
先に"目次"の話をしましたが、Table Full Scanは目次を見ずに本を一冊、頭から最後まで読破する事です。
本当は本に書かれているある特定の情報だけ知りたかったのに、目次がないために全部読まざるを得なくて時間を浪費してしまった。これこそがSQLのパフォーマンス問題の事例の過半数です。Indexがない/あっても見ないから、不必要なデータを読み込んでしまい、レスポンスが返ってこないのです。
しかし、書いてある内容を体系的に理解したいなど、そもそも一冊全て読破する前提で本を読む事も多分にあるかと思います。この場合には目次は必要はありません。システムで言うと、分析処理などで特定期間の全てのデータにアクセスしたい場合などでしょうか。
また、メールやチャットのメッセージなどの文章自体が短いものは目次自体が不要です。もしあったとしても、ちょっと邪魔なのではないかと思います。
こういった場合(レコード数が非常に少ないTableへのアクセス)は、Table Full/Seq Scanが好ましいと言えます。
Join
SQLではテーブル同士を"結合"してデータを取得する事ができます。具体的には、"Inner Join"、"Left/Right Outer Join"、"Full Outer Join"、"Cross Join"といった記法があります。SQLチューニングでは、後述のSQLプランでしか確認できない、"JoinのJoin方法"を意識します。
"Nested Loops Join"、"Hash Join"、"(Sort )Merge Join"といったものです。
こちらもまた別の機会に詳述しようと思いますが、Indexを使いたい時は、Nested Loops Join、言い切るのは少し乱暴ですが、入門編としては取り合えずこれを覚えてください。
SQLプラン(実行計画)
SQLプランとは、SQLがデータにアクセスするにあたってどういったアクセス経路を辿るかを示したもの(前述のIndexを使うのかFull Scanするのか、Joinの方法など)です。これを確認する事で、自分の書いたSQLが自分の思い通りの経路でデータにアクセスしようとしているかを確認する事ができます。
SQLプランは主に次の要素から決定されます。
* テーブルの件数などの統計情報
* (適切な)Indexの有無、尚且つ、Indexを利用できる検索条件かどうか
* SQLの記述内容(過度に複雑だと想定外のプランになる場合がある)
SQLプランは、オプティマイザというDBの機能が各種情報を総合的に判断した結果、最適と結論付けられたものが採用されます。メジャーなプロダクトで実装されているオプティマイザは非常に賢く、複雑なSQLであってもmsecの世界で瞬時にプランを導出します。それゆえ、いかにオプティマイザに適切な情報をインプットするかが重要になってきます。
統計情報
統計情報とは、Tableのレコードや、Indexにおける値の偏り度合いなどの事です。
この情報を元にオプティマイザはSQLプランを組み立てるため、統計情報が適切に取得/設定されている事は非常に重要です。
統計情報は以下のディクショナリ・オブジェクトを参照する事で確認できます。
SQLプラン取得方法
一般的にDB接続に利用されているGUIアプリケーションには実行計画をワンクリックで確認可能なアイコンが用意されています。大抵の場合においては、GUIツールの利用で十分にチューニングが可能ですが、より詳細な情報が必要な場合はCUIを利用して出力粒度を調整可能です
Oracle：https://docs.oracle.com/cd/E16338_01/server.112/b56312/sqltrace.htm
PostgreSQL：https://www.postgresql.jp/document/11/html/using-explain.html
ケーススタディ：事前準備
- DBはPostgreSQL 11を利用
- サンプルテーブルして売上(sales)と、商品(products)を作成します
CREATE TABLE sales (
sales_id numeric(8) NOT NULL,
saler_id numeric(6) NOT NULL,
buyer_id numeric(6) NOT NULL,
product_id numeric(6) NOT NULL,
amount numeric(8) NOT NULL,
sale_time date NOT NULL,
PRIMARY KEY (sales_id)
);
CREATE TABLE products (
product_id numeric(6) NOT NULL,
product_name character varying(16) NOT NULL,
price numeric(8) NOT NULL,
PRIMARY KEY (product_id)
);
- salesには10,000,000件、productsには5,000件のサンプルレコードを登録。各項目には値の偏りを持たせます
INSERT INTO sales
(
sales_id,
saler_id,
buyer_id,
product_id,
amount,
sale_time
)
SELECT
generate_series,
mod(generate_series, 1000) + 1,
mod(generate_series, 10000) + 1,
mod(generate_series, 5000) + 1,
mod(generate_series, 100) + 1,
make_date(2019, 12, mod(generate_series, 31) + 1)
FROM
generate_series(1, 10000000)
;
INSERT INTO
products
(
product_id,
product_name,
price
)
SELECT
generate_series,
'商品' || generate_series::character,
generate_series * 100
FROM
generate_series(1, 5000)
;
- 本例では、salesとproductsを結合し、特定の顧客が特定の商品をいくら購入したかを集計するSQLを発行して結果を比較します
ケーススタディ：Index未作成
まずはIndex未作成の状態でクエリを発行し、プランを確認すると以下のような結果となります。
EXPLAIN ANALYZE
SELECT
s.buyer_id,
SUM(p.price * s.amount)
FROM
products p
INNER JOIN sales s
ON p.product_id = s.product_id
WHERE
s.buyer_id = 1
AND p.product_id = 1
GROUP BY
s.buyer_id,
p.product_id
;
GroupAggregate (cost=1000.28..137038.94 rows=1 width=42) (actual time=1326.297..1326.298 rows=1 loops=1)
Group Key: s.buyer_id, p.product_id
-> Nested Loop (cost=1000.28..137038.91 rows=1 width=21) (actual time=2.247..1325.231 rows=1000 loops=1)
-> Index Scan using products_pkey on products p (cost=0.28..8.30 rows=1 width=11) (actual time=0.005..0.009 rows=1 loops=1)
Index Cond: (product_id = '1'::numeric)
-> Gather (cost=1000.00..137030.60 rows=1 width=15) (actual time=2.239..1326.396 rows=1000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on sales s (cost=0.00..136030.50 rows=1 width=15) (actual time=2.456..1319.165 rows=333 loops=3)
Filter: ((product_id = '1'::numeric) AND (buyer_id = '1'::numeric))
Rows Removed by Filter: 3333000
Planning Time: 0.285 ms
Execution Time: 1327.903 ms
上記の実行計画からで確認できるのは以下のポイントです。実行計画の表示ですが、SQLのインラインビューと同様にネストの深いところから処理が実行されます。
* "Nested Loop"の記載と、その下のproducts → salesという並びから、productsを駆動表としてsalesとNested Loops Joinしている事が分かります。
* "Index Scan using products_pkey on products"の記載から、productsに対して、PKを利用したIndexアクセスをしている事が分かります。
* "Parallel Seq Scan on sales"の記載から、salesに対して2並列でSeq Scanをしている事が分かります。アクセスしたいのは特定の顧客、特定の商品の購入データですから、10,000,000件全てにアクセスする事は非常に非効率と言えます。
* "Execution Time"から、実行に約1300 msecを要している事が分かります。
Nested Loops Join x Full/Seq Scanでのデータアクセスは、Indexを利用できていない典型的なアンチパターンです。
ここでsalesへの非効率なアクセスを改善すべく、"buyer_id"、"product_id"の組み合わせの複合Indexを作成し、再度プランを確認します
CREATE INDEX ix_sales ON sales(buyer_id, product_id);
EXPLAIN ANALYZE
SELECT
s.buyer_id,
SUM(p.price * s.amount)
FROM
products p
INNER JOIN sales s
ON p.product_id = s.product_id
WHERE
s.buyer_id = 1
AND p.product_id = 1
GROUP BY
s.buyer_id,
p.product_id
;
GroupAggregate (cost=0.72..16.79 rows=1 width=42) (actual time=1.404..1.404 rows=1 loops=1)
Group Key: s.buyer_id, p.product_id
-> Nested Loop (cost=0.72..16.77 rows=1 width=21) (actual time=0.052..1.031 rows=1000 loops=1)
-> Index Scan using products_pkey on products p (cost=0.28..8.30 rows=1 width=11) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (product_id = '1'::numeric)
-> Index Scan using ix_sales on sales s (cost=0.43..8.46 rows=1 width=15) (actual time=0.043..0.890 rows=1000 loops=1)
Index Cond: ((buyer_id = '1'::numeric) AND (product_id = '1'::numeric))
Planning Time: 0.231 ms
Execution Time: 1.435 ms
salesへのアクセスが"Index Scan using ix_sales"とあるように、新規で作成したIndexを介したものに変わっています。
Indexを利用した方が低コストだとオプティマイザが判断したという事です。
肝心の改善効果ですが、"Execution Time"が1.4msecと、およそ1/1000まで実行時間が短縮しています。
ケーススタディ：Indexが効かない
前述の例ではIndexを作成する事による改善効果を示しましたが、折角作成したIndexが使用できなくなるパターンを紹介します。
以下の例では、sales.buyer_idに対して型変換を行っています。
EXPLAIN ANALYZE
SELECT
s.buyer_id,
SUM(p.price * s.amount)
FROM
products p
INNER JOIN sales s
ON p.product_id = s.product_id
WHERE
s.buyer_id::character = '1'
AND p.product_id = 1
GROUP BY
s.buyer_id,
p.product_id
;
GroupAggregate (cost=168289.57..168289.82 rows=10 width=42) (actual time=1272.890..1272.890 rows=1 loops=1)
Group Key: s.buyer_id, p.product_id
-> Sort (cost=168289.57..168289.59 rows=10 width=21) (actual time=1272.237..1272.308 rows=1000 loops=1)
Sort Key: s.buyer_id
Sort Method: quicksort Memory: 103kB
-> Nested Loop (cost=1000.28..168289.40 rows=10 width=21) (actual time=2.342..1271.297 rows=1000 loops=1)
-> Index Scan using products_pkey on products p (cost=0.28..8.30 rows=1 width=11) (actual time=0.005..0.008 rows=1 loops=1)
Index Cond: (product_id = '1'::numeric)
-> Gather (cost=1000.00..168281.00 rows=10 width=15) (actual time=2.335..1272.281 rows=1000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on sales s (cost=0.00..167280.00 rows=4 width=15) (actual time=1.732..1266.010 rows=333 loops=3)
Filter: ((product_id = '1'::numeric) AND ((buyer_id)::character(1) = '1'::bpchar))
Rows Removed by Filter: 3333000
Planning Time: 0.354 ms
Execution Time: 1274.319 ms
SQLによって取得されるデータは同じはずなのですが、実行計画が元に戻ってしましました。
Index作成カラムに対して何かしらの手が加わると、Indexは利用できなくなります。実際の開発において上記のような条件指定をする事はないでしょうが、この場合は"s.buyer_id = '1'::numeric"と右辺側で型変換を行えばIndexが使用できます。数値を用いた演算結果を検索条件にしたい際などの注意点になります。
ここまで見てきたように、SQLプランを見て"Indexが利用されているかどうか"を判断できれば、次のステップ"どういったIndexを作成すべきか"に進みます。
※今回は触れません
SQLチューニングをする機会はないに越した事はない
SQLチューニングをする機会は、少なければ少ない程好ましいと言えます。それはすなわち、適切にデータ・モデリングがなされ、モデルが適切にDB設計(論理/物理)に落とされ、設計が適切にアプリケーションの実装に落とされているという事の裏返しだからです。
著者もSQLチューニングをする機会がしばしばありますが、これにより性能を改善させて感謝の言葉を言われる事よりも、自身が設計・実装したシステムが性能問題を起こさず、何も言われないという事を誇りに思います。なぜなら、後者の方が遥かに難しいからです。
SQLチューニングは最終手段と位置付け、そのカードを切らなくてもよいようにデータモデリングをしましょう。データモデル次第でアプリケーション(SQL)からのアクセスパターンが変わります。
実装においては、バッチ処理などで非常に長いSQLを目にする機会がありますが、これも実行計画の組立に対しては好ましくなく、テストケースの作成・実施も複雑になります。可能であればシンプルな複数のSQLに分割しましょう。
実践の第一歩としては、まず最初に、データへのアクセス・パスをイメージしてSQLを書く。書いたSQLの実行計画を確認し、イメージした通りのアクセス・パスになっているか(入門編としては、きちんとIndexを利用しているかどうか)を確認する。これをするだけで、開発フェーズにおいて性能面の品質を作る事が可能です。
おわりに
DBMSを利用した開発をする限り、大なり小なりSQLチューニングが必要な場面に遭遇する機会はあるかと思います。しかし、本エントリのケーススタディで触れているような基礎の基礎を理解するだけでも、身近な性能問題を解決できるのではないかと思います。テクニックが必要な場合ももちろんありますが、Index利用で解決する事がほとんどですので、誰でもここまでは省エネで出来るようになります。
SQLプランを描いてからSQLを書く、そして実際のSQLプランを確認する。このルーティーンです。