데이터베이스 인덱스(Index)의 동작 원리와 장단점을 설명해주세요.

인덱스(Index)는 데이터베이스 검색 속도를 향상시키기 위해 별도로 관리되는 데이터 구조입니다. 책의 색인(목차)처럼 원하는 데이터를 빠르게 찾을 수 있게 해줍니다. 인덱스 없이는 전체 테이블을 처음부터 끝까지 스캔(Full Table Scan)해야 하지만, 인덱스를 활용하면 훨씬 적은 탐색으로 원하는 데이터를 찾을 수 있습니다.

인덱스 없이 검색하면?

-- 100만 건의 users 테이블에서 이메일로 검색
SELECT * FROM users WHERE email = 'alice@example.com';

-- 인덱스 없음: Full Table Scan
-- → 100만 건을 처음부터 끝까지 하나씩 비교
-- → O(n) 시간 복잡도
-- → 대용량 데이터에서 매우 느림

-- 인덱스 있음: B-Tree Index 탐색
-- → 약 20번의 비교로 찾을 수 있음 (log₂ 1,000,000 ≈ 20)
-- → O(log n) 시간 복잡도

B-Tree 인덱스 구조

대부분의 DBMS는 B-Tree(또는 B+Tree)를 기본 인덱스 구조로 사용합니다.

B+Tree 구조 예시 (이메일 인덱스):

                    [Root Node]
                 [alice | charlie]
                /        |        \
         [Leaf]       [Leaf]       [Leaf]
    [a..alice]    [alice..charlie]  [charlie..z]
         |               |                |
    [a@..alice@]   [alice@..charlie@]  [charlie@..z@]
         |               |                |
     (실제 데이터         (실제 데이터       (실제 데이터
      레코드 포인터)       레코드 포인터)     레코드 포인터)

Leaf Node는 연결 리스트로 연결 → 범위 검색에 효율적

B+Tree 탐색 과정

"charlie@example.com" 검색:

1. Root Node 탐색: alice < charlie ≤ charlie → 오른쪽으로
2. Internal Node 탐색: 해당 범위 leaf node로 이동
3. Leaf Node 탐색: 정확한 키 찾기
4. 레코드 포인터로 실제 데이터 접근

→ 트리 높이만큼만 I/O 발생 (보통 3~4회)

인덱스 생성 및 종류

기본 인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- UNIQUE 인덱스
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- → 중복 값 불허 + 검색 최적화

-- 인덱스 삭제
DROP INDEX idx_users_email ON users;  -- MySQL
DROP INDEX idx_users_email;           -- PostgreSQL

-- 인덱스 확인
SHOW INDEX FROM users;               -- MySQL
\d users                             -- PostgreSQL

복합 인덱스 (Composite Index)

-- 여러 컬럼을 하나의 인덱스로
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- ✅ 복합 인덱스 활용 가능한 쿼리
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

-- ❌ 복합 인덱스 활용 불가 (Leftmost Prefix 규칙 위반)
SELECT * FROM orders WHERE created_at > '2024-01-01';
-- → user_id 없이 created_at만으로는 인덱스 사용 불가!
-- → user_id가 인덱스의 첫 번째 컬럼이므로, user_id 없이는 풀스캔

Leftmost Prefix 규칙

-- 인덱스: (A, B, C)
-- 이 인덱스가 활용되는 경우:
WHERE A = 1                      -- ✅ A 사용
WHERE A = 1 AND B = 2            -- ✅ A, B 사용
WHERE A = 1 AND B = 2 AND C = 3  -- ✅ A, B, C 전부 사용
WHERE A = 1 AND C = 3            -- ✅ A만 사용 (C는 인덱스 미사용)

-- 인덱스가 활용되지 않는 경우:
WHERE B = 2                      -- ❌ A 없음
WHERE C = 3                      -- ❌ A, B 없음
WHERE B = 2 AND C = 3            -- ❌ A 없음

커버링 인덱스 (Covering Index)

-- 쿼리에 필요한 모든 컬럼이 인덱스에 포함된 경우
-- → 실제 테이블 접근 없이 인덱스만으로 응답 가능

CREATE INDEX idx_users_email_name ON users(email, name);

-- 이 쿼리는 테이블 접근 없이 인덱스만으로 처리
SELECT name FROM users WHERE email = 'alice@example.com';
-- → email로 인덱스 탐색 → name 값도 인덱스에 있음 → 테이블 접근 불필요

-- EXPLAIN으로 확인 (MySQL)
EXPLAIN SELECT name FROM users WHERE email = 'alice@example.com';
-- Extra: Using index  ← 커버링 인덱스 사용 표시

특수 인덱스 종류

해시 인덱스 (Hash Index)

-- 등호(=) 검색에 최적화, 범위 검색 불가
-- MySQL MEMORY 엔진, PostgreSQL에서 지원

-- 장점: O(1) 등호 검색
-- 단점: 범위 검색(>, <, BETWEEN) 불가, 정렬 불가

-- PostgreSQL에서 명시적 해시 인덱스
CREATE INDEX idx_hash_email ON users USING HASH(email);

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

-- 텍스트 내용 검색에 최적화
CREATE FULLTEXT INDEX idx_posts_content ON posts(title, content);

-- LIKE '%keyword%' 대신 Full-Text 검색 사용
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('React 성능 최적화' IN BOOLEAN MODE);
-- LIKE '%React%' 보다 훨씬 빠름, 형태소 분석 가능

부분 인덱스 (Partial Index / Filtered Index)

