3회차 인덱스 튜닝

개발 독서 스터디/친절한 SQL 튜닝

3회차 인덱스 튜닝 - 3

seoyyyy 2025. 2. 8. 15:17

4 인덱스 설계

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

SQL 최적화를 위해 테이블마다 인덱스를 수십개씩 남발해서 생성하다보면 여러 문제가 발생할 수 있다.

하나의 테이블에 인덱스가 여섯개 달려있으면 데이터의 입력/삭제 시 여섯 개 인덱스에도 전부 데이터를 입력/삭제해야 한다.

또한 테이블과 달리 인덱스는 정렬 상태를 유지해야 하므로 수직적 탐색을 통해 입력할 블록부터 찾고, 찾은 블록에 여유 공간이 없을 시 인덱스 분할(Index Split)도 발생한다.

<인덱스가 너무 많을 때 발생하는 문제>

1️⃣DML 성능 저하 (➡️ TPS(초당 처리되는 트랜잭션 수) 저하)

2️⃣데이터베이스 사이즈 증가 (➡️ 디스크 공간 낭비)

3️⃣데이터베이스 관리 및 운영 비용 상승

😣 개발 단계에서 최적 인덱스 설계의 중요성

시스템 개발 단계에서는 비교적 쉽게 인덱스를 변경할 수 있지만, 개발을 마치고 운영 환경으로 이행하게 되면 인덱스 변경시 그에 따른 시스템 변경 영향도가 클 수 있어 쉽지 않다. (특히 금융권에선 거의 불가능에 가까움) 그렇다고 신규 인덱스를 추가하자니 시스템 수준 TPS가 점점 나빠지질 수 있다.

즉 인덱스 추가는 시스템에 부하를 주고, 인덱스 변경은 운영 리스크가 크다.

그래서 시스템 개발 단계에서 인덱스를 최적으로 설계하는 일이 무엇보다 중요하다.

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

지금까지 계속 강조해왔지만 인덱스 스캔 방식 중 가장 정상적이고 일반적인 방식은 Index Range Scan 이다.

이를 위해서는 인덱스 선두 컬럼을 조건절에 반드시 사용해야 한다. 결합 인덱스 구성 시 기준 공식(?)은 아래와 같다.

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

2️⃣ '=' 조건으로 자주 조회되는 컬럼을 앞쪽에 둔다.

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

4.2 에서 설명한 공식은 인덱스 스캔 효율성 판단 기준이었으며, 그 외의 고려해야할 판단 기준 항목들은 아래와 같다.

▶️ 수행 빈도⭐

▶️ 업무상 중요도

▶️ 클러스터링 팩터

▶️ 데이터량

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

▶️ 저장 공간

▶️ 인덱스 관리 비용 등

자주 수행하지 않는 SQL 이면 인덱스 스캔 과정에 약간의 비효율이 있더라도 큰 문제가 아닐 수 있지만 수행 빈도가 매우 높은 SQL에는 최적의 인덱스를 구성해주는 것이 좋다.

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

NL 조인 시 Outer(드라이빙 집합) 에서 액세스 하는 인덱스는 스캔 과정에 비효율이 있더라도 큰 문제가 아닐 수 있다.

SELECT /*+ leading(a) use_nl(b) */      -- a를 드라이빙 집합(OUTER)으로 설정하고 b를 NL조인 
 b.상품코드, b.상품명, a.고객번호, a.거래일자, a.거래량, a.거래금액
FROM 거래 a, 상품 b
WHERE a.거래구분코드 = 'AC'
AND a.거래일자 BETWEEN '20090101' AND '20090131'
AND b.상품번호 = a.상품번호
AND b.상품분류 = '가전'

거래 테이블의 인덱스를 [거래일자 + 거래구분코드] 순으로 구성하여 실행한 위의 쿼리는 선두 컬럼인 거래일자가 BETWEEN 조건으로 인덱스를 스캔하는 과정의 비효율이 있더라도 NL조인 메커니즘 상 비효율은 한번에 그친다.

불필요한 액세스가 발생하지 않으므로 아주 넓은 거래일자 구간으로 조회하지 않는다면 성능도 나쁘지 않을 것이다.

반대로, NL 조인에서 Inner 쪽 인덱스 스캔과정에 비효율이 있다면, 성능에 큰 문제를 야기할 수 있다.

SELECT /*+ leading(b) use_nl(a) */ -- b를 드라이빙 집합(OUTER)으로 설정하고 a를 NL 조인 
 b.상품코드, b.상품명, a.고객번호, a.거래일자, a.거래량, a.거래금액
FROM 거래 a, 상품 b
WHERE a.거래구분코드 = 'AC'
AND a.거래일자 BETWEEN '20090101' AND '20090131'
AND b.상품번호 = a.상품번호
AND b.상품분류 = '가전'

거래 테이블의 인덱스를 [거래일자 + 상품번호 + 거래구분코드] 순으로 구성하여 위 쿼리 실행 시 BETWEEN 조건인 거래일자가 선두 컬럼이므로 Outer 테이블로부터 액세스 하는 횟수만큼 비효율적인 스캔을 반복한다.

