Backend/Database

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

멍분이 2024. 7. 2. 23:05

안녕하세요, 이번 글에서는 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 방식에 대해 서술해 보았습니다. 확실하게 이해하고 실무에 적용하면 좋을 것 같습니다.

저작자표시 비영리 (새창열림)