MySQL 커버링 인덱스 Covering Index

이 글에서는 커버링 인덱스와 pk 를 활용한 복합 인덱스에 대해 간단한 예시를 통해 설명합니다.

 

 

커버링 인덱스 Covering Index는 특정 쿼리의 모든 필요한 데이터를 인덱스에서 직접 얻을 수 있게 하는 인덱스입니다.

이는 테이블 자체에 접근할 필요 없이 인덱스에서 쿼리의 모든 필요한 데이터를 찾을 수 있게 합니다. 

커버링 인덱스는 인덱스에서 충족하는 데이터를 갖고 있어 디스크 I/O 를 줄이고,

테이블 락을 줄임으로써 데이터베이스의 전반적인 부하를 감소시켜 성능을 향상시킵니다. 

 

 

예제1 - 단순 select

 

테이블은 위와 같은 모습으로 준비했습니다.

id 는 pk, auto increment 로 하나씩 올라갑니다.

또한 테이블에는 미리 1,000 만 개의 데이터를 넣어 두었습니다.

 

커버링 인덱스는 인덱스를 통해 모든 데이터를 가져올 수 있는 것을 얘기한다고 말씀드렸습니다.

따라서 차이를 위해 인덱스를 통해 가져오는지

혹은 데이터 블록을 통해 가져오는지를 먼저 판단할 수 있는 쿼리를 실행하겠습니다.

 

 

explain
select *
from test2
where id = 333;

id 가 pk 이므로 B+Tree 를 사용한 인덱스로 관리됩니다.

레코드에 있는 모든 데이터를 조회하므로 직접 데이터 블록에 접근해야 할 것입니다.

 

위의 쿼리의 실행 계획을 explain 으로 조회하였습니다.

 

select_type simple 로 기본적인 select 쿼리임을 나타내고,

key 컬럼을 통해 index 를 id 로 사용한 결과를 보여줍니다.

다만 이때에는 Extra 가 빈 값으로 나왔다는 것은, 인덱스로 모든 결과를 반환할 수 없어

실제로 데이터 블록으로의 접근이 있었다는 것을 암시합니다.

 

 

 

explain
select id
from test2
where id = 333;

이번에는 커버링 인덱스를 사용하는 상황을 확인해보겠습니다.

id 가 pk 이므로 충분히 인덱스를 통해 반환받을 수 있을 것으로 예상됩니다.

 

Extra 에 Using index 로 커버링 인덱스가 사용되었다고 볼 수 있습니다.

 

 

 

예제2 - 커버링 인덱스 조건과 복합 인덱스

복합 인덱스로 (a, b, c) 로 구성할 경우 a, b, c 를 순서대로 조건에 만족해야 인덱스를 효율적으로 사용합니다.

단, 이때 순서를 만족했다면 모두 포함하지 않아도 괜찮습니다.

이를테면, group by a, b 만 있는 상황이라거나

where a = ? group by b, c 와 같은 상황을 얘기할 수 있겠습니다.

따라서 group by b, c 는 복합 인덱스를 효율적으로 사용하지 못합니다.

또한 조건에 복합 인덱스가 아닌 다른 조건은 들어와서는 안됩니다.

 

CREATE INDEX idx_email_first_name ON test2(email, first_name);

 

위와 같은 인덱스를 생성 후 실행 계획을 살펴보겠습니다.

 

 

 

explain
select 
	email, first_name
from 
	test2
where 
	first_name = 'Adrian';

email, first_name 을 조회하면서 where 조건에 first_name 을 걸어줍니다.

 

주목해야 할 것은 Extra 입니다.

"Using where; Using index" 라는 결과를 얻었습니다.

Using where 를 통해 data 를 찾을 때 where 절의 조건을 사용했다고 할 수 있고,

type 이 index 이므로 index 를 풀스캔 했다고 볼 수 있습니다.

다만, Using index 를 통해 Disk I/O 는 일어나지 않고 커버링 인덱스를 사용했음을 알 수 있습니다.

 

위에서 설명했듯, email, first_name의 순서로 where 이나 group by 조건을 걸어야 index 가 적용되지만

이 때에는 2 번째인 first_name 을 사용했고 이로 인해 인덱스를 풀스캔 했으나

커버링 인덱스로 Disk I/O 는 일어나지 않았다는 정리를 해볼 수 있습니다.

 

 

 

explain
select 
email, first_name
from test2
where email = 'amckeandrq@businessinsider.com'
and first_name = 'Adrian';

그렇다면 위의 경우라면 당연히 index 를 풀스캔 하지 않고 특정 값이 일치하는 행을 선택할 것입니다.

 

 

원하는 결과를 얻었습니다. Extra 에서 Using index 로 커버링 인덱스를 사용했음을 보여주고

type 을 통해 ref 로 인덱스의 특정 값이 일치하는 행을 선택한 것을 알 수 있습니다.

 

그렇다면 where 조건에 인덱스에 포함되지 않은, pk인 id 를 포함시킨다면 어떨까요?

 

 

 

explain
select 
email, first_name
from test2
where email = 'amckeandrq@businessinsider.com'
and first_name = 'Adrian'
and id = 2;

위와 같이 pk 를 조건으로 포함한 복합 인덱스의 순서대로 검색하는 쿼리를 수행하겠습니다.

