CRM データベースチューニング - インデックスはつけるべき？ パート 1

みなさん、こんにちは。

東京は最近すっかり寒いのですが、いかがお過ごしでしょうか？春が待ち遠しい今日この頃です。さて、今日は
パフォーマンスチューニングに関して、データベースのインデックスを取り上げます。全 4 回の予定です。
第 1 回は、インデックスとは何かを簡単におさらいします。

インデックスとは？

すでにご存知の方も多いと思いますが、今回は簡単にインデックスの話をします。インデックスは、各テーブルに対して
作成するもので、テーブルに格納されている情報の一部分を抜き出したものです。この一部分の情報を整理しておき、
実際のデータと結びつけることで、データベース処理を高速化することが目的です。データの読み取り時にインデックスを
利用することで、無駄な処理を省略することが可能となります。

以下に例を見てみましょう。

まず例として、以下のようなテーブルを考えます。またインデックスは今のところありません。
（例は3行ですが、例えばこのようなデータが1万件あるとします）

<メンバーテーブル>

このテーブルに対して、次のクエリを実行するとしましょう。

select 名前 from メンバーテーブル where ID = 2

このクエリを実行した場合 SQL Server はどのような処理を実行するでしょう？上から 2 行目の結果が欲しいことは
人間の目からはすぐに分かりますが、結果として SQL Server は全てのデータを検索します。(この場合 1 万件）
理由は、2 行目以外の行にも、 ID が 2 の行があるかもしれないからです。テーブルを設計した人から見れば、ID は
1 ずつ大きくなることが分かっているのですが、 SQL Server にはそれが分かりません。そこでインデックスの登場です。

では、インデックスを ID 列につける場合を考えましょう。この場合、実際のテーブルとは別にインデックスの情報を
SQL Server が持つことになります。（※非クラスタ化インデックスの場合）

インデックスは情報が順番に並んでいることが保障されているため、SQL Server はインデックスの中から、全ての 2 の
データを見つけた後、それ以降は 3 以上のデータしかないと分かるため検索を終了します。その後、インデックスの情報
から、実際のテーブルの行を読み取り、結果を返します。

よって、SQL Server はインデックスを読み取る作業と、実際のテーブルを読み取る作業をするわけです。もしこの作業が
実際のテーブルを全て読み取るより、結果として速くなると判断した場合、SQL Server はインデックスを利用しますし、
実際のテーブルを全て読み取って確認したほうが速いと判断した場合、インデックスを使いません。

インデックスの種類

インデックスには、クラスタ化インデックスと非クラスタ化インデックスの 2 種類があります。

[クラスタ化インデックス]

クラスタ化インデックスは、テーブルに 1 つしか作成できません。なぜならクラスタ化インデックスとは、テーブルそのもの
だからです。たとえとして、例のメンバーテーブルを取り上げましょう。

クラスタ化インデックスがない場合、以下の順番でデータを挿入したらどうなるでしょう。

1. ID 1 のレコードを挿入

2. ID 3 のレコードを挿入

3. ID 2 のレコードを挿入

この場合、テーブルは以下のようにデータを保持します。

一方で、ID 列にクラスタ化インデックスを作成した場合には、ID 列の順番を保守しながらデータの挿入を行うため、
以下のようにデータを保持します。

[非クラスタ化インデックス]

非クラスタ化インデックスは、実際のテーブル以外に、インデックスのデータを作成します。よって各テーブルに複数の
インデックスを作成することが可能です。その場合、複数のインデックスデータを作成します。例えば、電話番号に
非クラスタ化インデックスを作成した場合には、以下のようなデータを保持します。

1. 実際のテーブルのデータ （1万件）

2. 電話番号列の非クラスタ化インデックスのデータ （1万件）

この状態で、次のクエリを実行するとしましょう。

select 名前 from メンバーテーブル where 電話番号 = ’03-3333-xxxx’

この場合、SQL Server はまずインデックスのデータを検索します。そこで ID が 3 の列がそのデータを持っていると判断し、
実際のテーブルの ID が 3 の列のデータを取得します。ここで明らかなのですが、非クラスタ化インデックスを作成するには
クラスタ化インデックスが必要となります。ここの例では、クラスタ化インデックスが ID 列にあると想定しています。

尚、インデックスの無いテーブルは、ヒープと呼ばれます。他にもインデックスを作成するテクニックとして複数の行を指定
する方法や、データの持ち方を設定する方法などありますが、それはまた次回以降で詳しく説明したいと思います。

さて、今回はパート 1 ということでインデックスの紹介をしたところで終わりにします。
次回は、インデックスのプラスとマイナスを紹介しますので、お楽しみに。

参考:
SQL Server 2005 Books Online インデックスについて
https://msdn.microsoft.com/ja-jp/library/ms179613(SQL.90).aspx

SQL Server 2008 Books Online インデックスについて
https://msdn.microsoft.com/ja-jp/library/ms179613.aspx

- Dynamics CRM サポート 中村 憲一郎