一般的に、インデックススキャンの結果はインデックス列に対して順序付けられています。例えば、インデックス 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上のインデックスを使用する場合、実際のデータ分布によって異なり、時にはより極端なパフォーマンスを得られることもありますが、時には比較的低いパフォーマンスになることもあります。