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

Jae Hun Lee·2023년 4월 27일
0

parknav

목록 보기
4/8
post-thumbnail


  • 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 ?
              )
profile
기록을 남깁니다

0개의 댓글