【DB】インデックスによる検索高速化の仕組み

1. 経緯

実務や学習の中で「インデックスって結局どういう仕組みで速くなるの？」と聞かれた際、感覚的には理解していても言葉で明確に説明できなかったため、動作原理の調査とDocker環境での実証検証を行い、知識の整理としてまとめてみた。（Geminiさんに非常にお世話になりました🙇）

2. インデックスとは？

インデックスとは、一言で言うと「データベースの目次」です。本の中から特定のキーワードを探す際、目次や索引を使えば目的のページへ一瞬でたどり着けるのと全く同じ仕組み。

インデックスが存在しない場合、データベースはテーブル全体を1行ずつ確認する「フルテーブルスキャン（全件走査）」を行います。データ量が数十万〜数百万件に増えると、処理時間（I/O負荷）が大幅に増加してしまう。

仕組みの比較

① インデックスなし：全件スキャン（フルスキャン）

探すイメージ: 本の1ページ目から最後のページまで順番にめくって探す状態
データの持ち方: 順不同でデータファイルに書き込まれている（ヒープ構造）
探す仕組み: 10万件あれば10万件すべてのデータをディスクから読み出して確認
計算量: $O(N)$（データ量に比例して遅くなる）

② インデックスあり：B-Tree（木構造）による探索

探すイメージ: 巻末の五十音順・アルファベット順に整列された索引（目次）を使う状態
データの持ち方: B-Tree（Balanced Tree） と呼ばれる木構造で、データがあらかじめソート（整列）されて管理されている
計算量: $O(\log N)$（データが10万件あっても、十数回の比較で目的のデータアドレスへ到達）

3. インデックスの設定方法

インデックスはテーブル作成時、または作成後に CREATE INDEX 文を用いて設定。

SQL

-- 基本的な作成例（employeesテーブルのlast_nameカラムに作成）
CREATE INDEX idx_emp_name ON employees (last_name);

-- テーブル作成後にあとから追加する例
CREATE TABLE test_users (
    user_id NUMBER PRIMARY KEY,
    status VARCHAR2(20),
    created_at DATE
);

-- status カラムにインデックスを追加
CREATE INDEX idx_test_status ON test_users(status);

4. Docker (Oracle DB) での高速化検証

実際に Docker 上で Oracle Database XE 環境を構築し、10万件のデータを用いてインデックス有無による実行計画（Cost）の変化を検証。

検証環境の構築とデータ投入

docker-compose.yml として保存するファイルの中身。

YAML

services:
  oracle:
    image: gvenzl/oracle-xe:21-slim
    container_name: oracle-db
    ports:
      - "1521:1521"
    environment:
      - ORACLE_PASSWORD=YourPassword123
    volumes:
      - ./oradata:/gvenzl/oradata

作業ディレクトリ（例~/Documents/docker/oracle）を作成し、上記内容の docker-compose.yml を配置した上で以下のコマンドを実行。

Bash

# 1. コンテナの起動
docker compose up -d

# 2. 初期化ログの確認（"DATABASE IS READY TO USE!" が出れば完了）
docker logs -f oracle-db

# 3. Oracle DB（sqlplus）へのログイン
docker exec -it oracle-db sqlplus system/YourPassword123@XEPDB1SQL

SQL

-- 3. 検証用テーブルの作成
CREATE TABLE test_users (
    user_id NUMBER PRIMARY KEY,
    status VARCHAR2(20),
    created_at DATE
);

-- 4. 10万件のダミーデータ投入（約1%だけ 'ACTIVE' を設定）
INSERT INTO test_users
SELECT 
    LEVEL,
    CASE WHEN MOD(LEVEL, 100) = 0 THEN 'ACTIVE' ELSE 'INACTIVE' END,
    SYSDATE - MOD(LEVEL, 365)
FROM DUAL
CONNECT BY LEVEL <= 100000;

COMMIT;

検証①：インデックス作成前の実行計画（フルスキャン）

SQL

EXPLAIN PLAN FOR
SELECT * FROM test_users WHERE status = 'ACTIVE';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

🌾　EXPLAIN PLAN FOR を頭につけてSQLを実行すると、Oracleのオプティマイザが「この検索をするならどのルート（インデックスかフルスキャンか）が一番速いか」を計算し、その結果（実行計画）をデータベース内部の管理テーブル（PLAN_TABLE）に保存します。

🌾　この時点では、画面には Explained. と表示されるだけで、具体的な中身は見えません。

🌾　直前に EXPLAIN PLAN FOR で保存された実行計画を DBMS_XPLAN.DISPLAY という関数を使って取得し、読みやすい表形式（CostやOperationなど）に整えて画面に出力します。

【結果】

