次の方法で共有

第 2 回 「クエリの実行方法を理解する」～ システム構築 ～

  • [アーティクル]

NEC

Eラーニング事業部

鈴木 智行

2001年11月30日

目次

2-1. 実行プラン 2-1. 実行プラン
2-2. グラフィカルな実行プラン 2-2. グラフィカルな実行プラン
2-3. 論理演算子を使用したクエリ 2-3. 論理演算子を使用したクエリ
2-4. ネスト化ループ結合を使用したクエリ 2-4. ネスト化ループ結合を使用したクエリ
2-5. マージ結合を使用したクエリ 2-5. マージ結合を使用したクエリ
2-6. ハッシュ結合を使用したクエリ 2-6. ハッシュ結合を使用したクエリ
2-7. クエリオプティマイザ 2-7. クエリオプティマイザ
2-8. 統計情報 2-8. 統計情報
2-9. オプティマイザヒント 2-9. オプティマイザヒント

2-1. 実行プラン

第 1 回で説明したとおり、1 つのテーブルに対するクエリの実行方法は、たとえクラスタ化インデックスや非クラスタ化インデックスがテーブルに設定されていたとしても、データ量や検索条件などの様々な条件によっては、インデックススキャンではなくテーブルスキャンのパフォーマンスが良い場合も考えられます。これは複数テーブルであっても同様なことがいえるでしょう。以上のことからも、クエリを発行したときに SQL Server がどのようにクエリを実行したかを考察することは、パフォーマンスを評価する上で非常に重要だと考えることができます。

SQL Server ではクエリが内部でどのように実行されているかを実行プランとして非常に簡単に表示できます。Transact-SQL では set showplan_all on または set showplan_text on で設定可能であり、実行プランをテキスト行として返します。このステートメントを使用した場合は、実際に解析するクエリ自身は実行しません。

showplan_all の方が showplan_text に比べ、より細かい情報を出力してくれます。ここでは、set showplan_text on を設定して出力結果を確認してみましょう。

Northwindサンプルデータベースに対し、次のようなクエリを実行すると、次の結果が得られます（図2-1-1）。

  • クエリ

    set showplan_text on

go select CompanyName from Customers where CustomerID = N'ALFKI'

  • 結果

    |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), SEEK:([Customers].[CustomerID]=[@1]) ORDERED FORWARD)

図

図 2-1-1 Northwind サンプルデータベースに対しクエリを実行する

次に Northwind サンプルデータベースと同じ構成で、インデックスだけを削除した CopyNorthwind データベースに対して同じクエリを実行します（図2-1-2）。

  • 結果

    |--TableScan(OBJECT:([CopyNorthwind].[dbo].[Customers]), WHERE:([Customers].[CustomerID]=[@1]))

図

図 2-1-2 CopyNorthwind データベース（インデックスなし）に対しクエリを実行する

Clustered Index Seek と Table Scan という違いからわかるように、この環境下ではNorthwind サンプルデータベースに対してはインデックススキャン、CopyNorthwind データベースに対してはテーブルスキャンを実際に実行していることがわかります。

2-2. グラフィカルな実行プラン

クエリアナライザでは実行プランをテキストではなく、グラフィカルに表示することができます。[クエリ]メニューの[推定実行プランの表示]オプションでは実際に解析するクエリ自身を実行せずに、また[クエリ]メニューの[実行プランの表示]オプションではクエリ自身を実行して、実際の実行プランを表示することができます。では前と同じクエリを[推定実行プランの表示]オプションを使用して実行してみましょう。

図2-2-1 は Northwind サンプルデータベースに対してのクエリ推定実行プランを表示しています。Clustered Index Seek のアイコンによってクラスタ化インデックスが使われていることがわかります。

図

図 2-2-1 Northwind サンプルデータベースに対してのクエリ推定実行プランを表示する

図2-2-2 は Northwind サンプルデータベースと同じ構成で、インデックスだけを削除した CopyNorthwind データベースに対してのクエリ推定実行プランを表示しています。

Table Scan のアイコンによってテーブルを直接（もちろん IAM を利用して）検索していることがわかります。

図

図 2-2-2 CopyNorthwind データベース（インデックスなし）に対してのクエリ推定実行プランを表示する

SQL クエリ アナライザのグラフィカルな実行プランの出力は、右から左、上から下に読みます。もちろん結果は実行プランをテキスト表示した場合と変化はありませんが、クエリの実行がアイコンによって表され、より見やすくなったことがわかります。

2-3. 論理演算子を使用したクエリ

ではグラフィカルな実行プランを使用して様々なクエリの実行方法を確認していきたいと思います。ここでは AND 論理演算子を使用した例をみていきましょう。

