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번의 경우
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 전문가 가이드
'Database' 카테고리의 다른 글
[mysql] 인덱스 정리 및 팁 (6) | 2017.11.05 |
---|---|
패스트캠퍼스 SQL튜닝캠프 5일차 (1) - 고급 조인 테크닉 (0) | 2017.08.30 |
패스트캠퍼스 SQL튜닝캠프 4일차 - 조인의 기본 원리와 활용 (0) | 2017.08.25 |
패스트캠퍼스 SQL튜닝캠프 3일차 - 인덱스 스캔 효율 (0) | 2017.08.23 |
패스트캠퍼스 SQL튜닝캠프 2일차 - 인덱스 향상 (0) | 2017.07.30 |
패스트캠퍼스 SQL튜닝캠프 1일차 - 인덱스 구조와 스캔 방식 (7) | 2017.07.16 |
댓글0