SQL Server 非クラスター化インデックスの動作と実行プランについて
はじめに
SQL Server の検索速度を上げる方法にインデックスの利用があります。
今回は「非クラスター化インデックス」についてまとめます。
学習に使用した資料は公式HPのSQL Server 2012 自習書シリーズになります。
SQL Server 2012 | マイクロソフト クラウド プラットフォーム
データベースは自習書シリーズに付属しているサンプルを使用しています。
詳細は以下の記事を参考にして下さい。
knkomko.hatenablog.com
動作環境
・Windows 10 (バージョン 1909)
・SQL Server 2017 Express
Download Microsoft® SQL Server® 2017 Express from Official Microsoft Download Center
・Microsoft SQL Server Management Studio 18
SQL Server Management Studio (SSMS) のダウンロード - SQL Server Management Studio (SSMS) | Microsoft Docs
非クラスター化インデックスとは
ヒープ領域に保存されている実データへのポインタを持つインデックスです。
そのためインデックスを使用したSQLは以下のように動作します。
①インデックスを検索する Index Seek の動作
②検索したインデックスのポインタを辿って実データを読む RID Lookup の動作
引用 : インデックスの基礎とメンテナンス
実行プランの動作
実行プランの動作については以下のスライドが参考になりました。
④Clusted Index Scan、⑤Clusted Index Seek はクラスター化インデックスを使用した場合に行う為、非クラスター化インデックスでは使用されません。
引用 : https://www.slideshare.net/ssuser89be8d/index-tuning-129457451/12
インデックスを使用しない場合の実行プラン
最も遅い「Table Scan」を行っています。
インデックスを使用した場合の実行プラン
インデックスを姓列に作成します。
オブジェクトエクスプローラーから作成したインデックスが確認できます。
先ほどと同じSQLを実行すると高速な検索「Index Seek」を行っています。
インデックスを作成しても効果の無い列
1. WHERE句の検索条件にほとんど使用されない列
インデックスを作成した列のデータを更新した際にインデックスのツリー構造も更新されるためパフォーマンスが低下する場合があります。
2. 検索条件に該当するデータが大量にある場合
該当するデータが大量にあると RID Lookupによるデータの取得が、最も遅い「Table Scan」より効率が悪くなる場合があります。
検索結果に該当するデータが大量にある場合の実行プラン
LIKE文では295件のデータが該当し最も遅い「Table Scan」が行われました。
インデックスを使用していてもインデックスを利用しない検索が行われています。
"Aoki"で検索していた時は高速な検索「Index Seek」が行われていました。
