[DB 튜닝] 쿼리 성능 개선 및 인덱스(Index) 활용 가이드
데이터 건수가 적음에도 불구하고 쿼리 수행 속도가 느린 현상(예: 소량의 데이터 조회 시 0.7초 소요)은 주로 실행 계획의 비효율성에서 기인합니다. 본 문서는 DBeaver를 활용한 성능 분석 방법과 실무에서 적용 가능한 인덱스 튜닝의 핵심 원칙, 그리고 성능 저하의 주요 원인을 정리하였습니다.
1. 쿼리 성능 분석: DBeaver 활용
쿼리 실행 시 단순 수행 시간(Duration) 외에 세부 통계를 확인하여 지연 원인을 파악해야 합니다.
- 확인 방법: 쿼리 실행 후 결과 창의 [통계(Statistics)] 탭 확인
- 지표 분석:
- Execute (실행): DB 서버 내에서 쿼리를 연산하는 시간. 이 수치가 높을 경우 SQL 구조나 인덱스 부재가 원인입니다.
- Fetch (인출): 데이터를 클라이언트로 전송하는 시간. 이 수치가 높을 경우 네트워크 대역폭이나 과도한 데이터 전송량이 원인입니다.
- 참고: 실행 계획(
Ctrl + Shift + E)의 Cost(비용) 수치가 비정상적으로 높다면(예: 7,000 이상), 인덱스를 타지 않고 Full Scan이 발생하고 있을 가능성이 높습니다.
2. 상황별 인덱스 적용 전략
① GROUP BY 성능 저하 시
- 현상:
GROUP BY연산 시 테이블 전체를 스캔(Full Scan)하고 정렬하는 과정에서 부하 발생. - 해결: 해당 컬럼에 인덱스를 적용하면, 이미 정렬된 인덱스 스캔(Index Full Scan)만으로 집계가 완료되어 성능이 대폭 향상됩니다.
② JOIN 성능 저하 시 (다중 테이블 조인)
- 원인: 조인 연결 고리 컬럼에 인덱스가 없을 경우.
- 해결:
- 조인 조건(
ON a.id = b.id)에 사용되는 컬럼 중, 참조되는 테이블(FK)의 컬럼에는 반드시 인덱스가 존재해야 합니다. - 특히 데이터량이 가장 많은 테이블(Driving Table)과 조인되는 컬럼의 인덱스 유무가 성능의 핵심입니다.
③ 복합 조건 (WHERE 절 다중 조건)
- 원칙: 복합 인덱스 생성 시 컬럼 순서가 중요합니다.
- 동등 비교(
=) 조건을 선행 컬럼으로 배치합니다. - 범위 비교(
>,<,BETWEEN) 조건은 후행 컬럼으로 배치합니다.
- 예시:
WHERE A = 1 AND B > 100→CREATE INDEX idx_name ON table(A, B)
3. 인덱스가 적용되지 않는 주요 사례
인덱스를 생성했음에도 불구하고 성능이 개선되지 않는 경우, 다음 3가지 원인을 점검해야 합니다.
① IN 절의 과도한 파라미터
IN (값1, 값2 ... 값1000)과 같이 조건 값이 과도하게 많을 경우, 옵티마이저는 인덱스 랜덤 액세스(Random Access)보다 테이블 풀 스캔(Full Scan)이 유리하다고 판단하여 인덱스를 사용하지 않을 수 있습니다.- 해결: 애플리케이션 단에서 데이터를 분할 조회(Chunking)하거나,
JOIN절로 쿼리를 변경해야 합니다.
② 컬럼 가공 (함수 사용)
- 좌변 컬럼을 가공할 경우(
WHERE UPPER(name) = 'KIM') 인덱스를 사용할 수 없습니다. - 해결:
WHERE name = 'Kim'과 같이 원본 컬럼을 유지하거나, 불가피한 경우 함수기반 인덱스(FBI)를 생성해야 합니다.
③ 데이터 타입 불일치
- 컬럼 타입(VARCHAR)과 조건 값(Number)의 타입이 다를 경우, 내부 형변환이 발생하여 인덱스가 무시됩니다. 반드시 데이터 타입을 일치시켜야 합니다.
4. SELECT 절의 불필요한 컬럼 제거 및 커버링 인덱스
JOIN이나 WHERE 조건뿐만 아니라 SELECT 절의 컬럼 구성도 성능에 영향을 미칩니다.
- 문제: 불필요한 대용량 컬럼(CLOB, TEXT 등)이나
SELECT *를 사용할 경우, 인덱스 스캔 후 다시 테이블 데이터 블록을 조회하는 Table Access 비용이 발생합니다. - 해결 (커버링 인덱스): 쿼리에 사용되는 모든 컬럼(
SELECT,WHERE,JOIN)을 포함하는 인덱스를 생성하면, 테이블 접근 없이 인덱스만으로 데이터를 조회할 수 있어 획기적인 성능 개선이 가능합니다.
5. 가상 인덱스(Virtual Index)를 활용한 테스트 (Oracle)
운영 중인 서버에서 인덱스 생성에 따른 부하가 우려될 경우, 가상 인덱스를 통해 실행 계획 변화를 미리 테스트할 수 있습니다.
-- 1. 세션 설정 (가상 인덱스 사용 활성화)
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
-- 2. 가상 인덱스 생성 (NOSEGMENT 옵션: 저장 공간 미사용, 즉시 생성)
CREATE INDEX idx_test ON my_table(my_col) NOSEGMENT;
-- 3. 실행 계획(Cost) 감소 여부 확인
-- 4. 테스트 완료 후 삭제
DROP INDEX idx_test;
6. UX 관점에서의 응답 속도 기준
성능 최적화 목표 설정 시, 사용자 경험(UX)에 기반한 기준을 참고할 수 있습니다.
- 0.2초 이내: 즉각적인 반응 (목록 조회 등 일반적인 기능의 목표치)
- 1.0초 이내: 사용자의 사고 흐름이 유지됨 (대시보드 등)
- 3.0초 이내: 사용자가 지연을 인지하나 허용 가능함 (복잡한 통계 등. 단, 로딩 인디케이터 필요)
- 10초 초과: 사용자 이탈 발생
결론: 데이터 성격(OLTP vs OLAP)에 따라 적절한 목표 시간을 설정하는 것이 중요합니다.
