limit, offset 에서 인덱스 동작

[질문 템플릿]
1. 강의 내용과 관련된 질문인가요? (예/아니오) 예
2. 인프런의 질문 게시판과 자주 하는 질문에 없는 내용인가요? (예/아니오) 예
3. 질문 잘하기 메뉴얼을 읽어보셨나요? (예/아니오) 예

[질문 내용]
영한님의 인덱스 강의를 듣고, 간단한 CRUD 토이 프로젝트를 하면서 인덱스를 적용해보려고 하고 있습니다.

강의에서 설명해주신 것처럼, 칼럼의 값을 기준으로 인덱스를 타는 경우 데이터가 수백 ~ 수천만건이더라도 굉장히 빠르게 조회되는 것을 확인했는데요!

문제는 페이징 쿼리 같이, limit, offset을 활용해야 하는 경우에 인덱스가 기존과 같이 동작하는지, 아니면 특별한 무언가가 있는지 궁금합니다.

만약 배운 그대로 적용된다면 (ex. 생성일이 페이징 기준)

생성일 칼럼을 인덱스를 걸면 Filesort 를 피할 수 있겠다는 생각은 하였고, limit 절의 경우 where와는 다르게 select 절까지 모두 평가된 뒤에 적용되기 때문에 Random I/O를 피하기 위해 커버링 인덱스도 잘 사용해야 할 것 같다고 생각했습니다.

다만, offset에 해당하는 n 번째 칼럼을 빠르게 B+ 트리에서 알아낼 수 있는 방법이 있는지가 궁금하더라고요. DBMS에서 limit과 offset을 특별히 따로 처리하는지 아니면 강의에서 배운대로 인덱스를 그대로 활용하는지 궁금합니다.

안녕하세요, 인프런 AI 인턴입니다.

bebe님께서 궁금해하신 limit와 offset에 관련된 인덱스의 동작 방식은 데이터베이스 최적화에서 중요한 주제입니다. 많은 분들이 이와 관련된 질문을 하셨고, 비슷한 내용의 답변이 인프런의 질문 게시판에서 자주 논의되고 있습니다. 관련된 자료로는 실무(OLTP 환경)에서는 강의에서 알려주신 오프셋 페이징을 쓰면 안되는거 맞을까요? 게시글을 참고해주세요.

간단히 요약하면, 일반적인 쓰기 작업이나 다른 쿼리에 비해, limit와 offset을 사용한 페이징 쿼리는 주어진 조건에 따라 인덱스의 효율성이 달라질 수 있습니다.

인덱스 기반 조회: 페이징의 경우 일반적으로 인덱스를 통해 빠르게 필터링하여 특정 지점까지 이동한 뒤, limit에 정의된 수의 데이터를 가져오게 됩니다. 그렇지만, 인덱스를 타고 가는 과정에서 offset만큼의 데이터를 건너뛰는 것은 비용이 큰 작업이 될 수 있습니다. 대량의 데이터 처리시 문제가 될 수 있습니다.
커버링 인덱스 사용: 가능한 한 인덱스에 필요한 모든 칼럼을 포함시켜 커버링 인덱스를 사용하면 추가적인 데이터 페이지 접근을 줄여 성능 향상을 기대할 수 있습니다.
데이터베이스마다의 최적화 방법: 데이터베이스 시스템에 따라 limit와 offset 최적화가 이루어지기도 합니다. 어떤 시스템에서는 미리 계산하여 특정 지점에서 시작하도록 최적화를 수행하기도 하니까, 데이터베이스의 특성에 따라 다르게 동작할 수 있습니다.

이와 함께 여러 예를 통해 직접 실험해보시면서 이해도를 높여보시는 것도 좋을 것 같습니다. 자세한 내용은 상술한 게시글과 관련 자료를 통하여 더 많이 확인하시길 권장드립니다.

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

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