はじめに
インデックスを作成する際のアンチパターンを調査してまとめました。
インデックス？なにそれ美味しいの？から勉強を始めている方向けの記事です。
参考: 達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ
インデックス作成のアンチパターン
早速始めていきます↓
1. SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成していない
当然ではありますが、使用されている列にインデックスは作る必要があります。
WHEREやJOINで使用されるインデックスは特定のカラムから該当するレコードを探すために使用されるため、WHEREやJOINでしてされるカラムはインデックス候補となります。
2. カーディナリティが低い
まずカーディナリティとは、
数学で基数あるいは濃度という意味の用語。 ITの分野では、リレーショナルデータベースにおいてあるテーブルの同一の列（カラム）に含まれる異なる値の数（バリエーション）のことを指します
⇒ データのバラツキ度合いの指標。
インデックス作成においては、
🙆♂️カーディナリティが高い
→対象カラムに登録されているデータの種類が多い状態
🙅♂️カーディナリティが低い
→対象カラムに登録されているデータの種類が少ない状態
例えば、性別のカーディナリティは2になります。（少し時代に則していませんが…）
都道府県数であれば47なのでカーディナリティは47になります。比較すると、カーディナリティが高いのは都道府県数ということになります。
もし、性別のようにカーディナリティが小さいと、一つの値に多くのレコードが紐づくことになり、検索は早くなりません。データの種類としてはカラムに対して、20以上=全体の5%程度に絞り込まれるのが良いとされています。
2. データに偏りがある
カーディナリティが大きくてもレコード数に偏りがある場合は、インデックスが有効でない場合があるため注意が必要です。例えば、1~20のデータをとる列があったときに、カーディナリティは20となります。1.の話より、この列に対してインデックスを作成すると効果がありそうです。
しかし、20が99%を占めていて、1~19のデータが1%だけだったデータの時はどうでしょうか。この場合、20が検索条件にあった時はほぼ全てのデータをスキャンしていることと変わりません。この場合のインデックスは安定しません。
3. レコード件数が少ないテーブルに作成する
レコード件数が少ない時は、インデックスを用いた検索よりフルスキャンの検索の方が早い場合があります。一つの目安として、環境によって異なるので一つの目安程度ですが、1万件を超えるテーブルには効果があると言われます。
4. インデックス列に演算を行なっている
例えばcolにインデックスが作成されているとき、下記はインデックスの効果がありません。
B-treeインデックスの中のデータはcolであって、 col * 10ではないためです。
SELECT * FROM sample WHERE col * 10 > 100;
5. NULLに対してインデックスを貼っている
B-treeインデックスはNULLはデータの値とは見なしません。つまりIS NULLまたはIS NOT NULLに対しては効果がありません。※DBMSの種類によっては、NULLも使えることはあるようですが、汎用性はありません。
6. ORを用いる
ORではインデックスの効果がありません。この場合、INで書き換えると回避できます。
SELECT * FROM sample WHERE col = 100 OR col = 101;
7. 否定系を用いる
もし使えたとしても検索範囲が広くて効果がありません。
SELECT * FROM sample WHERE col <> 100;
8. 前方一致以外のLIKE術語を用いている
🙆♂️ 下記はOK！
中間一致や後方一致ではB-treeインデックスの構造上、インデックスを使った検索をすることができません。
SELECT * FROM sample WEHRE col LIKE ‘太郎%’; (前方一致)
🙅♂️ 下記はNG！
SELECT * FROM sample WEHRE col LIKE ‘%太郎’; (中間一致)
SELECT * FROM sample WEHRE col LIKE ‘%太郎%’; (後方一致)
9. 暗黙の型変換を行なっている
データ型が異なる値を選択条件や結合条件として利用する場合、型変換を行う必要があります。もし、データ型が異なる条件に指定した場合でも、DBMSは内部的に暗黙の型変換を行われます。
しかし、この場合インデックスは使用されなくなります。これを回避するために、明示的に条件に使用する値のデータ型を列のデータ型に合わせる必要があります。
例として、colカラムが文字列で定義されているとします。
🙆♂️ 下記はOK！
SELECT * FROM sample WEHRE col = '10';
🙅♂️ 下記はNG！
SELECT * FROM sample WEHRE col = 10;
10. WEHREの条件で複数カラムが指定される場合に単一のインデックスを作成している
WEHREの条件で複数カラムが指定される場合などは単一のインデックスは使用されないことがあります。複数のカラムに対して一つのインデックスを貼る場合は、複合インデックスを貼る必要があります。
11. メンテナンス（再作成）を行わない
インデックスはテーブルのデータが更新されていくと、長期的には構造が崩れていきます。長期運用するシステムでは定期的にインデックスを再構築する必要があります。
おわりに
もし他にも注意した方が良い点がありましたらご教示いただけると幸いです。
次の記事では、下記の実演を行いたいと思います。
- カーディナリティーが高いもの列に(5%)に貼った時と低いもの(50%)にインデックスを作成したときの速度比較
- カーディナリティーは高いがデータに偏りがある列にインデックスを作成したときの速度