인덱스 스캔 방식 및 인덱스 쿼리튜닝
* 인덱스 스캔 방식
1. index range scan : INDEX (RANGE SCAN)
: 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식이다.
: B-tree 인덱스의 일반적, 정상적 형태의 엑세스 방식
2. index full scan : INDEX (FULL SCAN)
: 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
: 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
ex. 연봉이 5000만원 이상인 사원 -> 극히 일부면 table full scan보다 index full scan 해도 됨
ex. 연봉이 1000만원 이상인 사원 -> 굉장히 많기 때문에 거의 모든 레코드에 닿으면 index full scan이 불리하다.
3. index unique scan : INDEX (UNIQUE SCAN)
: 수직적 탐색만으로 데이터를 찾는 스캔방식 Unique 인덱스를 = 조건으로 탐색하는 경우에 작동
4. index skip scan
: 인덱스 선두 칼럼이 조건절로 사용되지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다. 또는 Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 Index Full Scan방식을 사용한다.
근데 이런상황에서도 index 로 스캔을 할 수 있는 방법이다.
: 수행원리 : 루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 가능성이 있는 하위 블록만 골라서 엑세스하는 방식. 조건절에 빠진 인덱스 선두칼럼의 distinct value 개수가 적고 후행 칼럼의 distinct value 개수가 많을 때 유용하다.
--> index skip scan에 의지하지 말고, 이렇게 작성해 보는건 어떨까?
select * from 사원
where 연봉 between 2000 and 4000
이 쿼리를
select * from 사원
where 연봉 between 2000 and 4000
and 성별 in ('남','여') --> 남, 여 외의 값이 절대로 존재하지 않을 때!! 사용
5. index fast full scan
: index full scan 보다 빠르다. 이유는 인덱스 트리 구조를 무시하고
인덱스 구조를 따라서 스캔하는게 아니고, 세그먼트 전체를 스캔함 그래서 결과 집합 순서 보장이 안됨
6. index range scan desending
index range scan과 기본적으로는 동일한 스캔 방식이다.
근데 뒤에서 부터 읽어오므로, 내림 차순으로 정렬된 결과 집합을 얻는다.
------------------------------------------------------------------------------------------------
>>>>> 인덱스 튜닝
b tree 인덱스를 정상적으로 사용하려면
인덱스 선두 칼럼이 조건절에 사용 되어야 한다.
근데 조건절에 사용되더라도 범위 스캔이 불가능할 때가 있다.
* 인덱스 선두 칼럼이 조건절에 사용되는데 범위 스캔 불가능할 때
1. 가공할때
where substr('업체명',1,2) = '대한'
2. 부정형 비교할 때
where 국가 <> '대한민국'
3. is not null(부정형)
where 부서코드 is not null
가공 사례
**** 테이블 random 엑세스 최소화 튜닝
emp 테이블에 현재 pk 이외에 [deptno + job] 순으로 구성된 emp_x01 인덱스 하나만 있는 상태에서 아래 쿼리를 수행하려고 한다.
select ename, job, sal
from emp
where deptno = 30
and sal >= 2000
해결 방법
1. [deptno + job]을 [deptno + sal] 로 바꾸기 :
만약 이런 쿼리가 있다면 바꿀수 없음.. select ename, job, sal from emp where deptno=30 and job='check'
2. [deptno + sal] 를 새로 추가 :
인덱스의 계속추가는 데이터 조회를 더 느려지게 할 수 있다.
3. [deptno + job + sal] 기존 인덱스에 sal을 추가한다.
인덱스 스캔량은 줄지 않지만 테이블 random 엑세스 횟수는 줄어든다.
--> 이유?
위의 쿼리를 실행하면 index가 [deptno + job]로 걸려 있기 때문에 먼저 where 조건의 deptno을 검색해서 30인걸 찾는다.(여기까지는 다 똑같음) 그 후
원래 index : [deptno + job] 로 index가 걸려있다면 인덱스에서 deptno을 검색해서 30인 실제 행, 실제 테이블에 한개씩 접근해서 sal을 찾고(이렇게 실제 테이블에 접근하는걸 radnom 엑세스 라고 한다.) 그 다음에 sal이 조건에 부합하면 출력
수정한 index : [deptno + job + sal] 로 index가 걸려있다면 인덱스에서 deptno을 검색해서 30인 실제 행을 찾는다.그중 sal이 부합한 것을 찾는다. 그다음에 실제 행, 실제 테이블에 접근해서 출력.
결과적으로 random 엑세스 횟수를 줄여서 부하가 줄어든다(I/O튜닝의 핵심 원리중 하나).
**** 스캔 범위 최소화
sequential 엑세스 : 레코드 간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식
random 엑세스 : 레코드 간 논리적, 물리적 순서를 따르지 않고 한건을 읽기 위해 한 블록씩 접근 하는 방식
I/O 튜닝의 핵심 원리
1. random 액세스 발생량을 줄인다.
2. sequential 엑세스에 의한 선택 비중을 높인다.
인덱스 구성 칼럼이 조건절에서 모두 등치 조건으로 비교되면 리프 블록을 스캔하면서 읽은 레코드는 모두 테이블 엑세스로 이어진다. 읽고서 버리는 레코드가 하나도 없으므로(???) 인덱스 스캔단계에서의 효율은 최상이다.
인덱스 칼럼 중 일부가 = 조건이 아니거나 = 외의 조건이 뒤쪽 칼럼에 있을 때는 괜찮다.
인덱스 [아파트시세코드 + 평형 + 평형타입 + 인터넷 매물] 일 때
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물 아파트 매매
where 아파트 시세코드='A0103243' and 평형 = '59' and 평형타입 = 'A' and 인터넷매물 between '1' and '2'
면 =을 먼저찾고 마지막 인터넷 매물을 찾으므로 낭비가 없다.
그러나
인덱스 [인터넷 매물 + 아파트시세코드 + 평형 + 평형타입 ] 이라면?
between이라서 검색결과가 1과 2로 늘어나겠지?
결과적으로 인덱스 생성시에는 조건식에 =를 쓰는 칼럼을 먼저 쓴다.
---
근데 만약에 인덱스가 [인터넷 매물 + 아파트시세코드 + 평형 + 평형타입 ] 이거고 인덱스를 바꾸기가 어렵다면???
범위를 in-list로 작성하라!
ex.
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물 아파트 매매
where 인터넷매물 in ('1','2') and 아파트 시세코드='A0103243' and 평형 = '59' and 평형타입 = 'A'
이렇게!!
이 뜻은 하단과 같다.
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물 아파트 매매
where 인터넷매물 ='1' and 아파트 시세코드='A0103243' and 평형 = '59' and 평형타입 = 'A'
union all
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물 아파트 매매
where 인터넷매물 ='2' and 아파트 시세코드='A0103243' and 평형 = '59' and 평형타입 = 'A'
이렇게 하면 수직 탐색을 하는거니 전보다 between쓰는것보단 낫다고 한다.
그래도 in 조건에 여러개가 있으면 수직 탐색 많이 하는 거니까, 그리고 row가 많으면 더 부하가 되겠지?
적은 범위일때 쓰자
---
범위 조건을 2개 이상 사용할 떄의 비효율
-------------------------------------------------------------------------------------
index 설계
인덱스 설계 방식 중 가장 정상 적인 것은 index range scan.
이를 위해서는 인덱스 선두 칼럼이 조건절에 반드시 사용되어야 함.
따라서 결합 인덱스 구성시
1. 조건절에 항상 사용되거나, 적어도 자주 사용되는 칼럼 선정
2. 그렇게 선정된 칼럼중 = 조건으로 자주 조회되는 칼럼을 앞쪽에 두기
3. 소트 오퍼레이션을 생략하도록 하기 위해 칼럼을 추가
따라서 조건절에 사용되지 않은 갈럼이라도 orderby groupby를 위한 소트 연산을 대체할 목적으로 인덱스 구성에 포함시켜서 성능개선을 도모 할 수 있다.
인덱스를 스캔하면서 테이블을 엑세스 하는 양이 일정 수준(손익분기점)을 넘는 순간 full table scan보다 오히려 느려진다. 따라서 선택도가 높은 인덱스는 생성해 봐야 효용가치가 별로 없다.
이외 고려사항
-쿼리 수행빈도
- 업무상 중요도
-클러스터링 팩터
-데이터양
-dml 부하
-저장 공간
-인덱스 관리 비용