MySQLマルチカラムインデックスについて整理
きっかけ
「理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL」の11章を読みました。
対象テーブル
users.sql
CREATE TABLE `users` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `age` tinyint unsigned NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `name_reverse` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `pref_id` tinyint unsigned NOT NULL, `gender` varchar(5) NOT NULL, PRIMARY KEY (`id`), KEY `age_name_prefid` (`age`,`name`,`pref_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;; INSERT INTO `users` (`id`, `age`, `name`, `name_reverse`, `pref_id`, `gender`) VALUES (1, 20, 'Aaron', 'noraA', 9, 'men'), (2, 20, 'Bob', 'boB', 34, 'men'), (3, 20, 'Cammy', 'ymmaC', 24, 'women'), (4, 40, 'Danny', 'ynnaD', 44, 'men'), (5, 40, 'Eric', 'cirE', 21, 'men'), (6, 40, 'Faker', 'rekaF', 15, 'men'), (7, 27, 'George', 'egroeG', 45, 'men'), (8, 27, 'Henry', 'yrneH', 33, 'women'), (9, 60, 'Ivry', 'yrvI', 20, 'women'), (10, 60, 'Johny', 'ynhoJ', 8, 'men'), (11, 60, 'Karen', 'neraK', 4, 'women');
前置き
以降に書いている「Indexが効く」というのは今回のusersテーブルの場合のみです。
もちろん他のデータではカーディナリティや検索結果数などによって結果が変わります。
実践
Indexが効く場合
Indexの全てのカラムまたは左側のカラムを優先的に指定する
EXPLAIN SELECT * FROM `users` WHERE age=20 AND name='Aaron' AND pref_id=9; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra| 1 SIMPLE users NULL ref age_name_prefid age_name_prefid 1024 const,const,const 1 100.00 NULL
EXPLAIN SELECT * FROM `users` WHERE age=20; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra| 1 SIMPLE users NULL ref age_name_prefid age_name_prefid 1 const 3 100.00 NULL
Indexが効かない場合
Indexの項目を間抜けして指定する
EXPLAIN SELECT * FROM `users` WHERE age=20 order by pref_id; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra| 1 SIMPLE users NULL ref age_name age_name 1 const 3 100.00 Using filesort
Indexの一番左側のカラムを使用しない
EXPLAIN SELECT * FROM `users` WHERE name='Aaron' AND pref_id=9; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra| 1 SIMPLE users NULL ALL NULL NULL NULL NULL 11 9.09 Using where
なぜIndexが効かないのか
マルチカラムインデックスを設定したとき、Indexは以下のように並べられます。
まずcol1カラムの値でソートされた後、順にcol2,col3とソートされていくため、Indexにcol1からアクセスしないと適切な順序でレコードを取得することができません。
そのため間抜けでアクセスしたりcol1(一番左)項目を使用しないと、Indexが効きません。
| col1 | col2 | col3 |
|---|---|---|
| 99 | abc | 1 |
| 99 | xyz | 1 |
| 100 | aaa | 1 |
| 100 | aaa | 2 |
| 100 | abc | 1 |
| 100 | abc | 2 |
| 100 | abc | 3 |
| 100 | abc | 4 |
| 100 | xyz | 1 |
| 101 | abc | 1 |
| 101 | abc | 2 |
カラムの順番
カーディナリティの高いカラムを先頭に（左に）指定する
カーディナリティの高いカラムを先頭に指定しておくことでIndexから取得するレコード数を少なくすることができます。
Explainのfilteredの値をみるとnameを先に指定しているIndexの方が高くなっている
※直感的に20歳以下のユーザ群からAaronを探すより、名前がAaronのユーザ群から20歳以下を探す方が総取得レコード数が少なそうじゃないですか？(DBのデータによりますが...)
ALTER TABLE `users` ADD KEY `age_name` (`age`,`name`); EXPLAIN SELECT * FROM `users` WHERE age<=20 AND name='Aaron'; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra| 1 SIMPLE users NULL range age_name age_name 1023 NULL 1 10.00 Using index condition
ALTER TABLE `users` ADD KEY `name_age` (`name`,`age`); EXPLAIN SELECT * FROM `users` WHERE age<=20 AND name='Aaron'; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra| 1 SIMPLE users NULL range name_age name_age 1023 NULL 1 100.00 Using index condition
OR検索 （番外編）
OR検索も複数カラムを使用しているのでマルチカラムインデックスを使うかもと思いましたが
ORは各カラムにそれぞれIndexが必要で、それぞれの結果の集合和を結果として返します。( インデックスマージ)
key項目には複数のIndexが表示されており
Extraにはインデックスマージ時の特定のアルゴリズムが表示されています。
ALTER TABLE `users` ADD KEY `name` (`name`); ALTER TABLE `users` ADD KEY `pref_id` (`pref_id`); EXPLAIN SELECT * FROM `users` WHERE name='Aaron' OR pref_id=9; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra| 1 SIMPLE users NULL index_merge name,pref_id name,pref_id 1022,1 NULL 2 100.00 Using union(name,pref_id); Using where
