복합 인덱스로 쿼리 튜닝하기

[데이터베이스] 인덱스(index) 정리

 

위 글의 복합 인덱스를 통한 튜닝 부분을 따로 옮긴 포스팅입니다.

 
 

---

 
 
 
분명 일정한 기준이 있을 텐데 왜 얘기들이 조금씩 다른 것일까?
DB의 버전 때문일까 옵티마이저가 무조건적으로 100% 맞다는 보장이 없어서일까? 잘 모르겠다.
그래서 직접 쿼리 튜닝의 경험들을 복기하며 복합 인덱스를 생성할 때에는 어떤 순서로 인덱스를 구성해야 하는지 알아보았다.
 
 

 
서비스 내부에는 모든 유저의 장바구니(앞으로 견적함이라 부름)를 볼 수 있는 기능이 존재하는데 업종으로 필터링할 경우의 집계를 위한 쿼리의 일부이다.
(사진과 드레스를 선택했을 경우를 예시로 platform_A 테이블에 대한 인덱스 생성만 예시로 든다.)

SELECT cart_group_no
FROM platform_A.cart c
INNER JOIN service.product p
    ON p.no = c.product_no
WHERE p.category IN ("사진", "드레스")
    AND c.cart_group_no != 0
    AND c.option != 1;

 

 
 
COUNT(DISTINCT) 쿼리로 간단하게 카디널리티를 확인해 본 결과는 위와 같았고, 당연히 나는 아래와 같이 인덱스를 생성했다.

CREATE INDEX IDX_REALTIME_QUOTATION
ON platform_A (cart_group_no, product_no, option_product);

 
 

 

 

 

쿼리 실행 시간이 어느 정도 눈에 보이는 수치로 감소했다. 하지만 여전히 Fetch는 비슷했다.

 
 
인덱스 생성 전에는, 해당 테이블에서 풀스캔(ALL)을 했다. 전체 테이블 row를 스캔한 것으로 보인다.
인덱스 생성 후에는 INDEX RANGE SCAN을 통해 조회하였고 테이블 row의 스캔 개수도 20%가량 줄어들었다.
추측건대 fetch time이 개선되지 않은 이유는 스캔하는 row가 여전히 많기 때문이라고 판단했다.
 
쿼리에서는 조인을 먼저 수행하지만 쿼리 실행 계획을 보면 조인 시 인덱스를 효율적으로, 아니 전혀 사용하지 못하는 것처럼 보였다. 
 
왜 이런 결과가 나올까 생각해 보다가 쿼리 실행 순서를 바탕으로 인덱스 순서를 변경해 보았다.

CREATE INDEX IDX_REALTIME_QUOTATION
ON platform_A (product_no, cart_group_no, option_product);

 

 

p의 인덱스 길이는 category가 varchar(150)이기 때문인 것 같다

 
 
변경 후에도 결과가 다소 개선되었는데 Duration도 개선되었지만 Fetch Time이 대폭 개선되었음을 볼 수 있다. 실제로 스캔하는 row의 수가 100배 가까이 줄었기 때문이라고 유추해 볼 수 있었다. 추가로 c 테이블의 KEY LENGTH가 1이 줄었는데, 이 쿼리의 결과에서 option_product을 참조하지 않았다고 판단할 수 있다. (option_product는 tinyint이다)
 
카디널리티는 cart_group_no가 두 배 높았지만, 실제로 product_no를 앞에 사용해 줘야 올바르게 튜닝이 된 것을 확인할 수 있었다.
 

위 쿼리에 대해 모든 인덱스를 참조하게 되는데, 이런 인덱스를 커버링 인덱스라고 한다.

 
 
실제 쿼리를 튜닝하는 과정을 통해 카디널리티가 낮더라도 첫 번째 조건 절에서 사용된 컬럼을 인덱스 컬럼으로 사용하지 않는다면 올바르게 동작하지 않는다는 것을 알 수 있었다. 
 
정리하자면 선행 조건 컬럼은 반드시 인덱스 선행 컬럼이 되어야 하고, 다음과 같은 조건절이 있을 때 

WHERE col1 = ?
    AND col2 = ?
    AND col3 BETWEEN ? AND ?
    AND col4 = ?
    AND col5 = ?

CREATE INDEX ON IDX ON TB1 (col1, col2, col3, col4, col5);

 
 
인덱스를 위와 같이 생성했다면 col4, col5는 인덱스를 타지 않고 조건 필터링만 수행하게 되며 쿼리 실행 순서와 마찬가지로 WHERE 조건 절은 ORDER BY 컬럼보다 우선한다.
 

모든 인덱스를 참조하게 하고 싶다면 col1, col2, col4, col5, col3 순으로 생성해야 한다.

 
 
 
 
 

향로님 포스팅 일부 발췌

 
 
 
이왕이면 카디널리티도 위의 규칙을 지키면서 고려하면 좋다고 생각했지만, 카디널리티는 복합 인덱스에서 고려할 사항이 아니라는 자료도 있고 아예 선행 조건절이 일치한다면 후행 조건절에서는 순서가 유의미하지 않다는 글도 있다.
 
최근엔 이전과 같이 꼭 인덱스 순서와 조회 순서를 지킬 필요는 없는 것 같다. 인덱스 컬럼들이 조회 조건에 포함되어 있는지가 중요하고, 내가 사용하고 있는 데이터베이스 엔진에 맞게 인덱스를 활용하고 튜닝할 줄 아는 게 중요한 것 같다.