【SQL Server】インデックスと実行計画について解説！

各インデックスの特徴や実行計画の見方について言及しています！

学習教材・参考

絵で見てわかるSQLServerの仕組み

リンク

第5章「行ストア型テーブル」でインデックスについて詳しい解説がありオススメです。

達人に学ぶDB設計 徹底指南章

リンク

達人に学ぶDB設計 徹底指南章 を読んでみた感想や1部内容紹介をしているので是非読んでみてください！

達人に学ぶDB設計 徹底指南章 を読んでみた感想

バックエンド

【書籍紹介】達人に学ぶDB設計 徹底指南章 を読んでみた感想

2024/1/5    DB, 書籍

達人に学ぶDB設計 徹底指南章 というDB書籍を読んだ感想や重要だと思った箇所をピックアップしてご紹介します！

動画だと、ピーコックアンダーソン という方のUdemy講座がハンズオンで学べてわかりやすいです！

SQL Server のインストール

それぞれ以下のリンク先の参照をお願いします。

Windows環境

DBサーバ：SQL Server
DBクライアントツール：SQL Server Management Studio（日本語版）

Mac環境

DBサーバ：Docker 上にSQL Serverのコンテナ作成
DBクライアントツール：Azure Data Studio

概要

前提、仮定

テストデータとして、以下の様なテーブル・データがある前提として解説していきます。データ量が少ないとインデックスが効かずテーブルを全件検索するような処理になることもあるのである程度のデータ量が必要になります。

-- ---------------------
-- 取引
-- ---------------------
CREATE TABLE blog.sales (
    sales_id INT NOT NULL
    , amount INT NOT NULL
    , transaction_date DATE NOT NULL
    , status VARCHAR(20) NOT NULL
    , cancel_flg BIT NOT NULL
    , shop_id INT NOT NULL
    , user_id INT NOT NULL 
    , PRIMARY KEY(sales_id)
    , FOREIGN KEY(user_id) REFERENCES blog.my_user(user_id) -- ユーザテーブルがある前提
    , FOREIGN KEY(shop_id) REFERENCES blog.shop(shop_id) -- 店舗テーブルがある前提
)

インデックスの種類

インデックスにはクラスター化インデックスと非クラスター化インデックスの大きく分けて2種類のインデックスがあります。それぞれ特性が異なるので、以下で解説します。

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

テーブルに対して1つだけ設定でき、最も高速な検索を実現するインデックスです。リーフノードにデータページが格納されているため、インデックスページからデータページへのアクセスをする必要がなく、インデックスページを辿ることでデータを取得できます。インデックスを設定したキーの順番通りにデータが物理的に並ぶため、テーブルに1つしか作成することはできません。

クラスター化インデックスの作成方法は、プライマリーキーを作成する or 以下のようにDDLでインデックスを作成します（WindowsだとツールでGUIからも作れたりします）。

CREATE CLUSTERED INDEX clustered_index ON blog.sales (sales_id);

5,000万件のデータがある状態でも、クラスター化インデックスを指定のキーで検索した場合は一瞬でデータ取得が可能です。クラスター化インデックスが効いている場合は、「Clustered Index Seek」という表示になります。Seek というのがインデックスが効いている証拠です。

クラスター化インデックスのキー以外で検索を実施すると、Scanになる場合があります。クラスター化インデックスが作成されているテーブルでは、「Table Scan」とは表示されず「Clsutered Index Scan」と表示されます。ぱっと見ではインデックスが効いているようにも見えますが、「Table Scan」とほぼ同等で遅い検索になります。

select * 
from blog.sales 
where transaction_date = '2022-01-01'

まとめると以下のような特徴があります。

• 最も高速な検索ができる
• テーブルに1つしか作成できない
• キーの順番で物理的にデータが並ぶ
• リーフノードにデータが存在する（Lookupしない）
• プライマリーキーにセットしたキーはクラスター化インデックスのキーとなる
• クラスター化インデックスがない状態のでScan：Table Scan
• クラスター化インデックスがある状態のでScan：Clustered Index Scan

非クラスター化インデックス

テーブルに複数の作成できるインデックスで、インデックスページを辿った後にデータページへアクセスしデータを取得するインデックスです。主にこちらのインデックスをチューニングすることが多いかと思います。

取引テーブルのステータスカラムをキーにインデックスを作成するDDLは以下のようになります。「NONCLUSTERED」はなくても非クラスター化インデックスとなります（上の例では取引日カラムの例ですが、うまいこと実行計画が想定通りにならなかったため、ステータスカラムで説明させていだきます）。

