커버링 인덱스로 뷰 쿼리 개선하기

현재 진행중인 도서 공유 서비스에는 포인트라는 것이 존재합니다.

포인트는 서비스 내에서 사용할 수 있는 현금과 비슷한 느낌으로 포인트를 사용하여 책을 대여할 수 있습니다.

실제 운영하는 서비스에서는 돈과 관련된 데이터에 오류가 발생하면 이는 곧바로 회사의 손실로 직결되는 중요한 데이터입니다.

따라서 데이터 정합성을 유지하기 위해 다음과 같이 설계했습니다.

• 회원의 포인트 변화는 포인트 이력과 변화량을 테이블에 저장
• 포인트를 사용해야할 시점에 포인트 이력 테이블의 변화량을 계산하여 현재의 포인트를 조회

ERD

 

뷰에서 사용될 쿼리

테스트를 진행할 51348 아이디에는 50만건의 더미 데이터 이력을 삽입했습니다.

select pv1_0.user_id,pv1_0.total_point from ( SELECT u.id AS user_id, SUM(p.amount) AS total_point 
FROM users u JOIN point p ON u.id = p.user_id GROUP BY u.id ) pv1_0 where pv1_0.user_id=51348;

쿼리 실행 계획

쿼리 실행 시간

 

실행 결과 : 0.875s

 

현재 서비스에서 포인트를 조회하는 쿼리는 단독으로 실행되는 경우는 드뭅니다.

포인트를 조회하고 포인트 잔여 금액에 따라 추가적인 메서드를 실행하게 됩니다.

하지만 포인트 조회에서 1초 가까이 시간이 걸린다면 이는 성능저하를 유발하는 원인이 됩니다.

 

---

개선 과정

현재 쿼리의 동작 과정은 다음과 같습니다.

• user와 point 테이블을 조인
• 조인된 결과에 있는 point의 PK를 통해 디스크에 접근하여 amount를 반환
• 반환된 amount 를 연산하여 결과 반환

point 테이블의 PK 인덱스에 접근할 때, 연산에 필요한 amount 를 같이 가져올 수 있다면 디스크에 접근할 필요가 없게 되어 쿼리 실행 시간을 단축시킬 수 있습니다. 이 개념을 커버링 인덱스라고 합니다.

 

💡 커버링 인덱스란 ?

쿼리에 필요한 컬럼을 인덱스에서 찾을 수 있다면 인덱스에 있는 데이터들을 사용해 쿼리를 완성하는 것.
클러스터 인덱스는 원본 데이터의 주소를 가지고 있고, 논 클러스터 인덱스는 클러스터의 인덱스를 가지고 있습니다.
사용되는 인덱스에 현재 필요한 데이터가 있다면 디스크에 있는 데이터에 접근할 필요가 없게됩니다.
따라서 원본 데이터 접근 없이 쿼리를 완성할 수 있기에 IO 비용이 절감됩니다.

 

커버링 인덱스를 적용하기 위해 인덱스 생성

create index idx_point_test on point (user_id, amount);

 

포인트 연산에 필요한 amount 를 복합인덱스로 생성해주었습니다.

이제 point의 amount를 디스크에서 가져오지 않고, user_id 의 인덱스에 접근하면 amount를 가져올 수 있게 됐습니다.

 

실행결과를 통해 정말 커버링 인덱스를 사용하는지 알아보겠습니다.

인덱스 적용 후 쿼리 실행계획

위의 실행 계획과 다르게 point 테이블의 Extra 에 Using index 가 추가된 것을 볼 수 있습니다.

 

Extra 의 Using index 는 인덱스만을 사용하여 필요한 데이터를 모두 얻을 수 있음을 뜻합니다.

즉 생성된 인덱스를 적용하여 커버링 인덱스를 사용할 수 있다는 뜻입니다.

 

 

동일한 환경에서 다시 한번 쿼리를 실행시킨 결과로 0.172s 가 나왔습니다.

인덱스 적용 후 쿼리 실행 시간

0.875s -> 0.172s

커버링 인덱스를 적용하고 쿼리 실행 시간을 약 5배 단축 시킬 수 있었습니다.

 

---

참고자료

• 1. 커버링 인덱스 (기본 지식/  WHERE / GROUP BY)