성능 좋은 인덱스란?
정의
먼저 인덱스란 인덱스 생성 컬럼 기준으로 미리 정렬을 해두는 것입니다.
미리 정렬시켜 놓은 인덱스를 잘 활용한다면 ORDER BY, GROUP BY 시 발생되는 정렬 연산(비용)을 생략하여 최적화된 쿼리 결과를 얻을 수 있습니다.
정렬 연산 생략, 결과집합 출력 시 필요한 범위만 스캔 가능한 인덱스를 성능 좋은 인덱스라 말합니다.
결과적으로 쿼리 튜닝의 핵심인 레코드 스캔 시 발생되는 랜덤 I/O를 줄이기 위해 성능 좋은 인덱스를 생성해야 하는 것입니다.
(해당 글은 인덱스 스캔 중 가장 성능이 좋은 인덱스 범위 스캔을 기준으로 작성됐습니다.)
예를 통해 성능 좋은 인덱스를 알아볼까요?
인덱스 성능 예시
아래와 같은 쿼리 조건으로 검색의 대부분이 이루어 진다고 가정
select *
from ex_table
where 지역= '서울'
and 성별 = 'M'
and 구매날짜 >='2024-01-01'
;
- 성능 좋은 인덱스: (지역, 성별, 구매 날짜) 순서로 생성되어 있는 경우
| 지역 | 성별 | 구매날짜 | 스캔여부 |
| 서울 | F | 2024.1.1 | |
| 서울 | F | 2024.1.2 | |
| 서울 | F | 2024.1.3 | |
| 서울 | M | 2024.1.1 | 1 |
| 서울 | M | 2024.1.2 | 2 |
| 서울 | M | 2024.1.3 | 3 |
| 경기 | F | 2024.1.1 | 4 |
| 경기 | F | 2024.1.2 | |
| 경기 | F | 2024.1.3 | |
| 경기 | M | 2024.1.1 | |
| 경기 | M | 2024.1.2 | |
| 경기 | M | 2024.1.3 |
- 성능 좋지 않은 인덱스: (구매날짜, 성별, 지역) 순서로 생성되어 있는 경우
| 구매날짜 | 성별 | 지역 | |
| 2024.1.1 | F | 서울 | |
| 2024.1.1 | F | 경기 | |
| 2024.1.1 | M | 서울 | 1 |
| 2024.1.1 | M | 경기 | 2 |
| 2024.1.2 | F | 서울 | 3 |
| 2024.1.2 | F | 경기 | 4 |
| 2024.1.2 | M | 서울 | 5 |
| 2024.1.2 | M | 경기 | 6 |
| 2024.1.3 | F | 서울 | 7 |
| 2024.1.3 | F | 경기 | 8 |
| 2024.1.3 | M | 서울 | 9 |
| 2024.1.3 | M | 경기 | 10 |
인덱스 생성 시 컬럼 순서 (특정 기준에 따라 순서의 우선순위 판단)
1. 등치 조건(=)의 컬럼
- 등치 조건(=)이 2개 이상인 경우 NDV(Number Distinct Value)가 높은 컬럼이 선행 컬럼이 오게끔 인덱스를 구성하는게 유리함
- NDV란? 선택도라고도 부르는데 컬럼의 고유의 값을 의미함. 예를 들어 계절 컬럼은 {봄, 여름, 가을, 겨울} 총 4개의 값만 존재하기 때문에 NDV가 4이다.
2. 정렬 컬럼 순서
- 등치 조건 컬럼 다음에 ORDER BY 절에 나온 컬럼 순서대로 인덱스 생성되어 있으면 정렬하기 위한 연산을 생략할 수 있음
3. 범위 조건의 컬럼
- 범위 조건 컬럼이 2개 이상인 경우 순서는 상관없음
결론
테이블마다 카디널리티가 다르기 때문에 모든 경우에서 적합한 이론은 아닙니다. 하지만 쿼리 성능을 높이기 위한 전략으로는 대부분 맞는 전략이라고 보시면 되어, 해당 우선순위에 따라 인덱스를 생성하여 쿼리를 실행하시면 성능 개선 효과를 얻을 수 있습니다.
참고로 우선순위에 해당되지 않는 컬럼은 인덱스 구성 컬럼에서 제외하셔야 합니다.
'데이터베이스' 카테고리의 다른 글
| [SQL] DB SQL 반복문 쿼리 recursive CTE (0) | 2024.08.23 |
|---|---|
| [SQL] 집합 연산자 실습 (MINUS, UNION, INTERSACT...) (0) | 2024.02.21 |
| [DB 설계] 테이블 명세서 양식 (0) | 2024.01.30 |
| [DB]날짜(Date)타입이 문자열(Character)보다 용량이 적은 이유? (0) | 2023.09.05 |
