開発者のための SQL Server パフォーマンス チューニング/最適化講座

第 1 部 ‐ パフォーマンスに関する問題の概要

DAT 410
Presented at Tech-Ed 97

Adam Shapiro
Program Manager
Microsoft Corporation

目次

パフォーマンスのチューニングの目標
応答時間とスループット
パフォーマンスに影響を及ぼす要因
システム リソース
Windows NT オペレーティング システム
SQL Server
データベース アプリケーション
クライアント アプリケーション
どのようなことが可能か
Windows NT の利点
パフォーマンス チューニングの方法論
パフォーマンス チューニングの方法論 (続き)
パフォーマンス チューニングへのアプローチ
概要 ― インデックス付けの方針
目的
DSS と OLTP
データ取得用のインデックス
適切な種類のインデックスの作成
クエリに適したインデックスの選定
インデックス付けのガイドライン
クエリ オプティマイザの概要

パフォーマンスのチューニングの目標

パフォーマンスのチューニングでは、ネットワーク トラフィックの最小化、ディスク I/O の軽減、および CPU 時間の最小化を通じて、すべてのユーザーの処理に対して最大限のスループットを確保し、クエリごとの応答時間を改善することが目標となります。この目標を達成するには、アプリケーションの必要条件を徹底的に分析し、データの論理構造と物理構造を把握するとともに、オンライン トランザクション処理 (OLTP) と意思決定支援のように、互いに競合するデータベース用途を検証し、それらのバランスを調整することが必要になります。

応答時間とスループット

応答時間については、画面を通じてアプリケーションを操作しているユーザーがクエリの処理を速いと感じるか、遅いと感じるか、といった知覚上の時間の長さと見なされる傾向があります。しかし、パフォーマンスのチューニングでは、SQL Server が結果セットの最初の行を返すまでに要する時間の長さを応答時間として計測します。

スループットは、特定の時間内にサーバーで処理できるクエリの総数として計測されます。

ユーザーの数が増えると、ユーザー間の競合も増えます。その結果、応答時間が長くなり、全体のスループットが低下することがあります。

パフォーマンスの計測方法
パフォーマンスは、トランザクションの処理に必要な I/O の量、CPU 時間の量、および応答時間を尺度として計測できます。SQL Server のパフォーマンスは、実際に SQL Server が置かれている環境によって異なり、アプリケーション、アーキテクチャとリソース、サーバー、および同時実行状況に依存します。

パフォーマンスに影響を及ぼす要因

システム リソース

メモリ
SQL Server のパフォーマンスは、十分な RAM 容量があるかどうかによって決定的な影響を受けます。

プロセッサ
プロセッサの数は、個々のプロセッサの速度とともに全体的なパフォーマンスに直接影響します。

ディスク
ディスク ドライブの数、速度、および種類は、コントローラの種類とともにパフォーマンスに影響する要因となります。

ネットワーク
SQL Server のパフォーマンスは、ネットワークの同時接続数によって影響されることがあります。ネットワークの帯域幅とデータ転送速度も重要な要因となります。

Windows NT オペレーティング システム

スレッド
スレッドの優先度を調整すると、SQL Server のニーズとその他のサービスのニーズのバランス、および SQL Server のニーズと Microsoft Windows NT® オペレーティング システム自体のニーズのバランスを調整できます。SQL Server で割り当てるスレッドの数は、パフォーマンスに影響する要因となります。

ページングファイル
ページングファイルのサイズ、数、および保存場所は、システムのパフォーマンスに大きく影響します。

サービス
Windows NT 上でほかのサービスが実行されていると、SQL Server で使用できるリソースの量がその分少なくなります。不要なサービスを停止すると、パフォーマンスに良い影響を与えることがあります。

ディスク管理
Windows NT には、ストライピングやミラー化のように、使い方によってはパフォーマンスに好影響を与えることもあれば、悪影響を与えることもあるディスク管理機能がいくつかあります。

同時実行状況
クライアント プログラムやコンパイラなど、ほかのプログラムが同時に実行されていると、SQL Server で使用できる CPU サイクル、ディスク アクセス、およびネットワーク帯域幅が制限されることがあります。

SQL Server

環境設定
SQL Server の環境設定変数の多くは、サーバーのパフォーマンスに直接影響を及ぼします。

ロック
マルチユーザー環境では、データベース リソース (テーブルや個々のページ) の競合が発生し、プロセスがブロックされると、全体的なパフォーマンスが悪影響を受けます。

