NoOffset 방식을 통한 MySQL 페이징 쿼리 튜닝

안녕하세요, 이번 글에서는 MySQL의 offset 기반 페이징 방식의 문제점과 이를 개선하는 방법에 대해 적어보도록 하겠습니다.

 

페이징 쿼리란?

페이징 쿼리란 원하는 전체 데이터에 대해 부분적으로 나누어 데이터를 조회 및 처리하는 방법을 말합니다. 이렇게 되면 한 번에 처리할 데이터 양이 적어지게 되고 DB 및 애플리케이션 서버의 리소스 사용 효율을 높일 수 있습니다. 이를 통해 결과적으로 서비스 이용자들에게 더 빠른 응답 시간과 더 나은 서비스 경험을 제공할 수 있게 됩니다.

 

1-1. Limit & Offset

보통 페이징 쿼리를 작성하면 DB에서 제공하는 Limit & Offset 구문을 사용하는 경우가 많습니다. 사실 저 역시도 실무에서 이 방식을 많이 사용하는데요. 쿼리로는 다음과 같이 작성할 수 있습니다.

 

1. select * from t1 offset 0 limit 500;
...
2. select * from t1 offset 501 limit 500;

 

1-2. Limit & Offset의 단점

하지만 이 방식은 데이터가 많아지고, 페이지 번호가 높아질수록, 성능이 안좋아지게 됩니다. 이유는 DB는 항상 오프셋에 저장된 위치까지 모든 레코드를 읽어야 다음 레코드를 읽을 수 있기 때문입니다.

 

가령 첫번째 쿼리를 실행하게 되면 첫 데이터부터 500번까지 읽으면 되지만, 두 번째 쿼리를 실행할 때는 1~500까지의 데이터를 모두 읽은 뒤 그다음 500개를 읽을 수 있습니다. 즉 DB는 특정 offset 이후의 데이터만을 바로 가져올 수 없음을 의미합니다. 

 

1-3 개선안

따라서 가능하다면, Limit & Offset 구문을 사용하지 않으면서 데이터를 원하는 만큼만 조회해서 가져가도록 만들어야 합니다.

 

1-4 범위 기반으로 쿼리

첫번째는 범위 기반으로 쿼리를 하는 것인데요, 쉽게 말해서 날짜, 숫자 범위로 나누어 데이터를 조회하는 방식입니다. 전체 데이터를 일정한 날짜/숫자 (auto_incr) 범위로 나누어 조회하게 되면 조회 조건도 단순하고, 여러 번 쿼리를 하더라도 쿼리 형태가 처음과 동일하다는 장점이 있습니다.

 

select * from items where id > 0 and id < 500
...
select * from t1 where created_at >= '2024-07-02' and created_at < '2024-07-03'

 

 

데이터가 많고 특정 범위에 따라 세분화하여 처리하기 용이하기 때문에 보통 배치에서 많이 사용하는 형식입니다.

 

1-5 데이터 개수 기반 방식 (No Offset)

서비스를 할때 보통 일정 페이지 사이즈로 데이터를 끊어서 노출하는 것을 많이 볼 수 있는데요, 이와 같이 지정된 데이터 건수만큼 결과 데이터를 반환하는 방식입니다. 보통 이 경우 order by & limit이 많이 사용됩니다. 

 

offset을 사용하는 쿼리와 비교해서 몇 가지 주의할 점이 있는데요, 예시와 함께 살펴보도록 하겠습니다.

 

create table coupon
(
    id         bigint auto_increment
        primary key,
    user_id    bigint                             not null,
    status     enum ('ISSUED', 'USED', 'EXPIRED') not null,
    policy_id  bigint                             null,
    type       enum ('PROMOTION', 'REWARD')       not null,
    issued_at  datetime                           null,
    start_at   datetime                           null,
    end_at     datetime                           null,
    created_at datetime default CURRENT_TIMESTAMP not null,
    updated_at datetime default CURRENT_TIMESTAMP not null
);

create index coupon_user_id_id_index
             on coupon (user_id, id);

 

예시로 사용할 coupon 테이블입니다.

 

select *
from coupon
where user_id = ?;

 

해당 쿼리에 대해 페이징을 적용해보겠습니다.

 

1-5-1 각각의 데이터를 식별할 수 있는 컬럼으로 정렬이 가능할 때

첫 페이지 조회

select *
from coupon
where user_id = ?
order by id
limit 20;

 

이후 페이지 조회

select *
from coupon
where user_id = 1
  and id > 20
order by id
limit 20;

 

간단하게 id는 각 컬럼이 유일하게 갖는 값이기 때문에 id 순으로 정렬 후 특정 id 이후의 값을 조건으로 조회하게 되면 중복 없이 순서대로 조회할 수 있습니다. 이 경우 where 조건절에 id > { 이전 페이지의 마지막 id }이라는 조건이 있기 때문에 MySQL은 이전의 모든 레코드를 또 읽을 필요가 없습니다. 즉 21번째 데이터를 읽는데 offset 방식처럼 1~20까지의 데이터를 읽어야 하지 않습니다.

 

물론 위 예시의 경우 (user_id, id)가 인덱스로 생성되기 때문에 가능한 일입니다. 만약 해당 방법을 사용하여 쿼리를 작성하실 예정이라면 불필요한 조회와 정렬을 수행하지 않도록 사전에 인덱스가 적절하게 생성되어야 합니다.

 

