출처 - https://www.youtube.com/watch?v=GrQxht_XEJ8
네이버카페 데이터와사람들) 인덱스구성과 활용 - 1/3
인덱스구성과 활용 - 1/3
인덱스 - '정렬' 되어있고 데이터 저장되있는 주소가 매핑되어있음
10억개 중 select * from 사원 where 사번 between 5억 and 5억+10
가운데 찾으려면 어케?
root - branch - leaf - table (양끝부터 안찾아도 가지치기로 빠르게 찾아감)
(한 테이블에 적당히 400개 row 잡음)
0 root 400
1 branch1 400^2
2 branch2 400^3
3 branch3 400^4
4 leaf1 400^5 (10조 개)
4 depth 만 가도 10조 개의 인덱스 가능
root 노드 - 가장 상위 노드 - 하위 branch 노드 수만큼의 row
branch 노드 - roof와 leaf의 연결 고리 - 자기 하위 leaf 노드 수만큼의 row
leaf 노드 - key + rowID로 구성, key 순서대로 정렬/ 이전, 이후 leaf의 chain
root - branch - leaf 타고 수직으로 내려와서 시작점 찾으면 그대로 수평 탐색
인덱스 range scan 순서 수직적 탐색 → 수평적 스캔
수직적 탐색
- root branch leaf
- 읽고자하는 시작점 검색
- random access
수평적 스캔 - leaf block의 시작점부터 종료점까지
- sequential access
table full scan / index scan
전체 데이터 1~1억이라 할 때,
1~1억 다 읽는거 빼고 index scan이 훨씬 빠름
인덱스구성과 활용 - 2/3
random access / sequential access
귤 가져와서 한 알집 까는거 - random access
한 알집에서 1번알 2번알 차례로 먹는거 - sequential access
random access
- 주로 하나의 블록에서 하나의 레코드만 읽는다
- 효율이 낮다 / 높은 비용
- rowID 이용 테이블 액세스
- DB Block Address를 이용한 인덱스 수직적 탐색
- 클러스터링팩터가 낮을 때 높은 성능
- Single Block I/O
sequential access
- 하나의 블록에서 순차적으로 읽는다
- Index leaf block 읽을 때 / Full Scan 할 때
- 적은 비용
- Full Scan일 경우 Multi - Block I/O 가능
인덱스
- 수직적 탐색
- 수평적 스캔
- 테이블 Random Access
비용 많이 드는 순
- 테이블 Random Access > 수직적 탐색 > 수평적 스캔
인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
- 인덱스 컬럼의 가공 (좌변 가공)
- NULL의 검색
- 묵시적 형변환
ex) where VARCHAR = INT 해서 optimizer가 자동으로 바꿔줄 때 인덱스 적용 x - 부정검색 (NOT 조건)
인덱스 범위 스캔
- 항상 빠른 속도를 보장하지 않는다
- 인덱스 스캔하는 범위를 얼마나 줄일 수 있느냐?
- 테이블로 액세스 하는 회수를 얼만큼 줄일 수 있느냐
- SQL 튜닝의 핵심 원리
- 인덱스를 구성하는 선두 컬럼을 조건절에 사용해야 함
인덱스 풀 스캔
- 적당한 인덱스가 없을 경우 table full scan 수행
- 조회 조건의 인덱스가 있으나, 선두 컬럼이 아니면
- 옵티마이저가 인덱스 활용 시 이익이 있다고 판단할 경우
- 인덱스 풀 스캔 활용
- 최종결과 값이 적을 때 인덱스 풀 스캔이 효율적
- 최종결과 값이 많을 때 테이블 풀 스캔이 효율적
인덱스 유니크 스캔
- 수직적 스캔만 발생
- unique 인덱스일 경우 사용
- where '=' 조건일 때만 사용
인덱스 스킵 스캔
- 조회 조건이 인덱스 선두 컬럼이 아니며,
- 인덱스 선두 컬럼의 distinct가 매우 낮을 때 사용
- 인덱스 선두 컬럼이 between, like, 부등호일 때도 사용 가능
인덱스구성과 활용 - 3/3
RowID 구조 (오파블로) (오파블 - DB 블록 어드레스)
- 데이터 오브젝트 번호 (6자리)
- 데이터 파일 번호 (3자리)
- 블록번호 (6자리)
- 로우번호 (3자리)
테이블 Random Access 부하
- latch 획득의 부하
- buffer block의 대기
- LRU 알고리즘에 의해 메모리에서 Age Out 되었을 경우 LRU Latch 획득 필요
Buffer Pinning
- 다음 번 Read시 현재 읽은 동일 Block을 Read할 경우 대상 Block이 Age Out되지 않도록 Pin을 걸어두고, 해당 주소인 DB Block Address가 가리키는 메모리 번지수를 PGA에 저장하여 바로 찾아가는 기법
- Logical Read Count로 잡히지 않음 (블록을 읽어오는 횟수로 치지 않음)
(★ 인덱스가 정렬되있는데, 테이블이 인덱스처럼 정렬되어 있다면, 효율 급상승)
Clustering Factor
- 인덱스 오픈
- 변수 선언
- 인덱스를 순차적으로 읽어 이전 RowID 블록과 다음 RowID 블록이 상이할 때(다를 때) +1 증가
(CF가 낮을수록 랜덤 액세스 효율이 좋은거)
Table Full Scan / Index Scan의 손익분기점
- 통상적으로 찾고자 하는 레코드가 전체 용량 대비 10%라고 하나, 항상 그렇지 않고
- 정확히는 CF(Clustering Factor)에 의해 좌우된다
<결론>
모든 테이블 정렬을 일관되게 할 수는 없지만 가장 많이 사용하는 인덱스 기준으로
CF가 낮게 나오게 하면 인덱스 효율이 올라간다