ログ
ログの対象外となる特定の操作を例外として、データベースに対する変更はすべてログに記録する必要がありますが、ログへの書き込み自体がパフォーマンスに影響を与えることがあり、トランザクション ログ (syslogs) が競合の発生源になる可能性もあります。

SQL Server に対して同時に実行される操作
データベースのバックアップと復元、DBCCステートメントの実行、インデックスの構築などの保守操作を実施するときには、ユーザーがデータベースを通常どおり使用できなくなる可能性があります。

データベース アプリケーション

論理設計と物理設計
クエリのパフォーマンスは、正規化と正規化解除の一方または両方の影響を受けることがあります。物理設計には、インデックスの選択が含まれます。物理設計については、後で詳しく述べます。

デッドロックの回避
デッドロックが繰り返し発生すると、アプリケーションの実行速度が低下することがあります。デッドロックの発生は、プログラミングで抑えることができます。

トランザクション制御
トランザクション、ロック、およびデッドロックの間には、高い相関性があります。アプリケーションで使用するトランザクション制御のレベルによって、ロックの保持時間が大きく異なり、アプリケーション全体のスループットが左右されます。

クエリ
最適なプランで最大限のパフォーマンスを引き出せるかどうかは、個々のクエリがどのように作成されているか (それらがストアド プロシージャにカプセル化されているかどうかを含む) によって決まることがあります。

クライアント アプリケーション

ユーザーから要求される操作
ユーザーがデータベースに対する変更やクエリの実行を要求することによって、アプリケーションのパフォーマンスが大きく影響されることがあります。

デッドロックの処理
クライアント プログラムから SQL Server のデッドロックに対処するには、さまざまな方法が考えられます。応答を効率化することで、クライアント システムのパフォーマンスを向上できます。

トランザクション制御
トランザクションは、クライアント アプリケーションから制御することもできます。さらに、クライアント アプリケーションには、プログラマやユーザーが明示的に指定しなくてもトランザクション制御ステートメントを透過的に発行できるものがあります。

カーソル
カーソルは、数通りの方法で定義および操作できます。パフォーマンスに与える影響は、方法によって異なります。

どのようなことが可能か

パフォーマンスのチューニングには、科学というより、芸術に近い側面があります。チューニングの目標は、I/O、CPU、ネットワークなどに関連するボトルネックを取り除いて、パフォーマンスを向上することにあります。サーバーのチューニング、データベースのチューニング、プロセスのチューニング、そしてデータの競合の最小化を通じて、システムの処理時間の量を低減することになります。

ハードウェアの増設

• ハードウェアを増設しなくても、もっとコストの低い方法で対処できることもあれば、ハードウェアを増設することで高い効果が得られることもあります。

SQL Server のチューニング

• 環境設定オプションの値を調整します。

データベースのチューニング

• 論理設計と物理設計を改善します。

• クエリを改良します。

• 有用なインデックスを作成します。

競合および同時実行に関する問題の解決

• 読み取り操作と書き込み操作のバランスをとります。

• ロック競合を最小限に抑えます。

• デッドロックを回避します。

クライアントアプリケーションのチューニング

• アドホック クエリの代わりにストアド プロシージャを使用します。

• トランザクションの配分を分析して、優先度を設定します。

• サーバーに対するデータと処理の負荷をできるだけ減らします。

Windows NT の利点

SQL Server には、Windows NT オペレーティング システムの強化点がそのまま活かされています。

スケーラブルアーキテクチャ
Windows NT は、Intel® プロセッサと RISC プロセッサをサポートしており、ノートブック コンピュータから SMP (Symmetric Multiprocessor) スーパー サーバーに至るまで幅広いハードウェア環境で使用できます。このようなスケーラビリティは、そのまま SQL Server にも適用します。

大容量記憶のサポート
SQL Server では、Windows NT でユーザー プロセス用としてサポートされている上限の 2 GB までのメモリを使用できます。また、NTFS を使用した場合は、約 2TB までのハード ディスク パーティションがサポートされます。

SMP (Symmetric Multiprocessing)
Windows NT は、SMP に対応したオペレーティング システムです。オペレーティング システム コードとユーザー コードを、利用可能な任意のプロセッサ上で実行できます。SMP 対応のオペレーティング システムでは、スレッドの数がプロセッサの数より多い場合には、各プロセッサの時間を待機スレッド間で分割して、マルチタスキングを実行します。

SQL Server では、Windows NT オペレーティング システムのマルチスレッド機能を利用します。スレッド処理エンジンを SQL Server 側で独自に実装する代わりに、Windows NT ベースのスレッドを SQL Server 用に割り当てて、各クライアントへのサービス提供に使用するようになっています。プロセッサ間でのスレッドの負荷分散とスケジューリングは、Windows NT によって自動的に実行されます。

