Entry Web

SQL 201:実行速度の遅いクエリを見直す10の方法

2019-04-15

author:

SQL 201:実行速度の遅いクエリを見直す10の方法

Helen Anderson      
AWSの「ビルダー」でもあるデータ愛好家。Xero社のBIデータアナリスト兼テクニカルコンサルタント。

要件把握良し、テーブル確認良し、テーブル結合も良し、いつでもクエリを実行させられる準備を整えたあなた。

クエリを走らせます。

ラン。

そしてラン・・・

ブチ切れるかDBAを呼ぶ前に、下記の方法を確認し、クエリを改善させてみてください。

この記事はPostgreSQLを基に書いていますが、他のDBMSでも当てはまる所があるかもしれないので、一度確認しておいてください。

1 – すべてはタイミング

最初に確認しておきたいこと。データベースへの負荷はどれぐらいかかっていますか。たくさんのユーザーがデータベースのメモリを奪い合っている時、複雑なクエリを実行していませんか。

戦略:

データベースに直接聞くことで、どれぐらい負荷がかかっているのか確認できます。会社の全員にいちいち確認を取らなくても大丈夫です。

あなたの会社の心優しいDBAさんにより制限されている場合、実行結果は異なるかもしれません。

select *
from pg_stat_activity

/* This lists all queries currently running and idle. */


select 
pid, 
usename, 
pg_blocking_pids(pid) as blocked_by, 
query as blocked_query
from pg_stat_activity
where 
cardinality(pg_blocking_pids(pid)) > 0;

/* This lists queries causing locks. */

2 – ロックアウト

該当テーブルは更新中ではないですか? ETLプロセスで更新を行っているテーブルを触る場合は、更新自体をストップさせてしまう恐れがあり、クエリは実行されません。

戦略:

ETL処理を行っているチームに更新完了予定を確認してください。

3 – 全部はいらない

上記の2つをクリアしたら、DBAやクエリプランナーに頼る前に、次はクエリそのものを見直せないか確認してみましょう。

最初の行から。SELECT *は本当に必要ですか?

戦略:

  • テーブル内に何が入っているのかを確認したい場合は、スキーマツリーのカラムリストを展開してみてください。
  • さらにパフォーマンスを上げるために、SELECT *の代わりに、必要なカラム名で絞ってクエリを作成してください。
  • 特に大きなテーブルやカラムの多いテーブルがある場合、すべてをクライアント側に引き戻すそうとすると、クエリエンジンにたくさんの負担がかかります。すべての行を「綿密に」確認しないといけない場合以外、「LIMIT」制限を使い結果を出すようにしてください。
  • COUNT結果が欲しいのであれば、普通のクエリを実行させ、結果の画面下に表示されるCOUNT数を見るのではなく、サブクエリを使いカウントを直接だしてください。
select count(*) from

(
    select
    userid
    from userschema.usertable
    where market = 'UK'
    and payingcustomer = 1

) as derivedtable;

4 – 大文字・小文字変換

PostgreSQLでは大文字・小文字を区別するので、SQL Serverを使っていた人には慣れるまで時間がかかるかもしれません。

戦略:

  • データを「小文字」か「大文字」にする作業は相当な時間を要する作業です。どうしても必要な場合にのみ行ってください。クエリに一行追加する前に、まずデータがどのような形になっているか確認してください。
  • JOINで必要な場合は、片側だけで使用するか、大文字と小文字を区別しないで検索するILIKEを使用してみてください。

5 –  NOT INではない

「IN」または「NOT IN」を入れると、クエリエンジンはすべての行を調べて条件が満たされているかどうかを一々確認する「テーブルフルスキャン」を行うので、できるだけ避けた方がいいでしょう。

戦略:

  • ‘EXCEPT’または ‘NOT EXISTS’を使ってみてください。’NOT IN’よりだいぶ負荷が少ないです。

6 – CTEにするかどうか

CTEはサブクエリよりも読みやすいです。しかしPostgreSQLでは、クエリオプティマイザーの機能の妨げになります。ネストされたCTEの制約(constraints)の位置を書き変えたりするクエリ最適化ができなくなるためです。

戦略:

  • CTEとサブクエリはどちらも便利です。特定のケースでは、どちらかが片方よりパフォーマンスが良い場合があります。CTEを使う時は、テーブルの大きさや結果の行数、実行されるアクションを考慮した上で使ってください。

SQL 201: Why you should use SQL CTEs
なぜSQL CTEを使うべきなのか


7 – ワイルド、ワイルド、ワイルドカード

LIKEの始めと終わりにワイルドカードを使うと、実行スピードが遅くなります。そのうえ、意図より多い結果が返されるかもしれません。

戦略:

  • ワイルドカードは必要なときだけ使ってください。ワイルドカードは一般的に片側だけに要求されるので、クエリエンジンの動き方を意識しながら使うようにしてください。
  select
    firstname, lastname, userid
    from userschema.usertable
    where lastname like 'anders%' -- only on the end of the string

8 – テーブル一つにまとめる

関数化している多重ネストクエリはデータベースへの負荷が高いです。その場合は、テーブルに書き込んでおいた方が早い場合があります。

戦略:

  • 実行プロセスが多い場合には、ステージングテーブルを作成するのもありです。その後は、小刻みの結果データを結合すればいいだけです。

SQL 101: Relational Database Fundamentals
リレーショナルデータベースの基礎


9 – ビューの上のビューの上のビュー

ビューは、ビューにアクセスする度にクエリが実行されます。複数のビュー、または(もっとパフォーマンスの悪い)ビューの積み重ねを呼び出している場合、あなたはクエリエンジンに何度もクエリを回すようにしているわけです。

戦略:

  • 毎日/週/月ごとのように定期的にデータを抽出したい場合は、ビューでフィルタリングするのではなく、テーブルに書き込んでください。
  • ネストしたビューを使っている場合、必要なカラムを抽出するためには、最後にネストされたビューに複数のクエリをかけるより、できるだけ一つのクエリにする工夫が必要です。

10 – インデックス

インデックスはデータベースエンジンが調べる位置や、またはルックアップテーブルをセットします。これにより、クエリ実行を高速化します。インデックスの種類によって、インデックスの動作方法が決まります。

戦略:

  • クエリ内で頻繁に使用するカラムに、カーディナリティや変化の高い順によってインデックスをつけてください。
  • テーブル上のインデックスが多すぎないように、インデックス数を把握しておいてください。

SQL 201: Speed up your queries with Indexes
インデックスを使ってクエリをスピードアップ


以上の方法であなたのクエリが改善できると嬉しいです。世の中には様々なデータベースがあるので、自分にとって一番良い方法を見つけていけばいいでしょう。

DBAにおねだりしてクエリプランを解釈する前に、あなたか今から使える戦略はありましたか?

続きを読む:


Why a Database is like a Dance Class
データベースがダンスクラスのようなものである理由


SQL 101: Concepts from A to Z
AからZまでの概念


SQL 101: The SSMS Users Guide to pgAdmin
pgAdminのSSMSユーザーガイド


この記事は、著者の許可を得て翻訳しています。なお、原文はこちらです。