Paging Optimization - Covering Index

Paging 시 성능 개선을 위해 No Offset 방식과 Covering Index를 활용하는 것이 해당 포스팅의 목표입니다.

 

 

지난 포스팅은 Covering Index에 대해 다뤘습니다.

이번 포스팅은 이 Covering Index를 사용해서 성능을 개선하는 방법에 대해 다루고자 합니다.

데이터베이스 성능 개선이기 때문에 Spring 뿐만 아니라 모든 곳에서 활용할 수 있습니다.

 

 

 

Paging

일반적인 웹 서비스에서 Paging은 아주 흔하게 사용되는 기능입니다.

 

간혹 Paging과 Pagination이라는 단어를 둘 다 확인 할 수 있는데요.

궁금해서 찾아보았는데, 아래와 같은 뜻을 가진다고 합니다.

Pagination는 "act of creating pages for a document, book, etc, or determining when to truncate text on the pages"

Paging은 "arrangement of pages in a book or other publication"

해석해보면, Pagination은 문서나 책 등 페이지를 만들거나 페이지의 텍스트를 잘라낼 시기를 결정하는 행위이고,
Paging은 책이나 다른 출판물의 페이지 배열이라고 합니다.

전자가 조금 더 동적인 의미를 남는 군요.

 

데이터가 많아지면 질수록 뒤에 있는 데이터를 조회할 때 Paging의 성능이 저하되는데요.

흔히 볼 수 있는 이 페이징 기술은 예기치 못하게 높은 성능을 요구할 때가 있습니다.

 

상황에 따라 수십 초 ~ 수 분까지 느려질 수 있는데요.

대량의 데이터를 Paging 할 때에는 단순히 인덱스만 태운다고 해서 성능 문제가 해결되진 않습니다.

 

그 이유는 아래 그림을 보면 쉽게 이해할 수 있습니다.

 

 

 

 

LIMIT 조건을 사용해서 10번 째 row까지 읽고나면, 

그 이후에는 10번 째를 읽고 난 후 버리고 (dropped rows) 그 이후의 데이터를 취합니다.

 

극단적인 예시를 들어보자면, 100만번 째 데이터를 읽으면 어떻게 될까요?

100만 건을 읽고 버린 후 그 이후의 데이터를 취하는 굉장히 비효율적인 과정을 거쳐야 합니다.

물론, 조회 시간도 현저히 떨어지게 됩니다.

 

그럼 테스트를 통해 얼마나 큰 차이가 보여지는 지 확인해보도록 하겠습니다.

 

 

 

Performance Test

 

 

0건 부터 1,000 건 조회

13,000건 부터 1,000 건 조회

 

위에서 큰 차이가 생긴다는 것을 볼 수 있는데요.

#1은 0건부터 1,000건만 단순히 조회했기 때문에 0.27 sec 가 걸렸고,

#2는 13,000건이 될 때까지 조회한 후 그 이후의 1000건을 불러오기 때문에 3.62 sec가 걸린 것입니다.

책을 읽을 때 첫 장부터 1000글자 읽는 것과 13000장을 넘긴 후에 1000장을 읽을 때랑 비슷하다고 생각하시면 됩니다.

 

 

그렇다면 이런 페이징을 어떻게 하면 개선시킬 수 있을까요?

아래와 같은 두 가지 방법이 있습니다.

 

하나씩 살펴보고 얼마나 개선될 수 있을지 테스트를 진행해보겠습니다.

 

 

 

Paging 성능 개선 방법 #1 

No Offset

 

e.g. offset 10000, limit 20 

 

기존의 페이징으로 보면 10,020개의 행을 읽고, 이 중 10,000 개 행을 버리고 20개만 가져옵니다.

뒤로 갈수록 읽어야 할 행의 개수가 많아 점점 느려집니다.

 

반면, No Offset 방식은 조회 시작 부분을 인덱스로 빠르게 찾아 매번 첫 페이지만 읽도록 하는 방식입니다.