例えば Northwind サンプルデータベースに対し、次のクエリを実行します。

  • クエリ 1

    select  OrderID, CustomerID,EmployeeID,OrderDate from Orders

where CustomerID = N'BOTTM' and EmployeeID = 3

実行プランからは次のことがわかります（図2-3-1）。

  1. CustomerID = N'BOTTM' の条件に適合するインデックスを CustomerID 非クラスタ化インデックスから取得する

  2. EmployeeID = 3 の条件に適合するインデックスを EmployeeID 非クラスタ化インデックスから取得する

  3. 上記の 2 つの結果から CustomerID = N'BOTTM' and EmployeeID = 3 の条件に適合するインデックスを結合操作で取得する

  4. インデックスに対応する行を Bookmark Lookup 操作でデータベージにジャンプし、取得する。

図

図 2-3-1 Northwind サンプルデータベースに対してクエリを実行すると 2 つの非クラスタ化を使用して結果を取得する

一般的に AND 論理演算子では、各検索条件に対してインデックスがあれば、インデックスを使用してデータを取得します。しかし AND 論理演算子の解決方法は同じとは限りません。以下のクエリを Northwind サンプルデータベースに対し、次のクエリを実行します。

  • クエリ 2

    select  OrderID, CustomerID,EmployeeID,OrderDate from Orders

where OrderID between 10400 and 10449 and CustomerID = N'BOTTM'

実行プランからは次のことがわかります（図2-3-2）。

  1. OrderID between 10400 and 10449 and CustomerID = N'BOTTM' の条件に適合するデータを PK_Orders（OrderID のクラスタ化インデックス）から探し、リーフレベル（実際のデータページ）で取得する。

図

図 2-3-2 Northwind サンプルデータベースに対してクエリを実行するとクラスタ化インデックスだけを使用して結果を取得する（ CustomerID 非インデックスは使用しない）

他にもインデックスが有用でなければ一方を使用しなかったり、両方使用せずに、テーブルスキャンでアクセスすることもあります。

2-4. ネスト化ループ結合を使用したクエリ

クエリでは結合演算を利用するケースが多く存在します。SQL Server の結合演算の処理方法を理解することは、インデックスの有効性やパフォーマンスを評価するために重要です。ここではその中でネスト化ループ結合を使用したクエリをグラフィカルな実行プランを使用してみていきましょう。

通常、結合はネスト化ループ結合として処理されます。ネスト化ループ結合は複数のテーブルの処理を次の手順で示されたネストされた繰り返しで実行します。

  1. 最初のテーブルから行を取得する

  2. その行を使用して次のテーブルをスキャンし、結合して、限定された行を取得

  3. 前の結果を使用して次のテーブルをスキャンし、結合して、限定された行を取得

  4. ....... 以下繰り返し

Northwind サンプルデータベースに対し、次のクエリを実行します。

  • クエリ

    select ord.OrderID, cu.CompanyName,em.LastName,em.FirstName,ord.OrderDate

from Orders as ord inner join Customers as cu on ord.CustomerID = cu.CustomerID inner join Employees as em on ord.EmployeeID = em.EmployeeID where ord.OrderID between 10400 and 10449 and cu.CustomerID = N'BOTTM'  and em.EmployeeID = 4

実行プランからは次のことがわかります（図 2-4-1 ）。

  1. CustomerID = N'BOTTM' の条件に適合する行を Customers テーブルから CustomerID クラスタ化インデックスを用いて検索し、その行を使用して Orders テーブルをスキャンし、OrderID between 10400 and 10449 の条件に適合する行を OrderID クラスタ化インデックスを用いて検索して取得し、結合する

  2. その結果を使用して Employees テーブルをスキャンし、EmployeeID = 4 の条件に適合する行を Employee クラスタ化インデックスを用いて検索して取得し、結合する

  3. 要求された列を選択する。

図

図 2-4-1 Northwind サンプルデータベースに対してクエリを実行するとネスト化ループ結合が使用される

2-5. マージ結合を使用したクエリ

次にマージ結合を使用したクエリをみていきます。マージ結合は SQL Server 7.0 から導入されました。マージ結合はその名前から容易に想像できるとおり、2 つの並びかえられた入力を使用してその 2 つをマージする結合方法です。

したがって結合される列は並び替えられていることが必要ですが、必要なページ I/O は少なくなり、多くの場合で最速のアルゴリズムを提供します。

Northwind サンプルデータベースに対し、次のクエリを実行します。

  • クエリ

    select ord.OrderID,em.LastName,em.FirstName,ord.OrderDate

