본문 바로가기
DB

LIMIT OFFSET 페이징 쿼리를 개선할 수 있는 방법

by yoon_seon 2025. 3. 9.

대용량 데이터를 다루는 애플리케이션을 개발할 때, 빠르고 정확한 정보를 제공하기 위해서는 페이징 처리와 쿼리 최적화는 필수 요소입니다.

 

우리는 보통 페이징 쿼리를 작성할 때 관성적으로 LIMIT & OFFSET 구문을 사용하는데, 사실 이 두 구문의 조합은 DBMS 서버에 불필요한 부하를 발생시킬 수 있습니다.

 

LIMIT & OFFSET 페이징 쿼리의 문제점

SELECT * FROM table LIMIT 10000 OFFSET 10;

 

LIMIT & OFFSET 방식의 페이징은 페이지 번호가 커질수록 DB가 처리해야 할 데이터 양이 급격히 증가하는 문제를 안고 있습니다.

 

DBMS가 순차적으로 레코드를 읽지 않고 지정된 OFFSET 이후의 데이터만 바로 가져올 수 없기 때문에, OFFSET 이전의 모든 데이터를 탐색해야만 원하는 결과를 얻을 수 있기 때문이죠.

 

즉, 페이지 번호가 높아질수록 DBMS는 더 많은 레코드를 읽고 처리해야 하므로 성능 저하가 발생합니다.

 

예를 들어, LIMIT 10 OFFSET 10000 쿼리를 실행하면, DB는 먼저 10000개의 레코드를 읽고, 그 후 10개를 반환해야 합니다. 이 과정에서 DB는 불필요한 데이터를 모두 읽어야 하므로, 불필요한 I/O 작업이 발생하게 되어 성능 저하를 초래합니다.

 

이 때문에 LIMIT & OFFSET 구문을 사용하지 않으면서 데이터를 원하는 만큼만 조회 있도록 다른 방식으로 쿼리를 최적화해야 합니다.

대표적으로 페이징 쿼리는 대표적으로 범위 기반 방식과, 데이터 개수 기반 방식 이 존재하는데, 각 방식들을 하나씩 최적화해보겠습니다.

 

범위 기반의 방식의 쿼리를 최적화하기

범위 기반 방식은 말 그대로 특정 범위를 나눠서 데이터를 조회하는 방식입니다.

기간이나, 숫자 범위로 데이터를 나눠 조회하는 것이지요.

 

주로 이 방식은 테이블의 생성일자 컬럼을 기준으로 일정한 기간의 데이터를 나눠 처리하는 배치 작업이나, AUTO INCREMENT 컬럼 값을 바탕으로 테이블의 전체 데이터를 일정한 숫자 범위로 조회할 때 유용합니다.

 

그렇다 보니 쿼리를 실행할 때 WHERE 절에서 조회 범위를 직접 지정하기에 기존 페이징 방식처럼 LIMIT 절을 사용하지 않을 수 있습니다.

// 일정한 숫자 범위로 조회
SELECT * 
  FROM users 
 WHERE id > 0 AND id <= 5000; 

// 기간으로 조회
SELECT * 
  FROM payments 
 WHERE create_date >= '2025-03-01' AND create_date < '2025-04-01''

이 방법의 장점 중 하나는 쿼리가 단순하다는 것인데요.

조회 조건이 간단하기 때문에 여러 번의 쿼리를 나누어 실행하더라도 사용하는 쿼리는 동일하게 유지됩니다.

또한 WHERE 절에 사용되는 컬럼을 인덱싱 처리해 둔다면 성능을 더 향상할 수 있겠지요.

 

데이터 개수 기반 방식으로 최적화하기

데이터 개수 기반 방식은 10건, 20건과 같이 지정된 데이터건수만큼 결과 데이터를 반환하도록 하는 방식입니다.

범위 기반 방식으로 많이 처리하는 배치 작업보다는 일반 서비스에서 사용자 단위 데이터를 조회할 때 주로 사용되며, 쿼리에서 ORDER BY & LIMIT 절이 많이 사용됩니다.

 

여기까지 보면 기존 페이징 방식과 차이가 없어 보일 수도 있지만,

데이터 개수 기반 방식은 기존 페이징 방식과 달리 처음 데이터를 조회할 때 N회차 조회에서 사용하는 쿼리 형태가 달라지게 됩니다.

 

이때, N회차 쿼리에서는 WHERE 절에 사용되는 조건이 동등 조건인지, 아니면 범위 조건인지에 따라 쿼리의 형태가 달라지게 되는데요.

 

먼저, WHERE 절에 동등 조건이 사용될 때를 알아보겠습니다.

 

WHERE 절에 동등 조건이 사용될 경우

# payments 테이블의 DDL
CREATE TABLE payments (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid_id (user_id, id)
);

# 페이징이 적용될 SQL
SELECT * FROM payments WHERE user_id = ?;

payments 테이블과 페이징을 적용할 쿼리가 있습니다.

 

1회차의 쿼리를 아래와 같이 작성했습니다.