NoOffset 방식은 아래와 같은 형식으로 작성합니다.

그렇다면, 어느정도의 차이를 보이는지 테스트를 통해 확인해보겠습니다.

 

Performance Test

#1.WITH OFFSET - 5.37s

#2. NO OFFSET - 0.01s

 

#1는 500 만 건을 조회한 후 그 이후의 1000건을 불러오기 때문에 5.37 sec가 걸렸고,

#2은 emp_no를 통해 인덱스를 타도록 유도 한 후, 1000건을 조회했기 때문에 0.05 sec 가 걸렸습니다.

gender는 인덱스가 걸리지 않은 컬럼으로, 어느정도의 성능을 요구하는 상황을 가정하고자 추가해봤습니다.

 

 

Execution Plan

 

실행 계획을 보는 방법은 SQL Execution Plan, 제대로 이해하기 포스팅을 확인해주세요.

 

#1. WITH OFFSET

#2. NO OFFSET

 

쿼리 플랜을 보면 위와 같습니다.

primary key 컬럼을 범위로 가져오기 때문에 type field가 range라는 것을 알 수 있죠.

 

 

 

 

 

Paging 성능 개선 방법 #2 

Covering Index

: 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스.

SELECT, WHERE, ORDER BY, LIMIT, GROUP BY 등에서 사용되는 모든 컬럼이 INDEX 에 모두 포함된 경우를 의미합니다.

 

 

물론 No Offset 방식으로 해결되는 것이 베스트이겠지만, 불가능한 상황들이 생깁니다.

그럴 때 Covering Index를 사용하여 해결하는 방법이 있는데요.

그런데, 위에 조건에 따르면 다음과 같은 궁금증이 생기고, 그에 대한 답변을 해보면 아래와 같습니다.

 

 

❓ SELECT 절에 사용되는 Column을 위해 너무 많은 컬럼이 인덱스에 포함되지 않을까?

👉🏻 Covering Index를 태우는 부분은 SELECT를 제외한 나머지만 우선으로 수행합니다.

 

 

데이터를 필터링 한 다음 인덱스만을 가져와서 해당 인덱스로 전체 데이터를 조회합니다.

가령 데이터의 아이디 값만을 조회해서 아이디에 해당하는 데이터들을 불러오는 방식입니다.

아래 테스트를 확인해보시면 이해에 도움이 되실 거에요.

 

 

Performance Test

 

#1. NO COVERING INDEX - 2.76s

#2. WITH COVERING INDEX - 1.48s

 

 

마찬가지로 테스트를 진행해보았는데요.

No Offset 만큼 드라마틱한 변화를 가져오진 못하지만, 성능이 개선 된 것을 확인할 수 있습니다.

 

No Offset 테스트와 동일하게 gender에 조건을 달아보기도 했는데요.

 

#1. NO COVERING INDEX - 4.71s

#2. WITH COVERING INDEX - 3.39s

 

위와 비슷한 결과를 확인할 수 있습니다.

마찬가지로 뛰어난 성능은 아니지만, 성능이 개선되었다는 것을 확인할 수 있습니다.

 

 

Execution Plan

 

실행 계획을 보는 방법은 SQL Execution Plan, 제대로 이해하기 포스팅을 확인해주세요.

 

#1. NO COVERING INDEX

#2. COVERING INDEX

 

 

쿼리 플랜을 보게 되면 동일한 로우 수를 Using Index로 조회하고 있는 것을 확인할 수 있습니다.

 

여기서 중요한 건 Extra 필드의 Using Index 부분인데요.

Covering Index가 사용되었다는 증거가 바로 Using Index입니다.

Backward index scan은 ORDER BY emp_no DESC 구문으로 출력된 내용입니다.

 

 

이상으로 Covering Index에 대해서 다뤘습니다.

다음 포스팅으로는 Paging 을 할 때 Covering Index로 성능을 개선하는 방법에 대하해 다루겠습니다 🙏🏻

오타나 잘못된 내용은 댓글 부탁드립니다.

감사합니다 ☺️