クラスタードインデックスとノンクラスタードインデックス

インデックスとは

インデックス（索引）は、データベーステーブルからデータを高速に検索するためのデータ構造です。書籍の索引と同様に、全ページを読まずに目的の情報を見つけることができます。

インデックスの目的

1. クエリパフォーマンスの向上

   • データ検索の高速化
   • WHERE句、JOIN句、ORDER BY句の最適化

2. 一意性の保証

   • PRIMARY KEY、UNIQUEの制約実装

3. 外部キーの効率化

   • 参照整合性チェックの高速化

インデックスの基本構造：B-Treeとは

SQL Serverのインデックスは、**B-Tree（Balanced Tree）**という平衡木構造を使用しています。

B-Treeの階層構造

                    ルートノード (Root Node)
                    ┌─────────────┐
                    │  [50, 100]  │
                    └──────┬──────┘
           ┌───────────────┼───────────────┐
           │               │               │
    中間ノード          中間ノード       中間ノード
    ┌──────────┐      ┌──────────┐    ┌──────────┐
    │[10, 30]  │      │[60, 80]  │    │[110, 130]│
    └─────┬────┘      └─────┬────┘    └─────┬────┘
    ┌─────┼─────┐    ┌──────┼─────┐  ┌──────┼─────┐
    │     │     │    │      │     │  │      │     │
リーフノード リーフノード リーフノード リーフノード リーフノード リーフノード
┌──────┐┌──────┐┌──────┐┌──────┐┌──────┐┌──────┐
│1-9   ││11-29 ││31-49 ││51-79 ││81-99 ││101+  │
└──────┘└──────┘└──────┘└──────┘└──────┘└──────┘

B-Treeの特徴

• 平衡木: すべてのリーフノードが同じ深さ
• 効率的な検索: O(log n) の計算量
• ソート済み: キー値が常にソートされている
• 範囲検索に強い: 連続したデータの取得が効率的

クラスタードインデックス（Clustered Index）

クラスタードインデックスの定義

クラスタードインデックスは、テーブルのデータそのものをインデックスのキー順に物理的に並べ替えて格納するインデックスです。

重要な特性

1. テーブルごとに1つだけ

   • 物理的なデータの並び順は1つしか持てない
   • PRIMARY KEYがデフォルトでクラスタード

2. リーフノードにデータ行そのものが格納

   • インデックスとデータが一体化
   • 追加のルックアップが不要

3. 物理的な並び順を決定

   • データページ自体がインデックスの一部

クラスタードインデックスの構造

ルートノード（B-Tree上位層）
        │
        ▼
中間ノード（B-Tree中間層）
        │
        ▼
リーフノード = データページ（実際のテーブルデータ）
┌───────────────────────────────────────────┐
│ CustomerID │ CustomerName    │ City       │
├───────────────────────────────────────────┤
│ 1          │ Alice Johnson   │ New York   │
│ 2          │ Bob Smith       │ Chicago    │
│ 3          │ Carol White     │ Seattle    │
└───────────────────────────────────────────┘
↑ データそのものがリーフノードに格納されている

クラスタードインデックスの作成

-- テーブル作成時にPRIMARY KEYとして作成（デフォルトでクラスタード）
CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY CLUSTERED,  -- クラスタードインデックス
    CustomerName NVARCHAR(100),
    Email NVARCHAR(100),
    City NVARCHAR(50)
);

-- 既存のテーブルにクラスタードインデックスを追加
CREATE CLUSTERED INDEX IX_Orders_OrderDate 
ON Orders (OrderDate);

-- PRIMARY KEYを非クラスタードにして、別の列をクラスタードに
CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY NONCLUSTERED,  -- 非クラスタード
    OrderDate DATETIME,
    CustomerID INT,
    TotalAmount DECIMAL(18,2)
);

CREATE CLUSTERED INDEX IX_Orders_OrderDate 
ON Orders (OrderDate);

クラスタードインデックスのメリット

1. 範囲検索が高速

-- 範囲検索: クラスタードインデックスで非常に高速
SELECT * 
FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-31';
-- データが物理的に連続して配置されているため、シーケンシャルI/Oで高速読み取り

2. 追加のルックアップが不要

-- すべての列を取得する場合、追加アクセス不要
SELECT OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
WHERE OrderDate = '2024-01-15';
-- リーフノードに全データが含まれているため、1回のアクセスで完了

3. ORDER BYが不要な場合がある

