MySQLでデータベースパフォーマンスを最適化する際、インデックスの適切な使用は不可欠です。しかし、いくつかの一般的な状況では、意図せずインデックスが無効になり、フルテーブルスキャンが発生してしまいます。本記事では、インデックスが無効になる6つの主要なシナリオを詳しく解説します。
インデックスのストレージ構造
インデックスが有効に機能するためには、そのストレージ構造を理解することが重要です。MySQLのインデックス構造は使用されるストレージエンジンによって異なります。
デフォルトのInnoDBエンジンはB+Tree構造を使用します。テーブル作成時に、InnoDBは自動的にプライマリキーインデックス（クラスター化インデックス）を作成し、他のインデックスはセカンダリインデックスとして扱われます。
MyISAMエンジンはB+Tree、R-Tree、Full-Textインデックスをサポートしています。両エンジンでB+Treeが使用されますが、データの保存方法に重要な違いがあります：
- InnoDB: B+Treeのリーフノードに実際のデータを保存
- MyISAM: B+Treeのリーフノードにデータの物理アドレスを保存
例として、「user_info」テーブルを考えてみましょう。ここではidがプライマリキーインデックスです。
MyISAMの場合、インデックスのリーフノードはデータへのポインタを保持します：
[リーフノード]
[id: 1] → [データの物理アドレス: 0x1234]
[id: 2] → [データの物理アドレス: 0x5678]
InnoDBのクラスター化インデックスの場合、リーフノードに実際のデータが保存されます：
[リーフノード]
[id: 1, name: 'Taro', age: 25]
[id: 2, name: 'Hanako', age: 30]
セカンダリインデックス（例えばname列に作成されたインデックス）の場合、リーフノードにはプライマリキーの値が保存されます：
[リーフノード]
[name: 'Hanako'] → [id: 2]
[name: 'Taro'] → [id: 1]
インデックスが無効になる6つのシナリオ
1. LIKEによる左側または左右のあいまい一致
左側ワイルドカード（%xx）または左右のワイルドカード（%xx%）を使用するLIKEクエリはインデックスを無効にします。
-- name列にインデックスがある場合
SELECT * FROM user_info WHERE name LIKE '%Tanaka';
このクエリはフルテーブルスキャンを引き起こします。なぜなら、B+Treeはインデックス値の順序で保存されているため、前方一致以外の検索では効率的に検索できないからです。
前方一致（'Tanaka%'）の場合はインデックスが有効に機能します：
SELECT * FROM user_info WHERE name LIKE 'Tanaka%';
2. インデックス列に対する関数の使用
インデックス列に対してMySQLの関数を使用すると、インデックスが無効になります。
-- name列にインデックスがある場合
SELECT * FROM user_info WHERE LENGTH(name) = 6;
インデックスには元の値が保存されているため、関数処理された値に対しては検索できません。
MySQL 8.0以降では関数インデックスがサポートされています：
ALTER TABLE user_info ADD KEY idx_name_length ((LENGTH(name)));
3. インデックス列に対する式計算
インデックス列に対して式計算を行うと、インデックスが無効になります。
-- id列にインデックスがある場合
SELECT * FROM user_info WHERE id + 1 = 10;
しかし、条件を式ではなく直接値で指定すればインデックスが有効になります：
SELECT * FROM user_info WHERE id = 9;
4. インデックス列の暗黙的な型変換
インデックス列が文字列型で、クエリ条件に数値が指定されると、型変換が発生してインデックスが無効になります。
-- phone列にインデックスがある場合（varchar型）
SELECT * FROM user_info WHERE phone = 1300000001;
このクエリは内部でCAST(phone AS SIGNED INT)のような変換が行われ、関数が適用されたことになるためインデックスが無効になります。
逆に、インデックス列が数値型で、クエリ条件に文字列が指定されてもインデックスは有効です：
-- id列にインデックスがある場合（int型）
SELECT * FROM user_info WHERE id = '1';
5. 複合インデックスの最左一致原則の違反
複数の列からなる複合インデックス（(a, b, c)）を作成した場合、最左の列から順に条件を指定する必要があります。これが最左一致原則です。
有効なクエリの例：
SELECT * FROM user_info WHERE a = 1;
SELECT * FROM user_info WHERE a = 1 AND b = 2;
SELECT * FROM user_info WHERE a = 1 AND b = 2 AND c = 3;
無効になるクエリの例：
SELECT * FROM user_info WHERE b = 2;
SELECT * FROM user_info WHERE c = 3;
SELECT * FROM user_info WHERE b = 2 AND c = 3;
MySQL 5.6以降では、インデックス条件下推機能（index condition pushdown）により、一部の条件で効率が改善されています。
6. WHERE句でのORの使用
WHERE句でORを使用する際、両方の条件がインデックス列でないと、インデックスが無効になります。
-- idはプライマリキー、ageは非インデックス列
SELECT * FROM user_info WHERE id = 1 OR age = 25;
このクエリはフルテーブルスキャンを引き起こします。解決策は、非インデックス列にもインデックスを作成することです：
ALTER TABLE user_info ADD INDEX idx_age (age);
これにより、インデックスマージ（index merge）が有効になり、両方のインデックスを効率的に使用できます。
まとめ
MySQLでインデックスが無効になる主な原因は以下の通りです：
- LIKEによる左側または左右のあいまい一致
- インデックス列に対する関数の使用
- インデックス列に対する式計算
- インデックス列の暗黙的な型変換
- 複合インデックスの最左一致原則の違反
- WHERE句でのORの使用（非インデックス列との組み合わせ）
これらのシナリオを理解し、適切なクエリを記述することで、MySQLのパフォーマンスを最大限に引き出すことができます。