インデックス作成の長所と短所 ページ 1/2

インデックスの長所と短所

なぜインデックスを作成するのでしょうか?これは、インデックスを作成するとシステムのパフォーマンスが大幅に向上する可能性があるためです。まず、一意のインデックスを作成することで、データベース テーブル内のデータの各行の一意性を保証できます。次に、データの取得を大幅に高速化できます。これがインデックスを作成する主な理由でもあります。 3 番目に、テーブル間の接続を高速化できます。これは、データの参照整合性を達成する上で特に意味があります。第 4 に、データの取得にグループ化句と並べ替え句を使用すると、クエリでのグループ化と並べ替えにかかる時間も大幅に短縮できます。 5 番目に、インデックスを使用すると、クエリ プロセス中に最適化ハイダーを使用してシステムのパフォーマンスを向上させることができます。

インデックスを追加すると多くの利点があるのに、なぜテーブル内のすべての列にインデックスを作成しないのかと疑問に思う人もいるかもしれません。この考えは合理的ですが、一方的でもあります。インデックスには多くの利点がありますが、テーブル内のすべての列にインデックスを追加するのは非常に賢明ではありません。これは、インデックスの追加には多くの欠点があるためです。まず、インデックスの作成と維持には時間がかかり、データ量が増えるとこの時間も長くなります。次に、インデックスは、データ テーブルが占有するデータ スペースに加えて、一定量の物理スペースも占有する必要があります。クラスター化インデックスを確立する場合、必要なスペースはさらに大きくなります。 3 番目に、テーブル内のデータを追加、削除、変更する場合、インデックスを動的に維持する必要があるため、データのメンテナンス速度が低下します。

インデックスは、データベース テーブル内の特定の列に基づいて構築されます。したがって、インデックスを作成するときは、どの列にインデックスを付けることができ、どの列にインデックスを付けることができないかを慎重に検討する必要があります。一般に、インデックスはこれらの列に作成する必要があります。たとえば、頻繁に検索される列では検索を高速化でき、主キーとして機能する列では列の一意性が強化され、データの配置構造が整理されます。テーブル内で、結合でよく使用される列にインデックスを作成します。これらの列は主に外部キーであり、インデックスがソートされているため、結合を高速化できます。指定された範囲は連続的です。 インデックスはすでにソートされているため、クエリでインデックスのソートを使用して、WHERE でよく使用される列にインデックスを作成できます。条件の判断を迅速化するための条項。

また、インデックスを作成すべきではない列もいくつかあります。一般に、インデックスを作成すべきではない列には次の特徴があります。 まず、クエリでほとんど使用または参照されない列にはインデックスを作成すべきではありません。これは、これらの列がほとんど使用されないため、インデックスを作成してもしなくてもクエリ速度は向上しないためです。逆に、インデックスの追加により、システムのメンテナンス速度が低下し、必要なスペースが増加します。次に、データ値がほとんどない列にはインデックスを追加しないでください。これは、クエリ結果ではこれらの列 (人事テーブルの性別列など) の値が非常に少ないため、結果セット内のデータ行がテーブル内のデータ行の大部分を占めるためです。テーブル内で検索する必要があるデータ 行の割合が膨大です。インデックスを増やしても、検索が大幅に高速化されるわけではありません。 3 番目に、テキスト、イメージ、ビットのデータ型として定義された列にはインデックスを追加しないでください。これは、これらの列のデータ量が非常に大きいか、値が非常に少ないためです。第 4 に、変更パフォーマンスが検索パフォーマンスよりもはるかに大きい場合、インデックスを作成すべきではありません。修正性能と検索性能は相反するものだからである。インデックスを追加すると、検索パフォーマンスは向上しますが、変更パフォーマンスは低下します。インデックスを減らすと、変更パフォーマンスは向上しますが、検索パフォーマンスは低下します。したがって、変更パフォーマンスが検索パフォーマンスよりもはるかに優れている場合は、インデックスを作成しないでください。

インデックスの作成方法とインデックスの特徴

インデックスの作成方法

インデックスの作成方法には、直接インデックスを作成する方法と間接的にインデックスを作成する方法などがあります。 。 CREATE INDEX ステートメントやインデックス作成ウィザードを使用するなど、インデックスを直接作成するか、テーブルに主キー制約や一意キー制約を定義するなど、間接的にインデックスを作成すると、インデックスも同時に作成されます。時間。どちらの方法でもインデックスを作成できますが、インデックス作成の具体的な内容が異なります。

CREATE INDEX ステートメントを使用するか、インデックス作成ウィザードを使用してインデックスを作成します。これはインデックスを作成する最も基本的な方法であり、ニーズに合わせてインデックスをカスタマイズできます。 。この方法でインデックスを作成する場合、インデックスを最適化できるように、データ ページの埋まり具合の指定、並べ替え、統計の並べ替えなど、多くのオプションがあります。この方法を使用すると、インデックスのタイプ、一意性、および複合性を指定できます。つまり、クラスター化インデックスまたは非クラスター化インデックスのいずれかを作成したり、1 つの列または 2 つの列にインデックスを作成したり、次の列にインデックスを作成したりできます。 2 列以上。

