복합 인덱스 활용 경우의 수

다양한 데이터의 접근 형태에 일일이 대응하여 인덱스를 많이 만들 수 있는 환경이라면 인덱스 생성에 있어 고민의 여지가 적겠지만, 아쉽게도 인덱스가 많아지면 많아질수록 그에 따른 사이드 이펙트(DML 성능 저하, 저장 공간 차지)가 존재한다. 따라서 적은 수의 인덱스를 유지하는 동시에 다양한 데이터 접근 형태에 대해 인덱스 스캔을 지원하는 것은 중요하다.

이를 위해서는 복합 인덱스 기반으로 하나의 인덱스가 여러 상황을 커버할 수 있도록 테이블 설계가 이루어져야 한다. 

해당 글에서는 이를 돕기 위해 복합 인덱스가 어떤 방식으로 작동하는지 알아보고, 복합 인덱스가 작동하는 경우의 수에 대해 다뤄보려고 한다.

이 글은 Mysql 기준으로 작성되었다.

복합 인덱스 작동 방식

복합 인덱스는 둘 이상의 칼럼을 통해 생성한 인덱스를 의미한다. 이를 생성하는 법은 아래와 같다.

# Mysql 기준
CREATE INDEX {인덱스 명} ON {테이블 명} (col1, ...);

이렇게 생성한 복합 인덱스는 쿼리 실행 시 어떤 식으로 작동할까?

이를 이해하기 위해서는 먼저 인덱스 액세스 조건과 인덱스 필터링 조건에 대해 이해해야 한다.

인덱스 액세스 조건의 경우 B Tree 자료구조를 수직적 탐색을 하는데 활용이 된다. 반면 인덱스 필터링 조건은 수직적 탐색이 완료된 후 리프 노드를 순회할 때 활용된다. 

실질적인 B 트리 탐색 범위는 수직적 탐색을 통해 결정되기 때문에 최대한 많은 칼럼이 인덱스 액세스 조건으로 활용되도록 유도해야 불필요한 테이블 랜덤 액세스를 최소화할 수 있다.

위 이해를 바탕으로 정리한 복합 인덱스의 인덱스 스캔 활용 방식은 다음과 같다.

1. 인덱스 선두 칼럼부터 시작해서, 조건절에 등호 조건으로 사용한 칼럼들은 모두 인덱스 엑세스 조건으로 활용된다.
2. 만약 위와 같은 상황에서 처음 범위 탐색 조건을 발견한 경우, 해당 조건까지만 인덱스 엑세스 조건으로 활용하고 그 이후에 인덱스 존재 칼럼 조건절에 대해서는 모두 인덱스 필터 조건으로 활용된다. 
3. 그 후 인덱스 조건으로 사용되지 않은 조건절은 테이블 필터 조건으로 활용된다.
4. 복합 인덱스 내 모든 칼럼을 활용하지 않더라도, 선두칼럼부터 이어지는 칼럼 구성이 조건절에 포함된 경우 인덱스는 활용될 수 있다.
5. 통념과는 다르게, 인덱스 카디널리티가 낮을 칼럼을 복합 인덱스 앞쪽에 배치한다고 해서 인덱스 스캔 상 더 효율적인 것은 아니다. 인덱스 자료구조는 2차원 테이블 형태가 아니기 때문에 인덱스를 순차적으로 확인하지 않고 한번에 목적지를 향해 가기 때문에 이는 의미없는 고려이다. 

 

복합인덱스 작동 경우의 수

이제 위에서 서술한 복합 인덱스의 이해를 토대로 인덱스가 정상적으로 작동하는 경우와 그렇지 않은 경우들을 직접 확인해보겠다.

이를 위해 Mysql 서버에 아래 쿼리를 실행하여 테이블을 생성해주고 이에 목 데이터를 50000개 채워넣었다.

CREATE TABLE test_table (
  id INT NOT NULL AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  d INT NOT NULL,
  e INT NOT NULL,
  PRIMARY KEY (id)
);

또한 테스트 용 복합 인덱스를 아래와 같이 구성했다.

CREATE INDEX test_idx ON test_table (a, b, c, d);

확인해볼 경우의 수는 아래와 같다.

1. 조건식에 모든 복합 인덱스 구성 칼럼이 포함되는 경우
2. 조건식에 복합 인덱스 칼럼이 일부만 사용되는 경우 (선두 칼럼부터 연속적)
3. 조건식에 복합 인덱스 칼럼이 일부만 사용되는 경우 (선두 칼럼 포함 X)
4. 조건식에 복합 인덱스 칼럼이 일부만 사용되는 경우 (선두 칼럼은 포함되나 중간에 연결이 끊기는 경우)
5. 조건식에 모든 복합 인덱스 구성 칼럼이 포함되나, 중간에 범위 탐색 조건을 사용한 경우
6. 조건식 + ORDER BY 절의 칼럼들이 모두 복합 인덱스 구성 칼럼에 포함되는 경우 (조건식 -> ORDER BY 절 순서)
7. 조건식 + ORDER BY 절의 칼럼들이 모두 복합 인덱스 구성 칼럼에 포함되는 경우 (ORDER BY 절 -> 조건식 순서)
8. 조건식 + ORDER BY 절의 칼럼들이 모두 복합 인덱스 구성 칼럼에 포함되는 경우 (조건식 일부 -> ORDER BY 절 -> 조건식 순서)

