이번 포스팅에서는 복합 인덱스와 커버링 인덱스에 대해 알아보려고 합니다.

😁 복합 인덱스란?

복합 인덱스(Composite Index)는 두 개 이상의 컬럼을 조합하여 하나의 인덱스를 생성한 것을 의미합니다.

주로 WHERE 절에서 AND 조건에 많이 사용되는 컬럼들을 결합 인덱스로 구성합니다. 또한 하나 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때 많이 사용합니다.

복합 인덱스는 지정된 순서대로 데이터를 정렬하여 검색 조건을 최적화할 수 있습니다.

CREATE INDEX idx_composite
ON Table(A, B, C);

위와 같이 인덱스를 생성하면 A -> B -> C 순서로 데이터가 정렬되어 저장됩니다. 그래서 왼쪽부터 순서대로 조건이 충족될 때만 효율적으로 사용이 가능합니다.

예를 들어, WHERE 절에 A = ... AND C = ... 나 B = ... AND C = ... 이런 경우에는 순차적 탐색이 불가능하므로, 결국 인덱스를 사용하지 않고 전체 데이터를 스캔하게 됩니다.

또한, 결합 인덱스에서 앞의 컬럼에 범위 스캔이 동작하는 경우 그 이후의 조건에서는 인덱스가 아닌 필터 조건이 동작하게 됩니다.

select * from emp_pay where A LIKE 'exam%' and B = 'exam';

위 조건절의 경우 결합 인덱스의 첫 번째 컬럼인 A의 조건에 대해서 Equal(=) 연산이 아닌 LIKE 조건을 사용했기 때문에 범위 스캔이 동작하게 됩니다.

결합 인덱스의 정렬된 순서는 사전식(lexicographical) 정렬로, 첫 번째 컬럼(A)이 정렬되고, 그 다음 A의 값이 고정된 상태에서 B가 정렬, 그 다음 A, B의 값이 고정된 상태에서 C가 정렬됩니다.

그렇기 때문에 A라는 컬럼에 대해 범위 스캔이 동작하고 그 이후의 컬럼(B, C)에 대해서 정렬이 깨진 것으로 인식되기 때문에 인덱스 탐색을 하지 않고 필터 조건으로 동작하게 됩니다.

😁 커버링 인덱스란?

커버링 인덱스(Covering Index)란 기본 인덱스와 다르게 인덱스 키 값 외에도 쿼리에서 필요한 모든 컬럼의 값을 포함하고 있습니다.

기본 인덱스는 인덱스 키 값과 실제 데이터의 레코드 주소 혹은 레코드의 PK의 값을 포함하고 있기 때문에 실제 데이터를 조회할 때 I/O가 발생하게 됩니다.

그러나 커버링 인덱스를 사용할 경우, 인덱스 자체에 쿼리에서 필요한 모든 데이터가 포함되어 있으므로, 인덱스에서 데이터를 직접 읽어들이게 됩니다. 이로 인해 테이블에 대한 추가적인 디스크 I/O가 필요 없습니다.

CREATE INDEX idx_covering_employees
ON employees(department_id, first_name, last_name);

위의 인덱스를 사용하면 SELECT first_name, last_name, department_id FROM employees WHERE department_id = 10; 쿼리를 실행할 때, department_id, first_name, last_name 이 모두 인덱스에 포함되어 있기 때문에 테이블에 접근할 필요없이 인덱스만으로 데이터를 읽어올 수 있습니다.

즉, 커버링 인덱스는 인덱스 만으로 쿼리 결과를 조회할 수 있기 때문에 특정 상황에서 쿼리 성능을 크게 향상시킬 수 있습니다.

복합 인덱스와 커버링 인덱스의 생성 구문은 동일하지만, 커버링 인덱스의 경우 SELECT에 사용되는 컬럼이 인덱스에 모두 포함해야 합니다.

만약 커버링 인덱스를 (A, B, C, D)로 걸어두고, SELECT A, B, C, D, E 쿼리를 실행하면 SELECT 절에 E 컬럼이 추가되었기 때문에, 인덱스 (A, B, C, D)만으로는 데이터를 모두 반환할 수 없습니다.

E 컬럼은 인덱스에 포함되지 않았으므로, MySQL은 테이블에 접근해야 합니다. 그렇기 때문에, EXPLAIN 결과에서 "Using Index"는 사라지고, "Using where" 및 "Using filesort"가 표시될 수 있습니다.

즉, 이 경우 인덱스를 통해 A, B, C, D는 가져오지만, E를 가져오기 위해 테이블에 접근해야 합니다.