인덱스의 종류

인덱스에도 여러 종류가 있습니다. 용도에 따라 적합한 인덱스를 선택해야 제대로 된 성능 향상을 얻을 수 있습니다. 인덱스 종류를 이해하면 왜 이 쿼리에는 이 인덱스가 효과적인가를 판단할 수 있게 됩니다.

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

클러스터드 인덱스(Clustered Index)는 데이터 행이 클러스터 키 기준의 인덱스 구조 안에 저장되는 방식입니다. SQL Server나 InnoDB처럼 클러스터드 구조를 쓰는 DBMS에서는 테이블당 하나만 가질 수 있습니다.

비클러스터드 인덱스(Non-Clustered Index)는 별도의 인덱스 구조에서 데이터 행을 찾기 위한 위치 정보(row locator)를 저장하는 인덱스입니다. row locator는 DBMS와 테이블 구조에 따라 ROWID, TID, RID, 클러스터 키, 기본 키 컬럼처럼 달라집니다.

클러스터드 인덱스는 “파일이 항상 완벽하게 물리 정렬되어 있다”는 뜻이 아닙니다. 핵심은 행을 찾는 기본 저장 구조가 클러스터 키 순서의 B+Tree 계열이라는 점이며, 실제 저장 상태는 페이지 분할, 빈 공간, 단편화, 유지보수 정책의 영향을 받습니다.

클러스터드 인덱스의 구조 예시

아래 그림은 InnoDB 기본 키나 SQL Server clustered index처럼 리프 레벨에서 데이터 행을 만나는 구조를 단순화한 예시입니다. Oracle의 일반 힙 테이블, PostgreSQL 힙 테이블, SQL Server heap은 이 구조와 다릅니다.

비클러스터드 인덱스의 구조 예시

비클러스터드 인덱스는 별도 구조에서 row locator를 얻은 뒤, 필요하면 실제 행 저장 위치로 다시 접근합니다. InnoDB는 이 locator가 기본 키 컬럼이고, PostgreSQL은 TID, Oracle은 ROWID, SQL Server는 RID 또는 clustered key가 될 수 있습니다.

DBMS별 클러스터드 인덱스

비교	클러스터드 계열	비클러스터드 계열
개수	테이블당 1개	여러 개 가능
행 위치	리프에 데이터 행 또는 행 저장 구조	리프에 row locator
강점	범위 읽기와 PK 조회에 유리	다양한 조회 경로 추가
비용	키 변경·페이지 분할 비용이 클 수 있음	추가 lookup과 쓰기 비용

DBMS별 클러스터드 인덱스 동작

MySQL InnoDB:
  * PK가 자동으로 클러스터드 인덱스
  * PK가 없으면 UNIQUE NOT NULL 컬럼 사용
  * 그것도 없으면 내부 6바이트 Row ID 자동 생성
  * 세컨더리 인덱스 리프는 보조 키와 기본 키 컬럼을 저장
  * 보조 인덱스 조회 후 기본 키로 클러스터드 인덱스를 다시 탐색

Oracle:
  * 기본적으로 힙(Heap) 테이블 (데이터 정렬 없음)
  * 클러스터드 원하면 IOT(Index-Organized Table) 명시 생성
  * 일반 인덱스는 ROWID(물리적 주소)를 포인터로 저장

PostgreSQL:
  * 힙(Heap) 테이블이 기본
  * CLUSTER 명령으로 한 번 정렬 가능 (이후 유지 안 됨)
  * 인덱스는 ctid(물리적 주소)를 포인터로 저장

SQL Server:
  * PK는 clustered 또는 nonclustered로 지정 가능
  * 클러스터드 인덱스 없이 힙 테이블로도 사용 가능

InnoDB 세컨더리 인덱스와 PK 크기

MySQL InnoDB에서 세컨더리 인덱스 리프에는 보조 키와 함께 기본 키 컬럼이 저장됩니다. 따라서 기본 키가 길면 세컨더리 인덱스도 함께 커지고, 세컨더리 인덱스로 찾은 뒤 다시 클러스터드 인덱스를 탐색하는 비용이 생길 수 있습니다.

