[Optimization] 반복 조회 쿼리 개선

우아한테크코스 레벨 4 팀 프로젝트 festabook에서 학습한 내용을 정리한 글입니다.

 

💭 들어가며

이번 글은 Index나 Fetch Join을 활용해 쿼리를 해결한 문제는 아니지만, 초기에 가독성을 중시해 작성한 코드에서 놓쳤던 반복 조회 쿼리 문제를 개선한 과정을 정리한 글이다.

 

 

✅ 반복 조회 쿼리 문제

PlaceImage 순서 업데이트 과정에서 비즈니스 로직의 for문으로 인해 PlaceImage 개수만큼 조회 쿼리가 반복 실행되고 있었다. 현재는 각 Place마다 최대 5개로 제한되어 있어 큰 문제는 없었지만, 향후 Place당 Image 개수를 늘릴 계획도 있어 네트워크 요청을 최소화하는 방식으로 개선할 필요가 있다고 판단했다.

🔽 K6 평균 값 측정 스크립트

import http from 'k6/http';
import { check, sleep } from 'k6';
import { Trend } from 'k6/metrics';

export let options = {
  vus: 1,          // 동시 사용자 수
  iterations: 1000, // 총 요청 수
};

let responseTime = new Trend('response_time');

export default function () {
  const url = '${요청할_ENDPOINT}';
  const payload = JSON.stringify([
    { placeImageId: 126, sequence: 1 },
    { placeImageId: 127, sequence: 2 },
    { placeImageId: 128, sequence: 3 },
    { placeImageId: 129, sequence: 4 },
    { placeImageId: 130, sequence: 5 },
  ]);

  const params = {
    headers: {
      'Content-Type': 'application/json',
      'accept': '*/*',
      'festival': '1',
      'Authorization': 'Bearer ${토큰_값}',
    },
  };

  const res = http.patch(url, payload, params);

  check(res, {
    'status is 200': (r) => r.status === 200,
  });

  responseTime.add(res.timings.duration);

  sleep(0.1); // 요청 간격
}

 

 

✅ 개선 전

🔽 개선 전 쿼리

2025-09-29 12:52:14.368 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingFilter - [API Call] method=PATCH queryString=null uri=/api/places/images/sequences
2025-09-29 12:52:14.383 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=PlaceImageController methodName=updateFestivalImagesSequence
2025-09-29 12:52:14.385 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=PlaceImageService methodName=updatePlaceImagesSequence
2025-09-29 12:52:14.385 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=CrudRepository methodName=findById
2025-09-29 12:52:14.386 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    select
        pi1_0.id,
        pi1_0.created_at,
        pi1_0.deleted,
        pi1_0.deleted_at,
        pi1_0.image_url,
        pi1_0.place_id,
        pi1_0.sequence,
        pi1_0.updated_at 
    from
        place_image pi1_0 
    where
        pi1_0.id=? 
        and (
            pi1_0.deleted = 0
        )
2025-09-29 12:52:14.388 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=CrudRepository methodName=findById executionTime=2ms
2025-09-29 12:52:14.388 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    select
        p1_0.id,
        p1_0.category,
        p1_0.latitude,
        p1_0.longitude,
        p1_0.created_at,
        p1_0.deleted,
        p1_0.deleted_at,
        p1_0.description,
        p1_0.end_time,
        p1_0.festival_id,
        p1_0.host,
        p1_0.location,
        p1_0.start_time,
        p1_0.title,
        p1_0.updated_at 
    from
        place p1_0 
    where
        p1_0.id=? 
        and (
            p1_0.deleted = 0
        )
2025-09-29 12:52:14.390 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=CrudRepository methodName=findById
2025-09-29 12:52:14.391 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    select
        pi1_0.id,
        pi1_0.created_at,
        pi1_0.deleted,
        pi1_0.deleted_at,
        pi1_0.image_url,
        pi1_0.place_id,
        pi1_0.sequence,
        pi1_0.updated_at 
    from
        place_image pi1_0 
    where
        pi1_0.id=? 
        and (
            pi1_0.deleted = 0
        )
2025-09-29 12:52:14.392 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=CrudRepository methodName=findById executionTime=1ms
2025-09-29 12:52:14.392 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=CrudRepository methodName=findById
2025-09-29 12:52:14.392 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    select
        pi1_0.id,
        pi1_0.created_at,
        pi1_0.deleted,
        pi1_0.deleted_at,
        pi1_0.image_url,
        pi1_0.place_id,
        pi1_0.sequence,
        pi1_0.updated_at 
    from
        place_image pi1_0 
    where
        pi1_0.id=? 
        and (
            pi1_0.deleted = 0
        )
