데이터베이스 단일 인덱스와 복합 인덱스의 차이와 활용법

복합 인덱스 알아보기

복합 인덱스는 단어 그대로 여러 개의 컬럼 조합으로 생성된 인덱스이다.

(idx_col1, idx_col2) 에 대해 인덱스를 설정하면, 명시한 컬럼 순서대로 인덱스가 정렬됩니다.

• B+ Tree 구조를 만들 때 idx_col1 컬럼에 대해 먼저 정렬을 하고, 동일한 값이 있다면 idx_col2를 기준으로 정렬하여 인덱스를 관리합니다.
• 복합 인덱스 구조를 보면 첫 번째 컬럼을 먼저 조회하고 그다음에 두 번째 컬럼을 조회하는 것을 알 수 있습니다.
  그래서 해당 인덱스를 제대로 사용하기 위해서는 순서에 맞게 Query를 구성해야 합니다.
  • 만약, idx_col2를 먼저 탐색하고 idx_col1을 탐색하게 where 조건문을 설정했다면, 올바르게 인덱스 탐색이 이루어지지 않을 수 있습니다.

•  

CREATE TABLE test1(
		id INT PRIMARY KEY AUTO_INCREMENT,
    one_field BIGINT,
    two_field BIGINT,
    hash_email VARCHAR(500),
    INDEX idx_hash_email (hash_email), -- 단일 인덱스
    INDEX idx_col1_col2 (one_field, two_field) 	-- 복합 인덱스
);

 

인덱스 탐색하는지 확인하기 - Select

Index 테이블 탐색이 아닌 경우

-- full scan
EXPLAIN SELECT hash_email, one_field, two_field FROM test1 WHERE two_field = 3;

type 필드의 값이 ALL이다. 이는 full scan을 하여 테이블 전체를 조회한 것이다.
현재 쿼리에서 Select 절을 보면 hash_email, one_field, two_field에 대해 조회하고 있는데 3개의 컬럼에 대해 인덱스가 설정이 되어 있지 않아 인덱스를 사용하지 않고 전체 테이블을 모두 조회하고 있습니다.

 

Index 테이블 탐색인 경우

-- index scan (1)

explain select one_field ,two_field from test1 t
where two_field = 790473;

one_filed, two_field에 대해서 복합 인덱스가 존재합니다. 그래서 위 2 개의 컬럼을 조회하는 경우에는 복합 인덱스가 저장된 인덱스 테이블에서 조회가 이루어집니다.

 

Select 절에서 복합 인덱스 순서

궁금증으로 where 절에서는 복합 인덱스의 순서대로 조건문을 작성해야 인덱스 탐색이 이루어지는 것을 알고 있는데, select 절에서도 순서대로 조회해야 하는지 궁금했다.

 

Index 테이블 탐색인 경우

-- index scan (2)

explain select two_field, one_field from test1 t
where two_field = 790473;

위 쿼리의 경우에도 복합 인덱스를 통해 인덱스 탐색이 이루어진 것을 알 수 있다.
즉, 복합 인덱스를 사용할 때 Where 절에서 인덱스 순서를 지켜야 하며, Select 절에서는 인덱스 스캔에 영향을 미치지 않는다.

Select 절은 인덱스 사용 여부와 상관없이, 결과를 출력할 때 사용하는 단계이다. 순서가 바뀌었다 해도 여전히 인덱스를 사용하여 데이터를 조회한 후, 데이터의 순서를 바꿔서 값을 반환한다.

 

Index 테이블 탐색인 경우

-- index scan
explain select seq, one_field, two_field from test1 t;

PK는 기본적으로 모든 index에 포함되어 있습니다. 따라서 seq를 함께 조회하는 경우에도 인덱스 탐색이 이루어집니다.

 

인덱스 탐색하는지 알아보기 - Where

Index Full Scan 하는 경우

explain select two_field, one_field from test1 t
where two_field = 790473;

위 쿼리의 경우 two_field로만 검색하고 있어, Index Full Scan이 발생합니다. 인덱스 풀 스캔이 일반 데이터 풀 스캔보다 효율적이지만, 인덱스를 제대로 활용한다면 부분 인덱스 탐색(Ref)으로 더 빠르게 조회할 수 있습니다.

 

Index 탐색 Ref 하는 경우

explain select two_field, one_field from test1 t
where one_field = 266882 and two_field = 790473;

WHERE 조건에 복합 인덱스를 통해 조회한다. 인덱스의 특정 값에 대해 조건을 만족하는 행을 찾을때 사용하며, 인덱스의 특정 범위만 조회한다.

일반적으로 WHERE절에 인덱스가 적절하게 사용되는 경우에 나타난다. 인덱스 전체를 스캔하지 않고, 필요한 일부 범위에 해당하는 값만 조회하므로 type: Index보다 효율적이다.

 

범위 스캔 (type : range)

explain select two_field, one_field from test1 t
where one_field > 266882 and two_field < 790473;

범위 스캔은 인덱스의 일부 범위에 속하는 값을 조회할 때 사용됩니다. 인덱스 풀 스캔보다 성능이 좋으며, between, <, ≤, >, ≥ 조건을 통해 발생합니다.

 

설정된 인덱스 확인하기

show index from {테이블_이름}

 

 

느낀점

인덱스를 추가하여 성능을 최적화하고자 인덱스를 공부하면서 복합 인덱스에 대해서도 제대로 알아야겠다 생각했습니다.

나의 경우에는 복합 인덱스를 사용했을 때 이득이 되는 서비스 로직은 없었지만, 빈번하게 조회되는 컬럼이 2개 이상이 있을 때에는 복합 인덱스를 설정하여 조회 성능을 높일 수 있겠다 생각이 들었다.

 

복합 인덱스는 idx1, idx2 2개의 컬럼을 순서대로 정렬하고 있기에 idx2로만 조회하는 경우 인덱스 테이블을 사용해 조회하긴 하지만 index full scan으로 조회되어 예상한 인덱스 활용이 아닐 수 있다.

 

복합 인덱스를 효율적으로 사용하기 위해서 복합 인덱스를 어느 상황에서 사용하면 좋은지 알 수 있는 시간이었습니다.