[MySQL] Multi Column Index(복합 인덱스)

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

배경

지난 글에서는 Index를 비롯한 관련 기본 개념들을 알아보았다. 이번에는 여러 컬럼으로 인덱스를 구성하는 Multi Column Index와 그에 대한 특징을 알아본다.
 

Multi Column Index(복합 인덱스)

여러 개의 컬럼 조합으로 인덱스를 설정하는 방식, 실무에서 가장 많이 설정하는 인덱스 유형 중 하나이다. 다음과 같이 설정한 idx_col1_col2 인덱스가 복합 인덱스인데, 이때 컬럼을 명시한 순서대로 인덱스가 정렬된다.
 
즉 one_field 값들에 대해서 먼저 정렬하여 B-tree 구조를 만드는데, one_field에 동일한 값이 있다면 two_field를 기준으로 정렬한다. 따라서 one_field, two_field 순서로 동작하도록 서비스 로직을 작성하거나 쿼리를 구성해야한다. two_field를 먼저 처리하는 쿼리를 작성한다면, 인덱스를 탈 수 없어 성능이 저하된다.

CREATE TABLE test1(
	seq 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) 	-- 복합 인덱스
);

 
 
실무에서는 다음과 같이 중복 컬럼에 대해 여러개의 인덱스를 설정하는 경우가 있다.

CREATE TABLE test1(
		seq 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), 
    INDEX idx_col1_col2 (one_field, two_field, three_field),
    INDEX idx_col1_col2 (one_field, two_field, three_field, four_field)
);

 
하지만 인덱스도 결국 디스크와 MySQL의 캐싱 전략에 의해 메모리를 점유하고 있으므로 이 경우 불필요하게 리소스를 더 소요하게 된다. 또한 일반적인 상황에서는 문제가 되지 않지만 Index Dive와 같은 경우에서 오버헤드가 생기므로 주의해야한다. 따라서 무조건적으로 인덱스를 늘리기보다는 쿼리 최적화를 진행하며 설계해야한다.

테이블 Full scan을 타는 경우

앞서 생성한 idx_col1_col2 복합 인덱스가 설정된 테이블에 대해 SELECT 쿼리를 호출하며 실행 계획을 간단히 분석해본다.

CREATE TABLE test1(
	seq 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) 	-- 복합 인덱스
);

-- 재귀 호출하며 test1 테이블에 insert
INSERT INTO test1(one_field, two_field)
-- WITH RECURSIVE () : 재귀를 돌면서 () 내에 있는 SELECT 쿼리를 실행한다.
WITH RECURSIVE my_cte AS (
	SELECT 1 AS n, CAST(1 AS DOUBLE) AS abc, CAST(2 AS DOUBLE) AS se
  UNION ALL
  SELECT 1 + n, CAST(1 + n AS DOUBLE), CAST(2 + n AS DOUBLE) FROM my_cte WHERE n < 10000 -- 10000번 재귀 호출
)
SELECT abc, se FROM my_cte;

-- mysql의 재귀 호출 최대값을 15000으로 설정(기본 값은 1000)
SET @@cte_max_recursion_depth = 15000;

-- 결과 확인
SELECT count(1) FROM test1;

 
현재 test1 테이블에 two_field = 3인 로우는 다음과 같이 1개만 존재한다.
 

 
다음과 같이 full scan이 발생하는 쿼리를 실행해보자.

-- full scan을 타는 경우
EXPLAIN SELECT hash_email, one_field, two_field FROM test1 WHERE two_field = 3;

 
쿼리 실행 결과는 다음과 같다.
 

type 필드의 값이 ALL인 것을 볼 수 있는데, 이는 full scan을 하여 테이블 전체를 조회한 것이다. 즉 현재 실행 계획을 확인하는 쿼리에서 조회하는 hash_email, one_field, two_field에 대해 인덱스가 설정이 되어 있지 않아 인덱스를 사용하지 않고 전체 테이블을 모두 조회하였다. 따라서 인덱스가 효율적으로 사용되고 있지 않다.

인덱스 풀 스캔(index scan)을 타는 경우

-- idx_col1_col2를 타는 경우
EXPLAIN SELECT one_field, two_field FROM test1 WHERE two_field = 3;

 