1. 조건식에 모든 복합 인덱스 구성 칼럼이 포함되는 경우

이 경우는 사실 인덱스를 탄다는 사실이 명확하다. 그래서 단순히 인덱스를 타는지 안타는지에 대해 다루기 보다는 각 칼럼의 카디널리티에 따라 인덱스 구성 순서가 바뀌었을 때 영향이 있는지 직접 확인해보려 한다.

우선 현재 각 칼럼에 대한 카디널리티를 조회하면 다음과 같다.

SELECT
  (SELECT AVG(tmp_a.cnt) FROM (SELECT a, count(*) as cnt FROM test_table GROUP BY a) tmp_a) as avg_card_a,
  (SELECT AVG(tmp_b.cnt) FROM (SELECT b, count(*) as cnt FROM test_table GROUP BY b) tmp_b) as avg_card_b,
  (SELECT AVG(tmp_c.cnt) FROM (SELECT c, count(*) as cnt FROM test_table GROUP BY c) tmp_c) as avg_card_c,
  (SELECT AVG(tmp_d.cnt) FROM (SELECT d, count(*) as cnt FROM test_table GROUP BY d) tmp_d) as avg_card_d,
  (SELECT AVG(tmp_e.cnt) FROM (SELECT e, count(*) as cnt FROM test_table GROUP BY e) tmp_e) as avg_card_e;

카디널리티 조회 결과에 따라 a -> e로 갈수록 카디널리티가 낮아지는 상황이다. 이제 기존에 만들어둔 인덱스를 활용해서 조회한 결과는 아래와 같다.

SELECT * 
FROM test_table
WHERE a = 20 AND b = 150 AND c = 400 AND d = 750 AND e = 1500;

(a, b, c, d) 인덱스 활용

이제 기존에 만들어둔 인덱스를 삭제하고 인덱스 구성 칼럼 중 카디널리티가 가장 낮은 순으로 (d, c, b, a) 로 구성한 뒤 동일한 쿼리를 실행한 결과는 아래와 같다.

(d, c, b, a) 인덱스 활용

두 실행 계획의 결과를 보면 서로 코스트가 동일하다. 즉, 복합 인덱스 내에서 카디널리티에 따라 순서를 구성하는 것은 전혀 의미가 없다. (다만, 카디널리티에 따라 인덱스에 포함할지 말지 고려하는 것은 타당한 고려이다.)

2. 조건식에 복합 인덱스 칼럼이 일부만 사용되는 경우 (선두 칼럼부터 연속적)

아래 쿼리의 실행계획을 확인했다.

SELECT *
FROM test_table
WHERE a = 20 AND b = 150 AND e = 1200;

(a, b, c, d) 인덱스 사용

선두칼럼과 이어지는 칼럼이 포함되어 있기 때문에 두 조건은 인덱스 스캔에 활용된다.

3. 조건식에 복합 인덱스 칼럼이 일부만 사용되는 경우 (선두 칼럼 포함 X)

이를 확인하기 위해 아래 쿼리의 실행계획을 확인했다.

SELECT *
FROM test_table
WHERE b = 150 AND c = 400 AND d = 750;

(a, b, c, d) 인덱스 사용

결과에서 볼 수 있듯 인덱스를 전혀 활용하지 못하고 테이블 풀스캔이 수행된 것을 볼 수 있다. 선두 칼럼이 조건절에 포함되어 있지 않다보니 인덱스 대부분을 확인해야 하는 상황이 되고 이를 토대로 테이블에 접근까지 해야하는 상황(SELECT * 때문)이기 때문에 옵티마이저가 테이블 풀스캔을 수행한 것으로 이해할 수 있다. 

만약, 쿼리가 SELECT * 이 아닌 인덱스에 포함된 a, b, c, d만 조회하는 쿼리였다면 옵티마이저는 어떤 선택을 할까? 그 결과는 아래와 같다.

SELECT a, b, c, d
FROM test_table
WHERE b = 150 AND c = 400 AND d = 750;

(a, b, c, d) 인덱스 활용

인덱스 스캔만 수행해도 쿼리에서 원하는 칼럼을 모두 획득할 수 있기 때문에 이번엔 인덱스 스킵 스캔을 수행하는 것을 볼 수 있다. 이를 통해 쿼리 레벨에서 불필요한 칼럼은 조회하지 않도록 한다면 정상적인 인덱스 활용 상황이 아니더라도 (선두 칼럼이 조건절에 포함되지 않음) 성능 개선을 해볼 여지가 존재한다는 것을 알 수 있다.

4. 조건식에 복합 인덱스 칼럼이 일부만 사용되는 경우 (선두 칼럼은 포함되나 중간에 연결이 끊기는 경우)

