복합 인덱스(Composite Index)란?
복합 인덱스는 두 개 이상의 컬럼에 걸쳐 생성되는 인덱스를 의미합니다. 핵심 특징은 다음과 같습니다.
- 여러 개의 컬럼에 적용되는 인덱스로, 다중 컬럼 인덱스(Multiple-column Index)라고도 불립니다.
- MySQL에서는 최대 16개의 컬럼으로 구성된 복합 인덱스를 생성할 수 있습니다.
- 쿼리 옵티마이저는 인덱스에 포함된 모든 컬럼을 조건으로 검사하는 쿼리에 복합 인덱스를 활용합니다.
- 또한 첫 번째 컬럼, 처음 두 개의 컬럼처럼 인덱스의 선행 컬럼만 사용하는 쿼리에도 적용할 수 있습니다.
- 인덱스 정의 시 컬럼 순서를 올바르게 지정하면, 하나의 복합 인덱스만으로도 같은 테이블에 대한 다양한 유형의 쿼리 속도를 높일 수 있습니다.
즉, 컬럼의 순서를 잘 설계하면 여러 개의 단일 인덱스를 만드는 것보다 하나의 복합 인덱스로 더 효율적인 성능을 얻을 수 있습니다.
테이블 생성 시 복합 인덱스 만들기
테이블을 생성하는 과정에서 복합 인덱스를 함께 정의할 수 있습니다. 아래 구문을 사용하면 됩니다.
CREATE TABLE table_name (
c1 data_type PRIMARY KEY,
c2 data_type,
c3 data_type,
c4 data_type,
INDEX index_name (c2,c3,c4)
);
위 예제에서 복합 인덱스는 c2, c3, c4 세 개의 컬럼으로 구성됩니다.
기존 테이블에 복합 인덱스 추가하기
이미 존재하는 테이블에는 'CREATE INDEX' 문을 사용해 복합 인덱스를 추가할 수 있습니다.
CREATE INDEX index_name
ON table_name(c2,c3,c4);
복합 인덱스로 느린 쿼리 빠르게 만드는 방법
- 쿼리 실행 속도는 쿼리가 완료되기까지 걸리는 소요 시간에 따라 평가됩니다.
- 인덱스 힌트(index hint)를 활용하면 쿼리 속도를 향상시킬 수 있습니다.
- MySQL 옵티마이저를 활용하면 인덱스를 선택할 때 올바른 판단을 내릴 수 있습니다.
- 다만 이 방법은 정적 쿼리에만 적용해야 합니다.
- WHERE 절이 계속 변경되는 쿼리에 인덱스 힌트를 남발하면 옵티마이저가 제 역할을 하지 못해 오히려 성능이 저하될 수 있습니다.
- 'FORCE INDEX' 구문은 'USE INDEX (index_list)'와 유사하게 동작하지만, 테이블 스캔(table scan)을 비용이 큰 작업으로 간주한다는 차이점이 있습니다.
- 따라서 지정된 인덱스를 사용해 테이블의 행을 찾을 방법이 전혀 없는 경우에만 테이블 스캔이 수행됩니다.
결론적으로, WHERE 절에서 자주 함께 조회되는 컬럼들을 순서 있게 묶어 복합 인덱스를 설계하고, 필요한 경우에만 인덱스 힌트를 보조적으로 활용하면 느린 쿼리의 성능을 효과적으로 개선할 수 있습니다.