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

안녕하세요. bebe님

결론부터 말씀드리면, DBMS는 LIMIT와 OFFSET을 처리할 때 기본적으로 인덱스를 그대로 활용하지만, OFFSET 값이 커질수록 성능 저하가 발생하여 특별한 최적화 기법이 필요할 수 있습니다. 질문해주신 내용을 바탕으로 하나씩 자세히 설명해 드릴게요.

ORDER BY 절에 사용된 칼럼에 인덱스가 있다면, 데이터베이스는 인덱스를 통해 정렬된 순서대로 데이터에 접근하므로 Filesort를 피할 수 있습니다.

LIMIT와 OFFSET이 포함된 쿼리는 다음과 같은 방식으로 인덱스를 활용하여 동작합니다.

인덱스 스캔 시작: ORDER BY 절의 칼럼 인덱스(예: 생성일 인덱스)를 처음부터 순차적으로 스캔합니다.

OFFSET 만큼 건너뛰기: 데이터베이스는 인덱스의 리프 노드를 따라가며 OFFSET에 지정된 수만큼의 데이터를 읽고 버립니다. 여기서 중요한 점은 데이터를 읽기는 한다는 것입니다.

LIMIT 만큼 반환: OFFSET 수만큼 데이터를 건너뛴 후, LIMIT에 지정된 수만큼의 데이터를 가져와 사용자에게 반환합니다.

즉, 궁금해하신 "n번째 칼럼을 빠르게 알아내는" 특별한 방법이 있는 것은 아닙니다. DBMS는 인덱스를 처음부터 훑으면서 OFFSET 개수만큼의 데이터를 일일이 건너뛰는 방식으로 동작합니다.

OFFSET이 커질 때 발생하는 문제점과 해결 방안

이러한 동작 방식 때문에 OFFSET 값이 작을 때는 매우 효율적이지만, OFFSET 값이 수백만, 수천만에 달하는 "뒤쪽 페이지"로 갈수록 심각한 성능 저하가 발생합니다. 예를 들어 OFFSET 10000000, LIMIT 20 이라는 쿼리는 20개의 데이터를 가져오기 위해 무려 10,000,000개의 데이터를 인덱스로 스캔하고, 테이블에서 읽어온 뒤 버려야 하기 때문입니다.

이때 언급하신 커버링 인덱스가 1차적인 해결책이 될 수 있습니다. 쿼리에 필요한 모든 칼럼이 인덱스에 포함되어 있다면, 데이터 테이블에 접근하는 Random I/O를 피하고 인덱스 내에서만 데이터를 읽고 버릴 수 있어 성능이 향상됩니다.

하지만 커버링 인덱스만으로는 OFFSET이 매우 클 때 발생하는 대량의 불필요한 스캔 자체를 막을 수는 없습니다. 이를 해결하기 위한 더 근본적인 방법은 OFFSET을 사용하지 않는 방식으로 페이징을 구현하는 것입니다. 이를 "커서 기반 페이징(Cursor-based Pagination)" 또는 "Seek Method"라고 부릅니다.

커서 기반 페이징 (Seek Method)

이 방식은 마지막으로 조회된 페이지의 마지막 값을 기억해두었다가, 다음 페이지를 조회할 때 그 값을 WHERE 조건절에 사용하는 것입니다.

기존 OFFSET 방식:

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000000;

커서 기반 페이징 방식:

-- 이전 페이지에서 마지막으로 보았던 created_at 값이 '2023-10-27 10:00:00' 이었다고 가정

SELECT * FROM posts

WHERE created_at < '2023-10-27 10:00:00' -- 마지막 조회 시점 이후의 데이터를 조건으로

ORDER BY created_at DESC

LIMIT 20; -- OFFSET 없이 바로 20개 조회

이렇게 하면 DBMS는 created_at 인덱스에서 '2023-10-27 10:00:00' 위치를 바로 찾은 뒤, 그 지점부터 20개의 데이터만 스캔하면 되므로 수천만 개의 데이터를 버리는 비효율적인 작업을 피할 수 있습니다. OFFSET을 사용하는 것보다 훨씬 빠르고 효율적인 방법입니다.

관련해서 커서 기반 페이징(Cursor-based Pagination)을 검색해보시면 도움이 되실거에요.

감사합니다.

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

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

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

1. 인덱스 기반 조회: 페이징의 경우 일반적으로 인덱스를 통해 빠르게 필터링하여 특정 지점까지 이동한 뒤, limit에 정의된 수의 데이터를 가져오게 됩니다. 그렇지만, 인덱스를 타고 가는 과정에서 offset만큼의 데이터를 건너뛰는 것은 비용이 큰 작업이 될 수 있습니다. 대량의 데이터 처리시 문제가 될 수 있습니다.

2. 커버링 인덱스 사용: 가능한 한 인덱스에 필요한 모든 칼럼을 포함시켜 커버링 인덱스를 사용하면 추가적인 데이터 페이지 접근을 줄여 성능 향상을 기대할 수 있습니다.

3. 데이터베이스마다의 최적화 방법: 데이터베이스 시스템에 따라 limit와 offset 최적화가 이루어지기도 합니다. 어떤 시스템에서는 미리 계산하여 특정 지점에서 시작하도록 최적화를 수행하기도 하니까, 데이터베이스의 특성에 따라 다르게 동작할 수 있습니다.

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

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