contents

데이터베이스 인덱스 가이드

1. 인덱스란?

데이터베이스 인덱스란 테이블의 특정 컬럼(들)에 대해 별도의 자료구조를 만들어, 데이터를 빠르게 검색·조회할 수 있게 해주는 도구입니다.
(책의 찾아보기/색인처럼, 원하는 주제를 바로 찾을 수 있는 목차 역할)

인덱스에는 주로 **검색 키(컬럼 값)**와 실제 테이블의 **데이터 위치(포인터)**가 저장됩니다.
인덱스가 없으면 쿼리 시 전체 테이블(모든 행)을 일일이 탐색(Full Table Scan)해야 하며, 대용량 테이블에서는 매우 느려집니다.

2. 인덱스 동작 원리

인덱스를 생성하면, DBMS가 테이블을 스캔해 인덱스 자료구조(보통 키+포인터) 작성
쿼리 실행 시 (WHERE user_id=123 등)
- 해당 컬럼에 인덱스가 있는지 먼저 확인
- 인덱스에서 키 값을 탐색하여 포인터를 얻고,
- 해당 포인터로 테이블의 정확한 행을 즉시 조회, 나머지 데이터는 건너뜀
범위 쿼리(BETWEEN, ORDER BY 등)에도 인덱스가 있으면 탐색/정렬이 매우 빨라짐

성능 예시
수백만 건 테이블에서 전체 검색이 10초 걸리던 쿼리가, 적절한 인덱스 사용 시 수밀리초(ms) 단위로 줄어듭니다.

3. 주요 인덱스 종류

1) 구조별

B-Tree 인덱스: 거의 모든 DB의 기본. 일반 조회, 범위검색 모두 효과적
Hash 인덱스: 해시 함수 기반, 완전 일치 검색에 매우 빠름. 범위엔 비효율
비트맵 인덱스: 비트맵으로 값 관리, 값 종류가 적은(예: 성별, Y/N 등) 컬럼에 유리

2) 목적별

Primary(기본) 인덱스: 기본키에 자동 생성, 반드시 유일
Unique 인덱스: 기타 컬럼에 유일성을 부여
Clustered 인덱스: 테이블의 실제 행(저장 순서)까지 결정. 테이블마다 하나만
Non-clustered 인덱스: 데이터 저장 순서와 무관, 흔히 여러 개 설정 가능

3) 기타

Composite/복합 인덱스: 여러 컬럼 결합(성, 이름 등) → 멀티 컬럼 쿼리
Covering 인덱스: 쿼리에 필요한 모든 컬럼을 포함 → 테이블 값까지 안 읽고 인덱스에서 바로 결과 반환
Partial/Filtered 인덱스: 일부 행(조건부)에만 인덱싱
Function-based 인덱스: 연산 결과(예: lower(email)) 기준으로 인덱싱
Full-text/Spatial 인덱스: 텍스트/공간 좌표 전문 검색 최적화

4. Dense vs Sparse Index

Dense(조밀) 인덱스: 모든 키(모든 행)에 인덱스 엔트리가 있음
Sparse(희소) 인덱스: 일부 키(블록 단위 첫 row 등)에만 엔트리. 더 적은 공간, 블록 탐색 후 찾아감

5. B-Tree와 Hash 인덱스의 차이

구분	B-Tree 인덱스	Hash 인덱스
검색 유형	범위+일치 검색 모두 빠름	완전 일치 검색만 빠름
구조	트리(균형트리)	해시 테이블
용도	PK, 범위, 정렬, 범용	캐시, ID/키 빠른 탐색
주사용 DB	MySQL/InnoDB, Postgres 등	MySQL MEMORY, 일부 인메모리 DB

6. 인덱스의 이점

대량 데이터에서 읽기/조회 성능 급격히 개선
정렬, 그룹화, 여러 조건(WHERE, ORDER BY) 쿼리에 효과
유일성/중복 체크에도 필수(Unique 인덱스)
복합 검색(AND, OR), 멀티 컬럼 조건도 최적화 처리

