페이징 성능 개선: offset vs no offset vs covering index

페이징 성능 개선: offset vs no offset vs covering index

웹 서비스에서 페이징은 아주 흔하게 사용되는 기능이다. 일반적으로 페이징은 구현한다면 LIMIT ... OFFSET ...; 와 같은 패턴으로 OFFSET을 사용하는데 이는 점차 데이터가 많아짐에 따라 수십초 ~ 수분까지 조회가 느려질 수도 있다. 따라서 No offset과 Covering index를 통해서 페이징 기능을 개선하는 방법에 대해서 살펴보자.

 

product 테이블에 대략 1,572만 건 상품 데이터가 있다.

 

OFFSET

LIMIT 쿼리는 결과에서 지정된 순서에 위치한 레코드만 가져오고자 할 때 사용된다. 위 쿼리는 다음과 같은 순서로 실행된다.

1. ORDER BY id DESC: 일반적으로 최신에서 과거순으로 상품을 조회하기 때문에 id를 내림차순으로 정렬한다. 이 때, id가 PK이기 때문에 인덱스로 정렬되어 있어 filesort로 정렬 처리를 할 필요는 없다.
2. 정렬된 결과에서 상위 10건만 사용자에게 반환한다.

MySQL의 LIMIT은 위처럼 항상 쿼리의 마지막에 실행된다. LIMIT의 특성은 LIMIT에서 필요한 레코드 건수만 준비되면 즉시 쿼리를 종료한다는 것이다. 하지만 아래에서 살펴볼 커버링 인덱스를 태우지 않은 일반적인 조회 쿼리는 ORDER BY, OFFSET ~ LIMIT을 수행할 때 데이터 블록으로 접근을 하게 된다.

 

쿼리의 실행 계획 중 type과 rows 컬럼을 확인해보자. type은 index로 MySQL 옵티마이저가 Full index Scan을 선택하고, rows 컬럼은 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미한다. 즉, 커버링 인덱스로 처리할 수 없는 product 테이블의 데이터 파일을 처음부터 읽으면서 8,000,010건의 레코드를 읽은 후, 8,000,000건은 버리고 마지막 10건만 사용자에게 반환한다는 것이다. 따라서 실제 사용자의 화면에는 10건만 표시되지만, "LIMIT 10 OFFSET 8000000"처럼 OFFSET의 수치가 커지면 커질수록 읽어야 하는 레코드는 많아지기 때문에 상당히 오랜 시간이 걸리는 문제가 발생할 수 있다.

 

No Offset

No Offset방식은 Offset 키워드를 사용하지 않는 방식을 말한다. 위 Offset 페이징 방식이 페이지 번호(offset)와 페이지 사이즈(limit)를 기반으로 한다면 No Offset은 위 쿼리처럼 페이지 번호(offset)가 없는 방식이다. 예를 들어, 이전 페이지의 상품에서 id의 가장 마지막 값이 1,000,000인 경우에 다음 페이지에서는 id가 1,000,000 이후의 데이터 중 10개를 가져오는 방식으로 진행된다.

 

No offset 방식을 사용한 쿼리의 실행 계획을 살펴보자. type 컬럼은 range이고, Extra에 Using where;이 있는 것을 확인할 수 있다. type 컬럼이 range라는 것은 인덱스 레인지 스캔 형태의 접근 방법을 말한다. 인덱스를 하나의 값이 아니라 범위로 검색한 경우를 의미하는데 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다. 즉, No offset 방식이 위 방식보다 빠른 이유는 인덱스로 조회를 시작할 레코드를 탐색하고 그다음 필요한 수만큼 레코드를 읽기 때문이다.

하지만 No offset 방식은 이전 페이지의 값을 기준으로 페이징하기 때문에 특정 페이지로 넘어갈 수 없다는 단점이 있다. 따라서 무한 scroll 형태나 more 버튼이 있는 형태로만 페이징 기능을 사용할 수 있다.

 

