커버링 인덱스

쿼리에 필요한 컬럼만 인덱스에 포함시키는 방식

1. 인덱스의 기본 원리

[인덱스 컬럼 값] -> [해당 데이터가 있는 레코드의 주소(Primary Key)]

user 테이블에 email 컬럼으로 인덱스를 만들었다고 가정

'a@gmail.com' -> PK: 123
'b@gmail.com' -> PK: 456
'c@gmail.com' -> PK: 789

WHERE email = 'b@gmail.com' 쿼리를 실행하면, MySQL은 먼저 인덱스에서 'b@gmail.com'을 찾고, 그 옆에 있는 주소(PK: 456)를 얻습니다. 그런 다음, 이 주소를 가지고 다시 테이블의 데이터 블록으로 가서 PK가 456인 모든 레코드 정보를 읽어옵니다. 이렇게 인덱스를 읽고 다시 테이블로 돌아가는 과정을 '랜덤 I/O'라고 하며, 이 과정이 성능을 저하시키는 주범.

2. 커버링 인덱스의 원리: 인덱스에 데이터 '포함'시키기

커버링 인덱스는 방금 설명한 '랜덤 I/O' 과정을 없애기 위해 만들어졌습니다.

커버링 인덱스는 쿼리에서 필요한 컬럼들을 인덱스에 '추가'해서 만드는 복합 인덱스(Composite Index)의 일종입니다.

user 테이블에 email과 name 컬럼이 있고, SELECT name FROM user WHERE email = 'b@gmail.com'이라는 쿼리가 있다고 가정해봅시다.

만약 email 컬럼에만 인덱스가 있다면, MySQL은 email 인덱스를 통해 PK를 찾고, 다시 테이블로 가서 name 값을 가져와야 합니다.

하지만 (email, name)으로 인덱스를 만들면 어떻게 될까요?
이 인덱스의 구조는 다음과 같이 됩니다.

[인덱스 컬럼 값1][인덱스 컬럼 값2] -> [해당 데이터가 있는 레코드의 주소(Primary Key)]

'a@gmail.com'     'Alice'            -> PK: 123
'b@gmail.com'     'John'             -> PK: 456
'c@gmail.com'     'Michael'          -> PK: 789

SELECT name FROM user WHERE email = 'b@gmail.com' 쿼리를 실행하면,
MySQL은 'b@gmail.com'을 찾았을 때, 이미 name 컬럼의 값('John')이 인덱스에 함께 저장되어 있는 것을 확인합니다.
따라서 굳이 테이블로 돌아가서 데이터를 읽어올 필요가 없어집니다. 이 때문에 EXPLAIN 결과에 Using index가 뜨는 겁니다.

저장 공간 비효율성 문제에 대한 답

데이터를 인덱스에 추가로 저장하기 때문에 저장 공간을 더 차지합니다.

하지만 데이터베이스 시스템은 이 저장 공간의 증가와 I/O 작업 감소라는 성능 향상을 맞바꿉니다.

수천만에서 수억 개의 데이터가 쌓이는 대규모 시스템에서는 디스크 I/O가 가장 큰 병목 현상을 일으킵니다. 수십, 수백 GB의 데이터를 물리적으로 디스크에서 읽어오는 작업은 CPU 연산에 비해 수만 배 이상 느립니다.

커버링 인덱스를 통해 랜덤 I/O를 한 번이라도 줄이면 성능이 획기적으로 향상됩니다. SELECT 쿼리가 매우 자주 실행되는 서비스라면, 이 정도 저장 공간 증가는 충분히 감수할 만한 가치가 있습니다.

즉, 커버링 인덱스는 무작정 모든 컬럼을 다 포함시키는 것이 아니라, 자주 사용되는 쿼리를 분석하여 꼭 필요한 컬럼만 인덱스에 포함시켜 공간 효율과 성능을 모두 잡는 전략적 선택입니다.

3. 커버링 인덱스 생성에 관해

커버링 인덱스는 데이터베이스가 자동으로 생성해주지 않으며, DB 관리자(DBA)나 데이터베이스를 다루는 엔지니어가 직접 전략적으로 설계하고 생성해야 합니다.

1단계: 쿼리 분석

• 가장 먼저, 성능이 느린 쿼리를 파악. EXPLAIN을 통해 type이 ALL이거나 Extra에 Using filesort 등이 나타나는 쿼리를 찾아냅니다.

2단계: WHERE 절과 SELECT 절의 컬럼 파악

• EXPLAIN을 통해 느린 쿼리를 찾았다면, 해당 쿼리의 WHERE 절과 SELECT 절에 사용된 컬럼을 확인합니다.

• WHERE 절의 컬럼은 인덱스의 선행 컬럼이 되어야 합니다.

• SELECT 절의 컬럼은 인덱스에 포함되어야 할 컬럼입니다.

3단계: 인덱스 생성 및 검증

• 위에서 파악한 컬럼들을 묶어 복합 인덱스를 생성하고, 다시 EXPLAIN으로 실행 계획을 확인합니다.
• 이때 type이 ref나 range로 바뀌고, Extra에 Using index가 나타난다면 성공입니다.

SELECT name, phone FROM user WHERE email = 'john@example.com';

CREATE INDEX idx_user_email_name_phone ON user (email, name, phone);

분석: WHERE 절에 email이 있고, SELECT 절에 name과 phone이 있습니다.

가설 수립: (email, name, phone)으로 인덱스를 만들면 이 쿼리를 커버할 수 있을 것 같습니다.

검증: 다시 쿼리를 실행하고 EXPLAIN을 해봅니다. Extra에 Using index가 나타난다면 최적화에 성공한 것입니다.