예제 2의 서두에 언급했듯, 인덱스에 포함되지 않는 컬럼이 들어오면 인덱스를 효율적으로 사용하지 못합니다.

과연 예상하는 결과를 얻을지 실행하며 확인해보겠습니다.

 

인덱스를 사용하거나, 인덱스를 효율적으로 사용하지 못하는 상황도 아닌 예상외의 결과입니다!

 

복합인덱스와 포함되지 않는 컬럼으로 검색했으나

Extra 가 Using where 나 Using index 가 아닌 null 로 레코드를 직접 읽었다고 유추할 수 있습니다.

(Disk I/O 가 발생했거나 버퍼 풀을 읽었을 것입니다.)

 

Possible key 가 id 와 저희가 생성했던 idx_email_first_name 이지만 실제로 사용됐던 key 는 id 로 확인됩니다. 

자세한 이유는 아래에서 살펴보겠습니다.

 

 

 

예제3 - 복합 인덱스와 PK 를 포함한 Projection

이번에는 pk 를 포함하지 않는 인덱스를 사용하는 상황에서 pk 를 함께 조회한다면

커버링 인덱스로 조회할 수 있는지 확인해보겠습니다.

 

원하는 상황은 전과 같은 인덱스가 걸린 상황에서 id, email, first_name 을 조회할 때

커버링 인덱스로 작동하는지 확인하는 것입니다.

 

 

 

explain
select 
id, email, first_name
from test2
where email = 'amckeandrq@businessinsider.com'
and first_name = 'Adrian';

위와 같은 쿼리를 실행하며 확인하겠습니다.

index 의 순서를 보장하며 select 해보겠습니다.

 

위와 같은 결과를 얻었습니다!

Extra 가 Using index 로 커버링 인덱스를 사용한 것을 알 수 있습니다.

분명히 인덱스는 email, first_name 만을 포함하며 id 는 포함하지 않았습니다.

그럼에도 불구하고 pk 인 id 를 포함하며 조회할 때에도 커버링 인덱스로 조회되었습니다.

 

이러한 현상에 대한 이해는 B+Tree 구조를 살펴보며 알아보겠습니다.

 

 

 

Clustered Key

https://jojoldu.tistory.com/476

 

예제 3번에 대해서 설명합니다.

인덱스는 B+Tree 로 관리되며 리프 노드에는 pk 에 대한 참조 값을 가지고 있습니다.

여기서 참조 값은 pk 의 값을 의미하며, 이 값은 다시 clustered key 라고 표현합니다.

MySQL InnoDB는 clustered key 를 기준으로, 물리적인 정렬을 통해 페이지에 저장합니다.

index 를 email, first_name 으로 걸었으나 실제로 index에는 id와 함께 저장되어 있는 것입니다.

(다만, pk 의 사용은 use_index_extensions 옵션과 관련이 있습니다. 기본값이 pk 를 사용하도록 되어있습니다.)

 

index 는 어떤 조건에 대해 레코드에 빠른 접근을 가능하게 하며,

필요에 따라 레코드에 접근하지 않고도 데이터를 반환합니다.

당연히 레코드에 접근하지 않을 때 가장 빠른 응답속도를 보일 것이지만

그렇다고 모든 컬럼에 인덱스를 걸 수 없습니다.

 

위에서 살펴 봤듯, secondary index 에는 pk 가 함께 포함되어 있으므로

모든 index 에 pk 값을 가지게 되고, index 관리를 위한 비용이 발생하기 때문입니다.

(index 유지, 레코드 수정, 버퍼 풀 캐싱, 자식 노드(페이지) 개수 등)

따라서 크기를 고려하여 index 를 셋팅할 필요가 있겠습니다.

 

 

예제 2번에 대해서 설명합니다.

예제 2번에서는 index 를 순서대로 조건에 걸며 추가로 pk 를 조건으로 걸었습니다.

index에는 leaf에 pk값을 가지고 있지만, 위에서 살펴 봤듯 B+Tree 를 한 번 더 타야 하는 비효율이 있습니다.

 

따라서 optimizer 는 실행계획을 세우며 조건을 확인한 결과

id 가 pk 이므로 직접 페이지로 찾아가면 될 것이라고 생각했을 것입니다.

 

따라서 위의 결과에서 type 이 const 인 이유가 그렇습니다.

const 는 PK 이거나 Unique index 인 경우 상수를 활용하여 찾는 것을 의미합니다.

optimizer 는 인덱스를 통해 참조하는 것 보다 직접 clustered key 로 찾아가는 것이 효율적이라고 판단한 것입니다.

 

 

 

커버링 인덱스와 복합키를 포함한 복합 인덱스로 커버링 인덱스를 알아보았습니다.

하나의 테이블에 대해 여러 커버링 인덱스를 만들 수 있으며, 각각은 특정 유형의 쿼리를 최적화하는 데 도움이 될 수 있습니다. 

그러나 인덱스가 너무 많으면 쓰기 작업의 성능에 부정적인 영향을 미칠 수 있으므로, 이러한 점을 고려하여 인덱스를 설계해야 합니다.

따라서 애플리케이션의 요구 사항과 쿼리 패턴을 이해하고 적절한 인덱스를 설계하는 것이 성능 최적화에 중요한 요소가 되겠습니다.

 

끝!