전체 목록
데이터베이스Medium#36

SQL 인덱스(Index)란 무엇이며 언제 사용해야 하나요?

#DB#인덱스#성능#SQL
힌트

조회 성능 향상과 쓰기 성능 저하의 트레이드오프를 생각해보세요.

정답 및 해설

SQL 인덱스(Index)란 무엇이며 언제 사용해야 하나요?

인덱스(Index)는 데이터베이스 테이블의 특정 컬럼에 대한 검색 속도를 높이기 위한 자료구조입니다. 책의 목차나 색인처럼, 원하는 데이터가 어디에 있는지 빠르게 찾을 수 있도록 별도의 탐색 구조를 미리 만들어 두는 방식입니다.

인덱스가 없을 때 vs 있을 때

인덱스 없이 조회하면 데이터베이스는 Full Table Scan을 수행합니다. 테이블의 모든 행을 처음부터 끝까지 읽어야 하므로 시간 복잡도는 O(n) 입니다. 데이터가 100만 건이라면 최대 100만 번 읽어야 합니다.

인덱스를 사용하면 B-Tree 탐색을 통해 O(log n) 으로 줄어듭니다. 100만 건의 데이터라면 약 20번의 비교로 원하는 데이터를 찾을 수 있습니다.

-- 인덱스가 없는 경우: 100만 행 Full Scan
SELECT * FROM users WHERE email = 'user@example.com';

-- email 컬럼에 인덱스가 있는 경우: B-Tree 탐색 O(log n)
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';

인덱스의 내부 구조

B-Tree 인덱스 (기본 인덱스)

대부분의 RDBMS(MySQL, PostgreSQL, Oracle 등)에서 기본으로 사용하는 구조입니다. 모든 리프 노드가 같은 깊이를 가지는 균형 트리(Balanced Tree)로, 범위 검색(BETWEEN, >, <)과 정렬(ORDER BY)에 효과적입니다.

                [50]
               /    \
           [25]      [75]
          /    \    /    \
       [10]  [30] [60]  [90]
  • WHERE age > 30 같은 범위 쿼리에 적합
  • ORDER BY 절의 정렬 비용을 줄여줌
  • LIKE 'abc%' 처럼 앞부분이 고정된 패턴 검색에 유리

Hash 인덱스

해시 함수를 이용해 키를 버킷에 매핑하는 구조입니다. 정확한 일치(=) 검색에서 O(1) 의 속도를 낼 수 있지만, 범위 검색이나 정렬에는 사용할 수 없습니다.

특징B-TreeHash
등호 검색 (=)O(log n)O(1)
범위 검색 (>, <, BETWEEN)가능불가능
정렬 (ORDER BY)가능불가능
주요 사용처일반 목적메모리 DB, 캐싱

기타 인덱스 유형

  • Composite Index (복합 인덱스): 두 개 이상의 컬럼을 묶어 하나의 인덱스로 생성
  • Covering Index: 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 테이블 접근 없이 인덱스만으로 쿼리 처리
  • Unique Index: 중복 값을 허용하지 않는 인덱스 (PK에 자동 생성)
  • Full-Text Index: 텍스트 전문 검색을 위한 인덱스

인덱스를 사용해야 하는 상황

WHERE 절에 자주 등장하는 컬럼

-- users 테이블에서 특정 이메일 조회가 자주 발생
SELECT id, name FROM users WHERE email = ?;

-- email 컬럼에 인덱스 생성
CREATE INDEX idx_users_email ON users(email);

JOIN 조건에 사용되는 컬럼

-- orders.user_id로 JOIN이 자주 발생
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- orders 테이블의 user_id에 인덱스 생성
CREATE INDEX idx_orders_user_id ON orders(user_id);

ORDER BY / GROUP BY 컬럼

-- created_at 기준 정렬이 자주 발생
SELECT * FROM posts ORDER BY created_at DESC;

CREATE INDEX idx_posts_created_at ON posts(created_at);

Cardinality(카디널리티)가 높은 컬럼

