タイトル: インデックスの作成
SEOタイトル: DB2 CREATE INDEX 構文｜UNIQUE / 複合 / 降順 / INCLUDE の使い方
| この記事の要点 |
- DB2 でインデックスを作成する SQL 構文
- 基本:
CREATE INDEX 名 ON テーブル名 (列1, 列2); - UNIQUE インデックス:
CREATE UNIQUE INDEX ... — 重複を許さない - 降順:
CREATE INDEX ix ON t (col DESC); - INCLUDE 句で非キー列を含める（カバリングインデックス）— 検索性能 +α
- 貼りすぎると INSERT/UPDATE が遅くなる。WHERE / ORDER BY / JOIN で頻繁に使う列に絞る
|
基本構文
-- 単一列インデックス
CREATE INDEX idx_users_email ON users (email);
-- 複合インデックス（左から評価される）
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);
-- UNIQUE（重複不可。実質ユニーク制約）
CREATE UNIQUE INDEX idx_users_email_uniq ON users (email);
-- 降順
CREATE INDEX idx_orders_date_desc ON orders (order_date DESC);
-- 複数の昇降混合
CREATE INDEX idx_logs ON access_logs (user_id ASC, accessed_at DESC);
INCLUDE 句（カバリングインデックス）
DB2 はインデックスに非キー列を含める INCLUDE 句をサポート。検索条件には使わないが SELECT に含めたい列を入れることで、テーブル本体を読まずに済むようになります:
-- email で検索したついでに name と age を取りたいケース
CREATE UNIQUE INDEX idx_users_cover
ON users (email)
INCLUDE (name, age);
-- このクエリがインデックスだけで完結する（テーブルアクセスなし）
SELECT name, age FROM users WHERE email = ?;
インデックスの確認・削除
-- システムカタログから一覧
SELECT TABNAME, INDNAME, COLNAMES
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = 'MYSCHEMA';
-- 特定テーブルのインデックス確認
SELECT INDNAME, UNIQUERULE, COLNAMES
FROM SYSCAT.INDEXES
WHERE TABNAME = 'USERS';
-- インデックス削除
DROP INDEX idx_users_email;
-- インデックスのリビルド（断片化解消）
REORG INDEXES ALL FOR TABLE users;
複合インデックスの「左から評価」ルール
CREATE INDEX ix ON orders (user_id, order_date) がある場合:
| クエリ | インデックス使用 |
|---|
WHERE user_id = 1 | ○ 使われる（先頭列） |
WHERE user_id = 1 AND order_date = '2024-01-01' | ○ 完全に使われる |
WHERE user_id = 1 ORDER BY order_date | ○ ソートにも使える |
WHERE order_date = '2024-01-01' | × 使われない（先頭列なし） |
WHERE user_id LIKE '1%' | ○ 前方一致は使われる |
WHERE user_id LIKE '%1' | × 後方一致は使えない |
EXPLAIN（実行計画）で使われているか確認
-- DB2 の EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM users WHERE email = 'foo@example.com';
-- 結果を見る
SELECT OPERATOR_TYPE, OBJECT_NAME, TOTAL_COST
FROM EXPLAIN_OPERATOR
ORDER BY OPERATOR_ID;
-- ツール経由なら db2expln コマンドが便利
-- db2expln -d MYDB -q "SELECT * FROM users WHERE email = '?'"
インデックスを貼るべき列の選び方
| 条件 | インデックス効果 |
|---|
| WHERE で頻繁に使う | ○ 最重要 |
| JOIN の結合キー | ○ 大幅な性能改善 |
| ORDER BY / GROUP BY の対象列 | ○ ソート省略可能 |
| カーディナリティ（種類数）が高い列 | ○ ユニーク値が多い列ほど効く |
| 性別など 2 値しかない列 | × ほぼ無意味 |
| UPDATE 頻度が高い列 | △ INSERT/UPDATE 性能が落ちる |
| NULL が多い列 | △ DB2 はデフォルトで NULL を含めるが、NOT NULL 列の方が効率的 |
注意点
- INSERT / UPDATE / DELETE が遅くなる: インデックス更新のオーバーヘッド。1 テーブル 5 個前後が目安
- ディスク容量を消費: 大きなテーブルでは数百 MB〜数 GB
- 断片化: 大量更新後は
REORG INDEXES を定期実行
- 使われないインデックス:
MON_GET_INDEX で利用統計を取り、未使用なら削除
- 主キー / UNIQUE 制約は自動でインデックスが作られる — 二重で貼らない
関連
- パーティション索引: 大規模テーブルではパーティションごとに索引を持つ設計が有効
- クラスタード索引:
CREATE INDEX ... CLUSTER でテーブル物理配置を索引順に近づける
- テキスト索引: 全文検索なら
CREATE INDEX ... USING DB2 TEXT
