概要

「複合インデックスを使うにはwhere句に書くカラムの順番をインデックスの定義順と合わせなければならんのじゃよ *1」って言われて、いやそんなまさか…？と思ったので念のため検証してみたメモ。

結論から言うと、試した限りではそんなことはなかった。

どういうことかというと

こういうテーブルとインデックスがあったときに…

create table sample_data (
    id integer primary key,
    colum_a integer not null,
    colum_b integer not null,
    colum_c integer not null
);
create index idx_sample_data_01 on sample_data (colum_a, colum_b, colum_c);

こう書くと複合インデックス idx_sample_data_01 が使われるけど

-- where句のカラムの順番を複合インデックスでの定義順 colum_a, colum_b, colum_c に合わせる
select * from sample_data where colum_a = 100 and colum_b = 100 and colum_c = 100;

こう書くと使われない、という主張らしい。

-- where句のカラムの順番を複合インデックスでの定義順とは異なる順番にする
select * from sample_data where colum_c = 100 and colum_b = 100 and colum_a = 100;

というわけで試してみる

環境

PostgreSQL 15.3
MySQL 8.0.33

いずれもDocker Hubの公式イメージを使う。

手順

1. Dockerコンテナ立ち上げ

$ docker run -d \
  --name postgres \
  -p 127.0.0.1:5432:5432 \
  -e POSTGRES_PASSWORD=postgres \
  -v pgdata:/var/lib/postgresql/data \
  postgres:15.3

$ docker run -d \
  --name mysql \
  -p 127.0.0.1:3306:3306 \
  -e MYSQL_ROOT_PASSWORD=mysql \
  mysql:8.0.33

2. テーブルを作ってデータを投入

前述のテーブルにデータを50,000件入れて試してみる。

こんな感じのSQLファイルを作っておいて

-- insert.sql
create table sample_data (
    id integer primary key,
    colum_a integer not null,
    colum_b integer not null,
    colum_c integer not null
);
create index idx_sample_data_01 on sample_data (colum_a, colum_b, colum_c);

insert into sample_data values (1, 1, 1, 1),
(2, 2, 2, 2),
(3, 3, 3, 3),
-- 中略
(49999, 49999, 49999, 49999),
(50000, 50000, 50000, 50000);

各DBに投入する。analyze もやっておく。

# PostgreSQL
$ psql -h localhost -U postgres -f insert.sql
$ psql -h localhost -U postgres -c 'analyze sample_data;'

# MySQL
$ mysql -p -u root mysql < insert.sql
$ mysql -p -u root mysql -e 'analyze table sample_data;'

ちなみにデータはこんな感じのRubyコードで作ったもの。

values = 1.upto(50000).map {|n| "(%d, %d, %d, %d)" % [n, n, n, n] }.join(",\n")
puts "insert into sample_data values #{values};"

4. 試してみる

各条件で実行計画を取得してみて、インデックスが使われるかどうかを確認する。

PostgreSQL

まずはwhere句のカラムの順番を複合インデックスでの定義順に合わせた状態でやってみる。これは当然インデックスが使われる。

explain verbose select * from sample_data where colum_a = 100 and colum_b = 100 and colum_c = 100;

QUERY PLAN                                                                                                 |
-----------------------------------------------------------------------------------------------------------+
Index Scan using idx_sample_data_01 on public.sample_data  (cost=0.29..8.31 rows=1 width=16)               |
  Output: id, colum_a, colum_b, colum_c                                                                    |
  Index Cond: ((sample_data.colum_a = 100) AND (sample_data.colum_b = 100) AND (sample_data.colum_c = 100))|

QUERY PLAN が Index Scan using idx_sample_data_01 〜 となっているので、ちゃんと複合インデックスが使われていることがわかる。

次にwhere句のカラムの順番を複合インデックスでの定義順とは逆にしてみる。

explain verbose select * from sample_data where colum_c = 100 and colum_b = 100 and colum_a = 100;

QUERY PLAN                                                                                                 |
-----------------------------------------------------------------------------------------------------------+
Index Scan using idx_sample_data_01 on public.sample_data  (cost=0.29..8.31 rows=1 width=16)               |
  Output: id, colum_a, colum_b, colum_c                                                                    |
  Index Cond: ((sample_data.colum_a = 100) AND (sample_data.colum_b = 100) AND (sample_data.colum_c = 100))|

