B-Tree Indexアンチパターン

tl;dr

• B-Treeインデックスが効かなくなる典型的なSQLパターンを具体例で理解できます
• 演算・関数・IS NULL・否定形・LIKE・暗黙の型変換がなぜアンチパターンかを説明します
• EXPLAINの結果が悪化する理由をB-Treeの性質から説明します
• 初心者でもインデックスを活かすSQLの書き方がわかるようになります

はじめに

SQLを書いていると、「インデックスを貼っているのに遅い」と感じたことはありませんか？
その原因の多くは、B-Treeインデックスの使い方を壊すSQLの書き方にあります。

本記事では、達人に学ぶDB設計徹底指南書 第2版 を参照しながら、
B-Tree Indexアンチパターンについて、パフォーマンスチューニング初心者向けに解説していきます。

特定のDB製品を貶しているわけではなく、MySQL / PostgreSQL / Oracleなど、
一般的なB-Treeインデックスに共通する性質を扱います。

B-Treeインデックスとは何か

B-Treeインデックスは、キーがソートされた木構造で管理されるインデックスです。
範囲検索や前方一致検索が高速に行える一方で、検索条件の書き方に強く依存します。

つまり、WHERE句の書き方次第でインデックスが使われなくなるという特徴があります。

• B-Treeはキー順に並んでいる
• 条件がキー順を崩すと、探索ができなくなる
• 結果としてフルスキャンが発生します

前提となるテーブルとインデックス

以降の例では、以下のようなテーブルを想定します。

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255),
  age INT,
  created_at DATE
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);
CREATE INDEX idx_users_created_at ON users(created_at);

アンチパターン1: インデックス列に演算を行っている

問題のあるSQL例

SELECT *
FROM users
WHERE age + 1 = 21;

• ageに対して演算を行っています
• B-Treeはageの生の値でソートされています
• 演算後の値では探索できません

改善例

SELECT *
FROM users
WHERE age = 20;

• インデックス列をそのまま比較します
• インデックスレンジスキャンが可能です

つまり、インデックス列は左辺で裸のまま使う必要があります。

アンチパターン2: インデックス列にSQL関数を適用している

問題のあるSQL例

SELECT *
FROM users
WHERE DATE(created_at) = '2025-01-01';

• created_atにDATE()関数を適用しています
• インデックスは関数適用後の値を保持していません

改善例

SELECT *
FROM users
WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02';

• 範囲検索に書き換えています
• B-Treeの順序性を活かせます

アンチパターン3: IS NULL述語を使っている

問題のあるSQL例

SELECT *
FROM users
WHERE email IS NULL;

• 標準的なB-Treeの構造上はNULLを通常の値として扱いにくいです
• DBによってはインデックスが使われません

NULLを検索したいケース自体を減らせないか、設計段階で検討するのがおすすめです

一部のDBMSはIS NULLを用いた場合にもインデックスが有効に作用します。

代替案の一例

• NOT NULL制約を付ける
• デフォルト値を使う

アンチパターン4: 否定形を用いている

問題のあるSQL例

SELECT *
FROM users
WHERE age <> 20;

• 否定条件は範囲が広すぎます
• 結果として全件走査になりやすいです

考え方

• 否定条件はインデックスに不向き
• 必要な値を肯定形で列挙できないか検討します

アンチパターン5: 後方一致・中間一致のLIKE述語

問題のあるSQL例

SELECT *
FROM users
WHERE email LIKE '[e-mail]';

• %から始まるLIKEは前方一致ではありません
• B-Treeの順序性を使えません

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

• 中間一致も同様にインデックスが効きません

全文検索が必要な場合は、B-Treeではなく全文検索インデックスの利用を検討してください

アンチパターン6: 暗黙の型変換を行っている

問題のあるSQL例

SELECT *
FROM users
WHERE age = '20';

• ageはINT型です
• 文字列との比較により暗黙の型変換が発生します
• DBによってはインデックスが無効化されます（多くの場合、DB内部でインデックス列側に型変換関数が適用され、アンチパターン2と同じ状態になるため）

改善例

SELECT *
FROM users
WHERE age = 20;

• 型を正しく揃えます
• インデックスが安定して使われます

まとめ

• B-Treeインデックスは条件の書き方に非常に敏感です
• 演算・関数・否定形・LIKE・NULL・型変換はアンチパターンになりやすいです
• インデックス列は「そのまま・肯定形・型一致」で使うのが基本です
• SQLを書く前に、B-Treeの並び方を意識することが重要だと感じました

パフォーマンスチューニングは難しそうに見えますが、
まずは「インデックスが使える書き方」を避けないだけでも、
クエリはかなり改善されるかもしれません。