인덱스 설계

728x90

OLTP 시스템에서 인덱스 설계는 매우 중요.
인덱스튜닝, SQL튜닝의 하이라이트.

1. 인덱스 설계가 어려운 이유

인덱스가 많을경우 발생하는 문제
- DML성능 저하 (-> TPS 저하)
- 데이터 베이스 사이즈 증가(-> 디스크공간 낭비)
- 데이터베이스 관리 및 운영 비용 상승(백업, 복제, 재구성을 위한 운영비용)
한테이블에 인덱스 여섯개가 있으면, 신규 데이터 1건입력할때마다 여섯개 인덱스에도 데이터를 입력해야한다.
테이블과 다르게 인덱스는 정렬을 유지해야하므로, 수직적 탐색을 통해 입력할 블록부터 찾는다.
찾은 블록에 여유공간이 없으면 인덱스 분할(Index Split)도 발생한다.

개발단계 최적 인덱스 설계 중요성

운영중 인덱스 변경시에는 영향받는 SQL을 모두 찾아 성능을 검증해야한다.
시스템 개발단계에서는 비교적 쉽계 인덱스변경이 가능하지만, 운영환경에서는 일이커진다.
(금융권에선 불가능에 가깝다)
신규인덱스는 변경영향도가 적다, BUT 신규인덱스를 추가할수록 시스템수준 TPS가 나빠진다.

2. 가장 중요한 두 가지 선택 기준

가장 일반적인 방식은 INDEX RANGE SCAN, 이를위해 선두컬럼 조건절 사용이 필수.
So, 결합 인덱스 구성시 첫번째 기준은 조건절에 항상 사용하거나, 자주사용하는 컬럼을 선정
두번째 기준은 그렇게 선정한 컬럼중 등치(=)조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다.

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

그외 인덱스 설계시 고려해야할 항목

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

수행빈도가 중요한 고려항목중 하나

자주수행하지않는 SQL이면 인덱스스캔에 약간이 비효율이 있어도 큰문제는 아님.
수행빈도가 높은 SQL에서는 인덱스 설계 기준들을 이용해 최적의 인덱스를 구성해야한다 > 시스템전체성능과 관련
드라이빙테이블(OUTER TABLE)의 인덱스는 인덱스스캔 과정에 비효율이있더라도 큰문제가 아닐수 있지만
INNER쪽 테이블의 스캔과정에 비효율이 있을경우 성능에 큰 문제가 생길 수 있다
OUTER 테이블추출건수만큼 INNER 테이블에 액세스하기때문에 횟수만큼 비효율적 스캔을 반복한다.
수행빈도가 매우높은 SQL이라면 인덱스를 최적으로 구성해줘야한다
NL조인의 INNER쪽 인덱스는 등치조건 컬럼을 선두에 두는것이 중요하고, 될수있으면 테이블 액세스없이 인덱스에서 필터링을 마치도록 구성해야 한다.

데이터량

데이터량이 적다면 굳이 인덱스를 많이 만들필요가없다, FULL SCAN으로도 충분히 빠르기 때문
데이터량이 적을때 인덱스를 많이 만들어도 저장공간이나 트랜잭션 부하측면에서 문제될게없다
초대용량테이블은 INSERT도 많다. 초당DML발생량은 트랜잭션 성능(TPS)에 직접적 영향
이때 진정한 튜닝전문가가 빛을 발한다. 인덱스 하나를 줄였을때 시스템에 미치는 영향은 적지않다.

4. 공식을 초월한 전략적 설계

조건절 패턴이 10개일때, 최적을달성해야할 핵심적 액세스경로 1~2개를 선택해 최적인덱스를 설계하고
나머지 액세스경로는 약간 비효율이 있더라도 목표성능을 만족하는 수준으로 인덱스를 구성할 수 있어야한다.
업무상황을 이해하고 나름의 판단기준으로 왜그렇게 선택했는지 근거를 답할 수 있어야 한다.
인덱스 개수를 최소화하면, 사용빈도가 높거나 중요한 액세스경로가 새로 도출되었을때 최적의 인덱스를 추가할 여유도 생긴다.

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

