[ParkNav] 예약 알고리즘 성능 개선

Jae Hun Lee·2023년 4월 27일

parknav

목록 보기

4/8

version 1, version 1-1

예약 가능 여부를 확인하기 위해 예약시간에 해당하는 매시간마다 예약현황DB에서 예약정보를 찾고, 주차현황DB에서 출차여부를 확인한다. → 예약시간만큼 쿼리가 반복해서 실행되므로, 예약시간이 길어질수록 로직 실행 속도가 느려진다.

예약 불가 시간대 구하는 로직 코드

@Query(value = "SELECT p FROM ParkBookingInfo p WHERE p.parkInfo.id = :parkId " +
        "AND ((p.startTime >= :startTime and p.startTime < :endTime) " +
        "OR (p.endTime > :startTime and p.endTime <= :endTime)" +
        "OR (p.startTime <= :startTime and p.endTime >= :endTime))")
List<ParkBookingInfo> getSelectedTimeBookingList(@Param("parkId") Long parkId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

private List<LocalDateTime> getNotAllowedTimeList(Long id, BookingInfoRequestDto requestDto, ParkOperInfo parkOperInfo) {

    List<LocalDateTime> notAllowedTimeList = new ArrayList<>();

    ParkSpaceInfo spaceInfo = mgtService.getParkSpaceInfo(parkOperInfo);

    long hours = Duration.between(requestDto.getStartDate(), requestDto.getEndDate()).toHours();
    LocalDateTime start = requestDto.getStartDate();
    // 선택한 시간만큼 반복
    for (int i = 0; i < hours; i++) {
        // 선택 시작 시간부터 한시간 단위로 예약 건수를 구한다.
        LocalDateTime time = start.plusHours(i);
        // 선택시간 사이 예약정보
        List<ParkBookingInfo> bookingList = parkBookingInfoRepository.getSelectedTimeBookingList(id, time, time.plusHours(1));
        // 예약정보 중 이미 출차한 차량 수는 제외한다.
        int bookingCnt = bookingList.size() - parkMgtInfoRepository.countByParkBookingInfoInAndExitTimeNotNull(bookingList);
        // 예약 건수가 예약 구역수보다 같거나 크면, 예약불가 시간 리스트에 추가한다.
        if (bookingCnt >= spaceInfo.getBookingCarSpace()) {
            notAllowedTimeList.add(time);
        }
    }

    return notAllowedTimeList;
}

version 2

시간별 예약현황DB(ParkBookingByHour) 테이블 추가 설계

CREATE TABLE `park_booking_by_hour` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `park_info_id` bigint DEFAULT NULL,
  `date` date NOT NULL,
  `time` int NOT NULL,
  `available` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_park_info_id` (`park_info_id`),
  CONSTRAINT `FK_park_info_id` FOREIGN KEY (`park_info_id`) REFERENCES `park_info` (`id`)
)

예약 가능 여부를 확인하기 위해 시간별 예약현황DB의 주차가능대수를 확인한다.
ParkBookingByHour 테이블에서 예약시간에 해당하는 주차가능대수(available) 컬럼 값을 확인하도록 QueryDSL 사용하여 로직을 구현했다. → 테이블 조회 속도는 빠르나, 예약정보 생성 후 ParkBookingByHour 에 데이터 생성 및 업데이트 추가 작업이 필요하다.

예약 불가 시간대 구하는 로직 코드

private List<LocalDateTime> getNotAllowedTimeList(Long parkId, BookingInfoRequestDto requestDto) {

    List<ParkBookingByHour> hourList = parkBookingByHourRepositoryCustom.findByParkInfoIdAndFromStartDateToEndDate(parkId, requestDto.getStartDate(), requestDto.getEndDate());

    return hourList.stream()
            .filter(hour -> hour.getAvailable() <= 0)
            .map(hour -> LocalDateTime.of(hour.getDate(), LocalTime.of(hour.getTime(), 0, 0)))
            .collect(Collectors.toList());
}

*findByParkInfoIdAndFromsStartDateToEndDate QueryDSL 쿼리 코드*

@Slf4j
@Repository
@RequiredArgsConstructor
public class ParkBookingByHourRepositoryImpl implements ParkBookingByHourRepositoryCustom {

    private final JPAQueryFactory jpaQueryFactory;
    private final QParkBookingByHour qParkBookingByHour = QParkBookingByHour.parkBookingByHour;
    private final QParkInfo qParkInfo = QParkInfo.parkInfo;

    @Override
    public List<ParkBookingByHour> findByParkInfoIdAndFromStartDateToEndDate(Long parkInfoId, LocalDateTime startDate, LocalDateTime endDate) {

        LocalDate startDay = startDate.toLocalDate();
        LocalDate endDay = endDate.toLocalDate();
        long days = Period.between(startDay, endDay).getDays();
        int startTime = startDate.getHour();
        int endTime = endDate.getHour();
        if (endDate.toLocalTime().getMinute() == 0 && endDate.toLocalTime().getSecond() == 0) {
            endTime = endDate.minusHours(1).getHour();
        }

        List<ParkBookingByHour> result = new ArrayList<>();
        if (days == 0) {
            result = jpaQueryFactory.selectFrom(qParkBookingByHour)
                    .innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
                    .fetchJoin()
                    .where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
                            .and(qParkBookingByHour.date.eq(startDay))
                            .and(qParkBookingByHour.time.between(startTime, endTime)))
                    .fetch();
        } else {
            List<ParkBookingByHour> resultFirstDay = jpaQueryFactory.selectFrom(qParkBookingByHour)
                    .innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
                    .fetchJoin()
                    .where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
                            .and(qParkBookingByHour.date.eq(startDay))
                            .and(qParkBookingByHour.time.between(startTime, 23)))
                    .fetch();

            List<ParkBookingByHour> resultLastDay = jpaQueryFactory.selectFrom(qParkBookingByHour)
                    .innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
                    .fetchJoin()
                    .where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
                            .and(qParkBookingByHour.date.eq(endDay))
                            .and(qParkBookingByHour.time.between(0, endTime)))
                    .fetch();

            result.addAll(resultFirstDay);
            result.addAll(resultLastDay);

            if (days > 1) {
                List<ParkBookingByHour> resultMiddleDays = jpaQueryFactory.selectFrom(qParkBookingByHour)
                        .innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
                        .fetchJoin()
                        .where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
                                .and(qParkBookingByHour.date.between(startDay.plusDays(1), endDay.minusDays(1))))
                        .fetch();
                result.addAll(resultMiddleDays);
            }
        }

        return result;
    }
}

예약가능여부 판단하기

☑️ 테스트 설정

4/13 17:00 ~ 4/16 17:00 3일 예약 진행
선택한 예약시간으로 예약이 가능한지 확인하는 로직 실행속도와 전체 예약 프로세스 실행속도를 측정한다.

version 1, version 1-1

실행 속도
- 예약가능여부 확인 : 평균 1711ms
- 전체 예약 프로세스 : 평균 1754ms

Hibernate 실행결과

아래 쿼리문을 예약시간만큼 반복하여 실행

Hibernate: 
    select
        parkbookin0_.id as id1_2_,
        parkbookin0_.car_num as car_num2_2_,
        parkbookin0_.end_time as end_time3_2_,
        parkbookin0_.park_info_id as park_inf5_2_,
        parkbookin0_.start_time as start_ti4_2_,
        parkbookin0_.users_id as users_id6_2_ 
    from
        park_booking_info parkbookin0_ 
    where
        parkbookin0_.park_info_id=? 
        and (
            parkbookin0_.start_time>=? 
            and parkbookin0_.start_time<? 
            or parkbookin0_.end_time>? 
            and parkbookin0_.end_time<=? 
            or parkbookin0_.start_time<=? 
            and parkbookin0_.end_time>=?
        )
Hibernate: 
    select
        count(parkmgtinf0_.id) as col_0_0_ 
    from
        park_mgt_info parkmgtinf0_ 
    where
        (
            parkmgtinf0_.park_booking_info_id in (
                ?
            )
        ) 
        and (
            parkmgtinf0_.exit_time is not null
        )

version 2

실행 속도
- 예약가능여부 확인 : 평균 68ms
- 전체 예약 프로세스 : 평균 1046ms → 예약가능여부 확인하는 속도는 빠르나, 예약정보 생성 후 ParkBookingByHour 에 데이터를 생성 및 업데이트하는 시간이 추가로 소요됨

Hibernate 실행결과

예약 시간이 길어도 아래 쿼리 3개만 실행됨

Hibernate: 
    select
        parkbookin0_.id as id1_2_0_,
        parkinfo1_.id as id1_4_1_,
        parkbookin0_.available as availabl2_2_0_,
        parkbookin0_.date as date3_2_0_,
        parkbookin0_.park_info_id as park_inf5_2_0_,
        parkbookin0_.time as time4_2_0_,
        parkinfo1_.address1 as address2_4_1_,
        parkinfo1_.address2 as address3_4_1_,
        parkinfo1_.la as la4_4_1_,
        parkinfo1_.lo as lo5_4_1_,
        parkinfo1_.name as name6_4_1_ 
    from
        park_booking_by_hour parkbookin0_ 
    inner join
        park_info parkinfo1_ 
            on parkbookin0_.park_info_id=parkinfo1_.id 
    where
        parkbookin0_.park_info_id=? 
        and parkbookin0_.date=? 
        and (
            parkbookin0_.time between ? and ?
        )
Hibernate: 
    select
        parkbookin0_.id as id1_2_0_,
        parkinfo1_.id as id1_4_1_,
        parkbookin0_.available as availabl2_2_0_,
        parkbookin0_.date as date3_2_0_,
        parkbookin0_.park_info_id as park_inf5_2_0_,
        parkbookin0_.time as time4_2_0_,
        parkinfo1_.address1 as address2_4_1_,
        parkinfo1_.address2 as address3_4_1_,
        parkinfo1_.la as la4_4_1_,
        parkinfo1_.lo as lo5_4_1_,
        parkinfo1_.name as name6_4_1_ 
    from
        park_booking_by_hour parkbookin0_ 
    inner join
        park_info parkinfo1_ 
            on parkbookin0_.park_info_id=parkinfo1_.id 
    where
        parkbookin0_.park_info_id=? 
        and parkbookin0_.date=? 
        and (
            parkbookin0_.time between ? and ?
        )
Hibernate: 
    select
        parkbookin0_.id as id1_2_0_,
        parkinfo1_.id as id1_4_1_,
        parkbookin0_.available as availabl2_2_0_,
        parkbookin0_.date as date3_2_0_,
        parkbookin0_.park_info_id as park_inf5_2_0_,
        parkbookin0_.time as time4_2_0_,
        parkinfo1_.address1 as address2_4_1_,
        parkinfo1_.address2 as address3_4_1_,
        parkinfo1_.la as la4_4_1_,
        parkinfo1_.lo as lo5_4_1_,
        parkinfo1_.name as name6_4_1_ 
    from
        park_booking_by_hour parkbookin0_ 
    inner join
        park_info parkinfo1_ 
            on parkbookin0_.park_info_id=parkinfo1_.id 
    where
        parkbookin0_.park_info_id=? 
        and (
            parkbookin0_.date between ? and ?
        )

Jae Hun Lee

기록을 남깁니다

이전 포스트

[ParkNav] 주차장 조회 성능 테스트

다음 포스트