More than 3 years have passed since last update.

<SQL>テーブルカラムへの効果的なインデックス(索引)の張り方

Posted at 2021-02-18

#テーブルのカラムにインデックスを張るときはどんな時？

こんな疑問がふつふつと湧いてきました。
インデックスの張り方、貼った時の挙動はある程度は理解できたけど、
どんな時に索引を張るべきか
があやふやなので、これもまとめたいと思います。

（基本的に、インデックスはB-TREEを指して話してます。）

#テーブルのレコード数が多く、かつ、少量のレコードを検索したいケース
検索結果のレコード数が、推定、全体の2~15%程度のケースで、インデックスの効果が最も発揮できるそう。

ちなみに、

では、インデックスは使用しないほうがいい。

#カーディナリティが高いカラム（カラムが保有する値の種類が多い）

一般的なB-TREEインデックス（バイナリーツリーindex:二分木インデックス）のケースでは、
カラムの値が一意に近づけば近づくほど、効果が得られるようになる。

つまり、列の値が比較的ユニーク（一意）なケースで、インデックスを張ると良い。

###カーディナリティが低い場合は、ビットマップインデックスが有効

実際のテーブルのレコードこんな感じだった時に、

ビットマップインデックスはこんな感じのイメージで

aに1というフラグが立っている部分（インデックス）と
テーブルそのものを照らし合わせて
加藤さんと大和さんをヒットさせるというやり方

※ちなみに、ANDやORだけで行う検索や、NOTを用いた否定検索にも有効らしい

#WHERE句の条件、またはJOIN句(結合)の条件としてよく使用するカラム

例えば、
ECサイト等で、注文番号みたいな値を頻繁に条件として指定する場合や

SELECT * FROM orders WHERE order_no = 1356;

よくユーザー情報を用いて正規化されたテーブル同士を結合JOINしてデータを引っ張ってくる場合とか

SELECT * FROM users LEFT JOIN user_orders ON users.id = user_orders.user_id
WHERE users.id = 3;

こんな利用ケースがある時とかは、効果を発揮するみたい。

逆に、WHERE句の条件としてあまり使用されないものについては、
インデックスを張るあまり意味ないっぽい。

#外部キー制約になってるカラム
外部キーを設定すると、
間違ったデータの登録や削除を防いだり、
関連するデータを一括に変更できたりして、
関連のあるテーブル間で整合性を担保できるようにする役目がありますが、

例えば、
usersテーブルでレコードを消そうとした時に、user_ordersテーブルにそのuser.idがあった場合に、データが消さないなど。

その外部キー設定カラムにインデックスを張ると効果があるそうだ。

#カラムの値にNULLが多い時に、NULL値以外のレコードを検索したいケース
インデックスにはNULLが含まれないので、NULL値以外の検索には効果を発揮する。
つまりNULL検索では、B-TREEインデックスが使用できないので、ビットマップインデックスだと効果を発揮するそう。

#あまり値が更新されないカラム
インデックスは、テーブルとは独立した存在として、インデックスデータ単体でも保存されている。
なので、テーブルにinsert update deleteしたら、テーブルだけじゃなくて、
インデックスのほうのデータ更新されるようになる。

つまり、更新が頻繁な物に対してインデックスを張ると、重くなってしまうので、気をつけて設計するといい。

#まとめ
B-TREEインデックスだけでなく、ビットマップインデックスについても少し勉強なりました。

以上、ありがとうございました。