개발 학습 실습실

이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

공부 › DB › SQL

OFFSET과 키셋 페이징

뒤쪽 페이지일수록 느려지는 이유

게시글 목록을 최신순으로 페이지마다 나눠 보여 줍니다. OFFSET 방식은 앞 페이지의 행을 전부 읽고 버린 뒤에야 원하는 페이지에 도착합니다. 키셋(커서) 방식은 "마지막으로 본 글 다음부터"를 인덱스로 바로 찾아갑니다.

일부만 실행으로 확인 · 결과 내용은 SQLite로 확인, 읽는 행 수는 문서 기준

이 실습에 나오는 개념

OFFSET 페이징과 키셋(커서) 페이징
OFFSET 페이징은 앞에서부터 지정한 개수만큼 건너뛰고 가져오는 방식으로, 뒤쪽 페이지일수록 건너뛸 행을 모두 읽어야 해서 느려진다. 키셋 페이징은 직전 페이지의 마지막 행의 키 값을 기억해 두고 "그 값 다음부터"를 조건으로 조회하는 방식으로, 인덱스를 이용해 바로 해당 위치부터 읽으므로 페이지가 깊어져도 속도가 일정하다.
인덱스
테이블에서 원하는 행을 빨리 찾기 위해 특정 컬럼의 값을 정렬된 형태로 따로 유지하는 자료구조다. 인덱스가 없으면 조건에 맞는 행을 찾기 위해 테이블 전체를 읽어야 한다. 조회는 빨라지지만 데이터를 추가, 수정, 삭제할 때마다 인덱스도 갱신해야 하므로 쓰기 성능과 저장 공간에 비용이 든다.
B-트리 인덱스
대부분의 관계형 데이터베이스가 기본으로 쓰는 인덱스 구조다. 값이 정렬된 상태로 유지되는 균형 잡힌 트리이며, 한 노드에 많은 키를 담아 트리의 높이가 낮다. 루트에서 몇 단계만 내려가면 원하는 값을 찾을 수 있고, 값이 정렬되어 있어 같은 값 조회뿐 아니라 범위 조회와 정렬에도 사용된다.

이렇게 바꿔 보세요

처음 설정으로 실행하면

  1. post 테이블에는 글이 100만 개 있고 id가 클수록 최신입니다. 1,000페이지를 OFFSET 19,980으로 요청합니다.
  2. DB는 "19,980번째 다음"이 어디인지 미리 알 수 없습니다. 가장 최신 글부터 인덱스를 따라 하나씩 세면서 읽습니다. 19,980행을 읽고 그대로 버립니다.
  3. 그다음 20행을 읽어 결과로 돌려줍니다. id 980,020부터 980,001까지입니다.

실행 결과

코드

-- 1,000페이지 (id 내림차순 = 최신순)
SELECT id, title, created_at
  FROM post
 ORDER BY id DESC
 LIMIT 20 OFFSET 19980;

이 모형의 전제

글 100만 개가 id 순으로 정렬돼 있고 id에 인덱스(기본 키)가 있다고 가정했습니다. OFFSET으로 건너뛰는 행도 서버 안에서는 계산해야 하므로 OFFSET이 크면 비효율적일 수 있다는 것은 PostgreSQL 공식 문서(LIMIT and OFFSET)의 설명입니다. MySQL 문서에서는 같은 문장을 확인하지 못했습니다. 정렬 기준이 id가 아니라 등록 시각처럼 값이 겹칠 수 있는 컬럼이면, 키셋 조건에 (등록 시각, id)처럼 겹치지 않는 컬럼을 함께 써야 합니다. 행 수는 계산값이며 실제 DB에서 측정한 것은 아닙니다. 결과의 내용은 실제 SQL 엔진(SQLite)에서 확인했습니다. 두 방식이 같은 페이지에서 같은 글을 돌려줬고, 새 글이 하나 끼어든 뒤 OFFSET의 다음 페이지 첫 줄에는 앞 페이지의 마지막 글이 다시 나왔으며 키셋에서는 그다음 글이 나왔습니다.

앞 실습: 윈도우 함수 · 다음 실습: 뷰, 프로시저, 트리거

이 실습에 나오는 개념

이 실습에서 보는 것

설정

코드


      
    


    

      
그림

      

      

    


    

      

      

      

    


  


  

    

      
실행 비교

      

      

    

    

      
이렇게 바꿔 보세요