from Orders as ord inner join Employees as em on ord.EmployeeID = em.EmployeeID where ord.OrderID between 10400 and 10449 or em.EmployeeID between 4 and 6 order by em.EmployeeID

実行プランからは次のことがわかります（図2-5-1）。

  1. OrderID between 10400 and 10449 の条件に適合する行を Orders テーブルから OrderID クラスタ化インデックスを用いて検索し、取得する

  2. 結果をEmployeeIDの昇順に並び替える

  3. EmployeeID between 4 and 6 の条件に適合する行を Employees テーブルか らEmployee クラスタ化インデックスを用いて検索して行を取得する（クラスタ化インデックスなので既に行は並び替えられている）

  4. 上記の 2 つの結果を突き合わせて結果を取得する。

図

図 2-5-1 Northwind サンプルデータベースに対してクエリを実行するとマージ結合が使用される

マージ結合のパフォーマンスは高速ですが、上記のクエリのように必要なデータを事前に並び替えていない場合は、処理中に並び替えをする必要があります。この処理中の並び替えがパフォーマンスに影響を与える場合があるので注意が必要です。

2-6. ハッシュ結合を使用したクエリ

最後にハッシュ結合を使用したクエリをみていきます。ハッシュ結合もマージ結合同様に SQL Server 7.0 から導入されたものです。ハッシュとは指定のプロパティや特徴に基づいて、管理可能な同じサイズのセットにグループ化し、データを分割することです。他グループを調べることなくグループ内のデータを見つけることが可能なので、インデックスが存在しない場合や既存のインデックスが有用でない場合に使用されます。

CopyNorthwind データベース（インデックスが設定されていないデータベース）に対し、次のクエリを実行します。

  • クエリ

    select ord.OrderID,em.LastName,em.FirstName,ord.OrderDate

from Orders as ord inner join Employees as em on ord.EmployeeID = em.EmployeeID where ord.OrderID between 10400 and 10449 or em.EmployeeID between 4 and 6  

実行プランからは次のことがわかります（図2-6-1）。

  1. OrderID between 10400 and 10449 の条件に適合する行を Orders テーブルからテーブルスキャンし、取得する

  2. EmployeeID between 4 and 6 の条件に適合する行を Employees テーブルからテーブルスキャンし、取得する

  3. 上記の 2 つの結果をハッシュ結合し、結果を取得する。

図

図 2-6-1 CopyNorthwind データベースに対してクエリを実行するとハッシュ結合が使用される

マージ結合と同様に、ハッシュ結合も非常に効率的ですがデータの重複度が高いとハッシュによってデータが均等に分散されず、パフォーマンスに影響を与える場合があるので注意が必要です。

2-7. クエリオプティマイザ

クエリオプティマイザは Transact-SQL ステートメントを評価し、最も効率的な実行プランを決定するコンポーネントです。結合方法、結合順およびインデックスのすべての組み合わせについて、論理読み取り数、および必要かつ使用可能なメモリリソースを考慮してコスト（I/O と CPU リソースの消費度）を予測し、予想コストが小さくかつ最も結果を早く返す実行プランを選択します。

クエリオプティマイザが携わるクエリの最適化プロセスには以下の 3 つのフェーズがあります。

  • クエリの分析（第 1 フェーズ）
    クエリオプティマイザはクエリの検索と結合の基準を識別します。すなわち検索を絞り込むことで対象データを制限するのに役立つかどうかを判断します。対象データが制限されれば、処理される行の数が減少し、I/O コストが減少します。

  • インデックスの選択（第 2 フェーズ）
    第1フェーズで検索と結合の識別ができたら、その識別句に対しインデックスの有無を検出します。存在する場合にはそのインデックスの効果を評価します。インデックスの効果は句の選択効率（何行返されるか）から判断します。条件を満たす行を検索するためのさまざまなアクセス方法による I/O コストを見積もって決定します。

  • 結合の選択（第 3 フェーズ）
    クエリが複数のテーブルに対するもの、あるいは自己結合である場合には、どの結合方法を使用するか評価します。コストを見積もるには、読み取りの予想値や必要な記憶容量など、多数の要因が考慮されます。

したがって、適切な状況下では皆さんの指定が特になくても、上記のフェーズを経てクエリオプティマイザにより、発行されたクエリをコストベースで最適に実行へと導いてくれます。

2-8. 統計情報

統計情報とは列値のサンプリングデータから生成された分布ステップ情報です。分布ステップの内容や構造はあまり意識しなくても構いませんが、統計情報そのものはクエリの最適化に非常に大きな意味を持ちます。なぜならばクエリオプティマイザがコストを予測するときには、実際のデータではなく統計情報を元にコストベースの判断を行うからです。しかし通常、SQL Server では統計の作成を意識する必要はありません。

