커버링 인덱스(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 |
|---|