단일 인덱스 vs 복합 인덱스: 성능 비교와 최적화 사례

소일로·2024년 12월 2일

1. 배경

사이드 프로젝트 진행 중 단일 인덱스와 복합 인덱스의 성능 차이를 확인해보고자 약 10만 건의 더미 데이터를 삽입하여 성능 테스트를 진행했습니다.
테스트 대상 테이블은 ProjectEntity로, 프로젝트와 관련된 정보를 저장하고 있습니다. 초기 설계에서는 단일 인덱스를 사용했으며, 다중 조건을 포함한 쿼리 실행 시 1.5~2초의 응답 시간을 보였습니다. 과연 더빠를수는 없을까?라는 문제를 해결하기 위해 복합 인덱스를 설계하고, 데이터베이스 성능을 최적화를 시도했습니다. 성능 테스트 도구로는 DBeaver를 사용하여 데이터 삽입 및 쿼리 분석을 진행했습니다.

2. 단일 인덱스 적용

2.1 단일 인덱스란?

단일 인덱스는 하나의 컬럼에만 적용된 인덱스입니다. 예를 들어, pType (프로젝트 타입을 의미)컬럼에 단일 인덱스를 추가하면 해당 컬럼을 기준으로 하는 검색은 빠르게 수행되지만, 다중 조건 쿼리에서는 추가적인 조건을 처리하기 위해 테이블 전체를 스캔해야 합니다.

2.2 단일 인덱스 사용 사례

CREATE INDEX idx_project_pType ON project (pType);
- 위 인덱스는 pType을 기준으로 하는 단일 조건 쿼리에서 성능을 개선합니다.
- 예:
```
sql

SELECT * FROM project WHERE pType = 'PROJECT';
```

2.3 문제점

다중 조건 쿼리에서는 단일 인덱스가 충분하지 않음.
예:
```
sql
SELECT * FROM project
WHERE pType = 'PROJECT'
  AND project_status = 'ACTIVE'
  AND recruitment_start <= CURRENT_DATE
  AND recruitment_end >= CURRENT_DATE;
```
- 위와 같은 쿼리에서는 pType에 단일 인덱스가 적용되더라도 나머지 조건(project_status, recruitment_start, recruitment_end)은 풀 테이블 스캔(Full Table Scan)이 발생.

3. 복합 인덱스의 적용

3.1 복합 인덱스란?

복합 인덱스는 다중 컬럼에 대해 하나의 인덱스를 생성하여 다중 조건 쿼리에서 효율적으로 작동하도록 설계된 인덱스입니다.

3.2 복합 인덱스 설계

다음과 같은 쿼리를 최적화하기 위해 복합 인덱스를 설계했습니다:

sql

SELECT * FROM project
WHERE pType = 'PROJECT'
  AND project_status = 'ACTIVE'
  AND recruitment_start <= CURRENT_DATE
  AND recruitment_end >= CURRENT_DATE
ORDER BY created_at DESC
LIMIT 20;

복합 인덱스 생성:

sql
CREATE INDEX idx_project_search
ON project (pType, project_status, recruitment_start, recruitment_end, created_at);

3.3 복합 인덱스의 이점

다중 조건이 결합된 쿼리에서 Index Scan을 통해 검색을 처리.
데이터 필터링과 정렬이 인덱스 수준에서 이루어지므로 성능 향상.

4. 성능 비교

4.1 테스트 환경

데이터셋: 10만 건의 더미 데이터.
도구: DBeaver를 통해 SQL 스크립트를 사용하여 데이터 삽입.
쿼리 조건:
- pType = 'PROJECT'
- projectStatus = 'ACTIVE'
- recruitmentStart <= CURRENT_DATE
- recruitmentEnd >= CURRENT_DATE
- ORDER BY createdAt DESC

4.2 테스트 결과

인덱스 유형	쿼리 실행 시간	설명
단일 인덱스	1.5~2초	단일 조건만 최적화 가능, 나머지 조건은 풀 테이블 스캔 발생
복합 인덱스	50~150ms	다중 조건을 인덱스 스캔으로 처리, 정렬도 인덱스에서 처리

5. 10만 건 데이터 생성 방법

DBeaver의 SQL Editor에서 다음 스크립트를 실행하여 데이터를 삽입:

sql
INSERT INTO project (title, description, pType, project_status, recruitment_start, recruitment_end, created_at)
SELECT
  CONCAT('Project ', seq) AS title,
  'Description for Project ' || seq AS description,
  CASE WHEN seq % 2 = 0 THEN 'PROJECT' ELSE 'STUDY' END AS pType,
  CASE WHEN seq % 3 = 0 THEN 'ACTIVE' ELSE 'INACTIVE' END AS project_status,
  DATE_ADD(CURRENT_DATE, INTERVAL -seq DAY) AS recruitment_start,
  DATE_ADD(CURRENT_DATE, INTERVAL seq DAY) AS recruitment_end,
  DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -seq HOUR) AS created_at
FROM generate_series(1, 100000) AS seq;

결론

이번 테스트를 통해 단일 인덱스와 복합 인덱스의 성능 차이를 명확히 확인할 수 있었습니다. 특히, 복합 인덱스를 통해 쿼리 실행 속도를 약 10~30배 개선할 수 있었습니다. 앞으로 DB 인덱스 설계시 좀 더 이 부분을 명확히 설계할 필요성을 느끼는 계기가 되었습니다.