Q&A
解答ありがとうございます！
実例をあげていただいて、大変ためになりました。
タイトルの通りですが、検索をする時の効果的なインデックスの貼り方がイマイチ分かっておりません。
例えば
users (
id int
type int
prefecture int
name varchar
)
みたいなテーブル構成のユーザーを検索する時に、全てのカラムで検索項目入力欄がある場合に
typeが入力されていたらtypeのwhere文を追加、prefectureが入力されていたらprefectureのwhere文を追加
という感じで実装しています。
この場合、type,prefecture,nameにそれぞれインデックスを貼るのが正解なのでしょうか？(idはプライマリーキーなので除外しています)
このぐらいのカラム数のテーブルならそれでも良いのかな・・？と思うのですが、カラム数が多いテーブルの場合にも検索対象のカラムは全部インデックスをつけるのが正しいのかよく分かっておりません。
よろしければどなたかご教授頂けたらと思います。
宜しくお願いいたします。
回答4件
0
users テーブルのselect, delete, updateで使われている列がどうなっているか確認します。
SQL
1select ... 2from users 3where type = ... 4and prefecture = ... 5and name = ...
であれば、type, prefecture, name で1つの索引を作ります。
索引の数が増えると索引の更新の負荷が増えるので1つのテーブルに索引は4つまでとかルールを決めているユーザーも多いです。ほとんど更新されないテーブルでいろいろな条件でアクセスされる場合は索引がいくつも作られることもあります。
実行計画を確認 して索引が有効に使われているかチェックしましょう。
投稿2018/02/15 01:32
総合スコア16415
0
前提ですが、webでの検索を想定されていると思っています。
画面へのレスポンスが3秒程度の制約という認識の上での話です。
インデックスの要否を判断するには
カラム数ではなく、レコード数を確認します。
数千件程度ならインデックスは不要。
数十万件以上になってくると必須。
数万件の場合は、とりあえずインデックスは付けないで非機能テストで確認します。
次に対象のカラムを検討するにあたって
データのバリエーションを確認します。
インデックスは、
データの値が散らばっているほど効果があります。
また一般的にデータの3割以下を検索する際に、有効に機能すると言われています。
性別みたいなデータのバリエーションが2つでどちらも50％ほどのカラムには付けません。
投稿2018/02/15 01:51
総合スコア1400
0
項目を任意に条件にする場合のインデックスの検討は、その項目の組み合わせを考えます。
質問されている内容では、3C3+3C2+3C1=7で、内訳は以下になります。
1.(type,prefecture,name)
2.(type,prefecture)
3.(type,name)
4.(type)
5.(prefecture,name)
6.(prefecture)
7.(name)
この組み合わせの最適なインデックスは、
A.(1,2,3,4)：(type,prefecture,name)
B.(5,6) ：(prefecture,name)
C.(7) ：(name)
となります。
※項目をどう並べるかについては、カーディナリティが低いものから並べた方が効率は良いと思われます。
但し、条件以外にもorder by項目等があるなら話は別になります。
想定される件数のデータを準備した上で、実行計画により確認して下さい。
尚、他の方が言われているように更新頻度とのバランスも必要です。
投稿2018/02/15 03:39
編集2018/02/15 03:46
総合スコア25195
解答ありがとうございます！
最適なインデックスの例まであげていただいて助かります。
合わせて質問なのですが、
＞カーディナリティが低いものから並べた方が効率は良いと思われます。
とありますが、szk.さんが解答している
＞性別みたいなデータのバリエーションが2つでどちらも50％ほどのカラムには付けません。
というのは、単体でインデックスを貼る場合の話で、複合インデックスの場合はsaziさんがおっしゃってる通り、カーディナルが低いものから並べたほうが効率が良いということなのでしょうか？
複合インデックスは分類表などをイメージしてもらえればいいかと思います。
分類表は大分類＞中分類＞小分類としますよね。
これが、中分類＞大分類＞小分類などと記述されていると、探すのは非効率です。
なるほど、凄く分かりやすい例えですね！
ありがとうございます！
0
どれが正解というのはないと思います。仕様や要求により、ですね。
あくまで検索処理の向上を目的としたものなので、データ量によっては1つだけでも充分な効果が得られることもあるかと思います。
（貼りすぎは良くないとか昔上司に教えられたのですが理由まで思い出せず・・・）
適切に貼るというのはなかなか簡単ではないですね＾＾；
下記も参考にしてみてください。
投稿2018/02/15 01:31
総合スコア80850
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/02/15 01:34
2018/02/16 03:30
2018/02/23 11:45