kazu22002の技術覚書

技術屋として日々の内容を記録しているサイト。PHPやcordovaをやっています。

SQLのパフォーマンス改善 その1

[Postgres]SQLのパフォーマンス改善を最近よくやっていました。

やっと成果が出てきたので、少しやり方を書いてみます。

スロークエリを見る

まずどのSQLが重いのか、パフォーマンスが悪いのかを見つけることが重要だと思います。

最初はふつうに作って、速度的に動作すれば問題ないと思います。開発環境でやってても重くない場合でもデータ量によって重くなる場合がかなりあるので、実環境でスロークエリを出力するようにしておくといいと思います。

lets.postgresql.jp

エラーのクエリも出力しておくのも必須だと思います。条件によってエラーがでている場合もありますので。

スロークエリを見てまずdurationを見てみます。

''' duration: 4016.724 ms SELECT * FROM table;

'''

こんな感じでdurationと時間がログとして出ています。

SQLの実行結果で設定時間以上の場合に出力されます。

設定についてはpostgresql.confで

''' log_min_duration_statement = 3s '''

の部分で設定されています。サービスしだいで設定を変更するといいと思います。

durationは実行した時間が出ますので、時間がかかっているクエリから修正をしていくといいと思います。

クエリのコストを確認

重いクエリを見つけてからはどの部分が重くしているのかを探してみます。

ここからはクエリを実行して実際に重いのかを確認していきます。

ほかのクエリによって重い場合もあるので、実行してみて本当に重いのか確認し、重い場合はexplainでコストを確認してみます。

実行クエリの前に「explain」をつけて確認します。

''' explain SELECT * FROM table; '''

表示される内容でcostやrowsの内容を確認し、重そうな部分を探していきます。

探す順番ですが、

  • インデックスが効いていない箇所を探す
  • 件数が過剰ではないか
  • サブクエリ
  • 関数での計算
  • ソート
  • DISTINCT

まず重い部分の察知として「seq」を探します。

「seq」とは・・・「シーケンシャルスキャン」

https://www.postgresql.jp/document/8.3/html/using-explain.html

シーケンシャルスキャンですが、indexが使われていない検索になっています。

indexを使用するようにするだけで早くなる可能性はかなりあります。indexを張るだけで効果がでれば十分です。

検索に使用している部分でindexを作成するだけで変わりますので、頻繁に検索をする部分のカラムにはindexを使用することをお勧めします。

indexが使われていないと全件検索を実行しているので、CPUとメモリが大量に使用されます。

よってサービスに過剰な負担がかかってしまうので、常に気にしておくといいと思います。

長くなってしまったので、次回に

もうすこしやっている内容を書いてみたいので、次回。

広告を非表示にする