Ch07. 인덱스 - 복합 인덱스(2)

복합 인덱스 실패 예제1: 인덱스 순서 무시

"카테고리와 상관없이 가격이 80,000원인 상품을 찾아보자."

EXPLAIN SELECT * FROM items WHERE price = 80000;

쿼리 결과

• type(ALL): 풀 테이블 스캔이 발생했다.
• key(NULL): "idx_items_category_price" 인덱스가 있음에도 불구하고, 옵티마이저는 이 인덱스를 사용하지 않았다.

왜 이런 결과가 나왔을까? 인덱스 왼쪽 접두어 규칙 때문이다. "idx_items_category_price" 인덱스는 category로 먼저 정렬되어 있다. price가 "80000"인 상품은 '전자기기' 카테고리에도 있을 수 있고, 만약 있다면 '패션' 카테고리에도 있을 수 있다. 즉, "price" 값은 인덱스 전체에 흩어져 있다.

쿼리 동작 과정

• 데이터베이스 입장에서 price만으로 데이터를 찾으려면, '도서' 카테고리 섹션을 처음부터 끝까지 다 보고, '생활용품' 섹션도 다 보고, '전자기기', '패션' 등 모든 카테고리 섹션을 다 뒤져봐야 한다.
• 이는 인덱스 전체를 스캔하는 작업으로, 차라리 원본 테이블 전체를 스캔하는 것보다 나을 것이 없다.

복합 인덱스 실패 예제2: 범위 조건을 먼저 사용

복합 인덱스 활용에는 한 가지 더 중요한 제약 조건이 있다. 바로 선행 컬럼에 범위 조건( >, < , BETWEEN,  LIKE %) 이 사용되면, 그 뒤에 오는 컬럼은 인덱스를 제대로 활용할 수 없다는 점이다.

 

"카테고리명이 '패션' 이상인 상품들 중에서, 가격이 정확히 20,000원인 상품을 찾아줘." 라는 요구사항을 생각해보자.

쿼리 결과

• 실행 계획을 자세히 분석해 보자. type이 range이고, key에 "idx_items_category_price"가 사용되었으니 언뜻 보기에는 인덱스를 잘 사용한 것처럼 보인다.
• 하지만 여기서 주목해야 할 것은 "filtered" 컬럼의 값인 "10.00%"와 "Extra" 컬럼의 "Using index condition"이다.

쿼리 동작 과정

1. "idx_items_category_price" 인덱스를 사용해 category가 '패션'인 위치를 찾는다. (>= 조건의 시작점)
2. 거기서부터 인덱스의 끝까지 모든 데이터를 스캔한다. ('패션', '헬스/뷰티' 카테고리에 해당하는 모든 데이터)
3. 스캔하는 각 레코드마다 "price = 20000" 조건을 만족하는지 하나하나 검사한다. (인덱스 사용이 아니라 직접 필터링한다.)

이것이 바로 "filtered: 10.00%"의 의미다. 옵티마이저는 "category >= '패션'" 조건으로 약 6개의 행을 찾을 것으로 예상하고(rows: 6 ), 그 중에서 "price = 20000" 조건을 만족하는 데이터는 10% 정도일 것이라고 예측한다. 즉, price 컬럼은 데이터를 효율적으로 "찾는(seek)" 데 사용된 것이 아니라, 일단 category 조건으로 넓게 가져온 데이터를 걸러내는(filter)데만 사용된 것이다

 

데이터베이스는 "category >= '패션" 조건에 따라 인덱스에서 '패션'과 '헬스/뷰티' 섹션을 찾았다. 하지만 그다음 조건인 "price = 20000"을 처리할 때 문제가 생긴다. '패션' 섹션은 "price" 순으로 정렬되어 있다. '헬스/뷰티' 섹션도 "price" 순으로 정렬되어 있다. 하지만 '패션' 섹션의 가격들과 '헬스/뷰티' 섹션의 가격들이 전체적으로 정렬된 것은 아니다. '패션'의 마지막 상품 가격이 "헬스/뷰티"의 첫 상품 가격보다 높을 수 있다.

 

패션 + 헬스/뷰티의 price

패션 + 헬스/뷰티의 price + item_id

• 합쳐진 가격은 이제 정렬된 상태가 아니다!(인덱스를 활용하려면 정렬된 상태여야 한다!)
• 따라서 데이터베이스는 "category >= '패션'" 범위를 스캔하면서 가져온 각각의 데이터에 대해 "price = 20000"인지 일일이 확인하는 추가 작업을 해야만 한다.
  • 이렇게 찾은 패션 + 헬스/뷰티의 "price" 컬럼은 정렬되어 있지 않다!
  • 따라서 인덱스를 통한 빠른 탐색이 어렵다.
• 데이터베이스는 "category >= '패션'" 범위를 인덱스의 category 컬럼를 통해 스캔하면서 6개의 행을 빠르게 찾았다.
• 하지만 가져온 각각의 데이터에 대해 "price = 20000"인지 일일이 확인하는 필터링 추가 작업을 해야만 한다.
• 이처럼 복합 인덱스에서 앞선 컬럼(category)에 범위 조건(>=)이 걸리는 순간, 데이터베이스는 더 이상 뒤따라오는 컬럼(price)의 정렬 순서를 활용할 수 없게 된다. 
  • category가 '패션'일 때의 price 정렬과 category가 '헬스/뷰티'일 때의 price 정렬은 둘을 합치는 순간 정렬이 깨지기 때문이다.
  • 따라서 데이터베이스는 "category >= '패션'"이라는 범위 조건만 사용해서 인덱스를 스캔하고, 나머지 "price = 20000" 조건은 빠르게 찾지 못하고, 필터링만 수행하는 것이다.
  • 이는 인덱스의 성능을 절반만 활용하는 셈이다.

 

이처럼 복합 인덱스에서 어떤 컬럼에 범위 검색을 사용하는 순간, 그 뒤에 오는 컬럼들은 인덱스의 정렬 효과를 제대로 누릴 수 없게 된다. 따라서 인덱스를 설계할 때는 "=" 조건으로 사용될 컬럼을 범위 조건으로 사용될 컬럼보다 앞에 배치하는 것이 일반적인 최적화 전략이다.