MySQLの複合インデックス設計で検索性能が変わる理由｜犬の診療記録を例に順番の決め方を解説

MySQLで大量の診療記録や顧客情報などを検索するシステムでは、複合インデックスの設計方法によって検索速度が大きく変わることがあります。特に複数の条件を指定する検索では、インデックスの列順を間違えると期待した性能が出ない場合があります。

この記事では、犬の診療記録を管理するデータベースを例に、犬ID・診療日・病名など複数条件で検索する場合に、なぜ複合インデックスの順番が重要なのか、どのような考え方で設計すればよいのかを詳しく解説します。

MySQLの複合インデックスとは

複合インデックスとは、複数のカラムを組み合わせて作成するインデックスのことです。例えば犬の診療記録テーブルに対して、以下のようなインデックスを作成できます。

CREATE INDEX idx_medical_record ON medical_records(dog_id, visit_date, disease_name);

この場合、犬ID、診療日、病名の順番でインデックスが構成されます。同じ3つのカラムを使う場合でも、順番を変更するとMySQLの検索処理は大きく変わります。

MySQLの複合インデックスは、左側のカラムから順番に利用される仕組みになっています。これは「左端一致の原則」と呼ばれる考え方です。

例えば、以下のインデックスがあるとします。

(dog_id, visit_date, disease_name)

この場合、犬IDを指定した検索では効率的に利用できます。しかし、病名だけを条件にした検索では、インデックスの先頭が病名ではないため、十分に活用できない可能性があります。

つまり、複合インデックスでは「どの条件で検索されることが多いか」を考えて列順を決める必要があります。

例えば、以下のような犬の診療記録テーブルがあるとします。

検索条件として「犬IDが100番の犬について、2025年以降に皮膚病の診療記録を取得する」という処理が多い場合、以下のようなインデックスが有効です。

(dog_id, visit_date, disease_name)

犬IDで対象データを絞り込み、その中から診療日、病名の条件を確認できるため、効率的に検索できます。

同じ診療記録データでも、利用される検索条件によって最適なインデックス順は変化します。

例えば「皮膚病の診療記録をすべて検索する」という処理が多い場合、以下のようなインデックスが適している可能性があります。

(disease_name, visit_date, dog_id)

病名を最初に指定することで、皮膚病という条件で大量のデータを効率的に絞り込めます。

一方、「特定の犬の過去の診療履歴を見る」という利用が中心なら、犬IDを先頭に配置する方が効果的です。

複合インデックスの順番を決める際は、単純にカラム数やデータ量だけで判断するのではなく、実際のSQLの利用状況を確認することが重要です。

主に以下のようなポイントを考慮します。

例えば犬IDはほぼ一意に近い値になるため、多くの場合で高い検索効果があります。一方、病名のように「皮膚病」「骨折」など種類が少ないカラムは、単独では検索効果が低い場合があります。

複合インデックスでは、範囲検索を行うカラムの位置にも注意が必要です。

例えば以下のSQLを考えます。

SELECT * FROM medical_records WHERE dog_id = 100 AND visit_date BETWEEN '2025-01-01' AND '2025-12-31' AND disease_name = '皮膚病';

この場合、MySQLは犬IDで絞り込み、診療日の範囲検索を行います。診療日より後ろにある病名の条件は、状況によってはインデックスの恩恵を十分に受けられない場合があります。

そのため、範囲検索を含むSQLでは、どの条件までインデックス検索が効いているかを実行計画で確認することが重要です。

作成した複合インデックスが本当に利用されているか確認するには、MySQLのEXPLAINを使用します。

例えば以下のようにSQLの前にEXPLAINを付けます。

EXPLAIN SELECT * FROM medical_records WHERE dog_id = 100 AND visit_date >= '2025-01-01';

実行結果を見ることで、MySQLがどのインデックスを選択しているか、どの程度データを絞り込めているかを確認できます。

インデックスは作成するだけではなく、実際の検索処理で効果が出ているかを確認しながら調整することが大切です。

MySQLの複合インデックスでは、同じカラムを使用していても並び順によって検索性能が大きく変わります。これは、複合インデックスが左端のカラムから順番に利用される仕組みになっているためです。

犬の診療記録のような大量データを扱う場合でも、「犬IDによる検索が多いのか」「病名検索が多いのか」「診療日による期間検索が多いのか」を分析して順番を決めることが重要です。

最適な複合インデックス設計を行うには、実際のSQLや利用パターンを確認し、EXPLAINによる実行計画を参考にしながら調整することが、安定した高速検索につながります。