콘텐츠로 이동

인덱스 구조와 종류

인덱스 파워링크

바로가기 볼 내용
개요 인덱스 전체 지도, 상황별 바로가기
구조와 종류 B+Tree, 클러스터/보조 인덱스, 물리 동작
B-Tree VS B+Tree B-Tree와 B+Tree 구조 차이, DB가 B+Tree를 쓰는 이유
설계와 사용법 커버링 인덱스, 카디널리티, CREATE/ALTER/DROP
쿼리 패턴 연산자별 동작, 복합 인덱스, ESR 규칙
운영과 튜닝 고급 기법, 힌트, 통계, 모니터링, 베스트 프랙티스

인덱스: 테이블의 특정 컬럼에 대해 빠른 검색을 가능하게 하는 자료구조. 책의 "색인"처럼 원하는 데이터를 전체 테이블을 읽지 않고도 빠르게 찾을 수 있게 한다.

왜 쓰는지

인덱스 없이 WHERE id = 100 같은 쿼리를 실행하면 DBMS는 테이블의 모든 행을 처음부터 끝까지 스캔(Full Table Scan) 한다. 행이 100만 건이면 100만 번 비교한다.

인덱스를 만들면 B-Tree 계열 구조에 정렬된 값이 저장되어 O(log n) 으로 찾을 수 있다.

상황 인덱스 없음 인덱스 있음
1,000만 건에서 특정 회원 조회 ~10,000,000 행 비교 ~23 번 비교 (log₂ 10M)
복잡한 JOIN + WHERE 매우 느림 대폭 개선

내부 구조 — B-Tree 계열

대부분의 RDBMS는 B-Tree 계열을 기본 인덱스 구조로 사용한다. 실무에서는 이를 통칭해서 B-Tree 인덱스라고 부르지만, MySQL InnoDB의 실제 일반 인덱스 구조는 B+Tree에 가깝다.

        [50]
       /    \
   [25]      [75]
   /  \      /  \
 [10][30] [60][90]
  • 모든 리프 노드까지의 깊이가 같다. (Balanced)
  • 범위 검색(BETWEEN, >, <)에도 효율적이다.
  • 한 노드에 여러 키를 저장해 트리 높이를 낮춘다.

팁: MySQL InnoDB는 내부 노드에는 길 안내용 키를, 리프 노드에는 실제 데이터 또는 PK를 저장하는 B+Tree 구조를 사용한다. 자세한 구조 차이는 B-Tree VS B+Tree에서 따로 정리한다.

다른 인덱스 구조

B-Tree 계열이 기본이지만, 용도에 따라 다른 구조의 인덱스도 사용할 수 있다.

Hash Index

Hash Index: 키를 해시 함수로 변환하여 O(1) 에 접근하는 인덱스. 등호(=) 비교에 최적화되어 있다.

  • 범위 검색 불가: BETWEEN, >, < 사용 불가
  • 정렬 불가: ORDER BY에 활용할 수 없음
  • 부분 일치 불가: LIKE 'abc%' 사용 불가
  • MySQL InnoDB는 Hash Index를 직접 생성할 수 없다. 내부적으로 Adaptive Hash Index를 자동 생성하여 자주 조회되는 B-Tree 리프를 캐싱한다.

Full-Text Index

Full-Text Index: 텍스트 내용을 단어 단위로 분해하여 역인덱스(Inverted Index) 로 저장하는 인덱스. 자연어 검색에 사용한다.

LIKE '%keyword%'는 앞쪽 와일드카드 때문에 항상 Full Scan이지만, Full-Text Index를 사용하면 대용량 텍스트에서도 빠르게 검색할 수 있다.

-- Full-Text 인덱스 생성
CREATE FULLTEXT INDEX ft_article_content ON article (title, content);

-- 검색
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('인덱스 최적화');

팁: MySQL InnoDB는 5.6부터 Full-Text Index를 지원한다. 한국어·중국어·일본어(CJK)는 공백 기반 토큰화가 불가능하므로 ngram parser를 설정해야 한다.

CREATE FULLTEXT INDEX ft_article_content ON article (content) WITH PARSER ngram;

인덱스 구조 비교

