인덱스 설계

인덱스 설계가 어려운 이유

SQL 각각에 최적화된 인덱스를 마음껏 생성할 수 있다면, SQL 튜닝과 인덱스 설계만큼 쉬운게 없다. 하지만 그렇게 인덱스를 생성하다 보면 테이블마다 인덱스가 수십 개씩 달리게 되고, 관리비용뿐만 아니라 시스템 부하를 증가시키는 요인이 된다. 인덱스가 많으면 아래와 같은 문제점이 생긴다.

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

예를 들면, 테이블에 인덱스가 6개가 달려있으면, 신규 데이터를 insert 할때마다 6개 인덱스 모두 데이터를 insert 해주어야 한다. 테이블과 달리 인덱스는 정렬이 되어 있기 때문에 수직적 탐색을 통해 입력할 블록부터 찾는다. 찾은 블록에 여유 공간이 없으면 인덱스 분할도 발생한다.

데이터를 지울때도 마찬가지다. 6개 인덱스에서 레코드를 일일이 찾아 지워줘야 한다. 핵심 트랜잭션이 참조하는 테이블에 대한 DML 성능 저하는 TPS 저하로 이어진다. 꼭 필요한 인덱스를 많이 만들면 디스크 공간을 낭비하게되고, 데이터베이스의 사이즈가 커지는 만큼 백업, 복제, 재구성 등을 위한 운영 비용 역시 상승하게 된다.

인덱스 개수를 최소화하려면 기존 인덱스 구성을 변경함으로써 문제를 해결해야 할 것이다. 하지만 인덱스 변경이 그리 쉽지 않다. 특히 개발 단계에서는 비교적 쉽게 인덱스를 변경할 수 있겠지만, 운영 단계에서는 인덱스 변경에 따른 시스템 변경 영향도가 매우 커서 쉽지 않다. 영향을 받는 SQL 모두 찾아 성능을 검증해야 하기 때문이다.

정리하자면, 인덱스 추가는 시스템에 부하를 주고, 인덱스 변경은 운영 리스크가 크다. 시스템 개발 단계에서 인덱스를 최적으로 설계하는 일이 무엇보다 중요한 이유가 바로 여기에 있다.

인덱스 구성 시 반드시 기억해야할 2가지..!!!

인덱스 스캔 방식에 여러 가지가 있지만, 가장 정상적이고 일반적인 방식은 INDEX RANGE SCAN 이다. 이를 위해서 아래 2가지를 명심하자.

1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
(선두 컬럼을 조건절에 반드시 사용해야한다.)
2. 선정한 컬럼 중 '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다.

공식처럼 외우자!!

하지만 무조건 위 공식처럼 인덱스를 설계하지는 않는다. 공식처럼 설계를 하다보면 인덱스 생성 갯수가 더 늘어나는 현상이 발생할 수도 있다.

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

위에서 설명한 공식은 인덱스 스캔 효율성 판단 기준이다. 그 외 고려해야 할 판단 기준도 많다.

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

가장 중요한 하나를 꼽자면, 수행 빈도이다.

자주 수행하지 않는 SQL이면 인덱스 스캔 과정에 약간의 비효율이 있어도 큰 문제가 아닐 수 있다. 반면, 수행 빈도가 매우 높은 SQL에는 앞서 설명한 공식을 이용해 최적의 인덱스를 구성해줘야 한다.

수행 빈도와 관련해, NL 조인할 때 어느 쪽에서 자주 액세스 되는지도 중요한 판단 기준이 된다.
Outer(드라이빙) 테이블 인덱스를 스캔하는 과정에 비효율이 있더라도 NL 조인 매커니즘 상 비효율은 한번에 그친다. 하지만 Inner 테이블의 인덱스 스캔 과정에 비효율이 있다고 하면, 성능에 큰 문제를 야기할 수 있다. Outer 테이블로부터 액세스하는 횟수만큼 비효율적인 스캔을 반복하기 때문이다.

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

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

초대용량 테이블일 경우는 어떨까?

초대용량 테이블은 Insert도 많다. 앞서 말했듯이, 초당 DML 발생량은 트랜잭션 성능 (TPS)에 직접적인 영향을 준다. 따라서 초대용량 테이블 인덱스 신중히 설계해야하고 전문가의 손길이 필요할 수 있다.

