SQLの最適化に役立つツール「Query Plan Viewer」

SQL の実行時間がシステム全体のパフォーマンスに影響することがあります。従いまして、開発者であれば SQL の実行時間をいかに短縮させるかについて考える必要があります。今回は、SQL の実行時間を短縮するための一つの方法として、クエリの最適化方法を判断できるツール「Query Plan Viewer」を紹介します。

クエリの構文を処理するために Zen のエンジンが選択した実行計画を、Query Plan Viewer を使えば診ることができます。最適なクエリを書くためには、高度な知識と経験が求められることがありますが、「Query Plan Viewer」を使いこなすことができれば、知識、経験の不足分をある程度カバーできると考えています。

今回は「Query Plan Viewer」の使い方を簡単に説明しますが、ほとんどの方は使ったことがないと思いますので、操作画面のキャプチャを多く添付しています。一通りの説明をした後に、想定したインデックスが使われているかどうかの観点で例を挙げます。

そもそもクエリの実行時間が遅くなる原因は？

クエリの実行時間が遅くなる主な原因は2つあると考えています。一つ目は「リソース不足」、二つめが「実行計画が最適ではない」になります。

一つ目の「リソース不足」についてですが、メモリ不足によりディスクとキャッシュ間のデータページの頻繁なスワップ（ディスクI/O）が発生することで、パフォーマンスが相当に低下するという事象がよく発生します。これは皆さま、経験があるのではないでしょうか。（本ブログでは「リソース不足」について詳しく触れることはいたしませんので、詳細については AGBP サービスの技術資料に公開している「Actian Zen のパフォーマンス改善について」を参照ください。）

今回は、二つ目の「実行計画が最適ではない」について、次のような解決方法を紹介します。

実行計画とは

実行計画とは、クエリを実行するための手順です。例えば、テーブルからのデータ取得方法やテーブルの結合方法といった手順が設定されています。基本的には、Zen のエンジンによって最適な実行計画を作成してくれますが、場合によっては、ユーザの意図した通りに作成されません。そういった点から、想定通りの実行計画となっているかを確認することは非常に重要です。

実行計画を確認するフェーズとしては設計段階があり、そこではいくつかのクエリ候補があった場合、実行計画を確認することで最適なクエリを選択することが可能となります。また他には、運用段階で明らかにパフォーマンスの低下を感じた時に実行計画を確認することも有効です。
（私の失敗談ですが、インデックスが効いてない状態でシステムを運用してしまったことにより、月次処理で大量のデータを累積させてしまったことがあります。数か月後に異常に気が付き、先輩に実行計画を見せつけられました。）

同じ抽出結果でも SQL の書き方は複数ありますが、パフォーマンスに違いがある場合、実行計画には違いがあるはずです。実行計画を確認する癖をつけると Zen SQL の知見が深まります。

Query Plan の作成方法について

それでは Query Plan Viewer の使い方を説明します。
下記のステートメントは、Zen Control Center （v13 以前は PSQL Control Center）内から実行しています。

１）Query Plan を有効にします。
SET QRYPLAN=<on | off>

２）クエリプランファイルの場所と名前を設定します。
SET QRYPLANOUTPUT=＜ファイルパス＞

３）SQL クエリを実行します。
この例では Demodata の Person テーブルからデータを抽出します。

４）SET QRYPLAN = off を実行して、クエリプランの作成を無効にします。

これで、クエリプランファイルが作成されました。クエリプランファイルを作成するだけでしたら、簡単な作業ということが分かりますね。

実行計画の確認方法について

次に、実行計画の確認方法を説明します。

１）Zen Control Center で、［ツール］>［Query Plan Viewer］をクリックします。

２）［ファイル］>［開く］メニューをクリックします。

３）先ほど作成したクエリプランファイルを選択してください。
すると、Query Plan Viewer と Plan Viewer が表示されます。

 

