[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 3. 인덱스튜닝

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.

 

 

3과목. SQL 고급활용 및 튜닝

1. SQL 수행 구조
2. SQL 분석 도구
3. 인덱스 튜닝
4. 조인 튜닝
5. SQL 옵티마이저
6. 고급 SQL 튜닝
7. Lock과 트랜잭션 동시성 제어

 

 

 

 

B*Tree 인덱스 (Balanced Tree) 구조
- 브랜치 블록의 각 로우는 하위블록에 대한 주소값을 갖는다.
- 리프 블록의 키 값은 테이블 로우의 키 값과 같다.
- 리프 블록 끼리는 이중연결리스트(double linked list) 구조이다.

인덱스 ROWID
- (오브젝트번호 + ) 데이터파일번호 + 블록번호 + 블록내로우번호
- 데이터파일 상의 블록 위치를 가리킨다. 논리적인 주소 (물리적인 주소는 아니다.)
- 버퍼 캐시에서 찾을때는 해시 알고리즘을 이용한다.


인덱스 클러스터링 팩터
- 인덱스에 저장된 순서와 테이블에 저장된 데이터의 저장 순서가 얼마나 일치하는지를 나타내는 값 
- 최대값은 인덱스에 저장된 항목의 개수 
- 최소값은 테이블 블록의 개수

테이블 블록 수에 가까울수록 좋고, 테이블 레코드 수에 가까울수록 나쁘다.

인덱스 클러스터링 팩터가 좋으면, 테이블 액세스 단계에서 읽어들이는 블록(데이터) 가 줄어든다.
인덱스 리빌드 해도 클러스터링 팩터가 좋아지지 않는다. 테이블을 재생성 해야 한다.




인덱스에는 구성 컬럼값이 모두 NULL 인 값은 저장하지 않는다.


IOT (index organizaion table)
- Table Random 엑세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성
- PK 순으로 정렬상태를 유지한다.
- IOT 테이블의 일반 인덱스에는 PK 컬럼도 함께 저장된다.

- MS-SQL 의 Clustered Index 와 유사한 개념


인덱스 스캔 방식

Index Range Scan
- 인덱스 루트에서 리프 블록까지 수직적 탐색을 하고, 필요한 범위만큼 수평적 탐색하는 스캔 방식
- 인덱스 선두컬럼이 조건절에 있어야 한다.

Index Unique Scan
- 수직적 탐색으로만 데이터를 찾는 스캔 방식
- 인덱스 컬럼이 모두 조건절에 있는 경우( = 조건)

Index Skip Scan
- 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해서 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 액세스
- 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식
- 조건절에 빠진 선행 인덱스 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value 개수가 많을 때 유용
(선행컬럼이 datetime 일때는 불리, 날짜값(년월일)만 있을때 유리)

- 힌트 : index_ss
- 선두 컬럼이 부등호, between, like 같은 범위 조건일때도 사용 가능
- 선두 컬럼이 in ( list ) 조건일때는 사용 불가

Index Full Scan
- 수직적 탐색 없이 인덱스 리프 블록 처음부터 끝까지 수평적으로 탐색하는 방식
- 인덱스 힌트 사용하고, 해당 인덱스의 선두컬럼이 조건절에 없는 경우 사용된다.
- 인덱스 필터 조건을 만족하는 데이터가 적어야 효과적이다.

Index Fast Full Scan
- 논리적인 인덱스 트리 구조를 무시하고, 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔
- 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록을 읽음.
- 결과 레코드의 순서는 실제 데이터 순서와 다르게 나옴
- 힌트 : index_ffs

Index Range Scan Descending
- Index Range Scan과 기본적으로 동일한 스캔, 인덱스를 뒤에서부터 앞쪽으로 스캔
- 힌트 : index_desc


인덱스 컬럼의 가공
- 인덱스 컬럼이 가공되면, index range scan 이 불가해 진다.
- 부정형 비교는 index range scan 이 불가하다.
- is not null 조건은 index range scan 이 불가하다.

where substr(업체명, 1, 2) = '대한' => where 업체명 like '대한%'

where 월급여 * 12 = 36000000 => where 월급여 = 36000000 / 12

묵시적 형변환

문자와 숫자를 비교하는 경우, 자동으로 숫자로 형변환이 일어난다.

where 숫자형DeptNo = '20' => 숫자형DeptNo = to_number('20') 로 형변환이 일어나고, 인덱스 사용 가능

where 문자형DeptNo = 20 => to_number(문자형DeptNo) = 20 로 형변환이 일어나고, 인덱스 사용 불가

테이블 Random 액세스

인덱스 Leaf 노드에는 ROWID 가 있어, 인덱스에 없는 컬럼을 참조시,
테이블 ROWID 로 Random 액세스를 하게 된다.

실행계획에는 아래와 같이 표시된다.

TABLE ACCESS (BY INDEX ROWID) OF '테이블명' (TABLE)

테이블 랜덤 액세스가 많아지면, 전체 테이블 스캔(Full Table Scan) 보다 느려질 수 있다.
(인덱스 손익 분기점)



배치 I/O
- 테이블 랜덤 액세스시, 버퍼에서 블록을 찾지 못하면, 디스크에서 읽게 된다.
이때, 매번 디스크에서 읽지 않고, 모았다가, 일괄로 찾아오는 방법으로 I/O 효율을 높이는 방법이다.

배치 I/O 가 수행되면, 랜덤 액세스 방식이 아래처럼 변경된다.

TABLE ACCESS BY INDEX ROWID => TABLE ACCESS BY INDEX ROWID BATCHED

배치 I/O 가 수행되면, 버퍼에 없는 블록끼리 모아서 처리 되므로, 결과집합의 정렬 순서가 바뀌게 되고,

실행결과에 SORT ORDER BY 가 나타나면, 부분범위 처리가 불가능해 진다.




인덱스 튜닝

col1 이 인덱스 선두 컬럼일 때, in (list) 방식이 유리하다.

where col1 <> 3 => where col1 in (0, 1, 2, 4, 5, 6)

where col1 between 'A' AND C => where col1 in ('A', 'B', 'C')

col2 가 인덱스 후행 컬럼일 때는 in(list) 보다 filter 방식이 유리하다.

where col1 = :val1 and col2 in(0, 1, 2, 4, 5, 6) => where col1 = :val1 and col2 <> 3

선두컬럼 "or" 조건은 union all 로 처리하는게 유리하다.

select /*+ use_concat */ from where col1 like 'A%' or col1 like 'B%'

후행컬럼 or 조건은 union all 로 처리하는게 불리하다.

select /*+ no_expand */ from where col1 = 'ABC' and ( col2 like 'A%' or col2 like 'B%' )

LIKE 검색

like :val || '%'

시작값으로 조회할때만 index range scan 이 가능하다.
null 허용 컬럼에서 null 값을 찾을때는 like '%' 로 변환되어, null 인 값을 못 찾게 된다.
숫자형 컬럼일때 또는 가변 컬럼일때..   123 인 값을 찾고자 해도, 앞에 123으로 시작하는 모든 데이터를 찾게 된다. (결과의 오류)
index 선두 컬럼에 대해 like 검색시 null 값으로 조회하면, 모든 index 를 찾아야 하므로, 비효율이 발생한다.



소트연산 생략

WHERE col1 = 'ABC' ORDER BY col2, col3

실행계획에 ORDER BY 가 나타나야 하지만, 인덱스에 있으면, ORDER BY 가 생략된다.
인덱스에 2번째부터 순서대로 order by 컬럼이 나와야 한다.

인덱스 구성 : col1 + col2 + col3

소트 연산이 생략되면, 부분범위 처리가 가능해진다.





MS-SQL 인덱스 힌트

PK 인덱스 사용, PK(Clustered Index) 가 없는 테이블은 테이블 풀 스캔
select * from tablename with(index(0))

PK 인덱스 사용, PK(Clustered Index) 가 없는 테이블은 오류
select * from tablename with(index(1))

인덱스명 사용
select * from tablename with(index=table_ix1)
select * from tablename with(index(table_ix1))

테이블 풀 스캔
select *
from
where ...
option( forcescan )

select *
from tablename
where ...
option( table hint( tablename, index(table_ix1) ) )

select *
from tablename with ( forceseek(table_ix1 (col1) ) )