[UMC_study] 함수 기반 인덱스와 복합 인덱스

함수 기반 인덱스와 복합 인덱스: 성능 최적화를 위한 완전 가이드

개요

데이터베이스 성능 최적화에서 인덱스는 핵심적인 역할을 합니다. 특히 **함수 기반 인덱스(Function-Based Index)**와 **복합 인덱스(Composite Index)**는 복잡한 쿼리의 성능을 크게 향상시킬 수 있는 강력한 도구입니다. 이 글에서는 두 인덱스 유형의 특성, 장단점, 그리고 실제 적용 사례를 상세히 분석해보겠습니다.

함수 기반 인덱스(Function-Based Index)

정의 및 특징

함수 기반 인덱스는 컬럼의 원본 값이 아닌 함수나 표현식의 결과에 대해 생성되는 인덱스입니다. 이는 WHERE 절에서 함수를 사용하는 쿼리의 성능을 대폭 향상시킬 수 있습니다.

주요 특징:

• 표현식 기반의 인덱싱으로 복잡한 계산 결과를 미리 저장
• 대소문자 구분 없는 검색을 위한 UPPER(), LOWER() 함수 지원
• 수학적 연산이나 문자열 조작 함수 활용 가능
• B-tree 또는 비트맵 인덱스로 구성 가능

생성 문법

Oracle 예시:

-- 대소문자 구분 없는 검색을 위한 함수 기반 인덱스
CREATE INDEX idx_last_name_upper
ON employees (UPPER(last_name));

-- 수학적 연산을 위한 함수 기반 인덱스
CREATE INDEX idx_total_cost
ON products (price * quantity);

 

 

PostgreSQL 예시:

 

-- 표현식 인덱스 생성
CREATE INDEX idx_lower_email 
ON users (lower(email));

 

1. 쿼리 성능 대폭 향상

함수 기반 인덱스는 함수 계산 결과를 미리 저장하여 전체 테이블 스캔을 방지합니다. 실제 테스트에서는 쿼리 실행 시간이 최대 40% 단축되는 효과를 보였습니다.

2. CPU 비용 절감

함수 연산을 매번 수행하지 않아도 되므로 CPU 사용량이 크게 감소합니다. 특히 복잡한 수학적 계산이나 문자열 처리가 포함된 쿼리에서 그 효과가 뚜렷합니다.

3. 효율적인 대소문자 구분 없는 검색

애플리케이션 로직을 변경하지 않고도 빠른 대소문자 구분 없는 검색이 가능합니다.

함수 기반 인덱스의 단점

1. 유지보수 오버헤드

가장 큰 단점은 DML 작업 시의 성능 저하입니다. 데이터가 삽입, 수정, 삭제될 때마다 인덱스의 함수 결과값을 재계산하고 업데이트해야 합니다.

2. 옵티마이저 제약사항

Oracle의 경우 비용 기반 옵티마이저(CBO)에서만 사용 가능하며, 규칙 기반 옵티마이저에서는 활용할 수 없습니다.

3. NULL 값 처리 제한

함수 기반 인덱스는 NULL 값을 포함할 수 없어 인덱스에 포함되는 컬럼이 NULL을 포함하지 않도록 보장하거나 적절한 처리가 필요합니다.

4. 성능 저하 가능성

일부 경우에는 오히려 쿼리 성능을 저하시킬 수 있으므로 실행 계획(Explain Plan) 분석이 필수입니다.

복합 인덱스(Composite Index)

정의 및 특징

복합 인덱스는 둘 이상의 컬럼을 조합하여 생성하는 인덱스로, 다중 컬럼 인덱스라고도 합니다. 여러 조건을 동시에 사용하는 쿼리의 성능을 최적화하는 데 매우 효과적입니다.

주요 특징:

• 최대 16개 컬럼까지 포함 가능 (MySQL 기준)
• 컬럼 순서가 성능에 결정적 영향을 미침
• 정렬된 배열 구조로 연결된 값들로 구성
• B-tree 구조를 기반으로 함

생성 문법

-- 기본 복합 인덱스
CREATE INDEX idx_users_name 
ON users (first_name, last_name);

