はじめに

業務でテーブル設計をすることがあり、どのような条件だとindexが効果的かを検証しました。
検証しましたといっても自分の経験として知っていることや、知識として明らかなことは検証していません。
検証したことは以下のことです。

• Enumのような特定の値しかとらない（カーディナリティが低い）カラムについてindexを張る。
  このとき、値に偏りがあれば、偏りが小さい値を検索するときには効果的にindexを使えるのではないか
• nullableなカラムに対してindexを張る。
  このとき、nullでない値を検索するときにindexを利用できるのではないか。

検証は、業務ではPostgreSQLを利用するPostgreSQLと、使い慣れているMySQLで行いました。
本記事の最後に、おまけでMySQLユーザが驚いたPostgreSQLについて少しだけ記載しようと思います。

なお、テーブル設計やindexについて以下の本が大変役立ちましたので紹介します。

• 達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
• SQLアンチパターン

また、検証で利用したデータ作成方法などは以下のREADME.mdに記載しています。
https://github.com/uekiGityuto/sql-expt

テーブル設計

検証用にミニマムなテーブルを作成しました。

MySQL

CREATE TABLE `users` ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, maiden_name VARCHAR(255), status TINYINT UNSIGNED NOT NULL, age SMALLINT UNSIGNED NOT NULL, PRIMARY KEY (id) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CREATE INDEX status_age_idx ON users (status, age); CREATE INDEX maiden_name_idx ON users (maiden_name);

PostgreSQL

CREATE TABLE users ( id SERIAL NOT NULL, nameVARCHAR(255) NOT NULL, maiden_name VARCHAR(255), statusSMALLINTNOT NULLCHECK (age >= 0), age SMALLINT NOT NULL CHECK (age >= 0), PRIMARY KEY (id) ); CREATE INDEX status_age_idx ON users (status, age); CREATE INDEX maiden_name_idx ON users (maiden_name);

検証

カーディナリティが低い、かつ、値に偏りがある場合

RDBのindexのアンチパターンで一番有名なものはカーディナリティが低いカラムに対してindexを張ることでしょう。
RDBのindexで一般的なB Tree（B+ Tree）の場合、カーディナリティが低いとindexを張っても十分に絞り込めないので、効果的ではないわけです。
※ bitmap indexではカーディナリティが低い方が効果的らしいですが、良くわかっていないので言及しません。
（実際のお仕事でbitmap indexを見たことも使ったこともないのですが、結構一般的なんですかね？）

しかし、値に偏りがある場合は少ない方の値で検索すれば、カーディナリティが低くても十分に絞り込めるはずです。
例えば、有効と無効のステータスがあり、無効のステータスの方が少ない場合ですね。
ということで検証してみました。

MySQL

大量データがある方（status=1）の方は2.58秒に対して、データが少ない方（status=2）の方は0.01秒なので、カーディナリティが小さくても偏りがあれば、データが少ない方の検索にはindexが有効に働くことがわかります。

検索実行

mysql> select count(*) from users where status = 1; +———-+ | count(*) | +———-+ | 2100000 | +———-+ 1 row in set (2.48 sec)mysql> select count(*) from users where status = 2; +———-+ | count(*) | +———-+ | 30 | +———-+ 1 row in set (0.01 sec)

実行計画

mysql> explain select count(*) from users where status = 1; +—-+————-+——-+————+——+—————-+—————-+———+——-+———+———-+————-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+——-+————+——+—————-+—————-+———+——-+———+———-+————-+ | 1 | SIMPLE | users | NULL | ref | status_age_idx | status_age_idx | 1 | const | 1048169 | 100.00 | Using index | +—-+————-+——-+————+——+—————-+—————-+———+——-+———+———-+————-+ 1 row in set, 1 warning (0.01 sec)mysql> explain select count(*) from users where status = 2; +—-+————-+——-+————+——+—————-+—————-+———+——-+——+———-+————-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+——-+————+——+—————-+—————-+———+——-+——+———-+————-+ | 1 | SIMPLE | users | NULL | ref | status_age_idx | status_age_idx | 1 | const | 30 | 100.00 | Using index | +—-+————-+——-+————+——+—————-+—————-+———+——-+——+———-+————-+ 1 row in set, 1 warning (0.01 sec)

