More than 1 year has passed since last update.

【SQL】インデックス作成時のアンチパターン

Last updated at 2022-09-04Posted at 2022-09-04

はじめに

インデックスを作成する際のアンチパターンを調査してまとめました。
インデックス？なにそれ美味しいの？から勉強を始めている方向けの記事です。

早速始めていきます↓

当然ではありますが、使用されている列にインデックスは作る必要があります。
WHEREやJOINで使用されるインデックスは特定のカラムから該当するレコードを探すために使用されるため、WHEREやJOINでしてされるカラムはインデックス候補となります。

まずカーディナリティとは、

数学で基数あるいは濃度という意味の用語。 ITの分野では、リレーショナルデータベースにおいてあるテーブルの同一の列（カラム）に含まれる異なる値の数（バリエーション）のことを指します

⇒ データのバラツキ度合いの指標。

インデックス作成においては、
🙆‍♂️カーディナリティが高い
→対象カラムに登録されているデータの種類が多い状態
🙅‍♂️カーディナリティが低い
→対象カラムに登録されているデータの種類が少ない状態

例えば、性別のカーディナリティは2になります。（少し時代に則していませんが…）
都道府県数であれば47なのでカーディナリティは47になります。比較すると、カーディナリティが高いのは都道府県数ということになります。

もし、性別のようにカーディナリティが小さいと、一つの値に多くのレコードが紐づくことになり、検索は早くなりません。データの種類としてはカラムに対して、20以上=全体の5%程度に絞り込まれるのが良いとされています。

カーディナリティが大きくてもレコード数に偏りがある場合は、インデックスが有効でない場合があるため注意が必要です。例えば、1~20のデータをとる列があったときに、カーディナリティは20となります。1.の話より、この列に対してインデックスを作成すると効果がありそうです。

しかし、20が99%を占めていて、1~19のデータが1%だけだったデータの時はどうでしょうか。この場合、20が検索条件にあった時はほぼ全てのデータをスキャンしていることと変わりません。この場合のインデックスは安定しません。

レコード件数が少ない時は、インデックスを用いた検索よりフルスキャンの検索の方が早い場合があります。一つの目安として、環境によって異なるので一つの目安程度ですが、1万件を超えるテーブルには効果があると言われます。

例えばcolにインデックスが作成されているとき、下記はインデックスの効果がありません。
B-treeインデックスの中のデータはcolであって、 col * 10ではないためです。

SELECT * FROM sample WHERE col * 10  > 100;

B-treeインデックスはNULLはデータの値とは見なしません。つまりIS NULLまたはIS NOT NULLに対しては効果がありません。※DBMSの種類によっては、NULLも使えることはあるようですが、汎用性はありません。

ORではインデックスの効果がありません。この場合、INで書き換えると回避できます。

SELECT * FROM sample WHERE col = 100 OR col = 101;

もし使えたとしても検索範囲が広くて効果がありません。

SELECT * FROM sample WHERE col <> 100;

🙆‍♂️ 下記はOK！
中間一致や後方一致ではB-treeインデックスの構造上、インデックスを使った検索をすることができません。

SELECT * FROM sample WEHRE col LIKE ‘太郎%’; (前方一致)

🙅‍♂️ 下記はNG！

SELECT * FROM sample WEHRE col LIKE ‘%太郎’; (中間一致)
SELECT * FROM sample WEHRE col LIKE ‘%太郎%’; (後方一致)

データ型が異なる値を選択条件や結合条件として利用する場合、型変換を行う必要があります。もし、データ型が異なる条件に指定した場合でも、DBMSは内部的に暗黙の型変換を行われます。

しかし、この場合インデックスは使用されなくなります。これを回避するために、明示的に条件に使用する値のデータ型を列のデータ型に合わせる必要があります。

例として、colカラムが文字列で定義されているとします。

🙆‍♂️ 下記はOK！

SELECT * FROM sample WEHRE col = '10';

🙅‍♂️ 下記はNG！

SELECT * FROM sample WEHRE col = 10;

WEHREの条件で複数カラムが指定される場合などは単一のインデックスは使用されないことがあります。複数のカラムに対して一つのインデックスを貼る場合は、複合インデックスを貼る必要があります。

インデックスはテーブルのデータが更新されていくと、長期的には構造が崩れていきます。長期運用するシステムでは定期的にインデックスを再構築する必要があります。

もし他にも注意した方が良い点がありましたらご教示いただけると幸いです。

次の記事では、下記の実演を行いたいと思います。