Covering Index

인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업이다. 위에서 살펴봤듯이 커버링 인덱스가 없는 경우, 데이터 블록으로의 접근이 빈번하게 발생할 수 있으며, 이는 디스크 I/O 비용이 증가할 수 있다는 것을 의미한다. 특히, 대용량 데이터에서 ORDER BY, OFFSET ~ LIMIT을 사용하는 경우 성능에 영향을 줄 수 있다. 따라서 디스크 I/O를 최소화하고 쿼리의 성능을 향상시킬 수 있는 방안으로 커버링 인덱스가 있다.

커버링 인덱스란 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 모두 처리할 수 있는 쿼리를 말하고, EXPLAIN 명령어를 통해 실행계획을 살펴봤을 때 Extra 컬럼에 "Using index"가 표시된다. 즉, 쿼리에서 필요한 모든 열을 인덱스에 포함하고 있어, 별도의 테이블에서 데이터를 읽어오지 않고도 쿼리 결과를 생성할 수 있기 때문에 디스크 I/O를 줄여 쿼리 성능을 향상시킬 수 있다.

 

커버링 인덱스를 사용하여 페이징 쿼리를 작성했을 때 MySQL 옵티마이저는 아래와 순서로 처리한다.

-> Nested loop inner join (cost=8.23e+6 rows=10)
    -> Table scan on pt (cost=692013..692015 rows=10)
        -> Materialize (cost=692013..692013 rows=10)
            -> Limit/Offset: 10/8000000 row(s) (cost=692012 rows=10)
                -> Covering index scan on product using PRIMARY (reverse) (cost=692012 rows=8e+6)
    -> Single-row index lookup on p using PRIMARY (id=pt.id) (cost=0.942 rows=1)

1. Covering Index scan on product using PRIMARY (reverse):
   먼저 product 테이블에서 디스크 I/O를 최소화하기 위해 커버링 인덱스를 활용한 스캔 작업을 수행한다. PRIMARY 키를 역순으로 사용하고 있으며, 예상 결과 레코드 수는 8e+6(8,000,000)건이다.
2. Limit/Offset: 10/800000 row(s):
   커버링 인덱스 스캔으로 얻은 결과 중에서 LIMIT 10 OFFSET 8000000을 수행하는 작업으로 상위 10개의 행을 선택한다. 예상 결과 행 수는 10건이다.
3. Materialize:
   pt 테이블에서의 스캔 결과를 메모리에 저장하는 작업을 하고, 나중에 조인 연산에서 활용된다.
4. Table scan on pt:
   pt 테이블에 Table Scan을 수행하여 조인 연산에 필요한 데이터 10개의 행을 반환한다.
5. Single-row index lookup on p using PRIMARY (id=pt.id):
   인덱스(pk)를 사용하여 p 테이블에서 pt 테이블의 id와 일치하는 단일 행(1건)을 조회한다.
6. Nested loop inner join:
   두 개의 테이블을 조인하는 방법 중 하나로 5번 단계를 4단계에서 반환된 각 행에 대해 반복 수행하여 10건의 행을 반환한다.

 

쿼리 성능 비교

	offset	no offset	covering index
0	1ms	2ms	2ms
100,000	60ms	2ms	35ms
1,000,000	400ms	2ms	183ms
5,000,000	1,867ms	2ms	778ms
8,000,000	2,973ms	2ms	1,244ms

 

데이터 크기를 10,000,000건으로 고정했을 때 OFFSET 크기를 키웠을 때 각 방식의 성능 결과이다. No Offset 방식은 크기가 커져도 현재 데이터 크기에 대해서는 성능적으로 이슈가 발생하지 않고 속도가 일정했다. 그리고 Covering Index 방식과 Offset 방식은 OFFSET 크기가 커질수록 성능적으로 느려지는 것을 확인할 수 있는데 OFFSET 방식 보다는 Covering Index 방식이 더 빠른 조회 결과를 보여줬다.