Plan Viewer に表示されている色付きの図形が、実行計画になります。初見となる読者の大半の皆様は意味がよくわからないと思います。Plan Viewer の見方については、ここでは重要なポイントに絞って説明します。詳細につきましては、下記のマニュアルをご参照ください。
https://www.agtech.co.jp/products/actian/docs_portal/Zen/15.2/index.html#page/sqlref%2Fqpv.htm%23

まず、クエリに応じて、異なる種類のノード（色付きの図形）が現れます。各ノードは、クエリの実行のステップを表しており、下から上のノードにかけて実行しています。各ステップのノードについて説明します。

一番下の赤い長方形はデータベース内のテーブルからデータを得ることを表しています。

直下に表示されているものは、テーブル名です。今回は Person テーブルになります。テーブル名の直下に表示されているのは、データを抽出するためのインデックスを表します。（Names） はカラム名ではなく、インデックスの名前になります。
（以下 Person テーブルのインデックス情報）

インデックスがなければ、Plan Viewer には表示されません。今回の実行計画では、Person テーブルから Names というインデックスを使ってデータを抽出するということが分かります。

＜インデックスの選択方法について＞ インデックスを使用するクエリの場合、インデックス内の行数や一意の値の数など、stat コールから取得したキーに関する情報を使用します。小さなテーブルの場合は、それ以上の評価は行われません。 複数のインデックスが最適化のために使用される可能性のある大きなテーブルやクエリの場合、SQL エンジンは Find Percentage コールを発行して、どのインデックスがより良いものであるかを判断します。

赤い長方形のノードをダブルクリックすると、下記のポップアップウィンドウが現れ、詳細な情報が表示されます。実際の行数などが確認できます。

 

次に、黄緑色のひし形は、列の選択オペレーションを表しています。

カッコの中は、［Normal］か［Range］のどちらかが表示されます。 [Range] フィルターは、赤い長方形（ベースノードと言います）の真上に現れ、インデックス値に基づきベーステーブルから制限つきのレコードを抽出します。[Normal］フィルターは、列が下流のノードから返ってきた後に適用されます。

Filter（Range）ノードをダブルクリックすると、使用されるインデックスに関する情報、データを絞り込む方法が記載されています。Filter（Normal）ノードには、条件文の内容が記載されています。

これらから、インデックスカラム「Last_Name」「First_Name」を使って、データを絞り込んでいるということが分かります。

以上が Plan Viewer の見方の重要なポイントになります。

注意する点としては、実行計画を出力する前にどのようにデータを抽出するべきかを想定しておく必要があります。そうでなければ、実行計画を見るだけで納得してしまいます。今回の例でいえば、正しく Names インデックスを使ってデータ検索をしているかどうかということになります。複雑なクエリになると実行計画が複雑になり、慣れるまでに時間が掛かると思いますが、ぜひとも使いこなしていただきたいです。

Query Plan Viewer と Plan Viewer の活用例

下記 SQL について、実行計画を確認すると、インデックスが使われていないことが分かります。

Names インデックスを確認すると、複合インデックスの2番目（First_Name）を検索条件に指定しています。

複合インデックスは、インデックスのすべてのカラムまたは最初のカラムだけを検索する場合に役立ちます。2 番目以降のカラムだけを検索する場合には役立ちません。実行計画を確認すると、上記のような間違いに気が付くことができます。

開発者が間違ったクエリ作成またはテーブル設計を行った場合でも、実行計画を見れば簡単に修正ができます。しかし、どちらも理論的に問題ない場合であっても、Zen が最適な実行計画を作成してくれないことがあります。どういうことかと言うと、実行計画はクエリやデータの構造だけではなく、インデックスに登録されているユニークなデータの数や、データ件数が影響する場合もあります。そういった場合、実行計画を見直して正しいインデックスを使うように設定できれば、あなたは相当な Zen SQL マスターと言えるでしょう。

※上級者向けではありますが、テーブル ヒント機能を使用すると、どのインデックスを使用するかを指定することができます。詳細につきましては、下記のマニュアルをご参照ください。
https://www.agtech.co.jp/products/actian/docs_portal/Zen/15.2/index.html#page/sqlref%2Fsyntaxref.03.70.htm%23ww2342624