検証環境の構築

まず、検証用のテーブルとデータを準備します。複合インデックス idx_name_age_job を staff_name, staff_age, job_role 列に設定します。

CREATE TABLE `staff_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_name` varchar(24) NOT NULL DEFAULT '' COMMENT '氏名',
  `staff_age` int(11) NOT NULL DEFAULT '0' COMMENT '年齢',
  `job_role` varchar(20) NOT NULL DEFAULT '' COMMENT '役職',
  `enrolled_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入社日',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_job` (`staff_name`,`staff_age`,`job_role`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='社員マスター';

初期データの投入および批量挿入用のストアドプロシージャを作成します。

INSERT INTO staff_records(staff_name, staff_age, job_role, enrolled_at) VALUES('Yamada', 25, 'leader', NOW());
INSERT INTO staff_records(staff_name, staff_age, job_role, enrolled_at) VALUES('Sato', 26, 'engineer', NOW());
INSERT INTO staff_records(staff_name, staff_age, job_role, enrolled_at) VALUES('Suzuki', 26, 'engineer', NOW());

DROP PROCEDURE IF EXISTS generate_staff_data; 
delimiter ;;
CREATE PROCEDURE generate_staff_data()        
BEGIN
  DECLARE counter INT DEFAULT 0;                    
  REPEAT
    INSERT INTO staff_records(staff_name, staff_age, job_role) VALUES(CONCAT('User', counter), counter, 'engineer');  
    SET counter = counter + 1;                       
  UNTIL counter >= 100000 END REPEAT;
END;;
delimiter ;
CALL generate_staff_data();

複合インデックスの先頭列における範囲検索の挙動

複合インデックスの最初の列で範囲検索（>, <, BETWEEN など）を行った場合、インデックスが有効に機能しないケースがあります。

EXPLAIN SELECT * FROM staff_records WHERE staff_name > 'Yamada' AND staff_age = 25 AND job_role ='leader';

EXPLAIN の結果において possible_keys にインデックスが表示されていても、実際に使用されない（key が NULL）ことがあります。これは、MySQL のオプティマイザが「先頭列で範囲検索を行う場合、結果セットが大きくなりすぎ、インデックス経由での行検索（回表）コストが全表スキャンよりも高くなる」と判断するためです。

インデックス強制使用のコスト検証

ヒント句を用いて強制的にインデックスを使用させることも可能です。

EXPLAIN SELECT * FROM staff_records FORCE INDEX(idx_name_age_job) WHERE staff_name > 'Yamada' AND staff_age = 25 AND job_role ='leader';

クエリキャッシュの影響を排除するため、キャッシュを無効化した状態で実行時間を比較します。

• キャッシュ無効化：

  SET GLOBAL query_cache_size = 0;
  SET GLOBAL query_cache_type = 0;

• 通常検索：SELECT * FROM staff_records WHERE staff_name > 'Yamada';
• 強制インデックス検索：SELECT * FROM staff_records FORCE INDEX(idx_name_age_job) WHERE staff_name > 'Yamada';

計測結果によると、強制インデックスを使用するとスキャン行数は減少するものの、実行時間は全表スキャンよりも長くなる傾向があります。これは、インデックス走査後のランダムアクセスによる回表オーバーヘッドが原因と考えられます。

カバリングインデックスによるオーバーヘッド削減

SELECT 句で指定する列がインデックスに含まれている場合、テーブル本体へのアクセスが不要になります。

EXPLAIN SELECT staff_name, staff_age, job_role FROM staff_records WHERE staff_name > 'Yamada' AND staff_age = 25 AND job_role ='leader';

このクエリでは key_len が適切に設定され、インデックスツリー上の走査のみで処理が完結します。回表が発生しないため、先頭列が範囲検索であってもインデックスが有効に機能します。

データ量による IN 句と OR 句のインデックス選択方針

IN 句や OR 条件を使用した場合、テーブルの行数によってインデックスの使用可否が変化します。

データ量が約 10 万件の場合：

EXPLAIN SELECT * FROM staff_records WHERE staff_name IN ('Yamada','Sato','Suzuki') AND staff_age = 25 AND job_role ='leader';
EXPLAIN SELECT * FROM staff_records WHERE (staff_name = 'Yamada' OR staff_name = 'Sato') AND staff_age = 25 AND job_role ='leader';

データ量が数件しかない別テーブル（staff_records_copy）の場合：

EXPLAIN SELECT * FROM staff_records_copy WHERE staff_name IN ('Yamada','Sato','Suzuki') AND staff_age = 25 AND job_role ='leader';

一般的に、データ量が多い場合はインデックスが選択されますが、行数が少ない場合は回表コストを考慮し全表スキャンが選択されることがあります。ただし、MySQL 8.0 以降では optimizer の改善により、小規模データでもインデックスが採用されるケースが増えています。

LIKE 演算子と索引下推 (ICP) の仕組み

前方一致検索（LIKE 'prefix%'）は、通常インデックスを利用できます。

EXPLAIN SELECT * FROM staff_records WHERE staff_name LIKE 'Yamada%' AND staff_age = 25 AND job_role ='leader';

これは「索引下推（Index Condition Pushdown, ICP）」という最適化機能が働いているためです。複合インデックス (staff_name, staff_age, job_role) において、staff_name でフィルタリングした後、本来であれば一度回表してから他の列の条件をチェックする必要があります。

MySQL 5.6 以前のバージョンでは、インデックスで名前を絞り込んだ後、主キーインデックスを辿って行を取得し、サーバー層で staff_age と job_role の条件を評価していました。

MySQL 5.6 以降では、ストレージエンジン層でインデックスに含まれるすべての列に対して条件評価を行い、適合しないレコードを早期に除外します。これにより、不要な回表アクセスを削減し、パフォーマンスを向上させます。ICP は二级索引（セカンダリインデックス）に対して有効であり、クラスタインデックス（主キー）には適用されません。

一方で、範囲検索（> など）に対して ICP が適用されにくいのは、オプティマイザが範囲検索の結果セットが大きくなると判断し、ICP によるメリットよりも全表スキャンのコストが低いと見積もる傾向があるためです。LIKE 'prefix%' は結果セットが限定されやすい性質を持つため、ICP の恩恵を受けやすくなります。