インデックスを使用してテーブルスキャンを実行することは、データベース内のデータにアクセスするための2つの基本的なアプローチであり、それぞれが状況に応じて異なるトレードオフを備えています。
データベースのインデックスは、テーブル内のすべての行をスキャンせずにシステムが迅速に行を見つけることができるようにすることで、より速いルックアップを可能にするデータ構造です。ほとんどのリレーショナルデータベースは、ツリー形式でキーとポインターを整理するインデックスにB+ツリー構造を使用します。これにより、対数時間の複雑さ$$ o(\ log n)$$の検索、挿入、および削除が可能になります。これは、通常、$$ o(n)$$の複雑さでテーブル全体をスキャンするよりもはるかに高速です。インデックスはクラスター化または非クラスター化され、クラスター化されたインデックスは物理的に整理された順序でデータを保存し、データ変更で追加のオーバーヘッドをコストで範囲スキャンパフォーマンスを改善します。インデックスは、特定のクエリパターン用に調整された複合、部分、フィルター、またはハッシュベースにすることもできます。
対照的に、テーブルスキャン(またはフルテーブルスキャン)は、クエリの選択性に関係なく、テーブル内のすべての行を順番に読み取ります。これには、テーブルのすべてのデータブロックをスキャンすることが含まれ、多くの場合、必要以上のデータを処理するため、最も高価なアクセス方法と見なされます。ただし、テーブルスキャンは特定の場合にはうまく機能します。たとえば、クエリが行の大部分を取得すると、インデックスを使用するオーバーヘッド(実際の行に追加の検索が必要なことが多い)は、テーブル全体を1回スキャンするコストを超える可能性があります。テーブルスキャンでは、マルチブロック読み取りを利用できます。これにより、I/O操作が少ないデータの大量のデータを読み取ることができるため、インデックススキャンでランダムに必要な多くの個々のブロックを読むことと比較して遅延が軽減されます。
1つの主要なトレードオフには、クエリによって返されるデータセットの選択性とサイズが含まれます。クエリが少数の行(選択性の高い)にフィルターをかけた場合、インデックスは一般に、関連データにアクセスする必要があるため、テーブルスキャンを上回ります。しかし、返される行の割合が増加すると、複数のキールックアップが必要になる可能性があるため、インデックススキャンのコストが上昇し、データベースエンジンは追加のランダムI/O操作を実行する必要があります。ある程度のしきい値では、多くの場合、テーブルの行の約10〜20％ですが、データの幅とハードウェアに依存して、フルテーブルスキャンがより効率的になります。これは、選択性に関係なくスキャンコストが一定のままであり、テーブルを一度順次読み取るだけだからです。
通常、インデックススキャンは、覆われた列がフルテーブルの行よりもコンパクトになっていない場合、テーブルスキャンよりも少ないページを読み取ります。たとえば、インデックスには、フルテーブルの行データのないインデックス付き列のみが含まれる場合があり、それを薄くし、各データベースページにもっと多くの行を適合させることができます。これにより、テーブルデータ全体のスキャンと比較して、インデックスをスキャンするときにI/Oオーバーヘッドが減少します。さらに、一部のインデックスをフィルタリング(部分インデックス)して、無関係な行を除外して、スキャンフットプリントをさらに削減できます。
一方、完全なテーブルスキャンは、データベースのメンテナンス側の負担を少なくします。インデックスは、挿入、更新、削除などのデータ変更操作中にオーバーヘッドを導入します。テーブルに変更するたびにインデックスを更新する必要があり、特にテーブルに多くのインデックスが存在する場合、書き込み遅延とストレージのオーバーヘッドが増加する場合があります。このオーバーヘッドは、同時性にも影響を与え、重い書き込み環境での競合につながる可能性があります。したがって、テーブルスキャンは、追加の構造メンテナンスなしで自然な順序でデータを単に読み取るだけで、このコストを避けます。
もう1つの重要な考慮事項は、キャッシュとハードウェアの特性の効果です。テーブルスキャンは、シーケンシャルI/Oとプリフェッチの恩恵を受け、システムが複数の連続ブロックを効率的に読み取ることができます。逆に、特にインデックススキャンが行のポインターをヒープストレージに調べる必要がある場合、さまざまなデータブロックを取得するためにランダムなI/Oが発生するインデックススキャンが発生します。これにより、SSDと大きなメモリプールがこのギャップを狭めるものの、ディスクランダムI/Oパフォーマンスが遅いシステムでのインデックススキャンが遅くなります。状況は、データベースエンジンの並列性やマルチスレッド機能などの詳細にも依存する場合があります。ここでは、並列テーブルスキャンがスループットを大幅に高めることができます。
さらに、内部断片化と物理ストレージレイアウトは、パフォーマンスのトレードオフに影響します。ヒープ編成されたテーブルのテーブルスキャンは、更新のために行が異なるページに移動し、スキャン効率が悪化しているため、転送されたレコードに悩まされる可能性があります。キーによってソートされたデータを保存するクラスター化されたインデックスは、この問題を回避し、クラスター化されたインデックススキャンに相当する「テーブルスキャン」を時々行うことがあります。ただし、その利点は、重いデータの解約中に高価なrow順のコストがかかることに伴います。
クエリオプティマイザーの観点から、インデックススキャンとテーブルスキャンの決定は、通常、データ分布、行数、ハードウェアコストに関する統計を考慮して、コストベースの推定モデルによって行われます。 Optimizerは、CPU、I/O、およびメモリコストのバランスを取り、最も効率的なアクセスパスを選択します。これらの決定は、利用可能なメモリ、キャッシュ状態、クエリパターンなどの要因の影響を受ける可能性があります。どちらか一方を使用する時期の間に固定しきい値はありません。クロスオーバーポイントは、システムとワークロードごとに異なります。
要約すると、インデックスとテーブルスキャンの使用との間のトレードオフには、次のものが含まれます。
- パフォーマンスとデータのボリューム：インデックスは、少数の行にフィルタリングするときにアウトパフォームします。テーブルスキャンは、大規模なデータ検索に適しています。
-I/Oパターン：インデックススキャンはランダムI/Oリードを引き起こします。テーブルスキャンは、シーケンシャルI/Oおよびマルチブロック読み取りの恩恵を受けます。
- メンテナンスオーバーヘッド：インデックスのインデックスは、インデックス構造の更新により、書き込み操作コストを増加させます。テーブルスキャンではこれが発生しません。
- ストレージ効率：列をより少ない列をカバーすることにより、インデックスをコンパクトにすることができます。テーブルスキャンは、完全な行と潜在的に多くのデータを処理します。
- キャッシング効果：テーブルスキャンは、特に大規模な順次読み取りでデータキャッシュを効果的に利用できます。インデックススキャンは、ランダムアクセスのためにそれほど利益を得ない場合があります。
- オプティマイザーの決定：コストベースのクエリオプティマイザーは、クエリの統計とワークロードの詳細に基づいて、これらのオプションを動的に選択します。
- データレイアウトの影響：ヒープテーブルは、スキャン中に転送されたレコードのようなペナルティが発生する場合があります。クラスター化されたインデックスは、データを物理的に編成しますが、更新コストを増やします。