-- 유니크 복합 인덱스
CREATE UNIQUE INDEX st_man_ix 
ON stock (stock_num, manu_code);

-- 다중 컬럼 복합 인덱스 (최대 16개 컬럼)
CREATE INDEX idx_employee_complex
ON employees (department, salary, hire_date, status);

복합 인덱스의 성능 이점

1. 다중 조건 쿼리 최적화

복합 인덱스는 여러 컬럼을 동시에 필터링하는 쿼리에서 뛰어난 성능을 보입니다. 각 컬럼에 개별 인덱스를 생성하는 것보다 훨씬 효율적입니다.

2. 디스크 공간 절약

개별 단일 컬럼 인덱스들을 유지하는 것보다 디스크 공간을 절약할 수 있습니다.

3. 커버링 인덱스 효과

쿼리에서 선택하는 모든 컬럼이 인덱스에 포함되어 있으면 테이블에 접근하지 않고도 결과를 반환할 수 있어 성능이 크게 향상됩니다.

4. 범위 쿼리 지원

B-tree 구조의 특성상 범위 검색과 정렬된 데이터 검색에 최적화되어 있습니다.

복합 인덱스의 컬럼 순서 최적화

선택도(Selectivity) 우선 원칙

 

가장 선택적인 컬럼을 첫 번째로 배치하는 것이 일반적인 규칙입니다. 이는 인덱스 스캔 시 가장 빠르게 결과를 좁힐 수 있기 때문입니다.

 

-- 선택도가 높은 컬럼을 앞에 배치
CREATE INDEX idx_optimized 
ON employees (employee_id, department, status);
-- employee_id (고유값) > department > status 순으로 배치

쿼리 패턴 기반 최적화

실제 쿼리에서 가장 자주 사용되는 컬럼을 우선적으로 배치해야 합니다:

 

-- 자주 사용하는 쿼리: WHERE country = 'US' AND age > 30
CREATE INDEX idx_users_country_age 
ON users (country, age);

논리적 계층 구조 고려

데이터의 논리적 계층이 있는 경우, 계층 순서를 따라 인덱스를 구성하면 더 나은 성능을 얻을 수 있습니다.

복합 인덱스의 단점

1. 왼쪽 접두어 의존성

복합 인덱스는 왼쪽부터 순차적으로 사용되는 컬럼에서만 효과적입니다. 중간 컬럼을 건너뛰는 쿼리에서는 인덱스를 완전히 활용할 수 없습니다.

2. 유지보수 복잡성

DML 작업 시 모든 인덱스 컬럼을 고려해야 하므로 유지보수 오버헤드가 증가합니다.

3. 메모리 사용량 증가

복잡한 복합 인덱스는 상당한 메모리와 저장 공간을 요구합니다.

4. 잘못된 컬럼 순서의 위험

부적절한 컬럼 순서는 인덱스의 효과를 크게 감소시킬 수 있습니다.

성능 비교 및 선택 기준

함수 기반 인덱스 vs 일반 B-tree 인덱스

연구에 따르면 함수 기반 인덱스는 일반적으로 B-tree 인덱스보다 빠르지 않습니다. 성능 차이는 주로 다음 요인들에 의해 결정됩니다:

• 통계 정보의 정확성
• 함수 계산의 복잡도
• 데이터 분포
• 쿼리 패턴

언제 함수 기반 인덱스를 사용할까?

1. 함수를 사용하는 WHERE 조건이 빈번한 경우
2. 대소문자 구분 없는 검색이 필요한 경우
3. 복잡한 계산이나 변환이 포함된 쿼리
4. SELECT 문이 DML 문보다 훨씬 많은 경우

언제 복합 인덱스를 사용할까?

1. 여러 컬럼을 동시에 필터링하는 쿼리가 많은 경우
2. WHERE 절과 ORDER BY 절이 여러 컬럼을 사용하는 경우
3. 조인 연산에서 여러 컬럼이 조인 조건으로 사용되는 경우
4. 커버링 인덱스 효과를 노릴 수 있는 경우