2025-09-29 12:52:14.393 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=CrudRepository methodName=findById executionTime=1ms
2025-09-29 12:52:14.393 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=CrudRepository methodName=findById
2025-09-29 12:52:14.393 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    select
        pi1_0.id,
        pi1_0.created_at,
        pi1_0.deleted,
        pi1_0.deleted_at,
        pi1_0.image_url,
        pi1_0.place_id,
        pi1_0.sequence,
        pi1_0.updated_at 
    from
        place_image pi1_0 
    where
        pi1_0.id=? 
        and (
            pi1_0.deleted = 0
        )
2025-09-29 12:52:14.394 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=CrudRepository methodName=findById executionTime=0ms
2025-09-29 12:52:14.394 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=CrudRepository methodName=findById
2025-09-29 12:52:14.394 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    select
        pi1_0.id,
        pi1_0.created_at,
        pi1_0.deleted,
        pi1_0.deleted_at,
        pi1_0.image_url,
        pi1_0.place_id,
        pi1_0.sequence,
        pi1_0.updated_at 
    from
        place_image pi1_0 
    where
        pi1_0.id=? 
        and (
            pi1_0.deleted = 0
        )
2025-09-29 12:52:14.395 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=CrudRepository methodName=findById executionTime=1ms
2025-09-29 12:52:14.395 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=PlaceImageService methodName=updatePlaceImagesSequence executionTime=9ms
2025-09-29 12:52:14.396 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-09-29 12:52:14.397 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-09-29 12:52:14.399 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-09-29 12:52:14.400 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-09-29 12:52:14.401 [http-nio-80-exec-10] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-09-29 12:52:14.405 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=PlaceImageController methodName=updateFestivalImagesSequence executionTime=22ms
2025-09-29 12:52:14.407 [http-nio-80-exec-10] INFO  c.d.f.g.logging.LocalLoggingFilter - [API End] statusCode=200 requestBody=[
  {
    "placeImageId": 126,
    "sequence": 1
  },
  {
    "placeImageId": 127,
    "sequence": 2
  },
  {
    "placeImageId": 128,
    "sequence": 4
  },
  {
    "placeImageId": 129,
    "sequence": 5
  },
  {
    "placeImageId": 130,
    "sequence": 3
  }
] executionTime=38ms

🔽 개선 전 코드

@Transactional
public PlaceImageSequenceUpdateResponses updatePlaceImagesSequence(
        Long festivalId,
        List<PlaceImageSequenceUpdateRequest> requests
) {
    List<PlaceImage> placeImages = new ArrayList<>();

    for (PlaceImageSequenceUpdateRequest request : requests) {
        PlaceImage placeImage = getPlaceImageById(request.placeImageId());
        validatePlaceImageBelongsToFestival(placeImage, festivalId);
        placeImage.updateSequence(request.sequence());
        placeImages.add(placeImage);
    }

    Collections.sort(placeImages);

    return PlaceImageSequenceUpdateResponses.from(placeImages);
}

🔽 개선 전 Local 평균값

Local WAS -> Local DB

🔽 개선 전 Dev 평균값

Local WAS -> Dev DB

Dev WAS -> Dev DB

 

 

✅ 개선 후

🔽 개선 후 쿼리

2025-10-13 21:24:47.953 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingFilter - [API Call] method=PATCH queryString=null uri=/api/places/images/sequences
2025-10-13 21:24:47.971 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=PlaceImageController methodName=updateFestivalImagesSequence
2025-10-13 21:24:48.007 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=PlaceImageService methodName=updatePlaceImagesSequence
2025-10-13 21:24:48.007 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method Call] className=ListCrudRepository methodName=findAllById
2025-10-13 21:24:48.014 [http-nio-80-exec-2] DEBUG org.hibernate.SQL - 
    /* <criteria> */ select
        pi1_0.id,
        pi1_0.created_at,
        pi1_0.deleted,
        pi1_0.deleted_at,
        pi1_0.image_url,
        pi1_0.place_id,
        pi1_0.sequence,
        pi1_0.updated_at 
    from
        place_image pi1_0 
    where
        (
            pi1_0.deleted = 0
        ) 
        and pi1_0.id in (?, ?, ?, ?, ?)
2025-10-13 21:24:48.028 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=ListCrudRepository methodName=findAllById executionTime=20ms
2025-10-13 21:24:48.028 [http-nio-80-exec-2] DEBUG org.hibernate.SQL - 
    select
        p1_0.id,
        p1_0.category,
        p1_0.latitude,
        p1_0.longitude,
        p1_0.created_at,
        p1_0.deleted,
        p1_0.deleted_at,
        p1_0.description,
        p1_0.end_time,
        p1_0.festival_id,
        p1_0.host,
        p1_0.location,
        p1_0.start_time,
        p1_0.title,
        p1_0.updated_at 
    from
        place p1_0 
    where
        p1_0.id=? 
        and (
            p1_0.deleted = 0
        )