SMP コンピュータでは、すべての CPU リソースを SQL Server が占有することが可能です。

マルチスレッドの単一プロセスアーキテクチャ
SQL Server では、SMP をスレッド レベルでサポートしており、Windows NT のスレッド処理の利点を次のような方法で活かすことができます。

• マルチスレッドの単一プロセス アーキテクチャにより、システムのオーバーヘッドとメモリの必要量を低減できます。

• マルチスレッド アプリケーションに単一のアドレス空間を割り当てます。すべてのスレッドが同じプロセスに属するので、共有メモリ プロセスを調整する必要がありません。

非同期 I/O
Windows NT の非同期 I/O では、アプリケーションは I/O 要求を発行した後でも継続して動作します。デバイスからのデータ転送が完了するまでアプリケーションが待機する必要はありません。これに対し、同期 I/O システムでは、I/O 要求の処理が完了するまでは、アプリケーションに制御が戻されません。SQL Server では、Windows NT の非同期 I/O を利用して、スループットを向上しています。

Windows NT サービスの使用
SQL Server では、スレッド処理、スケジューリング、イベント通知、プロセスの同期化、非同期 I/O、例外処理、および統合セキュリティ用の Windows NT サービスを使用します。

Windows NT との統合に関して、SQL Server には次のような特長があります。

• SQL Server エラー ログのほかに、Windows NT のイベント ログを使用します。

• Windows NT の自動負荷分散を利用します。

• Windows NT のパフォーマンス モニタと完全に統合されています。

• Windows NT のセキュリティを活用して、ログインおよびパスワードを統合できます。

パフォーマンス チューニングの方法論

ここでは、データベースをチューニングしてパフォーマンスを向上するには、どのような方法論でパフォーマンスのチューニングを開始すべきかを示します。ここで示す方法論は、この講座で学ぶ各トピックのフレームワークとなります。

パフォーマンス チューニングの方法論 (続き)

この方法論では、あくまで標準的な手順を標準的な順序で示します。実際のデータベース環境における運用の段階に応じて、順序を適宜変更したり、不要な手順を省いてもかまいません。

パフォーマンス チューニングへのアプローチ

チューニングへのアプローチには、次の 2 通りが考えられます。

この講座では、SQL Server がどのようにデータにアクセスし、複数ユーザーによる同時使用をどのように制御し、オペレーティング システムとどのように連携して動作するかを学びます。これらの知識に基づいて、論理設計と物理設計を計画し、SQL Server を構成するとともに、トランザクションを計画し、クエリを作成して、パフォーマンスの最適化を図ることができます。

もう 1 つのアプローチとして、特定の問題に対処するためにチューニングが必要になることがあります。たとえば、クエリの実行に時間がかかるとか、スループットが不自然に低いなどの問題が生じた場合です。このような場合は、SQL Server の動作に関する情報を収集して、最適なパフォーマンスが得られるように、クエリやシステムの環境設定を適切に調整することができます。

実際には、両方のアプローチからのチューニングが必要になります。サーバー、ユーザー、データ、およびプロセスにどれほど精通していても、実際のパフォーマンスを把握しなければ、理論的には正しく設計されているアプリケーションが実際にはうまく動作していない場合に気がつかないことになります。逆に、パフォーマンスをどれだけ詳細に把握していても、アプリケーションやサーバーの知識がなければ、パフォーマンス上の問題を特定して解決することはできません。

概要 ― インデックス付けの方針

目的

• 各種クエリに有用なインデックスを選択する。

• 意思決定支援システム (DSS) とオンライン トランザクション処理 (OLTP) の方針の違いを明確にする。

• 効果的なインデックスを作成する。

• 選択度と結合密度を区別する。

• インデックスが役に立たないケースを理解する。

• インデックスの有用性をテストする。

DSS と OLTP

ほとんどのクエリは、DSS と OLTP の 2 つのカテゴリに分類できます。DSS と OLTP とではインデックス付けの目的が大きく異なるので、ここでは、DSS と OLTP のそれぞれについて説明します。

意思決定支援システム
意思決定支援では、通常、複数の検索引数と複数のテーブルを使用します。集計、グループ化、CUBE 操作、および ROLLUP 操作を使用するなど、クエリが非常に複雑になることがあります。意思決定支援は、オンライン分析処理 (OLAP) と呼ばれることもあります。

意思決定支援用のクエリの内容は、任意性が高く、予測できないため、目的とする行を指定するために、ほとんどすべての列が使用されることもあります。