-- クラスタードインデックスのキー順で自動的にソート済み
SELECT * 
FROM Orders
ORDER BY OrderDate;
-- データがすでにOrderDate順に物理的に並んでいるため、追加ソート不要

クラスタードインデックスのデメリット

1. 挿入パフォーマンスへの影響

-- ランダムな値での挿入時にページ分割が発生
INSERT INTO Orders (OrderID, OrderDate, CustomerID, TotalAmount)
VALUES (5000, '2023-06-15', 123, 500.00);
-- OrderDateが2023年のため、既存の2024年データの間に挿入される
-- → ページ分割が発生し、パフォーマンスが低下

2. 更新時の物理移動

-- クラスタードキーの更新は物理移動を伴う
UPDATE Orders
SET OrderDate = '2025-01-01'  -- 日付を大幅に変更
WHERE OrderID = 1234;
-- 行が物理的に移動する必要があるため、コストが高い

ノンクラスタードインデックス（Non-Clustered Index）

ノンクラスタードインデックスの定義

ノンクラスタードインデックスは、インデックスキーとデータへのポインタを別に管理するインデックスです。書籍の索引に似ており、索引からページ番号を見つけて本文にアクセスします。

重要な特性

1. テーブルごとに最大999個

   • SQL Server 2008以降: 999個
   • SQL Server 2005以前: 249個

2. リーフノードにポインタが格納

   • クラスタード有り: クラスタードキー値を格納
   • クラスタード無し（ヒープ）: RID（Row Identifier）を格納

3. 独立したB-Tree構造

   • データとは別の構造を持つ

ノンクラスタードインデックスの構造（クラスタードあり）

ノンクラスタードインデックス
ルートノード
    │
    ▼
中間ノード
    │
    ▼
リーフノード（インデックスキー + クラスタードキー）
┌────────────────────────────────┐
│ Email              │ CustomerID│ ← クラスタードキーへのポインタ
├────────────────────────────────┤
│ alice@example.com  │ 1         │
│ bob@example.com    │ 2         │
│ carol@example.com  │ 3         │
└────────────────────────────────┘
         │
         ▼ クラスタードインデックスでルックアップ
┌───────────────────────────────────────────┐
│ CustomerID │ CustomerName    │ City       │ ← クラスタードインデックス（実データ）
├───────────────────────────────────────────┤
│ 1          │ Alice Johnson   │ New York   │
│ 2          │ Bob Smith       │ Chicago    │
│ 3          │ Carol White     │ Seattle    │
└───────────────────────────────────────────┘

ノンクラスタードインデックスの作成

-- 単一列のノンクラスタードインデックス
CREATE NONCLUSTERED INDEX IX_Customers_Email
ON Customers (Email);

-- 複合インデックス（複数列）
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
ON Orders (CustomerID, OrderDate);

-- INCLUDE句を使用したカバリングインデックス
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_INC
ON Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount);

-- フィルター付きインデックス
CREATE NONCLUSTERED INDEX IX_Orders_ActiveOnly
ON Orders (OrderDate)
WHERE Status = 'Active';

-- ユニークインデックス
CREATE UNIQUE NONCLUSTERED INDEX UQ_Customers_Email
ON Customers (Email);

ノンクラスタードインデックスのメリット

1. 特定列の検索が高速

-- Emailでの検索が高速
SELECT CustomerID, CustomerName
FROM Customers
WHERE Email = 'alice@example.com';
-- IX_Customers_Emailインデックスを使用して高速検索

2. 複数のインデックスで多様なクエリに対応

-- 異なるクエリパターンに対応
CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID);
CREATE INDEX IX_Orders_OrderDate ON Orders (OrderDate);
CREATE INDEX IX_Orders_Status ON Orders (Status);

-- それぞれのクエリが適切なインデックスを使用
SELECT * FROM Orders WHERE CustomerID = 123;     -- IX_Orders_CustomerID
SELECT * FROM Orders WHERE OrderDate = '2024-01-01';  -- IX_Orders_OrderDate
SELECT * FROM Orders WHERE Status = 'Active';    -- IX_Orders_Status

3. カバリングインデックスで最高のパフォーマンス

-- INCLUDE句でカバリングインデックスを作成
CREATE INDEX IX_Orders_CustomerID_Covering
ON Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);

-- このクエリはインデックスだけで完結（Key Lookup不要）
SELECT OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerID = 123;
-- インデックスに必要な列がすべて含まれているため、テーブルアクセス不要