전략적 인덱스 설계

조건절 패턴이 만약 10개 정도 있다고 가정하면, 패턴마다 인덱스를 각각 하나씩 만들 수는 없다. 10 개중 최적을 달성해야할 가장 핵심적인 액세스 경로 한두개를 전략적으로 선택해서 최적 인덱스를 설계해야하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성해야한다. 그리고 왜 그런 선택을 했는지, 전략적 판단 근거가 무엇인지 답할 수 있어야 한다. 단순한 공식에 의한 결정이 아니라, 업무상황을 정확하게 이해하고 나름의 판단 기준을 가지고 결정을 내려야 한다.

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

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

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

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

예시

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

공식에 따라 생성한 인덱스는 [취급지점ID + 청약일자 + 입력자ID] 순으로 구성될 수 있다.

입력일자와 계약상태코드는 뒤쪽에 붙여도 되고 안붙여도 된다. 이들 조건을 만족하는 데이터가 적으면, 인덱스에 추가하는게 좋다. 테이블 랜덤 액세스를 줄일 수 있기 때문이다. 이들 조건을 만족하는 데이터가 많으면 굳이 인덱스에 추가하지 않아도 된다. 테이블 필터링 시, 큰 성능 차이가 없기 때문이다.

참고로 IN조건은 '='이 아니다. IN 조건절을 인덱스 액세스 조건으로 사용하면 안된다.

결합 인덱스 선택도

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

선택도(Selectivity) : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
카디널리티(Cardinality) : 선택도 * 총 레코드 수

인덱스 선택도는 인덱스 컬럼을 모두 '='로 조회할 때 평균적으로 선택되는 비율을 말한다.

선택도가 높은(카디널리티가 높은) 인덱스는 생성해봐야 효용가치가 없다. 테이블 액세스가 많이 발생하기 때문이다. 인덱스를 생성할 때 반드시 선택도/카디널리티를 확인해야 한다.

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

결합 인덱스 컬럼 간 순서를 정할 경우, 선택도가 중요할까?

결합 인덱스를 구성할 때 선택도가 낮은(변별력이 높은) 컬럼을 앞에 두는 것이 유리하다고 흔히 알려져있다. 하지만 이는 잘못된 오해다. '='를 사용하는 컬럼 중 어떤 컬럼이 앞쪽에 오든 인덱스 스캔 범위는 똑같기 때문에 스캔 효율에 영향을 주지 않는다.

인덱스를 설계할 때 우리가 할 일은 항상 사용하는 컬럼을 앞쪽에 두고 그 중 '=' 조건을 앞쪽에 위치시키는 것뿐이다.

그 중 선택도가 낮은 컬럼을 앞쪽에 두려는 노력은 의미없거나 오히려 손해일 수 있다.

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

중복 인덱스 제거

예시1)

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

X02가 X01를 완전 포함하고, X03 인덱스 선두 컬럼이 X01, X02 인덱스 전체를 포함해서 중복이다.

예시2)

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

위 4개의 인덱스는 얼핏보면 중복이 아닌 것 같지만, 만약 계약ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 예를 들면, 계약ID 평균 카디널리티가 5라고 가정하자. 계약ID를 '='조건으로 조회하면, 평균 다섯 건이 조회된다는 뜻이다. 그렇다면 이렇게 인덱스를 4개씩이나 만들 이유가 전혀 없다. 아래처럼 하나면 충분하다.

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

참고로 NDV(Number of Distinct Values)는 컬럼에 입력된 값의 종류 개수를 말한다.

NDV가 낮으면 선택도가 높다고 할 수 있다. 변별력이 없다는 뜻이다. 하지만 NDV가 낮지만 특정 값이 변별력이 좋을 수도 있다.

예시)
상태구분코드

• 00001 (100)
• 00002 (250000)
• 00003 (200000)

NDV = 3 으로 매우 낮다. 00001 값으로 조회할 경우 성능이 매우 빠를 수 있지만, 00002 나 00003으로 조회할 경우 성능이 느려질 수 있다.