인덱스는 항상 정렬상태를 유지하므로 ORDER BY, GROUP BY 를 위한 소트연산을 생략해준다.
조건절에 들어가지않는 컬럼이더라도 소트 연산을 생략할 목적으로 인덱스에 추가함으로써 성능개선을 도모할 수 있다.
등치조건이 아닌 조건절 컬럼들은 반드시 ORDER BY컬럼보다 뒤쪽에두어야 소트 연산을 생략할 수 있다.

I/O를 최소화하면서 소트연산을 생략하는 인덱스 구성 공식

등치조건 연산자로 사용한 조건절 컬럼 선정
ORDER BY 절에 기술한 컬럼 추가
등치(=) 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정.
(데이터가적은 조건절 컬럼은 인덱스에 추가하는게 좋다 > 테이블 랜덤 액세스를 줄일수 있기 때문
많으면 굳이 추가하지않아도됨 > 테이블에서 필터링할때와 큰 성능차이가 없음)

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

SELECT 고객번호, 고객명, 거주지역, 혈액형, 연령
FROM 고객
WHERE 거주지역 = '서울'
AND 혈액형 IN ('A','O')
ORDER BY 연령
--인덱스는 [거주지역+혈액형+연령]

IN조건이 등치조건이 되려면 IN-List Iterator방식으로 풀려야한다. (union all로 묶는것.)
그러면 in조건은 '='이 됐지만 union all위아래 두집합을 묶어 '연령'순으로 정렬하는 문제가 남는다.
소트연산을 생략하려면 union all위쪽 실행하고 이어 아래실행했을때 그 결과가 연령순으로 정렬되야한다.
그게 가능하려면 서울에 거주하는 모든 A형이 O형보다 어려아한다.
불가능한 일이므로 옵티마이저는 소트연산을 생략하지 않는다.
소트연산을 생략하려면 IN조건이 IN-List Iterator 방식으로 풀려선 안된다, 즉 IN조건절을 인덱스 액세스 조건으로 사용하면 안된다, 필터조건으로 사용해야한다, 따라서 인덱스를 [거주지역+연령+혈액형] 순으로 구성해야 한다

6. 결합 인덱스 선택도

인덱스 생성여부 결정시 선택도가 충분히 낮은지갖 중요한 판단기준
선택도란 전체레코드중 조건절에 의해 선택되는 레코드 비율
선택도에 총 레코드수를 곱해 카디널리티를 구한다.
인덱스 선택도는 인덱스컬럼을 모두 =로 조회할때 평균적으로 선택되는 비율
선택도가 높은 인덱스는 생성해봐야 효용가치가 별로 없다. 테이블 액세스가 많이 발생하기때문
아래는 계약 ID와 취급지점ID, 두컬럼에 대한 카디널리티를 조회하는 쿼리SELECT COUNT() AS NDV, MAX(CNT) AS MX_CARD, MIN(CNT) MN_CARD, AVG(CNT) AS AVG_CARD
FROM (
SELECT 계약ID, 취급지점ID, COUNT() CNT
FROM 계작조직
WHERE (계약ID IS NOT NULL OR 취급지점 IS NOT NULL)
GROUP BY 계약ID, 취급지점ID
)

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

인덱스설계시 항상 사용하는 컬럼을 앞쪽에 두고 그중 '='조건을 앞쪽에 위치시킨다.
그중 선택도가 낮은 컬럼을 앞쪽에 두려는 노력은 의미없거나 오히려 손해일 수 있따.
고객등급, 고객번호 둘다 필수 '='조건이면 순서는 크게상관없지만, 둘중하나이상이 조건절에서 누락되거나 범위검색 조건일 수 있으면 복잡해짐
고객번호는 필수인데 고객등급이 조건절에서 누락되거나 범위검색조건일수 있으면 고객등급을 앞쪽에 두는것이 유리 INDEX SKIP SCAN이나 IN-List조건을 활용할 수 있기 때문.
인덱스 생성여부를 결정할 때는 선택도가 매우중요, 컬럼간 순서를 결정할때는 선택도보다 필수조건여부, 연산자 형태가 더 중요한 판단기준, 어느컬럼을 앞에둘지는 상황에 따라 판단

7. 중복 인덱스 제거

