[TODOMON] EP.11 인덱스를 통한 투두 조회 API 성능 개선 feat. 인덱스 컨디션 푸시다운

이전 포스팅에 이어 이번에는 투두 조회 API의 성능을 개선해보자!

개선 전 상황

특별한 개선 없이도 1500만 개의 todo와 750만 개의 todo_instance가 있는 상황에서 일간 조회, 주간 조회, 월간 조회 API의 성능은 다음과 같이 준수하게 나왔다.

• 평소 트래픽(Vusers: 29)
  • TPS: 556.2
  • MTT: 45.43ms
• 최대 트래픽(Vusers: 86)
  • TPS: 587.5
  • MTT: 139.77ms

Vuser가 증가함에도 불구하고 TPS는 늘지 않고 MTT만 증가한 것을 보니 리소스 한계로 인한 포화점(Saturation Point)을 넘었음을 확인할 수 있었다. 이미 목표로 하는 성능을 뽑아내고 있지만, 개인적인 욕심에 조금 더 최적화를 하고 싶었다!

 

포화점 문제를 해결하기 위해선 로드밸런싱이나 스케일 업, 쿼리 튜닝, 인덱스 추가 등의 방법이 있을 것이다. 하지만, 현재는 로컬 PC로 테스트 중이기에 스케일 업은 불가능하고, 로드밸런싱을 도입하기에는 새로운 기술을 학습해야 하기에 가장 먼저 적용해볼 수 있는 쿼리 튜닝과 인덱스 추가를 해보도록 결정했다!

 

(쿼리 튜닝을 먼저 시도하려 했으나.. 이미 내가 할 수 있는 한 최적화를 시켜둔 상태라 인덱스만 추가해보자..!)

---

인덱스 적용 (feat. 인덱스 컨디션 푸시다운)

필요한 인덱스를 파악하기 위해 사용되는 쿼리를 확인해보았고, 발생하는 쿼리는 다음과 같았다.

-- 투두 조회 예시 쿼리 
SELECT *
FROM todo
WHERE writer_id = 20 
    AND start_at BETWEEN '2024-09-01 00:00:00' AND '2024-09-07 23:59:59.001' 
    AND repeat_info_id IS NULL;

-- 투두 인스턴스 조회 예시 쿼리
SELECT *
FROM todo_instance ti1_0
    JOIN todo t1_0 ON t1_0.id=ti1_0.todo_id
    JOIN repeat_info ri1_0 ON ri1_0.id=t1_0.repeat_info_id
WHERE t1_0.writer_id = 31029 
    AND ti1_0.start_at BETWEEN '2024-09-01 00:00:00' AND '2024-09-07 23:59:59.001';

 

일간, 주간, 월간 조회 모두 동일한 쿼리에서 start_at의 범위만 바뀌도록 해두었기에 최적화할 포인트가 크게 줄었다. (이미 나름의 시행착오를 통해 쿼리튜닝을 해두었음)

 

먼저 투두 조회 쿼리의 경우 EXPLAIN을 통해 실행 계획을 확인해보니 writer_id에 걸려있는 FK 인덱스를 사용 중인 것을 확인할 수 있었다.

 

여기서 FKdh3a8yy7cvmmc9cxnwv4wxwai가 writer_id에 걸린 FK 인덱스이다. 나름 준수하게 성능을 보여주고는 있지만, start_at과 repeat_info_id에 대한 정보는 인덱스에 없기에 다음과 같이 동작하게 된다.

 

FK 인덱스만 사용하는 경우

1. 스토리지 엔진(InnoDB)에서 FK 인덱스를 통해 writer_id와 일치하는 행을 1차로 걸러준다.
2. 이후 start_at과 repeat_info_id 조건에 해당하는 데이터를 찾기 위해 MySQL 엔진은 writer_id가 일치하는 행을 모두 찾은 후 start_at과 repeat_info_id 조건을 만족하는 행을 찾는다. (= 2차 거르기)

결국 실제 테이블을 모두 확인하며 필터링을 해야 한다는 것이 문제다.. 투두 조회 API의 경우 이 서비스의 매우 핵심적인 기능이며 가장 많이 호출될 API이다. API 호출마다 매번 실제 테이블에서 필터링 및 조회 작업이 발생하는 것을 방지하기 위해 start_at과 repeat_info_id에 대한 인덱스를 걸어주자!

 

-- 인덱스 생성
CREATE INDEX todo_writer_id_start_at_repeat_info_id_index ON todo (writer_id, start_at, repeat_info_id);

 

이후 다시 EXPLAIN을 통해 실행계획을 확인해보니 결과는 다음과 같았다.

Extra의 값을 보면 Using where에서 Using index condition으로 변경된 것을 확인할 수 있는데, 이는 인덱스 컨디션 푸시다운에 의한 것이다.

인덱스 컨디션 푸시다운

1. 스토리지 엔진에서 우리가 생성한 인덱스를 통해 모든 조건에 부합하는 데이터들을 걸러준다.
2. MySQL 엔진은 스토리지 엔진으로부터 받은 필터링된 데이터를 그대로 활용하여, 이 데이터와 일치하는 행들을 조회하기만 하면 된다. (SELECT *이니까) 따로 실제 테이블에서 필터링 작업을 수행하지 않는다.

이를 통해 2차 거르기 작업을 없애 더욱 최적화 할 수 있었다! SELECT *을 필요한 데이터만 조회하도록 변경할 수도 있을 것이고, 극한까지 조회 최적화를 한다면 커버링 인덱스까지 도입할 수도 있겠지만, 여기까지 하기엔 이미 충분히 좋은 성능을 보여주고 있어 오버튜닝이 될 위험이 있다. 이정도로 만족하자!

 

TodoInstance에 대해서도 똑같은 원리로 다음의 쿼리를 통해 인덱스를 생성해주자

create index todo_instance_todo_id_start_at_index on todomon.todo_instance (todo_id, start_at);

 

todo_id는 JOIN 시에 사용되고, start_at은 WHERE 절에서 사용되고 있으므로 이 순서에 맞게 복합 인덱스를 생성해주었다.

이후 실행 계획을 보니 Using index condition, 즉 인덱스 컨디션 푸시다운이 발생하고 있다는 것을 확인할 수 있었다!

 

나름 최적화 된 것 같으니 다시 성능 테스트를 진행해보니 결과는 다음과 같았다.

개선 후 결과 비교

개선 전

개선 전

개선 후

개선 후

• TPS: 558 → 813 (약 45.7% 개선)
• MTT: 45.43ms → 27.27ms (약 39.97% 개선)