なぜデータベースにインデックスが必要なのか？ [Database Performance Analyzer :DPA]

投稿日: 2022年2月27日 作成者: climb
このエントリーをはてなブックマークに追加
LINEで送る

新しいアプリケーションをディプロイしたとき、最初はうまく動作していたのに、データが増えるにつれて動作が遅くなったという経験はありませんか？あるいは、レポートを実行しても、結果が出るまでに数分、数時間かかったことはありませんか？ここでは、データベースのパフォーマンスに関する重要な側面であるインデックスについて紹介します。最後にインデックスに関する問題を導き出すことができるDatabase Performance Analyzer について簡単に触れます。

インデックスとは？

インデックスとは、同じデータベース内に存在するテーブルのサブセットのリンクされたコピーのことです。1つまたは複数のカラムで構成することができます（すべての行を含む必要はありません）。インデックスはテーブルにリンクされているので、テーブルにデータが挿入、更新、削除されると、データベースがトランザクションを完了とみなす前にインデックスに反映されます。インデックスを使用すると、クエリオプティマイザはテーブルデータのサブセットを使用することができます。以下はその例です。


SELECT FirstName, LastName

FROM Employees

WHERE LastName = ‘Smith’


このクエリは、Employees という名前のテーブルから、姓と名を取得します。もしインデックスがなければ、このクエリはテーブル全体を読み込まなければなりません。しかし、LastNameカラムのインデックスがあれば、テーブル全体を読み込むのではなく、特定のカラムだけを読み込むことができます。

最初は些細なことに思えるかもしれませんが、Employeesテーブルが50カラム、10,000,000行あるとすれば、読み込むレコード数の減少は突然大きなものになります。また、特定の顧客の残高など、少数のレコードを取得する場合は特にそうです。

すべてのカラムにインデックスを付けるべきでしょうか？

システムのパフォーマンスチューニングを行ったとき、ほとんどの場合インデックスが不足しているのを見かけますが、時々、誰かが工夫してデータベースのすべてのテーブルのすべてのカラムにインデックスを付けているシステムに出くわします。しかし、インデックスはディスク上に独自のスペースを必要とし、テーブル内のデータと同期して保持されます。50カラムの社員テーブルに話を戻すと、50個のインデックスがあると、新しいレコードが書き込まれるたびに、メインテーブルへの書き込みを完了するために、データベースエンジンはその50個のインデックスすべてに書き込む必要があることになる。これでは、書き込みのパフォーマンスが極端に低下してしまうことは想像に難くありません。また、50個のインデックスは多くのディスクスペースを占有することになる。

どのカラムにインデックスを付けるべきでしょうか？明解な答えは、よく実行されるクエリのwhere句とorder by句で参照されるカラムです。これらのカラムは、データ検索における初期作業が行われる場所であるため、最も恩恵を受けることになります。また、ほとんどのテーブルは、各行を一意に識別する主キーを持っています。Employees テーブルでは、EmployeeId がこれにあたります。主キーは本質的にインデックスです。

これらは他のテーブルのカラムを参照しており、頻繁に検索が行われます。データベース・システムによっては、フィルタリング・インデックス (WHERE 節を含むインデックス) を作成することもできます。

たとえば、Employees テーブルに IsActive フィールドがあり、現在その会社で働いている従業員を識別する場合、次のように isActive = 1 のフィルタリングインデックスを作成することができます。

CREATE INDEX IdxFilter on Employees (EmployeeID, LastName)

WHERE IsActive=1;

これにより、作業用のデータセットのサイズが小さくなり、従業員に対するクエリFがより効率的に行えるようになるます。

インデックスの種類

データベースエンジンによって、さまざまな種類のインデックスが用意されています。最も一般的なのは、クラスタ化インデックスと非クラスタ化インデックスです。

クラスタ化インデックスは、実際のテーブルデータをクラスタリングキー（通常はテーブルの主キー）でソートしたものです。クラスタ化インデックスのないテーブルはヒープと呼ばれ、このパターンはOracleでは一般的ですが、他のデータベースではあまり一般的ではありません。この例外は一時的なステージングテーブルで、これはデータ取り込みの速度を厳密に考慮して設計されています。

一方、非クラスタ化インデックスは、LastNameの例のように、テーブル内のデータ行とは別のディスク上の構造体です。LastName の例では、LastName は非クラスタ化インデックスのキーとみなされ、非クラスタ化インデックス内のポインタは、クラスタ化インデックスまたはヒープ(heap)内の行ロケータを指し、行の高速な検索を可能にします。

また、非クラスタ化インデックスに追加の列を含めるオプションもあります。これらの列はキーの一部ではなく、クエリに必要となる可能性のある追加列のために使用されます。これらのカラムを含めることで、レコードを取得するためにベーステーブルを参照する必要がなくなり、パフォーマンスを大幅に向上させることができます。

その他、XMLやJSONのようなネストされたデータ型のインデックスも一般的で、これによりデータをより効率的にクエリすることができる。また、最新のインデックスには、データを列に格納するカラムストアがあります。これはデータを大幅に圧縮することができ、データウェアハウスで発生するような大規模な集計クエリに有効です。

これらの後者のタイプのインデックスは、どちらかというと特殊な用途に使われるものであり、使用しているデータベースプラットフォームで利用可能なオプションを調査する必要があります。

データベースの性能を維持する

データベースのパフォーマンスにおけるインデックスの重要性は、いくら強調してもし過ぎることはありません。しばしば、1つのインデックスが複数のクエリに対応し、サーバーの全体的なパフォーマンスを劇的に向上させることがあります。

このデータベースインデックスの概要は、データベースを可能な限り効率的に動作させるための一端を示すものです。インデックスを最大限に活用するには、その監視と管理を支援する適切なツールを入手することが有効です。データベース管理製品であるDatabase Performance Monitor (DPM)は、監視と管理のニーズを満たすのに役立ちます。SolarWindsが提供するデータベース管理製品の機能を確認したい場合は、是非ご連絡ください。。

関連したトピックス