B-Tree Hash Full-Text
등호 검색 O(log n) O(1) -
범위 검색 가능 불가 -
정렬 가능 불가 -
텍스트 검색 LIKE 'abc%'만 불가 자연어 검색
InnoDB 지원 기본 자동 생성만 5.6+

클러스터 인덱스 (Clustered Index)

클러스터 인덱스: 테이블의 실제 데이터 행을 인덱스 키 순서대로 물리적으로 정렬하여 저장하는 인덱스. InnoDB에서는 PK가 자동으로 클러스터 인덱스가 되며, 리프 노드에 실제 데이터 행 전체가 저장된다.

클러스터 인덱스 (PK = id) B+Tree 리프 노드
┌──────────────────────────────────────┐
│ id=1 | name='Alice' | email='a@...'  │  ← 실제 데이터 행 전체
│ id=2 | name='Bob'   | email='b@...'  │
│ id=5 | name='Carol' | email='c@...'  │
└──────────────────────────────────────┘

테이블 자체가 B+Tree 구조로 저장되는 셈이다. PK로 조회 시 리프 노드에서 한 번에 전체 데이터를 얻을 수 있다.

PK가 없을 때 — InnoDB의 선택 순서

우선순위 조건 동작
1순위 PK 선언 PK를 클러스터 인덱스로 사용
2순위 PK 없음 + NOT NULL UNIQUE 컬럼 존재 첫 번째 NOT NULL UNIQUE 컬럼 사용
3순위 위 둘 다 없음 내부적으로 6-byte Row ID 자동 생성 (숨김 컬럼)

주의: PK를 지정하지 않으면 InnoDB가 숨겨진 Row ID를 클러스터 인덱스로 사용한다. 외부에서 접근할 수 없어 PK 조회 최적화가 불가능하다. 항상 PK를 명시적으로 선언하자.

PK 설계 원칙

Bad — UUID (무작위 PK)

CREATE TABLE member (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100)
);

UUID는 순서가 없어 삽입마다 B+Tree 중간에 끼워 넣어야 한다 → 페이지 분할 빈발 → 단편화 심화 → 쓰기 성능 저하

Good — AUTO_INCREMENT (순차 PK)

CREATE TABLE member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

순차 증가 PK는 항상 리프 노드의 가장 오른쪽에 추가되어 페이지 분할이 최소화된다.

보조 인덱스 (Secondary Index)

보조 인덱스 (Secondary Index / Non-Clustered Index): PK 이외의 컬럼에 생성하는 인덱스. InnoDB에서는 리프 노드에 실제 데이터 대신 PK 값이 저장된다.

보조 인덱스 (email 컬럼) B+Tree 리프 노드
┌──────────────────────────────────┐
│ email='alice@test.com' | id=1    │  ← PK 값(포인터)만 저장
│ email='bob@test.com'   | id=2    │
└──────────────────────────────────┘
         │ ② PK로 클러스터 인덱스 재탐색 (Double Lookup)
         ▼
┌──────────────────────────────────────┐
│ id=1 | name='Alice' | email='a@...'  │  ← 실제 데이터
└──────────────────────────────────────┘

Double Lookup — 왜 2번 탐색하나?

InnoDB가 보조 인덱스 리프에 실제 데이터 주소 대신 PK를 저장하는 이유:

  • 클러스터 인덱스에서 삽입/삭제가 일어나면 행의 물리적 위치가 바뀔 수 있다
  • 물리 주소를 저장했다면 위치가 바뀔 때마다 모든 보조 인덱스도 갱신해야 함 → 비용 급증
  • PK를 저장하면 클러스터 인덱스만 관리하면 됨 → 유지보수 비용 절감

해결책: 커버링 인덱스로 Double Lookup 제거

클러스터형 vs 보조 인덱스 비교

클러스터 인덱스 보조 인덱스
리프 노드 실제 데이터 행 전체 PK 값만 저장
개수 테이블당 1개 여러 개 가능
조회 속도 빠름 (1번 탐색) 상대적으로 느림 (Double Lookup)
삽입 비용 PK 순서 유지 → 높음 상대적으로 낮음
대표 예시 PRIMARY KEY CREATE INDEX

실제 물리적인 인덱스 동작 원리

InnoDB 페이지 (Page)

InnoDB는 데이터를 페이지(Page) 단위로 관리한다. 기본 페이지 크기는 16KB.

