SQLサーバーのインデックス入門

インデックスとは？

インデックスは、データベース内のデータ検索を高速化するためのデータ構造です。本の索引と同じように、データベースのインデックスも情報を迅速に見つけるために使用されます。

インデックスの種類

クラスター化インデックス: データ行自体をキー値に基づいて物理的に並べ替える。テーブルごとに一つだけ設定できます。
ノンクラスター化インデックス: データ行の物理的な順序を変更せず、別の場所にインデックスが作成されます。

インデックスの作成

CREATE INDEX index_name ON table_name (column1, column2, ...);

index_name: 作成するインデックスの名前。
table_name: インデックスを作成するテーブルの名前。
column1, column2, ...: インデックスを作成するカラム。

インデックスの利点

検索速度の向上: インデックスを使うことで、データベースエンジンはテーブル全体をスキャンする代わりに、必要なデータを迅速に見つけることができます。
クエリの効率化: 複雑なクエリもインデックスを使用することで、より効率的になります。

注意点

ストレージの使用量: インデックスは追加のディスクスペースを使用します。
更新パフォーマンス: テーブルにデータを挿入、更新、削除する際に、インデックスも更新する必要があり、これがパフォーマンスに影響を与えることがあります。

結論

インデックスは、適切に使用するとデータベースのパフォーマンスを大幅に向上させることができます。しかし、その設計と使用には慎重さが求められます。

インデックスのリーフとノード

インデックス構造

インデックスは、効率的なデータ検索のために特別に整理されたデータ構造です。この構造は木構造（B-tree）に似ており、ノードとリーフという要素で構成されています。

ノード

**ノード（Node）**は、木構造の中での「分岐点」です。
データベースでは、これらのノードにはキー（index key）が格納されており、これらのキーによって検索クエリはどの方向に進むべきかを決定します。
ノードは木構造の中間に位置し、検索プロセスをガイドする役割を果たします。

リーフ

**リーフ（Leaf）**は、木構造の「末端」に位置する要素です。
クラスター化インデックスでは、リーフレベルには実際のデータ行が格納されます。
ノンクラスター化インデックスでは、リーフにはデータへのポインタ（行への参照）が含まれます。

例

クラスター化インデックスの場合、リーフは実際のデータベースの行です。つまり、リーフに到達すると、必要なデータが直接そこにあります。
ノンクラスター化インデックスの場合、リーフはデータへのポインタを含みます。これは、実際のデータへの「道しるべ」のようなものです。

インデックスの効率

インデックスの木構造を通して検索することで、データベースは大量のデータの中から迅速に必要な情報を見つけることができます。
ノードとリーフの効率的な配置により、データベースの検索性能が大幅に向上します。

結論

インデックスのリーフとノードは、データベース検索の効率性を高めるための重要な構成要素です。これらは、データベースが必要な情報を迅速かつ効率的に見つけ出すための道しるべのような役割を果たします。

リーフの並び順

インデックスの定義に基づくソート：インデックスは、一つまたは複数のカラムに基づいて作成されます。リーフレベルのデータは、これらのカラムの値に基づいてソートされます。
クラスター化インデックスの場合：クラスター化インデックスでは、テーブル全体がインデックスのキーに基づいて物理的にソートされます。この場合、リーフレベルは実際のデータ行そのものであり、これらの行はインデックスキーに従って順序付けられます。
ノンクラスター化インデックスの場合：ノンクラスター化インデックスでは、リーフはインデックスキーに基づいてソートされたポインタ（実際のデータ行への参照）を含みます。データ自体はその物理的な順序で保持されるため、ノンクラスター化インデックスのリーフはキーに従ってソートされたポインタの集まりです。

このように、インデックスのリーフは、そのインデックスが定義されたカラムの値に基づいてソートされており、これによりデータベースは効率的にデータを検索できるようになります。

インデックスのアクセス

インデックスのスキャン

