패스트캠퍼스 SQL튜닝캠프 2일차 - 인덱스 향상

5. 테이블 Random Access 부하

• 인덱스를 쓰는 이유는 RowId를 찾기 위함
• RowId는 HDD에 있음
• RowId를 해시 함수로 돌려

Clustering Factor

테이블 블럭들이 인덱스 정렬 순으로 얼마나 잘 정렬되어있는지에 대한 지표

6. 테이블 Random 액세스 최소화 튜닝

인덱스가 많을 때의 단점

• Command(명령 : 등록/수정/삭제)시 인덱스 변경이 필요하여 시간이 많이 소모
• 인덱스 저장 장소가 필요하여 저장소 용량이 많이 필요하게 됨
• 옵티마이저가 인덱스를 보고 실행계획을 작성하는데, 이때 인덱스가 많으면 많을수록 옵티마이저는 잘못된 실행계획을 생성할 확률이 높다.

인덱스는 3개 이하를 권장한다.

인덱스 컬럼 추가로 해결하기

문제상황)

인덱스 : deptno + job  select * from emp where deptno = 30 and sal > 2000

위와 같은 쿼리를 실행하게 되면 sal > 2000인 값을 인덱스를 통해 알 수 없으므로 아래와 같이 deptno=30인 값들은 모두 테이블 랜덤 엑세스가 발생하게 됩니다.

(출처 : DB가이드)

이걸 해결하기 위해서 인덱스를 부서+sal 로 변경하면 좋겠지만, 이미 다른 곳에서 해당 인덱스를 통해 조회하고 있다면 어려울 것입니다.
그래서 기존의 다른 조회 쿼리들의 성능을 저해하지 않으면서 개선하는 방법은 인덱스 컬럼을 추가하는 것입니다.

인덱스 : deptno + job + sal

(출처 : DB가이드)

이렇게 인덱스 스캔은 전과 동일하지만, 테이블 랜덤 엑세스를 효율적으로 개선할 수 있게 되었습니다.

8. 인덱스 스캔 효율

• 수직적 탐색의 효율화
  • 가능한 방법은 인덱스 재생성
• 수평적 탐색의 선택도
  • 인덱스 매칭도를 높이는 것
• 테이블 랜덤 Access 최소화

위 3가지가 인덱스 개선의 방향

인덱스 매칭도 (인덱스 스캔 범위 최소화)

아래와 같은 쿼리를 수행한다고 가정해보겠습니다.

select *  from 사원 where 입사일자 between '20121201' and '20130105' and 성별 = '남' and 관리자 = '000054';

참고로 Distinct (중복된 값이 얼마나 적은지)는 아래와 같습니다.

• 입사일자 : 매우 좋음 (중복된 값이 적음)
• 관리자 : 보통
• 성별 매우 나쁨 (중복된 값이 많음)

이 상황에서 가장 성능이 좋은 인덱스 구성은 어떤것일까요?

1. 입사일자 + 성별 + 관리자
2. 관리자 + 입사일자 + 성별
3. 성별 + 관리자 + 입사일자

하나씩 확인해보겠습니다.

먼저 1번의 경우
정렬순서가 입사일자 -> 성별 -> 관리자 순으로 되어있어, 조회시 아래와 같이 인덱스 검색을 하게 됩니다.

사실상 인덱스 전체를 검색한 것과 마찬가지의 조회가 발생합니다.

다음 2번의 경우

1번보다 효율은 좋지만, 000054/20121221/여 라는 불필요한 값을 만나게 됩니다.

마지막으로 3번의 경우

보시는것처럼 가장 효율이 좋은 것은 3번 성별 + 관리자 + 입사일자 임을 알 수 있습니다.

이것만 보면, 성별 -> 관리자 -> 입사일자 순으로 잡는게 가장 좋아보입니다만,

(실제 수업에서도 이렇게 설명을 해주셨는데, 테스트해보면 달랐습니다)

실제로 데이터가 수천만건 이상이 되면 성별 -> 관리자 -> 입사일자순으로 잡으면 오히려 인덱스 효과를 제대로 보기 힘듭니다.

다른 글에서 실험 결과를 보여드렸는데,

예로 1700만건을 가지고 실험을 하게 되면 성별로 구분하는 경우 첫번째 인덱스 컬럼으로 좁혀지는 건수는 850만건이나 됩니다.

이와 반대로 날짜를 기반으로 구분하게 되면 첫번째 인덱스 컬럼으로 좁혀지는 경우는 10~20만건으로 아주 적은 범위의 데이터만 남게되어 두번째/세번째 인덱스 컬럼을 사용하게 됩니다.

그래서 실제로 멀티 컬럼을 잡으실때는 웬만하면 첫번째 컬럼은 가장 중복도가 낮은 (카디널리티가 높은) 컬럼으로 잡는것을 추천드립니다. 

이를 통해 인덱스 컬럼을 효율적으로 생성하기 위해선 다음의 규칙을 지키시면 좋습니다.

• 카디널리티가 높은 -> 낮은 순으로 인덱스를 생성하는 것이 유리하다.
  • 입사일자 -> 관리자 -> 성별 
• 단일 컬럼으로 인덱스를 만들어야 한다면, Distinct가 높을수록(즉, 종류가 많을수록) 유리하다.
• between, <, >, like는 본인만 만족하는 조건이라 다음 인덱스 컬럼은 다 깨져버린다.
• 반면에 =, in 은 다음 인덱스 컬럼이 안깨지도록 한다.
• in은 범위 조회가 아닌가?
  • 해당 값 찾고 바로 다시 수직적 탐색을 시작한다.
  • 단, in의 값이 많으면 많을수록 비효율이라 Between과 비교가 필요함
  • 인덱스를 안태우도록 좌변을 가공하면 인덱스를 타지 않아 수직적 탐색이 발생하지 않고 오히려 비효율이 개선된다.(ex: trim(컬럼))

in은 범위조회가 아닌가? 라는 의문을 가질 수 있습니다.
아래 쿼리를 참고하시면 그게 아님을 확인할 수 있습니다.

select * from t where c1 in ('A', 'K')

위 쿼리는 사실상 아래 쿼리와 동일하다고 보셔야 합니다.

select * from t where c1 = 'A' union all select from  where c1 = 'K'

union all : 두 테이블의 중복되는 값까지 그룹화한다.

즉, in의 항목 하나하나를 조회하는것과 동일하기에 범위조건이라 할수는 없습니다.

(출처 : DB가이드)

즉, 결합 인덱스의 우선 순위는 다음을 기준으로 결정하시면 됩니다.

• 자주 사용하는가?
• equals(=) 조건인가?

추천 도서

• 오라클 성능 고도화 원리와 해법 (2권부터 읽기를 추천)
• SQL 전문가 가이드