7. 인덱스의 단점·부담

쓰기 성능 저하: insert/update/delete 시 인덱스 자료구조 실시간 갱신 부담
디스크사용: 별도 자료구조로서 저장공간 추가 소모(테이블 만큼 or 그 이상)
과다 인덱스: 지나치게 많으면 저장공간 낭비/쓰기 느림/쿼리최적화 혼란
유지보수: 대량 INSERT/DELETE 시 인덱스의 재구성/재구축 필요

8. 실전 예제

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    gender CHAR(1),
    created_at DATETIME
);

user_id는 Primary Key라 자동 인덱스(B-Tree)
gender는 값 수가 적으므로 비트맵 인덱스 유리

복합 인덱스 예시

CREATE INDEX idx_name_age ON Users(name, age);

아래 쿼리 속도 대폭 향상

SELECT * FROM Users WHERE name = 'Jane' AND age = 25;
SELECT * FROM Users WHERE name = 'Jane' ORDER BY age;

9. 책 색인과의 비유

책의 목차/색인처럼:
색인이 없으면 한 장씩 다 넘겨야 함
색인이 있으면 "파리/paris: 77, 155, 219쪽" → 바로 찾아감

10. 언제, 어떻게 인덱스를 써야 할까?

WHERE, ORDER BY, JOIN 조건에 자주 쓰는 컬럼에 인덱스 생성
너무 많은 인덱스는 오히려 비효율(쓰기 느려지고 공간 낭비)
자주 함께 조회하는 컬럼에는 복합 인덱스(순서 중요)
값 종류 적은 컬럼엔 비트맵 인덱스 적극 검토
주기적으로 EXPLAIN/실행계획 분석으로 쓸모없는 인덱스는 정리

11. 고급 인덱스 전략

부분 인덱스: 조건부(예: 활성화 상태만)
커버링 인덱스: 쿼리의 모든 컬럼 포함, 테이블 액세스 자체를 최소화
함수 기반 인덱스: 연산 결과에도 인덱싱(예: LOWER(email))
Full-Text/Spatial: 긴 텍스트, 위치좌표 검색 등 특화 인덱스

12. 결론

훌륭한 인덱스 설계는 데이터베이스 성능의 핵심입니다. 하지만 인덱스는 읽기만 빠르게 해주는 대신,

쓰기 오버헤드,
저장공간,
유지보수
등의 부담도 반드시 고려해야 합니다.

실제 쿼리/워크로드/EXPLAIN을 꼭 확인하며, 쿼리 성능에 맞춰 신중하게 인덱스를 설계하세요!

데이터베이스 인덱스 성능 튜닝 & 텍스트 검색 최적화

1. 인덱스 튜닝의 기본 원칙

인덱스는 조회(SELECT, JOIN, ORDER BY) 성능을 비약적으로 향상시키지만, 쓰기(INSERT/UPDATE/DELETE) 비용·디스크 공간이 늘어납니다.
자주 필터·정렬·조인 등에 쓰이는 컬럼에만 인덱스를 부여해야 효과적입니다.
과도한 인덱스는 오히려 느려지거나, 관리/공간 낭비의 원인이 됩니다.

인덱스 선정 최적화

조회가 잦은 큰 테이블의 WHERE, ORDER BY, JOIN 컬럼에 인덱스 부여
Cardinality(고유값 개수)가 높은 컬럼에 일반 인덱스(B-Tree), 값이 적은 컬럼은 비트맵 인덱스 활용
복합 쿼리(동시 다중 컬럼 검색)는 접근 패턴 순서에 맞는 복합 인덱스 생성

2. 인덱스 구조별 성능 특성

B-Tree 인덱스

범위/정렬/LIKE 등 범용 쿼리에 강함
대부분 PK, 일반 인덱스 기본 구조

Hash 인덱스

완전 일치 검색에 빠르지만, 범위/정렬에는 부적합