인덱스로 정의된 컬럼인 one_field, two_field만을 조회하는 경우 type이 Index로 지정되고 idx_col1_col2 인덱스를 타고있는 것을 볼 수 있다. possible_keys와 key의 차이는 다음과 같다. (출처 : https://cheese10yun.github.io/mysql-explian/)
 

• possible_keys : 이용 가능성이 있는 인덱스 목록
• key : possible_keys 목록 중에서 실제로 옵티마이저가 선택한 인덱스(이 값이 NULL이라면 행 데이터를 가져오기 위해 인덱스를 사용할 수 없다는 것을 의미한다)

index scan + PK index을 타는 경우

-- PK index를 타는 경우
EXPLAIN SELECT seq, one_field, two_field  FROM test1;

 

PK는 기본적으로 모든 index에 포함되어 있다. 따라서 seq를 함께 조회하는 경우 type에 Index가 나오는 것을 확인할 수 있다.
실무에서도 이와 같이 EXPLAIN 키워드를 통해 쿼리가 Index를 탈 수 있도록 작성하는 것이 중요하다.

인덱스 풀 스캔

인덱스에 대한 풀스캔, 인덱스 전체를 스캔하고 인덱스만을 활용해 모든 값을 순차적으로 읽는 것을 의미한다. 즉 테이블의 모든 데이터를 읽는게 아니라 인덱스에 저장된 값들을 차례대로 읽는 것이다. 인덱스 값이 정렬되어 있어도 전체를 읽어야하는 상황(e.g. ORDER BY)에서 사용된다.
 

• 테이블 풀스캔 (type: ALL): 테이블에 있는 모든 행을 순차적으로 읽는다.
• 인덱스 풀스캔 (type: Index): 인덱스 전체를 순차적으로 읽는 작업으로, 테이블 자체를 읽지 않으며, 인덱스만을 활용해 데이터를 가져온다.

주로 정렬된 결과를 원하거나 WHERE 조건이 인덱스와 맞지 않는 쿼리일 때 발생한다. 예를 들어, 테이블에서 데이터의 정렬이 필요할 때 인덱스는 이미 값들이 정렬된 상태이기 때문에 조회하고나서 테이블을 정렬하기보다는 인덱스를 풀스캔하는 것이 더 효율적일 수 있다. 또는 WHERE 절이 인덱스를 타지 않는 경우에도 MySQL이 인덱스 풀스캔을 선택할 수 있다.

예시 1) 정렬이 필요한 경우

인덱스 풀스캔이 발생하는 예시를 살펴보면 다음과 같다.

SELECT name FROM employees ORDER BY name;

 
위와 같은 쿼리에서 employees테이블의 name 컬럼에 인덱스가 있다고 가정하자. 이 쿼리는 ORDER BY로 인해 정렬된 결과를 반환해야 한다. 이때 인덱스는 이미 정렬된 형태로 저장되어 있다. 따라서 MySQL은 테이블을 읽고 정렬하는 대신 인덱스만 풀스캔하여 이미 정렬된 데이터를 가져온다.
 

예시 2) WHERE절이 인덱스를 타지 않는 경우

다음과 같은 쿼리를 실행한다. 현재 인덱스는 one_field와 two_field 순서로 구성되어 있어 주어진 WHERE 조건만으로 인덱스는 효율적으로 탈 수 없다.

EXPLAIN SELECT seq, one_field, two_field FROM test1 WHERE two_field = 3;

결과적으로 인덱스 풀스캔이 발생한 것을 볼 수 있다. (type : Index)
 

