質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.90%

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

解決済

回答 5

投稿

  • 評価
  • クリップ 0
  • VIEW 5,328

score 30

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

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

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

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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 5

checkベストアンサー

+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はその要素を条件にした結合などの操作にも威力を発揮します。しかしカーディナリティが低い要素では境界を発見するような操作にしか威力を発揮できないので、コストが同じな割りに用途が少なく効率が悪いということだと思います。
でも”その操作をこそメインに頻繁に行う”のであれば意味はあるんじゃないかな。。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/05 18:25

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

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

    キャンセル

  • 2016/08/05 18:41 編集

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

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

    キャンセル

  • 2016/08/05 19:07

    hirohiro様

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

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

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

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

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

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/04 20:24

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

    キャンセル

  • 2016/08/04 20:47

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

    キャンセル

0

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

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

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

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

追記

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.90%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る