[MySQL] LIMIT OFFSET 성능 튜닝

#MySQL

데이터 리스트를 뽑는 API를 구현한다면 보통 LIMIT OFFSET으로 페이지네이션 처리를 많이 한다.

하지만 MySQL에서는 무작정 Limit Offset만 쓰다가는 성능에서 손해를 많이 볼 수가 있다.

정확히는, 데이터가 수십만개 이상으로 많은 상태에서 OFFSET이 뒤로 갈수록 느려지는 문제인데, 다른 DB는 어떤지 모르겠다.

한번 예시와 함께 살펴보겠다.

테스트에 사용한 테이블에는 약 200백만개의 데이터가 들어있는 상태다.

그냥 LIMIT OFFSET 쿼리를 한번 걸어보겠다.

약 5초가 걸렸다. 이건 원래 느릴만한 거라서 이정도는 걸릴 수 있는 쿼리다.
이 상태에서는 문제가 없으나

오프셋이 뒤로 가면 문제가 생긴다.

세상에. 5초 걸리던게 18초가 걸렸다.
게다가 뒤로 갈수록 점점 더 느려진다.

이게 왜 그런가 하면,
이러면 풀스캔을 때린 다음에 전부 정렬을 하고, LIMIT OFFSET으로 자르는데, 정렬 단계에서 성능 부하가 상당히 걸리기 때문이라고 한다.

이걸 좀더 빠르게 개선하고 싶다면, 방법은 의외로 간단하다.
기존 쿼리를 통째로 서브쿼리로 감싸고, 뻥 조인 한번 해서 UNIQUE KEY로 인덱스를 타게 만들면 된다.

저기서 fake.id는 UNIQUE KEY다.
이러면 이제 그냥 풀스캔이 아니라 풀 인덱스 스캔을 태우게 돼서, 정렬시간이 크게 단축된다. 인덱스로 긁어오는 단계에서 이미 정렬을 처리할 수 있기 때문이다.

자, 이제 8초밖에 걸리지 않는다.

여전히 뒤로갈수록 조금씩 느려지긴 하는데, 그 차이가 훨씬 좁아진다.

다만, 인덱스 스캔이란게 범위가 크면 클수록 비효율적이다보니 이것도 임계점을 넘어가면 오히려 느려질 수도 있다.