인덱스 설계
1. 인덱스 설계가 어려운 이유
인덱스가 많으면, 다음과 같은 문제가 생긴다.
- DML 성능 저하 (->TPS 저하)
- 데이터베이스 사이즈 증가 (-> 디스크 공간 낭비)
- 데이테베이스 관리 및 운영 비용 상승
위와 같이 테이블에 인덱스가 여섯 개 달려 있으면, 신규 데이터를 입력할 때마다 여섯 개 인덱스도 데이터를 입력해야 한다. 테이블과 달리 인덱스는 정렬 상태를 유지해야 하므로 수직적 탐색을 통해 블록을 찾는다.
테이터를 지울 때도 마찬가지다. 여섯 개 인덱스에서 레코드를 일일이 찾아 지워줘야 한다.
2. 가장 중요한 두 가지 선택 기준
- 조건절에 항상 사용하거나 자주 사용하는 컬럼을 선정해야한다.
- 그렇지 않은 컬럼 중 = 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다.
3. 스캔 효율성 이외의 판단 기준
- 수행빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML부하
- 저장공간
- 인덱스 관리 비용
이 중 가장 중요한 요소 중 하나는 수행 빈도이다. 자주 수행하지 않는 SQL이면 인덱스 스캔 과정에 약간 비효율이 있어도 큰 문제가 아닐 수 있다. 반면, 수행빈도가 매우 높은 SQL에는 앞서 설명한 공식을 이용해 최적의 인덱스를 구성해줘야한다.
수행빈도와 관련하여, NL조인할 때 어느 쪽에서 자주 엑세스 되는지도 중요한 판단기준이다.
NL조인은 Inner의 인덱스가 굉장히 중요하다.
수행빈도가 매우 높은 SQL이라면, 인덱스를 최적으로 구성해 줘야 한다. NL 조인 Inner 쪽 인덱스는 = 조건 컬럼을 선두에 두는 것이 중요하고, 될 수 있으면 테이블 엑세스 없이 인덱스에서 필터링을 마치도록 구성해야한다.
데이터량도 인덱스를 설계할 때 중요한 판단 기준이다. 데이터량이 적다면 굳이 인덱스를 많이 만들 필요가 없다.
Full Scan으로 충분히 빠르기 때문이다
4. 공식을 초월한 전략적 설계
인덱스를 설계할 때 업무 상황을 이해하고 나름의 판단 기준을 가지고 결정을 내리는 것이 중요하다.
ex)
보험사에 가계약 테이블이 있다. 가계약 목록을 조회할 때 다양한 방식을 사용할 수 있다.
이런 상황에서 인덱스 스캔 효율을 위해 = 조건 컬럼을 앞에 BETWEEN 조건 컬럼을 뒤에 두려면 24개 인덱스가 필요하다.
이때 다음과 같이 설계할 수도 있다. 일자/일시 조건을 선두에 두고, 자주 사용하는 필터 조건은 모두 뒤쪽에 추가하는 방식이다.
설계의 핵심
- 일자 조회구간이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않다.
- 인덱스 스캔 효율보다 테이블 엑세스가 더 큰 부하요소다.
- 가계약은 주로 최근 3일 이내 데이터를 조회한다.
- 가계약 테이블은 다양한 패턴으로 조회하지만, 가장 많이 사용하는 패턴은 입력자 =, 데이터 생성일시 BETWEEN 조건이다
- 따라서 해당 인덱스를 하나 추가 (입력자 + 데이터 생성일시)하면 다섯 개로 줄였다.
5. 소트 연산을 생략하기 위한 컬럼 추가
인덱스는 항상 정렬 상태를 유지하므로, ORDER BY, GROUP 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
성능을 고려하지 않아도 된다면, ORDER BY절 순서대로 청약일자 + 입력자ID로 구성할 수 있다.
= 조건절은 ORDER BY에 없더라도 인덱스 구성에 포함할 수 있다. 따라서 취급지점이 포함된
청약일자 + 취급지점ID + 입력자ID 순으로 구성해도 소트 연산을 생략할 수 있다는 뜻이다. (앞뒤 중간 어디에 두어도 상관없다)
=이 아닌 조건절 컬럼은 반드시 ORDER BY 컬럼보다 뒤쪽에 두어야 한다.
이와같이 인덱스를 구성하면, 청약일자가 범위조건이므로, 나머지는 필터 조건으로 분류되고 인덱스를 너무 많이 탐색하게 될 수도 있다.
I/O를 최소화하면서 소트 연산을 생략하려면, 아래 공식을 따르자
- = 연산자로 사용한 조건절 컬럼 선정
- ORDER BY절에 기술한 컬럼 추가
- = 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정
위 공식에 따라 [취급지점ID+ 청약일자 +입력자ID]순으로 구성할 수 있다.
입력일자와 계약상태코드는 뒤쪽에 붙여도 되고 안 붙여도 된다. 이들 조건을 만족하는 데이터가 적으면 인덱스에 추가하는 게 좋다.
6. IN 조건은 =이 아니다.
select 고객번호, 고객명, 거주지역, 혈액형 , 연력
from 고객
where 거주지역 = '서울'
and 혈액형 in ('A','O')
order by 연령
IN 조건이 =이 되려면 IN-List Iterator 방식으로 풀려야한다. (이는 UNION ALL로 풀린다는 말이다)
소트 연산을 생략하려면, 각 UNION ALL의 결과가 연령 순으로 정렬돼야 한다. 이는 가능하기 쉽지 않다. 따라서 옵티마이저는 소트 연산을 생략하지 않는다. 소트 연산을 생략하려면 IN-List Itrator 방식으로 풀면 안되고, IN 조건절이 인덱스 필터 조건으로 사용되도록, 만들어야한다. 따라서 인덱스는 [거주지역 + 연령 + 혈액형]으로 구성해주자
(혈액형의 선두컬럼이 비어있음으로, 필터 조건으로 사용)
7. 결합 인덱스 선택도
인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준이다.
선택도란 전체 레코드 중 조건절에 의해 선택되는 레코드 비율을 의미한다 (즉 많은 데이터 중 작은 일부)
선택도에 총 레코드 수를 곱해서 카디널리티를 구한다.
인덱스 선택도는 인덱스 컬럼을 모두 =으로 조회할 때 평균적으로 선택되는 비율을 의미한다.
선택도가 높은 인덱스는 생성해봐야 효용가치가 별로 없다.
따라서 인덱스를 생성할 때는 반드시 선택도/카디널리티를 확인해야한다.
7.1 컬럼 순서 결정시, 선택도 이슈
결합 인덱스 컬럼 간 순서를 정할 때도 선택도가 중요할까?
결론적으로 딱히 그렇지는 않다. 인덱스를 설계할 때 우리가 할 일은 항상 사용하는 컬럼을 앞쪽에 두고 그 중 = 조건을 앞쪽에 위치시키는 것뿐이다.
ex) 다음 4개의 조건절이 있다고 해보자
--조건절 1
WHERE 고객등급 = :V1
AND 고객번호 = :V2
AND 거래일자 >= :V3
--조건절 2
WHERE 고객등급 = :V1
AND 고객번호 = :V2
AND 거래일자 >= :V3
AND 거래유형 = :V4
--조건절 3
WHERE 고객등급 = :V1
AND 고객번호 = :V2
AND 거래일자 >= :V3
AND 상품번호 = :V5
--조건절 4
WHERE 고객등급 = :V1
AND 고객번호 = :V2
AND 거래일자 >= :V3
AND 거래유형 = :V4
AND 상품번호 = :V5
여기서 항상 사용하는 고객번호, 고객등급, 거래일자 중 고객번호와 고객등급은 = 조건, 거래일자는 BETWEEN 조건이다.
거래유형과 상품번호는 항상 사용하는 조건은 아니다.
고객등급과 고객번호는 자리를 바꾸어도 스캔에 영향이 없다
거래유형과 상품번호는 바꾸어도 영향이 없다.
결론적으로, 인덱스 생성 여부를 결정할 때는 선택도가 매우 중요하지만, 컬럼 순서를 결정할 때는 선택도보다
필수 조건 여부, 연산자 형태가 더 중요한 판단 기준이다.
8. 중복 인덱스 제거
- 완전중복
X02 인덱스 선두 컬럼이 X01 인덱스 전체를 완전히 포함 X03 인덱스 선두 컬럼이 X01과 X02 인덱스를 완전히 포함한다.
따라서 완전 중복이므로, X03 인덱스를 남기고 X01, X02는 삭제해도 된다.
- X01 : 계약ID+청약일자
- X02 : 계약ID+청약일자+보험개시일자
- X03 : 계약ID+청약일자+보험개시일자+보험종료일자
-불완전중복
- X01 : 계약ID+청약일자
- X02 : 계약ID+보험개시일자
- X03 : 계약ID+보험종료일자
- X03 : 계약ID+데이터생성일시
얼핏보면 중복이 아니지만, 계약ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 이미 계약ID 조건으로 적은양을 탐색할 수 있다면 굳이 인덱스를 여러개 만들 필요가 없다.
- X01 : 계약ID+청약일자+보험개시일자+보험종료일자+데이터생성일시
8.1 중복제거 실습1
PK: 거래일자 + 관리지점번호 + 일련번호
N1 : 계좌번호 + 거래일자
N2 : 결제일자 + 관리지점번호
N3 : 거래일자 + 종목코드
N4 : 거래일자 + 계좌번호
거래일자, 결제일자는 항상 BETWEEN 또는 부등호 조건으로 조회한다.
각 칼럼의 데이터 분포는 다음과 같고 NDV는 칼럼에 입력된 값의 종류 개수를 의미한다.
거래일자가 항상 BETWEEN 또는 부등호 조건이라면 N3와 N4 인덱스는 둘 다 거래일자가 엑세스 조건이다. 그렇다면 인덱스를 두 개나 만들 필요가 없다. N4를 제거하고, N3 뒤쪽에 계좌번호를 추가하면 된다.
PK: 거래일자 + 관리지점번호 + 일련번호
N1 : 계좌번호 + 거래일자
N2 : 결제일자 + 관리지점번호
N3 : 거래일자 + 종목코드 + 계좌번호
최종적으로 다음과 같이 설계할 수도 있다.
PK: 관리지점번호+ 거래일자 + 일련번호
N1 : 계좌번호 + 거래일자
N2 : 결제일자 + 관리지점번호
N3 : 거래일자 + 종목코드 + 계좌번호
만약 거래일자가 BTWEEN이고, 관리지점번호가 = 조건이면 비효율이 발생하므로, 순서를 바꾸자
8.2 중복제거 실습2
조건절 없이도 중복 인덱스를 찾아내는 경우가 있다. 아래에서 중복 인덱스를 찾아 재설계해 보자.
PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호
N1 : 상태구분코드 + 관리번호
N2 : 관리번호
N3 : 주소ID + 관리번호
상태구분코드는 NDV가 3이므로 선택도가 매우 높다 -> 상태구분코드로만 조회할 때는 N1인덱스가 필요없다 N1인덱스가 사용되려면, 상태구분코드와 관리번호를 항상 같이 조회해야 의미가 있다.
N2 인덱스는 관리번호 조회용도만 있음으로, 이를 삭제하고, N1 인덱스를 관리번호+ 상태구분코드 순으로 변경할 수 있다.
PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호
N1 : 관리번호 + 상태구분코드
N3 : 주소ID + 관리번호