디스크
  └── 테이블스페이스 (.ibd 파일)
        └── 세그먼트
              └── 익스텐트 (64 페이지 = 1MB)
                    └── 페이지 (16KB)  ← B+Tree 노드 하나 = 페이지 하나

하나의 페이지에 여러 행이 담기며, B+Tree의 루트·내부·리프 노드 각각이 페이지 하나에 해당한다.

INSERT 시 동작

① 삽입 위치 탐색
   → B+Tree 루트부터 내려가 삽입할 리프 페이지 찾음

② 페이지에 빈 공간이 있으면 → 바로 삽입

③ 페이지가 꽉 찼으면 → 페이지 분할 (Page Split)
   → 현재 페이지 절반을 새 페이지로 이동
   → 부모 노드에 새 페이지의 키 등록
   → 부모도 꽉 찼으면 재귀적으로 분할 (루트까지)

주의: 페이지 분할은 비용이 크다. UUID처럼 무작위 PK를 쓰면 항상 중간 삽입이 발생해 분할이 잦아진다. AUTO_INCREMENT를 쓰면 항상 오른쪽 끝에 추가되어 분할이 최소화된다.

DELETE 시 동작

① 리프 노드에서 레코드 삭제 마킹 (즉시 물리 삭제 아님)
② 페이지 내 레코드가 너무 적어지면 → 인접 페이지와 병합 (Page Merge)
③ 삭제된 슬롯은 같은 크기의 새 레코드가 들어오면 재활용

팁: 대량 삭제 후 단편화가 심해졌다면 OPTIMIZE TABLE member;로 페이지를 재구성할 수 있다. 단, 실행 중 테이블 잠금이 발생할 수 있으므로 트래픽이 적은 시간대에 수행한다.

UPDATE 시 동작

PK 값 변경 여부 동작
PK 값 변경 없음 같은 위치에 덮어쓰기 (크기가 커지면 다른 페이지로 이동 가능)
PK 값 변경 있음 기존 레코드 DELETE + 새 위치에 INSERT (이동)

보조 인덱스는 PK가 바뀌지 않으면 같이 변경할 필요가 없으므로, PK는 불변(Immutable)으로 설계하는 것이 성능에 유리하다.

인덱스 자동 생성

MySQL InnoDB에서는 명시적으로 CREATE INDEX를 하지 않아도 특정 제약 조건 선언 시 자동으로 인덱스가 생성된다.

제약 조건 자동 생성 인덱스 종류 대상
PRIMARY KEY 클러스터 인덱스 PK 컬럼
UNIQUE UNIQUE 인덱스 해당 컬럼
FOREIGN KEY 일반 인덱스 자식 테이블의 FK 컬럼 
CREATE TABLE orders (
    id        BIGINT AUTO_INCREMENT PRIMARY KEY,  -- ① 클러스터 인덱스 자동 생성
    member_id BIGINT NOT NULL,
    amount    INT,
    order_code VARCHAR(50),
    UNIQUE KEY uq_order_code (order_code),        -- ② UNIQUE 인덱스 자동 생성
    FOREIGN KEY (member_id) REFERENCES member(id) -- ③ FK 인덱스 자동 생성 (MySQL)
);

-- 자동 생성 인덱스 확인
SHOW INDEX FROM orders;

-- Key_name: PRIMARY       ← ① 클러스터 인덱스
-- Key_name: uq_order_code ← ② UNIQUE 인덱스
-- Key_name: member_id     ← ③ FK 자동 생성 인덱스

MySQL FK 인덱스 자동 생성: MySQL(InnoDB)은 FK 선언 시 자식 테이블의 FK 컬럼에 인덱스가 없으면 자동으로 인덱스를 생성한다. FK 참조 무결성 검사와 ON DELETE CASCADE 등의 연산을 효율적으로 수행하기 위해서다.

PostgreSQL은 FK 인덱스를 자동 생성하지 않는다. 수동으로 CREATE INDEX를 해줘야 한다.

주의: FK 인덱스는 자식 테이블에만 자동 생성된다. 부모 테이블의 참조 컬럼(보통 PK)은 이미 PK 인덱스가 존재하므로 추가 생성하지 않는다.

다음: 인덱스 설계와 사용법