インデックスを張るべき項目について

質問

20万件レコードのあるテーブルに、インデックスを張ると
INSERTが遅くなるので、WHERE句で検索する項目のどれに
インデックスを張るか悩んでいます。

インデックスはパターンが多い程、張った場合に
検索速度が向上すると理解しているのですが正しいでしょうか？
であれば、下記１．だけは貼ろうと思っているのですが・・

１．カラムに入るデータが殆どバラバラのVARCHAR(30)
２．カラムに入るデータは10万パターンのINT型
３．カラムに入るデータは1万パターンのINT型
４．カラムに入るデータはdatetime型

インデックスを張る事でINSERT速度が何％ぐらい下がるでしょうか？

よろしくお願いします。

回答No.1

chukenkenkou · ベストアンサー

>インデックスはパターンが多い程、張った場合に検索速度が向上する
>と理解しているのですが正しいでしょうか？

よく例に挙がるのが、性別ですね。男、女の2種類しかデータがないので、特にB-TREE構造のインデクスの場合は、性能向上は期待できません。
インデクスを定義しても、インデクスを有効利用できる検索条件でないと、意味がありません。
インデクスを有効利用できる条件は、「=」条件、範囲条件、LIKEの前方一致（列名 LIKE 'ABC%'）等です。
MAXやMIN、COUNTなどの集計（集合）関数でも、インデクスがあれば活用できます。
また、group by、order by、distinctでも、インデクスにより、ソート抑止することが可能です。

>であれば、下記１．だけは貼ろうと思っているのですが・・
>１．カラムに入るデータが殆どバラバラのVARCHAR(30)
>２．カラムに入るデータは10万パターンのINT型
>３．カラムに入るデータは1万パターンのINT型
>４．カラムに入るデータはdatetime型

これらの列に、上述のような検索条件を指定したり、order by指定したりするのでしょうか？
もしそうなら、この4列それぞれにインデクスを付けた方がいいのでは？３でも、平均の重複数は20件なのですよね？
また、必ず「列名1=値1 and 列名2 between 値2 and 値3」のように、複数の列の組み合わせで検索条件を指定するなら、列1や列2の単一列でなく、（列1、列2）で構成する複数列インデクスの定義をした方がいいでしょう。

>インデックスを張る事でINSERT速度が何％ぐらい下がるでしょうか？

インデクスを付けることによりオーバヘッドが発生するのは、insertだけでなく、delete、update（インデクスの構成列更新時）もです。
何％といった定量的な値は、出せません。
簡易的な考え方としては、（データ部＋インデクス部×インデクス本数）のI/O要求が出ると考えればいいでしょう。インデクスが4本なら、データの更新を加えて、5回のI/O要求が出ると考えてください。
しかし、厳密には、B-TREEインデクスのインデクス分割が発生すると、+1～2回程度のI/O要求が増えます。
また、データベースのI/Oバッファ上に更新対象のデータやインデクスのページがあると、I/O要求回数に比べ、実I/O回数はもっと少なくなります。

お礼

お礼が遅くなってすみません。

複合インデックスやディスクI/O。大変勉強になりました。
ありがとうございます。平均の重複数ですが、データの型にもよりそうですが、どのくらい重複している場合インデックスを張るべきなのでしょうか？　全部バラバラの場合、インデックスを張らない方が良いのでしょうか？

よろしくお願いします。