複合インデックスは1つのwhereでも効果がありますか？

Question

前提

first_nameとlast_nameを1つとしてインデックスを作成しています。

CREATE TABLE `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(16) NOT NULL COLLATE 'utf8_general_ci',
  `last_name` VARCHAR(16) NOT NULL COLLATE 'utf8_general_ci',
  `age` INT(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `first_name_last_name` (`first_name`, `last_name`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;


INSERT INTO `feed_router`.`users` (`id`, `first_name`, `last_name`, `age`) VALUES ('1', 'tarou1', 'tanaka', '1');
INSERT INTO `feed_router`.`users` (`id`, `first_name`, `last_name`, `age`) VALUES ('2', 'tarou2', 'tanaka', '2');
INSERT INTO `feed_router`.`users` (`id`, `first_name`, `last_name`, `age`) VALUES ('3', 'tarou3', 'tanaka', '3');

聞きたいこと

SQL1はwhere句でfirst_name, last_nameを指定しているので作成済みのインデックスの通りindexが利用されています。
SQL2はwhere句でfirst_nameのみを指定しているのですがpossible_keysがあるのでindexが利用されているように見ます。

Q1. 複合インデックスを作っている場合、where句で1つだけを指定してもインデックスが効きますか？
（B木で作成されるインデックスは複数列専用では無く、1つだけの場合も考慮された構造になっているのでしょうか？）
Q2. 複合インデックスがあるところに対して更に1つだけのインデックスを作る必要はありますか？

SQL1

select * from users where first_name = 'tarou1' and last_name = 'tanaka'
explain select * from users where first_name = 'tarou1' and last_name = 'tanaka'
（explain結果省略）

SQL2

select * from users where first_name = 'tarou1'
explain select * from users where first_name = 'tarou1'

---

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ref	first_name_last_name	first_name_last_name	50	const	1	Using index condition

補足情報（FW/ツールのバージョンなど）

• 10.2.12-MariaDB

Answer 1

Q1. 複合インデックスを作っている場合、where句で1つだけを指定してもインデックスが効きますか？

そのインデックスの先頭の項目が条件であれば、適用されます。

Q2. 複合インデックスがあるところに対して更に1つだけのインデックスを作る必要はありますか？

複合インデックスの先頭を含まない条件の場合は、その条件項目から始まるインデックスが必要です。

※where条件の記述順序は関係ありません。

Comments

[daiki002]

ご回答ありがとうございます。
確かに先頭を含まない条件の場合インデックスが効いてない事が確認できました。
例. select * from users where last_name = 'tarou1'

理解が深まりました。ありがとうございます。

[daiki002]

すみません更に以下の3つでインデックスを作成して確認してみた所、先頭を含まない場合でもインデックスが使われているように見えました。

INDEX `first_name_last_name_age` (`first_name`, `last_name`, `age`) USING BTREE
explain select * from users where last_name = 'tarou1' and age = 1

possible_keys: NULL
key: first_name_last_name_age
key_len: 104
ref: NULL
rows: 3
Extra: Using where; Using index

また、先程はインデックスが効かなかった以下のSQLも今はインデックスが効いています。
select * from users where last_name = 'tarou1'
3つ以上の複合インデックスの場合は先頭を含まない場合でもインデックスが効くのでしょうか？

[sazi]

ref=Nullなのでwhere条件が理由でインデックスが使われた訳ではなさそうです。
理由は分かりかねます。
検証データが少なすぎるからかもしれません。
> 先程はインデックスが効かなかった以下のSQLも今はインデックスが効いています
クエリーキャッシュは都度クリアして検証していますか？

[daiki002]

RESET QUERY CACHE; を使用してクエリーキャッシュを削除して同様の確認をしましたがUsing indexと表示される為、今回の環境ではキャッシュによらずインデックスが使われている状況と思われます。

[sazi]

では、後はデータ件数をそれなりに用意してみる事ですね。

[daiki002]

1,000万件の適当なデータを入れたテーブルで試した所おっしゃる通りの結果となりました。

※explainの結果で「possible_keysがnull」「keyがfirst_name_last_name_age」と表示される件は変わらないままでした。詳細不明です。
しかし、明らかに結果が返ってくるまでの時間に違いがありました。

Answer 2

Using index condition

だから、インデックスが有効です。
郵便番号データダウンロード などまとまったデータを使って実感を実測されては？