인덱스 설계

이재호·2023년 3월 20일

3.4.1 인덱스 설계가 어려운 이유

인덱스를 마음껏 생성할 수 있다면, 인덱스 설계만큼 쉬운 일도 없다. 하지만 인덱스를 생성하다 보면 관리비용뿐만 아니라 시스템 부하를 증가시키는 요인이 된다.

DML(Data Manipulation Language)성능 저하(Transaction per Second 저하)
- 인덱스가 여섯 개 있으면, 신규 데이터를 입력할 때마다 여섯 개 인덱스에도 데이터를 입력
- 인덱스는 정렬상태를 유지하므로, 수직적 탐색을 통해 입력할 블록을 찾는다.
- 찾은 블록에 여유 공간이 없으면 인덱스 분할발생
- 데이터를 지울때는 여섯개 인덱스에서 레코드를 일일이 찾아 지워줘야 한다.
데이터베이스 사이즈 증가(디스크 공간 낭비)
데이터베이스 관리 및 운영 비용 상승

3.4.2 가장 중요한 두 가지 선택 기준

인덱스 스캔 방식 중 가장 정상적이고 일반적인 방식은 Index Range Scan이다.
1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정
2. 그렇게 선정한 컬럼 중 '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

3.4.3 스캔 효율성 이외의 판단 기준

수행 빈도
업무상 중요도
클러스터링 팩터
데이터량
DML부하(기존 인덱스 개수, 초당 DML발생량, 자주 갱신하는 컬럼 포함 여부 등)
저장 공간
인덱스 관리 비용

수행 빈도

위의 판단 기준 중에서 가장 중요한 것을 하나 꼽으라면 수행 빈도이다. 자주 수행하지 않는 SQL이면 인덱스 스캔 과정에서 약간의 비효율이 있어도 큰 문제가 아니다. 반면, 수행빈도가 매우 높은 SQL에는 앞서 설명한 공식을 이용해 최적의 인덱스를 구성해 줘야 한다.

특히 NL 조인할 때 Outer쪽에서 액세스하는 인덱스는 스캔 과정에 비효율이 있더라도 큰 문제가 아닐 수 있다.

select /*+ leading(a) use_nl(b) */
b.상품코드, b.상품명, a.고객번호, a.거래일자, a.거래량, a.거래금액
from 거래 a, 상품 b
where a.거래구분코드 = 'AC'
and a.거래일자 between '20090101' and '20090131'
and b.상품번호 = a.상품번호
and b.상품분류 = '가전'

위 SQL에서 거래쪽 인덱스를 <거래일자+거래구분코드> 순으로 구성하는 경우에, 거래 쪽 인덱스를 스캔하는 과정에 비효율이 있더라도 NL조인 매커니즘 상 비효율은 한 번에 그친다.

반대로, NL조인에서 Inner쪽 인덱스 스캔과정에 비효율이 있다면, 이는 성능에 큰 문제를 야기할 수 있다.

select /*+ leading(b) use_nl(a) */
b.상품코드, b.상품명, a.고객번호, a.거래일자, a.거래량, a.거래금액
from 거래 a, 상품 b
where a.거래구분코드 = 'AC'
and a.거래일자 between '20090101' and '20090131'
and b.상품번호 = a.상품번호
and b.상품분류 = '가전'

위 SQL에서 거래 쪽 인덱스를 <거래일자 + 상품번호 + 거래구분코드> 순으로 구성하는 경우가 그렇다.BETWEEN 조건 컬럼이 인덱스 선두 컬럼이므로 Outer 테이블로부터 액세스하는 횟수만큼 비효율적인 스캔을 반복한다.

데이터량

데이터량이 적다면 굳이 인덱스를 많이 만들 필요 없이 Full Scan으로도 충분히 빠르다.
반대로, 인덱스를 많이 만들어도 저장 공간이나 트랜잭션 부하 측면에서 그다지 문제될 것이 없다.

초대용량 테이블일 경우에는 DML 발생량이 TPS에 직접적인 영향을 주므로 전문가의 손길이 필요하다.

3.4.4 공식을 초월한 전략적 설계

조건절 패턴이 열 개 있을 때, 패턴마다 인덱스를 하나씩 만들 수는 없다. SQL튜닝 전문가라면, 열 개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최적 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야 한다.

만약 좌측의 6개 컬럼 중 하나를 선택하고 우측의 4개 일자 중 하나를 BETWEEN연산자를 사용해서 조회해야하는 경우가 있다면, 인덱스 스캔 효율을 위해서 '=' 연산자를 앞에두고 BETWEEN연산자를 뒤에 두려면 6*4의 인덱스가 필요하다.

이런 경우에 인덱스를 24개나 만드는 것 보다는, 우측 4개의 일자/일시 조건을 선두에 두고 자주 사용하는 필터 조건을 모두 뒤쪽에 추가하는 방식을 사용하는 것이 효율적이다.

X01: 청약일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점설계사 + 대리점지사
X02: 보험개시일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점설계사 + 대리점지사
X03: 보험종료일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점설계사 + 대리점지사
X04: 데이터생성일시 + 취급부서 + 취급지점 + 취급자 + 대리점설계사 + 대리점지사

일자 조회구간이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않다.
인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하요소다.

위 처럼 BETWEEN조건 컬럼을 선두에 두고 설계했으나 가장 많이 사용하는 패턴은 다음과 같이 따로 '='조건이 선두에 오도록 설계해서 인덱스를 하나 둔다.

X05: 입력자 + 데이터생성일시

