【SQL】実行計画を読み解きパフォーマンス改善
【SQL】ゼロ知識からSQLのパフォーマンスを考えられるようになるまで
ではindexの理解や、DBMSがどのような仕組みで動いているかなどについてまとめました。
知識がついたところで、実際に手を動かしながらSQLのチューニングをしてみようと思います。
※ 毎回記載していますが、間違った事を書いているかもしれないので、何か気づいたらコメントください。
また、この記事ではSQLのパフォーマンスのボトルネックになっている箇所を特定し改善するための視点をまとめたものです。
この記事を読むと得られること
・実行計画を読んで現状を把握することが出来るようになります。
・パフォーマンスの優れたSQLを意識して書けるようになります。
・indexを使い全体を最適化し実行計画を変更することが出来るようになります。
前提
前提としてPostgreSQL 10.4を使用しています。
SQLパフォーマンス改善へのアプローチ方法
SQLのパフォーマンス改善までの流れとして、まずは実行計画を読み、
どのような方法でテーブルにアクセスしようとしているのか理解する必要があります。
その上で、ボトルネックや想定していない挙動を把握し、以下のアプローチで改善していきます。
・indexを使い全体を最適化し実行計画を変更
・ヒント句を使用し部分最適化
・クエリの変更
実行計画のおさらい
では実行計画を読めるようになるためにまずは実行計画のおさらいからいきます。
統計情報を元にオプティマイザが最適な実行計画を作成してくれます。
ですが、使用する統計情報が古かったり、オプティマイザが誤った実行計画を作成するかもしれません。
なので私たちは、実行計画を読み、その計画が正しいか判断しアプローチする方法を考える必要があるわけです。
実行計画の得方と簡単な読み方
では実行計画はどのようにしたら得ることができるのか、
それは「EXPLAIN」を、実行するSQLの先頭につけることで、実行計画を取得できます。
※EXPLAINを付けても実際にSQLが実行されません。使用するSQLの実行計画を表示してくれるものだと思ってください。
以下はSELECT * FROM badge_list;の実行計画を表しています。
hooop=# EXPLAIN SELECT * FROM badge_list;
-[ RECORD 1 ]-----------------------------------------------------------------
QUERY PLAN | Seq Scan on badge_list (cost=0.00..1708.22 rows=24322 width=444)
hooop=#
「ANALYZE」オプションを使用することで、「EXPLAIN」の予想に加え、実際にかかった処理時間等の情報も得ることができます。
※実際にSQL文を実行するので注意してください！！！
hooop=# EXPLAIN ANALYZE SELECT * FROM badge_list;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------
QUERY PLAN | Seq Scan on badge_list (cost=0.00..1708.22 rows=24322 width=444) (actual time=0.126..34.021 rows=24322 loops=1)
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------
QUERY PLAN | Planning time: 0.065 ms
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------
QUERY PLAN | Execution time: 49.411 ms
hooop=#
簡単に上記の結果の見方は、
actual time：実際にかかった処理時間
rows：実行結果として、実際に戻ってきた行数
loops：ステップの実行回数
Planning time：解析されたクエリから実行計画を生成し、最適化するのに要した時間
Execution time：実行時間
実行計画に書かれる演算子とSQL文の関係
EXPLAINをしたときに全ての処理に演算子がつけられます。
その演算子は何をしているのかを理解できれば、なんの処理にどのくらいコストを必要としているのかがわかります。
実行計画を理解する際にここが一番大切になってくるかと思います。
以下に簡単な表としてまとめましたが、他にももっとあったり、DBMSによって表記に差があるります。
| 分類 | 演算子 | 説明 |
|---|---|---|
| 表スキャン | Seq Scan | indexを使わずに上から順番にスキャンしていく |
| -- | Index Scan | indexを使用したスキャン |
| -- | Index Only Scan (PostgreSQL 9.2以降) | インデックスのみを利用してデータを読み取るスキャン。テーブルアクセスしない |
| 結合 | Nested Loops | 片方のテーブルから結果を取り出し、その結果をもう1つのテーブルの各行に対して問い合わせて、2つのテーブルを結合。データ量が少ない場合に良い |
| -- | Hash Join / Hash | ハッシュ値同士で結合。メモリーの空き容量が大きい場合に良い |
| -- | Merge Join | 並び替えられたデータ通しの結合。データ量が多い場合に良い |
| ソートとグルーピング | Sort / Sort Key | 順番の並び替え |
| -- | GroupAggregate | 集約関数にGroup by |
| -- | HashAggregate | レコードをグルーピングするのに一時的なハッシュテーブルを使用。 |
| -- | Group | Group by |
indexを貼ってみる
ではここからは実際にindexを貼ることでどの程度パフォーマンスが改善されるのか見ていきます。
indexとして登録されているリストをみるには以下のコマンドで確認できます。
hooop=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+----------+-------+----------+------------
public | index_id | index | postgres | badge_list
(1 row)
hooop=#
追加するときは CREATE INDEX インデックス名 ON テーブル名(カラム名);
hooop=# CREATE INDEX index_id ON badge_list (id);
CREATE INDEX
hooop=#
削除するときはDROP INDEX インデックス名;
hooop=# DROP INDEX public.index_id;
DROP INDEX
hooop=#
例えば、インデックスを貼らずに表をスキャンした場合
検索条件にヒットするレコードを一行目から順番に探索している事がわかります。
（Seq Scanと記載されているので）
hooop=# EXPLAIN ANALYZE SELECT id FROM badge_list where id = 20000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on badge_list (cost=0.00..1769.03 rows=1 width=4) (actual time=6.056..6.928 rows=1 loops=1)
Filter: (id = 20000)
Rows Removed by Filter: 24321
Planning time: 0.141 ms
Execution time: 6.951 ms
(5 rows)
hooop=#
idを検索キーとしているのでこのidをindexに設定した場合
※indexを指定する際にはカーディナリティの高いものを設定してください。
また、正しくないindexを貼りすぎてもオプティマイザが間違った解釈をする可能性があるので、使う際は十分に注意してください。
hooop=# EXPLAIN ANALYZE SELECT id FROM badge_list where id = 20000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Only Scan using index_id on badge_list (cost=0.29..8.30 rows=1 width=4) (actual time=0.121..0.122 rows=1 loops=1)
Index Cond: (id = 20000)
Heap Fetches: 1
Planning time: 0.468 ms
Execution time: 0.180 ms
(5 rows)
hooop=#
このようにたったこれだけで実行速度が変わります。
複合indexを適切に使えていない状態
例えば、複合インデックスとして(good, id)を設定しているとします。
このテーブルに対し、「id」のみをWHERE句条件に指定した場合には、インデックスが無効になります。
実際に見てみましょう。
まず複合indexを設定(good,id)
hooop=# CREATE INDEX index_id ON badge_list (good,id);
CREATE INDEX
hooop=#
2番目のidを検索条件に入れたSQLを実行すると
hooop=# EXPLAIN SELECT * FROM badge_list where id = 20000;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using index_id on badge_list (cost=0.29..462.71 rows=1 width=446)
Index Cond: (id = 20000)
(2 rows)
hooop=#
次に複合indexの順番を変更してみます。(id,good)
hooop=# CREATE INDEX index_id ON badge_list (id,good);
CREATE INDEX
hooop=#
1番目のidを検索条件に入れたSQLを実行すると
hooop=# EXPLAIN SELECT * FROM badge_list where id = 20000;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using index_id on badge_list (cost=0.29..8.30 rows=1 width=446)
Index Cond: (id = 20000)
(2 rows)
hooop=#
costを確認してください。全く違う事がわかると思います。
※また、実際に実行した場合、レコード数が多くなるにつれて指数関数的に実行時間が長くなります。
SQL文を書く際にはちゃんと何がindexとして指定されているか確認してから書かないと大変なことになります。
部分index
例えば、こんな状況があるかもしれません。
「シーケンシャルアクセスを止めるために、where句の検索条件である値がtrueかfalseか絞りたいけど、
カーディナリティが低いからindexとしてつけるには微妙。trueの時だけindex効いて欲しい」
そんなときに使うのが部分indexらしいです。データを用意するのがめんどくさかったので試してはいないです。
使い方はindexを貼る際にwhere句で絞ってあげるだけ。
CREATE INDEX index_id ON badge_list (id) WHERE ●● is true;
統計情報の更新
indexを正しく貼っていても統計情報が古いと最適化された実行計画になりません。
統計情報を更新するには1日に1回程度ANALYZEを行い情報を最新のものにしていきます。
（更新が多いとindexのツリー構造が平衡木でなくなってくるので1日1回だと足りない）
また、PostgreSQLには、省略可能ですが強く推奨される自動バキュームという機能があるのでそんなに気にしなくてもいいかもしれないです。
自分がチューニングした時使用したRedshift(PostgreSQLベースのDB)は自動バキュームしてくれていました。
ヒント句を貼ってみる
ヒント句とは実行されるクエリの実行計画の決定をプランナに任せきりにせず、プランナに実行計画を直接指示することを可能とする機能です。
ただヒント句は対象のSQLのみに適用する部分的な最適化手法なので、まずはindexやテーブル設計を考え
全体最適化を試みるほうがいいと思います。
全体最適したら部分最適のやる事も減っていると思います。
※自分の環境でヒント句用のモジュールがなぜかうまくダウンロードできないので、後日付け足します。
クエリの変更
そもそもパフォーマンスに優れていない（レコード数が大きくなると負荷が上がるSQL）を書いているパターンが多いので、
実行計画を読み、ボトルネックになっているSQLを特定したら他のSQL文の書き方がないか検討してます。
以下によくある悪いパターンと良いパターンを実行計画を使って定量的に比較してみました。
WHERE句で検索カラムに算術式を記載しない
idをindexに指定
hooop=# CREATE INDEX index_id ON badge_list (id);
CREATE INDEX
hooop=#
idを2倍にしてそれが20000以上のものを検索
hooop=# EXPLAIN SELECT * FROM badge_list WHERE id * 2 > 20000 ;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on badge_list (cost=0.00..1862.83 rows=8107 width=446)
Filter: ((id * 2) > 20000)
(2 rows)
hooop=#
上記と同じ結果になるSQL文を違う書き方で書いて見る。
hooop=# EXPLAIN SELECT * FROM badge_list WHERE id > 20000 / 2;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using index_id on badge_list (cost=0.29..1322.02 rows=14322 width=446)
Index Cond: (id > 10000)
(2 rows)
hooop=#
ちゃんとindexを使った検索ができるようになっている事がわかる。
WHERE句は選択条件が少なくなる条件から記述
これは実行計画で示さなくてもわかると思うので、割愛するが、
100レコード→90レコード→30レコードと絞るより
100レコード→20レコード→10レコードとより絞る事ができるカラムから絞っていったほうが良い。
INよりEXISTSのほうが速い
INよりEXISTSのほうが速いと多くのブログなどで見るし、自分も風の噂でそう聞いています。
でも実際は？
EXISTSを使った場合
hooop=# EXPLAIN ANALYZE
hooop-# SELECT P.sender_name
hooop-# FROM badge_list AS P WHERE
hooop-# EXISTS (SELECT FROM badge_list AS C WHERE P.sender_name = C.sender_name);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1806.03..4265.39 rows=24322 width=13) (actual time=16.773..32.279 rows=24322 loops=1)
Hash Cond: ((p.sender_name)::text = (c.sender_name)::text)
-> Seq Scan on badge_list p (cost=0.00..1741.22 rows=24322 width=13) (actual time=0.022..5.082 rows=24322 loops=1)
-> Hash (cost=1803.81..1803.81 rows=178 width=13) (actual time=16.736..16.736 rows=178 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> HashAggregate (cost=1802.03..1803.81 rows=178 width=13) (actual time=16.648..16.683 rows=178 loops=1)
Group Key: (c.sender_name)::text
-> Seq Scan on badge_list c (cost=0.00..1741.22 rows=24322 width=13) (actual time=0.008..8.220 rows=24322 loops=1)
Planning time: 3.093 ms
Execution time: 33.903 ms
(10 rows)
hooop=#
INを使った場合
hooop=# EXPLAIN ANALYZE
hooop-# SELECT sender_name
hooop-# FROM badge_list WHERE sender_name
hooop-# IN (SELECT sender_name FROM badge_list);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1806.03..4265.39 rows=24322 width=13) (actual time=17.233..32.920 rows=24322 loops=1)
Hash Cond: ((badge_list.sender_name)::text = (badge_list_1.sender_name)::text)
-> Seq Scan on badge_list (cost=0.00..1741.22 rows=24322 width=13) (actual time=0.018..5.236 rows=24322 loops=1)
-> Hash (cost=1803.81..1803.81 rows=178 width=13) (actual time=17.204..17.204 rows=178 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> HashAggregate (cost=1802.03..1803.81 rows=178 width=13) (actual time=17.131..17.164 rows=178 loops=1)
Group Key: (badge_list_1.sender_name)::text
-> Seq Scan on badge_list badge_list_1 (cost=0.00..1741.22 rows=24322 width=13) (actual time=0.009..8.630 rows=24322 loops=1)
Planning time: 0.404 ms
Execution time: 34.482 ms
(10 rows)
hooop=#
INとEXISTSと比べてわかるように実行計画のかかるコストがほとんど同じなようです。
ちょっと調べて見たところ、version8.0らへんまではEXISTSの方が明らかに早かったようですが、
改良されてほとんど誤差がなくなってきてるみたいです。
※もちろん使う場面によって差は出ると思います。
ここでも重要なのは、実際に自分が書いたSQLの実行計画を読んで、どちらがパフォーマンスに優れたSQLか自ら判断する事だと思いました。
DISTINCTでなくGROUP BY を使う
重複を排除したい時に、迷うのがDISTINCTとGROUP BYだと思います。
※EXISTSを使えばソート処理もなく重複排除できるというのは一旦置いてます。
この両者を比較してみると、DISTINCTを使った場
hooop=# EXPLAIN ANALYZE
hooop-# SELECT DISTINCT id
hooop-# FROM badge_list;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1802.03..2045.25 rows=24322 width=4) (actual time=28.156..33.608 rows=24322 loops=1)
Group Key: id
-> Seq Scan on badge_list (cost=0.00..1741.22 rows=24322 width=4) (actual time=1.820..11.238 rows=24322 loops=1)
Planning time: 0.086 ms
Execution time: 35.189 ms
(5 rows)
hooop=#
GROUP BYを使った場合
hooop=# EXPLAIN ANALYZE
hooop-# SELECT id
hooop-# FROM badge_list
hooop-# GROUP BY id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1802.03..2045.25 rows=24322 width=4) (actual time=17.975..22.792 rows=24322 loops=1)
Group Key: id
-> Seq Scan on badge_list (cost=0.00..1741.22 rows=24322 width=4) (actual time=0.016..5.813 rows=24322 loops=1)
Planning time: 0.086 ms
Execution time: 24.340 ms
(5 rows)
hooop=#
確かに、実行計画上だと違いはありませんが、実行結果で比較して見ると性能差がある事がわかるかと思います。
Likeによる文字列の前方探索
インデックスの走査においてLIKEが有効なのは、ワイルドカードの前までとなっています。
例えば、「LIKE '久川%'」のように後方に%を入れた場合、%の前である「久川」まではインデックスが効きます。
一方で、「LIKE '%善法'」のように%が前方につく「後方一致検索」の場合だと、インデックスは無効となり非常に重たいクエリになってしまいます。
では実際に比較して意味ます。
まずインデックスを効かせたいのでインデックスを生成します。
hooop=# CREATE INDEX index_id ON badge_list (sender_name);
CREATE INDEX
hooop=#
前方一致のワイルドカードを使用し、インデックスを効かせた場合
hooop=# EXPLAIN ANALYZE
hooop-# SELECT sender_name
hooop-# FROM badge_list
hooop-# WHERE sender_name like '久川%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on badge_list (cost=11.26..740.51 rows=291 width=13) (actual time=0.236..0.560 rows=290 loops=1)
Filter: ((sender_name)::text ~~ '久川%'::text)
Heap Blocks: exact=138
-> Bitmap Index Scan on index_id (cost=0.00..11.19 rows=290 width=0) (actual time=0.185..0.185 rows=290 loops=1)
Index Cond: (((sender_name)::text >= '久川'::text) AND ((sender_name)::text < '久州'::text))
Planning time: 2.937 ms
Execution time: 0.643 ms
(7 rows)
hooop=#
後方一致のワイルドカードを使用した場合（インデックスが効かない想定）
hooop=# EXPLAIN ANALYZE
hooop-# SELECT sender_name
hooop-# FROM badge_list
hooop-# WHERE sender_name like '%善法';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on badge_list (cost=0.00..1802.03 rows=291 width=13) (actual time=0.066..10.778 rows=290 loops=1)
Filter: ((sender_name)::text ~~ '%善法'::text)
Rows Removed by Filter: 24032
Planning time: 0.153 ms
Execution time: 10.824 ms
(5 rows)
hooop=#
インデックスが効いていないことをもわかりますし、実行速度も雲泥の差になっています。
まとめ
SQLのパフォーマンスチューニングをする際は、
1. 実行計画を読んで現状を把握する。（ANALYZEして実際の実行結果も見た方が良い）
2. SQLの変更が出来そうなら、パフォーマンスの優れたSQLに変更
3. indexを使い全体を最適化し実行計画を変更
4. 部分indexを使い部分最適化
のような手順で進めていけばいいかと思います。