クエリ最適化 (Query Optimization)

クエリ最適化 MOC

1. クエリ最適化入門 MOC

• クエリ最適化とは
  • クエリ最適化の定義 (与えられたクエリに対して最も効率的な実行方法を見つけ出すプロセス)
  • なぜクエリ最適化が重要か (アプリケーションの応答性、スループット、リソース消費に直結)
  • パフォーマンス問題のボトルネックとしてのデータベースクエリ
• クエリ処理の全体フロー
  • 1. 構文解析 (Parsing)
  • 2. セマンティック解析・バインディング (Semantic Analysis / Binding)
  • 3. クエリ書き換え (Query Rewriting)
  • 4. 最適化 (Optimization)
  • 5. 実行 (Execution)
• クエリのコストとは
  • コストの構成要素 (CPUコスト, I/Oコスト)
  • I/Oコストが支配的である理由
  • コストベースオプティマイザにおけるコストの推定値

2. クエリオプティマイザ (The Query Optimizer) MOC

• オプティマイザの役割 (多数の実行計画候補から最適なものを選択)
• オプティマイザの種類
  • ルールベースオプティマイザ (RBO - Rule-Based Optimizer) (ヒューリスティックベース、歴史的)
  • コストベースオDプティマイザ (CBO - Cost-Based Optimizer) (現代の主流)
    • CBOの動作原理 (コストモデルに基づく実行計画の評価)
• データベース統計情報 (Statistics) MOC
  • 統計情報の役割 (CBOによるコスト推定の基礎)
  • 収集される統計情報の種類
    • テーブルの行数、ページ数
    • 列のカーディナリティ (異なり値の数)
    • 列のNULL値の数
    • 列のヒストグラム (データ分布)
    • 相関統計
  • 統計情報の収集と更新 (ANALYZEコマンドなど)
  • 古い統計情報が引き起こす問題
• カーディナリティ推定 (Cardinality Estimation) MOC
  • カーディナリティ推定の重要性と難しさ
  • ヒストグラムを用いた推定
• 選択性 (Selectivity) MOC
  • 選択性の定義 (条件を満たす行の割合)
  • 選択性とインデックス利用の関係
• ヒント (Hints) の利用と注意点 MOC
  • ヒントとは (オプティマイザの決定を上書きする指示)
  • ヒントの利用シーン (オプティマイザの誤判断時、特殊なケース)
  • ヒントの乱用による弊害 (保守性低下、バージョンアップでの挙動変化)

3. 実行計画 (Execution Plan) MOC

3.1. 実行計画の基本 MOC

• 実行計画とは (クエリ実行の具体的な手順を示したツリー構造の計画)
• なぜ実行計画を読む必要があるのか (パフォーマンス問題の根本原因特定)
• 実行計画の取得方法
  • “EXPLAIN コマンド (推定実行計画)
  • “EXPLAIN ANALYZE コマンド (実測実行計画)
• 実行計画の読み方の基本
  • ノード (操作) のツリー構造
  • コスト (Cost) の見方 (起動コスト, 総コスト)
  • 推定行数 (Rows) と実測行数 (Actual Rows) の比較
  • ループ回数 (Loops)
  • 実行時間 (Actual Time)

3.2. 一般的な実行計画の操作 MOC

• スキャン操作 (Scan Operations)
  • シーケンシャルスキャン (Sequential Scan / Full Table Scan)
    • シーケンシャルスキャンが選択されるケース
  • インデックススキャン (Index Scan)
    • インデックススキャンが選択されるケース
  • インデックスオンリースキャン (Index-Only Scan) (カバリングインデックス)
  • ビットマップスキャン (Bitmap Scan - PostgreSQL)
    • ビットマップヒープスキャン (Bitmap Heap Scan) とビットマップインデックススキャン (Bitmap Index Scan)