…普通にインデックス使われている様子。コストも全く一緒。

さらに順番を入れ替えてみたけど、やっぱり結果は同じ。

explain verbose select * from sample_data where colum_c = 100 and colum_a = 100 and colum_b = 100;

QUERY PLAN                                                                                                 |
-----------------------------------------------------------------------------------------------------------+
Index Scan using idx_sample_data_01 on public.sample_data  (cost=0.29..8.31 rows=1 width=16)               |
  Output: id, colum_a, colum_b, colum_c                                                                    |
  Index Cond: ((sample_data.colum_a = 100) AND (sample_data.colum_b = 100) AND (sample_data.colum_c = 100))|

MySQL

where句のカラムの順番を複合インデックスでの定義順に合わせた場合。当然インデックスが使われる。

explain select * from sample_data where colum_a = 100 and colum_b = 100 and colum_c = 100;

Name         |Value             |
-------------+------------------+
id           |1                 |
select_type  |SIMPLE            |
table        |sample_data       |
partitions   |                  |
type         |ref               |
possible_keys|idx_sample_data_01|
key          |idx_sample_data_01|
key_len      |12                |
ref          |const,const,const |
rows         |1                 |
filtered     |100.0             |
Extra        |Using index       |

type = ref、key = idx_sample_data_01 なので、ちゃんと複合インデックスが使われている。

PostgreSQLと同様、順番を逆にしたり入れ替えたりしてもやっぱり結果は変わらず。

explain select * from sample_data where colum_c = 100 and colum_b = 100 and colum_a = 100;

Name         |Value             |
-------------+------------------+
id           |1                 |
select_type  |SIMPLE            |
table        |sample_data       |
partitions   |                  |
type         |ref               |
possible_keys|idx_sample_data_01|
key          |idx_sample_data_01|
key_len      |12                |
ref          |const,const,const |
rows         |1                 |
filtered     |100.0             |
Extra        |Using index       |

explain select * from sample_data where colum_c = 100 and colum_a = 100 and colum_b = 100;

Name         |Value             |
-------------+------------------+
id           |1                 |
select_type  |SIMPLE            |
table        |sample_data       |
partitions   |                  |
type         |ref               |
possible_keys|idx_sample_data_01|
key          |idx_sample_data_01|
key_len      |12                |
ref          |const,const,const |
rows         |1                 |
filtered     |100.0             |
Extra        |Using index       |

こんな話はどこから出てきた？

試した結果を見てそりゃそうだよなと思いつつもうちょっと調べてみると、「達人に学ぶSQL徹底指南書」の初版第4刷 p.205 にはこんなことが書いてあった。

(col_1, col_2, col_3) に対してこの順番で複合インデックスが張られているとします。その場合、条件指定の順番が重要です。

○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100;
× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10;

必ず最初の列（col_1）を先頭に書かねばなりませんし、順番も崩してはいけません。

最後の例は条件を col_2, col_1 の順番で書いていて × となっているので、今回の話と合致する。

しかし同書籍の第2版では下記のように変わっており、WHERE col_2 = 100 AND col_1 = 10 の例が無くなっている。

○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100;
× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500;

初版の正誤表にも当該部分の記載が無いので、もしかしたら昔はそういう制約事項があったのかもしれない。

www.shoeisha.co.jp

まとめ

少なくとも PostgreSQL 15.3 と MySQL 8.0.33 では、where句のカラムの順番を複合インデックスの定義順と合わせなくても大丈夫そう。

いにしえのDBは知らん。

参考

各DBの実行計画の読み方の参考。

*1: (colum_a, colum_b) みたいなインデックスの順番で colum_a を使わず where column_b = 'hoge'; だけだったりすると多くのDBでは当然インデックスが使われないけど、そういうことではなく。ちなみにこういうケースでもOracleではINDEX SKIP SCANってやつでインデックスが使えるらしい。すげえな。

ser1zw's blog

複合インデックスを使うにはwhere句に書くカラムの順番を合わせないとダメとか無いですよねという話

概要