Ch07. 인덱스 - 복합 인덱스 정리

복합 인덱스 대원칙

• 인덱스는 순서대로 사용하라! (왼쪽 접두어 규칙)
• 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!
• 정렬(ORDER BY)도 인덱스 순서를 따르라!

 

인덱스는 순서대로 사용하라!

 

이는 "인덱스 왼쪽 접두어 규칙"을 의미한다. 복합 인덱스는 "(A, B, C)" 순서로 생성되었을 때, "WHERE" 절에서 A 조건 없이는 B나 C를 사용할 수 없다. 전화번호부에서 "성"을 모르고 "이름"만으로 사람을 찾을 수 없는 것과 같은 이치다.

• 가능 (O): "WHERE A", "WHERE A AND B", "WHERE A AND B AND C"
• 불가능 (X): "WHERE B", "WHERE C", "WHERE B AND C"

이것이 복합 인덱스의 가장 기본적이고 절대적인 규칙이다.

등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!

복합 인덱스의 컬럼 중 하나에 범위 조건(>, <, BETWEEN, LIKE%...)이 사용되는 순간, 그 뒤에 오는 컬럼은 인덱스의 정렬 효과를 누릴 수 없다. 예를 들어, "(category, price)" 인덱스가 있을 때 "WHERE category > '도서' AND price = 30000" 쿼리를 생각해보자.

 

데이터베이스는 category가 "도서"보다 큰 ('생활용품', '전자기기', ...) 섹션을 인덱스에서 찾는다. 하지만 "생활용품" 섹션과 "전자기기" 섹션의 price는 서로 연결되어 정렬된 것이 아니다. 결국 데이터베이스는 "도서" 이후의 모든 인덱스를 스캔하면서, price가 "30000"인지 일일이 확인해야 한다. price 컬럼은 필터링에만 사용될 뿐, 탐색(seek)에는 사용되지 못한다.

 

따라서 가장 효율적인 인덱스 설계는 다음과 같은 순서를 따른다.

• 변별력이 높은 등호(=) 조건으로 사용할 컬럼을 인덱스 앞쪽에 배치한다.
  • IN 절도 여러 개의 등호 조건으로 취급되어 유리하다.
• 범위 조건으로 사용할 컬럼은 인덱스 뒤쪽에 배치한다.

이렇게 하면 등호 조건으로 검색 대상을 최대한 좁힌 뒤, 그 좁은 범위 내에서만 범위 검색을 수행하므로 성능이 극대화된다.

 

정렬(ORDER BY)도 인덱스 순서를 따르라!

복합 인덱스의 가장 강력한 기능 중 하나는 불필요한 정렬 작업을 생략하게 해주는 것이다. "ORDER BY" 절이 인덱스 컬럼 순서와 일치하면, 데이터베이스는 이미 정렬된 인덱스를 순서대로 읽기만 하면 되므로 매우 빠르다. 이 경우 실행 계획에서 "Using filesort"가 사라지는 것을 볼 수 있다.

• 인덱스: (category, price)
• 빠른 쿼리: "WHERE category = '전자기기' ORDER BY price"
  • '전자기기' 섹션은 이미 price 순으로 정렬되어 있으므로 추가 정렬이 필요 없다.
• 느린 쿼리 (filesort 발생): "WHERE category = '전자기기' ORDER BY stock_quantity"
  • 인덱스는 "stock_quantity" 순서와는 무관하므로, 결과를 가져온 뒤 별도로 정렬해야 한다.

"WHERE" 절에서 인덱스를 잘 활용하는 것만큼, "ORDER BY"에서 "filesort"를 피하는 것도 전체 쿼리 성능에 결정적인 영향을 미친다.

결론적으로, 복합 인덱스는 단순히 여러 컬럼을 묶는 것이 아니라, "순서"와 "조건의 종류(등호/범위)"를 고려한 전략적인 설계가 핵심이다. 이 세 가지 대원칙만 명심한다면, 여러분도 실무에서 고성능 쿼리를 충분히 잘 작성할 수 있을 것이다.