3장. 인덱스 튜닝 - 인덱스 설계

인덱스가 만약 많다면?

---

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

⇒ 개별 쿼리 성능뿐만 아니라 그 개수를 최소화함으로써 DML 부하를 줄여야 하므로 인덱스 설계가 어렵다.

⇒ 그리고 인덱스 추가는 시스템에 부하를 주고 인덱스 변경은 운영에 리스크가 있기 때문에 처음 설계가 중요하다.

인덱스 선택

---

1. 조건절에 항상 사용하거나 자주 사용하는 컬럼
2. ‘=’ 조건으로 자주 조회되는 컬럼을 앞쪽에 두어야한다.
3. 수행빈도, 중요도, 데이터량 등 ..
   1. nl 조인할 때 어느쪽에서 자주 액세스 되는지도 중요한 판단 기준이 된다. ⇒ 이후에 nl조인에서 설명함
   2. 수행빈도가 높다면 당장 성능이 좋아도 인덱스를 최적으로 구성해야한다.
   3. 데이터량이 적다면 굳이 인덱스를 만들 필요가 없다.

전략적 설계

---

핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최적 인덱스를 설계하고 목표 성능을 만족하는 수준으로 인덱스를 구성한다.

예시) 왼쪽과 오른쪽의 조건을 구성하고 있는 다양한 쿼리가 있다.

 

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

X02: 보험개시일자 + 취급부서…

X03: 보험종료일자 + 취급 부서 ..

X04: 데이터생성일지 + 취급 부서..

1. 일자 조회구간이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않다.
   1. 가계약은 주로 최근 3일
2. 인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하

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

1. 가장 많이 사용하는 패턴 데이터 생성일시 between 조건이기 때문.

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

---

예시) select .. 취급지점id = 1 and 청약일자 between 2 and 3 and 입력일자 ≥ 4 and 계약상태코드 in () order by 청약일자, 입력자 ID

1. = 연산자로 사용한 조건절
2. order by 컬럼
3. = 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

⇒ 취급지점 + 청약일자 + 입력자

만약 청약일자 + 입력자 ID 로 했다면 ⇒ 데이터를 뒤쪽(늦게)만나면 성능이 나빠짐

in 조건은 = 이 아니다.

select * from 고객
where 거주지역 = '서울'
and 혈액형 in ('A', 'O')
order by 연령

index가 거주지역 + 혈액형 + 연령이라면?

• 연령순으로 정렬해야함 ⇒ 소트 연산 생략X
• 결론적으로 소트 연산이 생략하려면 in이 ‘=’이 되면 안된다.

index가 거주지역 + 연령 + 혈액형이라면?

• 소트 연산 생략
• 하지만 인덱스를 더 많이 읽어야할듯?(혈액형순으로 정렬이 안되어 있기 때문에)

결합 인덱스 선택도

---

인덱스 결정할 때 선택도/카디널리티 또한 중요하다.

선택도란 ‘=’로 조회했을 때 평균적으로 선택되는 비율

하지만 선택도가 낮은 컬럼을 앞쪽에 두려는 것이 손해일 수도 있다.

index: 고객등급 + 고객번호 + 거래일자 + 거래 유형 + 상품번호

ㄴㄴㄴㄴ = = between (=, between), (=, between)

• 고객 등급, 고객번호 순서가 인덱스 스캔 효율에 영향이 없다.
• 하지만 고객번호는 필수, 고객등급이 조건절에서 누락 or 범위 검색이면 앞쪽에 두는 것이 유리(고객 등급 + 고객번호??)
  • index skip scan or in을 사용 가능
• 결론적으로 선택도도 중요하지만 필수 조건 여부, 연산자 형태 등이 더 중요하다.

중복 인덱스 제거

---

X01: 계약id + 청약일자

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

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

⇒ 완전 중복 - 마지막거만 냅두고 삭제해도 됨(의미가 없음)

X01: 계약ID + 청약일자

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

X03: 계약ID + …

⇒ 불완전 중복: 계약ID가 카디널리티가 낮다면 의미가 없다.

예시1)

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

X02: 계좌번호 + 거래일자

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

X04: 거래일자 + 종목번호

X05: 거래일자 + 계좌번호

1. 범위 검색인 거래일자가 두개일 필요가 없다. (5를 지우고 4에 계좌번호 추가)
2. 관리 지점 번호 ‘=’ 거래일자 between으로 조회하면 1번이 비효율적(1번의 관리지점번호 + 거래일자로 변경)

예시2)

X01: 주소아이디 + 건물동번호 + 건물호번호 + 관리번호

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

X03: 관리번호

X04: 주소아이디 + 관리번호

컬럼 NDV

주소아이디	736000
건물동번호	175
건물호번호	3000
관리번호	250000
상태구분코드	3

1. 상태구분코드의 선택도가 높음 → 2번은 거의 쓰이지 않음(효율이 별로) → 관리번호 + 상태구분코드로 가능
   1. 하지만 특정 값이 매우 적어 특정 값에만 효율이 좋을 수 있다.
2. 1번에서 변경해서 3번은 쓸모없어짐

결론

인덱스 설계시 시스템 전체 효율을 고려

상황에 따라 인덱스가 달라진다.