このようなクエリでは、検索を開始してから結果を返すまでに要する時間をできるだけ短くすることが最も重要な課題となります。

オンライントランザクション処理 (OLTP)
OLTP では、単一のテーブルだけを扱うことが多く、ごく少数の行に対してのみ操作を行うのが普通です。INSERT ステートメントの場合、OLTP アプリケーションのトランザクションでは、単一の行だけを挿入することがよくあります。

意思決定支援ではクエリの内容を予測しにくいのに対し、OLTP ではクエリの内容の予測が比較的容易です。

OLTP クエリでは、データ修正の速度が最も重要なポイントになります。

データ取得用のインデックス

有用なインデックスの作成

パフォーマンス向上の手段としてまず第一に考えられるのが、有用なインデックスを作成することです。ユーザーのニーズとデータそのものを詳細に把握したうえで、インデックスの種類、および数とインデックス対象の列を慎重に選定する必要があります。インデックスは、テーブルに対するクエリを実行する場合にも、データ修正を実行する場合にも役立ちます。どちらの場合も、インデックスを使うことで、データへの読み取りアクセスと書き込みアクセスの速度を向上できます。

ユーザーのニーズの分析
データに対するユーザーのニーズを把握し、よく実行されるクエリの種類と頻度を確認します。ユーザーのニーズを詳細に分析することで、どのようなトレードオフが必要になるかが明確になります。重要性の高いクエリが複数ある場合は、いずれかのクエリのパフォーマンスを向上するために、ほかのクエリの速度を犠牲にしなければならなくなることがあります。

データの分析
データそのものを分析し、論理設計と物理設計においてデータがどのように編成されているかを調べます。

SQL Server の動作の理解
SQL Server の動作を深く理解すればするほど、より良いシステムを設計でき、賢明な決定を下させるようになります。SQL Server がデータをどのように保存および取得するか、クエリ オプティマイザが最も効率性の高い実行プランをどのようにして選択するかなどを理解する必要があります。

一般的な考慮事項

• クエリ オプティマイザでは、通常、各クエリに対し、1 テーブルあたり 1 つのインデックスだけを使用します。

• 大規模なテーブルに対するクエリの実行速度を向上する必要がある場合は、WHERE 句で指定する列にインデックスを置くと効果的です。

• 妥当なインデックス数を決定するには、更新の頻度と取得の頻度を考慮する必要があります。

• データベースに対して使用する WHERE 句や結合の種類に基づいて、インデックスを選択してください。

• インデックスを選定する際は、インデックスの種類と数に注意する必要があります。インデックスが多くなりすぎると、保守に時間がかかりパフォーマンスが低下するので、最も有用性の高いインデックスに絞り込んで、作成するインデックスの数をできるだけ減らします。

• 使用されることのないインデックスを作成しないようにしてください。

• インデックスを使用するかどうかの最終的な判断は、クエリ オプティマイザによって行われます。

選択度

特定のセットのトランザクションで使用するテーブルに作成するインデックスの種類を選定する際は、結果セットを見積もると選定が容易になります。

クエリの選択度は、SELECT ステートメント、UPDATE ステートメント、または DELETE ステートメントでアクセスするテーブルの行のパーセンテージとして表されます。選択度が高ければ、検索条件に一致する行の数が少なくなります。選択度が最も高いクエリでは、行が 1 つだけ返されます。選択度が低いと、検索条件に一致する行の数が多くなります。

これに関連する概念として、インデックス内の重複行の平均パーセンテージを示す密度があります。重複行の多いインデックスほど、密度が高くなります。一意なインデックスは、低密度になります。

テーブルスキャン
結果セットに含まれるテーブル行のパーセンテージが高いクエリ (低選択度のクエリ) の場合は、インデックスを使う代わりに、テーブルをスキャンした方がパフォーマンスが高くなります。

データの分布
データの分布は、テーブル内の特定の値の範囲に入る行の割合を示します。結果セットとして返されるデータのパーセンテージは、多くの場合、ある程度まで予想できます。たとえば、性別を基準とする場合なら、女性を指定したクエリの結果セットのパーセンテージは 50% と予想できます。

クエリの列の分布は、次のように求めることができます。

SELECT column, count(*) 
FROM table 
GROUP BY column 

選択度 : 例

上の例では、両方のセットに同数の X がありますが、X のパーセンテージ (選択度) は異なります。

member テーブルに 10,000 の行があり、1 ～ 10,000 の一意なメンバ番号が割り当てられていると仮定して、これらのクエリの選択度を見積もってみてください。

結合密度

結合密度は、内部テーブルの行のうち、外部テーブルの行に一致する行の平均数を示します。結合密度は、重複行の平均数と考えることもできます。