主キー制約または一意キー制約を定義することで、間接的にインデックスを作成することもできます。主キー制約は、テーブル内のレコードが同じ主キー レコードを持つように制限することでデータの整合性を維持するロジックです。主キー制約を作成すると、システムは一意のクラスター化インデックスを自動的に作成します。論理的には主キー制約は重要な構造ですが、物理構造的には主キー制約に対応する構造は一意のクラスター化インデックスになります。つまり、物理的な実装では、主キー制約はなく、一意のクラスター化インデックスのみが存在します。同様に、一意キー制約を作成すると、一意の非クラスター化インデックスであるインデックスも作成されます。したがって、制約を使用してインデックスを作成する場合、インデックスの種類と特性は基本的に決まり、ユーザーによるカスタマイズの余地は少なくなります。

テーブルに主キー制約または一意キー制約を定義する場合、そのテーブルに CREATE INDEX ステートメントを使用して作成された標準インデックスがすでにある場合、主キー制約または一意キー制約によって作成されたインデックスは以前のインデックスを上書きします。標準インデックスを作成しました。つまり、主キー制約または一意キー制約によって作成されたインデックスは、CREATE INDEX ステートメントを使用して作成されたインデックスよりも高い優先順位を持ちます。

インデックスの特徴

インデックスには、ユニークインデックスと複合インデックスという 2 つの特徴があります。

一意のインデックスは、インデックス列内のすべてのデータが一意であり、冗長なデータが含まれていないことを保証します。テーブルに主キー制約または一意キー制約がすでに設定されている場合、SQL Server はテーブルの作成または変更時に一意のインデックスを自動的に作成します。ただし、一意性を保証する必要がある場合は、一意のインデックスを作成する代わりに、主キー制約または一意キー制約を作成する必要があります。一意のインデックスを作成するときは、次のルールを慎重に考慮する必要があります。テーブルに主キー制約または一意キー制約を作成するとき、テーブルにデータが既に含まれている場合、SQL Server はインデックスを作成するときに自動的に一意のインデックスを作成します。サーバーは、テーブル内の既存のデータの冗長性をチェックします。挿入ステートメントを使用してデータを挿入するか、または変更ステートメントを使用してデータを変更するたびに、SQL Server はデータの冗長性をチェックします。冗長な値がある場合、SQL Server はステートメントをキャンセルします。実行してエラー メッセージを返します。テーブル内のデータの各行が一意であることを確認して、各エンティティを一意に確認できるようにします。たとえば、エンティティの整合性を保証できる列にのみ一意のインデックスを作成できます。複数の人が同じ名前を持つ可能性があるため、person テーブルの name 列に一意のインデックスを作成することはできません。

複合インデックスは、2 つ以上の列に対して作成されるインデックスです。検索時に、2 つ以上の列がキー値として機能する場合は、これらの列に複合インデックスを作成するのが最善です。複合インデックスを作成するときは、次のルールを考慮する必要があります。 最大 16 列を 1 つの複合インデックスに結合でき、複合インデックスを構成する列の合計長は 900 バイトを超えることはできません。複合列は長すぎてはいけません。複合インデックスでは、すべての列が同じテーブルから取得されている必要があり、複数のテーブルにわたって複合列を作成することはできません。そのため、列の順序は重要です。原則として、最も一意な列を最初に定義する必要があります。たとえば、(COL1, COL2) のインデックスは (COL2, COL1) のインデックスと同じではありません。これは、2 つの列の順序が異なるためです。クエリ オプティマイザが複合インデックスを使用するには、クエリが複合インデックスの最初の列を参照する必要があります。複合インデックスは、テーブルに複数のキー列がある場合に非常に役立ちます。複合インデックスを使用すると、クエリのパフォーマンスが向上し、テーブル内に作成されるインデックスの数を減らすことができます。

インデックスの種類

インデックスの順序がデータテーブルの物理的な順序と同じかどうかにより、インデックスは 2 つのタイプに分けられます。 1 つはデータ テーブルの物理的な順序がインデックスの順序と同じであるクラスター化インデックスであり、もう 1 つはデータ テーブルの物理的な順序がインデックスの順序と異なる非クラスター化インデックスです。

クラスター化インデックスのアーキテクチャ

インデックスの構造はツリー構造に似ており、ツリーの最上位はリーフ レベルと呼ばれ、ツリーの他の部分は非リーフと呼ばれます。レベルであり、ツリーのルートは非リーフ レベルにあります。同様に、クラスター化インデックスでは、クラスター化インデックスのリーフ レベルと非リーフ レベルがツリー構造を形成し、インデックスの最下位レベルがリーフ レベルになります。クラスター化インデックスでは、テーブル内のデータが配置されているデータ ページがリーフ レベル、リーフ レベルより上のインデックス ページが非リーフ レベル、インデックス データが配置されているインデックス ページが非リーフ レベルです。レベル。クラスター化インデックスでは、データ値の順序は常に昇順になります。