ノンクラスタードインデックスのデメリット

1. Key Lookupのオーバーヘッド

-- インデックスにない列を取得する場合
SELECT CustomerID, CustomerName, Email, Phone, Address
FROM Customers
WHERE Email = 'alice@example.com';

-- 実行される処理:
-- 1. IX_Customers_Email でEmailを検索 → CustomerID (クラスタードキー) を取得
-- 2. クラスタードインデックスでCustomerIDをルックアップ → 全データ取得
-- ⚠️ 2回のアクセスが必要（Key Lookup）

2. ストレージオーバーヘッド

-- インデックスが多いほどストレージを消費
-- 各ノンクラスタードインデックスは独立したB-Tree構造を持つ

-- 例: Customersテーブルに5つのノンクラスタードインデックス
-- → テーブルデータ + 5つのインデックスツリーのストレージが必要

3. 更新コスト

-- データ更新時、すべての関連インデックスも更新が必要
UPDATE Orders
SET TotalAmount = 1000.00
WHERE OrderID = 123;

-- 更新される要素:
-- 1. テーブルデータ（クラスタードインデックス）
-- 2. TotalAmountを含むすべてのノンクラスタードインデックス
-- 3. 関連する統計情報

クラスタード vs ノンクラスタード: 比較表

項目	クラスタードインデックス	ノンクラスタードインデックス
テーブルごとの数	1つのみ	最大999個
リーフノード	データ行そのもの	インデックスキー + ポインタ
物理的配置	インデックス順に配置	別構造
範囲検索	非常に高速	高速（ただしKey Lookup発生可能）
ストレージ	テーブルデータと一体	追加ストレージ必要
挿入パフォーマンス	ページ分割の可能性	影響少ない
更新パフォーマンス	キー更新時は物理移動	独立して更新
推奨用途	PRIMARY KEY、範囲検索	WHERE句、JOIN列

インデックスシーク vs インデックススキャン

SQL Serverがインデックスを使用してデータにアクセスする方法には、主に2つあります。

インデックスシーク（Index Seek）

定義: B-Tree構造を利用して、特定の行を効率的に検索する方法

動作の仕組み

B-Treeを上から下へナビゲート

ルートノード: [50, 100] を確認
   ↓ 25 < 50 なので左へ
中間ノード: [10, 30] を確認
   ↓ 25 > 10 かつ 25 < 30 なので中央へ
リーフノード: 25を含むページを直接アクセス
   ↓
目的のデータを取得（数ページのみアクセス）

インデックスシークが発生する条件

-- 1. 等号条件（=）
SELECT * FROM Customers WHERE CustomerID = 123;

-- 2. 範囲条件（小さい範囲）
SELECT * FROM Orders 
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-07';

-- 3. IN句（値が少ない場合）
SELECT * FROM Customers WHERE CustomerID IN (1, 2, 3);

-- 4. 複合インデックスの先頭列を使用
-- インデックス: IX_Orders_CustomerID_OrderDate (CustomerID, OrderDate)
SELECT * FROM Orders WHERE CustomerID = 123;  -- ✓ Seek可能

-- 5. LIKE句（前方一致）
SELECT * FROM Customers WHERE Email LIKE 'alice%';

インデックスシークの特徴

• ✅ 高速: 必要なページのみアクセス
• ✅ 効率的: I/Oが最小限
• ✅ スケーラブル: データ量が増えても速度低下が少ない
• 📊 論理読み取り: 通常は数ページ～数十ページ

インデックススキャン（Index Scan）

定義: インデックスのリーフレベルを先頭から順にスキャンする方法

動作の仕組み

インデックスの全リーフノードを順次読み取り

リーフノード1 → リーフノード2 → リーフノード3 → ... → リーフノードN
   ↓            ↓            ↓                ↓
すべてのページをスキャン（全ページアクセス）

インデックススキャンが発生する条件

-- 1. WHERE句がない
SELECT * FROM Orders;

-- 2. インデックスキーを使わない条件
-- インデックス: IX_Orders_OrderDate (OrderDate)
SELECT * FROM Orders WHERE CustomerID = 123;  -- OrderDateを使わない

-- 3. 関数の使用
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- 4. NOT、!=、<> の使用
SELECT * FROM Orders WHERE Status != 'Cancelled';

-- 5. LIKE句（後方一致、中間一致）
SELECT * FROM Customers WHERE Email LIKE '%@example.com';