-- 특정 조건의 행에만 인덱스 생성 (PostgreSQL, SQLite)
CREATE INDEX idx_active_users ON users(email)
WHERE active = true;
-- → 비활성 사용자는 인덱스에 포함 안 됨 → 인덱스 크기 감소

-- 유용한 케이스: soft delete
CREATE INDEX idx_orders_pending ON orders(user_id, created_at)
WHERE status = 'PENDING';

인덱스 장단점

장점

-- 1. 검색 성능 향상 (SELECT, WHERE)
-- 100만 행 검색: Full Scan O(n) → Index O(log n)

-- 2. ORDER BY 최적화
-- 인덱스가 이미 정렬된 상태 → filesort 불필요
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
-- → 인덱스를 순서대로 읽으면 됨

-- 3. GROUP BY 최적화
CREATE INDEX idx_orders_user ON orders(user_id);
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- → 인덱스로 그룹핑 최적화

-- 4. JOIN 최적화
-- 조인 컬럼에 인덱스가 있으면 Nested Loop Join 최적화
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id  -- o.user_id에 인덱스가 있어야 효율적
WHERE u.id = 123;

단점

-- 1. 추가 저장 공간 필요
-- B-Tree 인덱스는 원본 데이터 크기의 약 10~30% 추가 공간 사용

-- 2. INSERT/UPDATE/DELETE 성능 저하
-- 데이터 변경 시 인덱스도 함께 갱신해야 함

-- INSERT: 새 데이터 삽입 + B-Tree 재조정
INSERT INTO users (email, name) VALUES ('new@example.com', 'Bob');
-- → users 테이블 삽입 + 모든 관련 인덱스 갱신

-- UPDATE: 인덱스 컬럼 변경 시 인덱스 항목 이동
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- → email 인덱스에서 이전 값 삭제, 새 값 삽입

-- DELETE: 인덱스에서도 삭제 표시
-- → 쓰기가 많은 테이블에서 과도한 인덱스는 쓰기 성능 저하

-- 3. 잘못된 인덱스는 오히려 성능 저하
-- DBMS가 인덱스 사용 여부를 결정 (쿼리 옵티마이저)
-- 카디널리티가 낮은 컬럼(성별 M/F)에 인덱스 → 오히려 느릴 수 있음

인덱스를 어떤 컬럼에 생성해야 할까?

적합한 컬럼

-- 1. Cardinality(카디널리티)가 높은 컬럼 (값의 다양성)
-- ✅ email: 사용자마다 다름 → 높은 카디널리티
-- ✅ user_id: 고유 식별자
-- ✅ order_number: 주문번호
-- ❌ gender: M/F 두 가지 → 낮은 카디널리티 (50%씩 → 인덱스 비효율)
-- ❌ is_active: true/false 두 가지

-- 2. WHERE 절에 자주 사용되는 컬럼
-- 3. JOIN 조건으로 사용되는 컬럼
-- 4. ORDER BY, GROUP BY에 사용되는 컬럼
-- 5. 자주 검색하지만 잘 변경되지 않는 컬럼

인덱스가 무시되는 경우

-- 1. 함수/연산 사용
-- ❌ 인덱스 미사용
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM orders WHERE amount * 1.1 > 10000;

-- ✅ 인덱스 사용
SELECT * FROM users WHERE email = 'alice@example.com';  -- 소문자로 저장된 경우
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM orders WHERE amount > 9090;  -- (10000/1.1)

-- 2. LIKE 와일드카드 (앞에 %)
-- ❌ 인덱스 미사용
SELECT * FROM users WHERE name LIKE '%alice%';
SELECT * FROM users WHERE name LIKE '%alice';

-- ✅ 인덱스 사용 (앞에 % 없음)
SELECT * FROM users WHERE name LIKE 'alice%';

-- 3. OR 조건 (인덱스 각각 있어야)
-- ⚠️ 경우에 따라 인덱스 미사용
SELECT * FROM users WHERE email = 'a@a.com' OR name = 'Bob';

-- 4. NULL 비교
-- ❌ 일반적으로 인덱스 미사용 (DBMS마다 다름)
SELECT * FROM users WHERE email IS NULL;

EXPLAIN으로 인덱스 사용 확인

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 결과 예시:
-- id | select_type | table | type  | key             | rows | Extra
-- 1  | SIMPLE      | users | ref   | idx_users_email | 1    | NULL
--                           ↑ref는 인덱스 사용, ↑인덱스명, ↑예상 행 수

-- type 값 성능 순서:
-- system > const > eq_ref > ref > range > index > ALL(Full Scan)

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan using idx_users_email on users
-- Index Cond: (email = 'alice@example.com')

정리

항목	내용
기본 구조	B+Tree (정렬, 범위 검색 지원)
탐색 복잡도	O(log n) vs Full Scan O(n)
장점	검색/정렬/조인 성능 향상
단점	추가 저장 공간, 쓰기 성능 저하
적합한 컬럼	높은 카디널리티, WHERE/JOIN/ORDER BY 자주 사용
부적합한 컬럼	낮은 카디널리티, 자주 변경, 쓰기 위주 테이블
Leftmost Prefix	복합 인덱스에서 왼쪽부터 순서대로 사용해야 효율적
커버링 인덱스	쿼리 컬럼이 인덱스에 모두 있으면 테이블 접근 불필요

핵심: 인덱스는 "읽기 성능 향상을 위해 쓰기 성능과 공간을 트레이드오프하는 것"입니다. 실제 쿼리 패턴과 데이터 특성을 분석하여 신중하게 설계해야 합니다.