인덱스 풀스캔이 발생하여 row 필드를 통해 9834개의 행을 가져온 것을 볼 수 있다. rows 필드는 최초에 접근하는 테이블에 대해서는 쿼리에 의해 접근하는 행의 수, 그 이후 테이블에 대해서는 1행의 Join으로 몇 행에 접근했는가를 표시한다. (단 어디까지나 통계 값으로 계산한 값이므로 실제 행 수와 반드시 일치하지 않는다. 출처 : https://cheese10yun.github.io/mysql-explian)
 
이 예시에서 인덱스 풀 스캔의 조회 과정을 살펴보면 다음과 같다.
 

1. 사용 가능한 인덱스 조회
   • MySQL 옵티마이저는 쿼리에 대해 가능한 인덱스들을 평가한다. 예를 들어 WHERE 절에 포함된 컬럼인 two_field로 구성된 인덱스를 찾고 평가한다.
   • possible_keys 필드에는 이처럼 MySQL 옵티마이저가 고려한 인덱스(사용 가능성이 있는 인덱스) 목록이 출력된다.
2. 인덱스 선택 및 풀스캔
   • 옵티마이저는 possible_keys 중 적합한 인덱스를 선택하여 사용한다. 이처럼 실제로 사용한 인덱스가 key에 출력한다.
   • 이때 two_field가 복합 인덱스(one_field, two_field)의 두 번째 컬럼이므로, 인덱스 풀스캔이 발생할 수 있다.
   • 쉽게 말해 인덱스는 one_field를 기준으로 정렬되어 있으므로, two_field 조건에 맞는 값을 찾기 위해 많은 로우를 탐색하게 된다.

부분 인덱스 스캔(type : ref)

WHERE 조건에 의해 인덱스의 일부만 조회하는 방식, 인덱스의 특정 값에 대해 조건을 만족하는 행을 찾을때 사용된다. 즉, 인덱스의 특정 범위만 조회한다.
 
일반적으로 WHERE절에 인덱스가 적절하게 사용되는 경우에 나타난다. 인덱스 전체를 스캔하지 않고, 필요한 일부 범위에 해당하는 값만 조회하므로 type: Index보다 효율적이다.
 
다음과 가은 쿼리를 실행해보면 인덱스 풀 스캔이 발생하지 않고 필요한 row만 조회할 수 있다.

EXPLAIN SELECT one_field, two_field FROM test1 WHERE one_field = 2 AND two_field = 3;

결과적으로 ref 이면서 1개의 row에 접근하고 조회한 것을 볼 수 있다
 

 

범위 스캔(type : range)

인덱스의 일부 범위에 속하는 값을 검색할 때 사용된다. 주로 WHERE 절에 특정 조건이 포함되어 있을 때 발생한다. 인덱스를 효율적으로 활용하기 때문에, 풀스캔보다 성능이 좋다.
 
인덱스가 있는 컬럼에 다음과 같이 BETWEETN, <, ≤, >, ≥, IN 등의 날짜나 숫자 범위 조건이 포함된 경우 발생한다.

EXPLAIN SELECT one_field, two_field FROM test1 WHERE one_field = 2 AND two_field >= 3;

 
실행 결과 type: range인 것을 볼 수 있다. 즉, 인덱스를 사용하는 범위에 맞는 첫번째 엔트리를 찾고, 그 다음부터 해당 범위 내에 속하는 값들을 순차적으로 탐색한다. 이 과정에서 인덱스의 범위 내에 속하지 않는 값은 탐색하지 않기 때문에 효율적이다. 또한 인덱스의 범위를 넘어서는 탐색을 하지 않으므로, 범위 내에서의 탐색이 필요할 때 최적화되어 있다.

 

인덱스 목록 조회

다음 쿼리를 통해 테이블에 정의된 인덱스를 조회할 수 있다.

-- 인덱스 목록 조회
SHOW INDEX FROM test1;

 
쿼리 실행 결과 다음과 같이 PK와 설정된 인덱스 목록을 볼 수 있다.
 

 

정리

type 필드에 나타나는 주요 값은 다음과 같다. (출처 : https://cheese10yun.github.io/mysql-explian/)

접근 방식	설명
const	기본 키 또는 고유키에 의한 loockup(등가비교) 조인이 아닌 가장 외부의 테이블에 접근 하는 방식, 결과는 항상 1행이다. 단 기본 키, 고유 키를 사용하고 있으므로 범위 검색으로 지정하는 경우 const가 되지 않는다.
system	테이블에 1행밖에 없는 경우의 특수한 접근 방식
ALL	전체 행 스캔, 테이블의 데이터 전체에 접근한다.
index	인덱스 스캔, 테이블의 특정 인덱스의 전체 엔트리에 접근한다.
eq_ref	조인이 내부 테이블로 접근할 때 기본키 또는 공유 키에 의한 lookup이 일어난다. const와 비슷하지만 조인의 내부 테이블에 접근한다는 점이 다르다
ref	고유 키가아닌 인덱스에 대한 등가비교, 여러 개 행에 접근할 가능성이 있다.
ref_or_null	ref와 마찬가지로 인덱스 접근 시 맨 앞에 저장되어 있는 NULL의 엔트리를 검색한다.
range	인덱스 특정 범위의 행에 접근한다
fulltext	fulltext 인덱스를 사용한 검색
index_merge	여러 개인스턴스를 사용해 행을 가져오고 그 결과를 통합한다.
unique_subquery	IN 서브쿼리 접근에서 기본 키 또는 고유 키를 사용한다. 이 방식은 쓸데 없는 오버헤드를 줄여 상당히 빠르다.
index_subquery	unique_sunquery와 거의 비슷하지만 고유한 인덱스를 사용하지 않는 점이 다르다. 이 접근 방식도 상당히 빠르다

실무에서는 위에서 살펴본 바와 같이 type이 ref이거나 range가 되도록 인덱스를 구성하고 쿼리를 작성하는 편이 좋다.