CREATE INDEX IX_sales_01 ON blog.sales(status);
-- または、
CREATE NONCLUSTERED INDEX IX_sales_01 ON blog.sales(status);

ステータスカラムでの検索をインデックス作成前後で比較してみます。

select * 
from blog.sales 
where status = 'failure'

インデックス作成前

インデックス作成後

インデックス作成後ですと、「Index Seek」となりインデックスが効いていることがわかります。また、「Key Lookup」とあるのでインデックスページを辿った後に、データページへアクセスしていることがわかります。

Lookupの方法は、テーブルにクラスター化インデックスがある場合とない場合異なります。クラスター化インデックスがない状態では、RID Lookup（ポインタ参照）といって、データページのポインタをリーフノードに持ち、データを取得しにいきます。クラスター化インデックスがある状態では、Key Lookup（キー参照）といって、リーフノードにはクラスター化インデックスのキー情報を持ち、データページへデータ取得しにいきます。

ただし、このLookupは意外とコストが高くインデックスが効いているのに、思ったよりも性能が出ないことがあります（画像の例ですが、Index Seekが17% / Key Lookupが83%であることからもデータページへのアクセスコストは高いことがわかります）。この場合に有効なのが次に説明する付加列インデックスになります。

Lookupにより性能が出ない場合は付加列インデックスを使用することを検討しましょう。

非クラスター化インデックスについてのまとめ

• テーブルに1つしか作成できない
• インデックスページを辿った後にデータページを参照する
• クラスター化インデックスがない状態ので参照：RID Lookup（ポインタ参照）
• クラスター化インデックスがある状態ので参照：Key Lookup（キー参照）

付加列インデックス

非クラスターインデックスの拡張版インデックスで、特定のカラムをSELECTする場合に有効なインデックスになります。内部構造としては、インデックスページのリーフノードに特定のカラムのデータを持たせるというものです。基本的にインデックスページにはキーになるカラムのデータしか持ちませんが、追加でSELECTしたいカラムがある場合にデータを2重持ちさせるような方法になります。

特定のカラムでなく＊で全カラムを取得する場合（通常の非クラスター化インデックス）

select *
from blog.sales 
where status = 'failure'

上記の例では全てのカラムを取得するような検索でしたが、ステータス・取引ID・ユーザIDだけが必要な場合と仮定します。この場合は付加列（INCLUDE句）に取引IDとユーザIDを含めることでLookupせずにデータ取得することができます。

付加列インデックスのDDLは以下のようになります。

CREATE INDEX IX_sales_02 ON blog.sales(status) INCLUDE(sales_id,user_id);

各ノードには、ON句に指定した検索条件となるキーカラム（この場合、ステータス）の情報はあります。リーフノードにのみ特定のカラム（この場合、取引ID / ユーザID）を追加して、データページとデータを2重持ちにすることで、Lookupすることなく高速な検索を実現します。

各ノードに持たせずリーフノードにだけ持たせるというのがミソです。全てのノードに必要なカラムを持たせるの（ON句にカラム指定）はカバリングインデックスと言い、付加列インデックスよりも高速になりますが、データ容量をより必要とするため、デメリットが大きくなります。高速な検索をしつつ、カバリングインデックスよりもデータ容量を節約できるのが付加列インデックスになります。

特定のカラムのみをSELECTするようなクエリを実行すると、

select sales_id,user_id,status
from blog.sales 
where status = 'failure'

「Key Lookup」がなくなり「Index Seek」のみの実行計画で、データページへのアクセスがなくなっていることがわかります。

だたし、前述の通り付加列インデックスはデータページとデータを2重持ちになるため、更新系の処理が更に遅くなります。そのため無闇にINCLUDEするのではなく、最低限必要なカラムを選択することが重要になります。また、INCLUDEに指定したカラム以外を1つでもSELECTするとLookupしてしまいます。

付加列インデックスのまとめ

• 非クラスター化インデックスの拡張版
• リーフノードにのみ付加列（INCLUDE）したデータを持つ
• 付加列に指定したデータのみを参照する場合はデータページへ参照しないため高速

まとめ

SQL Serverの内部構造と実行計画の基本的な読み方を抑えた上で、用途に合わせてインデックスを使い分けることが重要です。クラスター化インデックスは1つしか作成できないので、無闇にプライマリーキーをセットするのではなく、実用的なキーにセットすることが求められます。また、インデックスは作成すればするほど更新系の処理が遅くなるため、最適なインデックスを最適な数だけ作成することも、チューニングする上では考慮しなければなりません。

実際に性能改善にてSQL Serverを用いたバッチ処理の改善内容についても以下の記事で言及しているので、是非読んでみてください！