クラスター化インデックスは、頻繁に検索または連続アクセスされるテーブル内の列に作成する必要があります。クラスター化インデックスを作成するときは、次の要素を考慮する必要があります。 テーブル内のデータの物理的な順序は 1 つだけであるため、各テーブルにはクラスター化インデックスが 1 つだけ存在します。テーブル内の行の物理的な順序は物理的な順序と同じです。非クラスター化インデックスを作成する前に、クラスター化インデックスを作成します。これは、クラスター化インデックスによってテーブル内の行の物理的な順序が変更されるためです。この順序は次のとおりです。キー値の一意性、または UNIQUE キーワードの使用。明示的に、またはシステム自体によって使用され、ユーザーがアクセスできない内部の一意の識別子によって維持されます。クラスター化インデックスの平均サイズは約 5% です。データ テーブルのサイズは異なりますが、実際には、クラスター化インデックスのサイズはインデックス列のサイズに応じて変化することが多く、SQL Server はクラスター化データベースの作成時に一時的に現在のデータベースのディスク領域を使用します。インデックスの作成には、表スペースの 1.2 倍のサイズが必要となるため、クラスター化インデックスを作成するために十分なスペースを確保してください。

システムがテーブル内のデータにアクセスするとき、まず対応する列にインデックスがあるかどうか、またそのインデックスが取得するデータにとって意味があるかどうかを判断します。インデックスが存在し、意味がある場合、システムはそのインデックスを使用してテーブル内のレコードにアクセスします。システムはインデックスからデータを参照し、インデックスの参照はツリー インデックスのルートから開始します。ルートから開始して、検索値が各キー値と比較され、検索値がキー値以上であるかどうかが判断されます。このステップは、検索値より大きいキー値が見つかるまで、または検索値がインデックス ページ上のすべてのキー値以上になるまで繰り返されます。

非クラスター化インデックスのアーキテクチャ

非クラスター化インデックスの構造もツリー構造であり、クラスター化インデックスの構造とよく似ていますが、明らかな違いもあります。

非クラスター化インデックスでは、リーフ レベルにはキー値のみが含まれ、データ行は含まれません。非クラスター化インデックスは行の論理的な順序を表します。非クラスター化インデックスには 2 つのアーキテクチャがあります。1 つのアーキテクチャはクラスター化インデックスなしでテーブルに非クラスター化インデックスを作成し、もう 1 つのアーキテクチャはクラスター化インデックスのあるテーブルに非クラスター化インデックスを作成します。

データ テーブルにクラスタード インデックスがない場合、そのデータ テーブルはデータ ヒープとも呼ばれます。非クラスター化インデックスがデータ ヒープの最上部に作成されると、システムはインデックス ページ内の行識別子を使用して、データ ページ内のレコードを指します。行識別子には、データが配置されている場所に関する情報が格納されます。データ ヒープは、インデックス割り当てマップ (IAM) ページを使用して維持されます。 IAM ページには、データ ヒープが配置されているクラスターのストレージ情報が含まれています。システム テーブル sysindexes には、データ ヒープに関連する最初の IAM ページを指すポインターがあります。システムは IAM ページを使用してデータ ヒープを参照し、新しい行を挿入できるスペースを見つけます。これらのデータ ページとこれらのデータ ページ内のレコードには順序がなく、相互にリンクされていません。これらのデータ ページ間の唯一の関係は、IAM 内のレコードの順序です。非クラスター化インデックスがデータ ヒープ上に作成されると、リーフ レベルにはデータ ページを指す行識別子が含まれます。行識別子はレコード行の論理シーケンスを指定し、ファイル ID、ページ番号、および行 ID で構成されます。行識別子は一意のままです。ノンクラスタード インデックスのリーフレベルのページの順序は、テーブル内のデータの物理的な順序とは異なります。これらのキーの値は、リーフ レベルで昇順に維持されます。

クラスター化インデックスのあるテーブルに非クラスター化インデックスが作成されると、システムはクラスター化インデックスを指すインデックス ページ内のクラスター化キーを使用します。クラスタリングキーにはデータの位置情報が格納されます。テーブルにクラスター化インデックスがある場合、非クラスター化インデックスのリーフ レベルには、物理​​行識別子にマッピングされるのではなく、クラスター化キーにマッピングされるクラスター化キー値が含まれます。システムが非クラスター化インデックスを使用してテーブル内のデータにアクセスし、この非クラスター化インデックスがクラスター化インデックス上に作成されている場合、システムはまず非クラスター化インデックスからクラスター化インデックスへのポインターを検索し、次にクラスター化インデックスを使用します。データを検索するためのクラスター化インデックスへのポインターを検索します。

非クラスター化インデックスは、データを複数の方法で取得する必要がある場合に非常に役立ちます。非クラスター化インデックスを作成するときは、次の状況を考慮してください。 デフォルトでは、作成されるインデックスは非クラスター化インデックスです。各テーブルで作成できる非クラスター化インデックスは 249 個までですが、クラスター化インデックスは最大 1 つしか作成できません。 。