# 1회차
SELECT * 
  FROM payments 
 WHERE user_id = ?
 ORDER BY id
 LIMIT 30;

조회 대상 데이터를 부분적으로 나누어 빠짐없이 순차적으로 가져오기 위해, 조건을 만족하는 데이터들을 식별자 컬럼(이 경우 id)을 기준으로 정렬하고, LIMIT 절에 지정된 건수만큼 나누는 형태입니다.

 

WHERE 절에 user_id = ? 조건을 사용하여 필요한 데이터를 조회하고, 그 결과를 id 기준으로 정렬한 후 지정된 30개를 반환합니다.

 

N회차에서 쿼리는 아래와 같이 달라집니다.

# N회차
SELECT * 
  FROM payments 
 WHERE user_id = ?
   AND id > {이전 데이터의 마지막 id 값} # 1회차와 달라진 AND 조건
 ORDER BY id 
 LIMIT 30;

N회차 쿼리에서는 이전에 가져온 데이터의 마지막 id 값과 비교하여, 그 이후의 데이터를 조회합니다.

WHERE 절에 AND id > {이전 데이터의 마지막 id 값} 조건을 추가함으로써, 이미 조회한 데이터는 제외하고 그다음 순서에 해당되는 데이터만을 조회할 수 있게 됩니다.

 

또한, ORDER BY 절을 사용하므로 쿼리 처리 시 내부적으로 정렬이 발생하지 않으면서 지정된 건수만큼만 데이터를 읽어 반환할 수 있습니다.

 

💡 기존의 SELECT * 쿼리는 모든 컬럼을 반환하는 반면, 이 방식에서는 필요한 컬럼만을 조회함으로써 성능을 더욱 최적화할 수 있습니다. 이렇게 필요한 컬럼만 조회함으로써 데이터베이스에서 불필요한 데이터의 전송과 처리를 줄일 수 있게 되는 것이죠.

 

또 payments 테이블에는 user_id와 id가 인덱싱 해두었기에, 조건에 맞는 데이터를 빠르게 읽을 수 있습니다.

 

다음으로는 WHERE 절에 범위 조건이 사용될 때를 알아보겠습니다.

 

WHERE 절에 범위 조건이 사용될 경우

# payments 테이블의 DDL
CREATE TABLE payments (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  finish_date datetime NOT NULL,
  PRIMARY KEY (id),
  KEY idx_finishdate_id (finish_date, id)
);

# 페이징이 적용될 SQL
SELECT * 
  FROM payments 
 WHERE finish_date >= '{결제일시 시작 날짜}'
   AND finish_date < '{결제일시 종료 날짜}'

이어서 1회차의 쿼리를 아래와 같이 작성했습니다.

SELECT * 
  FROM payments 
 WHERE finish_date >= '{결제일시 시작 날짜}'
   AND finish_date < '{결제일시 종료 날짜}'
 ORDER BY finish_date, id
 LIMIT 30;

기존 쿼리(페이징이 적용될 SQL)와 비교했을 때, ORDER BY와 LIMIT 조건이 추가되었으며, 동등 조건을 사용한 경우와 다르게 ORDER BY 절에 조건 컬럼인 finish_date가 포함되었습니다.

ORDER BY에 finish_date 이 명시된 이유

만약 ORDER BY 절에 id 컬럼만 명시한다면 WHERE 절에 명시된 finish_date 조건을 만족하는 데이터들을 모두 읽은 후, 다시 id 컬럼을 기준으로 정렬한 다음, LIMIT 절에 명시된 건수만큼 반환하게 됩니다.
즉 테이블에 finish_date와 id로 구성된 인덱스가 있더라도, 온전히 인덱스를 활용하지 못하고 조건을 만족하는 전체 데이터에 대해 정렬 작업이 발생해 불필요한 성능 저하가 발생하는 것이죠.
ORDER BY 절에서 finish_date와 id를 모두 명시하면, finish_date와 id로 고정된 인덱스를 활용하여 정렬 없이 원하는 건수만큼 순차적으로 데이터를 조회할 수 있으므로 인덱싱의 장점을 가져갈 수 있습니다.

 

N회차에서 쿼리는 아래와 같이 달라집니다.

1. id(식별자 컬럼) 조건으로 해결되지 않는 경우(인덱스 정렬조건이 id와 다른 경우)

데이터가 생성될 때 채번되는 id와 다르게 언제 값이 추가될지 알 수 없는 완료일시 finish_date 컬럼이 있습니다.

SELECT *
  FROM payments
 WHERE (
   # {1} 
   (finish_date = '2025-01-01 00:00:02' AND id > 8)
   OR
   # {2}
   (finish_date > '2025-01-01 00:00:02' AND finish_date < '2025-01-02 00:00:00')
)
 ORDER BY created_date, id
 LIMIT 5;

쿼리의 조건을 살펴보겠습니다. 두 가지 조건이 OR로 결합되어 있습니다.

 

첫 번째 조건에서는 finish_date가 2025년 1월 1일 00:00:02이고 id가 8보다 큰 경우를 반환하고,