-- 6. OR条件（インデックスが異なる）
SELECT * FROM Orders 
WHERE CustomerID = 123 OR OrderDate = '2024-01-01';

-- 7. 複合インデックスの先頭列を使わない
-- インデックス: IX_Orders_CustomerID_OrderDate (CustomerID, OrderDate)
SELECT * FROM Orders WHERE OrderDate = '2024-01-01';  -- ✗ Scan発生

インデックススキャンの特徴

• ⚠️ 遅い: すべてのインデックスページをアクセス
• ⚠️ I/O増加: 大量のページ読み取り
• ⚠️ スケール問題: データ量に比例して遅くなる
• 📊 論理読み取り: 数千～数万ページ以上

テーブルスキャン（Table Scan）

定義: インデックスを使わずに、テーブル全体を読み取る方法

-- ヒープ（クラスタードインデックスなし）の場合
-- インデックスが使用できない、または存在しない場合に発生

-- 例: インデックスが存在しない列での検索
SELECT * FROM Orders WHERE Notes LIKE '%urgent%';
-- Notesにインデックスがない → Table Scan

テーブルスキャンの特徴

• ❌ 非常に遅い: テーブル全体を読み取り
• ❌ 最悪のパフォーマンス: 最も多くのI/O
• ⚠️ 小さいテーブルでは許容: 数百行程度なら問題なし

Seek vs Scan: 実例

-- テーブル: Orders（100万行）
-- インデックス: IX_Orders_OrderDate (OrderDate)
-- インデックス: IX_Orders_CustomerID (CustomerID)

-- ✅ Index Seek（高速）
SELECT * FROM Orders WHERE OrderDate = '2024-01-15';
-- 実行: IX_Orders_OrderDateでSeek
-- 論理読み取り: 5ページ
-- 実行時間: 1ms

-- ⚠️ Index Scan（遅い）
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;
-- 実行: IX_Orders_OrderDateでScan（関数使用のため）
-- 論理読み取り: 5,000ページ
-- 実行時間: 500ms

-- 改善策: 関数を使わない
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';
-- 実行: IX_Orders_OrderDateでSeek
-- 論理読み取り: 1,000ページ（2024年のデータのみ）
-- 実行時間: 100ms

判断基準: いつSeekでいつScanか

条件	操作	理由
取得行数 < 全体の1%	Index Seek	少数の行を効率的に取得
取得行数 > 全体の20%	Index/Table Scan	Seekの繰り返しより一括読み取りが効率的
WHERE句なし	Table/Index Scan	全行取得のため
インデックスキーの等号条件	Index Seek	B-Tree探索が最適
関数使用	Index Scan	インデックスキーが変換されるため
小さいテーブル（<1000行）	Table Scan	Seekのオーバーヘッドより高速

実行プラン（Execution Plan）

実行プランとは

実行プランは、SQL Serverがクエリを実行するために選択した具体的な手順を示したものです。クエリオプティマイザが生成する「最適な実行方法の設計図」です。

実行プランの重要性

1. パフォーマンスボトルネックの特定
2. インデックスの効果確認
3. クエリ最適化の方向性決定
4. 予期しない動作の診断

実行プランの種類

1. 推定実行プラン（Estimated Execution Plan）

-- SQL Server Management Studio (SSMS)
-- Ctrl + L または メニューから「推定実行プランの表示」

• クエリを実行せずに表示
• オプティマイザが生成するプラン
• 統計情報に基づく予測

2. 実際の実行プラン（Actual Execution Plan）

-- SQL Server Management Studio (SSMS)
-- Ctrl + M で有効化してからクエリ実行
-- または メニューから「実際の実行プランを含める」

• クエリを実際に実行して取得
• 実際の行数、実行時間を含む
• パフォーマンス分析に最適

実行プランの表示方法

方法1: SSMSのグラフィカル実行プラン

-- 1. SSMSでクエリを記述
SELECT c.CustomerID, c.CustomerName, o.OrderDate, o.TotalAmount
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2024-01-01';

-- 2. Ctrl + M を押して「実際の実行プランを含める」を有効化
-- 3. クエリを実行
-- 4. 「実行プラン」タブを開く

グラフィカルプランの見方:

                     SELECT
                       ↑
                  Nested Loops Join (80%)
                   ↗         ↖
    Index Seek (10%)      Index Seek (10%)
    IX_Customers_PK       IX_Orders_OrderDate
          ↑                     ↑
    [Customers]           [Orders]