まず、押さえておきたいのは、重要なのはこのリーフの並び順です。
この並び順で検索できるのので処理が早くなる可能性があります。
シーケンシャルに検索しても、少なくとも見つかった後ろには検索対象はないわけです。

インデックスのシーク

例えばインデックスが一つのファイルなら、先頭からスキャンしないでファイルの真中へシークしそこを起点に残りの半分のの真ん中へシークして、というような方法もあるでしょう。

階層のあるインデックスのシーク

本の索引など

インデックスの概念を理解するために、本の索引を例にとるのは非常に良い方法です。日本語の本であれば、50音順に並べられた索引が使われることが多いですね。この例を使って、データベースのインデックスの動作を説明します。

本の索引とは

本の索引は、本の中の特定の情報を簡単に見つけるためのものです。たとえば、あるトピックやキーワードに関連するページ番号がリストアップされています。

50音順の索引の利用

日本語の本では、50音順の索引がよく使われます。例えば、あるトピックを探したいとき、まずはそのトピックの最初の文字（例えば「さ」行）を索引から探します。これがデータベースでいう「ルートノード」に相当します。

ステップ1: 行の選択

索引の中で、「さ」行のセクションに移動します。これはB-treeインデックスの中間ノードを探すことに似ています。
この行には、「さ」で始まるすべてのトピックが含まれています。

ステップ2: 文字の選択

次に、「さ」行の中で必要なトピックの最初の文字（例えば「し」）を探します。
ここで、より具体的な範囲（「し」で始まるトピック）に絞り込むことができます。これはデータベースのインデックスでリーフノードを探すことに相当します。

ステップ3: トピックの検索

最終的に、「し」で始まるトピックの中から目的のトピックを探し出し、関連するページ番号を見つけます。
これにより、大量の情報の中から必要な情報にすばやくアクセスできます。

データベースのインデックスとの類似点

データベースのインデックスも、本の索引と同じように機能します。データベースにおいて、インデックスは特定の列（例えば、名前やID）に基づいて作成され、データの検索を高速化します。ユーザーが特定のデータ（例：特定の名前）をクエリすると、データベースはインデックスを使用して、データの大海から必要な情報を効率的に見つけ出します。

インデックスにより、データベースは大量のデータの中から迅速に必要な情報を見つけ出すことができるのです。

B-Tree系のインデックスのシーク

B-Tree系のインデックスにおける「シーク」を理解するためには、まずB-Treeインデックスの基本的な構造と動作原理を把握することが重要です。ここでは、B-Treeインデックスのシークについて初心者にも理解しやすいように説明します。

B-Treeインデックスとは

B-Tree（Balanced Tree）インデックスは、データベースの検索効率を高めるために使われるバランスの取れた木構造です。この構造は、データの挿入、削除、検索を高速かつ効率的に行うことができます。

B-Treeインデックスは主に3つの種類のノードから構成されています：

ルートノード：木構造の最上部に位置するノード。
中間ノード：ルートとリーフの間にあるノード。
リーフノード：木の最下層に位置し、実際のデータやデータへのポインタが格納されています。

インデックスのシークとは

インデックスの「シーク」は、特定のデータをB-Treeインデックスを使って効率的に探すプロセスです。データベースが特定の値や範囲の値を見つけるために、インデックスの木構造を上から下へと進んでいきます。

シークのプロセス

開始点（ルートノード）：
- シークはルートノードから始まります。データベースは、検索する値と比較して、どの中間ノードへ進むかを決定します。
中間ノードの通過：
- 中間ノードは、検索プロセスをリーフノードに導くためのガイド役を果たします。データベースは中間ノードを通過しながら、目的の値を含む可能性のあるリーフノードへと進んでいきます。
目的のデータの特定（リーフノード）：
- 最終的に、リーフノードに到達します。リーフノードには、実際のデータやデータへのポインタが含まれています。
- データベースはリーフノード内のデータをチェックし、求めている特定の値や範囲のデータを見つけ出します。