유니크 인덱스

유니크 인덱스(Unique Index)는 중복 값을 허용하지 않는 인덱스입니다. UNIQUE 제약을 걸면 자동으로 생성됩니다.

유니크 인덱스 생성

-- 직접 생성
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 제약 조건으로 생성 (UNIQUE 제약이 인덱스를 자동 생성)
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

-- 복합 유니크 인덱스
CREATE UNIQUE INDEX idx_enrollment 
    ON enrollments(student_id, course_id);

유니크 인덱스와 NULL

Oracle
  * NULL 값 여러 개 허용 (NULL은 중복으로 보지 않음)
  * 모든 컬럼이 NULL인 복합 유니크는 여러 개 가능
  * 일부 컬럼만 NULL인 복합 유니크는 NULL이 아닌 컬럼 조합으로 중복 여부가 갈릴 수 있음

MySQL
  * NULL 값 여러 개 허용

PostgreSQL
  * NULL 값 여러 개 허용
  * NULLS NOT DISTINCT 옵션으로 NULL도 유니크 강제 가능 (v15+)

SQL Server
  * NULL 값 1개만 허용 (두 번째 NULL은 중복 오류)
  * 여러 NULL을 허용하려면 filtered unique index로 설계 가능

UNIQUE와 NULL의 관계는 DBMS별 차이가 커서 마이그레이션 때 자주 문제를 만듭니다. “값이 있을 때만 유일해야 한다”는 요구라면 SQL Server의 filtered unique index, PostgreSQL의 partial unique index처럼 조건부 유니크 설계를 먼저 검토하는 편이 안전합니다.

복합 인덱스

복합 인덱스(Composite Index)는 두 개 이상의 컬럼으로 구성된 인덱스입니다. 컬럼 순서가 매우 중요합니다.

선두 컬럼 규칙 (Leftmost Prefix Rule)

복합 인덱스에서 범위 조건 컬럼 뒤에 오는 컬럼은 효율적인 탐색이나 전역 정렬에 활용이 제한될 수 있습니다. 예를 들어 (user_id, order_date, status)는 user_id = ? AND order_date BETWEEN ? AND ?에는 잘 맞지만, 그 뒤의 status로 전체 결과를 깔끔하게 정렬하는 데에는 한계가 있습니다. status 정렬이나 필터가 더 중요하면 (user_id, status, order_date) 같은 다른 순서와 비교해야 합니다.

DBMS에 따라 skip scan, index merge, bitmap scan 같은 예외 계획이 있을 수 있지만, 복합 인덱스 설계의 기본은 “가장 자주 실행되는 쿼리 패턴에서 어디까지 연속적으로 좁혀지는가”를 확인하는 것입니다.

커버링 인덱스

커버링 인덱스(Covering Index)는 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있어 테이블 접근을 줄일 수 있는 인덱스입니다. DBMS와 MVCC 상태에 따라 실제 테이블 접근이 완전히 사라지지 않을 수도 있습니다.

커버링 인덱스 활용

-- 커버링 인덱스 설계
CREATE INDEX idx_cover_order 
    ON orders(user_id, order_date, status, total_amount);

-- 이 인덱스로 커버링되는 쿼리들:
SELECT order_date, status FROM orders WHERE user_id = 1001;
SELECT COUNT(*) FROM orders WHERE user_id = 1001;
SELECT total_amount FROM orders 
    WHERE user_id = 1001 AND order_date > '2024-01-01';

커버링 인덱스의 장단점

장점
  * 테이블 액세스 감소 → I/O 감소
  * 자주 실행되는 좁은 조회에서 큰 효과

단점
  * 인덱스 크기 증가 → 디스크 사용량 증가
  * DML 시 인덱스 갱신 비용 증가
  * 모든 쿼리를 커버링하려면 인덱스가 너무 많아짐

실무 원칙
  * 빈번하고 성능이 중요한 쿼리에만 적용
  * SELECT * 대신 필요한 컬럼만 선택하는 습관이 커버링 활용에 유리

