複合インデックスについて、「構成列の一部が指定された場合でも効く」というのはなんとなく知っていたのですが、その組み合わせの範囲とやらがわかっていなかったので、公式ドキュメントをベースに調べてみました。対象は、Oracle Database, MySQL, PostgreSQLです。
前提
- あくまで「複合インデックスが選択される可能性がある」というだけです。先頭列を抽出条件に含まなかったり、偏った部分集合を抽出条件とした場合などは、別のスキャン方法が選択される可能性が高いです。どのインデックスが選択されるかは、オプティマイザが総合的に判断するということを前提としてください。
想定する状況
- あるテーブルのカラム(C1, C2, C3) に対して複合インデックスを貼っている状況を考えます。C1, C2, C3 の順で順序付けされています。
- 上記カラムをSQLで指定した場合に、どのような組み合わせであれば複合インデックスがオプティマイザの候補に挙がるのかを考えます。
- 上記カラムを指定する方法ですが、ここでは便宜上、以下の方法で考えます。
- 抽出条件として等号で指定
- 複数指定する場合は、それぞれの条件を
andで結合した場合を考えます
（例：where C1 = 'x' and C2 = 'y' and C3 = 'z'）。
Oracle Database
調べたバージョンは18cです。
Oracle Database では、コンポジット索引(Composite Indexes)というのが複合インデックスに当たります。
『SQL Tuning Guide』によると、
SQL文の構成メンバーが索引の先頭部分を使用する場合、その文はコンポジット索引を含むアクセス・パスを使用できます。
とあります。加えて、以下のような注意書きがあります。
注意:
このことは、索引スキップ・スキャンには現在、該当しなくなっています。
つまり、「索引の先頭部分を使用する場合は、コンポジット索引が利用可能であるが、索引スキップ・スキャンについては、この原則が当てはまらないよ。」ということです。ここでの"索引の先頭部分”とは何を指しているのでしょうか。これを説明したのが以下の記述になります。
索引の先頭部分とは、その索引を作成したCREATE INDEX文で列リストの先頭から連続的に指定された1つ以上の列の組合せのことです。次のCREATE INDEX文について考えてみます。
CREATE INDEX comp_ind ON table1(x, y, z);
x、xy、xyzの各列の組合せは、索引の先頭部分です。
yz、y、zの各列の組合せは、索引の先頭部分ではありません。
これにより、原則どのような組み合わせでコンポジット索引が候補に挙がるのかはっきりしました。
最後に索引スキップ・スキャンの説明ですが、これはOracle9iから導入された機能で、索引内の先頭列が抽出条件に含まれていなくても、先頭列をスキップすることで複合索引が使用可能になるというものです。
以上を踏まえて、抽出条件の組み合わせと、複合インデックス利用可不可についてまとめてみます。 C1, C2, C3 列の "○"は抽出条件に含めることを意味します。
| C1 | C2 | C3 | 複合インデックス利用可不可 |
|---|---|---|---|
| ○ | ○ | ○ | 可 |
| ○ | ○ | × | 可 |
| ○ | × | × | 可 |
| ○ | × | ○ | 可 ※C1のみ、C3は無視 |
| × | ○ | ○ | 可 ※索引スキップ・スキャン |
| × | ○ | × | 可 ※索引スキップ・スキャン |
| × | × | ○ | 可 ※索引スキップ・スキャン |
| × | × | × | 不可 |
MySQL
こちらは調べたバージョンは8.0です。
MySQLでは、マルチカラムインデックス(Multiple-Column Indexes)というのが複合インデックスに当たります。
『MySQL 8.0 Reference Manual』 にると、以下のような記述があります。
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
ここは、Oracle Databaseと同じですね。
Oracle Database の索引スキップ・スキャンのような機能を探してみると、最近のリリースノートに記載がありました。バージョン8.0.13 から追加された機能のようです。
MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.2 Range Optimization
以上を踏まえてまとめてみます。
| C1 | C2 | C3 | 複合インデックス利用可不可 |
|---|---|---|---|
| ○ | ○ | ○ | 可 |
| ○ | ○ | × | 可 |
| ○ | × | × | 可 |
| ○ | × | ○ | 可 ※C1のみ、C3は無視 |
| × | ○ | ○ | 可 ※Skip Scan |
| × | ○ | × | 可 ※Skip Scan |
| × | × | ○ | 可 ※Skip Scan |
| × | × | × | 不可 |
PostgreSQL
調べたバージョンは10.5です。
PostgreSQLでは、マルチカラムインデックス(Multicolumn Indexes)というのが複合インデックスに当たります。
『PostgreSQL10.5 日本語マニュアル』では、以下のような記述があります。
複数列に対するB-treeインデックスをインデックス対象列の任意の部分集合を含む問い合わせ条件で使用することができます。
さらに、以下のように続きます。
例えば、(a, b, c)に対するインデックスがあり、（中略）このインデックスは原理上、 aに対する制約を持たず、bあるいはcに制約に持つ問い合わせでも使用することができます。 しかし、インデックス全体がスキャンされますので、ほとんどの場合、プランナはインデックスの使用よりもシーケンシャルテーブルスキャンを選択します。
こちらをまとめてみると、以下のような感じでしょうか。
| C1 | C2 | C3 | 複合インデックス利用可不可 |
|---|---|---|---|
| ○ | ○ | ○ | 可 |
| ○ | ○ | × | 可 |
| ○ | × | × | 可 |
| ○ | × | ○ | 可 ※C1のみ、C3は無視 |
| × | ○ | ○ | 可 ※table scanが選択される可能性高い |
| × | ○ | × | 可 ※table scanが選択される可能性高い |
| × | × | ○ | 可 ※table scanが選択される可能性高い |
| × | × | × | 不可 |
おわりに
組み合わせの指定範囲について学べたほか、Oracle Database の索引スキップ・スキャンや、MySQL の Skip Scan 機能について知るいい機会になりました。