シークの利点

高速な検索：B-Treeインデックスのシークは非常に効率的で、大量のデータの中からも素早く必要なデータを見つけ出すことができます。
最適化されたデータアクセス：データベースは不必要なデータを読み込むことなく、直接目的のデータへアクセスできます。

まとめ

B-Treeインデックスにおけるシークは、データベースの効率的な検索手法の一つです。このプロセスにより、データベースは膨大なデータの中から迅速に必要な情報を見つけることができ、パフォーマンスと効率を大幅に向上させることが可能です

雑談のネタ

名前

人定

社員マスター

所属
権限サブシステム当の使用権

戸籍

-　戸籍にPKは存在するか。

ジェンダー

LGBTO

付録

SELECT *
    --OBJECT_NAME(ips.object_id) AS TableName,
    --i.name AS IndexName,
    --ips.index_id,
    --ips.index_type_desc,
    --ips.avg_fragmentation_in_percent,
    --ips.page_count,
    --ips.index_depth
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
    sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE
    OBJECT_NAME(ips.object_id) = 'TestTable'
ORDER BY
    ips.index_depth DESC;

21	917578307	3	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	0	0.372219578121212	28567	7.79633843245703	222718	99.8266617247344	100000000	0	0	16	16	16	NULL	0	72057594046251008	0	NOT VALID	0	0	0	0	0	0	917578307	PK_TestTable	3	2	NONCLUSTERED	1	1	0	1	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	3	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	1	0.725513905683192	827	1	827	43.2296639486039	222718	0	0	11	11	11	NULL	0	72057594046251008	0	NOT VALID	0	0	0	0	0	0	917578307	PK_TestTable	3	2	NONCLUSTERED	1	1	0	1	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	3	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	2	100	2	1	2	66.3886829750432	827	0	0	11	11	11	NULL	0	72057594046251008	0	NOT VALID	0	0	0	0	0	0	917578307	PK_TestTable	3	2	NONCLUSTERED	1	1	0	1	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	3	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	3	0	1	1	1	0.296515937731653	2	0	0	11	11	11	NULL	0	72057594046251008	0	NOT VALID	0	0	0	0	0	0	917578307	PK_TestTable	3	2	NONCLUSTERED	1	1	0	1	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	4	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	0	0.01	25684	8.67146861859523	222718	99.8266617247344	100000000	0	0	16	16	16	NULL	0	72057594046316544	0	NOT VALID	0	0	0	0	0	0	917578307	IX_TestTable	4	2	NONCLUSTERED	0	1	0	0	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	4	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	1	0.316205533596838	157	8.05732484076433	1265	45.6547442550037	222718	0	0	19	19	19	NULL	0	72057594046316544	0	NOT VALID	0	0	0	0	0	0	917578307	IX_TestTable	4	2	NONCLUSTERED	0	1	0	0	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	4	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	2	33.3333333333333	6	1	6	54.6763034346429	1265	0	0	19	19	19	NULL	0	72057594046316544	0	NOT VALID	0	0	0	0	0	0	917578307	IX_TestTable	4	2	NONCLUSTERED	0	1	0	0	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	4	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	3	0	1	1	1	1.53199901161354	6	0	0	19	19	19	NULL	0	72057594046316544	0	NOT VALID	0	0	0	0	0	0	917578307	IX_TestTable	4	2	NONCLUSTERED	0	1	0	0	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0
21	917578307	0	1	HEAP	IN_ROW_DATA	1	0	51.2518582599128	61079	15.5926095712111	952381	99.864096861873	100000000	0	0	75	75	75	0	0	72057594046185472	0	NOT VALID	0	0	0	0	0	0	917578307	NULL	0	0	HEAP	0	1	0	0	0	0	0	0	0	0	1	1	0	NULL	NULL	0	0	0