커버링 여부도 DBMS마다 확인 방법이 다릅니다. MySQL은 실행 계획의 Using index, PostgreSQL은 Index Only Scan과 Heap Fetches, SQL Server는 key lookup 유무를 함께 봐야 합니다. InnoDB 세컨더리 인덱스에는 기본 키 컬럼이 함께 들어가므로, 기본 키 컬럼은 별도로 넣지 않아도 커버링에 활용될 수 있습니다.

함수 기반 인덱스 (Function-Based Index)

컬럼의 값이 아닌 연산 결과에 인덱스를 생성합니다.

함수 기반 인덱스

-- Oracle: 대소문자 무시 검색
CREATE INDEX idx_upper_name 
    ON employees(UPPER(last_name));

SELECT * FROM employees WHERE UPPER(last_name) = 'KIM';
-- → 일반 인덱스는 UPPER() 때문에 사용 불가
-- → 함수 기반 인덱스는 사용 가능!

-- MySQL 8.0+: 표현식 인덱스
CREATE INDEX idx_year ON orders((YEAR(order_date)));

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- PostgreSQL: 함수 인덱스
CREATE INDEX idx_lower_email ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'user@test.com';

함수 기반 인덱스는 쿼리의 표현식이 인덱스 표현식과 맞아야 효과가 납니다. 날짜 조건처럼 YEAR(order_date) = 2024를 범위 조건으로 바꿀 수 있는 경우에는 함수 인덱스보다 order_date >= '2024-01-01' AND order_date < '2025-01-01' 형태가 더 단순하고 이식성도 좋습니다.

부분 인덱스 (Partial Index)

테이블의 일부 행에만 인덱스를 생성합니다. PostgreSQL은 Partial Index, SQL Server는 Filtered Index라는 이름으로 지원합니다.

부분 인덱스 (PostgreSQL)

-- 활성 주문에만 인덱스 (전체의 10%라면 인덱스 크기 90% 절약)
CREATE INDEX idx_active_orders 
    ON orders(order_date) 
    WHERE status = 'ACTIVE';

SELECT * FROM orders 
WHERE status = 'ACTIVE' AND order_date > '2024-01-01';
-- → 부분 인덱스 사용!

SELECT * FROM orders 
WHERE status = 'CANCELLED' AND order_date > '2024-01-01';
-- → 부분 인덱스 사용 불가! (CANCELLED는 인덱스에 없음)

부분 인덱스 활용 사례

* 소프트 삭제: WHERE is_deleted = false
* 미처리 작업: WHERE processed = false
* 최근 데이터: WHERE created_at > '2024-01-01'

효과: 인덱스 크기 감소, 해당 조건 밖 행의 DML 부담 감소
주의: 쿼리 조건이 인덱스 조건을 만족한다는 것을 옵티마이저가 증명할 수 있어야 함

MySQL InnoDB는 PostgreSQL식 WHERE 부분 인덱스를 직접 지원하지 않습니다. 비슷한 목적이 필요하면 복합 인덱스, 생성 컬럼(generated column), 애플리케이션 쿼리 조건 재설계, 파티셔닝 같은 대안을 비교해야 합니다.

전문 검색 인덱스 (Full-Text Index)

LIKE '%검색어%' 패턴은 일반 B+Tree 인덱스로 효율적인 시작점 탐색을 하기 어렵습니다. 전문 검색 인덱스는 역인덱스(Inverted Index) 구조를 사용하여 텍스트 내용을 검색합니다.

전문 검색 인덱스 생성 및 사용

-- MySQL
CREATE FULLTEXT INDEX idx_ft_content ON articles(title, content);

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('데이터베이스 설계' IN BOOLEAN MODE);

-- Oracle Text
CREATE INDEX idx_content ON articles(content)
INDEXTYPE IS CTXSYS.CONTEXT;

SELECT * FROM articles
WHERE CONTAINS(content, '데이터베이스 AND 설계') > 0;

-- PostgreSQL (언어 설정은 설치 환경에 따라 다름)
CREATE INDEX idx_gin_content ON articles
USING GIN (to_tsvector('simple', content));

