페이징 성능 최적화 - No Offset 은 왜 빠를까?

이 글에서는 No Offset 방식이 왜 빠른지 이해하기 위한 개념과 예시를 상술합니다.

 

LIMIT

MySQL 에서 LIMIT 은 범위를 제한할 때 사용합니다.

쿼리 결과에서 지정된 순서에 위치한 레코드만 가져오고 싶을 때에 유용하게 사용됩니다.

 

 

SELECT *
FROM test_tb
LIMIT 0, 10;

위와 같은 쿼리에서 만약 LIMIT 이 없었다면 테이블 풀 스캔을 실행하여 결과값을 반환했을 것입니다.

하지만 이 때 LIMIT 을 사용하면서 MySQL 엔진은 레코드를 10건만 읽은 직후 반환하게 됩니다.

따라서 모든 레코드를 읽어야하는 것 보다 훨씬 부하가 줄게 됩니다.

 

-- 1
SELECT *
FROM test_tb
GROUP BY first_name
LIMIT 0, 10;

-- 2
SELECT *
FROM test_tb
WHERE emp_no between 10001 and 11000
ORDER BY first_name
limit 0, 10;

다만 위와 같이 단순히 LIMIT 만 걸려있는 것이 아니라,

GROUP BY, ORDER BY 등 어떤 조건이 있다면 상황은 달라집니다.

 

1번 쿼리는 테이블에서 모든 쿼리를 읽고 group by 를 처리한 이후 limit 을 처리합니다.

 

2번 쿼리는 where 절에 일치하는 레코드를 읽은 후 first_name 컬럼 값으로 정렬하고

이때 정렬하며 10건이 완성되면 결과를 반환합니다.

 

위의 두가지 경우는 모든 레코드를 읽고 후처리를 한 이후 결과값을 반환하기 때문에

LIMIT 을 사용하며 얻을 수 있는 성능적 향상을 취하기 어렵습니다.

따라서 LIMIT 을 효율적으로 사용하기 위해서는 옵티마이저의 실행 계획을 파악할 필요가 있습니다.

 

 

 

SELECT 절의 처리 순서

 

위의 순서는 각 요소가 없는 경우는 가능하지만 순서가 바뀌어서 실행되는 형태는 거의 없다고 봐도 무방합니다.

ORDER BY 나 GROUP BY 절이 있더라도 인덱스를 이용해 처리될 때는 그 단계 자체가 불필요하므로 생략됩니다.

또한 LIMIT 은 모든 절차를 마치고 난 이후에야 적용이 됩니다.

 

따라서 각 단계에서 인덱스를 적절히 사용하거나,

이미 사용하고 있다면 절차 자체를 줄여주는 노력이 필요합니다.

아래에서 예시로 살펴보겠습니다.

[ 참고 글, Covering Index ] 위와 관련된 모든 내용을 담고있진 않지만, 읽어보시면 도움이 될만한 내용입니다.

 

 

select * 
from test_tb
order by id desc
limit 800000, 10;

--// => 10 rows fetched 280ms

위의 쿼리는 일반적으로 잘 사용하는 형태의 페이징 쿼리입니다.

현재 테이블에는 100만 개의 데이터를 준비해두었고,

80만 부터 10개의 row 를 가져오는 데에 260ms 가 걸렸습니다.

 

앞서 살펴본 실행계획에 따르면, 모든 레코드를 테이블에서 가져와 id 를 기준으로 역순 정렬하고

그 이후 80만까지 읽고 추가로 10개를 더 읽은 이후 이전의 80만개는 버려 10 개만 반환했을 것입니다.

 

그렇다면 위의 페이징 쿼리를 사용하며 레코드를 가져올 때 가져올 때

항상 이전의 모든 데이터를 읽고 버리고 10개만 반환하겠구나 라고 예상할 수 있습니다.

이러한 비효율을 개선하기 위해 No Offset 방식을 이용합니다.

 

 

 

No Offset

No Offset 은 Offset이 없는 페이징 쿼리입니다.

시작해야하는 조건을 offset 이 아닌 다른 방식으로 처리하여 모든 레코드를 읽지 않고 10개만 읽어 반환하여

시작 위치가 어디든 첫 페이지를 읽는 것과 동일한 효과를 내게 해주는 기법입니다.

아래에서 예시를 통해 살펴보겠습니다.

 

select *
from test_tb
where id <= 1000000 - 800000
order by id desc
limit 10;

--// => 10 rows fetched 2ms

조건은 No Offset 을 사용하지 않았던 쿼리와 마찬가지로 80만 에서 시작해서 10개만 찾아오는 것입니다.

현재 쿼리에서 id 는 pk, 인덱스로 관리됩니다.

실행계획을 토대로 살펴보면 우선 id 가 20만 이하인 레코드를 찾아왔을 것입니다.

이후 id 를 토대로 역순 정렬을 하면서 10개가 찾아졌다면 바로 반환했을 것입니다.

 

따라서 No Offset 을 사용하지 않았던 쿼리에서는 80만 + 10 모두정렬 후 10개를 반환했다면,

No Offset 방식은 order by 를 수행하는 동시에 id 가 인덱스이므로 역순으로 읽어 나가다가

result set이 10개가 되는 순간 바로 반환했을 것입니다.

 

 

No Offset 방식을 실행계획으로 확인해본 결과입니다.

예상한 대로 Using where 를 통해 시작 위치를 index(id) 를 사용하여 탐색했고

이후 range scan 을 했다는 것을 알 수 있습니다.

또한 rows 도 80만 개가 아닌 43만개로 추정되는 것을 확인할 수 있습니다.

(rows 는 추정치이므로 실제 반영된 결과는 아닙니다. 다만 80만보다는 절반에 가까운 수치임을 알 수 있습니다.)

 

 

 

SELECT 절의 실행 계획과 페이징에 대한 최적화를 예시와 함께 살펴보았습니다.

살펴보았듯, No Offset 방식은 페이징 쿼리의 성능을 크게 향상시킬 수 있는 효율적인 방법입니다.

이 방식을 통해 불필요한 데이터 읽기를 최소화하고, 인덱스를 효과적으로 활용하여 쿼리 처리 시간을 줄일 수 있습니다.

 

끝!