대용량 데이터를 빠르게 조회하기 - 페이징 전략 비교(offset, no offset, Where In)

- Intro
- Offset
- No Offset
- 성능 비교

📌  INTRO

대용량 데이터로 정산이나 집계 작업을 위해서는 전체 데이터를 한 번에 조회하기 어렵습니다. 

메모리에 전체 데이터를 로딩해야 하기 때문이죠. 데이터가 1000만 ~ 1억 건이 된다면 일반적인 사양의 컴퓨터라면 현실적으로 불가능합니다.  

 

그래서 데이터를 나눠서 조회해야 합니다. 조회 단위를 Page라고 하고 Page별로 조회하기 위하는 작업을 Paging이라고 합니다.

Paging 전략에는 대표적으로 1) Offset 2) No Offset 이 있습니다. 

 

결론적으로 No Offset 방식이 가장 빠릅니다. 그 이유를 페이징 전략별로 동작 원리와 쿼리 실행계획 결과로 파악해 보겠습니다. 

(조회 조건에 사용되는 컬럼에 인덱스 설정이 잘 되어있을 경우 비교가 가능합니다!)

 

📌  Offset

 

offset 페이징은 페이지 크기별로 페이지를 나누는 것입니다. 

첫 번째 페이지에서 10개 레코드를 조회했다면 그다음 페이지는 10개 이후인 11번째 레코드부터 조회하는 것입니다. 

 

대용량 데이터를 모두 스캔해야 된다면 Offset 방식은 성능이 좋지 못합니다. 

레코드를 모두 스캔하며 조회를 시작 위치를 찾을 때

11번째는 0 ~ 11번, 21번째는 0 ~ 21, 10만 번째는 0 ~ 10만 식으로 반복해서 첫 레코드부터 탐색하게 됩니다. 

이는 offset의 값이 커질수록 조회 속도가 느려지게 되는 원인입니다.

 

Explain Analyze로 쿼리 작업 내용을 확인해 보면

 

LIMIT 10000 OFFSET 0	Limit/Offset:  10000/1 row(s)   (cost=446979 rows=10000)  (actual time=0.0468..22.8 rows=10000 loops=1)
LIMIT 10000 OFFSET 7000000	Limit/Offset:  10000/7000000 row(s)   (cost=446979 rows=0)  (actual time=13150..13180 rows=10000 loops=1)

 

Offset 이 0인 경우 첫 레코드 조회까지 0.0468 ms, 모든 데이터 조회까지 22.8 ms 가 소요되었습니다. 

Offset 이 7000000인 경우 첫 레코드 조회까지 13150ms, 모든 데이터 조회까지 13180 ms가 소요되었습니다. 

 

Offset이 클수록 조회를 시작할 레코드를 찾는 시간이 오래 걸리는 것을 알 수 있습니다.

 

📌  No Offset

 

 

No Offset 방식은 인덱스로 조회를 시작할 레코드를 탐색하고 그다음 필요한 수만큼 레코드를 읽습니다. 

시작 위치를 인덱스로 빠르게 찾기 때문에 Offset 방식보다 성능이 좋습니다. 

 

이번에도 Explain Analyze로 쿼리 작업 내용을 확인해 보면

 

WEHRE seq > 0 LIMIT 0, 10000	-> Limit: 10000 row(s)   (cost=880038 rows=10000)  (actual time=0.905..41.6 rows=10000 loops=1)
WEHRE seq > 7000000 LIMIT 0, 10000	-> Limit: 10000 row(s)   (cost=745426 rows=10000)  (actual time=0.0445..70.9 rows=10000 loops=1)

 

seq >  0인 경우 첫 레코드 조회까지 0.905 ms, 모든 데이터 조회까지 41.6 ms 가 소요되었습니다.

seq > 7000000인 경우 첫 레코드 조회까지 0.0445 ms, 모든 데이터 조회까지 70.9 ms가 소요되었습니다. 

 

Offset 방식에 비하면 첫 레코드 조회까지 시간 차이가 매우 적다는 게 확인됩니다.

📌  성능 비교

 

 

데이터 수를 100만 -> 500만 -> 1,000만으로 늘려 조회 테스트 결과 

Offset의 추세선 경사도가 급격하게 변경되었습니다. 반면 No Offset이 가장 안정적이었습니다. 

 

📌  결론

1. Offset과 No Offset의 가장 큰 차이는 조회 시작 레코드 탐색 성능에 매우 큰 차이를 보입니다.

2. 어떤 페이징 전략이든 인덱스 설정을 통한 쿼리 튜닝이 진행되어야 합니다. 그다음에 상황에 맞게 잘 선택해야 합니다.

 

---

참고

[1] 

https://dev.mysql.com/blog-archive/mysql-explain-analyze/

[2] 

https://jojoldu.tistory.com/528