1-5-2 범위 조건을 사용하는 쿼리

이번에는 아래와 같이 특정 날짜를 기준으로 범위 검색을 하는 쿼리에 페이징을 적용해 보도록 하겠습니다.

select *
from coupon
where issued_at >= '2024-07-01 00:00:00'
  and issued_at < '2024-07-07 00:00:00'

 

첫 페이지 조회

select *
from coupon
where issued_at >= '2024-07-01 00:00:00'
  and issued_at < '2024-07-07 00:00:00'
order by issued_at, id
limit 20;

 

이 경우 페이지를 조회할 때 반드시 issued_at과 id를 같이 정렬조건에 명시해주셔야 하는데요. 이유는 만약 order by id만 명시되게 되면 2024년 7월 1일 ~ 2024년 7월 7일까지의 모든 레코드를 읽은 후, id 순서로 잘라서 반환하기 때문입니다. 따라서 복합인덱스를 설정하신 뒤, 위와 같이 두 컬럼을 모두 order by 절에 기술해 주셔야 필요한 데이터만큼만 인덱스에서 찾아서 빠르게 처리할 수 있습니다.

 

create index coupon_issued_at_id_index
    on coupon (issued_at, id);

 

이후 페이지 조회

이 경우는 직전에 보았던 id로 정렬하는 예시와는 조금 다른데요, 아래와 같이 작성하면 안 됩니다.

# 잘못된 예시
select *
from coupon
where issued_at >= '2024-07-01 00:00:00'
  and issued_at < '2024-07-07 00:00:00'
  and id > 8
order by issued_at, id
limit 5;

 

이유는 조금 생각해 보면 알 수 있습니다. 아래 예시를 한번 살펴볼게요.

issued_at	id
2024-07-01 00:00:00	5
2024-07-01 00:00:01	1
2024-07-01 00:00:01	2
2024-07-01 00:00:01	3
2024-07-01 00:00:02	8
2024-07-01 00:00:02	9
2024-07-01 00:00:03	4
2024-07-01 00:00:03	6
2024-07-01 00:00:03	7
2024-07-01 00:00:03	10

 

위 쿼리를 실행하였을 때 기대 결과는 id가 9, 4, 6, 7, 10 인 데이터를 반환받기를 원하지만 실제로는 id 값이 9, 10 인 데이터만 반환받게 됩니다.

 

이유는 조금만 생각해보면 알 수 있는데요, issued_at 값과 id 값의 정렬순서가 다르지만 id 값 만을 기준으로 데이터를 나누기 시도했기 때문입니다.

 

해결 방법은 issued_at에 의존적으로 id가 정렬되어 있다는 점을 착안하시면 됩니다. 

issued_at	id
2024-07-01 00:00:00	5
2024-07-01 00:00:01	1
2024-07-01 00:00:01	2
2024-07-01 00:00:01	3
2024-07-01 00:00:02	8
2024-07-01 00:00:02	9
2024-07-01 00:00:03	4
2024-07-01 00:00:03	6
2024-07-01 00:00:03	7
2024-07-01 00:00:03	10

• 5 
• 1, 2, 3
• 8, 9
• 4, 6, 7, 10

각각 issued_at이 같은 기준으로 정렬되어 있습니다.

 

즉 마지막 값의 issued_at이 같으면서, id가 마지막 값의 id 이상인 값과 마지막 issued_at 보다 issued_at이 크고 7월 7일보다 작은 값을 합하여 결과에 포함시키면 됩니다. 

 

select *
from coupon
where (issued_at = '2024-07-01 00:00:02' and id > 8)
   or (issued_at > '2024-07-01 00:00:02' and issued_at < '2024-07-07 00:00:00')
order by issued_at, id
limit 5;

 

1-5-3 범위 조건을 사용하지만, 식별자 컬럼으로만 나눌 수 있는 경우

범위 조건을 사용하지만, 위와 같이 정렬 순서를 고려하여 처리하지 않아도 되는 경우가 있는데요, 데이터가 삽입되는 시점부터 범위와 id가 같은 순서로 정렬됨이 보장되는 데이터라면 id만으로 나눌 수 있습니다.

 

select *
from coupon
where created_at >= '2024-07-01 00:00:00'
  and created_at < '2024-07-07 00:00:00'
  and id > { 이전 페이지의 마지막 데이터 id }
order by created_at, id
limit 5;

 

예를 들어 created_at은 레코드가 삽입된 순서로 정렬이 되어 있을 것이고, id가 만약 auto_increment 컬럼이라면 두 컬럼의 정렬순서는 항상 같습니다. 따라서 이런 경우라면 id로만 범위를 주어도 놓치는 데이터 없이 모두 조회할 수 있습니다.

 

select *
from coupon
where created_at >= { 이전 페이지의 마지막 데이터 created_at }
  and created_at < '2024-07-07 00:00:00'
  and id > { 이전 페이지의 마지막 데이터 id }
order by created_at, id
limit 5;

 

하지만 역시 이경우도 created_at을 이전 페이지의 마지막 데이터 값을 주어서 index 스캔 효율을 높이기 위해 created_at의 시작지점도 이전 페이지의 마지막 지점으로 지정해주어야 합니다.

 

오늘은 DB에서 페이징 쿼리를 효율적으로 작성하는 NoOffset 방식에 대해 서술해 보았습니다. 확실하게 이해하고 실무에 적용하면 좋을 것 같습니다.