SQL Server の構造について記事にしてみる - その1 (インデックスの基礎知識) -

多くの開発者はインデックスの構造や最適な作成方法を知らない！？

　ある日、プロジェクトの仲間と仕事帰りに飲んでいた時のことです。システムのことについて色々と語っていたのですが、

『そもそも、開発者がデータベースのことを知らなさすぎる！(とはいっても、自分もレベル的には、まだまだですけどね。) 』

という内容の話題で盛り上がりました。確かに、開発者はデータベースのことってあまり関心があなく、SQL を書いてデータが正しく帰ってきたらいい、という人も少なからずいます。 このような理由から、SQL Server の構造について少しづつ書いていこうと思い立ったわけです、ハイ。( その1 とか書いてますが、シリーズ化できるかはわかりませんwww )

　SQL Server のデータ構造やメモリ管理のことから書いてもよかったのですが、開発者はなかなかこのあたりは興味をもってくれません。まずは、開発者が苦手かつパフォーマンス面で問題に直面しやすいインデックスのお話からにします。

まずは、B-Tree から

　SQL Server のインデックス構造は B-Tree 基本にしていると言われていますが、正確には、B+Tree のアルゴリズムをベースにしています。ウィキペディアから引用すると、

B+木（英: B+ tree）は、キーを指定することで挿入・検索・削除が効率的に行える木構造の一種である。動的な階層型インデックスであり、各インデックスセグメント（「ブロック」などと呼ばれる。木構造におけるノードに相当）にはキー数の上限と下限がある。B+木はB木とは異なり、全てのレコードは木の最下層（葉ノード）に格納され、内部ノードにはキーのみが格納される。

図1：B+Tree の構造 ( インデックス構造に置き換えてみる )

　レコードはリーフノードのみに格納されており、それ以外のノードはキーのみ格納されていること、同一階層のノード間で関連をもっていることが B-Tree と大きく異なります。

インデックスの種類

　SQL Server は下記のようなインデックスを持っています。

1. クラスタ化インデックス
2. 非クラスタ化インデックス
3. フルテキストインデックス
4. XML インデックス
5. カラムストアインデックス
6. ハッシュインデックス

　今回は、上記のインデックスから必ず覚えておかなければならない、1. クラスタ化インデックス  2. 非クラスタ化インデックス  について説明します。あと、非クラスタ化インデックスを語る上で外せない、『 付加列 』に関しても説明します。

※フィルタ選択されたインデックスも、外せない機能の一つですが、すでに記事にしてありますので、SQL Server のチューニングについてまとめてみる - その2 - ( フィルタ選択されたインデックス ) - 都内で働くSEの技術的なひとりごと を参照してください。

1. クラスタ化インデックス

　クラスタ化インデックスとは、1テーブルに1つのみ定義可能なインデックスで、リーフノードにキーで並び替えられたデータ行も一緒に保持することが可能なインデックスです。キーの値はなるべく小さくすることで、より高速な検索を実現することが可能です。

図2：クラスタ化インデックス 

2. 非クラスタ化インデックス

 　非クラスタ化インデックスとは、1 テーブルに 249 個まで作成可能で、リーフノードに、データ行へのポインタ（行識別子：RID）が格納されるインデックスです。但し、クラスタ化インデックスが存在する場合は、行識別子ではなく、クラスター化インデックスの値が格納されます。

図3：クラスタ化インデックスが存在しない場合

図4：クラスタ化インデックス存在する場合

　テーブルに対して、クラスタ化インデックスおよび非クラスタ化インデックスの両方を作成する場合は、データ行の物理的な並べ替えが発生するので、クラスタ化インデックスを作成してから、非クラスタ化インデックスを作成するようにしましょう。

3. 付加列

 　最後に付加列です。付加列は非クラスタ化インデックスに対して作成可能です。非クラスタ化インデックスのインデックス列に存在しない列を SELECT 句に指定した場合、そのデータを取得するために、SQL Server は データページまたはクラスタ化インデックスにアクセスを行い、足りないデータを取得しようとします。アクセス回数が少なければさほどパフォーマンスには影響しませんが、アクセス回数が多くなるとパフォーマンスに影響が出てしまいます。

　上記の問題を解決するため、付加列という機能を使用します。付加列を使用すると、非クラスタ化インデックスのリーフノードに実データを付加し、データページまたはクラスタ化インデックスにアクセスさせないようにすることが可能です。下図の例は、Col1 を付加列とした場合の例です。

図5：付加列の例

　付加列を追加することにより、大幅なパフォーマンス向上を図ることができます。但し、リーフノードに実データを保持するため、容量の増加および更新時のパフォーマンス劣化の可能性があります。設計時に十分注意する必要あります。

おわりに

　今回は、SQL Server のインデックスまわりで最低限覚えてもらいたいインデックス関連の説明をしました。概念的な話だけでは、理解しにくいと思いますので、次回は実データを使用して、クラスタ化インデックス、非クラスタ化インデックスおよび付加列の説明をしたいと思います。また、今回から記事の構成を変更しました。今後の記事はこのような構成にしたいと思います。

 ※2014 の日本語書籍発売されませんねぇ。

SQL Server 2014 CTP2インメモリOLTP機能の概要 (SQL Server 2014 CTP2自習書シリーズ)