Zenn
🌊

インデックスのアンチパターン

2025/02/05に公開
SQL
DB
インデックス
パフォーマンス改善
tech

Daily Blogging46日目

DBパフォーマンスのチューニングといえば、まずインデックスが挙がってくるよね

インデックスとは

SQLのチューニングの基本は、I/Oをいかに減らすかであり、
インデックスは、実テーブルに対するI/Oを減らすための手段である。
インデックスとは、指定したカラムの値とその値がどこに位置しているのかを合わせたデータ(テーブル)。
格納される時は、値でソートされた状態で格納される。

インデックススキャンとは、このインデックステーブルをスキャンすることであり、その後に実テーブルをスキャンして実際のレコードを取得する。
インデックスを先にスキャンすることにより、膨大なデータがある実テーブルをフルスキャンする必要がなくなり、処理が速くなる。

どれだけばらつきがあるか、どれくらいデータを取得するのか

効果的なインデックスを考える時の重要な概念が２つある。

  • カーディナリティ
  • 選択率

カーディナリティ

カーディナリティとは、そのカラムに対する値のばらつきのことであり、ばらつきが多い場合はカーディナリティが高い。
値がばらつくことで、インデックステーブルに対する検索が効率的に行われる。
→値の重複があるとその分検索量が増える
そのため、絞り込んだ時の値のばらつきが全体レコードの5％以内になる場合でないとインデックスはあまり効果がないと言われている。
※時と場合によるよ

もっともカーディナリティが高いのはprimaryキーのカラムとか

選択率

選択率とは、全レコードに対してクエリが返すレコード数の割合のことであり、選択率が低いほどインデックスの効果がある。
大体5%以内であれば効果があると言われている
※時と場合によるよ

選択率が高いということはヒットするレコード数が多いということであり、場合によっては実テーブルをそのままフルスキャンした方が余計なI/Oが発生せずに済むこともある。

インデックスの種類

インデックスと言ってもいくつか種類があるんだよ
状況に応じて適切なインデックスを選ぼう

カバリングインデックス

カバリングインデックスは、クエリに登場するカラム全てにまとめてインデックスを貼るインデックスのことであり、実テーブルにアクセスしなくてもこのインデックステーブルにアクセスするだけで必要な値が取得できる。
そのため、インデックステーブルのスキャンのみの処理になり高速な検索が可能になる。

クエリに他のカラムが追加されるとこのインデックスが採用されないので、扱いに注意が必要であり、ストレージを大きく消費する。

複合インデックス

複合インデックスは、複数のカラムに対して貼られるインデックスであり、検索効率が向上する。
登録するカラムの順番が大事であり、先頭にカーディナリティが高いものを登録する。
B-tree検索で先頭のカラムから検索をかけていくため、値のばらつきが少ないものを先に登録することで後のスキャンするテーブルサイズが小さくなる。

また、クエリの条件式で使用する際は、複合インデックスの順番通りに記述しないと複合インデックスが採用されないので注意が必要。

インデックスのアンチパターン

  • そもそもサイズが小さいテーブルに対してインデックスを貼る
    • スキャンするテーブルサイズを減らすのが目的なので、元々サイズの小さいテーブルは余計なスキャンが発生するため逆効果になる。
    • 一概には言えないが、1万行以下の場合はほぼ効果がない
  • カーディナリティが低いカラムに対するインデックス
  • 選択率が高いクエリに対するインデックス

インデックスを貼る際は、カラムだけみていても効果的なインデックスを貼ることができない
実際に実行したいクエリや対象のテーブルのデータ数なども確認する必要があるよ。

インデックスショットガン

むやみやたらにインデックスをつくるパターン
※テーブルにあるカラム全部にインデックス貼ってるサービスもあるとかないとか...

インデックスがあればパフォーマンスが上がるんでしょ？と思ってインデックスを作りまくるのはデメリットしかない。

書き込みの遅延

インデックスはデータ取得時には効果を発揮するが、その分データ更新時に処理が遅くなる。
インデックス自体の更新があるので必然的にそうなる
「何かを得るためには同等の対価が必要になる」のだ

ディスクI/Oが増える

一度読み込まれたデータはキャッシュに残る。
※LRUアルゴリズムで消されてくよ

selectクエリが実行される時は、キャッシュにデータを確認しにいき、該当データがなければディスクに読み込みに行く。

インデックスも同じ領域にデータが格納されているので、インデックスが多いと本来はキャッシュに残るはずの実データが残らないという可能性が出てくる。

これにより、本来は不要なディスクI/Oが増えることになる。

キャッシュ領域はDBによって名前がちがう
PostgreSQLだと、shared buffers

意図しない実行計画になる

インデックスがいっぱいあると、オプティマイザが一番処理効率のいいクエリの実行計画を算出できなくなることがある。

インデックスが効かないパターン

クエリによってはそもそもインデックスが効かないことがあるよ

否定系を用いた絞り込み

!=, <>, NOT IN, NOT EXISTS
インデックスが効くのは = や IN などの条件

SELECT * FROM users WHERE age != 30;

演算されたカラムによる絞り込み

インデックスは、カラムの値そのものに対して効くので加工されちゃうと効かなくなる

SELECT * FROM orders WHERE DATE(created_at) = '2024-02-01';
SELECT * FROM orders WHERE created_at + INTERVAL '7 days' < NOW();

前方一致以外の曖昧検索

効く

SELECT * FROM products WHERE name LIKE 'Laptop%';

効かない

SELECT * FROM products WHERE name LIKE '%Laptop%';
SELECT * FROM products WHERE name LIKE '_aptop%';

LIKE'xxx%'（前方一致）なら、B-treeインデックスを左端から順番に探索できるため効率的。
LIKE'%xxx%'の場合、インデックスが使えずフルスキャンが発生する。

IS NULLによる絞り込み

SELECT * FROM users WHERE last_login IS NULL;

インデックスにNULLは含まれないので

暗黙的な型変換が行われている

-- `id` は INT 型なのに、'123' は文字列
SELECT * FROM users WHERE id = '123';

注意

DBMSによっては仕組みや設定がところどころ違うよ

Discussion