P244 중복제거 실습 반복해서 해볼것
중복인덱스 제거방법
- 완전중복(한 인덱스의 컬럼이 다른인덱스의 컬럼을 모두 포함할때)일경우 인덱스 삭제
- 완전중복은아니지만 선두컬럼의 카디널리티가 매우 낮을때(불완전 중복)
  EX)선두컬럼으로 필터시 평균 5건정도가 조회된다면 후행컬럼을 덕지덕지붙여 여러 인덱스를 만들필요가없다.

8. 인덱스 설계도 작성

출처: https://velog.io/@tothek/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%84%A4%EA%B3%84

인덱스 설계가 어려운 이유

인덱스가 많으면 아래와 같은 문제가 생긴다

DML 성능 저하 (TPS 저하)
데이터베이스 사이즈 증가 (디스크 공간 낭비)
데이터베이스 관리 및 운영 비용 상승

테이블에 인덱스가 여러개 달려 있으면, 신규 데이터를 입력할 때마다 여러개의 인덱스에도 데이터를 입력해야 한다.

테이블과 달리 인덱스는 정렬 상태를 유지해야 하므로 수직적 탐색을 통해 입력할 브,ㄹ록부터 찾는다. 찾은 블록에 여유공간이 없으면 인덱스 분할(Index Split)도 일어난다.

테이블에 데이터가 변경될 때, 3개의 인덱스에도 똑같이 수정해준다.

핵심 트랜잭션이 참조하는 테이블에 대한 DML 성능 저하는 TPS 저하로 이어진다.
필요하지 않은 인덱스를 많이 만들면 디스크 공간을 낭비한다.
데이터베이스 사이즈가 커지면 백업, 복제, 재구성을 위한 운영 비용도 상승한다.

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

인덱스 스캔 방식에 가장 정상적인 방식은 Index Range Scan이다. 이를 사용하기 위해 선두컬럼을 조건절에 반드시 사용해야 한다.

결합 인덱스를 구성할 때, 조건절을 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
선정한 컬럼 중 ‘=’ 조건으로 자주 조회하는 컬럼을 앞쪽에 둬야 한다.

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

그 외 고려해야 할 기준

수행빈도 (저자 pick⭐)
- 자주 수행하지 않는 SQL은 비효율이 조금 있어도 큰 문제가 아니다.
- 자주 수행되는 SQL은 최적의 인덱스를 구성해줘야 한다.
업무상 중요도
클러스터링 팩터
데이터량
DML 부하
(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부 등)
저장공간
인덱스 관리 비용

수행빈도 예시

(사실 여기 이해를 못하겠음..)

수행빈도와 관련해, NL 조인할 때 어느 쪽에서 자주 액세스 되는지도 중요한 판단 기준이 된다.

SELECT /** leadling(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.상품번호 = ‘가전’

SELECT /** leadling(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이라면, 인덱스를 최적으로 구성해 줘야 한다.
NL 조인 Inner 쪽 인덱스는 ‘=’ 조건 컬럼을 선두에 두는 것이 중요
테이블 액세스 없이 인덱스에서 필터링을 마치도록 구성

데이터량도 인덱스를 설계할 때 중요한 판단 기준이다. 데이터량이 적다면 Full Scan으로도 충분히 빠르기 때문에 굳이 인덱스를 많이 만들 필요가 없다.

반대로, 인덱스를 많이 만들어도 저장 공간이나 트랜잭션 부하 측면에서 그다지 문제될 것이 없다. 테이블이 작으면, 심각하게 고민할 이유가 없다는 뜻이다.

초대용량 테이블은 INSERT도 많다. 초당 DML 발생량은 트랜잭션 성능(TPS)에 직접적인 영향을 준다.

NL 조인

SELECT /*+ ordered use_nl(e) */
   E.EMP_NO, E.ENAME, D.DNAME, E.JOP, E.SAL
FROM DEPT D, EMP E
WHERE E.DEPT_NO = D.DEPT_NO  --- [1]
    AND D.LOC = 'SEOUL'      --- [2]
    AND D.GB = '2'           --- [3]
    AND E.SAL >= 1500        --- [4]
ORDER BY SAL DESC;                   

Answer :: [2] -> [3] -> [1] -> [4]

공식을 초월한 전략적 설계

조건절 패턴이 10개가 있을때, 패턴마다 인덱스를 다 만들 수 없다.

