インデックスのカーディナリティについて

Question

MySQLのインデックスについて質問です。

カーディナリティの低いものにインデックスをつけるべきではない、と考えていたのですが
カーディナリティが低くても、対象テーブルのレコードの分布に偏りがある場合はインデックスを貼ると効果がでるのでしょうか。

例えば、商品の在庫表などで
販売ステータスという列があり、ステータスは「未販売」「販売済み」という種類があったとします。

普通に考えれば、このステータスカラムにインデックスを付けることはカーディナリティの低いと思いますが
販売済みステータスは増え続けるはずですので、時を経るごとに未販売ステータスのレコードが極端に少なくなります。

この場合、インデックスを貼ることで高速化が図れるのでしょうか。

ご回答よろしくお願いします。

Answer 1

データ件数が膨大で、一方に絞り込んだデータ郡に対する検索の頻度が利用方法の大多数を占めるなら効果はあると思います。

SELECT * 
FROM item 
WHERE st = 2 /* 未販売 */
AND price > 1000

このようなSQL発行が多く、未販売が100件程度で販売済みが数百万件のような場合、st（未販売or販売済み）にindexがあればSQLの実行自体は高速化されるでしょう。
「未販売」と「販売済み」を選り分ける作業がindexによってほぼ無くなる程度まで軽減されるので、この作業のコストが高ければ高いほど(つまりレコード件数が多いほど)効果があります。
そして(上記の設定のように)その選り分ける作業こそがそのSQLのコストの大半だったなら、高速化率も高くなります。

