[Anifriends] 페이지네이션 성능개선

🚀 개요

데브코스 4기에서 동물 보호소와 봉사자 매칭 플랫폼인 AniFriends 를 개발하면서 경험한 페이지네이션 성능 개선기이다. 봉사자는 봉사 모집 목록을 조회할 수 있으며 서버에서는 모집 목록을 페이지네이션으로 응답한다. 100만개의 데이터를 저장하여 조회 성능을 측정하였다.

🚨 개선 전

offset 이 각각 0/500,000/900,000일 때 다음과 같이 약 5~7초가 소요된다.

offset: 0 / size: 20

offset: 500,000 / size: 20

offset: 900,000 / size: 20

 

✅  인덱스

우선, 정렬 기준인 created_at 에 내림차순 인덱스를 적용해보자

created_at desc 인덱스 적용

인덱스 적용 후의 소요시간은 다음과 같다.

offset: 0 / size: 20

offset: 500,000 / size: 20

offset: 900,000 / size: 20

offset 이 0일 경우 소요시간이 5.3초에서 0.1초로 약 50배 감소되었다. 하지만 offset 이 500,000/900,000일 경우는 전혀 개선되지 않았다. 이유가 무엇일까? 실행계획을 확인해보자

offset: 0 / size: 20

offset: 500,000 / size: 20

offset: 900,000 / size: 20

• type 을 보면 offset 이 0일 때는 index 이고 500,000/900,000일 때는 ALL이다.
• rows 를 보면 offset 이 0일 때는 20이고 500,000/900,000일 때는 약 1,000,000이다.
• Extra 를 보면 500,000/900,000일 때 Using filesort(추가적인 정렬 작업을 의미) 가 존재한다.

이를 통해 offset 이 어느정도 커지면 index 를 사용하지 않고 table full scan 으로 데이터를 가져온다는 것을 확인할 수 있다. Mysql 공식문서에 따르면, 쿼리에서 대부분의 행에 액세스해야 하는 경우 인덱스를 통해 작업하는 것보다 순차적으로 읽는 것이 더 빠르다고 판단한다고 한다. 이를 해결하기 위해 커버링 인덱스와 커서 방식을 적용해보도록 하자

✅  커버링 인덱스

커버링 인덱스란 쿼리의 모든 항목이 인덱스 컬럼으로 이루어져 있는 것을 의미한다. 커버링 인덱스를 사용할 경우 실제 데이터 블록에 접근할 필요 없이 필요한 데이터를 불러올 수 있다. 커버링 인덱스를 사용한 쿼리는 다음과 같다.

select * from recruitment r
	join (select recruitment_id from recruitment
    		order by created_at desc
            	limit [offset], [size]) temp on r.recruitment_id = temp.recruitment_id
    	join shelter s r.shelter_id = s.shelter_id;

첫 번째 join 의  서브쿼리를 살펴보면 select 컬럼은 pk 인 recruitment_id 이고 order by 컬럼은 인덱스를 적용한 created_at 이다. 즉, 쿼리의 모든 항목이 인덱스 컬럼으로 이루어져 있다. 이제 소요 시간을 확인해보자.

offset: 0 / size: 20

offset: 500,000 / size: 20

offset: 900,000 / size: 20

커버링 인덱스를 적용한 결과 offset 이 500,000/900,000 인 경우도 약 40배가 감소된 0.2~0.3초가 소요되는 것을 확인할 수 있다. 그렇다면 커버링 인덱스를 적용한 후의 실행계획은 어떻게 될까? offset 이 900,000 일 경우를 확인해보자.

offset 이 900,000 일 경우의 실행 계획

select_type 이 DERIVED(from 절의 서브쿼리) 인 행의 Extra 를 보면 Using index 이다. 이를 통해 해당 부분에 커버링 인덱스가 적용된 것을 알 수 있다.

✅  커서 방식

커서방식이란 offset 을 0 으로 설정하고 특정 지점(커서)을 where 문으로 설정하여 그 지점부터 특정 개수만큼 데이터를 가져오는 방식이다. offset 이 0이기 때문에 항상 첫 페이지를 조회하는 것과 동일한 성능을 낼 수 있다. 봉사 모집글의 정렬 기준은 created_at 기준 내림차순이고 created_at 마저 같을 경우 recruitment_id 기준 내림차순이다. 커서방식을 적용한 쿼리는 다음과 같다. 

select * from recruitment r
    join shelter s on s.shelter_id=r.shelter_id
    where r.created_at < [created_at 커서]
        or (r.created_at = [created_at 커서] and r.recruitment_id < [recruitment_id 커서])
    order by r.created_at desc
    limit [size];

이제 offset 이 900,000 인 커서를 선택해서 소요시간을 측정해보자. limit 899999, 1 을 통해 900,000 번째의 커서를 가져올 수 있다.

899,999 번째 데이터

실행 결과는 다음과 같다.

offset: 0 / size: 20

커버링 인덱스보다 50배가 감소된 0.1 초가 소요되는 것을 확인할 수 있다. 

애니프렌즈의 봉사 모집 목록 조회같은 경우는 무한 스크롤 방식이며 offset 을 필요로 하지않기 때문에 성능이 조금 더 나은 커서방식을 선택하였다.