10개중 최적을 달성해야 할 가장 핵심 액세스 경로 한두개를 선택해 최적 인덱스를 설계한다.

나머지 엑세스는 목표 성능을 만족하는 수준으로 인덱스 구성해야 한다.

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

인덱스는 항상 정렬상태라 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』로 구성하여 소트연산 생략

I/O를 최소화하면서도 소트 연산을 생략하려면, 아래 공식에 따라 인덱스를 구성하면 된다.

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

이 공식에 따라 위에서 제시한 SQL에는 인덱스를 『취급지점ID + 청약일자 + 입력자ID』 순으로 구성

결합 인덱스 선택도

인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준이다.

선택도(Selectivity)
- 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
- 카디널리티 = 선택도 * 총레코드 수
인덱스 선택도
- 인덱스 컬럼을 모두 ‘=’으로 조회할 때 평균적으로 선택되는 비율

인덱스를 생성할 때 선택도/카디널리티 를 확인해야 한다.

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

컬럼의 액세스 조건이라 어떤 컬럼이 먼저 오든 스캔범위는 같다.

인덱스 설계할 때..

항상 사용하는 컬럼을 앞쪽에 두기
그중 '=' 조건 앞쪽에 위치 시키는 것 뿐

인덱스 생성 여부를 결정할때는 선택도가 중요하다.

컬럼간 순서를 정할 때는 컬럼의 선택도보다는 ’필수조건 여부’, ‘연산자 형태’가 더 중요한 판단 기준이다.

중복 인덱스 제거

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

이 인덱스들은 중복이다. 포함 여부를 보면 X03 > X02 > X01 이기 때문에, X03을 남기고 나머지는 지워도 된다.

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

위의 인덱스들은 중복이 아니나, 계약 ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 이를 불완전 중복이라 부름

아래 하나만 만들면 충분하다.

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

실습1

거래일자, 결제일자는 항상 BETWEEN 또는 부등호로 조회됨

PK : 거래일자 + 관리지점번호 + 인련번호
N1 : 계좌번호 + 거래일자
N2 : 결제일자 + 관리지점번호
N3 : 거래일자 + 종목코드
N4 : 거래일자 + 계좌번호

거래일자2,356

관리지점번호	127
일련번호	1,850
계좌번호	5,956
종목코드	1,715
결제일자	2,356
- 정답1

거래일자가 항상 BETWEEN이면 N3, N4는 둘다 거래일자가 액세스 조건임. 그래서 인덱스를 2개나 만들 필요 없다. N3, N4를 합쳐준다.

- N4 삭제
- N3 : `거래일자 + 종목코드 + 계좌번호` 로 수정

- PK : 거래일자 + 관리지점번호 + 인련번호
- N1 : 계좌번호 + 거래일자
- N2 : 결제일자 + 관리지점번호
- **N3 : 거래일자 + 종목코드 + 계좌번호**
- ~~N4 : 거래일자 + 계좌번호~~

정답2하지만 관리지점번호가 선두인 인덱스가 없어 PK를 관리지점, 거래일자로 조회하는 인덱스로 사용한다.
- PK : 관리지점번호 + 거래일자 + 인련번호
- N1 : 계좌번호 + 거래일자
- N2 : 결제일자 + 관리지점번호
- N3 : 거래일자 + 종목코드
- ~N4 : 거래일자 + 계좌번호~
N4를 삭제하고 N3를 그대로 둔다. 계좌번호 관련 조회는 N1을 사용한다.

실습2

PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호
N1 : 상태구분코드 + 관리번호
N2 : 관리번호
N3 : 주소ID + 관리번호
정답그렇게 되면 N2는 중복이 되므로 삭제한다.
- PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호
- N1 : 관리번호 + 상태구분코드
- N2 : ~관리번호~
- N3 : 주소ID + 관리번호
상태구분코드는 NDV가 낮아 N1인덱스가 사용되지 않음. 그래서 관리번호랑 상태구분코드의 순서를 바꿔주는게 좋다.

인덱스 설계도 작성

인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 조화를 이룬 건축물을 짓기 위해 설계도가 필수인 것처럼 인덱스 설계 시에도 전체를 조망할 수 있는 설계도면이 필요한 이유다.

)