一意なインデックスを持つ列では、密度が低くなり、結合の選択度が高くなります。重複行の多い列では、密度が高くなり、結合の選択度が低くなります。

結合列のインデックスは、結合の内部テーブルに対してのみ有用となります。結合密度が低ければ、クラスタ化インデックスと非クラスタ化インデックスのどちらも有用になります。結合密度が高ければ、クラスタ化インデックスだけが有用になります。

適切な種類のインデックスの作成

クラスタ化インデックスが適している場合

考慮事項

クラスタ化インデックスを使用する場合は、どの列にインデックスを置くかによって、データの物理的な順序が決まります。物理順序の中で、必要とされる頻度が最も高いデータ列にクラスタ化インデックスを置くようにしてください。

インデックスは、使用する SELECT ステートメントの種類に応じて選定してください。

外部キーは一般に一意ではないので、外部キーにはクラスタ化インデックスを使用することをお勧めします。

テーブル 1 つあたりのクラスタ化インデックスの数は、1 つだけに制限されます。

注
特に単調なデータの場合は、主キーにクラスタ化インデックスを置くことも考えられますが、これが最善策になることはあまりありません。主キーには、通常、一意なインデックスが必要です。多くの場合は、一意なクラスタ化インデックスの代わりに一意な非クラスタ化インデックスを使っても、効率性の点では変わりません。

非クラスタ化インデックスが適している場合

データ修正時には非クラスタ化インデックスの維持に大きなオーバーヘッドが伴います。このため、非クラスタ化インデックスは、実際に有用な場合以外は使用しないようにしてください。

使用されないインデックスを作成することは避けてください。

考慮事項

• 記憶領域の必要条件。

• データ修正の影響。

• 候補となる列の揮発性。

• クエリの選択度。低選択度のクエリの場合は、テーブル スキャンの方がパフォーマンスが高くなります。

• 候補となる列の一意性。重複の度合いが高いと、インデックスの有効性が低くなります。

• 範囲クエリで非クラスタ化インデックスが有効となるのは、テーブルのごく一部分を範囲に含める場合だけです。

複合インデックスが適している場合

複合インデックスとは、主に、複数列の並べ替えキーを持つインデックスを意味します。複合キーは、クラスタ化インデックスと非クラスタ化インデックスのどちらにも持たせることができます。

考慮事項

• 複合インデックスは、WHERE 句でキーの最初の列を指定している場合に有効です。

• 対象範囲が広いインデックスや、WHERE 句で 2 番目以降の列を指定しているインデックスは、複合インデックスとして使用するのには適していません。

たとえば、(lastname, firstname) に作成したインデックスは、lastname および lastname, firstname の選択には適していますが、firstname の選択には適していません。

複合インデックスと複数の単一列インデックス

• 複数のインデックスを使用すると、データ修正ステートメントのパフォーマンスが影響を受ける可能性があります。

• 一般に、クエリではテーブルごとにインデックスが 1 つしか使用されないので、複数のインデックスを作成しても、複合インデックスを使用する場合のような効果は得られません。

• 単一のテーブル内の複数の列にアクセスするクエリの場合は、複合インデックスの方が適しています。

注
複合インデックスは、WHERE 句で列が指定されている順序に関係なく使用されます。重要なのは、複合インデックスの最初の列が WHERE 句に含まれているかどうかという点だけです。

複合インデックスは、インデックス並べ替えキーの上位 (最初) の列が WHERE 句に含まれていなくても使用できます。ただし、この場合は、SELECT リストで参照するすべての列をインデックス並べ替えキーに含める必要があります。詳細については、次の「カバリング インデックス」を参照してください。

カバリングインデックス

考慮事項

• 大半のクエリをカバーできるように、複数のインデックスに列を追加します。

• インデックス キーの対象範囲が広くなりすぎないように注意します。インデックス キーの対象範囲が広すぎると、インデックスのサイズが増え、パフォーマンスに悪影響を及ぼします。返される行が多ければ、レベル数が増え、ページの総数も増えます。ページ数が増えると、インデックス スキャンの所要時間が長くなります。

• 非クラスタ化インデックスの数が多くなると、更新のパフォーマンスが低下します。

• 2 つのインデックスを 1 つの複合カバリング インデックスとして結合すると効果的な場合があります。

• 低選択度のクエリを非クラスタ化インデックスでカバーすると、データ ページへのアクセスが発生せず、テーブル スキャンを回避できるので、処理速度が大幅に向上します。

• カバリング インデックスは、インデックス並べ替えキーの上位 (最初) の列が WHERE 句に含まれていなくても使用できます。

