mysql limit, offset과 id 기반 조회 비교 분석

안녕하세요.

 

최근 mysql database를 사용하여 데이터를 가져오는 테스트를 하고 있었는데, 유의미한 결과가 있어 공유하려 합니다.

 

먼저 간략하게 테스트하는 환경과 조건을 말씀드리겠습니다.

 

1. 테이블에 데이터는 모두 100만 건이 들어가 있습니다.

2. paging 처리하는 쿼리와 id 기반으로 검색하는 두 개의 쿼리를 비교하였습니다.

 

테이블

create table exmaple
(
    id                           bigint auto_increment        primary key NOT NULL,
    version                      bigint default 0 not null comment '상품 버전',
    product_id                   bigint           not null comment '상품 id',
    created_at                   datetime(6)      not null comment '생성일',
    deleted_at                   datetime(6)      null comment '삭제일',
    updated_at                   datetime(6)      not null comment '수정일',
    created_by                   varchar(255)     null comment '생성자',
    updated_by                   varchar(255)     null comment '수정자'
);


create index version_deleted_at
    on exmaple (version desc, deleted_at asc);
    
 CREATE INDEX product_id_created_at_deleted_at
ON exmaple (product_id, created_at, deleted_at);

 

보시면 index가 들어가 있는 것을 확인 할 수 있는데요. 이 부분도 참고해 두시면 좋을 거 같습니다.

 

다음은 쿼리입니다.

1번 쿼리

select *
from example
where (example.deleted_at is null)
  and example.version = 5
limit 197000, 1000;

 

jpa를 사용하면 실제로 실행되는 쿼리입니다. 혹은 jpql을 통해서도 해당 쿼리를 실행 시킬 수 있을 것입니다.

이 쿼리의 문제점은 어떤지 잠시 후에 보도록 하죠.

 

다음은 id 기반으로 조회하는 2번 쿼리입니다.

 

2번 쿼리

select *
from example
where (example.deleted_at is null)
  and example.version = 5
and id between 394000 and 395000
;

 

id는 pk 컬럼입니다. 이제 두 쿼리의 쿼리플랜을 보도록 하겠습니다.

그리고 옆에 실행 시간을 적어두겠습니다.

1번 쿼리의 쿼리 플랜 (실행시간: 1s 267ms)

 

2번 쿼리의 쿼리 플랜 (실행시간: 305ms)

 

쿼리 플랜 분석

• 일단 실행 시간이 상당히 개선되었습니다. 1s 267ms  -> 305 ms로 약 75% 감소되었습니다.
  
  
• 새로운 쿼리 플랜은 Primary Key를 사용하였는데요. 따라서 쿼리 플랜의 rows가 495950 -> 1001로 대폭 감소되었습니다. 이 말은 테이블에서 스캔하는 범위가 해당 rows 만큼 감소된 것을 의미합니다.
  
  
• key_len에 대해서는 추후 정리하겠습니다만, 간략하게 key_len이 작을수록, 더 적은 데이터를 메모리에 적재하며, 쿼리 성능이 향상됩니다. (쿼리 실행 시 사용된 인덱스 키의 길이를 뜻한다고 합니다) key_len이 17 -> 8로 절반 가량 줄었습니다.
  
  
• filtered 또한 100 -> 10 으로 90% 감소하였는데요. filtered는 필터링되는 비율로서 100% 인 경우에 테이블의 모든 데이터가 조에 의해 읽혀지지만 10%로 되어 읽어야 할 데이터가 줄어든 것입니다. 이는 메모리 사용량이 감소하고 속도 측면으로도 유리합니다.

 

결론

서비스를 개발하다 보면 페이징 처리는 반드시 필요한 부분 중 하나일 것입니다.

 

다만 많은 수의 데이터를 불러와서 CHUNK 단위로 처리하는 spring-batch 서비스나 많은 데이터를 쪼개어 처리하는 경우에는 id 값 기반 조회가 훨씬 좋은 성능을 내어주네요.

 

따라서 서버 내부에서 단순히 많은 양의 데이터를 나누어 읽고 싶을 때는 id 값을 기반으로 조회를 하거나, 데이터를 서버 내에서 감당 가능한 수준이라면 한번에 데이터를 불러오는 것이 훨씬 이득일 것입니다.

 

긴 글 읽어 주셔 감사합니다!