커버링 인덱스 (Covering Index) 를 사용해서 쿼리 최적화하기

2024. 11. 30. 09:07

커버링 인덱스(Covering Index)란 무엇인가?

커버링 인덱스는 쿼리가 필요로 하는 모든 컬럼을 포함하는 인덱스를 말합니다. 이 인덱스를 사용하면 테이블의 실제 데이터 페이지에 접근하지 않고도 인덱스만으로 원하는 데이터를 조회할 수 있어 디스크 I/O를 절약할 수 있습니다.

MySQL 공식 문서 정의:

*"쿼리에서 검색되는 모든 컬럼을 포함하는 인덱스입니다. 인덱스 값을 전체 테이블 행을 찾는 포인터로 사용하는 대신, 쿼리는 인덱스 구조에서 값을 반환하여 디스크 I/O를 절약합니다. InnoDB는 MyISAM보다 더 많은 인덱스에 이 최적화 기술을 적용할 수 있습니다. 왜냐하면 InnoDB의 보조 인덱스에는 기본 키 컬럼도 포함되어 있기 때문입니다. InnoDB는 해당 트랜잭션이 끝날 때까지 트랜잭션에 의해 수정된 테이블에 대한 쿼리에 이 기술을 적용할 수 없습니다."*

단일 컬럼 인덱스와 다중 컬럼 인덱스 모두 커버링 인덱스로 활용될 수 있습니다. 적절한 인덱스 설계와 쿼리 작성으로 이 최적화 기법을 최대한 활용할 수 있습니다.

커버링 인덱스를 사용하는 이유

디스크 I/O 감소: 인덱스만으로 데이터를 조회하므로 디스크 접근 횟수가 줄어듭니다.
성능 향상: 디스크 I/O 감소로 쿼리 응답 속도가 빨라집니다.
잠금 경합 감소: 테이블 데이터 페이지에 대한 접근이 줄어들어 잠금 경합이 감소합니다.

클러스터드 인덱스와 비클러스터드 인덱스

클러스터드 인덱스 (Clustered Index)

정의: 테이블의 실제 데이터가 인덱스와 동일한 구조로 저장되는 인덱스입니다.
특징:
- 테이블 당 하나만 존재합니다.
- 기본 키(primary key)가 클러스터드 인덱스로 사용됩니다.
장점:
- 인덱스를 통해 바로 데이터에 접근하므로 조회 속도가 빠릅니다.

비클러스터드 인덱스 (Non-clustered Index)

정의: 인덱스는 별도의 구조로 저장되고, 인덱스 엔트리는 데이터의 물리적 위치를 가리킵니다.
특징:
- 테이블 당 여러 개의 비클러스터드 인덱스를 가질 수 있습니다.
단점:
- 인덱스를 통해 데이터를 찾은 후 실제 데이터 페이지를 다시 조회해야 하므로 추가적인 I/O가 발생합니다.

MySQL 공식 문서 인용:

*"클러스터드 인덱스를 통해 행에 접근하는 것은 빠릅니다. 왜냐하면 인덱스 검색이 행 데이터가 있는 페이지로 직접 연결되기 때문입니다. 테이블이 큰 경우, 클러스터드 인덱스 아키텍처는 인덱스 레코드와 다른 페이지에 행 데이터를 저장하는 스토리지 구조와 비교했을 때 디스크 I/O 작업을 절약할 수 있습니다."*

커버링 인덱스 사용과 미사용 시 성능 비교

실험: 100만 건의 데이터로 임시 테이블 생성 후 성능 비교

데이터 준비

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    col1 VARCHAR(100),
    col2 VARCHAR(100),
    col3 VARCHAR(100)
);

INSERT INTO test_table (id, col1, col2, col3)
SELECT
    t1.number AS id,
    MD5(RAND()) AS col1,
    MD5(RAND()) AS col2,
    MD5(RAND()) AS col3
FROM
    numbers AS t1  -- numbers 테이블은 1부터 1,000,000까지의 숫자를 가진 테이블이라고 가정
LIMIT 1000000;

인덱스 설정
- 커버링 인덱스 생성
```
CREATE INDEX idx_col1_col2 ON test_table (col1, col2);
```
- 인덱스 미사용
  - 인덱스를 생성하지 않음.
쿼리 실행 및 성능 측정
- 커버링 인덱스 사용 시
```
SELECT col1, col2 FROM test_table WHERE col1 = 'some_value';
```
  - 인덱스만으로 쿼리를 처리하여 빠른 응답 속도를 보입니다.
- 인덱스 미사용 시
```
SELECT col1, col2 FROM test_table WHERE col1 = 'some_value';
```
  - 전체 테이블 스캔이 발생하여 응답 시간이 느려집니다.
결과
- 커버링 인덱스를 사용하면 쿼리 성능이 현저히 향상됩니다.
- 인덱스를 사용하지 않으면 대량의 데이터를 처리할 때 성능 저하가 발생합니다.

GROUP BY에서의 커버링 인덱스 활용

GROUP BY 절에서도 커버링 인덱스를 활용하여 성능을 개선할 수 있습니다.

인덱스 생성

CREATE INDEX idx_col1_col2 ON test_table (col1, col2);

쿼리 실행

SELECT col1, COUNT(*) FROM test_table GROUP BY col1;

설명
- col1과 col2를 포함하는 인덱스를 생성하여 GROUP BY 시 인덱스만으로 결과를 도출합니다.
- 디스크 I/O를 최소화하여 쿼리 성능을 높입니다.

참고 자료

'Database > My SQL' 카테고리의 다른 글

MySQL Index 정리 및 팁 (0)	2022.05.16

Software Engineer Ethan