synapse

クエリ最適化 (Query Optimization)

11 min read

クエリ最適化 MOC

1. クエリ最適化入門 MOC

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

3. 実行計画 (Execution Plan) MOC

3.1. 実行計画の基本 MOC

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

  • スキャン操作 (Scan Operations)
  • 結合操作 (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+木) MOC (最も一般的なインデックス構造)
    • [[B-Treeの構造と検索アルゴリズム]]
    • [[B-Treeが等価検索、範囲検索、ソートに有効な理由]]
  • インデックスの利点と欠点 (検索速度 vs. 更新/挿入/削除のオーバーヘッド、ストレージ消費)

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

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 INvs.NOT EXISTSvs.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

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

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

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

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

グラフビュー

バックリンク