복합 인덱스의 순서 관련된 흔한 오해

복합 인덱스의 순서 관련된 흔한 오해

개발자들이 데이터베이스 성능 최적화를 논할 때마다 항상 나오는 주제다. 인덱스를 어떻게 걸어야 하냐는 것. 그리고 십중팔구 엉뚱한 방향으로 인덱스를 설계하고는 한다. 오늘은 복합 인덱스 순서와 관련된 흔한 오해를 실제 사례로 들춰보겠다.

문제 상황: 생각해보자.

모 개발 커뮤니티에서 아래와 같은 퀴즈가 올라온 적이 있다.
당신이라면 쿼리를 건드리지 않고, 어떻게 아래 쿼리에 대한 인덱스를 설계할 것인가?

SELECT 주문일자, count(*) AS 주문수
FROM 주문
WHERE 주문일자 >= $1 AND 주문일자 <= $2
  AND 배송지 LIKE '%논현동%'
  AND 배송여부 = 'Y'
GROUP BY 주문일자
ORDER BY 주문수 DESC;

이 쿼리는 특정 기간에 논현동으로 배송 완료된 주문을 일자별로 집계하는 것이다.
이런 쿼리에 최적화된 인덱스를 만들어보라는 문제였다. (단, Full text index는 제외하고 생각한다.)

흔한 생각: "주문일자가 먼저 와야지"

많은 개발자가 인덱스(주문일자, 배송여부, 배송지)를 구성하려고 시도해본다.
왜 이런 생각을 할까? 이유는 단순하다.

1. 쿼리에서 '주문일자' 범위 검색을 한다. (카디널리티 개념으로 인해 배송여부가 먼저 오면 안 된다고 생각한다.)
2. 'GROUP BY'도 주문일자로 하니까 인덱스 첫 번째에 넣어야 한다고 생각한다.
3. 나머지 조건들은 그냥 순서대로 넣어본다. (커버링 인덱스 개념을 떠올리며 흐뭇해한다..)

하지만, 틀렸다. 이 방식은 전혀 최적의 인덱스 설계가 아니다. 왜일까?

착각의 원인: 카디널리티와 선택도를 구분하지 못한다.

여기서 꼭 알아야 할 두 개념이 있다.
'카디널리티(Cardinality)'와 '선택도(Selectivity)'다.

• 카디널리티: 컬럼의 유니크한 값 개수다.
• 선택도: 전체 데이터 중 특정 조건에 걸리는 데이터 비율이다.

대다수 개발자는 이 둘을 헷갈린다.
"카디널리티 높은 컬럼을 인덱스 앞에 두라"는 조언만 기억한다.
반은 맞고 반은 틀린 말이다.

진짜 최적화: "작업 범위부터 줄여라"

실제로 최적의 인덱스는 '인덱스(배송여부, 주문일자, 배송지)'이다.

왜 이게 더 효율적인가?

DB 인덱스는 B-Tree 구조로, 수직 탐색과 수평 탐색으로 이루어진다.
여기서 핵심은 수평 탐색의 범위를 최대한 줄이는 것이다. (인덱스가 사실 필요한 이유가 이것이다.)

인덱스 구조를 살펴보면:

• 주문일자가 먼저인 경우:
  (1992-05-01, Y), (1992-05-01, N), (1992-05-02, Y), (1992-05-02, N), ...
• 배송여부가 먼저인 경우:
  (N, 1992-05-01), (N, 1992-05-02), ..., (Y, 1992-05-01), (Y, 1992-05-02), ...

첫 번째는 '주문일자 범위의 모든 레코드(Y와 N 모두)를 스캔한다.
두 번째는 "배송여부 = 'Y'"인 레코드만 먼저 걸러 낸 후, 그 안에서 주문일자 범위를 검색한다.
어느 쪽이 효율적일까? 대답할 필요가 없다.

조건 유형에 따른 인덱스 순서 결정: 이건 공식에 가깝다.

인덱스 설계 시 조건 유형에 따른 우선순위가 있다.

1. 동등 조건(=): 최우선
2. 범위 조건(>, <, BETWEEN): 그 다음
3. LIKE '%xxx': 가장 후순위

따라서 "배송여부 = 'Y'"(동등 조건)이 "주문일자 >= $1 AND 주문일자 <= $2"(범위 조건)보다 인덱스 앞에 와야 한다.
앞서 살펴본 예시와 같은 이유에서 말이다.

성능 차이: 수치로 살펴보자.

실제 성능 차이를 보자.

• 주문 테이블에 1,000,000건의 데이터가 있다.
• 배송여부 'Y'는 200,000건(20%)이다.
• 조회 날짜 범위에 100,000건(10%)이 해당한다.

첫 번째 인덱스(주문일자 먼저)를 사용하면:

• 100,000건의 날짜 범위 데이터를 전부 스캔한다.
• 그 중 20%인 20,000건만 실제로 필요한 데이터다.

두 번째 인덱스(배송여부 먼저)를 사용하면:

• 200,000건의 'Y' 데이터 중
• 날짜 범위에 해당하는 20,000건만 스캔한다.

결론: 두 번째 인덱스는 첫 번째보다 5배 적은 데이터를 스캔한다. 성능 차이는 명백하다.

고급 최적화: 커버링 인덱스로 한방에 해결한다.

한 수 더 뜨는 최적화 방법이 있다. '커버링 인덱스'다.
쿼리에 필요한 모든 컬럼을 인덱스에 포함시켜 테이블 접근 없이 인덱스만으로 처리하는 방법이다.

인덱스 = (배송여부, 주문일자, 배송지)

 

이 인덱스는 커버링 인덱스로도 작동한다.
조건 컬럼과 SELECT 컬럼이 전부 포함되어 있기 때문이다. (일석이조다.)

결론: 인덱스 설계의 불변 법칙

1. 동등 조건(=) 컬럼을 맨 앞에 배치한다.
2. 범위 조건(>, <, BETWEEN) 컬럼을 그 다음에 배치한다.
3. LIKE 조건(특히 '%xxx%')은 가능하면 맨 뒤에 둔다.
4. GROUP BY, ORDER BY에 사용되는 컬럼도 고려한다.

이렇게 설계하면 쿼리 옵티마이저가 효율적인 실행 계획을 세운다.
물론 이렇게 간단한 원칙도 모르고 주문일자부터 인덱싱하는 개발자들이 수두룩하다.