各オペレータに表示される情報:
- オペレータ名（Index Seek、Nested Loops Joinなど）
- コスト（%）: クエリ全体に対する相対コスト
- 推定行数 vs 実際の行数
- 推定実行回数

方法2: SET STATISTICS プロファイル

-- テキスト形式の実行プラン
SET SHOWPLAN_TEXT ON;
GO

SELECT * FROM Orders WHERE OrderDate = '2024-01-15';
GO

SET SHOWPLAN_TEXT OFF;
GO

-- 出力例:
-- StmtText
-- |--Index Seek(OBJECT:([Orders].[IX_Orders_OrderDate]), 
--              SEEK:([OrderDate]='2024-01-15'))

方法3: SET STATISTICS IO/TIME

-- I/O統計とタイミング情報を表示
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM Orders WHERE OrderDate = '2024-01-15';

-- 出力:
-- Table 'Orders'. Scan count 1, logical reads 5, physical reads 0
-- SQL Server Execution Times:
--    CPU time = 0 ms,  elapsed time = 1 ms.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

方法4: XML形式の実行プラン

SET SHOWPLAN_XML ON;
GO

SELECT * FROM Orders WHERE OrderDate = '2024-01-15';
GO

SET SHOWPLAN_XML OFF;
GO

-- XMLで詳細な情報を取得（プログラム解析に便利）

実行プランの主要なオペレータ

1. データアクセスオペレータ

Clustered Index Scan

-- クラスタードインデックス全体をスキャン
SELECT * FROM Orders;

-- 実行プラン:
-- Clustered Index Scan (Orders)
-- コスト: 100%
-- 論理読み取り: 10,000ページ

Clustered Index Seek

-- クラスタードインデックスで特定行を検索
SELECT * FROM Orders WHERE OrderID = 123;

-- 実行プラン:
-- Clustered Index Seek (Orders.PK_Orders)
-- Seek Predicates: OrderID = 123
-- コスト: 0.0033%
-- 論理読み取り: 3ページ

Index Scan (Non-Clustered)

-- ノンクラスタードインデックス全体をスキャン
SELECT OrderDate FROM Orders;

-- 実行プラン:
-- Index Scan (Orders.IX_Orders_OrderDate)
-- コスト: 50%
-- 論理読み取り: 2,000ページ

Index Seek (Non-Clustered)

-- ノンクラスタードインデックスで特定行を検索
SELECT OrderID FROM Orders WHERE OrderDate = '2024-01-15';

-- 実行プラン:
-- Index Seek (Orders.IX_Orders_OrderDate)
-- Seek Predicates: OrderDate = '2024-01-15'
-- コスト: 0.01%
-- 論理読み取り: 5ページ

Key Lookup

-- インデックスにない列を取得するための追加アクセス
SELECT OrderID, OrderDate, CustomerName, TotalAmount
FROM Orders
WHERE OrderDate = '2024-01-15';

-- 実行プラン:
-- Nested Loops Join
--   ├─ Index Seek (IX_Orders_OrderDate)  -- OrderDateで検索
--   └─ Key Lookup (PK_Orders)            -- 残りの列を取得
-- ⚠️ Key Lookupは高コスト（各行ごとにクラスタードアクセス）

Table Scan

-- ヒープテーブル全体をスキャン（クラスタードなし）
SELECT * FROM TempData;  -- クラスタードインデックスなし

-- 実行プラン:
-- Table Scan (TempData)
-- コスト: 100%
-- 最悪のパフォーマンス

2. 結合オペレータ

Nested Loops Join

-- 小さいデータセット同士の結合に最適
SELECT c.CustomerName, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID = 123;

-- 実行プラン:
-- Nested Loops Join
--   ├─ Index Seek (Customers.PK_Customers)  -- 外側ループ（駆動表）
--   └─ Index Seek (Orders.IX_Orders_CustomerID)  -- 内側ループ
-- 特性: 外側の各行に対して内側を検索（ループ）
-- 適用: 小さいデータセット、インデックスあり

Hash Match Join

-- 大きいデータセット同士の結合に最適
SELECT c.CustomerName, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 実行プラン:
-- Hash Match (Inner Join)
--   ├─ Index Scan (Customers)  -- Build入力（ハッシュテーブル構築）
--   └─ Index Scan (Orders)     -- Probe入力（ハッシュテーブル検索）
-- 特性: ハッシュテーブルを作成してマッチング
-- 適用: 大きいデータセット、インデックスなし

