開発中にインデックスが有効にならないケースに遭遇することがあります。インデックスが設定されているカラムを使用しているのに、インデックスを利用したクエリにならない状況です。
テスト用テーブルの作成
DROP TABLE IF EXISTS performance_test;
CREATE TABLE performance_test(
record_id INT PRIMARY KEY,
column_a INT,
column_b INT,
column_c INT,
column_d INT,
description VARCHAR(30)
);
INSERT INTO performance_test VALUES(101,5,2,8,3,'product_alpha');
INSERT INTO performance_test VALUES(102,3,7,1,9,'product_beta');
INSERT INTO performance_test VALUES(103,9,4,6,2,'product_gamma');
INSERT INTO performance_test VALUES(104,2,8,4,7,'product_delta');
INSERT INTO performance_test VALUES(105,7,1,3,5,'product_epsilon');
CREATE INDEX idx_a_b_c_d ON performance_test(column_a,column_b,column_c,column_d);
インデックス無効化を防ぐ方法：
- 最左優先原則：複数カラムのインデックスの場合、クエリ条件はインデックスの最も左から始めるようにし、中間のカラムをスキップしない。（ただし、column_a = 5, description = 'product_alpha', column_b = 7 のように他の条件を追加しても、全体の効率に影響はありません）
使用されているクエリタイプはref（インデックススキャン）で、インデックスに基づいて定数クエリが実行されています（インデックスの値は定数です）。クエリ結果の利用率は100%（条件に一致した1行のみがスキャンされます）。
使用されているクエリ条件がインデックスの最初のカラムから始まっていない場合、クエリタイプはall（全テーブルスキャン）に変わり、key = null（インデックスが使用されていない）、ref = null（インデックスクエリが利用されていない）となります。クエリ結果の利用率は20%のみ（5行がスキャンされ、1行のみが条件に一致します）。
- インデックスカラムで操作（計算、関数、型変換、不等号、IS NULL、IS NOT NULL）を避ける
column_a = 5 を column_a + 1 = 6 に変更すると、結果は同じですが、インデックスクエリから全テーブルスキャンに変わります。
- 範囲クエリの右側にあるインデックスは無効になります。
これは範囲クエリであり、効率が非常に高いです。
column_b の後に column_c を追加すると、条件がより正確になったにもかかわらず、効率が低下します。なぜなら、column_c のインデックスが使用されておらず、column_c のために全テーブルスキャンが個別に行われるからです。
したがって、この場合はインデックスの順序を column_a、column_c、column_b にし、範囲クエリを最後に配置するのが最適です。
- インデックスカバリングを利用し、SELECT * クエリは最小限にする
クエリ内容にインデックスにないカラム（このテーブルのdescriptionはインデックスにありません）が含まれている場合、MySQLは条件に一致する行を見つけた後、テーブルからデータを読み取ります。
クエリのカラムがすべてインデックス内のカラム（同じインデックス内でなくても可）の場合、MySQLは直接インデックスからデータを読み取ります。
- ワイルドカードをフィールドの先頭にしない（LIKE '%aaa' はインデックスを無効にします）
テスト用にcolumn_eを追加
ALTER TABLE performance_test ADD COLUMN column_e VARCHAR(25);
UPDATE performance_test SET column_e = CONCAT('item_',description,column_a);
CREATE INDEX idx_e ON performance_test(column_e);
%を後ろに置くと、インデックスcolumn_eの範囲検索が行われます。
%を前に置く、または両側に置くと、全テーブル検索が行われます。
解決策：実際の状況では、%を両側に使用することが避けられない場合があります。この時はインデックスカバリングを利用して効率を向上させることができます。
複合インデックスcolumn_a、column_b、column_eを作成し、クエリ内容をcolumn_eを含むインデックス内の内容に変更します。インデックスカバリングにより、インデックスクエリに変換されます。
さらに主キーを追加しても有効ですが、他のインデックスカラムを追加することはできません。
- 新人トラップ：varcharフィールドに '' を付け忘れると、速度が大幅に低下し、発見が困難になります。
正常な場合：インデックスクエリ、column_eを使用し、定数クエリが実行されます。
例えば 'item_1000' がある場合に 1000 と書くと、インデックスが無効になります。全テーブル検索に変わります。
- ORの使用は最小限にする。
古いバージョンではインデックスが無効になる可能性がありましたが、現在は問題ないようです。
この場合は通常、UNION または IN を使用して最適化します。