페이지기반 페이지네이션 쿼리를 최적화하고 싶습니다.

비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전 (SQL 튜닝편)

커버링 인덱스(Covering Index)란?

해결된 질문

작성

수정됨

게시판 비슷한 기능을 구현중인데 아무래도 커서기반보다는 페이지를 원하는대로 넘나들 수 있는 페이지기반 페이지네이션을 구현하고 싶어서 구현은 했는데 대용량 데이터에는 맞지 않은 듯해서 어떻게 최적화를 하면 좋을지 고민하고 있습니다.

현재 저는 Postgres DB를 사용하고 있으며

SQL를 전부 올리기보다 고민하고 있는 부분에 대해서만 간략히 적도록 하겠습니다.

api에서는 SQL를 연속으로 두번 실행시키고 있습니다.

1. 조건에 맞는 모든 ROW를 COUNT
2. 1페이지당 사이즈와 현재 페이지로
OFFCET FETCH NEXT ROWS ONLY 절을 사용해서 ROW를 건너뛰고 검색을 하고 있는데

지금 구현대로면 최악의 경우일때는 거의 DB 전체를 두번 조회하는거나 다름 없을 것 같습니다.

사이드 프로젝트일때는 상관없을 것 같긴한데 실무에서는 어떤방식으로 최적화가 가능할까요?

검색을 해보니 대용량 데이터에서는 커서기반으로 하라는 의견이 많은 것 같은데 그래도 페이지기반의 장점을 살리고 싶습니다.

sql mysql dbms/rdbms query-tuning

답변 2

JSCODE 박재성

지식공유자

안녕하세요 지인님! 좋은 질문 해주셨네요:D

질문에 답변 드려볼게요!

'조건에 맞는 모든 ROW를 COUNT'하는 경우에는 '조건'에 대해 인덱스를 걸어서 풀 테이블 스캔을 하지 않도록 만들 수 있는 지를 고려해보시면 좋을 것 같습니다.
NO OFFSET이라는 방법을 활용하면 DB 전체를 스캔하지 않고 페이지에 해당하는 데이터를 찾을 수 있습니다. NO OFFSET에 대해 추가적으로 학습해보시는 걸 추천드립니다.
https://inf.run/j66Xs

혹시 이 답변이 원하시는 답변이 아닐 경우, 전체 SQL문도 같이 첨부해서 추가적인 질문 남겨주세요:D

유지인

질문자

답변 감사합니다.

NO OFFSET에 대해서는 따로 공부해보겠습니다.

챗지피티한테 물어보니 전체 count한걸 테이블에 캐싱하는걸 권장하는데 이게 유효한 방법일까요?

  CREATE TABLE posts_count_cache (

    total_count BIGINT

);

INSERT INTO posts_count_cache (total_count) VALUES (0);

추가적으로 인덱스에 관해서 질문이 있습니다. 내림차순 오름차순 정렬이 둘다 필요하면 인덱스는 양방향으로 걸아야할까요? 이거도 챗지피티에 먼저 물어보니 한방으로만 걸어도 반대방향이 어느정도 성능이 개선이 되니 한방향으로 걸어도 충분할 것 같은데 맞는지 궁금합니다.

JSCODE 박재성

지식공유자

챗지피티한테 물어보니 전체 count한걸 테이블에 캐싱하는걸 권장하는데 이게 유효한 방법일까요?

-> 네! 이렇게 테이블에 캐싱하는 것도 하나의 방법입니다! 이거에 관련된 방법으로는 '역정규화'라는 키워드로 검색해서 공부해보시면 좋을 것 같네요:D

내림차순 오름차순 정렬이 둘다 필요하면 인덱스는 양방향으로 걸아야할까요? 이거도 챗지피티에 먼저 물어보니 한방으로만 걸어도 반대방향이 어느정도 성능이 개선이 되니 한방향으로 걸어도 충분할 것 같은데 맞는지 궁금합니다.

-> 오래된 PostgreSQL 버전을 사용하지 않는다면 한 방향으로만 걸어도 성능이 충분히 개선됩니다! 하지만 확실하게 하기 위해서 한 방향으로 걸었을 때와 양방향으로 걸었을 때를 실제로 측정해보시면서 테스트해보시면 좋을 것 같아요~!!

이 외로 또 궁금한 점 생기시면 질문 남겨주세요~~~

인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 유지인님께서 페이지기반 페이지네이션에 대해 최적화 방법을 찾고 계신 것 같네요. 대량의 데이터를 다루면서도 사용자가 원하는 페이지로 쉽게 이동할 수 있는 페이지네이션 구현은 약간의 트레이드오프가 생길 수 있습니다. 하지만 최적화된 방법들을 통해 성능 개선을 시도할 수 있습니다.

인덱스 사용: OFFSET과 LIMIT을 사용할 때, 조회 성능을 높이기 위해 인덱스를 걸어주는 것이 좋습니다. 페이지네이션에서는 일반적으로 정렬 기준으로 사용되는 컬럼에 인덱스를 설정하면 효과적입니다.
카운트 쿼리 최적화: 전체 레코드 수를 매번 COUNT하는 대신, 미리 트리거나 스케줄링된 프로세스를 통해 데이터를 타임스템프에 따라 분리하거나, 현재 각 페이지 범위에 대한 부분적 카운트를 이용하는 것도 고려할 수 있습니다.
커서 기반의 장단점: 커서 기반은 대용량의 데이터에서 매번 페이지의 시작 지점을 효율적으로 찾을 수 있다는 장점이 있지만, 페이지 번호를 건너뛰는 상황에서는 사용자 경험이 제한될 수 있습니다.
부분적 Query Pre-fetching: 사용자가 주로 탐색하는 방향성을 예측하여 다음 데이터 페이지를 미리 불러오는 전략도 유용할 수 있습니다.

기존의 비슷한 질문과 답변을 살펴보도록 하겠습니다:

대규모 데이터에서 효율적인 페이지네이션 방법 - 대규모 데이터에서 OFFSET 및 효율적인 페이지네이션 전략에 대해 설명합니다.
PostgreSQL 페이지네이션 최적화 - PostgreSQL을 사용하는 경우 구현 가능한 페이지네이션의 다른 방법들에 대해 논의합니다.

이러한 참조 링크를 통해 페이지네이션의 성능을 개선할 수 있는 다양한 기법을 배울 수 있으니 참고하시기 바랍니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

인프런 커뮤니티 질문&답변

페이지기반 페이지네이션 쿼리를 최적화하고 싶습니다.