이번 포스팅에서는 인덱스를 적용하여 GPS 좌표 목록 조회 쿼리의 성능 개선을 해보겠습니다.
인덱스 개념에 대해서 알고 싶으신 분은 아래 자료를 참고해주시면 좋을 것 같습니다.
📌 인덱스 적용하기
현재 GPS 테이블의 데이터에는 31만개의 데이터가 들어있습니다.
먼저 인덱스 적용하기 전의 쿼리 성능을 보겠습니다. 아래 쿼리를 실행해보겠습니다.
select * from gps where jobpost_id = 33 order by coordinate_time limit 100 offset 1000;인덱스 적용 전 실행 결과
위의 쿼리를 실행하는데 결과는 0.125 sec이 걸리는 것을 확인하실 수 있습니다.
그리고 쿼리 실행 계획을 봤을 때 type 형태가 ALL이고 Extra가 Using filesort이기 때문에 데이터가 많아질수록 성능에 직접적으로 영향을 줄 가능성이 높습니다.
type이 ALL인 경우 테이블을 Full Scan 한다는 의미이며, Extra가 Using filesort인 경우 인덱스의 정렬을 사용하는 것이 아니라 MySQL 서버 자체가 Quick sort를 사용하여 행 정렬을 한다는 의미입니다.
이제 인덱스를 적용하여 쿼리를 실행해보도록 하겠습니다. 아래의 명령어로 인덱스를 설정해줍니다.
create index idx_jobpost_id_coordinate_time on gps (jobpost_id, coordinate_time);GPS 테이블 목록을 조회할 때 where 조건절에 jobpost_id에 대한 조건이 있고, 정렬 조건에 coordinate_time 순으로 정렬을 하기 때문에 jobpost_id, coordinate_time 두 컬럼에 대해 멀티 컬럼 인덱스를 걸어주었습니다.
인덱스 적용 전과 똑같은 쿼리로 성능을 비교해보겠습니다.
select * from gps where jobpost_id = 33 order by coordinate_time limit 100 offset 1000;인덱스 적용 후 실행 결과
쿼리를 실행하는데 결과는 0.016 sec이 걸리는 것을 확인하실 수 있습니다. 인덱스 적용 전의 실행 결과에 비해 약 87.2 % 성능이 개선된 것을 확인할 수 있습니다.
또한 쿼리 실행 계획을 조회했을 시 type 형태가 ref로 바뀌었고 idx_jobpost_id_coordinate_time 인덱스를 정상적으로 타는 것을 확인할 수 있습니다.
⚙️ 실제 테스트 해보기
실제 스프링 코드에서도 조회 로직이 개선되었는지 확인해보도록 하겠습니다.
💡 GpsController
@RestController
@RequiredArgsConstructor
@RequestMapping("/api")
public class GpsController {
private final GpsService gpsService;
...
@GetMapping("/gps/job-post/{job-post-id}")
public ResponseEntity getGpsList(@PathVariable("job-post-id") Long jobPostId,@PageableDefault(size = 100,sort="coordinateTime", direction = org.springframework.data.domain.Sort.Direction.ASC) Pageable pageable) {
Page<GpsResponseDto> gpsList = gpsService.getGpsList(jobPostId, pageable);
return new ResponseEntity(gpsList, HttpStatus.OK);
}
}💡 GpsService
@Service
@RequiredArgsConstructor
@Transactional
@Slf4j
public class GpsService {
private final GpsRepository gpsRepository;
...
/**
* jobPostId 조건 + coordinateTime에 대해 ASC 정렬 조건 페이지네이션 조회
*/
public Page<GpsResponseDto> getGpsList(Long jobPostId, Pageable pageable) {
return gpsRepository.findGpsListByJobPostId(jobPostId,pageable);
}
}💡 GpsRepository
public interface GpsRepository extends JpaRepository<Gps, Long> {
@Query("select new com.doggyWalky.doggyWalky.gps.dto.response.GpsResponseDto(g.id, g.latitude,g.longitude,g.coordinateTime) from Gps g where g.jobPostId = :jobPostId")
Page<GpsResponseDto> findGpsListByJobPostId(@Param("jobPostId") Long jobPostId, Pageable pageable);
}💡 TestCode
@SpringBootTest
@Transactional
@TestPropertySource(properties = {
"spring.jpa.properties.hibernate.cache.use_second_level_cache=false",
"spring.jpa.properties.hibernate.cache.use_query_cache=false"
})
class GpsRepositoryTest {
@Autowired
private GpsRepository gpsRepository;
@DisplayName("인덱싱 적용 테스트")
@Transactional(propagation = Propagation.NOT_SUPPORTED)
@Test
void test() {
long startTime = System.currentTimeMillis();
Page<GpsResponseDto> gpsListByJobPostId = gpsRepository.findGpsListByJobPostId(33L, PageRequest.of(10, 100));
long endTime = System.currentTimeMillis();
assertThat(gpsListByJobPostId.getContent().size()).isEqualTo(100);
long duration = endTime - startTime;
System.out.println("Execution time: " + duration + " ms");
}
}인덱스 적용 전
인덱스 적용 후
인덱스 적용 전 조회 쿼리 실행 시간은 357ms이고 인덱스 적용 후 조회 쿼리 실행 시간은 225ms로 약 37%의 성능 개선이 이루어졌습니다.
✏️ 성능 테스트
Jmeter를 사용하여 부하테스트도 진행해보았습니다.
인덱스 적용 전
인덱스 적용 후
인덱스 적용 전과 후를 비교했을 때
평균 응답 시간은 인덱스 미적용 시 9839.48 ms에서 인덱스 적용 시 1219.38 ms로 약 87.6% 개선되었습니다.
트랜잭션(초당)은 인덱스 미적용 시 5.00에서 인덱스 적용 시 39.50으로 약 690% 개선되었습니다.
Network 사용량은 수신의 경우 인덱스 미적용 시 51.61 KB/sec에서 인덱스 적용 시 408.08 KB/sec로 증가하였고 전송의 경우 인덱스 미적용 시 1.54 KB/sec에서 인덱스 적용 시 12.19 KB/sec로 증가하였기에 인덱스 적용 시 더 많은 요청을 처리하고 결과를 반환하는 데 필요한 네트워크 사용량이 증가했음을 나타냅니다.
결과적으로 인덱스 적용이 데이터베이스 쿼리 성능을 크게 개선했음을 확인할 수 있었습니다.
☕ 마치며
인덱스를 적용할 때 INSERT 작업이 많고 UPDATE나 DELETE 작업이 적은 경우 성능에 유리합니다.
현재 진행중인 프로젝트에서 GPS 좌표 데이터는 업데이트와 삭제가 자주 발생하지 않기 때문에 인덱스 갱신으로 인한 성능 저하가 크지 않다고 판단하였고, 테스트 결과 성공적인 것을 확인할 수 있었습니다. 앞으로 더욱 깊이 있게 성능 개선에 대한 공부를 하며 차근차근 적용해볼 계획입니다. 감사합니다.