2025-10-13 21:24:48.041 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=PlaceImageService methodName=updatePlaceImagesSequence executionTime=33ms
2025-10-13 21:24:48.091 [http-nio-80-exec-2] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-10-13 21:24:48.108 [http-nio-80-exec-2] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-10-13 21:24:48.121 [http-nio-80-exec-2] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-10-13 21:24:48.155 [http-nio-80-exec-2] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-10-13 21:24:48.167 [http-nio-80-exec-2] DEBUG org.hibernate.SQL - 
    /* update
        for com.daedan.festabook.place.domain.PlaceImage */update place_image 
    set
        deleted=?,
        deleted_at=?,
        image_url=?,
        place_id=?,
        sequence=?,
        updated_at=? 
    where
        id=?
2025-10-13 21:24:48.213 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingAspect - [Method End] className=PlaceImageController methodName=updateFestivalImagesSequence executionTime=242ms
2025-10-13 21:24:48.215 [http-nio-80-exec-2] INFO  c.d.f.g.logging.LocalLoggingFilter - [API End] statusCode=200 requestBody=[
  {
    "placeImageId": 103,
    "sequence": 5
  },
  {
    "placeImageId": 10000112,
    "sequence": 4
  },
  {
    "placeImageId": 10000113,
    "sequence": 2
  },
  {
    "placeImageId": 10000114,
    "sequence": 1
  },
  {
    "placeImageId": 10000115,
    "sequence": 3
  }
] executionTime=262ms

🔽 개선 후 코드

@Transactional
public PlaceImageSequenceUpdateResponses updatePlaceImagesSequence(
        Long festivalId,
        List<PlaceImageSequenceUpdateRequest> requests
) {
    validateDuplicatePlaceImageIds(requests);

    Map<Long, Integer> sequenceMap = requests.stream()
            .collect(Collectors.toMap(
                    PlaceImageSequenceUpdateRequest::placeImageId,
                    PlaceImageSequenceUpdateRequest::sequence
            ));

    List<PlaceImage> placeImages = placeImageJpaRepository.findAllById(sequenceMap.keySet());

    validateAllPlaceImagesExist(placeImages, sequenceMap);
    validateAllBelongsToFestival(placeImages, festivalId);
    validateAllBelongToPlace(placeImages);

    for (PlaceImage placeImage : placeImages) {
        placeImage.updateSequence(sequenceMap.get(placeImage.getId()));
    }

    Collections.sort(placeImages);

    return PlaceImageSequenceUpdateResponses.from(placeImages);
}

🔽 개선 후 Local 평균값

Local WAS -> Local DB

🔽 개선 후 Dev 평균값

Local WAS -> Dev DB

Dev WAS -> Dev DB

 

 

✅ 개선 결과

개선율 = (개선 전 실행 시간 - 개선 후 실행 시간) / 개선 전 실행 시간 * 100

• Local WAS → Dev DB (네트워크 경로 존재)
  • 네트워크를 경유하는 환경에서는, 동일 로직 내에서 5회의 쿼리 실행을 단일 쿼리로 개선한 결과 확연한 성능 향상이 나타났다.
  • (132.945643 - 85.389948) / 132.945643 * 100 = 35.77%
• Dev WAS → Dev DB (동일 VPC 내부 통신)
  • 네트워크를 타지 않는 환경에서는 개선 폭이 크지 않았다. 쿼리 수 자체가 적고, 가장 영향이 큰 다중 조회(5회) 쿼리에서 네트워크 왕복 비용이 제거되어 있기 때문이다. 향후 이미지 제한을 해제하면 쿼리 호출 빈도가 늘어나, 개선 전후 차이가 더 뚜렷해질 것으로 예상된다.
  • (21.783723 - 19.400191) / 21.783723 * 100 = 10.94%

→ 실제로 눈에 띄는 성능 향상은 적었지만, 이번 개선을 통해 네트워크 왕복 비용이 전체 성능에 미치는 영향이 상당히 크다는 점을 실측 데이터를 통해 확인할 수 있었다.

 

 

✅ 결론

실제로 서비스에서 실행 중인 쿼리를 개선한 결과를 직접 확인할 수 있어 재미있었다. 또한, 네트워크 왕복 비용이 크다는 사실은 이론으로만 알고 있었는데, 이번 경험을 통해 네트워크 비용의 유무에 따라 실제 성능 차이가 크게 발생함을 체감하며, 네트워크 비용이 시스템 성능에 미치는 영향이 상당하다는 것을 명확히 알 수 있었다.