• 結合操作 (Join Operations)
  • ネストループ結合 (Nested Loop Join)
    • ネストループ結合が選択されるケース (片方が小さいテーブル、インデックス利用時)
  • ハッシュ結合 (Hash Join)
    • ハッシュ結合の仕組み (ハッシュテーブル構築)
    • ハッシュ結合が選択されるケース (大規模テーブル同士、等価結合)
  • マージ結合 (Merge Join)
    • マージ結合の仕組み (ソート済みデータに対する結合)
    • マージ結合が選択されるケース
• 集計・ソート操作 (Aggregation and Sort Operations)
  • “GROUP BY の実現方法 (ハッシュ集計, ソート集計)
  • “ORDER BY の実現方法 (インデックス利用, メモリ/外部ソート)
  • “DISTINCT の実現方法
• その他の操作
  • “Filter (フィルタ)
  • “Limit (制限)
  • “Append/MergeAppend (UNION ALLなど)
  • CTE Scan (共通テーブル式)

4. インデックス戦略 (Indexing Strategies) MOC

4.1. インデックスの基本とアーキテクチャ MOC

• インデックスの役割 (検索パフォーマンスの劇的な向上)
• B-Treeインデックス (B木/B+木) MOC (最も一般的なインデックス構造)
  • B-Treeの構造と検索アルゴリズム
  • B-Treeが等価検索、範囲検索、ソートに有効な理由
• インデックスの利点と欠点 (検索速度 vs. 更新/挿入/削除のオーバーヘッド、ストレージ消費)

4.2. 効果的なインデックス設計 MOC

• インデックス候補列の選定
  • カーディナリティが高い列
  • WHERE句で頻繁に使用される列
  • JOIN句で使用される列
  • ORDER BY句で使用される列
• 複合インデックス (Composite Index) の設計 MOC
  • 列の順序の重要性 (最も絞り込める列を先頭に)
  • 複合インデックスが複数のクエリに利用されるケース
• カバリングインデックス (Covering Index) MOC
  • クエリに必要な全ての列をインデックスに含める
  • インデックスオンリースキャンを狙う
• 関数インデックス (Function-based Index - PostgreSQL) / 式インデックス
  • WHERE句で関数や式が使われている場合の最適化
• 部分インデックス (Partial Index - PostgreSQL)
  • テーブルの一部にのみインデックスを作成する

4.3. インデックスの利用を妨げるクエリの書き方 MOC

• インデックス列に関数を適用する (WHERE an_function(col) = ‘value’) (SARGableではない)
• インデックス列に演算を行う (WHERE col + 1 = 100)
• 否定形の使用 (<>, !=, NOT IN) (場合による)
• 中間一致・後方一致の LIKE (LIKE ‘%pattern%‘, LIKE ‘%pattern)
• 暗黙の型変換
• OR条件の多用 (ビットマップスキャンで解決される場合もある)

4.4. インデックスの種類と使い分け MOC

