인덱스의 종류

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

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

클러스터드 인덱스(Clustered Index)는 테이블의 데이터 자체가 인덱스 순서대로 물리적으로 정렬된 인덱스입니다. 테이블당 하나만 존재합니다.

비클러스터드 인덱스(Non-Clustered Index)는 별도의 인덱스 구조에서 데이터 행의 위치를 가리키는 인덱스입니다. 테이블당 여러 개 생성 가능합니다.

클러스터드 인덱스의 구조

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

DBMS별 클러스터드 인덱스

비교	클러스터드	비클러스터드
개수	테이블당 1개	여러 개 가능
데이터 정렬	물리적으로 정렬됨	별도 구조
검색 속도	매우 빠름 (범위 검색 최적)	빠름 (포인터 추적 필요)
삽입 비용	데이터 재배치 가능성 (페이지 분할)	인덱스만 갱신

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

MySQL InnoDB:
  * PK가 자동으로 클러스터드 인덱스
  * PK가 없으면 UNIQUE NOT NULL 컬럼 사용
  * 그것도 없으면 내부 6바이트 Row ID 자동 생성
  * 세컨더리 인덱스는 PK 값을 포인터로 저장

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

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

SQL Server:
  * PK가 기본적으로 클러스터드 인덱스 (변경 가능)
  * 클러스터드 인덱스 없이 힙 테이블로도 사용 가능

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

MySQL InnoDB에서 세컨더리 인덱스는 PK 값을 포인터로 저장합니다. 따라서 PK 크기가 세컨더리 인덱스의 크기에 직접적인 영향을 줍니다.

유니크 인덱스

유니크 인덱스(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인 복합 유니크는 여러 개 가능

MySQL
  * NULL 값 여러 개 허용

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

SQL Server
  * NULL 값 1개만 허용 (두 번째 NULL은 중복 오류)

유니크 인덱스 vs 일반 인덱스 성능

복합 인덱스

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

복합 인덱스 구조

선두 컬럼 규칙 (Leftmost Prefix Rule)

복합 인덱스 컬럼 순서 결정 기준

커버링 인덱스

커버링 인덱스(Covering Index)는 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있어 테이블에 접근하지 않아도 되는 인덱스입니다.

커버링 인덱스 활용

-- 커버링 인덱스 설계
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 * 대신 필요한 컬럼만 선택하는 습관이 커버링 활용에 유리

함수 기반 인덱스 (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';

부분 인덱스 (Partial Index)

테이블의 일부 행에만 인덱스를 생성합니다. PostgreSQL과 SQL Server에서 지원합니다.

부분 인덱스 (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 성능 향상

전문 검색 인덱스 (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('korean', content));

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

비트맵 인덱스

비트맵 인덱스(Bitmap Index)는 각 키 값에 대해 비트맵(0과 1의 배열)을 저장합니다. 카디널리티가 낮은 컬럼(성별, 상태값 등)에서 효과적이며, 주로 Oracle에서 지원합니다.

해시 인덱스

해시 인덱스(Hash Index)는 해시 함수로 키 값을 변환하여 저장합니다. O(1) 성능으로 등호 검색이 매우 빠르지만 범위 검색은 불가합니다.

해시 인덱스 지원 현황

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로 먼저 테스트

인덱스 종류 종합 비교

정리

인덱스 종류	핵심 특징	제한 사항
클러스터드	데이터를 키 순서로 물리 정렬	테이블당 1개
비클러스터드	별도 구조에서 행 위치 참조	테이블 액세스 추가 비용
유니크	중복 값 방지 + 인덱스	NULL 처리 DBMS별 차이
복합	다중 컬럼 결합, 선두 컬럼 규칙	컬럼 순서가 중요
커버링	테이블 접근 없이 응답	인덱스 크기 증가
함수 기반	연산 결과에 인덱스	표현식 일치 필요
부분	조건 만족 행만 인덱싱	PostgreSQL/SQL Server
전문 검색	텍스트 내용 검색	갱신 비용 높음

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

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

목차