MySQL (Amazon RDS) でスロークエリ対策した際のノート。

MySQLではクエリオプティマイザがクエリ実行計画を立てる際に、絞り込み効果の低い列 (例えばレコード全体の20-30%以上が該当する場合など) に付けられたインデックスを使用することは通常ありません。

例えば 1, 2 二つの値しか持たないようなカラムに付けられたインデックスが存在していても、通常テーブルスキャンが選択されます。(そちらのほうが早いので）

しかしながら、インデックスマージでは、選択性の低いカラムのインデックスが使用されてしまい、結果的にパフォーマンスの大幅な低下を招くケースがありました。

SQLとクエリ実行プラン

SQL

SELECT MAX( create_timestamp )
FROM foobars
WHERE ( 
    foobars.valid_flag = '0' 
    AND foobars.spec_id IN ( '3', '5', '6', '7', '8', '9' ) 
    AND foobars.group_id = '622' 
    AND foobars.status_flag = '1' 
    );

実行プラン

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	foobars	NULL	index_merge	group_id,spec_id,status_flag,valid_flag	group_id,status_flag,valid_flag	4,1,1	NULL	4804	100.00	Using intersect(group_id,status_flag,valid_flag); Using where

valid_flagは値が 1, 2 の2種類しか存在しない列で、全体の95%以上が 1 です。抽出条件として 2 を選択するケースは、システム管理でごくまれに発生するだけであり、なおかつ他のカラムに付けられたインデックスで十分検索対象を絞り込めることもあり、このカラムに対する単独のインデックスは全く不要です。

foobarsテーブルのレコード数は合計数百万件あります。
今回のケースでは、valid_flagセカンダリインデックスをスキャンして集めた数百万件 (の95%程度) のPKを他のセカンダリインデックス (group_id, status_flag) をスキャンして集めたPKのリストと突き合わせる、という処理が内部的に行われていたと思われます。
これではクエリも遅くなるはずです。

対策

valid_flagカラムに付けられた単独のインデックスは削除します。存在しても使われることがないばかりか、インデックスマージに混ぜ込まれてパフォーマンスの低下を招きます。

ALTER TABLE foobars DROP INDEX `valid_flag`;

この時点ですでに十分パフォーマンスが改善しました。

ついでに複合インデックスを作成します。しかもカバリングインデックスにしました。
- このSELECTクエリはかなりの数が発行されており、レコードの作成よりも読み取り時に発生する負荷のほうがはるかに大きいです。
- インデックスの最初の方に、他のクエリでも使用されるカラムを並べ、後ろの方にこのクエリのためのカラムを並べています。

ALTER TABLE foobars ADD INDEX `idx_group_spec_flag` (`group_id`, `spec_id`, `valid_flag`, `status_flag`, `create_timestamp`);

結論

低カーディナリティのカラムに付けられた、どう考えても役に立たないインデックス達。

あっても害はないように思えますが、INSERT/UPDATE 時のパフォーマンスに影響を与えるほか、インデックスマージに混ぜ込まれてパフォーマンスの大幅な低下を招くことがあります。

まぁいっか、で済まさず見つけたら削除しましょう！