SELECT * FROM articles
WHERE to_tsvector('simple', content) @@ to_tsquery('데이터베이스 & 설계');

전문 검색 인덱스는 언어별 토큰화 품질이 성능과 정확도를 크게 좌우합니다. 한국어처럼 형태소 분석이 중요한 언어는 DBMS 기본 설정만으로 충분한지, 별도 parser/tokenizer나 검색 엔진 연동이 필요한지 함께 검토해야 합니다.

비트맵 인덱스

비트맵 인덱스(Bitmap Index)는 각 키 값에 대해 비트맵(0과 1의 배열)을 저장합니다. 카디널리티가 낮은 컬럼(성별, 상태값 등)의 분석성 조건 결합에 효과적이며, 주로 Oracle의 데이터웨어하우스/분석 환경에서 사용합니다. 갱신이 많은 OLTP에서는 잠금과 유지 비용이 커질 수 있습니다.

해시 인덱스

해시 인덱스(Hash Index)는 해시 함수로 키 값을 변환하여 저장합니다. 동등 검색의 평균 비용이 낮지만, 키 순서를 보존하지 않으므로 범위 검색과 정렬에는 부적합합니다.

해시 인덱스 지원 현황

MySQL (Memory 엔진): 해시 인덱스 기본 지원
MySQL (InnoDB): Adaptive Hash Index (자동 생성, 수동 불가)
PostgreSQL: CREATE INDEX idx ON t USING HASH (col);
Oracle: 해시 클러스터로 유사 기능 제공

내림차순 인덱스와 보이지 않는 인덱스

내림차순 인덱스 (Descending Index)

-- 최신 주문부터 조회하는 쿼리가 빈번할 때
CREATE INDEX idx_order_desc ON orders(order_date DESC);

-- 복합 인덱스에서 방향 혼합
CREATE INDEX idx_mixed ON scores(class_id ASC, score DESC);
-- → class_id 오름차순 정렬 후, 같은 class 내에서 score 내림차순
-- → ORDER BY class_id ASC, score DESC 에 최적

보이지 않는 인덱스 (Invisible Index)

-- Oracle 11g+
ALTER INDEX idx_orders_date INVISIBLE;
-- → 옵티마이저가 이 인덱스를 무시 (삭제하지 않고 테스트)

ALTER INDEX idx_orders_date VISIBLE;
-- → 다시 사용 가능하게 복원

-- MySQL 8.0+
ALTER TABLE orders ALTER INDEX idx_date INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_date VISIBLE;

-- 용도: 인덱스 삭제 전 영향도 테스트
-- DROP 하면 재생성 비용이 크므로, INVISIBLE로 먼저 테스트

인덱스 종류 종합 비교

정리

인덱스 종류	핵심 특징	제한 사항
클러스터드	행이 클러스터 키 구조 안에 저장	DBMS별 지원 방식 차이
비클러스터드	별도 구조에서 row locator 참조	추가 lookup 비용
유니크	중복 값 방지 + 인덱스	NULL 처리 DBMS별 차이
복합	다중 컬럼 결합, 선두 컬럼 규칙	컬럼 순서가 중요
커버링	필요한 컬럼을 인덱스에 포함	인덱스 크기 증가
함수 기반	연산 결과에 인덱스	표현식 일치 필요
부분	조건 만족 행만 인덱싱	조건 증명과 DBMS 지원
전문 검색	텍스트 토큰 기반 검색	언어 설정과 갱신 비용

인덱스 선택의 핵심은 쿼리 패턴에 맞추는 것입니다. 자주 실행되는 쿼리의 WHERE, JOIN, ORDER BY 조건을 분석하고, 그에 맞는 인덱스 종류와 컬럼 구성을 결정해야 합니다. 과도한 인덱스는 DML 성능을 저하시키므로, 읽기와 쓰기 비율을 함께 고려하여 적정 수준의 인덱스를 유지하는 것이 중요합니다.

다음 절에서는 이러한 인덱스를 실전에서 생성하고 분석하는 방법을 다루겠습니다.