PostgreSQL

大量データがある方（status=1）の方は159.417ミリ秒に対して、データが少ない方（status=2）の方は4.627ミリ秒なので、カーディナリティが小さくても偏りがあれば、データが少ない方の検索にはindexが有効に働くことがわかります。

また、データ多い方に関しては、そもそもindexすら使われていません。

検索実行

expt=# select count(*) from users where status = 1; count ——— 2010000 (1 row) Time: 159.417 msexpt=# select count(*) from users where status = 2; count ——- 30 (1 row) Time: 4.627 ms

実行計画

expt=# explain select count(*) from users where status = 1; QUERY PLAN —————————————————————————————– Finalize Aggregate (cost=24428.85..24428.86 rows=1 width=8) -> Gather (cost=24428.64..24428.85 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=23428.64..23428.65 rows=1 width=8) -> Parallel Seq Scan on users (cost=0.00..21334.92 rows=837486 width=0) Filter: (status = 1) (6 rows) Time: 1.437 msexpt=# explain select count(*) from users where status = 2; QUERY PLAN —————————————————————————————- Aggregate (cost=5.77..5.78 rows=1 width=8) -> Index Only Scan using status_age_idx on users (cost=0.43..5.60 rows=67 width=0) Index Cond: (status = 2) (3 rows) Time: 1.435 ms

結果

MySQLでもPostgreSQLでもデータの偏りがあり、かつ、データの少ない方を検索する場合にはindexが有効なことがわかりました。

nullableな場合

「達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ」ではnullについて色々な問題点が紹介されています。
その一つにindexが効かなくなるということが挙げられていました。
正確に記載した方が良いと思いますので、本文を引用します。

インデックス列におけるNULLの扱いは難しく、実装によっても異なります。IS NULLやIS NOT NULLを使用するとインデックスが使用されなかったり、NULLが多い列ではインデックスが利用されなかったりという制限を受けることがあるからです。
SELECT * FROM SomeTable WHERE col_1 IS NULL;
なぜインデックスにおいてNULLが難しい問題になるかといえば、原則的にNULLが列の正統な値ではないからです（4 3論理値とNULL」を参照）。そのため、これをどのように扱うかに関して統一的な基準がなく、状況が複雑になっています。

要するに、nullableなカラムに対してindexを張っても効果がない可能性があるから気をつけてね。ということだと思います。
ということで検証してみました。

MySQL

以下をそれぞれ実施しましたが、indexは使われるようです。

• is nullを使う
• is not nullを使う
• nullが多い列で検索する

nullが多い場合、is nullが極端に遅かったですが（他が0.01秒程度なのに対して、4.23秒かかった）、これは単純にnullが多くて絞り込めなかった（カーディナリティが小さい時にデータが多い方の速度が遅くなる問題）だけと考えられます。

検索実行

mysql> select count(*) from users where maiden_name is not null; +———-+ | count(*) | +———-+ | 33 | +———-+ 1 row in set (0.01 sec)mysql> select count(*) from users where maiden_name is null; +———-+ | count(*) | +———-+ | 2100000 | +———-+ 1 row in set (4.23 sec)mysql> select count(*) from users where maiden_name = ‘oldtaro’; +———-+ | count(*) | +———-+ | 33 | +———-+ 1 row in set (0.00 sec)

実行計画

