프로젝트에 커버링 인덱스를 적용해볼까?

프로젝트를 진행 중에 최근 데이터를 정렬하라는 요구사항에 대해 빠르게 성능을 올리고 싶어 여러가지를 찾아보던 중 커버링 인덱스라는 개념을 발견했다.

인덱스 ?

인덱스는 데이터를 효율적으로 탐색하는 방법이다. 하나의 색인같은 것으로 MySQL 미리 정렬된 상태의 하나의 클러스링된 테이블이 존재하면 이 테이블에 접근하여 빠르게 해당 아이디 값을 찾아가는 방식으로 동작한다. 이 때 성능이 매우 빠르기 때문에 DB 성능향상을 위해 꼭 필요한 기술이다.

커버링 인덱스?

커버링 인덱스 는 쿼리를 충족하는데 필요한 모든 데이터를 갖는 인덱스를 뜻한다.(SELECT/WHERE/GROUP BY/ ORDER BY) 아까 인덱스에서 잠시 언급한 미리 정렬된 상태의 클러스터링된 테이블에 접근하여 데이터를 인덱스 테이블에서 조회하는데 이 때 대용량 처리 시 조회 성능을 상당히 올릴 수 있다.

커버링 인덱스를 사용해 보기 전에 DB 환경은 MySQL 5.7 + DATAGRIP 으로 설정하였고, 테이블은 다음과 같다.

현재 member_id와 email nickname이 인덱스 테이블로 묶어놓은 상태이다.

성능 비교

결과를 사전에 미리 공개하자면 두 쿼리를 비교 하여 차이를 나타내 보았다. (110만건의 데이터라 유의미한 차이는 보기 힘들었다.)

# 커버링 인덱스 SQL문
select member_id, email, nickname from member where email like 'member11%' order by member_id;

# 커버링 인덱스 Z
select member_id, email, birth, create_time from member where email like 'member11%' order by member_id;

두 쿼리를 테스트해보았을 때 결과는 다음과 같았다.

# 커버링 인덱스 O
select member_id, email, nickname from member where email like 'member11%' order by member_id
[2022-07-21 13:49:04] 500 rows retrieved starting from 1 in 117 ms (execution: 89 ms, fetching: 28 ms)

# 커버링 인덱스 X
select member_id, email, birth, create_time from member where email like 'member11%' order by member_id
[2022-07-21 13:49:15] 500 rows retrieved starting from 1 in 172 ms (execution: 143 ms, fetching: 29 ms)

조회 연산이 커버링 인덱스는 0.089s 커버링을 타지 않은 경우는 0.143s로 약 0.06s의 차이를 보였다. 아주 적은 데이터를 조회하는데도 불구하고 이 정도 차이가 나타났다.

실제로 jojoldu님의 블로그에 있는 사진을 빌려오면 뚜렷한 차이를 볼 수 있다.

 

데이터 1300만건 기준으로 기존의 쿼리보다 5배 가까이 성능이 향상된 것을 알 수 있다. 커버링 인덱스는 데이터가 많으면 많을 수록 더욱 빛을 본다.

커버링 인덱스가 빠를 수 있는 이유

DB를 조회할 때 느려지는 이유는 select 절의 영향이 있다. 인덱스를 탔음에도 select를 하기 위해서 물리 데이터에 접근을 해야하기 때문에 성능이 비교적 느려질 수 있다.

클러스터링 된 인덱스 테이블을 조회할 때 조건 절 모두 한 index table에 존재한다면 index 테이블에서 바로 조회하여 가져올 수 있기 때문에 성능이 훨씬 빨라진다.

또한 조건절에서만 인덱스를 타더라도 해당 인덱스에서 검색된 row에 대해서만 조회를 하기 때문에 성능이 비교적 잘나온다.

실행 계획을 보면 더 명확해 질 것이다.

index를 타지 않는 쿼리의 실행계획

index를 탈 때의 쿼리의 실행 계획

보다 싶이 index를 사용해서 조회해오기 때문에 성능이 훨씬 빠르게 사용할 수 있는 것이다.

회고

테스트를 하는 과정에서 인덱스 테이블과 인덱스를 이용한 조회 방식에 많은 착오가 있었고 부족한 지식도 많았었다. DB에 대한 기초 지식의 부재가 기술을 선택함에 있어 매우 중요함을 깨닫는 기회가 될 수 있었고, 이번 기회로 인덱스 클러스터링 테이블에 대한 존재도 알 수 있었다. 다음엔 한 기술을 공부하기전에 기반이 되는 기초 CS 지식을 확실히 학습하고 연구해보면 더 많은 도움이 될 수 있을 것 같다.

또한 아이디는 기본적으로 모든 인덱스 테이블에 존재함으로 같이 만들어줄 필요가 없는데 이러한 점도 당연히 알고 써야할 것 같다. (DB 기본 지식 너무 중요)

커버링 인덱스를 쓰기 전에 …

커버링 인덱스를 걸어준다는 것은 결국 테이블을 하나 더 만들어 주는 것이기 때문에 성능은 올라가더라도 데이터 용량에 부하가 갈 수 있다. 이런 점을 고려하고 적용해야할 것 같다.

또한, 인덱스가 많이 걸려있으면 그 만큼 데이터가 삽입/삭제/수정에 대해서 정렬을 하기 위해 관련된 연산이 많다면 성능에 대한 고려를 꼭 해야할 것 같다.

그리고 PK를 이용해서 조회를 해온다면 기본적으로 select를 제외하곤 모두 인덱스를 탄다. 그리고 해당 PK에 해당하는 row에 대한 컬럼만 조회하기 때문에 결국 성능에 큰 부하가 걸리지 않는다면 무리하여 커버링 인덱스를 거는 것은 위험할 수 있다.

Reference.

https://jojoldu.tistory.com/529

2. 페이징 성능 개선하기 - 커버링 인덱스 사용하기

https://jojoldu.tistory.com/476

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