패스트캠퍼스 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번 성별 + 관리자 + 입사일자 임을 알 수 있습니다.
이를 통해 인덱스 컬럼을 효율적으로 생성하기 위해선 다음의 규칙을 지키시면 좋습니다.

• Distinct가 낮은 -> 높은 순으로 인덱스를 생성하는 것이 유리하다.
  • 성별 -> 관리자 -> 입사일자
• 단일 컬럼으로 인덱스를 만들어야 한다면, 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 전문가 가이드

반응형

저작자표시비영리변경금지

• 카카오스토리
• 트위터
• 페이스북