複合インデックスの効果と注意点

MySQL SQL

概要

インデックスには複合インデックスというものがあります。
これは単一のカラムではなく、複数のカラムを組み合わせてインデックスを貼るもので、組み合わせで検索されるカラムに貼ると効率的です。
複合インデックスを使うにあたって、いくつか注意点があるのでまとめてみました。

参考

MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.3.6 マルチカラムインデックス

複合インデックスの働き

以下のテーブルを用意します。

CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Email VARCHAR(255) NOT NULL,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Gender VARCHAR(10),
    Status VARCHAR(50),
    UNIQUE(Email),
    INDEX Email_IDX (Email),
    INDEX Name_Status_IDX (Name, Status)
);

複合インデックスはNameとStatusに貼っています（この組み合わせの検索が多いと仮定）。
この時、どのような検索でインデックスが働くか見てみます。実行環境はMySQL8.0です。

SQL

keyが使用されているか確認します。

NameをWhere句に指定

explain select * from Users where Name = 'taro';

結果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Users null ref Name_Status_IDX Name_Status_IDX 402 const 1 100.00 null

これはシンプルにName_Status_IDXが使われています。

StatusをWhere句に指定

explain select * from Users where Status = 'active';

結果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Users null ALL null null null null 1 100.00 Using where

keyはnullとなっていて、インデックスが効いていません。 これは、最初にインデックス指定されたNameに基づいてインデックスが作成されて、それに基づいてさらにStatusのインデックスが作成されているためです。そのため、Status単体のインデックスは効いていません。

NameとStatusをandでWhere句に指定

explain select * from Users where Name = 'taro' and Status = 'active';

結果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Users null ref Name_Status_IDX Name_Status_IDX 605 const,const 1 100.00 null

こちらはNameとStatusがandで検索条件に入っているため、複合インデックスが効いています。

NameとStatusをorでWhere句に指定

explain select * from Users where Name = 'taro' or Status = 'active';

結果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Users null ALL Name_Status_IDX null null null 1 100.00 Using where

Name or Statusとそれぞれの検索を行うようになっており、Nameのインデックスを元にStatusのインデックスを辿る、という動作ができないためインデックスは使用されていません。

まとめ

複合インデックスの挙動を理解することで、意図せずインデックスが使用されないケースを防ぐことが大事ですね。