クラスタ化インデックスと非クラスタ化インデックス

クエリに適したインデックスの選定

データ範囲用のインデックス例

テーブルにインデックスを置かない場合

• テーブル スキャン (53,000 I/O) は、非クラスタ化インデックスより効率的です。

price 列にクラスタ化インデックスを置いた場合

• クラスタ化インデックスから最小値 (この例では $20.00) を検索します。

• $20.00 から行の読み取りを開始し、$30.00 で終了します。

• price 列はクラスタ化されているので、データの物理順序は、価格に基づいて並べ替えられます。その範囲に入るすべてのデータは、以降のページ上で連続した順序になるので、データの取得が容易になります。

• この検索では、10,000 ページ (1 ページあたり 190,000/19 行) の読み取りが必要です。

price 列に非クラスタ化インデックスを置いた場合

• 非クラスタ化インデックスを 1 行ずつ検索して、各行が検索条件に一致しているかどうかを判定します。

• 条件を満たすすべての行について、行が格納されているページからデータを取得する必要があります。

• この検索では、各行について 1 ページずつ、合計 190,000 のデータ ページに加え、インデックスのリーフ レベルを読み取る必要があるため、最悪のケースとなります。各データ ページは、キャッシュに複数回読み込まれます。

price 列と title 列にカバリングインデックスを置いた場合

• price 列と title 列がインデックスに含まれるので、データ ページを検索する必要がなく、I/O 数を低減できます。

• (price, title) インデックスでは、1 リーフ ページあたりの平均のインデックス行数が 38 行になります。この検索では、5,000 ページ (1 ページあたり 190,000/38 行) の読み取りが必要です。

AND 検索用のインデックス : 例

スライドの例を参照してください。受講者用のノートに示されている選択肢に従って、このステートメントに対して最も適切な種類のインデックスを検討してください。最善の選択は、条件を満たす行をすべてまとめることのできる選択肢 4 です。選択肢 6 でもクエリをカバーできますが、キーの対象範囲が広いためインデックスのサイズが大きくなります。選択肢 7 では、dept にクラスタ化インデックスを置いた場合と同じ結果が得られますが、インデックスのサイズがかなり大きくなります。

WHERE 句で列が指定されている順序が複合インデックスの処理に影響すると誤解することがよくありますが、実際には影響しません。重要なのは、複合インデックスの最初の列が WHERE 句に含まれているかどうかという点だけです。

また、ここでは、ごく普通に見られるデータ分布を想定しています。どの社員も収入額が 50,000 を超えているか、逆に 50,000 を超える社員がいない場合や、会社全体を調査の対象にする場合などには、異なる方針でインデックスを付ける必要があります。

AND で結合された 2 つの条件が一致すれば、その行が検索条件を満たす行として取得されます。

上の例を評価する際の選択肢

1. dept 列にクラスタ化インデックスまたは非クラスタ化インデックスを置く。

2. salary 列にクラスタ化インデックスまたは非クラスタ化インデックスを置く。

3. dept 列と salary 列にインデックスを 1 つずつ置く。

4. (dept, salary) に複合クラスタ化インデックスを置く。

5. (salary, dept) に複合クラスタ化インデックスを置く。

6. (dept, salary, name, address) に非クラスタ化インデックスを置く (カバリング インデックス)。

7. (dept, salary, name, address) にクラスタ化インデックスを置く。

OR 検索用のインデックス
OR 検索と AND 検索の違いを理解する必要があります。

複数の条件を AND で結合すると、目的のデータを限定する度合いが高くなります。AND 検索では、最終的な結果セットは、いずれかの AND 条件を満たすすべての行のセットのサブセットになります。

OR 条件の場合は、この点が異なっており、いずれかの OR 条件を満たすすべての行のセットに含まれていない行が最終的な結果セットに含まれる可能性があります。

OR 検索については、この講座で後ほど詳しく学びます。

SELECT * 用のインデックス : 例

SELECT * は、指定された列すべての情報を返します。

選択するデータではなく、限定する対象に基づいて、インデックスを選定する必要があります。この場合、インデックスは、行の位置を取得するために使用されます。行から選択する内容は、インデックスに影響されません。すべての列が並べ替えキーに含まれていない限り、非クラスタ化インデックスで SELECT * をカバーすることはできません。

SELECT * では、必要以上のデータを取得することがあります。このため、パフォーマンス向上を考えると、SELECT * を使ったクエリは適切ではないことが多くなります。

上の例を評価する際の選択肢

• au_id 列にクラスタ化インデックスを置く。