모범 사례(Best Practices)

함수 기반 인덱스 모범 사례

 

1. 통계 정보 수집: 함수 기반 인덱스 생성 후 반드시 테이블 통계를 다시 수집하세요.

 

-- Oracle 예시
EXEC DBMS_STATS.gather_table_stats(USER, 'table_name', cascade => TRUE);

 

 

2. 실행 계획 확인: EXPLAIN PLAN을 통해 인덱스가 실제로 사용되는지 확인하세요.

복합 인덱스 모범 사례

1. 컬럼 순서 최적화: 가장 선택적인 컬럼을 첫 번째로 배치하세요.
2. 쿼리 패턴 분석: 실제 쿼리 패턴을 분석하여 인덱스를 설계하세요.
3. 왼쪽 접두어 활용: 복합 인덱스의 앞쪽 컬럼들만으로도 효과를 볼 수 있도록 설계하세요.
4. 적정 컬럼 수 유지: 너무 많은 컬럼을 포함하지 말고 실제 필요한 만큼만 포함하세요.

실제 성능 테스트 결과

PostgreSQL vs MySQL 비교 연구

최근 연구에서는 PostgreSQL과 MySQL의 B-tree 인덱스 성능을 비교했습니다:

주요 결과:

• 대용량 데이터셋에서는 MySQL(InnoDB)이 더 빠른 성능을 보였습니다
• 소규모 데이터셋에서는 PostgreSQL이 더 우수한 성능을 나타냈습니다
• 인덱스 사용 시 SELECT, UPDATE, DELETE 작업에서 6-10배 성능 향상이 확인되었습니다

메모리 사용량 분석

• 인덱스 사용 시 추가 메모리 사용량 증가
• PostgreSQL이 MySQL보다 더 많은 인덱스 저장 공간을 요구
• 인덱스 유지보수로 인한 INSERT 작업 성능 저하 발생

결론 및 권장사항

함수 기반 인덱스 권장사항

✅ 사용 권장 시나리오:

• 함수가 포함된 복잡한 WHERE 조건이 빈번
• 읽기 중심의 워크로드
• 대소문자 구분 없는 검색이 필요

❌ 사용 지양 시나리오:

• 쓰기 작업이 빈번한 테이블
• 단순한 컬럼 검색으로 충분한 경우
• 함수 계산 비용이 높은 경우

복합 인덱스 권장사항

✅ 사용 권장 시나리오:

• 다중 컬럼 조건을 사용하는 쿼리가 많음
• 조인 연산에서 여러 컬럼 활용
• 커버링 인덱스 효과를 기대할 수 있음

❌ 사용 지양 시나리오:

• 단일 컬럼 조건이 대부분인 경우
• 컬럼 순서를 예측하기 어려운 경우
• 너무 많은 컬럼 조합이 필요한 경우

최종 선택 기준

1. 워크로드 패턴 분석: 읽기 vs 쓰기 비율을 고려하세요
2. 쿼리 복잡도: 함수 사용 빈도와 다중 컬럼 조건 사용 빈도를 파악하세요
3. 성능 테스트: 실제 데이터로 성능 테스트를 수행하세요
4. 유지보수 비용: 인덱스 유지보수에 따른 DML 성능 영향을 고려하세요

올바른 인덱스 전략은 애플리케이션의 특성과 데이터 패턴을 충분히 이해한 후 결정해야 합니다. 함수 기반 인덱스와 복합 인덱스는 각각 고유한 장점이 있으므로, 상황에 따라 적절히 조합하여 사용하는 것이 최적의 성능을 달성하는 열쇠입니다.

 

 

이번 조사를 통해 데이터베이스에 배웠던 내용도 복습을 하게 되어 좋은 경험이 되었다.사실 이 부분이 어려워서 그 당시에는 대충 이해한 채로 시험과 대가를 동시에 치뤘던 거 같다.그리고 아직도 여전히 어렵다.하지만 트렌젝션과 마찬가지로 하다보면은 또 나중에는 지금보다 이 글을 더 많이 이해할 수 있는 날이 올거다.꾸준히 공부하자