【MySQL】JOINする事も考慮してIndexを貼ろう

よくあるインデックス、よくあるSQL

MySQLでJOINを含むSQLを読み解いていると、かつて問題なかったインデックスがプロダクトの成長に伴い、いつのまにかCPU負荷を増大させているというケースに直面する事があります

INDEX (treatment_code)

でもSQLはこうなっている。

SELECT patients.*
FROM patients
JOIN treatments
  ON patients.id = treatments.patient_id
WHERE patients.hospital_id=1 AND treatments.treatment_code = 190030310;

テーブル構成はこんな感じ（たぶん世界で一番よくあるパターン）

patients
- id (PK)
- hospital_id

treatments
- id (PK)
- patient_id (FK)
- treatment_code

（先に結論）今回は以下のようなインデックスが最適

INDEX(patient_id, treatment_code)

単体インデックスが貼られがちな理由

アプリケーションの開発初期では「そのテーブルをどう検索するか」という点に集中しがちです。
例えばtreatments（処置）テーブルがあれば、「treatment_codeで検索するだろう」と考え、そのカラムに単体インデックス（又はテーブル単体にフォーカスした複合インデックス）を貼る。
これは設計として自然ですし、JOINを伴わない検索であれば合理的な選択です。

設計時には見落としがちな、データの成長と関係性の変化

従属関係にあるテーブルでは、時間経過と共に以下のような状態になる事が一般的です。

• patients < treatments
  • treatmentsテーブルの方のデータ量が多い（1:Nの関係）
• treatments.treatment_codeの値の種類が少ない（100とか1000種類以下）
  • カーディナリティが低い（という言い方をします）
• 基本的にpatientsにJOINして利用する
  • テーブルが従属関係にある場合、コード上JOINになるようなコードが自然な事が多い

開発環境やプロダクト初期（＝データ量が少ない場合）は、問題が顕在化する事はほとんどありません。
しかしデータ量が増えてくると、スロークエリを多発させCPUを食い潰すようになっていきます。

実際の処理順と実質的な検索条件

この時の処理順は次のようになります。

1. patients を hospital_id で絞る
2. その patient_id（patients.id） を使って treatments を検索
3. treatment_code でさらに絞る

つまりtreatments に対する実質的な検索条件は次になります。

patient_id = X AND treatment_code = Y

有効なインデックス

このような条件下で有効なインデックスは以下のような複合インデックスになります

INDEX(patient_id, treatment_code)

対策

実装時に正しく設計できるのが望ましですが、現実問題なかなか難しいです。
スロークエリを定期的にチェックしましょう。
非効率なインデックスや非効率なSQLはスロークエリを吐きます。
これを放置するとCPUリソースの上昇させ、後は天井に向かって進んでいきます。

終わりに

考慮が漏れたり、見落としたり、想定が変わったりがプロダクトの現場から無くなる事は期待できません。
定期的にスロークエリをチェックして、問題が顕在化する前に対応しましょう。

お知らせ

2026/1/28（水）に博多でエンジニア向けのLT会開催します。
お食事＆ドリンクでワイワイする会です。
是非お気軽にオフィスを見に来てください。
https://layered.connpass.com/event/378115/

若手エンジニア募集しています。
自社サービス開発、フルスタックエンジニア、要件定義・設計等に興味のある方は是非。
https://www.wantedly.com/projects/2297977