SQLServerのインデックスを理解して検索を高速にする方法
Amzon Kindle unlimited にしているが、
1か月に1冊ぐらいしか読んでいない。
解約しようか考えていて月日が過ぎていたが、
あるとき、ピーコック アンダーソン さんのプログラム開発系の本がいっぱい出ているのを発見。知っているつもり知らないことがいろいろあったので、お勉強開始。
ただ読んでいるだけだとまた忘れてしまうので、備忘録として残します。
今回は、「SQLServerのインデックスを理解して検索を高速にする方法」です。
Amazon.co.jp: SQLServerのインデックスを理解して検索を高速にする方法 eBook : ピーコックアンダーソン: 本
というこで、まず、家のパソコンにSQL Server 2019 Express Edition と SSMS のインストールから。無事に SSMS でローカルDBに接続確認。
テスト用データのセット
- IndA DBを作成、Shain テーブル作成
- 50,000件でShaiレコードInsrt(本でSQLを用意してくれている)
キャッシュをクリアするコマンド
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
インデックスが全くない場合
「実際の実行プランを含める」のボタンを押して実行
実行プランをみると「Table Scan」となる。
「Table Scan」は全レコード読み込みなのでNGとなる
インデックスには、「クラスター化インデックス」と「非クラスター化インデックス」の2つがある。
ちなみにクラスターとはどういう意味なのか。クラスター発生とか、クラスター爆弾と聞くし。
クラスターとは、もとはブドウの房の意味であり、そこから転じて、ある属性に基づくグループ化された集団を意味する語のこと。具体的には、年齢や性別、居住地域などの人口統計学に基づくデータや、趣味、生活スタイル、思想などの心理的側面に基づくデータからグループ分けした集団を指す。日本語では、「群れ、集団、塊、房」と表現する。クラスターは、さまざまな分野で用いられる用語である。IT分野では、複数のコンピュータを統合させて、1つのシステムとしてまとめることをクラスターという。「クラスターあたりの空き容量を増やす」など、ディスク装置のメモリの単位としても使用される。
インデックスの内部構造
インデックスの内部構造のイメージはキー項目にした内容でソートされた索引ができ、その末端が本当のデータになるのが「クラスター化インデックス」で、データレコードへのリンク先が書いてるのが「非クラスター化インデックス」
非クラスタ化インデックス
ここから作成
id のみで作成してみる
作成した非クラスター化インデックス(NonClusterIndex-id)の
Index Seek となった。
Seek は、全レコードではなく1部のみ検索なので高速!
Seekは位置を指定してピンポイントで取得している。
ScanはスキャナーのScanと同じで、すべてを読み取るという意味
そして、RID Look UP で、Seekで見つけたインデックス id の末端(リーフページ)の実データへのリンク情報から、そのレコードの他の情報を取得している。となる。
ちなみに、インデックスだけで項目が足りている場合、IndexSeek のみで完結。
試しにインデックス項目ではないもので検索。
Table Scan に戻る。なるほど。
クラスタ化インデックス
ここから作成
「クラスター化インデックスシーク」に変わった!
クラスター化インデックスの内部構造のイメージ
クラスター化インデックスを作ると、実データがなくなり、クラスター化インデックスのリーフページに実データが存在する状態になる。なので、上記のように全項目のSelectをしても、実行プランがインデックスシークのみ。リーフページから実データへみたいな(RID Loop up のようなもの)のがない。
ちなみに、非クラスターインデックス項目のidで検索すると、
前回(クラスター化インデックスがない場合)RID Loop だったのが、キー参照に変わる。これは、クラスター化インデックスのリーフページ(実データ)の位置リンクを参照しているということになる。
付加列インデックス
非クラスター化インデックスだけではどうしてもパフォーマンスがでないとき、リーフページに実データのリンク以外に実データももってしまえ、というものです。
たとえば、インデックスに含まれていない「Mei」を入れると、
「キー参照」になります。
ここで、付加列インデックスとしてMeiを追加してみる。
この「含まれる列」で選択。
Index Seek のみで完結しました!
ただ、付加列インデックスは、データの二重持ちになるので、必要ないならばやらないほうがよいそうです。データの更新には時間がかかるようになるし。本当の必要なときのみ。
まとめ
通常、テーブルを作成するときはキー項目でインデックスを作成するので、クラスター化インデックスは必ずできあがるので、非クラスタ化インデックスの作成、付加列インデックスをどのように入れるか、実行計画見ながら検討。
SQLが遅いのを見つけたら(設計段階で)、
- キー項目ではないが検索条件に使うもの、結合条件に使うもので非クラスタ化インデックスを作成。
- 表示項目でRIDLookUpやキー参照になっている項目を付加列インデックスに入れてみる。
という感じか。
後、likeとか、関数とか、検索条件を大量にしているときはインデックスがうまくきいてない場合あり。実行計画で確認。