공식대로 24개의 인덱스를 생성하는 것보다 위처럼 인덱스를 5개로 줄이는 것이 전략적이다.

3.4.5 소트 연산을 생략하기 위한 컬럼 추가

인덱스는 항상 정렬 상태를 유지하므로 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수 있게 해준다. 그래서 조건절에 사용하지 않는 컬럼이더라도 소트 연산을 생략할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수 있다.

'=' 조건절 컬럼은 어디에 두어도 상관없지만 아닌 조건절 컬럼들은 반드시 ORDER BY 컬럼보다 뒤쪽에 두어야 소트 연산을 생략할 수 있다.

'=' 연산자로 사용한 조건절 컬럼 선정
ORDER BY 절에 기술한 컬럼 추가
'=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

select 계약ID, 청약일자, 입력자ID, 계약상태코드, 보험시작일자, 보험종료일자
from 계약
where 취급지점ID = :trt_brch_id
and 청약일자 between :sbcp_dt1 and :sbcp_dt2
and 입력일자 >= trunc(sysdate - 3)
and 계약상태코드 in (:ctr_stat_cd1, :ctr_stat_cd2, :ctr_stat_cd3)
order by 청약일자, 입력자ID

이 공식에 따라 인덱스를 <취급지점ID + 청약일자 + 입력자ID> 순으로 구성한다.
입력일자와 계약상태코드는 뒤쪽에 붙여도 되고, 안 붙여도 된다. 이들 조건을 만족하는 데이터가 적으면 인덱스에 추가하는게 좋다. 테이블랜덤 액세스를 줄일 수 있기 때문이다.
이들 조건을 만족하는 데이터가 많으면, 굳이 인덱스에 추가하지 않아도 된다. 테이블에서 필터링할 때와 큰 성능차이가 없기 때문이다.
단 몇회라도 테이블 액세스를 줄이면 좋지만, 반대급부를 생각해야 한다.

IN 조건은 '='이 아니다.

select 고객번호, 고객명, 거주지역, 혈액형, 연령
from 고객
where 거주지역 = '서울'
and 혈액형 in ('A', 'O')
order by 연령

인덱스는 <거주지역 + 혈액형 + 연령> 순으로 구성했다.

거주지역	혈액형	연령
서울	A	23
서울	A	35
서울	A	48
서울	A	62
서울	O	29
서울	O	32
서울	O	45
서울	O	57

IN조건이 '='이 되려면 IN-List Iterator 방식으로 풀려야 한다. 아래처럼 풀면 IN조건이 '='이 됐지만, UNION ALL 위아래 두 집합을 묶어 '연령' 순으로 정렬하는 문제가 남는다.

select 고객번호, 고객명, 거주지역, 혈액형, 연령
from 고객
where 거주지역 = '서울'
and 혈액형 = 'A'
union all
select 고객번호, 고객명, 거주지역, 혈액형, 연령
where 거주지역 = '서울'
and 혈액형 = 'O'
order by 연령

소트 연산을 생략하려면, 위쪽 브랜치를 실행하고 이어서 아래쪽을 실행했을 때 그 결과가 연령 순으로 정렬돼야 한다. 그것이 가능하려면, 서울에 거주하는 모든 'A'형 고객이 'O'형 고객보다 연령이 낮아야 한다.

소트연산을 생략하려면 IN 조건절이 액세스 조건으로 사용하면 안되고, 필터조건으로 사용해야 한다.
인덱스는 <거주지역 + 연령 + 혈액형> 순으로 구성해야한다.

3.4.6 결합 인덱스 선택도

Reference
선택도 = 1 / Number of Distinct Values
카디널리티 = 총 로우 수 X 선택도 = 총 로우 수 / NDV
예를들어, 상품분류 컬럼에 '가전', '의류', '식음료', '생활용품' 네 개이 값이 있을 때, 아래 조건절에 대한 선택도는 25%다. 만약 전체 레코드가 10만 건이면, 카디널리티는 2만 5천이다.

WHERE 상품분류 = '가전'

선택도가 높은 (카디널리티가 높은) 인덱스는 생성해봐야 테이블 액세스가 많이 발생하기 때문에 효용가치가 별로 없다.
아래는 계약ID와 취급지점ID, 두 컬럼에 대한 카디널리티를 조회하는 쿼리다.

select count(*) as NDV, max(cnt) as MX_CARD, min(cnt) as MN_CARD, avg(cnt) as AVG_CARD
from (
    select 계약ID, 취급지점ID, count(*) as cnt
    from 계약조직
    where (계약ID is not null or 취급지점ID is not null)
    group by 계약ID, 취급지점ID
)

컬럼 순서 결정 시, 선택도 이슈

인덱스 생성여부를 결정할때는 선택도가 매우 중요하지만, 컬럼 간 순서를 결정할 때는 각 컬럼의 선택도보다 필수 조건 여부, 연산자 형태가 더 중요한 판단기준이 된다.

3.4.7 중복 인덱스 제거

완전 중복

X01 : 계약ID + 청약일자
X02 : 계약ID + 청약일자 + 보험개시일자
X03 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자

불완전 중복

X01 : 계약ID + 청약일자
X02 : 계약ID + 보험개시일자
X03 : 계약ID + 보험종료일자
X04 : 계약ID + 데이터생성일시

위 네개의 인덱스는 얼핏보기에 중복은 아니지만 계약ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 예를 들어, 계약ID 평균 카디널리티가 5라고 가정하고 계약ID를 '=' 조건으로 조회하면, 평균 다섯 건이 조회된다는 뜻이다. 그렇다면 아래와 같이 하나만 만들면 충분하다.