친절한 SQL 튜닝_3장.인덱스 튜닝_4

인덱스 튜닝

 

3.4 인덱스 설계

 

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

 

인덱스가 많으면 구첵적으로 아래와 같은 문제가 생긴다.

 

- DML 성능 저하(-> TPS 저하)

 

- 데이터베이스 사이즈 증가(-> 디스크 공간 낭비)

 

- 데이터베이스 관리 및 운영 비용 상승

 

테이블에 인덱스가 여섯 개 달려 있으면, 신규 데이터를 입력할 때마다 여섯 개 인덱스에도 데이터를 입력해야 한다. 테이블과 달리 인덱스는 정렬 상태를 유지해야 하므로 수직적 탐색을 통해 입력할 블록부터 찾는다, 찾은 블록에 여유 공간이 없으면 인덱스 분할(Index Split)도 발생한다.

 

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

 

 

 

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

 

1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.

 

2. ‘=’ 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

 

 

 

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

 

- 수행 빈도

 

- 업무상 중요도

 

- 클러스터링 팩터

 

- 데이터량

 

- DML 부하(=기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부 등)

 

- 저장 공간

 

- 인덱스 관리 비용 등

 

수행빈도와 관련해, NL 조인할 때 어느 쪽에서 자주 액세스 되는지도 중요한 판단 기준이 된다. NL 방식으로 조인하는 두 테이블이 있을 때, 1번과 2번 중 어느 쪽 인덱스가 더 중요할까?

 

NL 조인할 때 Outer 쪽(드라이빙 집합)에서 액세스하는 인덱스(1번)는 스캔 과정에 비효율이 있더라도 큰 문제가 아닐 수 있다. 예를 들어, 아래 SQL(힌트 주목)에서 거래 쪽 인덱스를 『거래일자 + 거래구분코드』 순으로 구성하는 경우를 말한다.

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.상품번호 = ‘가전’

거래 쪽 인덱스를 스캔하는 과정에 비효율이 있더라고 NL 조인 메커니즘 상 비효율은 한 번에 그친다. 불필요한 테이블 액세스는 발행하지 않으므로 아주 넓은 거래일자 구간으로 조회하지만 않는다면 성능도 비교적 나쁘지 않을 것이다. 당장 조회 성능에 별문제가 없고 자주 수행하는 SQL이 아니라면 굳이 스캔 효율을 높이기 위해 『거래구분코드 + 거래일자』 인덱스를 따로 안 만들어도 된다.

 

반대로, NL 조인에서 Inner 쪽 인덱스(2번) 스캔 과정에 비효율이 있다면, 이는 성능에 큰 문제를 야기할 수 있다. 아래 SQL(힌트 주목)에서 거래 쪽 인덱스를 『거래일자 + 상품번호 + 거래구분코드』 순으로 구성하는 경우가 그렇다. BETWEEN 조건 컬럼이 인덱스 선두 컬럼이므로 Outer 테이블로부터 액세스하는 횟수만큼 비효율적인 스캔을 반복한다.

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)에 직접적인 영향을 준다.

 

 

 

3.4.4 공식을 초월한 전략적 설계

 

SQL 튜닝 전문가라면, 열 개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최저 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야 한다.

 

예를 들어 보자. 어떤 보험사에 ‘가계약’ 테이블이 있다. 가계약 목록을 조회할 때 다양한 방식으로 조회한다. 우선, 드롭다운 리스트(콤보박스)에서 취급부서, 취급지점, 취급자, 입력자, 대리점설계사, 대리점지사 중 하나를 선택한다. 조건절 연산자는 ‘=’이다. 그리고 우측에 있는 네 개 일자/일시 중 하나를 선택한다. 조건절 연산자는 BETWEEN이다. 선택한 두 항목에 대한 값을 입력하고 조회 버튼을 누른다.

 

이런 상황에서 인덱스 스캔 효율을 위해 ‘=’ 조건 컬럼을 앞에, BETWEEN 조건 컬럼을 뒤에 두려면 24개 인덱스가 필요하다. 가계약 테이블에는 INSERT가 매우 많이 발생하는데, 과연 인덱스를 24개나 만들 것인가? 전략적인 판단이 필요하다.

 

아래와 같이 설계하면 어떨까? 일자/일시 조건을 선두에 두고, 자주 사용하는 필터 조건을 모두 뒤쪽에 추가하는 방식이다.

 

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

 

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

 

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

 

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

 

이렇게 설계한 핵심 포인트는 두 가지다. 첫째, 일자 조회구간이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않다는 점이다. 둘째, 인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하요소라는 점이다.

 

가계약은 주로 최근 3일 이내 데이터를 조회한다. 대개는 전일자로 조회하므로 인덱스 스캔량은 그리 많지 않다. 가끔 3일을 초과한 기간으로 조회할 수 있고, 어쩌다 한 달 치를 조회할 수도 있다. 그렇더라도 불필요한 테이블 액세스는 전혀 발생하지 않도록 설계했으므로 사용자가 인내할 수 있는 수준의 성능은 낼 수 있다.

 

