スポンサーリンク

インデックスを作成して,SQLの速度をチューニングする手順 (PostgreSQLで,EXPLAIN文とCREATE INDEX文によるパフォーマンス改善)

以下の5ステップで,適切なインデックスを作成し,SQLを高速化できる。

※↑ もくじジェネレータ で自動生成

DBはPostgreSQLを想定。



(1)パフォーマンスを改善すべきSQL(もしくはカラム)を特定

まず,インデックスを作成すべきカラムを見極める。

その方法は2つある。


(1−1)ログを閲覧し,実行秒数の大きいものを抽出する。

SQLの実行ログを閲覧する。

たとえば,Ruby on Railsなら,error_levelを :debug にすれば,実行した全SQLとその実行時間が記録される。

PostgreSQLパフォーマンスチューニングのために 〜重いSQLの見つけ方〜
http://blog.asial.co.jp/383

  • postgresql.confの中で,閾値としてlog_min_duration_statementを設定し,重いSQLのみログ出力する

実行時間の大きいSQLや,実行回数の多いSQLを探す。

(1−2)統計テーブルを閲覧し,よく利用されるテーブルを特定する。

アプリケーションがログを出力しなくても,PostgreSQLには「稼働統計情報ビュー」というものがある。

それを見れば,どのテーブルが頻繁に参照されているか識別できる。


テーブルの稼動統計情報を表示するSQL:

SELECT
	*
FROM
	pg_stat_user_tables
;

PostgreSQLのシステムテーブル入門 (暗記用のSQL集)
http://language-and-engineering.hatenablog.jp/entry/20100220/p1

  • 各テーブルがスキャンされた(読み取られた)回数,データが挿入された回数なども合わせて表示される。
  • どのテーブルに CREATE INDEX すべきか,否か?といった判断にも活用できるはず。

参照回数の多いテーブルを特定したら,そのテーブル内のカラムに対する高速化を試みるべきだとわかる。

それらのカラムに対して発行されているSQLを,アプリ内から抽出する。

(2)該当SQLのプランやコストを確認

前項で,高速化すべきSQLが特定できた。


実際の高速化に入る前に,まず,そのSQLに対する「コスト」を算出する。

高速化を施す前後で「コスト」を比較すれば,高速化作業にどれぐらい効果があったのか分かるから。)


コスト算出のためには,EXPLAIN文(またはEXPLAIN ANALYZE文)を使う。

書式:

EXPLAIN 〜; -- 〜の部分にはSELECT文が入る

EXPLAIN文を実行すると,SQLの「実行プラン」が表示される。

詳細な見方は下記を参照。

Explaining Explain 〜 PostgreSQLの実行計画を読む 〜 (PDF版)
http://lets.postgresql.jp/documents/t...


サンプルとして考える状況:

  • 注文テーブルと商品テーブルがある。
  • 注文の外部キーとして商品IDがある。(ol_i_id = i_id)
  • 注文番号が123であるような注文情報を,関連する商品情報と関連付けて閲覧したい。
--プランを表示する
EXPLAIN
SELECT i_id, i_title, i_publisher, i_cost,
       ol_qty, ol_discount, ol_comments
  FROM item, order_line
 WHERE ol_o_id = 123
   AND ol_i_id = i_id
;


                               QUERY PLAN
-------------------------------------------------------------------------
 Merge Join  (cost=167.41..172.51 rows=7 width=215)
   Merge Cond: ("outer".ol_i_id = "inner".i_id)
   ->  Sort  (cost=22.58..22.59 rows=6 width=102)
         Sort Key: order_line.ol_i_id
         ->  Seq Scan on order_line  (cost=0.00..22.50 rows=6 width=102)
               Filter: (ol_o_id = 123::numeric)
   ->  Sort  (cost=144.83..147.33 rows=1000 width=126)
         Sort Key: item.i_id
         ->  Seq Scan on item  (cost=0.00..95.00 rows=1000 width=126)

読み方:

  • 注文テーブルから条件付 (ol_o_id = 123::numeric)でレコードを取得。(5〜6行目)
    • それを,商品ID(order_line.ol_i_id)カラムでソートする。(3〜4行目)・・・A
  • 商品テーブルから全レコードを取得。(9行目)
    • それを,商品ID(item.i_id)カラムでソートする。(7〜8行目)・・・B
  • AとBをマージして結合する。(1〜2行目)
    • マージ条件は,商品IDの一致("outer".ol_i_id = "inner".i_id)。

SQLと同じで,プランも,ネストの内側から外側に向かって処理が走る。


SQLの出典:

SQLチューニング(2) 〜 実行コスト(EXPLAIN / EXPLAIN ANALYZE)
http://www.postgresql.jp/blog/71

以下は,詳細な読み方。


「Merge Join」とは,テーブルの結合方式のこと。

SQLの中で「JOIN」していなくても,内部的には「JOIN」を使って結合処理される場合がある。


上記のSQLも,「JOIN」と明示的に書いてなくても,内部的にはJoinを使ってデータを処理している。

http://itpro.nikkeibp.co.jp/members/I...

  • 入れ子結合方式(nested loop join)は,データが小さい場合に向いている
  • マージ・ジョイン(merge join)方式は,データ量が多い場合に向いている
  • ソートメモリーが十分にある場合はハッシュ・ジョイン(hash join)が早い


http://d.hatena.ne.jp/tgk/20081225/12...

  • IN句やEXISTS句は,実際には内部ではJOINとして処理される場合がある
  • ここで出てくるのは,ハッシュ結合による「セミジョイン」


