【データベース】インデックスの種類と役割

Transcript

1. 【データベース】 インデックスの種類と役割
2. 目次 1. Index（索引）とは？ 2. Indexの種類 3. Indexの構造イメージ（複合インデックス） 4. 検証（複合インデックス、部分インデックス） 5. まとめ
3. 1.　Index（索引）とは？ • 本の目次や辞書の索引のような役割 • データ検索を高速化する仕組み • 特定のカラムを元にデータの並びを最適化 • クエリの検索範囲を狭め、不要なスキャンを削減 • 適切に設計しないと、 書き込み（INSERT/UPDATE）が遅くなることもある
4. 2.　Indexの種類 概念 構造 用途 使用頻度 B-Treeインデックス 基本構造 一般的な検索用 ★★★★★ 複合インデックス B-Tree構造 複数カラム検索用 ★★★★☆ 部分インデックス B-Tree構造 条件付き検索用 ★★★☆☆ Uniqueインデックス B-Tree構造 重複防止 ★★★★☆ Clusteredインデックス B-Tree構造 （物理順序） 物理順序の最適化 ★★★☆☆ Full-Textインデックス 特殊構造 （全文検索用） テキスト検索用 ★★☆☆☆ よく使用される一部のIndexを紹介
5. 2.　Indexの種類 概念 PostgreSQL MySQL （InnoDB） Oracle SQL Server B-Treeインデックス ⭕ ⭕ ⭕ ⭕ 複合インデックス ⭕ ⭕ ⭕ ⭕ 部分インデックス ⭕ ❎ ⭕ ❎ Uniqueインデックス ⭕ ⭕ ⭕ ⭕ Clusteredインデックス ❎ ※類似機能有 ❎ ⭕ ⭕ Full-Textインデックス ⭕ ⭕ ⭕ ⭕ 各DB製品の対応有無
6. 2.　Indexの種類 1. B-Treeインデックス • 特徴: ◦ 最も一般的なインデックス構造。 ◦ データを階層的に管理し、範囲検索や等価検索に強い。 • 使用頻度: ★★★★★（非常に高い） • 適用シナリオ: ◦ 主キー（PRIMARY KEY）や一意性制約（UNIQUE）に必須。 ◦ 単一カラムのフィルタリングやソート、 JOIN操作。 • 具体例: CREATE INDEX idx_users_id ON users(id);
7. 2.　Indexの種類 2. 複合インデックス（Compositeインデックス） • 特徴: ◦ 複数のカラムを組み合わせたインデックス。 ◦ 複合条件や複数カラムを条件とするクエリを効率化。 • 使用頻度: ★★★★☆（高い） • 適用シナリオ: ◦ WHERE句やORDER BY句で複数のカラムが使用される場合。 ◦ 複数カラムによるJOIN操作が頻繁に行われる場合。 • 具体例: CREATE INDEX idx_users_name_age ON users(name, age);
8. 2.　Indexの種類 3. 部分インデックス（Partialインデックス） • 特徴: ◦ 特定の条件に一致するデータのみを対象とするインデックス。 ◦ 無駄なインデックス領域を削減し、パフォーマンスを向上。 • 使用頻度: ★★★☆☆（中程度） • 適用シナリオ: ◦ 特定の状態やフラグ（論理削除等） が立ったデータのみを頻繁に検索する場合。 ◦ データの一部だけが頻繁に参照される場合。 • 具体例: CREATE INDEX idx_active_users ON users(name) WHERE is_active = TRUE;
9. 2.　Indexの種類 4. Uniqueインデックス • 特徴: ◦ 各行の値が一意であることを保証するインデックス。 ◦ データ整合性を保つために使用される。 • 使用頻度: ★★★★☆（高い） • 適用シナリオ: ◦ ユーザー名やメールアドレスなど、重複を許さないデータ列に対して。 • 具体例: CREATE UNIQUE INDEX idx_unique_email ON users(email);
10. 2.　Indexの種類 5. Clusteredインデックス（クラスタ化インデックス） • 特徴: ◦ テーブル自体のデータをインデックスの順序で並べ替える。 ◦ データの物理順序がインデックスに従うため、 読み取りパフォーマンスが向上。 • 使用頻度: ★★★☆☆（中程度） • 適用シナリオ: ◦ データが頻繁にソートされてアクセスされる場合（例 : 日付順の履歴データ）。 • 具体例: CREATE CLUSTERED INDEX idx_order_date ON orders(order_date);
11. 2.　Indexの種類 6. Full-Textインデックス • 特徴: ◦ テキストデータに対する全文検索を効率化。 ◦ トークン化やキーワードの検索をサポート。 • 注意点 ◦ FullTextIndexは通常のIndexと構造が異なるため 例えば複合Indexなど他のIndexと同時に使用することはできない。 • 使用頻度: ★★☆☆☆（低いが特定シナリオで有効） • 適用シナリオ: ◦ 商品説明やコメント欄など、長文テキストの検索。 • 具体例: CREATE FULLTEXT INDEX idx_product_description ON products(description);
12. 3.　Indexの構造イメージ（複合インデックス） 1. データ 2. Create Index 3. イメージ
13. 3.　Indexの構造イメージ（Composite Index） ID NAME AGE OTHER_INFO 1 A 20 User1 2 A 30 User2 3 F 25 User3 4 F 45 User4 5 N 22 User5 6 N 50 User6 7 T 35 User7 8 T 45 User8 1. データ
14. 3.　Indexの構造イメージ（Composite Index） 2. CREATE INDEX idx_users_name_age ON users(name, age);
15. 3.　Indexの構造イメージ（Composite Index） Root (M, 30) │ ┌────────┴────────┐ Branch (A~F, 年齢昇順) Branch (N~T, 年齢昇順) │ │ ┌────┴────┐ ┌────┴────┐ A (20~30) F (25~45) N (22~50) T (35~45) │ │ │ │ ┌─┴─┐ ┌─┴─┐ ┌─┴─┐ ┌─┴─┐ A, 20 A, 30 F, 25 F, 45 N, 22 N, 50 T, 35 T, 45 ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ User1 User2 User3 User4 User5 User6 User7 User8 ツリー構造上の基準点なのでデータとして存在しなく ても良い。 分岐最適化のための中央値が設定される。 ツリー構造の分割基準はデータに 依存し固定ではない
16. 4.　検証 • 検証対象のIndex ◦ 複合インデックス ▪ Indexに指定の全てのカラムを指定 ▪ Indexに指定の先頭のカラムのみ指定 ▪ Indexに指定の最後のカラムのみ指定 ◦ 部分インデックス ▪ Indexに指定の条件をそのまま使用 ▪ Indexに指定の条件を一部変更し使用 ▪ Indexに指定の条件を使用しない
17. 4.　検証 • 検証環境 ◦ データベース　Postgresql ◦ テーブル名　PEOPLE ◦ 統計情報とIndexは最新の状態 ◦ データ数　400万件
18. 4.　検証（複合インデックス） インデックスが使用されている。
19. 4.　検証（複合インデックス） インデックスの先頭カラムを指定してもインデックスが 適用されるが、 対象データが多い場合 Bitmap Heap Scan になり、 インデックス結果をバッファに保存して一括取得する。
20. 4.　検証（複合インデックス） インデックスは先頭カラムから順番に 使用されるため、 途中のカラムだけを指定するとイン デックスが使われない場合がある。
21. 4.　検証（部分インデックス） インデックスが使用されている。
22. 4.　検証（部分インデックス） 定義された条件と完全一致しないと適 用されないため、 条件を変えると効果がなくなる。
23. 4.　検証（部分インデックス） 定義された条件を 使用しないと適用 されない。
24. 4.　検証（結果） インデックスの種類 条件 結果 複合インデックス 全カラム指定 Index Scan 先頭カラム指定 BitMap Heap Scan 最後のカラム指定 インデックス未使用 部分インデックス 条件そのまま Index Scan 条件一部変更 インデックス未使用 条件なし インデックス未使用
25. 5.　まとめ • インデックスの適用条件を理解し、実行計画の確認が重要。 • 適切なインデックス設計は検索速度を向上させるが、 不適切なインデックスはかえって負荷になるため、 実行計画の確認が必須。 • 複合インデックスは全カラムや先頭カラムを指定すると 適用されやすいが、途中・最後のカラムのみでは適用されにくい。 • 部分インデックスは定義通りの条件でのみ適用され、 異なる条件や指定なしでは無効になる。