식별자와 비식별자 인덱싱, Index 스캔과 Index Only 스캔¶
데이터베이스에서 인덱스는 데이터 검색 성능을 향상시키는 핵심 요소입니다. 이 문서에서는 식별자 인덱싱과 비식별자 인덱싱의 차이점, 그리고 Index 스캔과 Index Only 스캔의 특징과 활용 방법에 대해 설명합니다.
식별자 인덱싱 vs 비식별자 인덱싱¶
식별자 인덱싱 (Identifier Indexing)¶
식별자 인덱싱은 테이블의 기본 키(Primary Key) 또는 고유 식별자를 기반으로 생성된 인덱스를 의미합니다.
특징¶
- 테이블의 기본 키(PK) 또는 고유 식별자에 생성됨
- 데이터의 고유성(Uniqueness)을 보장
- 대부분의 DBMS에서 기본 키에 자동으로 생성
- 클러스터형 인덱스(Clustered Index)로 구현되는 경우가 많음
적합한 상황¶
- 레코드의 고유 식별이 필요한 경우
- 관계형 데이터베이스에서 테이블 간 관계 설정 시
- 특정 레코드를 직접 접근해야 할 때
장점¶
- 레코드 검색 속도가 매우 빠름
- 데이터 무결성 보장에 도움
- 조인 연산 최적화에 유리
단점¶
- 인덱스 유지 관리에 추가 비용 발생
- 데이터 삽입/수정/삭제 시 오버헤드 발생
- 인덱스 크기가 데이터베이스 공간을 차지
비식별자 인덱싱 (Non-identifier Indexing)¶
비식별자 인덱싱은 기본 키가 아닌 다른 컬럼이나 컬럼 조합에 생성된 인덱스를 의미합니다.
특징¶
- 기본 키가 아닌 컬럼에 생성됨
- 보조 인덱스(Secondary Index) 또는 비클러스터형 인덱스(Non-clustered Index)로 구현되는 경우가 많음
- 중복 값을 허용할 수 있음
- 검색 조건으로 자주 사용되는 컬럼에 생성
적합한 상황¶
- 특정 컬럼을 기준으로 자주 검색하는 경우
- 정렬이나 그룹화가 필요한 컬럼
- 범위 검색이 필요한 경우
- 복합 조건 검색이 필요한 경우
장점¶
- 다양한 검색 패턴 최적화 가능
- 여러 검색 조건에 맞는 인덱스 구성 가능
- 쿼리 성능 향상에 유연하게 대응
단점¶
- 너무 많은 인덱스는 성능 저하 초래
- 데이터 변경 작업 시 모든 관련 인덱스 업데이트 필요
- 저장 공간 추가 소비
Index 스캔 vs Index Only 스캔¶
Index 스캔 (Index Scan)¶
Index 스캔은 인덱스를 사용하여 데이터를 검색한 후, 필요한 데이터를 테이블에서 가져오는 방식입니다.
특징¶
- 인덱스를 통해 레코드의 위치를 찾음
- 인덱스에서 찾은 위치 정보를 사용하여 테이블에서 실제 데이터를 읽음
- 두 단계 프로세스: 인덱스 검색 → 테이블 접근
적합한 상황¶
- 인덱스 컬럼으로 검색 조건을 지정할 때
- 검색 결과가 전체 테이블의 일부분일 때
- 테이블에서 여러 컬럼을 조회해야 할 때
장점¶
- 테이블 전체 스캔보다 효율적
- 정렬된 결과를 얻을 수 있음
- 다양한 검색 조건에 활용 가능
단점¶
- 테이블 접근(Random I/O)으로 인한 추가 비용 발생
- 많은 행을 검색할 때는 비효율적일 수 있음
- 인덱스와 테이블 간 왕복 작업 필요
Index Only 스캔 (Index Only Scan / Covering Index Scan)¶
Index Only 스캔은 필요한 모든 데이터가 인덱스 자체에 포함되어 있어 테이블에 접근하지 않고 인덱스만으로 쿼리를 처리하는 방식입니다.
특징¶
- 쿼리에 필요한 모든 컬럼이 인덱스에 포함됨
- 테이블 접근 없이 인덱스만으로 쿼리 처리
- 단일 단계 프로세스: 인덱스 검색만 수행
적합한 상황¶
- 쿼리에서 요청하는 모든 컬럼이 인덱스에 포함된 경우
- 집계 함수(COUNT, MAX, MIN 등)를 사용하는 경우
- 높은 성능이 요구되는 쿼리
장점¶
- 테이블 접근이 없어 매우 빠름
- I/O 비용 대폭 감소
- 캐시 효율성 향상
단점¶
- 인덱스에 많은 컬럼을 포함시키면 인덱스 크기 증가
- 인덱스 유지 관리 비용 증가
- 모든 쿼리에 적용할 수 없음
성능 비교¶
| 스캔 유형 | 테이블 접근 | 성능 | 적합한 상황 |
|---|---|---|---|
| Index 스캔 | 필요 | 중간 | 일부 데이터 검색, 여러 컬럼 조회 |
| Index Only 스캔 | 불필요 | 매우 빠름 | 인덱스에 포함된 컬럼만 조회 |
인덱스 설계 전략¶
식별자/비식별자 인덱싱 전략¶
- 기본 키 선택 신중하게 하기
- 자연 키(Natural Key)와 대리 키(Surrogate Key) 중 적절한 것 선택
-
변경 가능성이 낮은 컬럼을 기본 키로 선택
-
자주 사용되는 검색 패턴 분석
- 자주 사용되는 WHERE 절 조건에 비식별자 인덱스 생성
-
조인 조건으로 사용되는 외래 키에 인덱스 생성
-
복합 인덱스 설계
- 함께 사용되는 컬럼들을 하나의 복합 인덱스로 생성
- 컬럼 순서는 선택도(Selectivity)가 높은 컬럼을 앞에 배치
Index Only 스캔을 위한 전략¶
- 커버링 인덱스(Covering Index) 생성
- 자주 사용되는 쿼리의 SELECT 절에 있는 컬럼들을 인덱스에 포함
-
인덱스에 포함된(INCLUDE) 컬럼 활용 (DBMS 지원 시)
-
인덱스 크기와 성능 간 균형 유지
- 너무 많은 컬럼을 포함시키면 인덱스 크기 증가
-
핵심 쿼리에 대해서만 커버링 인덱스 설계
-
쿼리 재작성
- Index Only 스캔을 활용할 수 있도록 쿼리 최적화
- 필요한 컬럼만 선택적으로 조회
실제 사용 예시¶
식별자/비식별자 인덱싱 예시¶
-- 식별자 인덱싱 (기본 키)
CREATE TABLE users (
user_id INT PRIMARY KEY, -- 자동으로 식별자 인덱스 생성
username VARCHAR(50),
email VARCHAR(100),
created_at TIMESTAMP
);
-- 비식별자 인덱싱 (보조 인덱스)
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);
Index 스캔과 Index Only 스캔 예시¶
-- 테이블 생성
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2),
status VARCHAR(20)
);
-- 비식별자 인덱스 생성
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);
-- 커버링 인덱스 생성 (Index Only 스캔 가능)
CREATE INDEX idx_customer_date_amount ON orders(customer_id, order_date, total_amount);
-- Index 스캔 발생 쿼리
SELECT order_id, customer_id, order_date, total_amount, status
FROM orders
WHERE customer_id = 1000 AND order_date > '2025-01-01';
-- Index Only 스캔 발생 쿼리
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 1000 AND order_date > '2025-01-01';
결론¶
식별자 인덱싱과 비식별자 인덱싱은 각각 다른 목적으로 사용되며, 데이터베이스 설계 시 적절히 조합하여 사용해야 합니다. 식별자 인덱싱은 레코드의 고유성과 빠른 접근을 보장하고, 비식별자 인덱싱은 다양한 검색 패턴을 최적화합니다.
Index 스캔과 Index Only 스캔은 데이터 접근 방식에 차이가 있으며, 가능한 경우 Index Only 스캔을 활용하면 성능을 크게 향상시킬 수 있습니다. 효율적인 인덱스 설계를 통해 두 스캔 방식의 장점을 최대한 활용하는 것이 중요합니다.
데이터베이스 성능 최적화를 위해서는 인덱스의 종류와 스캔 방식을 이해하고, 워크로드 특성에 맞게 인덱스를 설계하며, 쿼리 실행 계획을 분석하는 것이 필수적입니다.