メインコンテンツへスキップ
見出し画像

SQL実行計画とオプティマイザの仕組みを図解 | スキャン・結合・コスト・統計情報まで

    はじめに

    「SQLは書けるけれど、DBの中でどう実行されているかはわからない」
    「フルテーブルスキャンとインデックススキャンは、何が違うの?」「Nested LoopやHash Join、Full Scanって何?」

    そんな方に向けて、今回はSQLの実行計画とオプティマイザの仕組みを、図解を交えて解説します。同じ結果を返すSQLでも、データベース内部では複数の実行方法が考えられます。そして、どの方法が選ばれるかによって、処理速度やリソース消費量が大きく変わることがあります。

    この記事では、Oracle DatabaseやPostgreSQL、MySQLなど、特定製品の独自機能よりも、RDBMS全般に共通する考え方を中心に整理します。

    主に次の内容を扱います。

    • SQL実行計画とは何か

    • オプティマイザは何を基準に実行方法を選ぶのか

    • フルテーブルスキャンとインデックススキャンの違い

    • Nested Loop・Hash Join・Merge Joinの仕組み

    • コスト・統計情報・カーディナリティの意味

    • 実行計画の基本的な読み方

    応用情報技術者試験やデータベーススペシャリスト試験の学習に加えて、データベースの内部動作を理解したいエンジニアにもおすすめです。なお、SQLの書き換えやインデックス設計など、具体的なSQLチューニング技法については別記事で解説します。


    1. SQL実行計画とは?

    SQLの大きな特徴は、「何を取得したいか」は指定しますが、「どうやって取得するか」は基本的に指定しないことです。

    例えば、次のSQLを考えてみましょう。

    SELECT *
    FROM employees
    WHERE department_id = 10;

    このSQLは「部署IDが10の従業員を取得する」という要求を表しています。しかし、実際のデータの探し方までは指定していません。

    データベースには、例えば次のような選択肢があります。

    • テーブルの全行を順番に読み取り、条件に一致する行を探す

    • インデックスを使って対象行の位置を特定する

    • 複数のインデックスなどを組み合わせて対象行を探す

    こうした処理方法を具体的に組み合わせたものが、実行計画(Execution Plan)です。実行計画は、いわば「SQLを実行するための作業手順書」です。

    SQLの記述順序と実際の処理順序は違う

    SQLには、SELECT、FROM、WHERE、GROUP BY、ORDER BYなど、さまざまな句があります。しかし、SQLに書かれた順序が、そのまま物理的なデータ処理の順序になるわけではありません。

    特に複数のテーブルを結合するSQLでは、オプティマイザが結合順序やデータの読み取り方法を検討します。つまり、SQLの構文を読むことと、実際の実行手順を理解することは別の話なのです。


    2. オプティマイザとは? データベースの頭脳を理解する

    実行計画を決定する重要な役割を持つのが、オプティマイザ(Optimizer)です。オプティマイザは、SQLに対して考えられる実行方法の候補を評価し、効率がよいと予測される計画を選択する仕組みです。

    SQLが実行されるまでの流れ

    一般的な流れを単純化すると、以下のようになります。

    1. SQLを受け付け、構文や参照先を解析する

    2. SQLを論理的な処理として表現し、必要に応じて変換する

    3. テーブルへのアクセス方法や結合順序など、複数の候補を検討する

    4. 各候補の処理コストを見積もり、実行計画を選択する

    5. 実行エンジンが計画に従ってデータを処理する

    画像

    ここで覚えておきたいのが、オプティマイザは実際にすべての処理を試して比較しているわけではないということです。処理に必要なリソースを推定し、効率的と思われる実行計画を選択しています。

    コストベースオプティマイザ(CBO)

    現代の主要なRDBMSでは、処理コストを基準として実行計画を評価するコストベース最適化が広く採用されています。

    代表的な判断材料は次のとおりです。

    • テーブルの行数やデータ量

    • インデックスの有無や構造

    • 条件に一致すると予測される行数

    • データの値の分布

    • ディスクI/OやCPU処理の推定負荷

    • ソートやハッシュ表の作成に必要な処理

    これらをもとに、各実行計画の推定コストを比較します。ただし、実際のオプティマイザはすべての候補を総当たりで調べるとは限りません。探索時間にも制約があるため、効率的なアルゴリズムを使って候補を絞り込みます。

    コストは実行時間そのものではない

    実行計画には、Costなどの推定値が表示される場合があります。ここで注意したいのが、コストが100だから100ミリ秒で実行される、という意味ではないことです。コストは製品ごとのモデルに基づく推定値であり、実行時間そのものではありません。



    3. 代表的なスキャン方式を理解する

    実行計画の基本となるのが、テーブルからデータを読み取る「スキャン」です。まずは代表的なスキャン方式を理解しましょう。

    3-1. フルテーブルスキャン(Full Table Scan)

    フルテーブルスキャンは、テーブル全体を読み取って必要な行を探す方式です。製品によっては、Table ScanやSequential Scan、Seq Scanなどと表示されます。

    例えば、10万件のデータが格納された従業員テーブルから、部署IDが10の行を探す場面を考えてみましょう。フルテーブルスキャンでは、テーブル全体を読み取り、各行が条件に一致するか確認します。※10万件は説明用の仮定です。

    フルテーブルスキャンには、次のような特徴があります。

    • インデックスを必要としない

    • テーブル全体を順次読み取れる

    • 多くの行を取得する場合に効率的なことがある

    • 少数の行しか必要ない場合には、余分な読み取りが多くなることがある

    「フルスキャン=悪い実行計画」と覚えるのは間違いです。例えば、テーブルの大部分の行を取得する場合、インデックス経由で多数の行にアクセスするよりも、テーブル全体を順番に読む方が効率的なことがあります。

    3-2. インデックススキャン(Index Scan)

    インデックススキャンは、インデックスを利用して対象データを探す方式です。インデックスは、書籍の巻末にある索引のようなものです。本の内容を最初から読み進めなくても、索引を使えば目的のページを探せます。

    RDBMSでも同様に、インデックスを利用して必要なデータの位置や範囲を特定できます。例えば、従業員テーブルに部署IDのインデックスがあれば、部署IDが10の行を効率的に探せる可能性があります。

    ただし、インデックススキャンは必ずしも高速とは限りません。対象行が大量に存在する場合には、インデックスをたどる処理やテーブル本体へのアクセスにかかる負荷が大きくなることがあります。そのため、インデックスが存在していても、オプティマイザがフルテーブルスキャンを選ぶことがあります。

    画像

    3-3. インデックス範囲スキャン(Index Range Scan)

    インデックスを使って、指定した範囲のデータを取得する方式です。
    例えば、次のようなSQLが該当します。

    SELECT *
    FROM sales
    WHERE sale_date
      BETWEEN '2026-01-01' AND '2026-01-31';

    日付順に並ぶB-treeインデックスが利用できる場合、対象範囲を特定して、その範囲内の索引項目をたどることができます。ただし、実際に利用されるアクセス方法は、索引構造やデータ分布、取得する列などによって異なります。また、Range Scanという名称がそのまますべての製品の実行計画に表示されるわけではありません。

    3-4. インデックスオンリースキャン(Index Only Scan)

    通常のインデックススキャンでは、インデックスを利用して対象行を探した後、必要なデータをテーブル本体から取得することがあります。

    一方、必要な列をインデックス側から取得できる場合、テーブル本体へのアクセスを減らせることがあります。これがインデックスオンリースキャンや、それに相当するアクセス方式です。

    3-5. ビットマップを利用したスキャン

    一部のRDBMSには、インデックスから該当行の位置情報を集め、それを利用してテーブルを効率的に読み取る方式があります。PostgreSQLのBitmap Index Scan、Bitmap Heap Scanなどが代表例です。

    特に複数の条件に一致する行を取得する場面などで利用されることがあります。ただし、ビットマップを使った実行方式の実装や名称は製品によって異なります。

    画像

    試験対策では、まずフルテーブルスキャンとインデックススキャンの違いを確実に理解することが重要です。


    4. 代表的な3つの結合方式

    複数のテーブルを結合する場合、データベースにはいくつかの結合アルゴリズムがあります。特に覚えておきたいのが、次の3種類です。

    1. Nested Loop Join

    2. Hash Join

    3. Merge Join(Sort-Merge Join)

    なお、これらはRDBMSで広く知られる代表的な方式ですが、すべての製品がすべての方式を実装しているわけではありません。

    4-1. Nested Loop Join(ネステッドループ結合)

    Nested Loop Joinは、外側のテーブルから1行ずつ取り出し、内側のテーブルから一致する行を探す方式です。名前のとおり、ループ処理を入れ子にしたような仕組みです。

    処理の流れは次のとおりです。

    1. 外側テーブルから1行を取り出す

    2. 内側テーブルから結合条件に一致する行を探す

    3. 一致したデータを結合する

    4. 外側テーブルの次の行について同じ処理を繰り返す

    画像

    例えば、顧客テーブルと注文テーブルを結合する場面を考えます。顧客テーブルから1件の顧客を取り出し、その顧客IDに対応する注文を探す、という処理を繰り返すイメージです。

    Nested Loop Joinは、外側のテーブルから取得する行数が少ない場合や、内側を効率的に検索できる場合に有力な選択肢となります。特に、内側テーブルの結合キーに利用可能なインデックスがあれば、毎回の検索を効率的に行える可能性があります。

    一方、外側の行数が非常に多く、内側の探索にも大きな負荷がかかる場合には、処理量が増えやすくなります。

    覚え方:外側の1行ごとに、内側を探す。

    4-2. Hash Join(ハッシュ結合)

    Hash Joinは、片方の入力データを使ってハッシュ表を作成し、もう片方の入力データと照合する方式です。基本的な処理は、次の2段階です。

    ① Build(構築)
    片方の入力データを読み取り、結合キーを使ってハッシュ表を作成します。

    ② Probe(照合)
    もう片方の入力データを読み取り、ハッシュ表から結合キーが一致する行を探します。

    画像

    Hash Joinは、等値条件による結合で利用される代表的な方式です。大きなデータ集合を結合する際に有力な選択肢になることがあります。一方、ハッシュ表を作成するためのメモリが必要です。十分なメモリが確保できない場合は、ディスクへの一時退避などが発生することもあります。なお、一般的なHash Joinでは、単にハッシュ値が等しいことだけで結合結果を確定するわけではなく、必要な結合条件の確認も行います。

    覚え方:ハッシュ表を作り、キーで照合する。

    4-3. Merge Join(ソートマージ結合)

    Merge Joinは、両方の入力データが結合キー順に並んでいることを利用して、順番に照合する方式です。

    例えば、次のデータを結合する場合を考えましょう。

    • テーブルA:10、20、30、40

    • テーブルB:10、25、30、50

    どちらも結合キーの昇順に並んでいます。先頭から順番に比較すると、10と30が一致します。このように、整列済みのデータを順序に沿って照合していくのがMerge Joinです。

    画像

    Merge Joinは、両入力がすでに結合キー順で取得できる場合などに有力な方式です。逆に、結合のために大規模なソートが必要になる場合には、その準備コストも考慮する必要があります。また、同じキーを持つ行が複数存在する場合には、単純に1行ずつ進めるだけではなく、重複キーの組合せも適切に処理します。

    覚え方:並べてから順番に突き合わせる。


    5. なぜ結合方式が変わるのか?

    ここまで3種類の結合方式を紹介しました。では、オプティマイザはどうやって使い分けているのでしょうか。

    例えば、次の2つの状況を考えてみましょう。

    ケースA:外側のテーブルから数件だけ取得する

    顧客テーブルから条件に一致する数件の顧客を取り出し、注文テーブルと結合するとします。注文テーブルの顧客IDを効率的に検索できる場合には、Nested Loop Joinが有利になる可能性があります。

    ケースB:大量のデータ同士を結合する

    今度は、非常に多くの顧客と注文を結合するとします。外側の行数が大幅に増えれば、Nested Loop Joinで繰り返す内側の探索回数も増える可能性があります。このような状況では、Hash Joinなどの方が有利と判断されることがあります。

    重要なのは、SQLの書き方だけでなく、実際のデータ量や分布によって適切な実行方法が変わるということです。そして、その判断を行うための重要な情報が、次に説明する統計情報です。


    6. オプティマイザを理解するための重要用語

    6-1. カーディナリティ(Cardinality)

    実行計画で重要な意味を持つのが、カーディナリティです。

    データベースの文脈では、使われる場面によって意味が異なりますが、実行計画の説明では、ある処理から出力される行数、あるいはその推定行数を指すことが一般的です。

    例えば、10万行のテーブルから条件に一致する行が600行と予測された場合、フィルタ後の推定行数は600行です。この推定行数は、後続の結合方式やソート処理のコスト見積もりに大きく影響します。

    画像

    6-2. 選択率(Selectivity)

    選択率は、条件に一致する行の割合を表す指標です。

    例えば、全体が10万行で、条件に一致する行が600行だった場合、
    選択率 = 600/100000 = 0.6% となります。

    選択率が小さい場合、少数の行だけを効率的に取得するアクセス方法が有利になることがあります。ただし、選択率だけで最適なアクセス方法が決定するわけではありません。

    画像

    6-3. 統計情報(Statistics)

    オプティマイザが実行計画を選択する際には、テーブルや列に関する統計情報が利用されます。代表例として、次の情報があります。

    • テーブルの行数

    • データの値の分布

    • 値の出現頻度

    • 列の異なる値の数

    • NULLの割合

    • データのヒストグラム

    例えば、部署IDが1〜100まで均等に分布しているテーブルと、特定の部署IDに大半の行が集中しているテーブルでは、同じ条件で検索しても結果件数が大きく異なります。こうした違いを推定するために、統計情報が重要になります。

    画像
    画像

    6-4. 推定行数と実際の行数

    オプティマイザの推定が、実際のデータ量と一致するとは限りません。
    例えば、ある処理の推定行数が600行だったにもかかわらず、実際には10万行が取得されたとします。※数値は説明用の仮定です。

    この場合、後続の結合や集約に必要な処理量を大きく見誤っている可能性があります。推定と実際の行数が乖離する主な理由には、次のようなものがあります。

    • 統計情報が古い

    • 特定の値にデータが偏っている

    • 複数の列の間に相関がある

    • 条件が複雑で行数を推定しにくい

    そのため、オプティマイザが選んだ実行計画が、実際にも最適とは限りません。

    画像

    6-5. I/OコストとCPUコスト

    データベースの実行処理には、複数の種類の負荷が発生します。

    例えば、ディスクやメモリからデータを読み取る負荷、結合条件を評価するCPU処理、ソートやハッシュ表を構築する処理などです。オプティマイザは製品ごとのコストモデルを利用して、こうした処理負荷を推定します。なお、キャッシュ、並列実行、メモリの利用状況などによって、実際の処理時間は変わることがあります。


    7. 実行計画の基本的な読み方

    ここからは、実行計画を具体的にイメージしてみましょう。

    次のSQLを考えます。

    SELECT
        c.customer_name,
        COUNT(*) AS order_count
    FROM customers c
    JOIN orders o
        ON c.customer_id = o.customer_id
    WHERE c.region = '関東'
    GROUP BY c.customer_name
    ORDER BY order_count DESC;

    これは、関東の顧客について、顧客名ごとの注文件数を集計するSQLです。

    このSQLに対する実行計画の一例を、概念的に表すと次のようになります。

    Sort
      |
    Aggregate
      |
    Hash Join
      |--- Table Scan (customers)
      |       条件:region = '関東'
      |
      |--- Table Scan (orders)

    ※これは理解のための模式的な実行計画です。特定RDBMSで得られた実測結果ではありません。実際の計画は、データ量や製品によって異なります。

    画像

    この例では、次の処理が行われます。

    1. 顧客テーブルと注文テーブルから必要なデータを読み取る

    2. 顧客テーブルについて地域条件を評価する

    3. 顧客IDをキーにHash Joinで結合する

    4. 顧客名ごとに注文件数を集計する

    5. 注文件数の降順に並べ替える

    実行計画は、このように複数の処理を組み合わせた木構造として表現されることが一般的です。

    実行計画はどこから読む?

    木構造の実行計画を読む際には、末端のスキャン処理から、上位の結合・集約・ソート処理へと、データの流れを追うと理解しやすくなります。

    ただし、表示上の上下や左右、子ノードの処理順序はRDBMSや表示形式によって異なります。特に、すべての処理が必ず一度に完了してから次の処理に進むわけではなく、行を逐次渡しながら処理される演算子もあります。

    また、実行計画には見積コスト、推定行数、実行時間、ループ回数などが表示されることがあります。まずは各ノードが「何をしているのか」を理解し、続いて推定行数と実際の行数の違いを確認できるようになることが大切です。

    EXPLAINとは?

    多くのRDBMSでは、実行計画を確認するための機能が用意されています。例えば、PostgreSQLやMySQL、SQLiteでは、EXPLAINまたは関連するコマンドを利用できます。SQL Serverにも実行計画を表示する機能があります。

    ただし、表示形式や取得コマンド、計測できる項目は製品ごとに異なります。特に注意したいのが、実際の実行統計を取得する機能では、SQLが本当に実行されることがあるという点です。本番環境で確認する場合は、その製品の仕様とSQLの副作用を理解しておく必要があります。


    8. 初学者が誤解しやすいポイント

    Q1. フルテーブルスキャンは必ず遅い?

    いいえ。
    テーブルの大部分を取得する場合や、テーブルが小さい場合などには、効率的な選択となることがあります。

    Q2. インデックスがあれば必ず使われる?

    いいえ。
    オプティマイザはインデックスを利用する場合と利用しない場合のコストを比較し、フルテーブルスキャンなどを選ぶことがあります。

    Q3. Nested Loop JoinはHash Joinより遅い?

    一概にはいえません。
    外側の行数が少なく、内側を効率的に検索できる場合には、Nested Loop Joinが有利になることがあります。

    Q4. 同じSQLなら、いつも同じ実行計画になる?

    必ずしもそうではありません。
    統計情報、データ量、パラメータ、インデックス、実行環境などによって、実行計画が変わる場合があります。一方、製品によっては実行計画がキャッシュされ、再利用される仕組みもあります。

    Q5. コストが小さい実行計画なら必ず高速?

    いいえ。
    コストはあくまで推定値です。統計情報の不正確さや実行時の環境の違いなどによって、推定と実際の性能が一致しないことがあります。


    9. 理解度チェック! 3問で確認

    問題1

    大量の行が格納されたテーブルから、全体の大部分のデータを取得する場合、フルテーブルスキャンとインデックススキャンのどちらが有利になる可能性があるでしょうか。

    正解:フルテーブルスキャン

    データの大部分を読み取る場合、インデックス経由で多くの行を探すよりも、全体を順次読み取る方が効率的になることがあります。

    問題2

    片方の入力データからハッシュ表を作成し、もう片方の入力データを照合する結合方式は何でしょうか。

    正解:Hash Join

    BuildとProbeという2段階の考え方を理解しましょう。

    問題3

    オプティマイザが実行計画を選択する際に使用する、テーブルの行数やデータ分布などの情報を何と呼ぶでしょうか。

    正解:統計情報

    統計情報は、カーディナリティや処理コストを推定する重要な材料です。


    10. まとめ|SQL実行計画を理解するための重要ポイント

    最後に、この記事の重要ポイントを振り返ります。

    • 実行計画:SQLをどのような手順で処理するかを表したもの

    • オプティマイザ:複数の候補から、効率的と予測される実行計画を選ぶ仕組み

    • フルテーブルスキャン:テーブル全体を読み取る方式

    • インデックススキャン:インデックスを利用して必要なデータを探す方式

    • Nested Loop Join:外側の行ごとに内側のデータを探す

    • Hash Join:ハッシュ表を作成してデータを照合する

    • Merge Join:結合キー順に並んだデータを順番に照合する

    • カーディナリティ:実行計画では、処理の出力行数やその推定行数を表す

    • 統計情報:行数や値の分布など、コスト見積もりの基礎になる情報

    特に重要なのは、実行計画に絶対的な正解があるわけではなく、データ量や分布によって適切な処理方式が変わるという考え方です。SQL実行計画の基本が理解できると、「なぜこのアクセス方式が選ばれたのか」「なぜこの結合方式が使われるのか」を考えられるようになります。

    さらに理解を深めたい方は、次のテーマであるSQLチューニングの定石・典型パターンも併せて学ぶと、実行計画の知識を実務で活用できるようになります。


    関連記事


    参考資料


     
     
    IT資格の勉強法・合格ノウハウを発信。 書籍では追いにくい最新技術や最新動向まで。 試験対策だけでなく実務にも活きる知識をお届け。 【保有資格】 ・IPA:FE、AP、SC ・DB:OracleDB-Gold、OSSDB-Gold ・Cloud:AWS-SAP、OCI-AP

    あなたへのおすすめ