NL 조인 시 Inner 쪽 인덱스는 '=' 조건 컬럼을 선두에 두는 것이 중요하며 될 수 있으면 테이블 액세스 없이 인덱스에서 필터링을 마치도록 구성해야 한다.

데이터 량도 인덱스 설계 시 중요한 판단 기준이며 초당 DML 발생량은 트랜잭션 성능(TPS)에 직접적인 영향을 준다.

데이터량이 적다면 Full Scan만으로 충분히 빠르며, 인덱스를 많이 만들어도 저장 공간이나 트랜잭션 부하 측면에서 문제가 되지 않지만 초대용량 테이블의 경우 INSERT가 많기 때문에 전문적인 손길로 테이블 인덱스 설계를 고려해야한다.

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

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

즉 단순한 공식에 의한 결정이 아니라, 업무 상황을 이해하고 나름의 판단 기준을 가지고 결정을 내려야 한다.

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

인덱스는 항상 정렬 상태를 유지하므로 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

위 쿼리에서 성능을 고려하지 않는다면 소트연산을 생략하도록 인덱스를 [청약일자, 입력자ID] 순으로 구성할 수 있다.

'=' 조건절 컬럼은 ORDER BY 절에 없어도 인덱스 구성에 포함할 수 있으므로 [청약일자 + 취급지점ID + 입력자ID] 순으로도 생략 가능하다. '=' 조건의 순서는 어디든 상관없다.

'='이 아닌 조건절 컬럼들은 반드시 ORDER BY 컬럼보다 뒤쪽에 두어야 소트연산 생략이 가능하다. (ex. [청약일자 + 입력자ID + 입력일자 + 계약상태코드] 순)

다만 성능을 고려하여 I/O를 최소화 하면서도 소트연산을 생략하기 위해선 아래 공식에 따라 인덱스를 구성하면 된다.

① '=' 연산자로 사용한 조건절 컬럼 선정

② ORDER BY 절에 기술한 컬럼 추가

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

이 공식을 따라서 인덱스를 [취급지점ID + 청약일자 + 입력자ID] 순으로 구성할 수 있으며 입력일자, 계약상태코드는 이들 조건을 만족하는 데이터가 적으면 인덱스에 추가하면 좋으며 선택사항이다.

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

위 IN 조건에서 '='이 되려면 IN-List Iterator 방식으로 풀려야하는데 UNION ALL 로 변환되어 실행된다는 의미이다.

하지만 이렇게되면 ORDER BY 절에서 소트 연산이 생략되지 않으므로 생략을 원하면 IN-List Iterator 방식으로 풀려서는 안된다. 즉 IN 조건절을 인덱스 액세스 조건으로 사용하면 안되며 필터 조건으로 사용하도록 한다.

4.6 결합 인덱스 선택도

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

선택도는 전체 레코드 중 조건절에 의해 선택되는 레코드 비율을 뜻하며, 선택도 X 총 레코드 수 = 카디널리티 이다.

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

따라서 인덱스 생성 시 반드시 선택도/카디널리티를 확인해야한다.

선택도(Selectivity) = (고유한 값 개수) / (전체 행 개수)

값의 범위: 0 ~ 1
1에 가까울수록 선택도가 높고(좋음), 0에 가까울수록 낮음(나쁨)

선택도가 높다는 뜻

- 인덱스를 통해 조회할 때, 적은 행(row)만 검색하면 됨
- 필터링 효과가 큼 → 테이블 풀 스캔(Table Full Scan) 대신 인덱스를 효율적으로 사용 가능
- 쿼리 성능이 좋을 가능성이 큼

- 인덱스 검색이 매우 효율적

ex)

SELECT * FROM users WHERE ssn = '123-45-6789';

선택도가 낮다는 뜻

- 인덱스를 사용해도 효과가 크지 않음
- 필터링 효과가 적음 → 테이블 풀 스캔 가능성 증가
- 인덱스를 타더라도 많은 행을 읽어야 하므로 성능 향상 효과가 적음

ex)

SELECT * FROM users WHERE gender = 'M';

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

인덱스 설계 시 항상 사용하는 컬럼을 앞쪽에 두고, 그 중 '='조건을 앞쪽에 위치셔아 한다.

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

예를들어 인덱스 액세스 조건의 컬럼 두개는 어떤 컬럼이 앞에 오든 인덱스 스캔 범위가 똑같다.

SELECT *
FROM 거래
WHERE 고객등급 = : V1
AND 고객번호 = :V2
AND 거래일자 >= :V3
거래유형 = :V4
...

위 쿼리에서 '항상 사용하는' 고객번호(=), 고객등급(=), 거래일자(BETWEEN) 중 고객번호와 고객등급은 어떤 컬럼이 앞으로 오든 인덱스 스캔 효율에 어떤 영향을 주지 않는다.

만약 둘 중 하나 이상이 조건절에서 누락되거나 범위검색 조건일 수 있다면 얘기는 다르다.

만약 고객번호는 필수인데 고객등급이 조건절에서 누락되거나 범위검색 조건이면 이땐 고객등급을 앞쪽에 두어 Index Skip Scan이나 IN-List 조건을 활용하도록 하는것이 유리하다.

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

