[트러블 슈팅] 페이징 쿼리 최적화 - offset, no offset, covering index

 

[트러블슈팅] 인덱스로 쿼리 최적화하기 와 이어지는 트러블 슈팅이다.

지난 글에서는 인덱스로 정렬 비용을 줄이는 데에는 성공했다.

 

하지만 OFFSET 30 LIMIT 10 으로 인해 10개만 조회하면 되지만

40개를 읽고 필요한 10개를 반환하는 문제가 발생했다.

 

---

 

Offset (Offset-based Pagination)

LIMIT A OFFSET B라고 한다면 DB 옵티마이저는 A + B만큼의 데이터를 읽은 후

불필요한 부분은 버리는 식으로 동작한다.

 

그렇기 때문에 Offset 기반 페이지네이션은 구현이 단순하다는 장점이 있지만

뒷페이지로 갈수록 읽어야 할 데이터의 총량이 많아져 성능이 저하된다.

 

study 테이블에 약 삼백만개의 데이터가 있다.

 

-- 4번째 페이지 조회
SELECT s.id,
       s.name,
       s.category,
       s.finished,
       s.activated
FROM study s
WHERE s.name LIKE '%자바%'
  AND s.category = 'IT'
ORDER BY s.created_date DESC
LIMIT 10 OFFSET 30;

-- 50000번째 페이지 조회
SELECT s.id,
       s.name,
       s.category,
       s.finished,
       s.activated
FROM study s
WHERE s.name LIKE '%자바%'
  AND s.category = 'IT'
ORDER BY s.created_date DESC
LIMIT 10 OFFSET 499990;

 

스터디를 '자바' 검색어와 IT 필터링을 해서 조회하는 쿼리이다.

첫번째 쿼리는 4번째 페이지를 조회하고 있고

두번째 쿼리는 50000번째 페이지를 조회하고 있다.

 

쿼리를 실행하니 6초나 차이가 나는 것을 확인할 수 있다.

 

SELECT문 앞에 EXPLAIN 을 붙이면 실행 계획을 확인할 수 있다.

실행계획을 보면 4번째 페이지를 조회하는 쿼리의 type은 index로

데이터 양이 적기 때문에 인덱스만으로도 감당 가능해서 index scan 전략이 선택되었지만

50000번째 페이지를 조회하는 쿼리의 type은 ALL로 테이블을 풀스캔하고있다.

 

MySQL 옵티마이저가 인덱스 순서대로 50010개를 읽기보다는

인덱스 전략을 포기하고 전부 읽는 쪽이 더 낫다고 판단했기 때문이다.

 

No Offset (No Offset-based Pagination)

No Offset 기반 페이지네이션 방식은 마지막으로 읽은 row를 기준으로 다음 데이터를 가져오는 방식이다.

커서 기반 페이지네이션(cursor pagination)이라고도 한다.

 

SELECT *
FROM study
WHERE category = 'IT'
  AND created_date < '2025-09-17 23:59:59'
ORDER BY created_date DESC
LIMIT 10;

조건(created_date) 덕분에 전 포스팅에서 만들었던 idx_study_created 인덱스를 효율적으로 활용할 수 있다.

 

쿼리를 실행하니 0초가 걸리는 것을 확인할 수 있다.

 

실행계획을 보면 type이 range로 인덱스 범위만 스캔하며

key도 idx_study_created 인덱스를 선택하고 있다.

 

no offset 방식은 인덱스로 조회를 시작할 데이터를 탐색하고 필요한 수만큼 읽는다.

그렇기 때문에 불필요한 row까지 다 읽어야하는 offset 방식보다 효율적이다.

 

하지만 이전 페이지의 값을 기준으로 페이징하기 때문에 특정 페이지로 넘어갈 수 없다는 단점이 있다.

그렇기에 무한 scroll 형태나 더보기 버튼이 있는 형태로만 페이징 기능을 사용할 수 있다.

관리자 페이지는 무한스크롤보다는 페이지네이션을 제공하는 것이 적절하다고 생각했다.

 

Covering Index

커버링 인덱스는 쿼리에 필요한 컬럼들이 모두 인덱스에 포함되어있어서

인덱스 자체만으로 쿼리의 모든 데이터를 처리할 수 있는 인덱스이다.

 

데이터를 읽지 않고 인덱스가 포함된 정보만으로 쿼리가 가능한 인덱스이다.

인덱스만으로 쿼리를 커버할 수 있기 때문에 조회 성능이 크게 향상된다.

 

CREATE INDEX idx_study_covering
ON study (category, created_date DESC, id, name, finished, activated);

커버링 인덱스를 걸고 쿼리 실행을 해보았다.

 

0.5초로 no offset 만큼은 아니지만 개선된 것을 확인할 수 있다.

 

-> Limit/Offset: 10/499990 row(s)  (cost=198424 rows=0) (actual time=662..662 rows=10 loops=1)
    -> Filter: ((s.category = 'IT') and (s.`name` like '%?자바%'))  (cost=198424 rows=161963) (actual time=0.196..630 rows=500000 loops=1)
        -> Covering index lookup on s using idx_study_covering (category='IT')  (cost=198424 rows=1.46e+6) (actual time=0.184..482 rows=500000 loops=1)

 

실행계획을 보면 idx_study_covering 인덱스 덕분에

SELECT에 필요한 컬럼이 인덱스에 모두 있기 때문에 테이블까지 가지 않고 인덱스에서 바로 읽는다.

 

---

 

커버링 인덱스를 적용하니 테이블 접근 없이 인덱스만으로 결과를 반환할 수 있어서 성능이 확실히 개선되긴했다.

 

하지만 커버링 인덱스 또한 Offset 방식이기 때문에 불필요한 테이블 접근을 줄여 성능을 보완해주긴 하지만

현재의 상황과 다른 대용량 데이터 환경에서는 No Offset 방식이 더 효율적일 것 같기는 하다.

 

그렇지만 지금 나의 상황에서는 Covering Index로도 적절하다는 판단이 들었다.

방식	실행 시간
Offset	6.453
No Offset	0.000
Covering Index	0.547

 

Covering Index로 약 91.5% 성능 개선을 할 수 있었다 🎉 🎉