페이지네이션 성능 비교 : LIMIT-OFFSET vs NO-OFFSET

인트로

• 친구 게시글 조회 기능에서의 페이지네이션을 구현하기에 앞서, LIMIT-OFFSET과 NO-OFFSET 방식의 성능을 비교해 보았습니다.
• 게시글 테이블에 1억 건의 더미 데이터를 추가한 후, MySQL의 EXPLAIN 명령어와 Query Profiling을 활용하여 첫번째 페이지와 마지막 페이지에 대한 쿼리 실행 계획 및 쿼리 성능을 분석했습니다.

---

LIMIT-OFFSET 방식

쿼리문

쿼리 실행 계획

• 첫번째 페이지에서는 클러스터드 인덱스를 사용하지만, 마지막 페이지에서는 Full Table Scan이 발생함을 확인할 수 있었습니다.

쿼리 실행 시간

• 첫번째 페이지 : [ 156.68s ] , 마지막 페이지 : [ 295.17s ]
• 첫번째 페이지와 마지막 페이지의 조회 시간은 약 2배 정도의 차이를 보였습니다.
• 페이지 번호가 커질수록 조회 성능이 떨어지는 LIMIT-OFFSET 방식의 문제점을 확인할 수 있었습니다.

---

NO-OFFSET 방식

쿼리문

쿼리 실행 계획

• Limit-Offset 방식과 달리, 마지막 페이지를 조회할 때도 클러스터드 인덱스가 사용되는 것을 확인할 수 있습니다.

쿼리 실행 시간

• 첫번째 페이지 : [ 0.002944s ], 마지막 페이지 [ 0.003210s ]
• No-Offset 방식은 첫번째 페이지와 마지막 페이지의 조회 시간이 사실상 거의 동일했습니다.

---

정리

• Limit-Offset 방식은 기본적으로 Offset만큼 레코드를 조회한 후 필요하지 않은 부분을 제외하는 방식으로 동작하기 때문에, 페이지 번호가 커질수록(오프셋이 커질수록) 성능 저하가 발생하는 문제점이 존재합니다.
• 이러한 문제점을 해결하기 위해, 조회 시작 부분을 인덱스로 빠르게 탐색하여 매번 첫 페이지만 조회하는 No-Offset 페이징 방식을 활용할 수 있습니다.
• 더미 데이터 1억 건을 기준으로 성능을 측정해 본 결과, Limit-Offset 방식은 첫번째 페이지와 마지막 페이지의 조회 시간이 약 2배 정도의 차이를 보였습니다.
• 또한, 마지막 페이지를 조회하는데 Limit-Offset 방식은 295초가 소요되는 반면에 No-Offset 방식은 0.003초가 소요되었습니다.
• Offset 페이징은 구현이 단순하다는 장점이 있지만, 성능이 중요한 경우 No-Offset 방식으로 페이지네이션을 구현하는 것이 좋습니다.

---

부록 : Descending Index 생성 시도

CREATE INDEX desc_idx_article_id ON article(article_id DESC);

• No-Offset 방식으로 실행 계획을 분석한 결과, 사실상 마지막 페이지를 제외하고는 전부 Backward Index Scan이 발생함을 확인했습니다.
• B+Tree 인덱스에서 페이지 내부의 레코드들은 서로 단방향으로 연결되어 있어, Backward Index Scan이 성능상 좋지 않다는 내용을 발견했습니다.
• 이에, 기본키에 대해 Descending Index를 생성한 후 다시 한번 성능을 측정했습니다.

쿼리 실행 계획

• 이상하게도 마지막 페이지를 제외하고, 매번 인덱스 레인지 스캔이 발생했습니다.

쿼리 실행 시간

• 쿼리 자체를 실행하는데 오류가 발생했습니다. 심지어 Descending Index을 삭제한 이후로도 동일한 오류가 발생했습니다.
• Descending Index가 성능 저하를 가져온 것인지, 아니면 인덱스 생성 과정에서 오류가 발생한 것인지 판단할 수가 없었습니다.
• 아직 이러한 문제의 원인은 정확하게 파악하지 못했지만, 예전에 1000만건의 데이터에 대해 인덱스를 생성하는데 오랜 시간이 걸렸던 것을 감안하면 아무래도 1억건 데이터에 대해 인덱스를 생성하는 과정에서 내부적으로 오류가 발생한 것이 원인이지 않을까 추측하고 있습니다.
• 추후 데이터를 1000만건으로 줄여, 다시 한번 새로운 환경에서 성능 분석을 진행해 보아야 할 것 같습니다.