커버링 인덱스 & 성능 테스트

"DB에서 데이터 조회를 더 빨리하고 싶은데, 어떻게 하면 좋을까?"

 

 

이번 글은 데이터를 빠르게 조회할 수 있는 커버링 인덱스에 대해 소개하고, 직접 성능 테스트를 진행해 얼마나 빨라질 수 있는지 확인하는 글입니다. 먼저, 커버링 인덱스에 대해서 알아보도록 하겠습니다.

 

커버링 인덱스

커버링 인덱스(Covering Index)는 쿼리에 필요한 모든 데이터를 인덱스 자체가 포함하고 있어, 테이블을 조회하지 않고 인덱스만으로 결과를 반환할 수 있는 인덱스를 의미합니다. 커버링 인덱스는 테이블을 조회하지 않고 인덱스만으로 데이터를 반환할 수 있어, 일반적인 인덱스보다 더욱 빠르게 데이터를 조회할 수 있습니다.

 

"어떻게 인덱스로만 데이터를 반환하지?"

 

먼저, 인덱스가 동작하는 원리에 대해서 간단히 알아보도록 하겠습니다. 

인덱스가 동작하는 원리를 그림으로 나타낸것은 다음과 같습니다.

(📍현재 Index되어 있는 column은 age입니다.)

Non Clustered Key (일반적인 인덱스)는 인덱스 컬럼의 값들과 Clustered Key (PK) 의 값이 포함되어 있고, Clustered Key를 통해 필요한 데이터가 존재하는 테이블에 접근할 수 있습니다. 따라서, 기본 인덱스를 사용해서 값을 조회하기 위해선 2번의 과정이 필요하게 됩니다.

 

2번의 과정을 좀 더 세부적으로 나타내면 다음과 같습니다.

(1) where절의 age를 기준으로 인덱스 탐색을 진행해, age값이 20인 인덱스 컬럼(20)을 찾아 E라는 Clustered Key를 획득합니다.
(2) 획득한 E라는 Clustered Key를 통해 필요한 데이터인 이름값 "김갑환"을 조회하게 됩니다.

 

 

커버링 인덱스가 동작하는 방식은 다음과 같습니다. 

먼저, 다음과 같은 쿼리문을 통해 age와 name을 기준으로 Index를 생성해주도록 하겠습니다.

CREATE INDEX idx_users_age_name ON users (age, name);

 

커버링 인덱스가 동작하는 원리를 나타내면 다음과 같습니다.

커버링 인덱스는 인덱스 자체에 찾고자 하는 값이 포함되어 있기 때문에, 1번의 탐색 과정으로 데이터를 찾을 수 있습니다.

정리하면, 기존 인덱스는 2번을 탐색했다면, 커버링 인덱스는 1번의 탐색만으로 원하는 데이터를 조회할 수 있습니다.

 

⚠️ 그러나, 주의할 점은 조회하고자 하는 컬럼의 항목이 인덱싱되어 있어야 한다는 점입니다.

커버링 인덱스가 적용되지 않는 상황은 다음과 같습니다. 

이전과 똑같이 age와 name을 기준으로 인덱스를 생성해줍니다.

CREATE INDEX idx_users_age_name ON users (age, name);

 

조회에 사용되는 쿼리는 다음과 같습니다.

SELECT address FROM member WHERE age = 20;

 

이 쿼리는 현재 address값을 조회하고자 합니다. 하지만, 이전에 생성한 인덱스는 age와 name을 기준으로 생성이 되었습니다.

"이렇게 조회할 경우 어떻게 될까요?"

현재 인덱스에는 address라는 값이 존재하지 않습니다. 이말은 곧 한번 더 member 테이블을 조회해야 함을 의미합니다.

address를 조회하는 흐름은 다음과 같습니다.

(1) where절의 age를 기준으로 인덱스 탐색을 진행해, age값이 20인 인덱스 컬럼(20)을 찾았는데, address의 값이 존재하지 않기에 E라는 Clustered Key를 획득합니다.
(2) 획득한 E라는 Clustered Key를 통해 필요한 데이터인 주소값 "전주"를 조회하게 됩니다.

 

정리하면, 인덱스 생성시 사용했던 컬럼을 조회하지 않는 경우, 기존 인덱스 방식과 똑같이 동작합니다.

(age,name)으로 인덱스를 생성한 경우엔, 조회시 age나 name을 조회하는 경우에만 커버링 인덱스를 통해 더 빠르게 데이터를 조회할 수 있습니다.

 

성능 테스트

 

그림 예시와 동일하게 일반 조회, 인덱싱 조회, 커버링 조회를 통해 각각의 성능을 비교해보도록 하겠습니다.

