1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kotaro_ai_lab
in

【DB用語の歯がゆさ 第6回】インデックス ― 貼ったのに効かないのはなぜ？

1
Posted at

株式会社Good Labでエンジニアをしている コータロー です。
日々、Java・SQL・Gitなどの技術情報や、新人エンジニア向けの学習ノウハウ、
AI活用についての情報を発信しています。

Good Labについて気になった方は、コーポレートサイトもぜひご覧ください。
▶コーポレートサイト

このシリーズについて

「知ってはいる。けど、人に説明しろと言われると詰まる」——そんな歯がゆいDB用語を、1記事1用語・図解中心で解消していくシリーズです。

まず自己診断

「このクエリ遅いです」「じゃあインデックス貼っといて」——貼りました。変わりませんでした。

このとき何が起きているか説明できますか。多くの人が「インデックスが壊れてる？」と考えますが、たいていは違います。

そもそも「効かない」には、まったく別の2種類があります。

Bを「効いてない」と勘違いして、いじって遅くするのが典型的な事故です。この記事では、AとBを見分けられるようにします。

以下の実測はすべて PostgreSQL 16.14（Docker postgres:16）で50万行、③のみ MySQL 8.0.46で20万行の実測です。EXPLAIN ANALYZE の出力は主要行のみ抜粋しています。

パターンA：DBが「使えない」4つの書き方

① 列に関数・演算を掛けている

いちばん多い原因です。email にインデックスがある状態で、こう書きます。

SELECT * FROM users WHERE lower(email) = 'user123@example.com';

Parallel Seq Scan on users
  Filter: (lower(email) = 'user123@example.com'::text)
Execution Time: 66.328 ms

関数を外すだけで、こうなります。

SELECT * FROM users WHERE email = 'user123@example.com';

Index Scan using idx_email on users
  Index Cond: (email = 'user123@example.com'::text)
Execution Time: 0.190 ms

66.328 ms → 0.190 ms。理由は単純で、インデックスに入っているのは email の値であって lower(email) の値ではないからです。

WHERE price * 1.1 > 1000 のような演算も同じです。WHERE price > 1000 / 1.1 と、列側を裸にするのが鉄則。どうしても関数が必要なら、関数そのものにインデックスを貼る方法（式インデックス）があります。

② LIKE の中間一致・後方一致

SELECT * FROM users WHERE email LIKE '%123@example.com';

Parallel Seq Scan on users
Execution Time: 26.406 ms

インデックスは辞書と同じで「先頭から」しか引けません。「〜で終わる語」を辞書で探せないのと同じです。

③ PostgreSQL特有の罠：前方一致でも効かないことがある

ここは知らないとハマります。前方一致なのに使われないケースです。

SELECT * FROM users WHERE email LIKE 'user123%';

Parallel Seq Scan on users
Execution Time: 19.961 ms

インデックスは貼ってあるのに、Seq Scanです。原因は照合順序（collation）。このDBは en_US.utf8 で動いており、その場合PostgreSQLの通常のB-treeインデックスは LIKE の前方一致に使えません。専用の演算子クラスを付けて貼り直します。

CREATE INDEX idx_email_pattern ON users(email text_pattern_ops);

Bitmap Index Scan on idx_email_pattern
  Index Cond: ((email ~>=~ 'user123'::text) AND (email ~<~ 'user124'::text))
Execution Time: 0.553 ms

19.961 ms → 0.553 ms。まさに「貼ったのに効かない」の代表例です。なお text_pattern_ops を付けても、中間一致は依然として効きません（実測でも 25.037 ms のSeq Scanのまま）。前方一致だけの救済策です。

④ 暗黙の型変換（MySQLは黙って遅くなる）

member_no は文字列型（VARCHAR）。そこに数値を渡します。MySQL 8.0 の EXPLAIN です。

SELECT * FROM users WHERE member_no = 123;

type: ALL          ← フルスキャン
possible_keys: idx_member_no   ← インデックスは「候補」に挙がっている
key: NULL          ← なのに使われていない
rows: 199641

possible_keys に名前があるのに key が NULL。「インデックスはあるが使わなかった」が、そのまま出力されています。 正しく文字列で渡すと：

SELECT * FROM users WHERE member_no = '0000000123';

type: ref
key: idx_member_no
rows: 1

rows が 199641 → 1。ここでエンジン差が出ます。同じことをPostgreSQLでやると、そもそもエラーになります。

ERROR:  operator does not exist: text = integer

MySQLは黙って遅くなり、PostgreSQLは怒ってくれる。 MySQL側のほうが、気づかないぶん厄介です。

パターンB：DBが「あえて使わない」

ここからが本題であり、いちばん誤解される部分です。

⑤ 絞り込めない条件では、使わないほうが速い

