検索条件が複数の場合のインデックスの張り方

質問

Mysql5.0 + ASP.NETで開発中です。
サーバーはWindows2003サーバーです。

とある検索サイトを作っていますが、1テーブルのフィールド数が80くらいあります。
また、レコード数は常時100万件程度です。

このテーブルの検索を行うときに、ユーザーが任意の検索条件を設定できるような画面なのですが、実際に検索に使用されるフィールド数は最大で10です。
例えば、where a = 999 and b = 999 や where a = 999 and c = 999 and f = 999　や where b = 999 and d = 999 and f = 999 and g = 999
など、where句で使用されるフィールドがユーザーの指定により常に異なります。(999は任意の値です)
ORDER BYに使用されるフィールド数は3です。

現状ではインデックスは張っていないため、かなり検索速度が遅いため、インデックスを張りたいのですが、どのような張り方がいいのかがわかりません。

このような場合、インデックスを張る方法として、どの方法が一番よいのでしょうか？

1.検索に使用される10つのフィールドに１つずつ張ればよい
2.検索に使用される10つのフィールドとソートに使用される3つのフィールドに１つずつ張ればよい
3.where句の組み合わせを全て考えて複合インデックスを張る必要がある。
4.その他

また、80フィールドのテーブルを適当に4つくらいに分けて、検索時に結合すれば早くなったりするものでしょうか？

回答No.3

mitoneko

＞10!=3628800というのはどういう意味でしょうか？
　１０個のフィールドから、任意の順番で任意の個数の重複しないフィールドを採取するとします。（採取したフィールドは、この順番で検索条件に＆結合で使用されるとすると、あなたが提案した３番のインデックスを作成する際に必要なインデックス数となります。）
　任意の個数だから、最初のフィールドの数は、F1～F10の１０通り。
　次のフィールドは、一個目に採取したフィールドを除いた９通り。
　以下、最後まで続く。

　で、場合の数は、10*9*8*・・・*2*1=10!
　というのが、No2さんの回答の趣旨なんですが、実は、「任意の個数の」という条件と、or条件の組み合わせの場合がありますので、これよりも、場合の数は増えます。

　ところで、インデックスを張ると、検索は早くなる「可能性があります」が、挿入・修正・削除の速度は、おそく「なります」
　このトレードオフを考えると、７桁以上なんて個数のインデックスを張るのは、確実にシステムは遅くなります。現実性もありませんしね。

　何故、データベースが自分で勝手にインデックスを張ってくれないかを考えましょう。使われ方によって、効率の良い張り方が違う上に、データ内容によってもその妥当性が変わるからです。

　この場合は、おそらく、一番最初にする仕事は、ある期間に渡ってどんな条件で検索が行われたかの統計表を作ることだと思います。
　その上で、上位の物に関してだけ結合インデックスを張るのが妥当かと思います。
　最初は、上位から少しずつインデックスを作成していき、システム全体のレスポンスを見守ってください。（特に、挿入・修正・削除の効率は確実に低下しますから、その辺の監視も忘れないように。）その上で、さらにインデックスを春かどうかの決断をした方がよろしいかと思います。
　ただ、楽観的に見ると、人間という物は偏りのある物ですから、統計表を見れば、「何だ。結局、数個作れば、全体の５割カバーできるのか」というつぶやきになりそうな気もします。

お礼

ご回答ありがとうございます。
とりあえずは、必ず使用される検索条件をキーとしたindexを張ってみようと思います。
それにより、ほとんどの場合は10000件以下に絞られるようです。

回答No.2

javawater

10!=3628800で、このパターンのindexを貼るのは不可能でしょう。

ユーザの検索方法には偏りが必ずありますので、
その上位３つのパターンのみ暫定策としてindexを貼るのはどうでしょうか。
妙案が出れば別ですが、全カバーするのは通常無理だと思いますので、ユーザーに対して検索パターン上位三つのもののみ対策を取ります、とアナウンスするのはいかがでしょうか。

補足

10!=3628800というのはどういう意味でしょうか？
ユーザーの検索方法は自由なので基本的にはすべてカバーしてあげたいと思っているのですが。。。

回答No.1

chukenkenkou

>実際に検索に使用されるフィールド数は最大で10です。

検索条件は、「=」条件と限定できるのですか？また、ANDのみでORはないのですか？

>ORDER BYに使用されるフィールド数は3です。

母体が約100万件とのことですが、その内、1回の検索で、最大で何件くらい検索されることになるのでしょうか？
インデクスは、データの絞込みだけでなく、ソート抑止による性能向上にも有効です。ソート抑止するには、ORDER BYの列の指定とインデクスの先頭からの構成列が一致している必要があります。

>このような場合、インデックスを張る方法として、どの方法が一番よいのでしょうか？

単一の列に各々インデクスを定義する場合、各列の条件でそれぞれ絞り込み効果が高くないと、性能が改善するどころかオーバヘッド要因になる場合があります。
複数列（マルチカラム）インデクスを定義する方が、恐らく効果が高いと思います。

すべての組み合わせに定義した方がいいかどうかは、今の情報からだけでは判断できません。重複データ数などにより、結果的に特定のケースの場合は、複数列インデクスである程度絞込み、あとは表データを見る方が早い場合もあるでしょう。
また、インデクスを何本も付ける場合は、追加、更新、削除でオーバヘッド要因になることも無視できません。

>また、80フィールドのテーブルを適当に4つくらいに分けて、検索時に結合すれば早くなったりするものでしょうか？

今の情報だけでは、判断できません。

以下のURLは、MySQL 5.1のマニュアルの最適化の記述箇所です。
MySQL 5.0は英文マニュアルしかないと思うので、MySQL 5.1で違う部分もあるかも知れませんが、基本的な考え方は大きくは変わらないと思います。

http://dev.mysql.com/doc/refman/5.1/ja/optimization.html

補足

ご回答ありがとうございます。

・AND条件だけではなくOR条件もあります。
・検索数は、条件次第ですが、検索条件を緩めると、最大で1万件くらいにはなると思います。