Merge Join

-- ソート済みデータの結合に最適
SELECT c.CustomerName, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID;

-- 実行プラン:
-- Merge Join (Inner Join)
--   ├─ Index Scan (Customers.PK_Customers) -- ソート済み
--   └─ Index Scan (Orders.IX_Orders_CustomerID) -- ソート済み
-- 特性: 両方のデータをソート済み前提でマージ
-- 適用: ソート済みデータ、1対多の結合

3. 集計・ソートオペレータ

Sort

-- データをソート
SELECT * FROM Orders ORDER BY TotalAmount DESC;

-- 実行プラン:
-- Sort (ORDER BY: TotalAmount DESC)
--   └─ Clustered Index Scan (Orders)
-- ⚠️ Sortは高コスト（tempdbを使用することも）

Stream Aggregate

-- ソート済みデータの集計（効率的）
SELECT CustomerID, COUNT(*), SUM(TotalAmount)
FROM Orders
GROUP BY CustomerID;

-- 実行プラン（インデックスがCustomerID順の場合）:
-- Stream Aggregate (GROUP BY: CustomerID)
--   └─ Index Scan (IX_Orders_CustomerID) -- すでにソート済み
-- 特性: ソート済みデータを順次集計

Hash Aggregate

-- ソート不要の集計（大量データ向け）
SELECT Status, COUNT(*)
FROM Orders
GROUP BY Status;

-- 実行プラン:
-- Hash Aggregate (GROUP BY: Status)
--   └─ Clustered Index Scan (Orders)
-- 特性: ハッシュテーブルで集計

実行プランの読み方

1. コストを確認

クエリコスト: 100%
├─ Nested Loops Join: 80% ⚠️ 最も高コスト
│  ├─ Index Scan: 15%
│  └─ Key Lookup: 65% ⚠️ ボトルネック
└─ Index Seek: 20%

判断基準:

• 50%以上: 最適化の対象
• Key Lookup: カバリングインデックスを検討
• Scan: Seekに変更できないか確認

2. 行数の不一致を確認

オペレータ: Index Seek
├─ 推定行数: 10
└─ 実際の行数: 100,000 ⚠️ 大きな乖離

原因:

• 統計情報が古い
• データ分布が偏っている

対策:

-- 統計更新
UPDATE STATISTICS Orders WITH FULLSCAN;

3. 警告マークを確認

⚠️ 警告: 暗黙の型変換
⚠️ 警告: 統計情報なし
⚠️ 警告: メモリ不足による spillover

実行プラン最適化の実例

例1: Key Lookupの除去

問題のあるクエリ:

-- クエリ
SELECT OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
WHERE OrderDate = '2024-01-15';

-- 実行プラン:
-- Nested Loops Join (コスト: 100%)
--   ├─ Index Seek (IX_Orders_OrderDate): 10%
--   └─ Key Lookup (PK_Orders): 90% ⚠️ ボトルネック

解決策: カバリングインデックス

-- インデックス作成
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Covering
ON Orders (OrderDate)
INCLUDE (CustomerID, TotalAmount);

-- 改善後の実行プラン:
-- Index Seek (IX_Orders_OrderDate_Covering): 100%
-- Key Lookup が消滅！

例2: Index ScanからIndex Seekへ

問題のあるクエリ:

-- クエリ
SELECT * FROM Orders
WHERE YEAR(OrderDate) = 2024;

-- 実行プラン:
-- Clustered Index Scan (コスト: 100%) ⚠️ 全スキャン
-- 論理読み取り: 10,000ページ

解決策: 関数を除去

-- 改善後のクエリ
SELECT * FROM Orders
WHERE OrderDate >= '2024-01-01' 
  AND OrderDate < '2025-01-01';

-- 改善後の実行プラン:
-- Clustered Index Seek (コスト: 20%)
-- 論理読み取り: 2,000ページ（80%削減！）

例3: 適切なインデックスの追加

問題のあるクエリ:

-- クエリ
SELECT * FROM Orders
WHERE CustomerID = 123 AND Status = 'Active';

-- 実行プラン:
-- Clustered Index Scan (コスト: 100%) ⚠️ インデックスなし
-- 論理読み取り: 10,000ページ

解決策: 複合インデックス

-- 複合インデックス作成
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Status
ON Orders (CustomerID, Status);

-- 改善後の実行プラン:
-- Index Seek (IX_Orders_CustomerID_Status): 100%
-- 論理読み取り: 5ページ（99.95%削減！）

