![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
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つのフィールドに1つずつ張ればよい
2.検索に使用される10つのフィールドとソートに使用される3つのフィールドに1つずつ張ればよい
3.where句の組み合わせを全て考えて複合インデックスを張る必要がある。
4.その他
また、80フィールドのテーブルを適当に4つくらいに分けて、検索時に結合すれば早くなったりするものでしょうか?
A 回答 (3件)
- 最新から表示
- 回答順に表示
No.3
- 回答日時:
>10!=3628800というのはどういう意味でしょうか?
10個のフィールドから、任意の順番で任意の個数の重複しないフィールドを採取するとします。(採取したフィールドは、この順番で検索条件に&結合で使用されるとすると、あなたが提案した3番のインデックスを作成する際に必要なインデックス数となります。)
任意の個数だから、最初のフィールドの数は、F1~F10の10通り。
次のフィールドは、一個目に採取したフィールドを除いた9通り。
以下、最後まで続く。
で、場合の数は、10*9*8*・・・*2*1=10!
というのが、No2さんの回答の趣旨なんですが、実は、「任意の個数の」という条件と、or条件の組み合わせの場合がありますので、これよりも、場合の数は増えます。
ところで、インデックスを張ると、検索は早くなる「可能性があります」が、挿入・修正・削除の速度は、おそく「なります」
このトレードオフを考えると、7桁以上なんて個数のインデックスを張るのは、確実にシステムは遅くなります。現実性もありませんしね。
何故、データベースが自分で勝手にインデックスを張ってくれないかを考えましょう。使われ方によって、効率の良い張り方が違う上に、データ内容によってもその妥当性が変わるからです。
この場合は、おそらく、一番最初にする仕事は、ある期間に渡ってどんな条件で検索が行われたかの統計表を作ることだと思います。
その上で、上位の物に関してだけ結合インデックスを張るのが妥当かと思います。
最初は、上位から少しずつインデックスを作成していき、システム全体のレスポンスを見守ってください。(特に、挿入・修正・削除の効率は確実に低下しますから、その辺の監視も忘れないように。)その上で、さらにインデックスを春かどうかの決断をした方がよろしいかと思います。
ただ、楽観的に見ると、人間という物は偏りのある物ですから、統計表を見れば、「何だ。結局、数個作れば、全体の5割カバーできるのか」というつぶやきになりそうな気もします。
ご回答ありがとうございます。
とりあえずは、必ず使用される検索条件をキーとしたindexを張ってみようと思います。
それにより、ほとんどの場合は10000件以下に絞られるようです。
No.2
- 回答日時:
10!=3628800で、このパターンのindexを貼るのは不可能でしょう。
ユーザの検索方法には偏りが必ずありますので、
その上位3つのパターンのみ暫定策としてindexを貼るのはどうでしょうか。
妙案が出れば別ですが、全カバーするのは通常無理だと思いますので、ユーザーに対して検索パターン上位三つのもののみ対策を取ります、とアナウンスするのはいかがでしょうか。
この回答への補足
10!=3628800というのはどういう意味でしょうか?
ユーザーの検索方法は自由なので基本的にはすべてカバーしてあげたいと思っているのですが。。。
No.1
- 回答日時:
>実際に検索に使用されるフィールド数は最大で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/optimizat …
この回答への補足
ご回答ありがとうございます。
・AND条件だけではなくOR条件もあります。
・検索数は、条件次第ですが、検索条件を緩めると、最大で1万件くらいにはなると思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(データベース) 更新クエリをリンクデータベーステーブルに実行し実行時エラー3362固有インデックスに重複する値が含ま 1 2022/09/21 11:44
- Access(アクセス) AccessVBAで降順にするテーブル作成クエリを使用して作成したテーブルを削除し同一のテーブル作成 1 2023/01/06 11:17
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
- Access(アクセス) Accessフォーム 一部のレコードだけを抽出する方法について 1 2022/06/28 18:45
- JavaScript javaScriptで画面に値を入れるには 1 2022/08/14 12:27
- Access(アクセス) アクセスの更新クエリでカレントレコードのみ更新したい 1 2022/06/02 23:32
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Visual Basic(VBA) VBAチェックボックスで有効無効切り替えできるように 5 2022/10/21 16:13
- Excel(エクセル) PHPプログラムをエクセルに張り付けると検索ボックスがでてくる! 3 2022/05/08 07:10
- MySQL 書籍の内容はまともでしょうか? 1 2023/01/22 03:07
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
SQLローダーCSV取込で、囲み文...
-
テーブルの最後(最新)のレコー...
-
配列に指定した値が含まれてい...
-
テーブルのフィールドの一番長...
-
BLOBやCLOBのパフォーマンスを...
-
2回実行のSQL文を1回にしたい
-
【MySQL】 DECIMAL(2,1) に 13...
-
ACCESSのクエリで空白以降を別...
-
SELECTした合計値をそのまま割...
-
select文の実行結果に空白行を...
-
group byの並び順を変えるだけ...
-
Oracleで「文字が無効です」の...
-
割合(パーセント)を求めるには?
-
複数のテーブルから値を合計出...
-
SQL*Loader Append
-
アクセスのレポートでレコード...
-
並べ替えについて
-
【PL/SQL】FROM区に変数を使う方法
-
PL/SQL内の共通関数の引数にフ...
-
月末日のみソートしたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SQLローダーCSV取込で、囲み文...
-
テーブルの最後(最新)のレコー...
-
int型フィールドにnullを登録で...
-
テーブルのフィールドの一番長...
-
2回実行のSQL文を1回にしたい
-
SELECTした合計値をそのまま割...
-
配列に指定した値が含まれてい...
-
固定長データのテキストファイ...
-
BLOBやCLOBのパフォーマンスを...
-
入力データの半角スペースと全...
-
ACCESSのクエリで空白以降を別...
-
Null値件数をカウントする式に...
-
ファイルメーカーのフィールド...
-
MySQLでの近似値順での値の取得...
-
【MySQL】 DECIMAL(2,1) に 13...
-
ファイルメーカー10で重複デー...
-
CSVからNULL値をインポート
-
NULLの挿入の仕方が…?
-
一つのフィールドに複数の情報...
-
重複しないデータのみを抽出
おすすめ情報