JOINを重ねたSQLが返ってこなくなった話
はじめに
3月に未経験からエンジニアになったハヤシです。
今回は、私が実務で関わっている「健診システム」の開発を通して学んだ、SQLとリレーショナルデータベースの難しさについて書いていきます。
当社が扱っている健診システムは、膨大なデータ量を扱っており、かつ構造が複雑です。
私が所属しているチームは問い合わせの原因調査や不具合の修正をメインで担当しておりますが、調査を行なっていく中でデータベースに登録されている情報を確認することがよくあります。
実際にデータを取得しようとしたところ、SQLを実行しても結果が返ってこず、AWS上のインスタンス負荷が上昇し続ける事象に遭遇しました。
今回は、なぜそのような事象が起きたのか、実務ならではのテーブル構造や の注意点を交えながら整理します。
なぜ何度も JOIN を重ねてしまうのか？（健診システムの構造）
調査を進める中で、「複数のデータを確認する必要がある」となる場面はよくあります。
例えば、ある受診者の詳細画面を作るとします。起点となるのは受診を特定するID（例えば受診者のID）ですが、このIDから直接引っ張ってこれる情報には限界があります。
- 基本情報は別テーブル
- 健診予約は別のテーブル
- 問診はさらに別のテーブル……
そのため、目的の情報を集めるために、自然と何度も を重ねる必要があります。
当初は、必要なテーブルを JOIN していけば目的のデータを取得できると考えていました。
しかし、実際のデータ量が多い環境では、クエリの複雑さが大きな負荷につながることが分かりました。
JOINの種類：INNER と LEFT の違いで結果が変わる
ここで、 の種類について整理します。
ここを理解していないと、予期せぬバグやパフォーマンス低下を招く可能性があります。
- （内部結合）
- 左右のテーブルの両方に存在するデータだけを取得します。
- 例えば「必ず検査結果が存在するデータ」だけを取得したい場合に使います。ただし、片方にデータがないと、その行自体が結果から除外されます。
- （外部結合）
- 左側のテーブルのデータはすべて残し、右側のテーブルに一致するデータがあれば結合します。右側になければ になります。
- 健診システムのように「受診はしたけれど、特定のオプション検査は受けていない（データがない）」というケースがある場合、 だと受診データ自体が画面から消えてしまうため、 を使う必要があります。
たとえば、以下のような2つのテーブルがあるとします。
テーブルA (users / 受診者)
| user_id | name |
| 1 | 佐藤 |
| 2 | 鈴木 |
| 3 | 高橋 |
テーブルB (orders / 検査項目)
| user_id | item |
| 1 | 血糖値 |
| 2 | 中性脂肪 |
【INNER JOIN の結果】
が共通している「1（佐藤）」と「2（鈴木）」だけが取得され、今回の検査結果データがない「3（高橋）」は除外されます。
SQL
| name | item |
| 佐藤 | 血糖値 |
| 鈴木 | 中性脂肪 |
なぜクエリが返らなくなるのか？（注意すべきパターン）
今回の原因を振り返ると、以下のような要素が重なっていました。
- インデックスが十分に貼られていなかった
- の条件に使っている外部キーや、 句で絞り込むカラムにインデックスが用意されておらず、MySQLがすべてのデータを最初から最後まで探す「フルスキャン」の状態になっていました。
- 何重もの によるデータ量の爆発
- 巨大なテーブル同士を条件の絞り込み前に したため、データベースの内部で膨大な組み合わせの一時的なデータが生成され、処理しきれない状態になりました。
- 多くの情報を一度に取得しようとするクエリ
- 1回のSQLで必要な情報をすべて取得しようとした結果、クエリが複雑化し、オプティマイザ（実行計画を考える機能）が効率的な経路を見つけにくくなっていました。