前回はクラスター化インデックスの内部構造を解説しました。
目次
クラスター化インデックスを作成したことによる変化
現状はCreateDate指定で検索するとクラスター化インデックスシークになるという状態になりましたが，以前実施していたId指定でのSQLをもう一度実行してみましょう。
|
1 2 3 4 |
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE select * from Shain where Id = 1 |
実行プランの確認
実行プランを確認すると，以前と変化している場所があります。IndexSeekになっている場所は以前と同じです。ちがうのは，「キー参照」となっている場所です。これは以前RIDLookupになっていましたね。でも現在は「キー参照」になっています。これはクラスター化インデックスを作成したことによる変化です。
キー参照とは
先述した通り，クラスター化インデックスを作成したことによって，実データという考え方が無くなり，クラスター化インデックスの終端であるリーフページに実データが移りました。なので，実データの時に使っていた実データのファイルの場所と行番号であるRIDという考え方もなくなりました。では代わりにどうなったかというと，非クラスター化インデックスのリープページには，RIDの代わりに，クラスター化インデックスを検索するためのKeyが入っています。今回の例でいえばCreateDateが入っているという事になります。なので，Idの非クラスター化インデックスの終端には，IdとCreateDateが入っています。
非クラスター化インデックスの内部構造（クラスター化インデックスありの場合）
図のように，非クラスター化インデックスのリープページには，クラスター化インデックスのKeyが入っています。今までは，RIDを参照して実データを検索していたので，RIDLookupになっていましたが，クラスター化インデックスを作成後は，クラスター化インデックスのKeyでクラスター化インデックスを検索しに行くので，実行プランでは「キー参照」という表現になっています。
クラスター化インデックスと非クラスター化インデックスの速度の違い
先述の通り，クラスター化インデックスはリープページに実データがあるので，検索は1回で済みますが，非クラスター化インデックスの場合はリープページにクラスター化インデックスのKeyがあるだけなので，そのKeyでもう一度クラスター化インデックスの検索が必要です。なので非クラスター化インデックスは2段階で検索することになります。という事は当然非クラスター化インデックスの方が，検索には時間がかかります。
|
1 2 3 4 5 6 7 8 9 |
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE select * from Shain where Id = 1 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE select * from Shain where CreateDate = '2010-07-23 12:19:45.000' |
現状このSQLのように検索結果が1行になるような場合は，速度的に違いは感じませんが，検索結果が多い場合は，それだけ非クラスター化インデックスはキー参照が発生するので，その分検索には時間がかかるという事になります。
大量データがヒットするように検索
次のSQLを実行すると10400件がヒットします。
|
1 2 3 4 5 |
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE select * from Shain where CreateDate >= '2010-07-23 12:19:45.000' and CreateDate < '2010-07-30 17:38:45.000' |
これで一度実行してください。
私の環境では検索に「3秒」程度かかりました。
実行プランを確認すると，クラスター化インデックスシークになっているため，インデックスはうまく機能していますが，推定コストを確認すると，1行ヒットする時とくらべてか習い値が大きくなっていることが分かります。1万件のデータを取得するには，それなりに開く必要があるファイルが大量にあったという事です。
続いて，非クラスター化インデックスで確認してみましょう。
|
1 2 3 4 5 |
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE select * from Shain where Id >= 1 and Id < 10400 |
このようにId指定で10400件ヒットするSQLを実行します。
私の環境では7秒かかりました。やはり，非クラスター化インデックスの方が，時間がかかるという事が確認できます。
実行プランを見ると，IndexSeekでキー参照にはなっていますが，推定コストが高いことが分かります。
このようにクラスター化インデックと非クラスター化インデックスの検索の違いはあるので，どうしても速度が出ない非クラスター化インデックスの場合は，テーブル設計を見直す必要があります。例えば今回の社員テーブルの例で言うと，Bikou列が7900バイトでわざと大きな列にしていますが，そのせいで，物理的なファイル量が多くなっています。この辺りを別の表にすることで，キー参照で読み込むファイル数が減るので，検索が速くなるという事は見込めます。
Udemyという学習プラットフォームで動画コースを公開しています。
無料コースはこちらから今すぐご覧いただけます。良かったら観てみてください。
#S1_01_はじめに
#S1_02_インデックスとは
#S1_03_インデックスの有無実演
#S1_04_SQLServerのインストール
#S2_01_データベースとテーブルの作成
#S2_02_データの作成
#S2_03_全件検索
#S2_04_キャッシュとは
#S2_05_検索条件ありで検索
#S2_06_テーブルスキャンとは
#S2_07_インデックスの種類
#S2_08_非クラスター化インデックスの作成
#S2_09_非クラスター化インデックスの内部構造
#S2_10_インデックスのない列の検索とインデックスの有効化無効化
#S2_11_クラスター化インデックスの作成
#S2_12_クラスター化インデックスの内部構造
#S2_13_クラスター化インデックスの検索
#S2_14_インデックスの検索補足
#S2_15_付加列インデックス
#S2_16_プライマリキーとユニークキー
#S2_17_インデックス作成手順
#S2_18_インデックスの注意点
おわりに
すべてのUdemyコースはこちら