1. 커버링 인덱스 (Covering Index)
커버링 인덱스는 데이터베이스 엔진이 인덱스 트리만 탐색하여 쿼리에 필요한 모든 컬럼 값을 반환할 수 있는 상태를 의미합니다. 이 경우 일반적으로 발생하는 키 룩업(Key Lookup) 또는 북마크 룩업(Bookmark Lookup)이 생략됩니다.
동작 원리
일반적으로 비클러스터형(세컨더리) 인덱스는 인덱스 컬럼 값과 함께 클러스터형 인덱스(기본 키) 값만 포함합니다. 따라서 조회 대상이 인덱스에 없다면 기본 키를 기준으로 데이터 페이지를 추가로 읽어야 합니다. 반면, 쿼리의 SELECT, WHERE, JOIN, ORDER BY 절에 포함된 모든 컬럼이 단일 인덱스에 포함된다면, 엔진은 데이터 파일 접근 없이 인덱스 리프 노드에서만 결과를 완성할 수 있습니다.
실습 예시
재고 관리 테이블을 가정해 보겠습니다.
CREATE TABLE `inventory` (
`item_code` VARCHAR(20) PRIMARY KEY,
`warehouse_id` INT,
`sku` VARCHAR(50),
`stock_count` INT,
KEY `idx_wh_sku_cnt` (`warehouse_id`, `sku`, `stock_count`)
);
- 일반 쿼리 (키 룩업 발생):
SELECT * FROM inventory WHERE warehouse_id = 1001 AND sku = 'WIDGET-A';SELECT *는 인덱스에 없는item_code등 전체 행 데이터를 요청하므로, 엔진은 인덱스에서 찾은 기본 키로 원본 페이지를 다시 읽습니다. - 커버링 인덱스 활용 (키 룩업 없음):
요청한 컬럼이 모두SELECT warehouse_id, sku, stock_count FROM inventory WHERE warehouse_id = 1001 AND sku = 'WIDGET-A';idx_wh_sku_cnt에 포함되어 있습니다. 실행 계획의Extra필드에Using index가 표시되며, 추가적인 디스크 I/O가 발생하지 않습니다.
장점 및 설계 고려사항
- 데이터 페이지 접근을 최소화하여 I/O 병목을 해소합니다.
- InnoDB에서 세컨더리 인덱스는 B+Tree 구조로 기본 키를 내장하므로, 조회 컬럼이 인덱스와 기본 키로 구성될 때 매우 효율적입니다.
- 인덱스 크기가 과도하게 커지면 쓰기(INSERT/UPDATE) 성능이 저하되고 메모리 캐시 효율이 떨어질 수 있으므로, 빈번한 조회 패턴에 맞춰 신중하게 확장해야 합니다.
2. 인덱스 병합 (Index Merge)
인덱스 병합은 옵티마이저가 단일 테이블에 대해 여러 개의 단열 인덱스를 동시에 활용하고, 각 인덱스에서 추출한 레코드 세트를 연산자로 결합하는 전략입니다.
지원 알고리즘
- Intersection (교집합):
WHERE col1 = ? AND col2 = ?형태에서 각 컬럼에 독립된 인덱스가 존재할 때, 양쪽 인덱스 범위 스캔을 수행한 후 기본 키 집합의 교집합을 구합니다. - Union (합집합):
WHERE col1 = ? OR col2 = ?형태에서 각 조건에 해당하는 기본 키를 별도로 추출한 뒤 중복을 제거하여 합집합을 구성합니다. - Sort-Union:
OR조건이 범위 연산자(>,BETWEEN등)를 포함할 경우, 각 인덱스 스캔 결과의 기본 키를 정렬한 후 병합합니다.
활용 시 주의점
이 기능은 일반적으로 인덱스 설계가 미흡한 상황을 보완하는 백업(Bailout) 메커니즘으로 작용합니다. 병합 과정 자체의 CPU 연산 비용과 결합 후 발생하는 반복적인 키 룩업이 발생하기 때문에, 대부분의 경우 (col1, col2) 형태의 복합 인덱스를 구성하는 것이 더 예측 가능하고 효율적입니다. 옵티마이저 힌트나 optimizer_switch 변수를 통해 병합 기능을 임의로 비활성화하여 성능을 비교 분석할 수 있습니다.
3. 인덱스 스킵 스캔 (Index Skip Scan)
MySQL 8.0.13부터 도입된 기능으로, 복합 인덱스의 왼쪽most(가장 앞쪽) 컬럼이 WHERE 절에 누락되었음에도 인덱스를 유효하게 활용할 수 있게 합니다.
동작 방식
전통적인 B+Tree 인덱스는 왼쪽most 컬럼이 조건에 포함되지 않으면 범위 스캔을 시작할 수 없어 풀 테이블 스캔으로 이어지는 경우가 많았습니다. 스킵 스캔은 옵티마이저가 해당 인덱스의 왼쪽most 컬럼에 존재하는 고유 값(Disctinct Values) 목록을 사전에 파악하고, 쿼리를 여러 개의 서브 쿼리로 논리적으로 분할하여 병렬 또는 순차적으로 인덱스를 탐색합니다.
예시 시나리오
직원 정보 테이블에 (department_id, hire_date) 복합 인덱스가 있다고 가정합니다. department_id는 사내 부서 개수(예: 5~10개)만큼만 고유한 값을 가집니다.
SELECT * FROM employees WHERE hire_date > '2023-06-01';
- MySQL 5.7 이전: 왼쪽most 컬럼(
department_id)이 없으므로 인덱스를 무시하고 풀 테이블 스캔을 실행합니다. - MySQL 8.0.13+: 옵티마이저는
department_id의 고유 값을 순회하며 다음과 같이 암시적으로 변환하여 처리합니다.
실행 계획/* 논리적 변환 예시 */ SELECT ... FROM employees WHERE department_id = 'D01' AND hire_date > '2023-06-01' UNION ALL SELECT ... FROM employees WHERE department_id = 'D02' AND hire_date > '2023-06-01' ... (반복)Extra에Using index for skip scan가 출력됩니다.
한계점
왼쪽most 컬럼의 선택도(Selectivity)가 낮을수록(고유 값이 적을수록) 효율적입니다. 반대로 사용자 ID나 타임스탬프처럼 고유 값이 수만 개 이상인 컬럼이 왼쪽most에 위치하면, 옵티마이저는 오버헤드를 우려하여 해당 방식을 채택하지 않거나 풀 테이블 스캔을 선택할 수 있습니다.
4. 적응형 해시 인덱스 (Adaptive Hash Index, AHI)
InnoDB 저장 엔진이 내부적으로 제공하는 메모리 기반 자동 캐싱 기능입니다. B+Tree 인덱스 탐색이 반복적으로 특정 경로를 따라 고빈도로 발생하면, 엔진이 이를 감지하여 버퍼 풀(Buffer Pool) 내 별도의 해시 테이블을 동적으로 생성하여 조회 속도를 단축합니다.
특성 및 관리
- 자동 운영: 별도의 정의나 유지보수 없이
innodb_adaptive_hash_index시스템 변수가ON인 한 작동합니다. 인스턴스 재시작 시 소멸되며 재충돌 시 재생성됩니다. - 등호 비교 최적화: 해시 구조의 특성상
=및IN()같은 동등 비교 연산에만 유효합니다. 범위 조회(>,<,BETWEEN)나 정렬(ORDER BY)에는 적용되지 않습니다. - 잠금(Latch) 경합: 고동시성 트랜잭션 환경에서 해시 체인 관리 시 라치 경합이 발생할 수 있습니다. MySQL 8.0에서는
innodb_adaptive_hash_index_parts를 통해 라치를 분할(기본 8 파티션)하여 동시성을 향상시켰습니다. - 메모리 할당: 버퍼 풀의 일정 비율을 점유하므로, 메모리 제약이 엄격한 환경에서는 모니터링이 필요합니다.
활용 권장사항
대부분의 OLTP 워크로드에서는 활성화 상태가 성능에 유리합니다. 다만, SHOW ENGINE INNODB STATUS 또는 성능 스키마에서 해시 테이블 관련 라치 대기 시간이 지속적으로 증가한다면, 임시로 비활성화하여 성능 변화를 벤치마킹하는 것이 좋습니다.
기술 비교 및 설계 가이드
| 기능 | 작동 계층 | 핵심 목적 | 주요 이점 | 제약사항 / 주의점 |
|---|---|---|---|---|
| 커버링 인덱스 | 쿼리 설계 / 옵티마이저 | 키 룩업 제거 | I/O 최소화, 메모리 캐시 효율 극대화 | 인덱스 크기 증가로 인한 쓰기 성능 저하 가능성 |
| 인덱스 병합 | 옵티마이저 실행 전략 | 다중 단열 인덱스 활용 | 미비한 인덱스 설계 시 풀 스캔 방지 | 병합 연산 비용 발생, 복합 인덱스가 일반적으로 더 우수함 |
| 인덱스 스킵 스캔 | 옵티마이저 실행 전략 (8.0+) | 왼쪽most 컬럼 누락 대응 | 불완전한 WHERE 조건에서도 인덱스 활용 유도 | 왼쪽most 컬럼의 고유 값 개수가 적어야 효과적 |
| 적응형 해시 인덱스 | InnoDB 저장 엔진 내부 | 빈번한 B+Tree 탐색 가속화 | 등호 조회 시 O(1) 근접 응답 속도 | 범위 조회 미지원, 고동시성 시 라치 경합 발생 가능 |