[解決済み] SQL Serverでビットフィールドのインデックスを作成すべきですか？

質問

カーディナリティの低い (異なる値の数が少ない) フィールドにインデックスを作成することは、あまり意味がないという記事を読んだ覚えがあります。 私は、それがなぜなのかを理解するために、インデックスがどのように機能するかについて十分な知識がないことを認めます。

では、1 億行のテーブルがあり、ビットフィールドが 1 のレコードを選択する場合はどうでしょうか。そして、どの時点でも、ビット フィールドが 1 であるレコードはほんの一握りであるとします (0とは対照的です)。 そのビットフィールドにインデックスを付ける価値があるのかないのか？なぜでしょうか？

もちろん、私はただテストして実行プランを確認することができますし、そうするつもりですが、その背後にある理論についても興味があります。 カーディナリティはいつ重要で、いつ重要でないのでしょうか？

どのように解決するのか？

SQL におけるインデックスとは何かを考えてみましょう。インデックスとは実際には、他のメモリのチャンク (すなわち行へのポインタ) を指すメモリのチャンクのことです。 インデックスはページに分割され、使用状況に応じてインデックスの一部をメモリからロードおよびアンロードすることができます。

行のセットを要求するとき、SQL はインデックスを使用して、テーブルスキャン（すべての行を見ること）よりも迅速に行を見つけます。

SQLにはクラスタ化インデックスと非クラスタ化インデックスがあります。 私の理解では、クラスタ化インデックスは、類似したインデックス値を同じページにグループ化します。 これにより、インデックス値に一致するすべての行を要求したとき、SQL はメモリのクラスタ化されたページからそれらの行を返すことができます。 これは、GUID列にクラスタ化インデックスを作成しようとするのが悪い考えである理由です。

整数カラムのインデックスを作成する場合、SQL のインデックスは各インデックス値に対する行のセットを含んでいます。 1 から 10 までの範囲がある場合、10 個のインデックス ポインターがあることになります。 行の数によって、ページングが異なります。 クエリが "1" に一致するインデックスを探し、Name が "Fred" を含む場所を探す場合（Name 列にインデックスがないと仮定）、SQL は "1" に一致する行のセットを非常に迅速に取得し、その後テーブルスキャンで残りを探します。

つまり、SQL が実際に行っていることは、反復処理しなければならない作業セット (行の数) を減らそうとしているのです。

ビットフィールド（または狭い範囲）にインデックスを付けると、その値に一致する行の数だけ作業セットを減らすことができます。 一致する行の数が少ない場合は、作業セットを大幅に削減することになります。 50/50の配分で多数の行がある場合、インデックスを最新に保つことと比較して、ほとんど性能向上は得られないかもしれません。

SQL には非常に巧妙で複雑なオプティマイザーがあり、テーブルスキャンの方が速いと判断した場合にはインデックスを無視したり、ソートを使用したり、好きなようにメモリページを整理したりすることができるからです。