status 列にインデックスを貼ったテーブルで、まったく同じ形のSQLを2本流します。違うのは値だけです。

SELECT * FROM users WHERE status = 'banned';   -- 500件 / 全体の0.1%

Index Scan using idx_status on users
Execution Time: 1.619 ms

SELECT * FROM users WHERE status = 'active';   -- 499500件 / 全体の99.9%

Seq Scan on users
  Filter: (status = 'active'::text)
Execution Time: 69.097 ms

同じ列・同じインデックス・同じSQLの形。値が違うだけで、片方はIndex Scan、片方はSeq Scanです。

これはバグではありません。99.9%の行を返すなら、インデックスを経由して1行ずつ本体を読みに行くより、最初から全部順に読むほうが速いからです。

この場合、直すべきものは何もありません。 「Seq Scanが出ている＝悪」ではないのです。ここを理解していないと、無駄なインデックスを増やして更新性能だけ落とすことになります。

第3回で見たとおり、インデックスはロックの巻き添え範囲も左右します（ロックとデッドロック）。増やせばいいというものではない、という点でも共通です。

もうひとつの「使えない」：複合インデックスの左端規則

最後に、複合インデックス特有の落とし穴です。(status, created_at) の順で貼ったインデックスがあります。

-- 左端の status から条件がある → 使える
SELECT * FROM users WHERE status='banned' AND created_at >= '2020-06-01' AND created_at < '2020-07-01';

Index Scan using idx_status_created on users
Execution Time: 0.167 ms

-- 左端を飛ばして created_at だけ → 使えない
SELECT * FROM users WHERE created_at >= '2020-06-01' AND created_at < '2020-06-01 02:00:00';

Parallel Seq Scan on users
Execution Time: 19.259 ms

後者はヒットするのが120件だけ（全体の0.02%）で、絞り込みとしては理想的です。それでもSeq Scanになりました。左端の status に条件がないため、木を辿った絞り込みができないからです。

電話帳が「姓 → 名」の順に並んでいるとき、姓が分かれば一気にページを絞れますが、名前だけで探すなら全ページ読むしかありません。複合インデックスも同じで、左端の列に条件がないとインデックスを全部舐めることになり、それなら表を直接読むのと大差ない——だからプランナは選びません。

正確には「絶対に使えない」わけではありません。PostgreSQL公式ドキュメントにも、左端に条件がなくても複合インデックスは原理的には使用可能だが、インデックス全体をスキャンすることになるため、たいていの場合プランナは全表走査を選ぶと書かれています。実測のSeq Scanは、まさにこの判断の結果です。

なおMySQL 8.0には、この状況で左端列の値を総当たりするスキップスキャンという最適化があり、条件次第では複合インデックスが使われることもあります。

左端の列から順に条件が埋まっているほど効く——これが左端規則です。

早見表

症状 種類 対処
列に関数・演算を掛けている 使えない 列を裸にする／式インデックス
LIKE '%...'（中間・後方一致） 使えない 全文検索など別手段を検討
LIKE 'abc%' なのに効かない（PG） 使えない text_pattern_ops で貼り直す
文字列列に数値を渡している 使えない 型を合わせる
複合インデックスの左端を飛ばした 使えない 列順を見直す／条件を足す
大半の行がヒットする条件 使わない 対処不要（正しい判断）

下の1行だけが性質の異なる項目です。ここを切り分けられれば、「貼ったのに効かない」の大半は自力で説明できます。

まとめ：1行で説明するなら

「インデックスが効かない原因は2種類あります。ひとつはDBが使えないケースで、列に関数を掛けた・中間一致LIKE・型が違う・複合インデックスの左端を飛ばした、などSQLの書き方が原因。もうひとつはDBがあえて使わないケースで、大半の行がヒットするなら全表走査のほうが速いという正しい判断です。前者はSQLを直す、後者は触らない。まず EXPLAIN でどちらか確かめます」

もっと深く知りたい方へ

本記事は「効かない理由のカタログ」に絞りました。B-treeがなぜ速いのかという内部構造、複合インデックスの列順の決め方、カバリングインデックスといった設計面は、別記事で詳しく扱っています。

▶ 【新人向け】インデックス設計の基本：B-treeの仕組み・複合インデックス・カバリングインデックスをEXPLAINで読み解く（PostgreSQL 17）

次回

ここまで何度も EXPLAIN の出力を貼ってきましたが、実は読み方の説明を一度もしていません。Seq Scan と Index Scan の違いは見えても、cost=0.42..30.83 や rows=517、Bitmap Heap Scan が何を意味するかは別の話です。

第7回：実行計画 ——「EXPLAINの出力、どこを見る？」を図解します。

（シリーズ全11回の予定は第1回に掲載しています）

参考

@kotaro_ai_lab
AI活用や開発効率化について発信しています。フォローお気軽にどうぞ！

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?