• 行がそのままの順序で格納されるように au_id 列にクラスタ化インデックスを置く。

• au_id 列に非クラスタ化インデックスを置く。

• これには、データ ページへのアクセスが必要になります。I/O の量が増える可能性があります。

• (au_id, au_lname) に非クラスタ化インデックスを置く。

複数のクエリで使用するインデックス

前の例では、インデックスを単一のクエリに使用する場合を想定し、対象となるクエリに基づいて最適な種類のインデックスを選定しました。インデックスを複数のクエリで使用する場合は、いずれかのクエリに最適なインデックスがほかのクエリには最適ではない可能性があるため、インデックスの選定が複雑になります。優先度が特に高いクエリに対して、十分なパフォーマンスを確保することを目標に定める必要があります。

上の例を評価する際の選択肢
仮定: クエリ 1 はテーブルの 15 % を対象とすると仮定し、クエリ 2 は選択度が高く、単一の行にアクセスすると仮定します。

選択肢 1

• (price) にクラスタ化インデックスを置く。

• (title) に非クラスタ化インデックスを置く。

クエリ 1 の実行速度は、大幅に向上します。クエリ 2 も実行速度が高くなりますが、title 列にクラスタ化インデックスを置いた場合よりも多くの I/O を必要とします。

選択肢 2

• (price) に非クラスタ化インデックスを置く。

• (title) にクラスタ化インデックスを置く。

クエリ 1 の実行速度は選択肢 1 より低下しますが、クエリ 2 の実行速度は大幅に向上します。

選択肢 3

• (price) にクラスタ化インデックスを置く。

• (title, price) に非クラスタ化インデックスを置く。

クエリ 1 の実行速度は向上し、クエリ 2 の実行速度は大幅に向上します。

選択肢 4

• (price, title) に非クラスタ化インデックスを置く。

• (title) にクラスタ化インデックスを置く。

これが最適な選択となります。クエリ 1 と クエリ 2 の実行速度が共に大幅に向上します。

更新に関する考慮事項

オンライン トランザクション処理 (OLTP) のクエリのデータ取得では、修正対象の列を最初に検索する必要があります。さらに、重要度の高いクエリが OLTP クエリである場合は、このほかにも考慮すべき点があります。

• インデックスの維持が必要です。インデックス付きテーブルに修正を加えるたびに、少なくとも 1 つのインデックスを更新する必要があります。実際には、更新が必要なインデックスの数はもっと多くなることがよくあります。インデックスの列数が多ければ多いほど、インデックスの維持に要する処理が多くなります。

• 修正したデータ行だけではなく、修正したインデックス行もログに記録する必要があります。

ガイドライン

• OLTP アプリケーションの場合は、インデックスの数を最小限に抑えます。

• クラスタ化インデックスの列は、非揮発性の列にします。

インデックス付けのガイドライン

インデックスの維持

クラスタ化インデックス

テーブル上にクラスタ化インデックスがある場合は、クラスタ化インデックス キーと同じ順序で行を挿入する必要があります。ページ上に空きがない場合は、ページの分割が必要になるので、オーバーヘッドが増えます。

非クラスタ化インデックス
非クラスタ化インデックスは、各データ行へのポインタを持ちます。行が挿入または削除されるたびに、すべての非クラスタ化インデックスを調整する必要があります。

UPDATE が完全な DELETE/INSERT か遅延された UPDATE の場合は、削除行と挿入行のどちらについても、すべての非クラスタ化インデックスを調整する必要があります。同じ位置や同じページに対する UPDATE の場合でも、変更する列のインデックスはすべて調整が必要です。対象範囲の広い複合インデックスがある場合は、インデックス自体が大きなオーバーヘッドになります。

インデックス作成のガイドライン

すべてのクエリの優先度を決定します。

• データそのものとデータの使用方法を詳細に分析します。

• データベースに対して優先度の高いトランザクションを特定します。

各クエリの選択度を確認します。

• WHERE 句の各部分の選択度を確認します。

各テーブルの利用状況の分析

• テーブル内の各列の利用状況を分析します。

インデックス付けの対象となる列の決定

• 列が WHERE で使用されているかどうかをチェックします。

  クエリやデータ修正ステートメントの WHERE 句で参照されていない列は、インデックス付けの対象外です。

• 列が結合キーとして使用されているかどうかをチェックします。

  結合キーとして使用される列にインデックスを作成すると、クエリ オプティマイザが必要に応じてテーブル スキャンの代わりにそのインデックスを使用できるようになるので、結合のパフォーマンスが向上します。

• 列が頻繁に検索されるかどうかをチェックします。