インデックス設計のベストプラクティス

1. クラスタードインデックスの選択

-- ✅ 推奨: 単調増加する値
CREATE TABLE Orders
(
    OrderID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,  -- 順次増加
    OrderDate DATETIME,
    CustomerID INT
);

-- ❌ 非推奨: ランダムな値（GUID）
CREATE TABLE Orders
(
    OrderID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY CLUSTERED,  -- ランダム
    OrderDate DATETIME,
    CustomerID INT
);
-- 問題: ページ分割が頻発、断片化が進行

推奨基準:

• ✅ 単調増加（IDENTITY、SEQUENCE）
• ✅ 範囲検索が多い列
• ✅ GROUP BY、ORDER BYで頻繁に使用
• ❌ 頻繁に更新される列
• ❌ ランダムな値（NEWID()）

2. ノンクラスタードインデックスの選択

-- ✅ 推奨: WHERE句で頻繁に使用される列
CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID);

-- ✅ 推奨: JOIN列
CREATE INDEX IX_OrderDetails_OrderID ON OrderDetails (OrderID);

-- ✅ 推奨: 複合インデックス（検索条件の組み合わせ）
CREATE INDEX IX_Orders_CustomerID_Status 
ON Orders (CustomerID, Status);

-- ⚠️ 注意: 選択性が低い列（値の種類が少ない）
CREATE INDEX IX_Orders_Status ON Orders (Status);
-- Status が 'Active', 'Inactive' の2値のみ → 効果が限定的

推奨基準:

• ✅ WHERE句、JOIN句で頻繁に使用
• ✅ 選択性が高い（値の種類が多い）
• ✅ 複合インデックスは最も選択性の高い列を先頭に
• ❌ 選択性が非常に低い列（性別、フラグなど）
• ❌ 頻繁に更新される列にむやみに作成しない

3. INCLUDE句の活用

-- ✅ カバリングインデックスでKey Lookupを回避
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Covering
ON Orders (CustomerID)  -- キー列
INCLUDE (OrderDate, TotalAmount, Status);  -- 付加列

-- このクエリはインデックスだけで完結
SELECT CustomerID, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerID = 123;
-- Key Lookupなし → 高速！

INCLUDE の利点:

• リーフレベルにのみ格納（B-Treeの上位層には含まれない）
• インデックスサイズの増加を抑制
• Key Lookupを回避

4. フィルター付きインデックス

-- ✅ 特定条件のデータのみをインデックス化
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders (OrderDate)
WHERE Status = 'Active';

-- このクエリは小さいインデックスを使用
SELECT * FROM Orders
WHERE Status = 'Active' AND OrderDate >= '2024-01-01';
-- インデックスサイズ: Activeのデータのみ → 小さい → 高速

フィルター付きインデックスの利点:

• インデックスサイズ削減
• メンテナンスコスト削減
• 特定クエリの最適化

5. インデックスの数を適切に

-- ❌ 過剰なインデックス
CREATE INDEX IX_1 ON Orders (CustomerID);
CREATE INDEX IX_2 ON Orders (OrderDate);
CREATE INDEX IX_3 ON Orders (Status);
CREATE INDEX IX_4 ON Orders (TotalAmount);
CREATE INDEX IX_5 ON Orders (ShipDate);
-- ... 20個のインデックス

-- 問題:
-- - 更新時のオーバーヘッド増大
-- - ストレージ消費増加
-- - メンテナンスコスト増加

推奨:

• 読み取り重視: 5-10個程度
• 書き込み重視: 3-5個程度
• 未使用インデックスの削除: 定期的に確認

-- 未使用インデックスの確認
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND s.user_seeks = 0 
  AND s.user_scans = 0 
  AND s.user_lookups = 0
ORDER BY s.user_updates DESC;

6. 複合インデックスの列順序

-- インデックス: IX_Orders (CustomerID, OrderDate, Status)

-- ✅ 使用可能（先頭列を使用）
SELECT * FROM Orders WHERE CustomerID = 123;
SELECT * FROM Orders WHERE CustomerID = 123 AND OrderDate = '2024-01-01';
SELECT * FROM Orders WHERE CustomerID = 123 AND OrderDate = '2024-01-01' AND Status = 'Active';

-- ⚠️ 一部使用（先頭列のみ）
SELECT * FROM Orders WHERE CustomerID = 123 AND Status = 'Active';
-- OrderDateが抜けているが、CustomerIDは使用可能