비트맵/전문(full-text) 인덱스

값 종류가 적은 경우, 대용량 텍스트(검색엔진/블로그) 등에 특화

3. 전문(Full-Text) 검색에서의 인덱스

전문 검색 인덱스는 “역색인(inverted index)” 구조를 사용
→ 각 단어(토큰)마다 등장 문서 및 위치를 기록
→ O(n) → O(log n) ~ O(1) 수준으로 대폭 성능 개선
토큰화, 불용어(stopword) 처리, 어간 분석(stemming) 등이 내장돼 검색 정확성·속도 모두 향상
하지만 잦은 쓰기/수정 환경에서는 인덱스 관리 오버헤드가 커질 수 있음 → 실시간 메시징 등에서는 부분 인덱싱, 비동기 인덱싱도 고려

4. 인덱스 조정의 성능 영향

인덱스가 없는 경우: 테이블 전체를 순회(Full Table Scan) → 대량 데이터에서는 심각하게 느려짐
적절한 인덱스 추가: 쿼리 실행 시간이 초 단위→수밀리 초로 극적 감소
- 예시: Orders 테이블에서 특정 고객 + 기간 조회
```
SELECT * FROM Orders WHERE CustomerID=12345 AND OrderDate BETWEEN '2023-01-01' AND '2023-12-31';
CREATE INDEX idx_cust_date ON Orders(CustomerID, OrderDate);
```
- 위 인덱스 추가 전에는 모든 주문 데이터를 훑어야 하지만, 이후엔 인덱스만 스캔해서 원하는 행만 바로 조회

5. EXPLAIN: 실제 인덱스 사용 확인 및 진단

EXPLAIN (MySQL/Oracle/PostgreSQL 등 공통)
- 쿼리 실행계획 출력, 인덱스 사용 여부/순서/조인 방식까지 모두 확인 가능
```
EXPLAIN SELECT * FROM Orders WHERE CustomerID=12345;
```
- 결과 해석: type이 ALL이면 풀스캔, ref, range, index면 인덱스 사용!

EXPLAIN 활용 예

예상보다 느린 쿼리 → EXPLAIN으로 인덱스 사용 안 됨 확인
WHERE, ORDER BY조건이 인덱스에 맞도록 쿼리/인덱스 구조 수정
Covering Index(모든 필요한 컬럼이 인덱스에 포함된 경우) 활용 → EXPLAIN에서 using index 표시

고급:

실행계획 분석(행수, cost), 비효율 인덱스(스캔 비율 높음) 정리, 인덱스 fragmentation 관리

6. 인덱스 관리, 모니터링, 실전 조언

정기적으로 사용/미사용 인덱스 점검 (DBMS의 사용 통계, slow query log 등 활용)
높은 쓰기 빈도의 테이블은 인덱스를 최소화
텍스트 인덱스의 경우, 토큰 크기 및 불용어/스테밍 옵션 조정(mysqld의 full-text 파라미터 등)
조인·복합조건 쿼리에는 순서, 커버링, 부분 인덱스 전략을 고려

7. 결론

인덱스는 데이터베이스 성능의 핵심이며, 불필요한 인덱스나 잘못된 구조는 오히려 성능을 떨어뜨릴 수 있습니다.
특히, EXPLAIN 같은 실행계획 도구를 적극적으로 활용하여, 내 쿼리가 실제로 인덱스를 제대로 활용하는지, 불필요한 풀스캔이 발생하는지 반드시 체크하세요!
텍스트/전문 검색에서는 적절한 인덱스 구조와 튜닝(불용어·어간분석 등) 옵션까지 조합할 때 진짜 효율이 납니다.

여러 인덱스와 데이터베이스 인덱스 자료구조의 관계

1. 단일 컬럼 인덱스 (Single-Column Index)

각 컬럼(A, B 등)에 인덱스를 생성하면, DB 엔진은 해당 컬럼 값에 대해 별도의 B-Tree(또는 Hash) 자료구조를 만듭니다.
각 인덱스 구조는
- (정렬된) 값 → 실제 테이블 행의 위치(포인터)를 포함합니다.