クラスタ化インデックスに最も適した列の選定

• データ範囲にアクセスするかどうかをチェックします。また、トランザクション ステートメントに LIKE が含まれているかどうかをチェックします。

• クラスタ化インデックスは、データ範囲にアクセスする場合に最も効果が高くなります。

• データが常に並べ替えられるかどうかをチェックします。

  特定の列にデータが頻繁に格納される場合は、その列にクラスタ化インデックスを置くと、並べ替えのオーバーヘッドが軽減されます。

• 列に一意な値が格納される場合は、一意なインデックスによってパフォーマンスを向上できるかどうかを検討します。

• クラスタ化インデックスは、非クラスタ化インデックスよりも前に作成します。

• 特に単調なデータの場合は、主キーにクラスタ化インデックスを置くことも考えられますが、これが最善策になるとは限りません。

• 結合キー列では、クラスタ化インデックスは不要です。

ほかに必要なインデックスの確認

• 各テーブルに対して作成可能なインデックスの最小数を決定します。

• パフォーマンス向上と更新に関する保守の容易さの間のバランスをとります。

• 優先度の高いクエリの WHERE 句で、参照する列にインデックスが付いているかどうかを確認します。

• 実行頻度の低いクエリの場合は、そのクエリの実行時にのみインデックスを作成し、実行後にインデックスを削除するようにした方がよいことがあります。たとえば、レポートや集計などを月末や年度末にのみ作成する場合は、そのつど、インデックスを作成して削除するようにすると、無駄をなくすことができます。

作成する非クラスタ化インデックスの種類の決定

• 単一列のインデックスより複合インデックスの方が適しているかどうかをチェックします。

• クエリをインデックスでカバーできるかどうかをチェックします。

• クエリの選択度が完全一致かどうかをチェックします。

  非クラスタ化インデックスは、1 つの行が返される完全一致、結合、および一意な主キー列の場合に効果的です。

• 列に一意な値が格納される場合は、一意なインデックスによってパフォーマンスを向上できるかどうかを検討します。

クエリのパフォーマンスのテスト

• インデックスを作成し終えたら、優先度の高いクエリのパフォーマンスをテストします。

• SET SHOWPLAN ON、SET STATISTICS IO ON、SET STATISTICS TIME ON を実行した後、各クエリを実行します。

インデックス付けの対象外となるケース

次のような場合は、インデックス付けの対象外になります。

• オプティマイザがインデックスを使用しない場合

• 10 ～ 20% 以上の行が返される場合

• 列に 1 ～ 3 の一意な値が格納されている場合 (低選択度の場合)

• 列がインデックスを付けるには長すぎる場合 (20 バイトを超えている場合)

• インデックス付けによる効果以上にインデックス維持のオーバーヘッドが大きいと予想される場合

• テーブルのサイズが非常に小さい場合

DSS と OLTP の間のバランス

DSS 環境と OLTP 環境とではインデックス付けの必要条件が大幅に異なるため、両方が必要な場合には、インデックス付けの方針を決定するのが非常に難しくなります。

データのコピーを 2 つ用意して、取得と更新を実際には同じデータに対して行わないようにすることも考えられます。この方法を採る場合は、データを正しく同期する必要があります。2 つの異なる環境に対して最適なインデックスを作成するために必要なコストとその利点を検討することに加え、2 セットのデータの維持と同期に必要なコストも考慮に入れる必要があります。

クエリ オプティマイザの概要

SQL Server のクエリ オプティマイザは、インデックスが実際に有用かどうかを判断し、各クエリに対して、最適なインデックスを選択します。また、複数のテーブルの結合をどのように処理するかを決定し、テーブルの順序と結合方法を選択します。さらに、更新操作の実行に最適な方法も決定します。

次のモジュールでは、SQL Server オプティマイザが利用可能な情報をどのように使用して、最適な実行プランを決定するかについて詳しく学びます。

© 1997 Microsoft Corporation. All rights reserved.

本書に記載されている情報は、発行時点で議論されている問題点に関する Microsoft Corporation の最新の見解を示しています。Microsoft は変化する市場状況に対処しなければならないため、本書の内容を Microsoft の確約事項として解釈してはならず、Microsoft は発行日以降に提示された情報の精度についてはいかなるものであれ保証致しません。

本書は、情報の通知のみを目的としており、Microsoft は本書に記載されている情報について明示的にも暗黙的にも一切の保証を致しません。

Microsoft 、Windows NT は米国 Microsoft Corporation の米国およびその他の国における登録商標または商標です。

その他、記載されている会社名および製品名は、各社の商標および登録商標です。