친절한 SQL 튜닝 - 3장 인덱스 설계

3.4 인덱스 설계

• 온라인 트랜잭션을 처리하는 시스템에서 인덱스 설계의 중요성은 인덱스 튜닝, 더 나아가 SQL 튜닝의 하이라이트라고도 할 수 있다.

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

• SQL에 각각 최적화된 인덱스를 마음껏 생성할 수 있다면 인덱스 설계만큼 쉬운 일도 없다. 하지만 인덱스를 많으면 아래와 같은 문제가 발생한다.

1. DML 성능 저하 (TPS 저하)
2. 데이터베이스 사이즈 증가
3. 데이터베이스 관리 및 운영 비용 상승

• 예를 들어 한 테이블에 인덱스가 여섯 개 달려 있으면, 신규 데이터를 입력할 때마다 여섯 개 인덱스에도 데이터를 입력해야 한다.
• 테이블과 달리 인덱스는 정렬 상태를 유지해야 하므로 수직적 탐색을 통해 입력할 블록부터 찾는다.
• 데이터를 지울 때도 마찬가지이다. 여섯 개 인덱스에서 레코드를 일일이 찾아 지워야하고 이는 DML 성능 저하이며 TPS 저하로 이어진다.
• 또한 꼭 필요하지 않은 인덱스를 많이 만들면 디스크 공간을 낭비하고, 데이터베이스 사이즈가 커지는 만큼 백업, 복제, 재구성 등을 위한 운영 비용도 상승한다.

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

• 인덱스 스캔 방식에 여러 가지가 존재하지만, 가장 정상적이고 일반적인 방식은 Index Range Scan 이며 인덱스 선두 컬럼을 조건절에 반드시 사용해야 한다.
• 따라서 결합 인덱스를 구성할 떄 첫 번쨰 기준은, 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정하는 것이다.
• 두 번째 기준은 그렇게 선정한 컬럼 중 '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다는 것이다.
• 인덱스 설계할 때 기본적으로 두 가지 사항은 공식처럼 외우자.

1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
2. '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

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

• 위에서 제시한 두 가지 선택기준에만 해당하면 좋겠지만 그렇지만은 않다. 일반적으로 인덱스 개수가 더 늘어나는 현상도 경험하게 된다.
• 인덱스 스캔 효율성 이외에 고려해야 할 판단 기준은 아래와 같다.

1. 수행 빈도
2. 업무상 중요도
3. 클러스터링 팩터
4. 데이터량
5. DML부하
6. 저장 공간
7. 인덱스 관리 비용 등

• 위에 7가지 중에 가장 중요한 하나를 꼽으라면 수행 빈도이다.
• 수행빈도가 매우 높은 SQL 이라면, 테스트 과정에 당장 성능이 좋게 나오더라도 인덱스를 최적으로 구성해 줘야 한다.
• NL 조인 Inner 쪽 인덱스는 '=' 조건 컬럼을 선두에 두는 것이 중요하고, 될 수 있으면 테이블 액세스 없이 인덱스에서 필터링을 마치도록 구성해야 한다.
• 데이터량도 인덱스를 설계할 때 중요한 판단 기준이다. 데이터량이 적다면 굳이 인덱스를 많이 만들 필요가 없다. (Full Scan 으로도 충분히 빠르기 때문이다.)
• 반대로 초대용량 테이블일 때는 트랜잭션 성능에 직접적인 영향을 주기에 인덱스 설계할 때 가장 중요하다.

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

• 인덱스는 항상 정렬 상태를 유지하므로 ORDER BY, GROUP BY 를 위한 소트 연산을 생략할 수 있게 해준다.
• 조건절에 사용하지 않더라도 소트 연산 생략을 위한 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수도 있다.
• 성능을 고려하지 않는다면 인덱스는 ORDER BY 절 순서대로 구성하면 된다.
• 이 때 주의사항으로는 '=' 이 아닌 조건절 컬럼들은 반드시 ORDER BY 컬럼보다 뒤쪽에 두어야 소트 연산을 생략할 수 있다.
• 인덱스를 이렇게 구성하면 소트는 생략할 수는 있지만 조건을 만족하는 데이터를 맨 뒤쪽에서 만나면 많은 I/O가 발생할 것이다.

IN조건은 '='이 아니다

SELECT 고객번호, 고객명, 거주지역, 혈액형, 연령
FROM 고객
WHERE 거주지역 = '서울'
AND 혈액형 IN ('A', 'O')
ORDER BY 연령

• 위의 SQL을 예로 들어보자. 인덱스는 [거주지역 + 혈액형 + 연령] 순으로 구성되어있다.
• IN 조건이 '='가 되려면 IN-List Iterator 방식으로 풀려야 한다.
• UNION ALL 절로 두 집합을 묶어 연령순으로 정렬해야하기에 소트 연산을 생략하려면 IN 조건절을 인덱스 액세스 조건으로 사용하면 안 된다.
• 필터 조건으로 사용해야하므로 이 때는 인덱스를 [거주지역 + 연령 + 혈액형] 순으로 구성해야 한다.

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

• 선택도란 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율이다.
• 인덱스 선택도는 인덱스 컬럼을 모두 '=' 로 조회할 때 평균적으로 선택되는 비율을 의미한다.
• 인덱스를 설계할 때 하는 일은 항상 사용하는 컬럼을 앞쪽에 두고 그 중 '=' 조건을 앞쪽에 위치시키는 것 뿐이다.
• 결합 인덱스 컬럼 간 순서를 정할 때는 선택도는 중요하지 않다.

WHERE 성별 = :GENDER
AND 고객번호 = :CUST_NO

• 위의 SQL문에서 어떤 컬럼이 앞에 오든 인덱스 스캔 효율에는 전혀 차이가 없다는 말이다.
• 마찬가지로 뒤에 필터링 조건 BETWEEN 이나 IN 조건간에도 인덱스 스캔 효율에는 영향을 주지 않는다.

3.4.5 중복 인덱스 제거

• 아래 세 인덱스는 중복이다.

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

• X02 인덱스 선두 컬럼이 X01 인덱스 전체를 포함하고, X03 인덱스 선두 컬럼이 X01, X02 인덱스 전체를 완전히 포함하기 때문이다.
• 이를 완전 중복이라고 부르며, X03 인덱스를 남기고, X01, X02 인덱스는 삭제해도 된다.

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

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

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

• 이는 완전중복과 대비해 불완전 중복이라고 부른다.

중복 제거 실습1

• 아래 다섯 인덱스 중에서 중복 인덱스를 찾아 재설계해 보자.

1. PK : 거래일자 + 관리지점번호 + 일련번호
2. N1 : 계좌번호 + 거래일자
3. N2 : 결제일자 + 관리지점번호
4. N3 : 거래일자 + 종목코드
5. N4 : 거래일자 + 계좌번호

• 거래일자, 결제일자는 항상 BETWEEN 또는 부등호 조건으로 조회한다.
• NDV는 Number Of Distinct Values 의 약자이며 컬럼에 입력된 값의 종류 개수를 의미한다.
• 거래일자가 항상 BETWEEN 또는 부등호 조건이면 N3와 N4 인덱스는 둘 다 거래일자가 인덱스 액세스 조건이다.
• 그렇기에 인덱스를 두 개나 만들 필요 없이 N4 인덱스를 제거하고, N3 인덱스 뒤쪽에 계좌번호를 추가하면 된다.
• 두 번쨰 방안으로는 N3 인덱스 변경 없이 그냥 N4 인덱스를 제거해도 된다.