SQL 튜닝 - 인덱스 설계방법

SQL 튜닝 - 인덱스 설계방법

 

온라인 트랜잭션 시스템에서 최적화된 인덱스 설계는 매우 중요합니다. 시스템에 필요한 모든 SQL 각각에 최적화된 인덱스를 생성할 수 있다면 좋겠지만 그렇게 인덱스를 생성하다 보면 테이블마다 인덱스가 수십 개씩 달려 관리비용가 시스템 부하가 증가됩니다.

 

과다한 인덱스가 시스템에 미치는 영향

• DML 성능저하
• 데이터베이스 사이즈 증가
• 데이터베이스 관리 및 운영 비용 상승

 

단적인 예로 하나의 테이블에 인덱스가 여섯 개 달려있으면, 신규 데이터를 입력할 때마다 여섯개의 인덱스에도 데이터가 추가돼야 합니다. 인덱스는 항상 정렬상태를 유지해야 하므로 수직적 탐색을 통해 신규 데이터를 입력할 블록을 찾아야 하고 찾은 블록에 여유 공간이 없으면 인덱스 분할도 발생합니다. 데이터를 지울 때도 마찬가지입니다. 여섯 개의 인덱스에서 일일이 레코드를 찾아 삭제해야 합니다. 과도한 인덱스는 트렌젝션의 속도 저하로 이어집니다. 결론적으로 개별 쿼리의 성능뿐 아니라 인덱스 개수를 최소화하여 DML 부하를 줄여야 하므로 인덱스 설계는 까다로운 작업입니다.

 

최적 인덱스 설계의 중요성

 

인덱스 수를 최소화하려면 기존 인덱스 구성을 변경해야하는데 인덱스 변경에 따른 시스템 변경 영향도가 크다면 변경 역시 쉽지 않습니다. 영향받는 SQL을 모두 찾아 성능을 검증해야 하기 때문입니다. 신규 인덱스를 추가하는 것은 영향도는 적지만 인덱스가 많아질수록 시스템의 전반적인 거래속도는 저하됩니다.인덱스 추가와 변경 모두 시스템에 악영향을 주는 요인이므로 시스템 개발 단계에서 인덱스를 최적으로 설계하는 것이 무엇보다 중요합니다.

 

스캔 효율성을 위한 인덱스 구성 컬럼 선택기준

 

가장 정상적이고 일반적인 인덱스 스캔방식은 INDEX RANGE SCAN 방식입니다. 이를 위해 인덱스 선두 칼럼을 조건절에 반드시 사용해야 합니다.따라서 결합 인덱스의 구성 컬럼을 선택하는 첫 번째 기준은, 조건절에 자주 사용하는 칼럼인지 여부입니다. 두 번째 기준은 등치조건(=)으로 자주 조회하는 칼럼을 앞쪽에 두는 것입니다.

 

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

 

자주 수행하는 SQL의 인덱스는 최적의 인덱스를 구성해야합니다. 수행빈도와 관련해 NL 조인 시 INNER 테이블의 인덱스에 비효율이 있다면 큰 성능문제를 야기할 수 있습니다. INNER 테이블 인덱스에 비효율이 있다면 OUTER 테이블에서 액세스 하는 횟수만큼 비효율적인 스캔을 반복하게 됩니다.

 

-- INNER 테이블 인덱스에 비효율이 있는경우
-- index_거래 [거래일자 + 상품번호 + 거래구분코드]
SELECT /*+leading(b) use_no(a)*/
b.상품코드, b.상품명, a.고객번호, a.거래일자, a.거래량, a.거래금액
FROM 거래 A, 상품 B
WHERE A.거래구분코드 = 'AC'
AND A.거래일자 BETWEEN '20090101' AND '20090131'
AND B.상품번호 = A.상품번호
AND B.상품번호 = '가전'

 

INNER 테이블인 A의 BETWEEN 조건컬럼이 인덱스 선두 칼럼이므로 OUTER 테이블로부터 액세스 하는 횟수만큼 비효율적인 스캔을 반복합니다. 수행빈도가 매우 높은 SQL이라면, 테스트 과정에서 성능이 좋게 나오더라도 인덱스를 최적으로 구성해야합니다. NL 조인 시, INNER 쪽 인덱스는 = 조건 칼럼이 인덱스 선두에 오는 것이 중요하고 될 수 있으면 테이블 액세스 없이 인덱스에서 필터링을 마치도록 구성해야 합니다.

 