しかしこのような場合は、まず販売済みの古いデータを定期的にバックアップに移して省くことを検討したほうが良いかもしれません。あまりにレコードが多いとデータ登録時のindex作成も負担になってくるためです。(データ件数が膨大ということは登録や更新も頻繁に行われるということですし）

追記
ところで上記のような例でpriceにもindexがあり、全体に対する「price > 1000」が「st = 2」よりも少ないと簡単に予測できる場合、オプティマイザは「price > 1000」を先に実行するプランを選択するかも知れません。そうすると「st = 2」のために極少数のレコードのソートで済みstへのindexにあるなしに関わらず実行速度が殆ど変わらなかった。ということもあるかも知れません。

---

以下の部分はコメントで指摘いただいた通り誤りで、極端に分布が偏った方を参照するのでない限りindexに意味は無いようです。

カーディナリティが低くても、対象テーブルのレコードの分布に偏りがある場合は

仮に偏りが無くても、また100:100万に対して100万の方を選択する絞込みだったとしても、ソートが必要になる時点でindexには意味が出てくるように思います。
そして絞込みや検索には大抵ソートが必要です。(たぶん)
カーディナリティが高い例えばユニークidのような要素だと、indexはその要素を条件にした結合などの操作にも威力を発揮します。しかしカーディナリティが低い要素では境界を発見するような操作にしか威力を発揮できないので、コストが同じな割りに用途が少なく効率が悪いということだと思います。
でも”その操作をこそメインに頻繁に行う”のであれば意味はあるんじゃないかな。。

Comments

[sanapapa]

最も詳しくご教示頂けたhirohiroさんをベストアンサーにさせていただきます。
大量のテストデータを投入して実行計画を得るなど、余裕のあるときにテストし吟味したいと思います。
みなさん、ありがとうございました。

[KiyoshiMotoki]

hirohiro様、sanapapa様

横から失礼します。

> データ件数が膨大で、一方に絞り込んだデータ郡に対する検索の頻度が利用方法の大多数を占めるなら効果はある

このようなケースでインデックスが有効なのは、

> 未販売が100件程度で販売済みが数百万件のような場合

かつ、「未販売」を検索する場合のみです。

多い方のデータを検索する場合、MySQLのオプティマイザはインデックスを使用せず、テーブルスキャンを選択する可能性が高いです。

http://nippondanji.blogspot.jp/2009/04/1.html

> 例えば、YesかNoの2つの値しかとらないカラムは非常にカーディナリティが低く、インデックスをつけるととても効率が悪い。
> インデックスを使って目的の行を見付けようとしても、インデックススキャンが起きるだけなので、オプティマイザはしばしばそのようなスキャンを回避して、テーブルスキャンを選択してしまう。
> （その方がインデックスと行の間の行き来がなくなるので、高速になるからだ。）
> ただし、どちらか一方の値を持つ行だけが圧倒的に少ないというような場合、少ない方の値を指定すればオプティマイザはインデックスを使用する。

[KiyoshiMotoki]

hirohiro様

お手数ですが、

> 偏りが無くても、また100:100万に対して100万の方を選択する絞込みだったとしても、ソートが必要になる時点でindexには意味が出てくるように思います。

について、どのような状況についての説明なのか、もう少し詳しく教えていただけますでしょうか？

私には
「"販売ステータス"列で絞り込んだ後、さらに"販売ステータス"列でソートする」
というように読めたのですが、そのようなシチュエーションは一般的ではないため、どうも誤読しているように思えましたので。

[hirohiro]

kiyoshiMotoki様　コメントありがとうございます。
該当部分の記述は私の書いている内容が誤りのようですね。
「分布が偏ってるかどうかを確認するのにindexが必要なんじゃ？」(<-これが引用された該当部分です。)と思って追記したのですが、確認した結果不利ならテーブルスキャンするからindexがあっても意味がないということですね。

回答のほう修正しておきます。
sanapapa様、うそ書いてたみたいですみません。

[hirohiro]

indexが無い場合、where句での絞り込みにおいて常にカラムのソートは行わずにテーブルのフルスキャンをするのでしょうか？(これはwhere f=1という条件がある場合に、fで(全行)ソートしてf=1の塊を得るのではなく、全ての行についてf=1を確認して該当するものを得るという意味で)。
どうもソートするものだと理解していた(likeが遅いのはソートのプロセスを踏めないため？と思ってた)ので、どうせ全行ソートするならindexがあったほうが軽いよねといった解釈で指摘いただいたような記述になったようです。

しかしこれは改めて考えてみるとindexがあるからその結果を利用しているのであって、indexが無いのにわざわざソートを挟むというのはおかしな話ですね。

[KiyoshiMotoki]

hirohiro様

返信ありがとうございます。

> 回答のほう修正しておきます。

確認させていただきました。

> indexが無い場合、where句での絞り込みにおいて常にカラムのソートは行わずにテーブルのフルスキャンをするのでしょうか？

実際のところは私にも分かりませんが、
「いったんソートしてから対象の行を抽出する」
ということは無いとおもいます。

なぜなら、
　ソートする = 各行の値を検査する
ということなので、検査した結果、条件に合致する値であればその時点でフェッチしてしまえば良く、
わざわざ「いったんソートする」必要は無いからです。

Answer 2

カーディナリティが高かろうが低かろうが、外部キー制約をかける場合にはインデックスは必須となって、選択の余地はありません。

とりわけ、今回のような業務フラグ列の場合（本当にBOOLEANなどきっちりの型で入れる場合はともかく）、異常値の混入を防ぐために外部キー制約をかけておいたほうがいいのではないかと思いました。

Answer 3

ガーディナリティが低い項目が単独でインデックスを作成するのは、確かに効率は良くないですが、他の項目と併せて検索の条件がインデックスに収まる事によってキーのみ検索使えて早くなる事は有ります、インデックスに入れたりして速度を計っています。

Answer 4

今回のケースではインデックスは不得手なのではないかな思います。

更新を多く伴うテーブルのカラム(ステータスなので更新が多そうな気がする)にインデックスを張ると、
インデックス自体の更新も発生するため一般的には逆にパフォーマンスに悪影響が出る恐れも高いです。

takasima20さんのおっしゃる通り、
レスポンスが場合は先ずはボトルネックとなっている箇所を特定するのが重要です。

クエリに問題がありそうということでしたら、一度実行計画を見てみてはいかがでしょう？

###追記
分布に偏りがある方がインデックスの効果は高いですね。
素でボケをかましてました;

Answer 5

あんまり効果ないんじゃないかなあ。
もし、そういう需要があったとしてもインデックスで解決ってのはちょっと。
性能に不満があるということなら、まずは処理時間(所要時間)の分析から
はじめるのがふつうなんじゃ? 対策とるにも説得材料いりますよね。

Comments

[sanapapa]

一応、分布に偏りがある場合、インデックスが有効との記事も見まして。
今設計途中のテーブルでして、調査はできないんです。
何百万レコードの中に数百件の未販売ステータスを探す場合、確かに有効なインデックスにも考えられるのですが。
何人かで議論になっていて、何が正しいのか気になっているところでして、質問させていただいております。

[takasima20]

失礼。他の方の回答をみると効果はあるようです。
ただ、トータルでみてどれだけのメリットがあるかは
よく検討する必要があるでしょうね。
自分の印象では、テーブル構成を見直す方が(この場合)
いいんじゃないかなって気がしました。