• B-Treeインデックス (再掲)
• ハッシュインデックス (等価検索のみ、高速)
• GIN (Generalized Inverted Index - PostgreSQL) (配列、JSONB、全文検索)
• GiST (Generalized Search Tree - PostgreSQL) (地理空間データ、全文検索)
• [[BRIN (Block Range Index - PostgreSQL)] (大規模な順序性のあるデータ)
• 全文検索インデックス (Full-text Index) (MySQL, SQL Server)
• 空間インデックス (Spatial Index)
• クラスタ化インデックス (Clustered Index) (SQL Server, InnoDB)

4.5. インデックスのメンテナンス MOC

• インデックスの断片化 (Fragmentation) と再構築 (REINDEX)
• 未使用インデックスの特定と削除
• 統計情報の更新 (ANALYZE)

5. SQLチューニングとアンチパターン MOC

5.1. WHERE句の最適化 MOC

• SARGableな述語の記述
• インデックスが効く条件の記述
• データ型の不一致を避ける

5.2. JOIN句の最適化 MOC

• JOINの順序 (オプティマイザが最適化するが、理解は重要)
• 適切なJOINタイプの選択
• JOINキーにインデックスを作成する
• JOINキーのデータ型を一致させる

5.3. サブクエリの最適化 MOC

• IN vs. EXISTS vs. JOIN
  • 相関サブクエリとパフォーマンス
  • NOT IN` vs. `NOT EXISTS` vs. `LEFT JOIN ... IS NULL
• 共通テーブル式 (CTE - WITH句) の活用
  • CTEのマテリアライズとパフォーマンスへの影響

5.4. GROUP BY と ORDER BY の最適化 MOC

• “GROUP BY でのインデックス利用
• “ORDER BY でのインデックス利用 (ソート処理の回避)
• “HAVING句の適切な利用

5.5. SELECT句の最適化 MOC

• SELECT * の回避
  • ネットワーク帯域の節約
  • カバリングインデックスの利用促進
  • 意図しない列の取得防止
• DISTINCT のコスト (ソートまたはハッシュ処理)
• UNION vs. UNION ALL

5.6. SQLアンチパターン MOC

• N+1問題 (ループ内での逐次クエリ発行)
• マジックナンバーとハードコードされた値
• 巨大なトランザクション
• カーソルの不適切な使用
• インデックスの貼りすぎ
• “SELECT * の乱用
• SARGableでないWHERE句
• 不適切なデータ型の選択 (例: 数値をVARCHARで保存)
• 集計結果をアプリケーション側で計算する
• 正規化の無視、または過度な非正規化

6. 結合アルゴリズム (Join Algorithms) の詳細 MOC

• ネストループ結合 (Nested Loop Join)
  • インデックス付きネストループ結合
• ハッシュ結合 (Hash Join)
  • ハッシュフェーズとプローブフェーズ
  • メモリ使用量と一時ファイルへのスピル
• マージ結合 (Merge Join)
  • ソートマージ結合
  • ソート処理のコスト
• 実行計画における結合アルゴリズムの選択理由の分析

7. 物理設計とクエリパフォーマンス MOC

• 正規化 vs. 非正規化のパフォーマンスへの影響 (再掲・詳細)
• データ型の選択がストレージとパフォーマンスに与える影響
• テーブルパーティショニングによるクエリパフォーマンス向上
• マテリアライズドビューによる集計クエリの高速化
• データベース設定パラメータのチューニング (バッファサイズなど) - 概要

8. データベースごとのクエリ最適化 MOC

• PostgreSQLのクエリ最適化 MOC
  • 豊富なインデックス種類 (GIN, GiST, BRIN) の活用
  • ビットマップスキャンの理解と活用
  • “VACUUMとANALYZE の重要性
  • プランナヒント (pg_hint_plan)
  • JITコンパイル
• MySQLのクエリ最適化 MOC
  • ストレージエンジン (InnoDB, MyISAM) とパフォーマンス
  • インデックスヒント (USE INDEX, FORCE INDEX)
  • “EXPLAIN の読み方 (MySQL版)
  • クエリキャッシュ (非推奨・削除済み)
  • 複合インデックスとカバリングインデックスの重要性
• (オプション) Oracle Databaseのクエリ最適化 (ヒント、統計情報、オプティマイザモード)
• (オプション) SQL Serverのクエリ最適化 (インデックス、統計情報、クエリストア)

9. クエリ最適化の実践 MOC

• スロークエリの特定方法
  • スロークエリログの有効化と分析
  • データベースモニタリングツール
• 問題クエリの分析とチューニングのサイクル
  • 1. 問題の特定
  • 2. 現状の測定 (EXPLAIN ANALYZE)
  • 3. 仮説立案と改善策の検討 (SQL書き換え, インデックス追加など)
  • 4. 改善策の適用と再測定
  • 5. 効果測定と比較
• ツールの活用
  • データベース付属のモニタリング/分析ツール (pg_stat_statementsなど)
  • SaaS型DBパフォーマンスモニタリングツール (Datadog, New Relic, SolarWinds DPAなど)
• 継続的なパフォーマンスチューニングの文化醸成
  • コードレビューでのクエリチェック
  • 負荷テストとパフォーマンステスト