SQL 페이지네이션(Pagination) 최적화

Pagination(페이지네이션)을 구현하면서 최적화가 필요하다고 느낀적이 있나요?

Pagination을 구현하면서 DB데이터가 100만개 정도로 되는 페이지를 구현한 적이 있나요??

데이터가 별로 없어서 필요없다고 느낄 수 있지만, 시간이 지나면서 데이터가 10만개 이상, 특히 상태에 따른 정렬이나 검색에 따르게 된다면 얘기가 다를수 있습니다.

 

다음은 제가 실제 데이터에서 극단적인 케이스의 쿼리와 결과값을 보여드립니다.

먼저 총 데이터의 갯수가 23만개의 데이터로 예를 드려고 합니다.

극단적이겠지만 먼저 LIMIT 0,10의 결과 값과 210000,10 의 쿼리 수행속도 차이를 보여드립니다.

실제로 구현한다고 가정하더라도 관리자에서 이러한 페이지네이션의 결과값을 보여줄 일은 사실상 없다고 무방할 수 있습니다.

 

 

 

하지만 크롤링을 하거나 여기서 추가적으로 정렬 혹은 검색 부분이 추가된다면 더 많은 시간이 들어갈 수 있습니다. 

또한 여기서 이것보다 데이터가 많다면 더 많은 시간이 걸릴수가 있습니다.

우리는 쿼리의 결과값이 1sec이상 넘어가면 사이트가 느리다고 판단해야합니다.

왜냐하면 사용자 혹은 우리의 클라이언트는 그 이상 넘어가면 사이트에 대한 흥미를 잃게되거나 느리다고 불평을 할겁니다.

 

paging을 구현할 때 DB 툴에 따라서 LIMIT 혹은 OFFSET을 이용해 구현하는 경우가 많습니다. 보통 Primarty key로 인해 최적화가 되었다고 생각할 수 있습니다. 하지만 우리는 WHERE 조건이 색인이 걸린 부분이라 당현하게 최적하게 되었다고 생각할 수 있지만 아니게 되었다고 전환을 해야할 필요가 있습니다.

 

바로 row lookup에 따른 성능 저하 입니다.row lookup은 색인이 가르키는 Row를 접근하여 가져오는 행위입니다. 즉 LIMIT, OFFSET을 통해 페이지를 하는 동안 지나치는 Row들을 바라보며(lookup) 성능이 느려지게 됩니다. 자세한 내용은 먼저 성능 결과를 확인한 뒤 파악하겠습니다.

 

출처:  http://devoluk.com/mysql-limit-offset-performance.html

위의 그래프는 페이지 위에서 언급하듯 페이지 값이 커질수록 속도가 느려지지만 join을 이용한다면 우리가 영점 사격을 하듯 평균적인 값을 유지하는 것이 보입니다. 그래서 저는 limit만 사용하는 것이 아닌 Join절과 함께 쿼리를 사용하는 것을 권장드립니다.

 

일반 쿼리 예시

SELECT * FROM review LIMIT 210010,20

 

JOIN을 이용한 예시(use_review_seq는 primary key입니다)

SELECT * FROM (
	SELECT use_review_seq FROM review  LIMIT 210010,20
) tb1
JOIN review tb2
ON tb1.use_review_seq = tb2.use_review_seq

여기서 정렬을 추가한다면 결과값을 추출하는 시간은 더 걸립니다.

일반 페이지네이션 예시)

SELECT * FROM review ORDER BY product_cd LIMIT 210010,20

 

JOIN을 이용한 예시

SELECT * FROM (
	SELECT use_review_seq FROM review ORDER BY product_cd LIMIT 210010,20
) tb1
JOIN review tb2
ON tb1.use_review_seq = tb2.use_review_seq

이러한 쿼리 결과값을 확인하면서 DB의 인덱스에대해 명확히 이해하는지가 중요합니다. 어느정도 이해한다고 싶으면서 갈길이 멀다고 느껴집니다. 그래서 머리가 아프면서도 재밌다고 느껴지네요