インデックススキャンとテーブルスキャン間のI/O操作のコストは、データへのアクセス方法、読み取りページ数、クエリの選択性とデータ組織に基づく効率の点で根本的に異なります。
インデックススキャンには、インデックス構造(多くの場合Bツリー)を通過することにより、データへのアクセスが含まれます。ここでのコストには、主に2つのコンポーネントが含まれます。インデックスページにアクセスするコストと、対応するテーブルページを取得するコストです。インデックスページには、インデックスリーフノードがディスク上で物理的に隣接していないため、ランダムI/O操作につながるため、インデックスページには通常、ランダムアクセスパターンがあります。各論理的なステップは、ルートからリーフまでインデックスツリーを下に下げて、ページを読む必要があります。このコストは、システムのランダムページコストパラメーターを使用して推定されます。関連する行のインデックスエントリを見つけた後、システムは対応するテーブルデータページを取得します。このようなデータページフェッチの数は、クエリの選択性と、保存されたデータとインデックス順序(相関と呼ばれる)の物理的相関に依存します。高い相関は、インデックスエントリの行の場所が物理データの順序に密接に一致し、ランダムI/Oを減らし、インデックススキャンをより効率的にすることを意味します。一方、相関が低いと多くのランダムフェッチが発生し、I/Oコストが大幅に増加します。
インデックススキャンのCPUコストには、取得された個々の行の処理が含まれますが、主なコストの懸念はI/Oです。可視性の側面もあります。データベースにすべてのトランザクションに完全に表示されるデータページの可視性マップトラッキングがある場合、システムはインデックスのみのスキャン中に特定のページの読み取りを完全にスキップして、I/Oコストを大幅に削減できます。
対照的に、テーブルスキャンまたはフルテーブルスキャンは、すべてのデータページをテーブルから順番に読み取ることで動作します。ここのI/Oはほとんど順番であり、スピニングディスクではランダムI/Oよりも高速になる傾向があります。フルテーブルスキャンのコストは、クエリを満たす行の数に関係なくテーブル全体を読み取るため、比較的一定です。このコストは、選択した行の数ではなく、テーブル内のページの総数に依存します。完全なテーブルスキャンは、選択性の恩恵を受けません。クエリが行を大きくろ過する場合でも、すべてのページを読む必要があります。ただし、マルチブロック読み取り、並列読み取り、ストレージレイヤーオフロードなどの最新のストレージテクノロジーと最適化により、完全なテーブルスキャンのコストは競争力があるか、テーブルのかなりの部分を取得するクエリのインデックススキャンよりも少ない場合があります。
コストベースのオプティマイザー(CBO)は、推定コストに基づいてインデックススキャンとフルテーブルスキャンの使用との間で決定します。非常に選択的なクエリの場合、テーブルページの取得が少ないため、列のほんの一部のみを取得する必要がある場合は、I/Oコストが低い傾向があります。選択性のしきい値が増加すると(必要な行が増える)、ランダムなページフェッチとインデックストラバーサルの数が多いため、インデックススキャンのI/Oコストが上昇し、最終的には完全なテーブルスキャンのコストを上回ります。この時点で、CBOは、シーケンシャルI/Oコストが多くのインデックス検索のランダムなI/Oの負担よりも少ないため、テーブルスキャンを好みます。
I/Oコストの違いに影響を与えるもう1つの重要な要因は、「クラスタリング係数」またはインデックスキーに対応する行の物理的クラスタリングです。クラスタリング係数(より良いクラスタリング)は、インデックスを介してアクセスされる行が物理的に互いに近くに配置され、ランダムI/Oが減少し、インデックススキャン効率を改善することを意味します。クラスタリング因子が高いと、インデックススキャン中にランダムI/Oがよりランダムになり、テーブルスキャンと比較して利点が減少します。
最新のデータベースシステムは、I/OおよびCPUリソースが複数の労働者間で共有される並列スキャンとフルテーブルスキャンの両方を実装することもでき、合計クエリ時間を短縮できます。ただし、インデックススキャンのI/Oの基本性(ランダムページアクセス)対テーブルスキャン(主にシーケンシャルページアクセス)は、重要な差別化要因のままです。
I/Oコストの違いに関する重要なポイントを要約するには：
- インデックススキャンは、インデックスページ(ランダムアクセス)を読み取ることからI/Oコストに加えて、対応するデータページ(潜在的にランダムアクセス)を取得します。コストは、選択性、相関、クラスタリング因子に敏感です。
- 完全なテーブルスキャンは、選択性に関係なく比較的安定したI/Oコストで、すべてのページを読み取る連続I/Oを実行します。
- データの小さなサブセットをターゲットにすると、インデックススキャンがアウトパフォームしますが、選択性が上昇するにつれてランダムI/Oオーバーヘッドに悩まされます。
- 効率的なシーケンシャルI/Oのために、テーブルの大部分がアクセスする必要がある場合、完全なテーブルスキャンはより効率的になります。
- 可視性マップとインデックスのみのスキャンは、データページの読み取りを回避することにより、一部のインデックススキャンケースでI/Oコストを削減できます。
- 並列処理は両方のスキャンタイプを改善できますが、基本的なI/O特性を変更しません。
- オプティマイザーは、これらのコスト要因のバランスを取り、クエリとデータの特性に基づいて最も効率的な方法を選択します。