一般的に、インデックススキャンの結果はインデックス列に対して順序付けられています。例えば、インデックス IDX(C1, C2, C3) を指定した場合、そのインデックスをスキャンした結果は C1, C2, C3 の順になります。このような順序の性質は、ORDER BY を含むクエリの最適化に利用できます。以下のクエリを例に、インデックスを構築して最適化する方法について説明します。
CREATE TABLE T1 (C1 INT, C2 INT, C3 INT, C4 INT);
SELECT * FROM T1 WHERE C1 = 1 ORDER BY C3 LIMIT 5;
上記のクエリでは、フィルターポータントに対して、C1 列にインデックス IDX_C1 を作成できます。
CREATE INDEX IDX_C1 ON T1(C1);
EXPLAIN SELECT * FROM T1 WHERE C1 = 1 ORDER BY C3 LIMIT 5;
| ===========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-------------------------------------------
|0 |LIMIT | |5 |6527|
|1 | TOP-N SORT | |5 |6527|
|2 | TABLE SCAN|t1(IDX_C1)|990 |5832|
===========================================
Outputs & filters:
-------------------------------------
0 - output([t1.C1], [t1.C2], [t1.C3], [t1.C4]), filter(nil), limit(5), offset(nil)
1 - output([t1.C1], [t1.C2], [t1.C3], [t1.C4]), filter(nil), sort_keys([t1.C3, ASC]), topn(5)
2 - output([t1.C1], [t1.C2], [t1.C3], [t1.C4]), filter(nil),
access([t1.C1], [t1.C2], [t1.C3], [t1.C4]), partitions(p0)
IDX_C1 を利用すると、データベースは C1 = 1 を満たすすべてのレコードをスキャンし、次に C3 でヒープソートを行い、C3 の値が最小の上位5行の結果を保持します。C1 = 1 を満たすレコードが多すぎる場合、スキャンとヒープソートの両方にかなりの時間がかかります。このような場合、インデックススキャン結果の順序付けられた性質をさらに活用できます。C1, C3 にインデックス IDX_C1_C3 を作成できます。
CREATE INDEX IDX_C1_C3 ON T1(C1, C3);
EXPLAIN SELECT * FROM T1 WHERE C1 = 1 ORDER BY C3 LIMIT 5;
| ============================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
--------------------------------------------
|0 |TABLE SCAN|t1(IDX_C1_C3)|5 |111 |
============================================
Outputs & filters:
-------------------------------------
0 - output([t1.C1], [t1.C2], [t1.C3], [t1.C4]), filter(nil),
access([t1.C1], [t1.C2], [t1.C3], [t1.C4]), partitions(p0),
limit(5), offset(nil)
このインデックスには2つの利点があります：
C1 = 1のポータントを使用して、インデックス上のスキャン範囲を特定できます。- スキャン結果は
C1, C3の順になります。C1の値は固定されているため、結果もC3の順になります。実行エンジンは最初の5行のデータをスキャンするだけで最終的なクエリ結果を得ることができます。これが、インデックスの結果の順序を利用してソート操作を排除する方法です。
インデックス (C1 ,C2, C3, C4) が与えられています。クエリに C1, C2 の等価フィルター条件がある場合、スキャン結果は C3, C4 の順になります。しかし、C2 に非等価ポータントがある場合、スキャン結果は C2, C3, C4 の順になります。
以下のクエリを考えてみましょう。この場合、2つの最適化の方向性があります：
C1, C2のインデックスを選択し、より多くのポータントを利用してQuery Rangeを狭めます。C1, C3のインデックスを選択し、結果の順序を利用してソートを排除します。
これら2つの最適化の方向性は、C1 = 1 と C2 > 1 のポータントのフィルター性を総合的に判断して、どちらが適切かを決定する必要があります。
EXPLAIN SELECT * FROM T1 WHERE C1 = 1 AND C2 > 1 ORDER BY C3 LIMIT 5;
例えば：
C1 = 1のフィルター性が悪く、C2 > 1のフィルター性も悪い場合、C1, C3のインデックスを選択する方が良いでしょう。C1 = 1のフィルター性が悪く、C2 > 1のフィルター性が良い場合、C1, C2のインデックスを選択する方が良いでしょう。C1 = 1のフィルター性が良い場合、これら2つのインデックスのパフォーマンスはほぼ同じである可能性があります。大きな差は出ません。C1, C2のインデックスを使用すると、パフォーマンスがより安定します。C1, C3のインデックスを使用する場合、実際のデータ分布によって異なり、時にはより極端なパフォーマンスを得られることもありますし、相対的に低いパフォーマンスになることもあります。