DB Indexing을 통한 UserExerciseService 메소드의 성능 개선

TouchMyBody의 UserExerciseService에는 운동일자와 유저의 ID로 UserExercise를 가져오는 쿼리를 실행하는 몇개의 메소드가 존재한다.

아래가 해당 메소드들이다.

public interface UserExerciseRepository extends JpaRepository<UserExercise,Long>{

    /**
     * User ID, UserExercise date 를 만족하는 UserExercise들 찾기 
     * @param userId 찾으려는 UserExercise의 User ID
     * @param date 찾으려는 UserExercise의 date
     * @author kimgun-yeong
     */
    @Query("SELECT ue FROM UserExercise ue "
            + "INNER JOIN ue.user u "
            + "WHERE (u.id = :userId) "
            + "and (ue.date = :date) ")
    public List<UserExercise> findUserExercisesByUserIdAndDate(@Param("userId") Long userId, @Param("date") Calendar date);

    /**
     * 특정 유저의 특정 날짜 사이에 존재하는 운동정보의 날짜들을 가져오는 쿼리 
     * @param start 검색 시작 날짜
     * @param end 검색 종료 날짜
     * @author kimgun-yeong
     */
    @Query("SELECT ue.date FROM UserExercise ue "
            + "INNER JOIN ue.user u "
            + "WHERE (u.id = :userId) "
            + "AND (ue.date >= :startDay) "
            + "AND (ue.date <= :endDay)")
    public List<Calendar> findUserExercisesIdForDayToDay(@Param("userId") Long userId, @Param("startDay") Calendar start, @Param("endDay") Calendar end);

  //(중략)
}

UserExercise 테이블에 기존에 존재하는 인덱스 테이블은 테이블 생성할때 기본적으로 생성된 PK와 FK에 대한 인덱스 테이블뿐이었다. 위 코드에 있는 쿼리문을 보면 where 절에 userId(FK)말고도 date 컬럼이 있다. 그래서 userId와 date 컬럼으로 인덱스를 구성해보기로 했다.

위 2가지 쿼리 중 아래의 findUserExercisesIdForDayToDay 쿼리를 통해 추가된 인덱스를 활용했을 때와 하지 않았을 때의 성능을 비교해보기로 했다.

테스트 세팅 mysql 에 따로 테스트용 스키마를 추가해 user 테이블에는 1000개 정도의 row를 추가했고, 각 user 마다 360개의 userExercise row 를 추가했다. 총 userExercise 개수는 36만개라 볼 수 있다. 그리고 userExercise에서 date 어트리뷰트 값은 모두 테스트의 편의를 위해 2021년으로 통일했다. 물론 월과 일은 통일하지 않았다.
인덱스 추가

userExercise 테이블에 인덱스 테이블을 추가하기 위한 JPA코드를 추가했다.

@Entity
@Table(indexes = @Index(name = "uIdAndDate", columnList = "user_id, date"))
public class UserExercise extends BaseEntity {
}

인덱싱 컬럼 순서를 저렇게 한 이유는 2가지가 있다. 첫번째는 카디널리티가 더 높은 user_id 컬럼을 첫번째로 하고 date를 그 다음으로 했다. 두번째는 사실 이 이유로 인해 다른 이유가 없더라도 이러한 순서로 했을 것이다. 그 이유가 뭐냐면 between이나 >, < 같은 범위 연산이 들어간 where 절은 인덱스가 적용이 되지만 그 이후의 컬럼에 대해서는 인덱싱이 적용되지 않는다.

mysql 옵티마이저를 통한 비교

인덱스 추가 전과 후 동일한 쿼리에 대해서 mysql 옵티마이저가 계산한 비용을 비교해보았다.

인덱스 미사용 결과

인덱스 사용 결과

-> 기본 인덱스만이 있다면 위 쿼리에서 적용될 인덱스가 따로 없기에 table full scan을 할 수 밖에 없어 옵티마이저가 계산한 예상 코스트가 굉장히 크게 나온다.

서비스 클래스 메소드를 통한 실제 성능 비교

아래는 테스트 코드이다.

       @Test
    public void test() {

        List<User> allUsers = userRepository.findAll();

        long beforeTime = System.nanoTime();
        getAllUsers2021UserExercises(allUsers);
        long afterTime = System.nanoTime();

        System.out.println("Execution time with index: " + (afterTime - beforeTime));

    }

    private void getAllUsers2021UserExercises(List<User> allUsers) {
        for(User user: allUsers) {
            for(int month = Calendar.JANUARY; month <= Calendar.DECEMBER; month++) {
                userExerciseService.findIsDoneDTOByUserIdAndYearAndMonth(user.getId(), 2021, month);
            }
        }
    }

코드에 대해 간략히 설명하자면 해당 쿼리를 실행하는 서비스 클래스 메소드를 모든 유저, 그리고 2021년의 모든 달에 대해서 실행하는 테스트 케이스다. (총 유저수 1000명) * (1년은 12달) = 12000 번의 쿼리를 실행하게 된다. 실행 시간은 nanoTime으로 계산하였다.

인덱스 사용 전 (Entitiy 클래스에 인덱스 코드 추가 전에 진행)

인덱스 사용 후

테스트 결과 약 39% 정도 빨라진것을 볼 수 있다.

반영 커밋 : https://github.com/Gunyoung-Kim/Touch-My-Body/commit/d4b90576b97b474c750c35d4cbdab4417a8e1a81

Gunyoung-Kim / Touch-My-Body

DB Indexing을 통한 UserExerciseService 메소드의 성능 개선 #32