데이터베이스 인덱스 (Database Index) 핵심 가이드#

1.1 핵심 개념#

인덱스(Index)는 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조임 책의 뒷부분에 있는 ‘색인’이나 도서관의 ‘목차’와 유사한 역할을 하며, 데이터를 정렬된 상태로 유지하여 원하는 정보를 빠르게 찾을 수 있도록 도움

1.2 인덱스 적용 전후 비교#

인덱스가 없는 경우 (Full Table Scan) SQL 문을 실행했을 때, 데이터베이스는 조건에 맞는 데이터를 찾기 위해 테이블의 처음부터 끝까지 모든 행을 검사해야 함. 데이터 양이 많을수록 성능이 급격히 저하됨

1
SELECT * FROM users WHERE email = 'user@example.com';
2
-- 전체 테이블을 스캔하므로 속도가 느림

인덱스가 적용된 경우 (Index Scan) 인덱스를 생성하면 정렬된 구조를 통해 해당 데이터를 빠르게 찾아낼 수 있어 검색 성능이 비약적으로 향상됨

1
CREATE INDEX idx_email ON users(email);
2
SELECT * FROM users WHERE email = 'user@example.com';
3
-- 인덱스를 사용하여 빠르게 검색

2.1 B-Tree 인덱스 (Balanced Tree)#

MySQL, PostgreSQL 등 대부분의 관계형 데이터베이스에서 기본적으로 사용하는 인덱스 구조 데이터가 항상 정렬된 상태를 유지하며, 트리(Tree) 구조를 통해 검색, 삽입, 삭제 연산에서 O(log N)의 시간 복잡도를 가짐

특징

• 장점: 검색, 삽입, 삭제 성능이 균일하며, 범위 검색(BETWEEN, ORDER BY 등)에 매우 효율적임
• 단점: 데이터 삽입이나 삭제가 빈번하게 일어날 경우, 트리의 균형을 맞추기 위한 리밸런싱 비용이 발생하여 성능이 저하될 수 있음

2.2 Hash 인덱스#

해시 함수(Hash Function)를 사용하여 키 값을 메모리 주소로 매핑하는 방식

특징

• 장점: 동등 비교(=) 연산 시 O(1)에 가까운 매우 빠른 검색 속도를 제공함
• 단점: 데이터가 정렬되어 있지 않으므로 부등호(<, >), 범위 검색(BETWEEN), 정렬(ORDER BY)에는 사용할 수 없음. 주로 Redis 같은 인메모리 데이터베이스나 특정 상황에서 사용됨

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

긴 텍스트 데이터에서 특정 키워드를 검색할 때 최적화된 인덱스

특징

• 장점: 일반적인 LIKE '%keyword%' 패턴 검색보다 훨씬 빠른 속도를 제공하며, 자연어 처리에 유리함. MATCH ... AGAINST 구문을 사용함
• 단점: 실시간 데이터 업데이트 처리가 까다로울 수 있으며, 한 글자 검색 등에는 별도의 설정이 필요할 수 있음

3.1 Primary Key (기본 키) 인덱스#

테이블 생성 시 Primary Key로 지정된 컬럼에 대해 자동으로 생성되는 인덱스. 데이터베이스에 따라 클러스터형 인덱스(Clustered Index)로 구성되어 데이터 자체가 정렬된 상태로 저장됨

1
CREATE TABLE users (
2
    id INT PRIMARY KEY,  -- 자동으로 인덱스 생성
3
    name VARCHAR(100),
4
    email VARCHAR(255)
5
);

3.2 Unique 인덱스#

중복된 값을 허용하지 않는 인덱스. 특정 컬럼의 유일성을 보장해야 할 때 사용함 (단, NULL 값은 중복 허용 가능)

1
CREATE UNIQUE INDEX idx_unique_email ON users(email);

3.3 Composite Index (복합 인덱스)#

두 개 이상의 컬럼을 묶어서 하나의 인덱스로 만드는 것 중요: 인덱스 생성 시 컬럼의 순서가 매우 중요함. 왼쪽 컬럼부터 순서대로 정렬되기 때문에, 선행 컬럼이 조건절에 없으면 인덱스가 제대로 활용되지 못할 수 있음

1
CREATE INDEX idx_name_email ON users(name, email);

• 사용 가능: WHERE name = 'John' AND email = 'john@example.com'
• 사용 불가: WHERE email = 'john@example.com' (name 조건 누락)

3.4 Covering Index (커버링 인덱스)#

쿼리에서 조회하려는 모든 컬럼이 인덱스에 포함되어 있어, 실제 데이터 테이블을 읽지 않고 인덱스만으로 쿼리를 완료할 수 있는 경우를 말함. 디스크 I/O를 획기적으로 줄일 수 있음

1
-- name 컬럼에 인덱스가 있는 경우
2
SELECT name FROM users WHERE name = 'Alice';

4.1 인덱스 사용 가이드라인#

권장하는 경우

• WHERE 절에 자주 등장하는 컬럼
• JOIN 연결 고리로 사용되는 컬럼
• ORDER BY 또는 GROUP BY에 자주 사용되는 컬럼

피해야 하는 경우

• 데이터 전체 건수가 적은 경우 (인덱스를 거치는 것보다 전체 스캔이 빠를 수 있음)
• 데이터 변경(INSERT, UPDATE, DELETE)이 빈번한 컬럼 (인덱스 재정렬 비용 발생)
• 카디널리티(Cardinality)가 낮은 컬럼 (예: 성별, 사용여부 등 중복도가 높은 데이터)

4.2 실행 계획 (Explain) 확인#

SQL 쿼리 앞에 EXPLAIN 키워드를 붙여 인덱스가 정상적으로 사용되고 있는지 확인해야 함

1
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

주요 결과 해석

• type: ALL: 전체 테이블 스캔 (Full Table Scan). 성능 튜닝 필요
• type: INDEX: 인덱스 전체 스캔
• type: REF 또는 RANGE: 인덱스를 효율적으로 사용하여 특정 범위나 값을 조회 (권장)

Case 1: 함수 사용으로 인한 인덱스 미작동#

조건절의 컬럼을 함수로 가공하면 인덱스를 사용할 수 없음

문제 상황

1
-- email 컬럼을 가공하였으므로 인덱스를 타지 않음
2
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

해결 방안 컬럼 자체를 가공하지 않고 원본 데이터를 비교하거나, 미리 소문자로 변환하여 저장하는 방식을 사용해야 함

1
SELECT * FROM users WHERE email = 'test@example.com';

Case 2: OR 조건에서의 인덱스 효율 저하#

OR 연산자는 연결된 조건 중 하나라도 인덱스가 없으면 전체 스캔이 발생할 수 있음

문제 상황

1
SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@example.com';

해결 방안 각각 인덱스가 걸린 쿼리를 UNION으로 결합하여 처리하면 각 인덱스를 효율적으로 활용할 수 있음

1
SELECT * FROM users WHERE name = 'Alice'
2
UNION
3
SELECT * FROM users WHERE email = 'alice@example.com';