테이블의 데이터량도 인덱스 설계시 중요한 판단 기준이 됩니다. 테이블의 레코드가 적다면 굳이 인덱스를 많이 만들 필요가 없습니다. TABLE FULL SCAN 만으로도 스캔속도가 충분하기 때문입니다. 반대로 초대용량 테이블의 경우 DML 발생량이 많기 때문에 인덱스의 수가 트랜잭션 성능에 직접적인 영향을 줍니다.

공식을 초월한 전략적 인덱스 설계

 

조건절 패턴 중 핵심적인 엑세스액세스 경로 한두 개를 전략적으로 선택해 최적 인덱스를 설계하고 나머지 액세스 경로는 약간의 비효율을 감수하더라도 목표성능을 만족하는 수준으로 인덱스를 구성해야 합니다. 인덱스 설계 공식에 의한 결정이 아닌 업무 상황을 이해하고 나름의 판단 기준을 가지고 결정을 내리는 것이 중요합니다.

 

보험사의 '가계약' 테이블이 있다고 가정합니다. 등치조건절에 쓰이는 컬럼은 취급부서, 취급점, 취급자, 입력자, 대리점설계사, 대리점지사 6개 칼럼이고 between 조건에 쓰이는 칼럼은 청약일자, 보험개시일자, 보험종료일자, 데이터생성일시 4개의 칼럼입니다. 모든 조건패턴을 만족하려면 총 24개의 인덱스가 필요합니다. 24개의 인덱스를 모두 생성할 수 없다면 전략적 판단이 필요합니다.공식과 반대로 between 조건절에 쓰이는 칼럼을 선두에 두고 등치 조건에 쓰이는 컬럼을 모두 뒤에 추가하는 방식을 고려해 볼 수 있습니다.

 

- X01 : 청약일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점 설계사 + 대리점 지사

- X02 : 보험개시일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점 설계사 + 대리점 지사

- X03 : 보험종료일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점 설계사 + 대리점 지사

- X04 : 데이터생성일시 + 취급부서 + 취급지점 + 취급자 + 대리점 설계사 + 대리점 지사

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

 

일자 조회구간이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않습니다. 인덱스 스캔 효율보다는 테이블 액세스가 더 큰 부하요소로 작용합니다. between 조건절 칼럼이 선두에 나온 이유는 가장 많이 사용되는 패턴이 입력자 + 데이터생성일시 조건이기 때문입니다. 이 페턴이 최적 스캔효율을 제공하면 다른 패턴에서 다소 비효율이 있어도 크게 지장이 없습니다.

 

공식대로 설계하면 24개의 인덱스가 필요하지만 업무상황을 고려한 전략적 판단을 통해 5개의 인덱스로 줄일 수 있습니다. 인덱스 개수를 최소화하면 사용빈도가 높거나 중요한 액세스 경로가 새로 도출됐을 때 최적의 인덱스를 추가할 여유가 생깁니다.

 

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

 

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

 