インデックス作成時にその列に対して、統計情報が自動作成されますし、データベースオプションのauto create statistics（統計の自動作成）がデフォルトでON（modelデータベースをカスタマイズしていない限り）になっているので、クエリオプティマイザの判断材料として必要な場合には、インデックスを持たない列に対しても統計は自動作成されます。作成された統計情報はsysindexesシステムテーブルのstatblob列に格納されます。

また統計情報はインデックス付けがされていない列に関して CREATE STATISTICS ステートメントを使用して手動作成できます。CREATE STATISTICS ステートメントではテーブル内の行数をもとにしてサンプリングする行の比率を指定できます。FULLSCAN オプションを指定した場合には統計を収集するためにテーブルの全ての行を読み取ります。SAMPLE オプションを指定した場合には統計を収集するために無作為抽出を使用してテーブルの一定のパーセンテージまたは行数を読み込みます。

図2-8-1 は CopyNorthwind（インデックス付けされていない）データベースの Employees 列に明示的に統計情報を作成した後に、クエリしたものです。

sp_autostats システムストアドプロシージャで対象テーブルの統計情報が出力されます。[static_EmployeeID] が明示的に作成した情報で、 [_WA_Sys_OrderID_7D78A4E7] が SQL Server が自動的に生成した OrderID 列の統計情報です。

図

図 2-8-1 統計情報には静的に作成するものと自動的に作成されるものがある

しかしデータベースオプションの auto create statistics（統計の自動作成）がONになっていないと SQL Server は自動的に必要な統計情報を作成してくれません。この場合クエリオプティマイザは判断を誤ってしまう可能性があります。クエリアナライザではこのようなケースでは警告表示してくれるので、不足した統計情報を生成してください（図2-8-2）。

図

図 2-8-2 統計情報が不足した場合は生成する

2-9. オプティマイザヒント

適切な統計情報をクエリオプティマイザに提供すれば、クエリオプティマイザはコストを基準として最適な実行プランを選択します。しかしクエリオプティマイザは基本的に確率に基づいて実行プランを選択しているため、適切な統計情報を与えたとしても数％は誤った選択をする可能性があります。そのような場合には、私達はオプティマイザヒントを利用してクエリオブティマイザの選択をオーバーライドします。

オプティマイザヒントとして、以下の設定が可能です。

  • テーブルヒント（図2-9-1）
    テーブル ヒントは、テーブル スキャン、クエリ オプティマイザが使用する 1 つ以上のインデックス、またはこのテーブルとこの SELECT でクエリ オプティマイザが使用するロック手法を指定します。テーブルに対する with キーワードで指定します。例えばインデックスに関しては index 引数を使います。

    • index（インデックス名）は指定したインデックスの使用を指示

    • index(0) はテーブルスキャンを指示（クラスタ化インデックスがない場合）、あるいはクラスタ化インデックスのスキャンを指示（クラスタ化インデックスがある場合）

    • index(1) はクラスタ化インデックスのスキャンまたはシークを指示
      （クラスタ化インデックスがある場合）。あるいはエラーを返します（クラスタ化インデックスがない場合）

図

図 2-9-1 Orders テーブルに対して EmployeeID インデックスを強制的に使用して検索する

  • 結合ヒント（図2-9-2）
    結合ヒントは、クエリの FROM 句内で 2 つのテーブル間に結合方法を設定します。例えば Loop、Merge、Hash の指定はそれぞれネスト化ループ結合、マージ結合、ハッシュ結合の使用を指示します。

図

図 2-9-2 Orders テーブルと Employees テーブルを強制的にマージ結合で結合して検索する

  • クエリヒント（図2-9-3）
    クエリヒントはさまざまな操作を制御することができます。OPTION 句を指定してクエリ用に特定のヒントを使用するようにクエリオプティマイザを指定することができます。指定のクエリ ヒントはクエリ全体をとおして影響を与えます。また複数のクエリヒントを使用することができますが、各クエリヒントを指定できるのはそれぞれ 1 回だけです。たとえば FORCE ORDER は FROM 句に現れる順番でテーブルが結合されます。これと同等なことは SET FORCEPLAN ステートメントで設定することが可能です。

図

図 2-9-3 Orders テーブル、 Customers テーブル、 Employee テーブルの順番で強制的に結合する

sysbuild2.gif

鈴木   智行 : NEC Eラーニング事業部に所属。入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。 Windows 2000 および SQL Server 2000 での MCSE,MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア（データベース）も取得済。 SQL Server の優位性をアピールできるように Oracle Gold も取得した。今後 Oracle Plutinum を取得予定し、日々データベースを極めることに努力している。