極端なシナリオでは、1回のクエリで読み取る行数はそれほど多くないものの、そのクエリのQPSが非常に高いため、全体として非常に大量のデータ読み取りが発生する場合があります。マシンのI/O能力が限られている場合、このような状況ではディスクI/Oがボトルネックになる可能性があります。
以下のシナリオを例に挙げます：
CREATE TABLE T1 (C1 INT, C2 INT, C3 INT, C4 INT, ... ,C16 INT);
CREATE INDEX IDX_C1 ON T1 (C1);
EXPLAIN SELECT C3, C4 FROM T1 WHERE C1 = 1;
| =========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|T1(IDX_C1)|4 |229 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([T1.C3], [T1.C4]), filter(nil),
access([T1.C3], [T1.C4]), partitions(p0)
上記のクエリでは、C1列のインデックスを使用することで最適なQuery Rangeが得られました。このクエリでは、IDX_C1上でスキャンされるデータ量は毎回ごくわずかです。また、このクエリではC3およびC4列の結果が必要ですが、IDX_C1にはこれら2列の値が保存されていないため、主テーブルへの再アクセスが必要になります。クエリ実行時には、IDX_C1上で少量の順序読み取りが行われ、主テーブル上でも少量のランダム読み取りが発生します。クエリのQPSが非常に高い場合、主テーブルに対して大量のランダム読み取りが発生し、システムのI/Oリソースを大幅に消費します。このようなシナリオに対しては、主テーブルへの再アクセスを回避するために、カバリングインデックスを作成するという追加の最適化方向を検討する必要があります。上記のクエリを例にとると、カバリングインデックスを作成することで最適化できます。
CREATE INDEX IDX_C1_C3_C4 ON T1 (C1, C3, C4);
EXPLAIN SELECT C3, C4 FROM T1 WHERE C1 = 1;
| ===============================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------
|0 |TABLE SCAN|T1(IDX_C1_C3_C4)|4 |46 |
===============================================
Outputs & filters:
-------------------------------------
0 - output([T1.C3], [T1.C4]), filter(nil),
access([T1.C3], [T1.C4]), partitions(p0)
このクエリでは、IDX_C1_C3_C4へのアクセスに最適なQuery Rangeがあり、主テーブルへの再アクセスも不要です。実際のクエリ実行時には、インデックスに対してわずかな順序スキャンのみが行われます。これにより、クエリによって発生するI/Oを大幅に削減できます。
一般的に、カバリングインデックスを作成する必要があるシナリオは、いくつかの特徴を満たす必要があります：
- データベースのI/O負荷が非常に高い
- 多量のI/Oは、いくつかの高頻度クエリによる主テーブルへの再アクセスによって引き起こされるランダムI/Oによるものです
- 高頻度クエリで読み取る必要がある列はそれほど多くありません
このような場合、これらの高頻度クエリに対して、カバリングインデックスを作成して最適化することができます。カバリングインデックスを利用してテーブルへの再アクセスを削減するシナリオは比較的少ないです。