現象と課題

フロントエンドからの API リクエストに対し、バックエンドの処理時間が長く、タイムアウトエラーが発生する事象がありました。単にタイムアウト閾値を引き上げることは根本解決にならないため、処理 bottleneck の特定を行いました。

原因特定のプロセス

各メソッドの実行開始と終了にタイムスタンプを記録し、プロファイリングを行うことでボトルネックを特定しました。その結果、データベースへの問い合わせ処理に異常な遅延があることが判明しました。

問題のある実装パターン

当初の実装では、アプリケーション層でループ処理を行い、その中で個別にデータベースアクセスを行っていました。いわゆる N+1 問題の状態です。

List<ResponseDTO> results = new ArrayList<>();
List<UserEntity> targets = userRepository.fetchByCategory(type);
for (UserEntity target : targets) {
    OrderData data = orderRepository.findRelated(target.getUserId());
    results.add(data);
}
return results;

この方式では、ユーザー数に応じてクエリ発行回数が増加し、ネットワークレイテンシと DB 負荷が蓄積します。

SQL による最適化

アプリケーション側でのループを廃止し、結合クエリ（JOIN）と集約関数を用いてデータベース側で処理を完結させるように変更しました。また、以下のポイントに留意してクエリを構築しました。

• WHERE 句で検索範囲を事前に絞る
• SELECT 句では必要なカラムのみ指定し、* を避ける
• 部分一致検索には LIKE CONCAT を活用する
• NULL 値対策には IFNULL または COALESCE を使用する

最適化後のクエリでは、複数のサブクエリを結合する代わりに、条件付き集約を用いて単一の結合で済むように構造を変更しました。

SELECT
    u.id AS user_id,
    u.real_name AS name,
    IFNULL(COUNT(o.id), 0) AS finish_count,
    IFNULL(SUM(CASE WHEN o.handling_status = 20 THEN 1 ELSE 0 END), 0) AS unfinish_count
FROM sys_user u
LEFT JOIN wo_order o ON o.handler_user_ids LIKE CONCAT('%', u.id, '%')
WHERE
    u.org_code = '1300B00'
    AND u.is_handle_user != 0
    AND o.create_time BETWEEN '2020-00-00 16:54:21' AND '2020-12-30 16:54:21'
GROUP BY u.id, u.real_name
ORDER BY finish_count DESC;

改善結果

この変更により、処理時間は従来の 3.5 秒から 0.3 秒まで短縮することに成功しました。