mysql> explain select count(*) from users where maiden_name is not null; +—-+————-+——-+————+——-+—————–+—————–+———+——+——+———-+————————–+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+——-+————+——-+—————–+—————–+———+——+——+———-+————————–+ | 1 | SIMPLE | users | NULL | range | maiden_name_idx | maiden_name_idx | 1023 | NULL | 33 | 100.00 | Using where; Using index | +—-+————-+——-+————+——-+—————–+—————–+———+——+——+———-+————————–+ 1 row in set, 1 warning (0.01 sec)mysql> explain select count(*) from users where maiden_name is null; +—-+————-+——-+————+——+—————–+—————–+———+——-+———+———-+————————–+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+——-+————+——+—————–+—————–+———+——-+———+———-+————————–+ | 1 | SIMPLE | users | NULL | ref | maiden_name_idx | maiden_name_idx | 1023 | const | 1048169 | 100.00 | Using where; Using index | +—-+————-+——-+————+——+—————–+—————–+———+——-+———+———-+————————–+ 1 row in set, 1 warning (0.00 sec)mysql> explain select count(*) from users where maiden_name = ‘oldtaro’; +—-+————-+——-+————+——+—————–+—————–+———+——-+——+———-+————-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+——-+————+——+—————–+—————–+———+——-+——+———-+————-+ | 1 | SIMPLE | users | NULL | ref | maiden_name_idx | maiden_name_idx | 1023 | const | 33 | 100.00 | Using index | +—-+————-+——-+————+——+—————–+—————–+———+——-+——+———-+————-+ 1 row in set, 1 warning (0.00 sec)

PostgreSQL

以下をそれぞれ実施しましたが、is nullの場合だけindexが使われていません。

• is nullを使う
• is not nullを使う
• nullが多い列で検索する

ただし、これはMySQLと同様にnullの問題ではなく、カーディナリティの問題だと思われます。

検索実行

expt=# select count(*) from users where maiden_name is not null; count ——- 33 (1 row) Time: 0.986 ms expt=# select count(*) from users where maiden_name is null; count ——— 2010000 (1 row) Time: 64.985 ms expt=# select count(*) from users where maiden_name = ‘oldtaro’; count ——- 33 (1 row) Time: 1.790 ms

実行計画

expt=# explain select count(*) from users where maiden_name is not null; QUERY PLAN —————————————————————————————– Aggregate (cost=5.77..5.78 rows=1 width=8) -> Index Only Scan using maiden_name_idx on users (cost=0.43..5.60 rows=67 width=0) Index Cond: (maiden_name IS NOT NULL) (3 rows) Time: 1.434 msexpt=# explain select count(*) from users where maiden_name is null; QUERY PLAN —————————————————————————————– Finalize Aggregate (cost=22335.07..22335.08 rows=1 width=8) -> Gather (cost=22334.85..22335.06 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=21334.85..21334.86 rows=1 width=8) -> Parallel Seq Scan on users (cost=0.00..19241.14 rows=837486 width=0) Filter: (maiden_name IS NULL) (6 rows) Time: 1.568 ms expt=# explain select count(*) from users where maiden_name = ‘oldtaro’; QUERY PLAN —————————————————————————————- Aggregate (cost=4.45..4.46 rows=1 width=8) -> Index Only Scan using maiden_name_idx on users (cost=0.43..4.45 rows=1 width=0) Index Cond: (maiden_name = ‘oldtaro’::text) (3 rows) Time: 1.784 ms

結果

MySQLでもPostgreSQLでもnullableなカラムでもindexが利用できた。

おまけ

自分はMySQLをよく使っていたのですが、たまたま今回PostgreSQLでテーブル設計をすることになり、MySQLとPostgreSQLの違いを感じたのでおまけとして書いておきます。（主観も多く混じっているのでご注意ください）

PostgreSQLは文字列をシングルクォートで囲む

MySQLのときはダブルクォートでもシングルクォートでも文字列を表現できたので、
その感覚でPostgreSQLでもダブルクォートで囲んだらエラーになったので驚きました。

PostgreSQLでは、シングルクォートは文字列、ダブルクォートはカラム名を表現するときに利用するようです。
それに対してMySQLでは、シングルクォートやダブルクォートはどちらも文字列を表し、カラム名はバッククォートを利用しました。
なお、PostgreSQLの方が標準SQLに準拠しており、MySQLの方が独自仕様のようですね。

PostgreSQLの方がMySQLよりも早い？

自分の知識不足や実装の問題も大きいと思うのですが、大量データを投入するときにMySQLよりもPostgreSQLの方が早かったです。
具体的には100万件のデータを投入するときに、MySQLは約2分53秒かかったのですが、PostgreSQLは約7.39秒でした。