-- ❌ 使用不可（先頭列を使わない）
SELECT * FROM Orders WHERE OrderDate = '2024-01-01';
SELECT * FROM Orders WHERE Status = 'Active';
-- CustomerIDが条件にない → インデックス使用不可

列順序の原則:

1. 最も選択性の高い列を先頭に
2. 等号条件（=）を範囲条件（>, <, BETWEEN）より前に
3. WHERE句で最も頻繁に使用される列を先頭に

実行プラン分析の実践例

ケーススタディ: 遅いクエリの最適化

初期状態:

-- 問題のクエリ
SELECT 
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2024-01-01'
  AND o.Status = 'Active'
ORDER BY o.TotalAmount DESC;

-- パフォーマンス:
-- 実行時間: 5秒
-- 論理読み取り: 50,000ページ

ステップ1: 実行プランの確認

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 実行プランを確認（Ctrl + M）

-- 結果:
-- Clustered Index Scan on Orders (コスト: 80%) ⚠️ 全スキャン
-- Nested Loops Join (コスト: 15%)
-- Key Lookup on Customers (コスト: 5%)
-- Sort (TotalAmount DESC) (コスト: tempdb使用)

ステップ2: インデックスの追加

-- 複合インデックス作成
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Status
ON Orders (OrderDate, Status)
INCLUDE (CustomerID, TotalAmount);

-- OrderDateとStatusでSeek可能
-- CustomerIDとTotalAmountもインデックスに含めてKey Lookup回避

ステップ3: 改善後の確認

-- 同じクエリを再実行

-- 改善後の実行プラン:
-- Index Seek on IX_Orders_OrderDate_Status (コスト: 60%)
-- Nested Loops Join (コスト: 30%)
-- Key Lookup on Customers (コスト: 10%) ⚠️ まだ存在

-- パフォーマンス:
-- 実行時間: 1.5秒（70%改善）
-- 論理読み取り: 15,000ページ（70%削減）

ステップ4: さらに最適化

-- Customersのカバリングインデックス追加
CREATE NONCLUSTERED INDEX IX_Customers_CustomerID
ON Customers (CustomerID)
INCLUDE (CustomerName);

-- 最終的な実行プラン:
-- Index Seek on IX_Orders_OrderDate_Status (コスト: 70%)
-- Nested Loops Join (コスト: 20%)
-- Index Seek on IX_Customers_CustomerID (コスト: 10%)

-- 最終パフォーマンス:
-- 実行時間: 0.5秒（90%改善！）
-- 論理読み取り: 5,000ページ（90%削減！）

まとめ

クラスタードインデックス

• ✅ テーブルごとに1つ
• ✅ データそのものがインデックス順に配置
• ✅ 範囲検索に最適
• ⚠️ 単調増加する値を推奨（IDENTITY）
• ❌ ランダムな値（GUID）は避ける

ノンクラスタードインデックス

• ✅ テーブルごとに最大999個
• ✅ WHERE句、JOIN列に作成
• ✅ INCLUDE句でカバリングインデックス化
• ⚠️ 適切な数を維持（5-10個程度）
• ❌ 過剰なインデックスは更新コスト増

インデックスシーク vs スキャン

• ✅ Seek: 特定の行を効率的に検索（高速）
• ⚠️ Scan: インデックス全体をスキャン（遅い）
• 🎯 目標: Seekを最大化、Scanを最小化

実行プラン

• ✅ 実際の実行プランで分析（Ctrl + M）
• ✅ コストが50%以上のオペレータを最適化
• ✅ Key Lookupはカバリングインデックスで解決
• ✅ 推定行数と実際の行数の乖離を確認
• 🔧 統計更新、インデックス追加で改善

ベストプラクティス

1. 適切なインデックス設計

   • クラスタード: 単調増加する値
   • ノンクラスタード: WHERE句、JOIN列

2. 定期的な監視

   • 実行プランの確認
   • 未使用インデックスの削除
   • 統計情報の更新

3. 継続的な最適化

   • クエリパフォーマンスの測定
   • ボトルネックの特定と改善
   • A/Bテストで効果検証

参考リンク

• Microsoft Learn: SQL Server インデックス アーキテクチャとデザイン ガイド
• Microsoft Learn: クラスター化インデックスと非クラスター化インデックスの概念
• Microsoft Learn: 実行プランの表示と保存
• Microsoft Learn: パフォーマンスの監視とチューニング