INDEXの貼り方 - kamyu's chronicle

INDEXの貼り方

DBのTABLEにINDEXを貼るとき…
どこを見ても「場合によって違います」としか書いていないので、ちょっと簡単にまとめてみる。

よく思うんだけど、「場合によって違います」ってのは正しいのは正しいけど、解答として意味が無いんだよね。最低値と平均値と最高値くらい答えられるだろ、って思う。

２時間くらいネットサーフィンしただけの知識なので、間違ってたら指摘してください。

---

①よくSELECT文を投げるテーブルに対してINDEXを貼る

当然だけど、INDEXは検索の時にするもの。
なので、SELECTされるテーブルに対してINDEXをはる。
ログ的なものなど、INSERT/UPDATEの処理が多いものだと、INDEXにもINSERT/UPDATEする必要があるので逆にパフォーマンスが落ちる。

SELECTの頻度とINSERTの頻度を比べて、貼らないものは貼らないべき。
貼るとしても、たくさんは貼らない。

なお、実行計画の順位付けから、単純に比べたら、INDEXを貼らないほうが早い！
ってケースもあるにはあるけど、そんなときはSQL側の修正をすれば、
やっぱりINDEXを貼ったほうが早い。
だから、INDEXはSELECTを使うときには貼るべき。

②よく検索に使われるレコードに対してINDEXを貼る。

例えば、

(例1)
SELECT a.id,a.name FROM test_table a
WHERE a.kubun = :kubun AND a.status = :status AND a.name like '%test%'
ORDER BY a.seq_no

がよく使われる検索だったら、

1. kubun > status

という結合INDEXを貼る。

③ORDER BYするレコードに対してINDEXを貼る。

例1だと、

• seq_no

の単独INDEXを貼る。

ORDER BY a.seq_no,a.priority

とかだったら、

• seq_no > priority

の結合INDEXを貼る。
ただし、ORDER BYする列はNOT NULLでなければならない。
なお、DISTINCTはできるだけ指定しないこと。

④GROUP BYするレコードに対してINDEXを貼る。

SELECT place_id,sum(val) FROM tsumiki_sales GROUP BY place_id

上記なら、

• place_id > val

の結合INDEXを貼る。
なお、できるだけHAVINGは使わず、WHERE句を使う。

⑤COUNT,MAX,MINを使うカラムに対してINDEXを貼る。

MAXとかMINは結局ソートしてから値を出すので、INDEXを貼っておくと早くなる。
COUNTも、COUNT(列名)の指定ならINDEXを使うと早くなる。

⑥できるだけ、種類が多いカラムから優先的にINDEXを貼る。

INDEXは「コレでコレだったら何行目！」ってのを返すわけなので…
例えば、0と1しかないところにINDEXを貼っても意味があんまり無い。
なので、PRIMARY_KEYになっているものや、複合PRIMARY_KEYになっているものだと
INDEXの効果が最大限に発揮できる。

…と言っても、検索に使っていないカラムにINDEX貼っても無駄だけどね。

⑦INDEXを貼っても意味が無いのは…

• LIKE検索しかしていないカラム（前文一致ならOK）
• NOT検索しかしていないカラム（INとかに変えてINDEXを貼ろう）
• IS NULL検索しかしていないカラム
• カラムを演算している場合（カラムじゃないほうを演算しましょう）
• SELECT文に全く使っていないカラム

⑧単独INDEXがいいの？結合INDEXがいいの？

単独INDEXだけだと、例えば例１で

1. kubun
2. status

とINDEXを貼ると、「インデックスマージ」が行われる。
余計な手順であり、時間の無駄なので、
同時に使われる場合は、できるだけ結合INDEXにすべき。

ちなみに、kubun>statusでINDEXを貼っている場合、
kubunだけのINDEXも貼られていることになる。

⑨ひとつのテーブルにどれくらいINDEX貼っていいの？

SQL文自体は参照しているだけであっても、
データのロードやインポートなどの準備過程が必ず発生するため…
6～7個以内を目安とするのが普通みたいです。

ちなみに、更新が頻繁に発生するテーブルなら、2～3個以内くらい。