임의의 더미 데이터 100만개를 DB에 삽입한 후, 직접 조회 시간을 비교하며 테스트하도록 하겠습니다.

사용한 데이터베이는 MySQL Ver 9.0.1이고, 노트북의 성능은 CPU는 10Core, 메모리는 8GB입니다.

 

먼저, Mysql DB에 테이블을 생성해주도록 하겠습니다. 사용한 쿼리는 다음과 같습니다.

CREATE TABLE member (
	id int primary key auto_increment,
    name varchar(100),
    age int,
    address varchar(255)
);

 

다음으로, 더미 데이터 100만개를 DB에 삽입하도록 하겠습니다. 사용한 쿼리는 다음과 같습니다.

INSERT INTO users (name, age, address)
SELECT 
    CONCAT('User', FLOOR(RAND() * 1000000)),
    FLOOR(20 + (RAND() * 40)),
    CONCAT('City ', FLOOR(RAND() * 100))
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) c,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) d,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) e,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) f;

 

DB에 삽입된 레코드는 다음과 같습니다.

(단, random으로 값을 부여했으므로 중복이 될 수 있습니다.)

왼쪽을 기준으로 id, name, age, address의 컬럼을 나타냅니다.

 

 

조회할 데이터는 마지막 데이터인 100만번째 데이터를 조회하는 것을 기준으로 하겠습니다. 하나의 데이터만 조회하기 위해 100만번째 유저의 나이를 61로 변경해 줍니다. 

 

"왜 나이를 61으로 변경해주는걸까?"

DB에 삽입해준 임의의 데이터들의 나이의 범위가 20 ~ 60이기 때문입니다.

 

일반 조회 

먼저, 일반 조회를 통해 몇초가 걸리는지 확인해보겠습니다.

사용한 쿼리문은 다음과 같습니다.

SELECT name FROM member where age = 61;

 

이제, 조회 해보겠습니다. 조회하는데 걸린 시간은 다음과 같습니다.

일반 조회는 307ms가 걸립니다.

 

 

인덱스 조회

인덱스 조회를 하기 위해서 인덱스를 다음과 같이 생성해 줍니다.

CREATE INDEX idx_memeber_age ON member (age);

 

인덱스가 잘 생성이 되었는지 다음과 같은 쿼리문을 통해 확인해줍니다.

show index from member;

 

idx_member_age 인덱스키가 생성된것을 확인할 수 있습니다.

 

이제, 조회 해보겠습니다. 조회하는데 걸리는 시간은 다음과 같습니다.

인덱스 조회는 5.2ms가 걸립니다.

 

 

커버링 인덱스 조회

커버링 인덱스 조회를 하기 위해서 인덱스를 다음과 같이 생성해 줍니다.

CREATE INDEX idx_member_age_name ON member (age, name);

 

인덱스가 잘 생성이 되었는지 확인해줍니다.

idx_member_age 인덱스키가 생성된것을 확인할 수 있습니다.

(age, name)순으로 해주었기에 순서도 1,2로 됨을 확인할 수 있습니다.

 

이제, 조회 해보겠습니다. 조회하는데 걸리는 시간은 다음과 같습니다.

인덱스 조회는 0.85ms가 걸립니다.

 

 

일반조회, 인덱스 조회, 커버링 인덱스 조회 시간을 표로 나타내면 다음과 같습니다.

	일반 조회	인덱스 조회	커버링 인덱스 조회
조회 시간(ms)	307	5.2	0.85

 

확실히 일반 조회, 인덱스, 커버링 인덱스 순으로 점점 속도가 빨라지게 됩니다.

그렇다면, 커버링 인덱스를 무조건 사용하는 것이 좋은것일까요?

 

커버링 인덱스의 단점

커버링 인덱스를 남용하면 다음과 같은 문제점이 있습니다.

 

(1) 인덱스 크기 증가

(2) INSERT/UPDATE/DELETE 성능 저하

(3) 너무 많은 컬럼 포함 시 비효율적

(4) 쿼리 패턴에 따라 비효율적일 수 있음

(5) 인덱스 유지보수 어려움

 

 

정리

커버링 인덱스(Covering Index)는 쿼리에 필요한 모든 데이터를 인덱스 자체가 포함하고 있어, 테이블을 조회하지 않고 인덱스만으로 결과를 반환할 수 있는 인덱스입니다. 한번의 과정을 통해 조회하고자 하는 값을 바로 찾을 수 있습니다. 단, 조회시 인덱스로 생성해준 컬럼만 해당됩니다.

 

커버링 인덱스는 좋은점도 있지만 단점도 존재합니다. 상황에 맞게 커버링 인덱스를 적절히 사용하는 것이 좋다고 생각합니다.