SQLオプティマイザとインデックス設計から学んだこと
1. インデックスは常に速いのか？
- インデックスを使えば必ず速くなるわけではない。
-
インデックス検索が遅くなる2つの典型的なケース:
- 広すぎる範囲スキャン → 不要に多くのROWIDを取得し、テーブルアクセスを繰り返す
- フィルタ述語 (Filter Predicate) → インデックスで範囲を絞れず、行取得後に条件判定で捨てる
➡️ アクセス述語 (Access Predicate) として使われる条件だけが、真に性能を改善する。
2. オプティマイザ (Optimizer)
-
CBO (Cost-Based Optimizer)
複数の実行計画を生成し、統計情報を基にコストを計算して最適なものを選択。 -
RBO (Rule-Based Optimizer)
固定ルールで実行計画を作成。柔軟性が低いため、現在はほとんど使われない。
💡 実行計画を見る際は Predicate Information に注目すること。
ここに「どの条件がAccess Predicateか／Filter Predicateか」が表示され、性能差の理由がわかる。
3. ケースインセンシティブ検索
-
UPPER/LOWER関数を使った条件では、通常のインデックスが無効化される。 - 解決策: 関数ベースインデックス (Function-Based Index, FBI) を作成する。
CREATE INDEX emp_up_name
ON employees (UPPER(last_name));
- PostgreSQL →
IMMUTABLE関数宣言が必要 - Oracle →
DETERMINISTIC宣言が必要
⚠️ 非決定性関数（例: SYSDATE を利用した年齢計算）はインデックス化できない。
4. LIKE検索とワイルドカード
-
LIKEでは 最初のワイルドカードの前 までがAccess Predicateになる。 -
例:
-
LIKE 'WI%ND'→ プレフィックスが短く範囲スキャンが広い -
LIKE 'WIN%D'→ より絞り込める -
LIKE 'WINA%'→ プレフィックス一致なので最も効率的
-
⚠️ LIKE '%TERM' のような先頭ワイルドカードはインデックスを使えない。
→ フルテキスト検索 (MySQL: MATCH ... AGAINST, PostgreSQL: @@, Oracle/SQL Server: CONTAINS) を検討すべき。
5. スマートロジックの落とし穴
WHERE (subsidiary_id = :sub_id OR :sub_id IS NULL)
AND (employee_id = :emp_id OR :emp_id IS NULL)
AND (UPPER(last_name) = :name OR :name IS NULL)
- 一見便利だが、全条件が無効化される可能性があるため、DBは常にワーストケースに備え フルスキャン計画を選ぶ。
- 解決策: 必要な条件だけを組み立てる 動的SQL + バインド変数 を使う。
6. 数式による条件
-
numeric_number - 1000 > ?のように書くとインデックスは使えない。 - 式を変形して 片側に列、片側に定数 を置くことで関数ベースインデックスを作成できる。
WHERE 3*a - b = -5
CREATE INDEX math ON table_name (3*a - b);
7. データ量とスケーラビリティ
-
データ量が増えるとクエリは必ず遅くなるが、増加率はインデックス設計に依存する。
-
例:
-
SCALE_SLOW:
(section, id1, id2)-
sectionのみがAccess Predicate → セクション全件を読み、id2はFilter Predicate - データ量に比例して遅くなる
-
-
SCALE_FAST:
(section, id2, id1)-
sectionとid2がAccess Predicate → 必要行だけ取得 - 選択行数に比例して遅くなる
-
-
💡 結論: インデックス列順序を誤ると、データ量が増えたとき性能差は指数的に広がる。
結論
- インデックスは正しく設計しないと“遅いインデックス”になる。
- Predicate Informationを必ず確認し、WHERE句の条件が Filter PredicateでなくAccess Predicateとして使われる ように設計すること。
- データベースのスケーラビリティは、インデックス定義（特に列順序）に大きく左右される。