사용자가 인내할 수 있는 수준의 인덱스 스캔 비효율이더라도 BETWEEN 조건 컬럼을 선두에 두고 설계하는 것은 좀 찜찜하다. 그런데도 이런 결정을 한 이유는 따로 있다. 가계약 데이블을 다양한 패턴으로 조회하지만, 그중 가장 많이 사용(85% 이상)하는 패턴은 입력자 ‘=’, 데이터생성일시 BETWEEN 조건이기 때문이다. 따라서 이 패턴에 최적의 스캔 효율을 제공하면, 다른 패턴에 다소 비효율이 있어도 업무에 크게 지장이 없다고 판단한 것이다.

 

앞서 설계한 네 개 인덱스에 아래 인덱스를 하나 더 추가하자, (X04 인덱스에 입력자 컬럼은 누락된 것이 아니라 이 인덱스가 있어서 일부러 뺐다.)

 

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

 

 

 

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

 

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

 

아래 쿼리에 ORDER 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 절에 없더라도 인덱스 구성에 포함할 수 있다. 위 SQL에선 취급지점ID가 ‘=’조건이다. 이를 포함해 『청약일자 + 취급지점ID + 입력자ID』 순으로 구성해도 소트 연산을 생략할 수 있다는 뜻이다. 위치는 중간 어디에 두어도 상관없다.

 

‘=’이 아닌 조건절 컬럼들은 반드시 ORDER BY 컬럼보다 뒤쪽에 두어야(예를 들어, 청약일자 + 입력자ID + 입력일자 + 계약상태코드) 소트 연산을 생략할 수 있다.

 

인덱스를 그렇게 구성하면 일단 소트는 생략할 수 있다. 문제는 성능인데, 조건을 만족하는 데이터를 빨리 만날 수 있느냐가 관건이다. 다행히 앞쪽에서 만나면 결과집합이 빨리 출력되기 시작하겠지만, 불행하게도 맨 뒤쪽에서 만나면 사용자는 그 때까지 손 놓고 기다려야만 한다. 그 순간 DBMS 내부에서는 많은 I/O가 발생하고 있을 것이다.

 

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

 

1. ‘=’ 연산자로 사용한 조건절 컬럼 선정

 

2. ORDER BY 절에 기술한 컬럼 추가

 

3. ‘=’ 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

 

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

 

 

 

3.4.6 결합 인덱스 선택도

 

인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준이다. ‘선택도(Selectivity)’란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하며, 선택도에 총 레코드 수를 곱해서 ‘카디널리터’를 구한다.

 

‘인덱스 선택도’는 인덱스 컬럼을 모두 ‘=’로 조회할 때 평균적으로 선택되는 비율을 의미한다. 선택도가 높은(카디널리티가 높은) 인덱스는 생성해봐야 효용가치가 별로 없다. 테이블 액세스가 많이 발생하기 때문이다.

 

 

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

 

결합 인덱스 컬럼 간 순서를 정할 때도 선택도가 중요할까? 결합 인덱스를 구성할 때 선택도가 낮은(변별력이 높은) 컬럼을 앞에 두는 것이 유리하다고 흔히 알려져서 하는 질문이다.

 

구체적인 예로 아래 쿼리에서 고객번호를 앞에 두는 것이 유리하다고 생각하겠지만, 그렇지 않다. 성별과 고객번호 중 어떤 컬럼이 앞으로 오든 인덱스 스캔 효율에 전혀 차이가 없다. 둘 다 인덱스 액세스 조건이므로 어떤 컬럼이 앞으로 오든 인덱스 스캔 범위는 똑같다.

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

인덱스 설계할 때 우리가 할 일은 ‘항상 사용하는’ 컬럼을 앞쪽에 두고 그 중 ‘=’ 조건을 앞쪽에 위치시키는 것뿐이다. 그중 선택도가 낮은 컬럼을 앞쪽에 두려는 노력은 의미 없거나 오히려 손해일 수 있다.

 

 

 

 

 

3.4.7 중복 인덱스 제거

 

아래 세 인덱스는 중복이다. X02 인덱스 선두 컬럼이 X01 인덱스 전체를 완전히 포함하고, X03 인덱스 선두 컬럼이 X01과 X02 인덱스 전체를 완전히 포함하기 때문이다. 필자는 이를 ‘완전 중복’이라고 부른다. X03 인덱스를 남기고, X01, X02 인덱스를 삭제해도 된다.

 

- X01 : 계약ID + 청약일자

 

- X02 : 계약ID + 청약일자 + 보험개시일자

 

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

 

아래 네 개 인덱스는 얼핏 보기엔 중복이 아니다. 선두 컬럼은 같지만, 두 번째 컬럼이 모두 다르기 때문이다.

 

- X01 : 계약ID + 청약일자

 

- X02 : 계약ID + 보험개시일자

 

- X03 : 계약ID + 보험종료일자

 

- X04 : 계약ID + 데이터생성일시

 

하지만, 계약ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 예를 들어, 계약ID 평균 카디널리티가 5라고 가정하자. 계약ID를 ‘=’조건으로 조회하면, 평균 다섯 건이 조회된다는 뜻이다. 그렇다면 이렇게 인덱스를 네 개씩이나 만들 이유가 없다. 아래와 같이 하나만 만들면 충분하다. 필자는 ‘완전중복’과 대비해 이를 ‘불완전 중복’이라고 한다.

 

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

 

 

 

3.4.8 인덱스 설계도 작성

 

출처 : 친절한 SQL 튜닝 - 조시형 지음