--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |  1054 | 35836 |   104   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL | TEST_USERS |  1054 | 35836 |   104   (1)| 00:00:01 |
--------------------------------------------------------------------------------

TABLE ACCESS FULL（フルスキャン）が選択され、Costは 104。

検証②：インデックス作成後の実行計画（高速化確認）

SQL

-- インデックス作成
CREATE INDEX idx_test_status ON test_users(status);

-- 再度実行計画を確認
EXPLAIN PLAN FOR
SELECT * FROM test_users WHERE status = 'ACTIVE';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

【結果】

----------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Cost     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |  1000 |    10    |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_USERS      |  1000 |    10    |
|*  2 |   INDEX RANGE SCAN                  | IDX_TEST_STATUS |  1000 |     3    |
----------------------------------------------------------------------------------

処理が INDEX RANGE SCAN に変化し、Costが 104 → 10（約1/10に低減） された。

補足：Cost（コスト）とは？

Oracleのオプティマイザが算出した「処理にかかる予測負荷数値」。数値が小さいほどCPUやディスクI/Oの負荷が低く、処理が高速化。

後片付け

Bash

# コンテナの停止・削除
docker compose down

5. インデックスの種類

用途に合わせて適切な種類のインデックスを選択。

基本インデックス（B-Tree：デフォルト）

SQL

CREATE INDEX idx_emp_name ON employees (last_name);

Oracleの標準。一般的な範囲検索や等価検索に最も有効。

UNIQUE インデックス

SQL

CREATE UNIQUE INDEX idx_emp_email_uniq ON employees (email);

一意性を保証しつつ検索を高速化（重複値不可）。

複合インデックス（列順が極めて重要）

SQL

CREATE INDEX idx_emp_dept_job ON employees (department_id, job_id);

複数カラムを組み合わせたインデックス。先頭列（department_id）が WHERE 条件に含まれないと機能しない（最左プレフィックス原則）。

関数ベースインデックス（FBI）

SQL

CREATE INDEX idx_emp_upper_last ON employees (UPPER(last_name));

WHERE UPPER(last_name) = 'KING' のように関数を使う検索で有効。

Bitmap インデックス

SQL

CREATE BITMAP INDEX idx_emp_status ON employees (status);

カーディナリティ（値の種類）が低い列向け。分析系（DWH）に有効だが、更新時のロック競合が激しいため一般Webサービス（OLTP）には不向き。

オンライン作成（本番環境向け）

SQL

CREATE INDEX idx_orders_status ON orders (status) ONLINE;

テーブルの書き込み（DML）を止めずにバックグラウンドで作成。

6. インデックスの削除方法（DROP）

不要になったインデックスは DROP INDEX コマンドで削除。

SQL

DROP INDEX idx_test_status;

7. インデックスが使われない落とし穴

せっかく作っても、書き方によってインデックスが無効化（フルスキャン化）される典型的なケース。

カラムを関数・演算で囲んでいる
- ❌ WHERE TO_CHAR(hire_date, 'YYYY') = '2026'
- ⭕ WHERE hire_date >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
暗黙の型変換
- ❌ WHERE id = '100'（idが数値型のカラム）
LIKE の後方一致・中間一致
- ❌ WHERE name LIKE '%abc'（前方一致 name LIKE 'abc%' のみ有効）
IS NULL 条件での検索
- B-Treeは基本的に NULL 値をインデックスに格納しないため。
否定条件（NOT IN / != / <>）
- 「それ以外」を探す処理は基本的にフルスキャンになる。
先頭列を含まない複合インデックス検索
- (department_id, job_id) のインデックスに対し WHERE job_id = 'SA_REP' のみで検索した場合。
カーディナリティ（データの分散度）が低い列
- フラグや性別（男/女）など、全体に対する絞り込み割合が低い場合はオプティマイザが意図的にフルスキャンを選ぶことがある。

8. 実務におけるインデックス設計のポイント

WHERE句・JOIN条件で頻繁に使われる列に絞って作成する
外部キー（FK）列には原則作成する（親テーブル削除時の行ロック防止）
データ量が少ない小規模テーブルには不要（そのままフルスキャンした方が速い）
更新頻度が高い列への付与は慎重に行う（INSERT / UPDATE / DELETE のたびに目次更新が発生して更新処理が重くなるため）
複合インデックスは最も絞り込みに貢献する列を先頭に配置する

9. まとめ

インデックスは適切なカラムに正しく設定することで、検索速度を劇的に向上させる非常に有力な手段。

一方で、作成しすぎると書き込み性能の低下やストレージ容量の圧迫につながるため、「EXPLAIN PLAN で実行計画とCostを確認しながら適切に設計する」 習慣をつけることが重要。