SELECT 계약ID, 청약일자, 입력자ID, 계약상태코드, 보험시작일자, 보험종료일자
FROM 계약
WHERE 취급지점ID = :trt_brch_id
AND 청약일자 between :sbcp_id1 and :sbcp_dt2
AND 입력일자  >= trun(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 컬럼보다 뒤에 둬야 소트연산을 생략할 수 있습니다. 취급지점ID + 청약일자 + 입력자 ID + 입력일자 + 계약상태코드로 인덱스를 구성할 수 있습니다. I/O 를 최소화하면서도 소트 연산을 생략하려면 아래 공식에 따라 인덱스를 구성해야 합니다.

 

연산자로 사용한 조건절 칼럼 선정

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

 

= 연산자가 아닌 조건절 칼럼인 입력일자와 계상상태코드는 인덱스 뒤편에 붙여도 되고 안 붙여도 됩니다. 이들 조건을 만족하는 인덱스 레코드가 많으면 굳이 인덱스에 추가하지 않아도 됩니다. 테이블에서 필터링할 때와 큰 성능에 차이가 없기 때문입니다. 인덱스에 포함된 칼럼이 많아지는 생기는 반대급부보다 효율이 적습니다.

 

계약상태코드가 등차 조건이 되려면 IN-LIST ITERATOR 방식으로 풀려야 합니다. UNION ALL로 풀리고 나면 ORDER BY에 문제가 생깁니다. UNION ALL의 위쪽 브랜치를 실행하고 이어 아래쪽 브랜치를 실행했을 때 정렬돼 있을 수가 없기 때문에 옵티마이저는 소트 연산을 생략하지 않습니다. 결론적으로 소트 연산이 생략되려면 IN 조건절은 IN-LIST-ITERATOR 방식으로 풀려선 안됩니다. 범위조건절로 필터 조건으로 사용돼야 합니다.

 

결합 인덱스 선택도

 

인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지 고려해야 합니다. 선택도란 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말합니다. 인덱스 선택도는 인덱스 칼럼을 모두 = 로 조회할 때 평균적으로 선택되는 비율을 말합니다. 선택도가 높은 인덱스는 효용가치가 낮습니다. 테이블 액세스가 많이 발생하기 때문입니다. 따라서 인덱스를 생성할 때는 반드시 선택도를 확인해야 합니다.

 

-- 계약ID, 취급지점ID 두 컬럼의 카디널리티를 조회하는 쿼리
SELECT COUNT(*) AS NDV, MAX(CNT) AS MX_CARD, MIN(CNT) MN_CARD, AVG(CNT) AS AVG_GARD
FROM
(    SELECT 계약ID, 취급지점ID, COUNT(*) AS CNT
    FROM 계약조직
    WHERE (계약ID IS NOT NULL OR 취급지점 IS NOT NULL)
    GROUP BY 계약ID, 취급지점ID
)

 

결합 인덱스의 구성칼럼을 선택할 때도 선택도가 낮은 컬럼을 앞에 두는 것이 유리하다고 알려져있지만 사실이 아닙니다. 고려해야할 것은 자주 사용하는 컬럼을 앞쪽에 두고 그중 = 조건을 최우선에 위치시키는 것입니다.

 

-- 조건절 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 조건으로 쓰입니다. 그리고 거래유형과 상품번호는 자주 사용하는 조건이 아닙니다. 여기서 고객등급과 고객번호 중 어떤 칼럼이 앞으로 오느냐는 인덱스 스캔효율에 전혀 영향을 주지 않습니다. 거래일자까지 인덱스 엑세스 조건이므로 인덱스 스캔범위는 동일합니다. 자주쓰이지 않는 컬럼인 거래유형과 상품번호 간에도 어떤 컬럼이 앞에 오는지가 인덱스 스캔효율에 영향을 주지 않습니다. 인덱스 스캔범위는 인덱스 액세스 컬럼(고객등급, 고객번호, 거래일자)에 의해 결정됩니다.

 

인덱스 액세스 칼럼(고객등급, 고객번호, 거래일자)에서 고객등급과 고객번호가 모두 등차조건이면 순서가 상관없지만 둘 중 하나이상의 컬럼이 조건절에서 누락되거나 범위검색 조건이면 순서가 상관이있습니다. 고객번호는 필수인데 고객등급이 조건절에서 누락되거나 범위검색 조건이 될 수 있다면 고객등급을 인덱스 선두에 배치하여 INDEX SKIP SCAN이나 IN-LIST-ITERATOR를 활용할 수 있습니다.

 

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

중복 인덱스 제거

 

아래 네 개의 인덱스는 물리적으로는 중복인덱스가 아니지만 계약 ID의 평균 카디널리티가 매우 낮다면 논리적으로는 중복 인덱스로 볼 수 있습니다. 예를 들어 계약 ID의 평균 카디널리티가 5라고 가정하면 계약 ID를 = 조건으로 조회할때 평균 다섯건이 조회된다는 의미입니다. 

 

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

 

후행 조건이 달라도 조회되는 데이터는 거의 유사하게 조회됩니다. 그렇다면 인덱스를 4개 씩 만들 이유가 없습니다.

 

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

 

중복제거실습 1

 

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

 

거래일자와 결제일자는 항상 BETWEEN 또는 부등호 조건으로 조회합니다. 다음은 각 칼럼의 데이터 분포입니다. NDV는 칼럼에 입력된 값의 종류의 개수를 의미합니다.

 

-- 다음은 각 컬럼의 데이터 분포이다. NDV는 컬럼에 입력된 값의 종류 개수를 의미한다

-- 컬럼명         NDV
-- 거래일자       2356
-- 관리지점번호    127
-- 일련번호       1850
-- 계좌번호       5956
-- 종목코드       1715
-- 결제일자       2356

 

거래일자가 항상 범위조건이면 N3과 N4의 후행 칼럼은 항상 인덱스 필터조건입니다. 그렇다면 인덱스를 두 개나 만들 필요가 없습니다. N4 인덱스를 제거하고 N3 인덱스 뒤쪽에 계좌번호를 추가합니다.

 

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

 

두 번째 방안으로 N3 인덱스에 칼럼추가 없이 그냥 N4 인덱스를 제거해도 됩니다. 계좌번호와 거래일자로 조회하거나 계좌번호 단독으로 조회할 때는 N1 인덱스를 사용하고, 거래일자만으로 조회할 때는 N3 인덱스를 사용하면 되기 때문입니다.

 

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

 

기존에 관리지점번호가 선두인 인덱스가 없었으므로 관리지점번호를 단독으로 조회하는 경우는 없었습니다. 그렇다고 PK 인덱스를 그대로 두면 관리자번호(=), 거래일자(BETWEEN)를 조건으로 조회할 때 비효율이 있습니다. 최종안으로 설계하면 관리지점번호와 거래일자로 조회할 땐 PK 인덱스, 거래일자만으로 조회할땐 N3 인덱스를 사용하면 됩니다.

 

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

 

중복제거 실습 2

 

인덱스 설계는 조건절 분석과정이 필수지만, 조건절 없이도 중복인덱스를 찾아내는 경우가 있습니다.

 

- PK : 주소 ID + 건물동번호 + 건물호번호 + 관리번호
- N1 : 상태구분코드 + 관리번호
- N2 : 관리번호
- N3 : 주소ID + 관리번호

 

-- 다음은 각 컬럼의 데이터 분포이다. NDV는 컬럼에 입력된 값의 종류 개수를 의미한다

-- 컬럼명         NDV
-- 주소ID       736000
-- 건물동번호    175
-- 건물호번호       3052
-- 관리번호       250782
-- 상태구분코드       3

 

상태구분코드는 NDV 3으로 선택도가 매우 높습니다. 상태구분코드로 조회할 때는 N1 인덱스를 사용하지 않고 TABLE FULL SCAN을 하는 것이 좋습니다. N1 인덱스를 사용하려면 상태구분코드와 관리번호를 같이 조회해야 합니다. N2는 관리번호로 조회할 때만 사용되므로 N1을 관리번호 + 상태구분코드 변경하여 N2와 통합하는 것이 좋습니다.

 

- PK : 주소 ID + 건물동번호 + 건물호번호 + 관리번호
- N1 : 관리번호 + 상태구분코드
- N3 : 주소ID + 관리번호

 

주의할 점은 상태구분코드가 NDV가 3개지만 특정 값은 변별력이 매우 좋을 수 있다는 것입니다. 만약 그 값으로 조회할 목적으로 N1인덱스를 만들었다면 인덱스 변경으로 문제가 생길 수 있습니다.

 

인덱스 설계도 작성

 

인덱스 설계 시에는 시스템 전체의 효율을 고려해야 합니다. 조화를 이룬 건축물을 짓기 위해 설계도가 필수인 것처럼 인덱스 설계에도 전체를 조망할 수 있는 설계도면이 필요합니다. 변경 전 인덱스 구성필드와 변경 후 인덱스 구성필드가 있습니다. 전자는 말 그대로 현재의 인덱스 구성을 딕셔너리에서 읽어 기록한 것이고 , 후자는 새로운 구성에 대한 전략입니다.

 

전채를 보면서 전략을 수립하려면 테이블별로 발생하는 실제 액세스 유형을 조사하는 과정이 필요한데 액세스 경로가 그것입니다. 설계도 상단에 파티션 구성을 기록하는 필드가 있습니다. 인덱스 설계 전에 파티션 설계를 먼저 진행하거나 최소한 병행해야 제대로 된 인덱스 전략을 수립할 수 있습니다.