이를 확인해보기 위해 아래 쿼리에 대한 실행 계획을 확인했다.

SELECT * 
FROM test_table
WHERE a = 20 AND c = 400 AND d = 750 AND e = 1500;

(a, b, c, d) 인덱스 활용

실행 계획을 보면 Index Scan이 수행되나 복합 인덱스에 포함된 칼럼 중 a 칼럼에 대해서만 스캔이 수행된다.

그런데 그 뒤에 with index condition이라는 구문이 추가되어 있는데, 이는 Mysql의 Index Condition Pushdown(ICP)이라는 기능이 작동한 결과이다. 간략하게 설명하자면, ICP가 도입되기 전까지는 칼럼 a를 이용한 인덱스 스캔만 수행하고 이 결과를 토대로 나머지 칼럼에 대해서는 테이블 레벨 필터링이 작동하는 방식이었다. 이미 인덱스 스캔을 하면서 인덱스에 포함된 c, d 칼럼을 미리 확인할 수 있었으나 그렇게 하지 않고 있었던 것인데, ICP가 도입된 후에는 리프 노드에서 인덱스를 확인할 때 이 작업도 같이하게 된다. 일종의 인덱스 수직적 탐색인 것이다. 

즉, 이를 해석하면 a 칼럼은 인덱스 액세스 조건으로 c, d 칼럼은 인덱스 필터링 조건으로, 나머지 e 칼럼은 테이블 필터링 조건으로 사용된 것이다. (사실 인덱스 필터링 조건으로 사용 못할 줄 알았는데 의외에 결과. 다른 DB도 비슷한 방식으로 작동하는지는 확인이 필요해보인다.)

5. 조건식에 모든 복합 인덱스 구성 칼럼이 포함되나, 중간에 범위 탐색 조건을 사용한 경우

이를 확인해보기 위해 아래 쿼리의 실행 계획을 확인했다.

SELECT * 
FROM test_table 
WHERE a = 20 AND b > 100 AND c = 400 AND d = 750 AND e = 1500;

(a, b, c, d) 인덱스 활용

실행 계획을 보면 a와 b까지는 인덱스 스캔에 활용되었다. (인덱스 수직적 탐색, 인덱스 액세스 조건)

하지만 b 칼럼 조건절을 범위 탐색 조건으로 사용하여 그 이후 칼럼들은 모두 with index condition 절에 나타난 것처럼 인덱스 필터링 조건으로 활용된 것으로 파악할 수 있다. (인덱스 필터링 조건)

6. 조건식 + ORDER BY 절의 칼럼들이 모두 복합 인덱스 구성 칼럼에 포함되는 경우 (조건식 -> ORDER BY 절 순서)

이 경우는 사실 인덱스가 정상 작동하며, 추가적인 정렬도 스킵되는 형태라는 것을 알고 있지만 그래도 확인 차 실행 계획을 확인했다.

SELECT * 
FROM test_table
WHERE a = 20 AND b = 200 AND e = 1500
ORDER BY c, d;

(a, b, c, d) 인덱스 활용

실행 계획을 보면 c, d 칼럼에 대한 언급이 따로 없지만 Sort 실행 계획이 생략된 것을 알 수 있다. 의도대로 잘 작동하는 것으로 보인다.

7. 조건식 + ORDER BY 절의 칼럼들이 모두 복합 인덱스 구성 칼럼에 포함되는 경우 (ORDER BY 절 -> 조건식 순서)

그렇다면 이번엔 ORDER BY 절의 칼럼이 선두 칼럼인 경우는 어떨까?

이를 확인해보기 위해 아래 쿼리에 대한 실행 계획을 확인했다.

SELECT * 
FROM test_table 
WHERE b = 100 AND c = 400 AND d = 750 AND e = 1500
ORDER BY a;

(a, b, c, d) 인덱스 활용

실행 계획을 보면 인덱스를 전혀 활용하지 못하는 것을 알 수 있다. 조건절보다 ORDER BY 절의 칼럼이 먼저 활용되는 경우엔 인덱스를 타지 못하는 것으로 보인다.

8. 조건식 + ORDER BY 절의 칼럼들이 모두 복합 인덱스 구성 칼럼에 포함되는 경우 (조건식 일부 -> ORDER BY 절 -> 조건식 순서)

이를 확인해보기 위해 아래 쿼리에 대한 실행계획을 확인했다.

SELECT * 
FROM test_table
WHERE a = 20 AND d = 750 AND e = 1500
ORDER BY b, c;

(a, b, c, d) 인덱스 활용

해당 경우엔 인덱스를 타는 것으로 확인할 수 있다. a 칼럼을 인덱스 액세스 조건으로 사용하고, d 칼럼은 인덱스 필터링 조건으로 사용한다. 인덱스 리프노드는 이미 b, c 기준으로 정렬된 상태이기 때문에 인덱스 선두칼럼을 통해 인덱스를 타기만 한다면 Sorting은 생략 가능한 것으로 파악된다.