「cost」とは,SQLの実行にかかる手間を表現した数値。


PostgreSQLは,SQLを受け取ったら,いちばん手間の少ない処理方式を自動的に探してくれる。(オプティマイザ)

いろいろな処理方式を考えて列挙し,それらのパターンごとに「手間」を算出し,もっとも「手間」の少ないパターンが最終的に選ばれる。


costの算出方法は,以下のページ参照。

EXPLAINのマニュアル
http://www.postgresql.jp/document/pg8...

  • コスト=(ディスクページ読み取り x seq_page_cost)+(スキャンした行 x cpu_tuple_cost)


「rows」「width」は,返却されるレコードに関する情報。

(スライド)PostgreSQL チューニング SQL編
http://www.slideshare.net/kwappa/2009...

  • costの単位は時間ではなく,オプティマイザがプラン選択する際の指標である
  • rowsは返却されるレコード数の推定値
  • widthは返却されるレコードの長さ(データサイズ)
  • seq scan撲滅を目指せ
  • 不要ソートを撲滅せよ


「Filter」とは,行に対する条件のこと。

WHERE句は,「どの行を結果に含めるか・含めないか」というフィルタの役割をする。


「EXPLAIN」のかわりに
「EXPLAIN ANALYZE 〜」とした場合,SQLの実行にかかる推定秒数が出る。


もし,インデックス作成後に,EXPLAIN ANALYZEで表示される秒数が少なくなれば,

インデックス作成に効果があった事を客観的に判定できる。


(3)該当カラムに対してインデックスを作成

特定済みのカラムに対して,インデックスを作成する。

それらのカラムに対する検索処理が,素早くなる。

CREATE INDEX インデックス名 ON テーブル名 ( カラム名 );


WHERE句内に複数のカラムがAND条件で結合している場合,複合インデックス

CREATE INDEX インデックス名 ON テーブル名 ( カラム名1, カラム名2 );


インデックスの削除方法:

DROP INDEX インデックス名;

PostgreSQL【インデックス】
http://kichon.net/wiki/wiki.cgi?page=...


PostgreSQLチューニング実践テクニック:高度なインデックスの活用
http://thinkit.co.jp/free/marugoto/2/...

(4)インデックスが作成されたことを確認

前項のCREATE INDEX文が成功したことを確認する。


DB内のインデックスを一覧表示するSQL:

--インデックス名と,インデックス対象のカラム番号を表示

SELECT
	relname, indkey
FROM
	pg_index pgi
		LEFT JOIN
	pg_class pgc
		ON
	pgi.indexrelid = pgc.oid
;

groovyとPostgreSQLでインデックスを一覧表示する
http://groovyarekore.blogspot.com/200...

  • psqlにはコマンドがあるが,SQLで簡単に行なうのは難しい


PostgreSQLでのテーブルインデックスの列挙
http://wiki.jyo.jp/index.php?pg_%A5%A...


pg_indexのマニュアル
http://www.postgresql.jp/document/pg8...

  • indkeyはこのインデックスがどのテーブル列をインデックスとしているかを示す。
  • たとえば、1 3 は 1 番目と 3 番目のテーブル列がインデックスキーとなっていることを示す。


pg_classのマニュアル
http://www.postgresql.jp/document/pg8...

  • pg_class カタログはテーブルと、その他に列を保有しているもの、あるいはテーブルに似たすべてのものを目録にしている。



(5)SQLのプランやコストが改善されたことを確認

インデックスが意図通りに機能していることを確認する。


再度EXPLAIN:

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..47.08 rows=7 width=215)
   ->  Index Scan using order_line_pkey on order_line  (cost=0.00..17.07 rows=6 width=102)
         Index Cond: (ol_o_id = 123::numeric)
   ->  Index Scan using item_pkey on item  (cost=0.00..4.99 rows=1 width=126)
         Index Cond: ("outer".ol_i_id = item.i_id)

ちゃんとインデックスが利用されている。


また,コストや秒数も,インデックス作成前と比べて減っていることを確認する。

もし減っていなかったら,そのインデックスを削除。

インデックス作成方法を変えたり,対象カラムを変えたりして,再度インデックスを作成し直す。



補足

その他の高速化方法:

PostgreSQLを高速化する16のポイント
http://neta.ywcafe.net/000960.html

  • 100ミリ秒単位でページ表示を遅らせるA/Bテスト(条件を変えて2つのサービスを同時に公開するテスト)で、非常に小さな遅延ですら、収入に大きく響いてくる

運用中にインデックスを再構築したい場合:

CREATE INDEXを使いこなす
http://d.hatena.ne.jp/matsuou1/200903...

  • 夜間等、使用されない時間帯が決まっている場合、定期的にメンテナンスの時間が確保できる場合は、reindexを行う



関連する記事:

DBの「トランザクション分離レベル」が必要な理由  (PostgreSQLで,ファントム・リードを防止すべきサンプル事例)
http://language-and-engineering.hatenablog.jp/entry/20110104/p1


データベースとSQLの業務スキルレベル 判別表 (5段階)
http://language-and-engineering.hatenablog.jp/entry/20110320/p1


PREPARE文と,PL/pgSQL の入門  (PostgreSQLで「動的に」SQLを実行するために,プリペアド・クエリやストアドファンクションを定義しよう)
http://language-and-engineering.hatenablog.jp/entry/20110218/p1


「相関サブクエリ」とは何かを理解して,複雑なSQLでも読めるようになろう
http://language-and-engineering.hatenablog.jp/entry/20101108/p1