카디널리티란 컬럼에 있는 고유한 값의 수를 의미합니다. 인덱스는 카디널리티가 높은 컬럼(예: 이메일, 주민번호)에 효과적이며, 낮은 컬럼(예: 성별 M/F, boolean true/false)에는 효과가 거의 없습니다.

-- 카디널리티가 높음 → 인덱스 효과적
CREATE INDEX idx_high ON users(email);       -- 유니크한 값 많음

-- 카디널리티가 낮음 → 인덱스 효과 미미
-- gender는 'M', 'F' 두 가지 값만 있어 50% 행을 읽어야 함
-- CREATE INDEX idx_low ON users(gender);  -- 비효율적

복합 인덱스 활용

여러 컬럼을 함께 검색하는 패턴이 있다면 복합 인덱스가 유리합니다.

-- 자주 사용하는 쿼리 패턴
SELECT * FROM orders WHERE user_id = 1 AND status = 'completed';

-- 복합 인덱스 생성 (순서가 중요!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

복합 인덱스는 왼쪽 접두어(Leftmost Prefix) 규칙을 따릅니다.

-- (user_id, status) 복합 인덱스가 있을 때:
WHERE user_id = 1               -- 인덱스 사용 가능 (왼쪽 컬럼만)
WHERE user_id = 1 AND status = 'completed' -- 인덱스 사용 가능 (두 컬럼 모두)
WHERE status = 'completed'      -- 인덱스 사용 불가 (왼쪽 컬럼 누락)

인덱스의 단점

1. 추가 저장 공간 필요

인덱스는 별도의 자료구조로 저장되므로 디스크 공간을 추가로 소모합니다. 인덱스가 많을수록 저장 공간도 증가합니다.

2. 쓰기 성능 저하 (INSERT / UPDATE / DELETE)

데이터가 변경될 때마다 관련된 모든 인덱스도 함께 갱신해야 합니다. 인덱스가 많을수록 쓰기 작업의 오버헤드가 커집니다.

인덱스 1개 → 데이터 삽입 시 B-Tree 1번 갱신
인덱스 5개 → 데이터 삽입 시 B-Tree 5번 갱신

3. 인덱스가 무시되는 경우

인덱스를 만들었더라도 다음과 같은 경우에는 옵티마이저가 인덱스를 사용하지 않을 수 있습니다.

-- 함수로 감싼 컬럼 → 인덱스 무시됨
WHERE YEAR(created_at) = 2024       -- 비효율
WHERE created_at >= '2024-01-01'    -- 효율적

-- 앞에 와일드카드 LIKE → 인덱스 무시됨
WHERE name LIKE '%홍길동'            -- Full Scan
WHERE name LIKE '홍길동%'            -- 인덱스 사용 가능

-- 묵시적 형 변환 → 인덱스 무시됨
WHERE user_id = '123'               -- user_id가 INT이면 형변환 발생
WHERE user_id = 123                 -- 인덱스 정상 사용

인덱스 생성 및 관리 명령어

-- 인덱스 생성
CREATE INDEX idx_name ON table_name(column_name);

-- 유니크 인덱스 생성
CREATE UNIQUE INDEX idx_unique ON users(email);

-- 복합 인덱스 생성
CREATE INDEX idx_composite ON orders(user_id, status, created_at);

-- 인덱스 삭제
DROP INDEX idx_name ON table_name;

-- 인덱스 목록 확인 (MySQL)
SHOW INDEX FROM table_name;

-- 쿼리 실행 계획 확인 (인덱스 사용 여부 확인)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

인덱스 사용 전략 요약

상황권장 여부이유
WHERE 조건에 자주 쓰이는 컬럼권장검색 속도 향상
JOIN의 연결 컬럼권장조인 비용 감소
카디널리티가 높은 컬럼권장필터링 효과 큼
카디널리티가 낮은 컬럼비권장효과 미미, 오버헤드만 발생
INSERT/UPDATE가 매우 빈번한 테이블신중쓰기 성능 저하
소규모 테이블비권장Full Scan이 더 빠를 수 있음
이전 질문다음 질문