두 번째 조건에서는 finish_date가 2025년 1월 1일 00:00:02 이후이면서 2025년 1월 2일 00:00:00 이전인 데이터를 반환합니다.

 

위 쿼리에서는 OR 조건을 사용하고 있는데요. 이유는 인덱스 우선순위가 finish_date, id 순이기에 같은 finish_date 에 해당하는 id가 다르게 정렬된 경우에서의 데이터 누락을 방지하기 위해서입니다.

  • 첫 번째 조건 {1}에서는 이전 쿼리에서 마지막으로 반환된 finish_date와 id 값을 기준으로 데이터가 조회됩니다. 즉, 마지막으로 반환된 데이터의 finish_date 값은 그대로 두고, id는 그 값보다 큰 데이터를 조회하게 됩니다.
  • 만약 같은 finish_date 값을 가지는 데이터들이 여러 개 있다면, id 값에 따라 순차적으로 데이터를 읽어야 합니다. 이전에 반환된 마지막 데이터의 id 값보다 큰 값을 가진 데이터를 조회하기 위해 id > 8과 같은 조건을 추가합니다.
  • 두 번째 조건 {2}는 finish_date 값이 2025년 1월 2일보다 작은 범위 내에 포함되는 데이터들을 조회합니다. 이전에 반환된 데이터 이후에 해당하는 범위의 데이터를 찾아야 하므로, finish_date와 id 값을 기준으로 순차적으로 데이터를 읽어옵니다.
  • 이전에 반환받은 마지막 데이터 이후에 해당하는 데이터들을 순차적으로 필요한 만큼만 읽어서 반환하는 것을 알 수 있습니다.

2. id(식별자 컬럼) 조건으로 해결되는 경우

유저의 로그를 쌓는 user_log 테이블이 있고 데이터가 생성될 때 채번되는 id와 함께 추가되는 생성일시 create_date 컬럼이 함께 인덱싱 되어있다고 가정하겠습니다.

SELECT *
  FROM user_logs
 WHERE create_date > '2025-01-01 00:00:02' AND created_date < '2025-01-02 00:00:00'
   AND id > 5
 ORDER BY created_date, id # created_date, id 가 인덱싱 되어있다고 가정
 LIMIT 5;

payments 테이블의 경우 finish_date 컬럼은 생성일시로 자동 증가 값인 id 컬럼 값과 저장된 값들의 순서가 달랐습니다.

반면에 user_logs 테이블의 created_date 컬럼과 id 컬럼의 경우에는 모두 데이터가 테이블에 저장된 순서대로 값이 증가하므로 각 컬럼에 대해 데이터 간 순서가 동일하다고 볼 수 있습니다.

 

그래서 N회차 쿼리에서는 기존에 명시되어 있는 created_date 컬럼의 추가하더라도 누락되는 데이터 없이 그다음 순서의 데이터들이 반환되는 것이지요.

 

위 2가지 케이스를 정리하자면 아래와 같습니다.

  1. 범위 조건 컬럼과 식별자 컬럼의 순서가 동일하지 않은 경우 데이터의 누락이 발생할 수 있어 추가적인 조건이 필요하다.
    1. 1회차 쿼리에서는 ORDER BY 절 범위 조건 컬럼을 선행으로 해서 식별자 컬럼을 함께 명시해야하며 N회차 쿼리에서 이전에 가져온 마지막 데이터와 범위 조건 컬럼 값이 동일한 경우가 있을 수 있기 때문에 두 개의 조건이 OR로 주어지게 된다.
  2. 범위 조건 컬럼과 식별자 컬럼의 순서가 동일할 경우 식별자 컬럼으로만 조건을 설정할 수 있다.

 

마지막으로 내용을 정리하면서 마무리하겠습니다.

  • LIMIT & OFFSET 구문은 페이지 번호가 높아질수록 DBMS는 더 많은 레코드를 읽고 처리해야 하기에 DB 서버를 부하시킬 수 있으므로 사용을 지양하자
  • 페이징 쿼리는 대표적으로 범위 기반 방식과 데이터 개수 기반 방식이 존재한다.
  • 범위 기반 방식은 단순하게 날짜와 숫자 값을 특정 범위로 나누어서 쿼리를 실행하는 형태로 1회차와 N회차의 쿼리가 동일하다.
  • 데이터 개수 기반 방식은 지정한 데이터 개수만큼 조회하는 형태로 1회차와 N회차 쿼리 형태가 다르다.
    • 쿼리에 사용되는 컬럼이 어느 컬럼이냐에 따라쿼리 형태가 달라진다.

 

 

 

Real MySQL 시즌 1 - Part 1 강의 | 이성욱 - 인프런

이성욱 | , 국내 1위 MySQL 도서 ⟪Real MySQL 8.0⟫ 저자들이 선보이는 첫 번째 강의![사진][사진]Real MySQL 시리즈는, 전체 판매량 3만여권에 달하는 스테디셀러 MySQL DBMS 서적입니다. ⟪Real MySQL 8.0⟫ 책

www.inflearn.com

 

 

댓글

티스토리툴바