4.7 중복 인덱스 제거

<중복 인덱스 예시 1>

- X01 : 계약ID + 청약일자

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

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

위 세 인덱스는 중복이다. X02는 X01을 완전히 포함, X03은 X02, X01을 완전히 포함한다.

따라서 X3 인덱스만 남기고 X01, X02 인덱스는 삭제해도 된다.

<중복 인덱스 예시 2>

- X01 : 계약ID + 청약일자

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

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

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

위 네 인덱스는 얼핏보면 중복이 아니다. 선두 컬럼은 같으며 두 번째 컬럼이 모두 다르기 때문이다.

하지만 계약 ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 이를 '불완전 중복' 이라 부른다.

위 인덱스는 아래와 같이 하나만 만들어도 충분하다.

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

<중복 제거 실습 1>

- PK : 거래일자 + 관리지점번호 + 일련번호

- N1 : 계좌번호 + 거래일자

- N2 : 결제일자 + 관리지점번호

- N3 : 거래일자 + 종목코드

- N4 : 거래일자 + 계좌번호

첫 번째 방안은 거래일자, 결제일자는 항상 BETWEEN 또는 부등호 조건으로 조회한다. N3, N4 인덱스는 둘 다 거래일자가 인덱스 액세스 조건, 뒤가 필터 조건이다. 그러므로 인덱스 두개를 만들 필요 없이 N3 인덱스 뒤쪽에 계좌번호를 추가하면 된다.

- PK : 거래일자 + 관리지점번호 + 일련번호

- N1 : 계좌번호 + 거래일자

- N2 : 결제일자 + 관리지점번호

- N3 : 거래일자 + 종목코드 + 계좌번호

~~- N4 : 거래일자 + 계좌번호~~

두 번째 방안은 N4 인덱스를 제거하고 계좌번호와 거래일자로 조회할 땐 N1 인덱스를 사용하고 거래일자만으로 조회할 땐 N3 인덱스를 사용하게 한다.

또한 관리지점번호가 선두 컬럼인 인덱스가 없고, 기존 PK는 관리지점번호를 '=', 거래일자를 BETWEEN으로 조회시 비효율적이다. 따라서 관리지점번호를 PK의 선두 컬럼으로 변경하여 관리지점번호와 거래일자로 조회할 땐 PK 인덱스를 사용하게 한다.

- PK : 관리지점번호 + 거래일자 + 일련번호

- N1 : 계좌번호 + 거래일자

- N2 : 결제일자 + 관리지점번호

- N3 : 거래일자 + 종목코드

~~- N4 : 거래일자 + 계좌번호~~

<중복 제거 실습2>

- PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호

- N1 : 상태구분코드 + 관리번호

- N2 : 관리번호

- N3 : 주소ID + 관리번호

컬럼명	NDV (Number of Distinct Value, 각 컬럼에 입력된 값의 종류 개수)
주소 ID	736,000
건물동번호	175
건물호번호	3,052
관리번호	250,782
상태구분코드	3

위 표를 보면 상태구분코드는 NDV가 3이므로 선택도가 매우 높다.

상태구분코드로만 조회할 때는 N1인덱스가 사용되지 않는다. N1 인덱스가 사용되려면 상태구분코드와 관리번호를 같이 조회해야 한다.

N2 인덱스는 관리번호로 조회시만 사용되므로 아래와 같이 N2 인덱스를 제거하고, N1 인덱스를 [관리번호 + 상태구분코드] 순으로 변경하여 관리번호로만 조회하든, 상태구분코드까지 같이 조회하든 N1 인덱스를 사용하도록 할 수 있다.

- PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호

- N1 : 관리번호 + 상태구분코드

~~- N2 : 관리번호~~

- N3 : 주소ID + 관리번호

주의: 상태구분코드 NDV가 3이지만 그중 특정 값은 변별력이 매우 좋을 수 있어 만약 그 값으로 조회할 때 사용할 목적으로 N1 인덱스를 만들었다면 N1 인덱스 구성을 바꿨을 때 문제가 생길 수 있다.

4.8 인덱스 설계도 작성

인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 인덱스 설계에서 전체를 조망할 수 있는 설계도면이 필요하다.

- 위 설계도에서 상단 좌측에 변경 전 인덱스 구성① 필드가 있고 우측에 변경 후 인덱스 구성② 필드가 있는데 전자는 말그대로 현재의 인덱스 구성이며 데이터베이스 딕셔너리에서 읽어 기록한 것이고, 후자는 새로운 구성 전략을 기록한 것이다.

- 액세스 경로③는 테이블별로 실제 발생하는 액세스 유형을 모두 조사한 것이며 아래에의 변경전 현재 사용하는 인덱스이며 구성 변경 후 아래의 인덱스를 사용하게 될 것임을 표시하고 있다.

- 인덱스 설계전에 파티션 설계를 먼저 진행하거나 최소한 병행해야 제대로 된 인덱스 전략을 수립할 수 있어 상단에 파티션 구성을 기록하는 필드도 존재한다.