예시

[ 인덱스 A (B-Tree) ]
   10 → Row#3
   15 → Row#7
   20 → Row#2

여러 개의 인덱스가 각각 따로 관리됨.

2. 다중 단일 인덱스와 인덱스 병합

한 테이블에 (예: A, B) 여러 단일 인덱스가 있을 때,
WHERE 조건 등에 여러 컬럼이 쓰이면,
DB는 각 인덱스별로 검색후 결과 행(RowID) 집합을 병합(인터섹션, 머지) 할 수 있습니다.

예시

WHERE A=10 AND B=5
- 인덱스 A → Row#2, Row#8
- 인덱스 B → Row#2, Row#5
- → 두 집합 인터섹션: Row#2만 최종 매칭

3. 복합 인덱스 (Composite/Multi-Column Index)

두 개 이상 컬럼(예: (A,B))을 동시에 인덱스 키로 삼음
B-Tree의 정렬 기준이 (A, B) 순서대로 됨
WHERE A=10 AND B=5 쿼리를 한 번의 인덱스 스캔으로 바로 결과 찾음 (병합X)
다수 컬럼 필터링/정렬이 자주 함께 쓰이면 복합 인덱스가 훨씬 빠름

예시

[ 복합 인덱스 (A,B) ]
  (10,2)  → Row#2
  (10,4)  → Row#8
  (15,1)  → Row#5

4. 포인터 구조

모든 인덱스 엔트리는 실제 테이블 행(rowid)을 가리키는 포인터를 항상 포함합니다.
인덱스만으로 값 찾기 → 포인터로 테이블 행을 직접 접근 (Random access)
Covering Index라면 쿼리의 모든 컬럼이 인덱스에 포함되어 테이블 접근 자체 없이 인덱스만으로 값을 반환할 수도 있음

5. 쿼리 실행 흐름 예시

테이블: ID, Name, Age, City 컬럼
인덱스:
- 단일 인덱스: Name (B-Tree), City (B-Tree)
- 복합 인덱스: (City, Name) (B-Tree)
쿼리: SELECT * FROM T WHERE Name='Kim' AND City='Seoul'
- (1) 단일 인덱스 → 각각 Row 집합 추출, 병합 /
- (2) 복합 인덱스 → (Seoul, Kim) 키값 한 번에 바로 탐색

6. 실무 조언

복합 인덱스는 WHERE/ORDER BY에 자주 함께 등장하는 컬럼 조합, 순서까지 맞춰야 효과
단일 인덱스 병합(intersection)은 RDB 별 지원 수준 차이, 효율성도 복합 인덱스에 못 미침
쿼리 플랜(EXPLAIN)으로 실제 어떤 인덱스가 쓰이는지 확인 필요
복합 인덱스 (A,B)는 A=… 또는 A=… and B=…에서만 빠름 (B=… 단독에는 비효율적)

7. 요약 설명

테이블 ↔ [Index A(B-Tree)] -컬럼 A-
↘ [Index B(B-Tree)] -컬럼 B-
↘ [복합 인덱스 (A,B) (B-Tree)] - (컬럼A,컬럼B 함께 정렬)
질의 조건별
단일 인덱스는 병합해서 결과,
복합 인덱스는 한 번의 조회로 바로 결과
모든 인덱스 엔트리에는 실제 테이블 행(포인터)이 연결되어 있음

정리:
여러 인덱스가 있는 경우 DB는 개별 B-Tree(혹은 해시) 인덱스를 따로 관리하며, 쿼리 조건에 따라 각각 스캔 후 결과를 병합하거나, 복합 인덱스를 통해 한 번에 원하는 행을 빠르게 찾습니다.
포인터 구조와 인덱스 조합 설계가 Query 성능에서 매우 중요하며, EXPLAIN 등으로 실제 활용 현황을 반드시 확인해야 합니다.

references