데이터와 사람들 SQLP29기 교육 1주차(2025.01.04) 정리

제 5장 인덱스와 조인

---

1. 인덱스의 구조

인덱스 블록의 RowID는 데이터 블록의 Data와 연결된다.

 

Leaf Node는 키와 RowID만 저장해서 블록수가 테이블보다 작다.

 

인덱스 구조는 [root - Branch - Leaf - Table] 순으로 돼있어서, 하나의 인덱스 블록에 400row를 저장할 경우 4 depth에 약 10조개 인덱싱을 할 수 있다. ( 수능 시험장 생각하면 이해하기 편함 )

 

* root는 키의 범위와 가야할 블록 주소를 갖고있다.

 

인덱스의 탐색 2가지

 

1. 수직적 탐색

 > root - Branch - Leaf 순으로 읽고자 하는 시작점을 검색

 > Random Access

 

2. 수평적 탐색

 > Leaf Block 의 시작점 부터 종료점 까지

 > Sequential  Access

 

* root와 Branch의 depth는 무조건 읽어야하며, 수직적 탐색이 있기 때문에 중간에 있는 데이터를 찾더라도 빠르게 위치를 찾을 수 있다.

 

수직적 탐색 + 수평적 탐색 + 테이블 Random Access => Index Scan의 합계

 

인덱스 스캔 시 처리식

=> 4(수직적 탐색) + 300,000(수평적 탐색 한 리프당 400~500개의 레코드라고 가정) + 1억( Random Access) = 1억30만4

 

테이블 풀 스캔 처리식

=> 1억

 

*만약, 테이블 풀 스캔이 1억건이라고 가정하였을 때 1부터 1억건을 인덱스 스캔으로 조회한다면 인덱스 손익분기점을 넘기 때문에 풀 스캔이 더 빠르다. ( 뒤에서 나오겠지만, 클러스터링 팩터에 의해서 손익 분기점이 좌우된다. ) 전체 데이터가 10억건이라면 1억건을 넘기는 인덱스 스캔의 경우 풀스캔보다 느리다 즉, 찾고자 하는 레코드가 전체 용량 대비 10%인 것

 

Sequential Access vs Random Access

정리하자면, Sequential Access는 한블럭을 처음부터 끝까지 다 읽고 Random Access는 인덱스를 이용하여 한블록에 지정된 Row만 읽고 버린다. 따라서 인덱스를 사용하는 Random Access의 비용이 높다.

---

2. 인덱스의 기본 원리 ( 인덱스 사용이 불가능 하거나 범위스캔이 불가능한 경우 )

1. 인덱스 컬럼의 가공(좌변 가공)

 > 좌변을 가공하면 테이블 전체 데이터의 내용을 가공하기 때문에 인덱스를 탈 수 없음

 

2. Null의 검색 ( PK말고도 가급적 NOT NULL을 설정해줘야 하는 이유가 여기에 있다.(인덱스를 사용하기 위한 조건))

 > Null 허용인지, 아닌지에 따라서 인덱스 스캔이 탈 수도 있고 안탈 수도 있다.

 

3. 묵시적 형변환

 > 문자는 숫자로 변경

    문자는 날짜로 변경

    단,  LIKE 연산자 일 경우 반대로 숫자  문자로 변경

 

4. 부정검색

 > != 조건은 인덱스 안탄다는 소리

---

3. 다양한 인덱스 스캔 방식

Index Range Scan

 > 항상 빠른 속도를 보장하지 않는다. (인덱스 스캔 효율 10% 초과 시 풀스캔이 빠르다.)

 > 인덱스를 구성하는 선두 컬럼을 조건절에 사용(★ ★ ★ ★ ★ ★ ★ ★ ★)

 > 힌트 : Range Scan을 유도하는 힌트는 없으며, Index 힌트 활용

Index Full Scan

 > 조회 조건의 인덱스가 있으나, 선두 컬럼이 아니지만, 옵티마이저가 인덱스 활용 시 이익이 있다고 판단할 경우

 > 적당한 인덱스가 없을 경우

 > 최종 결과 값이 적을 때 Full Table Scan 보다 Index Full Scan이 효율적이다. 최종 결과 값이 많을 때 Full Table Scan 이 효율적이다.
 > 힌트 : Index_fs (X)  , Index 힌트 활용

Index Fast Full Scan

 > Disk I/O를 현저히 줄일 수 있는 스캔 방식

 > 전체 Index를 Full Scan
 > Multi-Block I/O  -> 하나의 익스텐트를 벗어날 순 없음(귤상자가 크든 작든 1개만 가능)
 > 파리미터의 db_file_multiblock_read_count 개수만큼 한번에 Read OR 하나의 익스텐트 중 최대치만
 > Index의 논리적 순서와 무관하게 물리적 순서대로 Read
 > 속도가 빠르다
 > 결과는 인덱스 키 컬럼의 순서와 무관
 > 힌트 : Index_ffs

 

Index Fast Full Scan의 차이점 [1. 멀티블록 사용 2. SELECT절에 인덱스 컬럼 필수 3. 인덱스 랜덤 액세스 사용하지 않음 4. 병렬스캔 가능 5. 결과집합 보장 안함]

Index  Unique Scan

 > 수직적 스캔만 발생

 > Unique 인덱스 일 경우 사용

 > = 조건일 경우만 사용

Index Skip Scan

 > 루트까지 안가고 적당한 브런치에서 다음 데이터를 찾는다.( 점프해서 찾는다고 생각하기 )

 > 조회 조건이 인덱스 선두 컬럼이 아니며, 인덱스 선두 컬럼의 Distinct 가 매우 낮을 때 사용 (남,여 같은 컬럼)
 > 인덱스 선두 컬럼이 Between, Like, 부등호 일 때도 사용 가능

 > 힌트 : Index_ss

 

Index Range Scan Descending

 > Index Range Scan가 왼쪽부터 찾는다면 이건 오른쪽 부터 시작하는 것만 다름

                      
Index Combine 

 > Join, Merge 등 다중컬럼으로 구성된 인덱스 사용 시 적용하는 스캔 방식인데 거의 안쓰는 듯

 

 

실행계획 읽는 순서

1. 위에서 아래로 ↓

2. 안에서 밖으로 ←

 2-1. 같은 레벨이면 위에 먼저

 

힌트 사용 법

1. /*+ */ -> 주석안에 전부 힌트

2. --+ -> 한줄 끝까지 힌트

3. index(e(성별)) -> 성별 컬럼이 들어간 인덱스를 써라

4. index(e) -> 알아서 써라

 

힌트 순서

1. 쿼리변환

2. 조인방식

3. index

---

4. 오라클 DBMS 구조 ( 그림 이해하면 됨 )

 

Data Buffer Cache전 Redo Log Buffer 먼저 수행하는 이유는? 

> Redo Log Buffer는 Append 방식으로 소량의 Write 만으로 데이터 정합성 보장 가능하며 빠르고, 적은 부하를 준다. ( 데이터를 읽고 쓸때마다 전체 블록을 저장하는 건 많은 메모리를 차지하기 때문에 Redo Log Buffer가 존재하는 것 )

---

5. 테이블 Random Access 부하

RowID의 구조 ( 오빠불러 )

 > 데이터 오브젝트 번호 (6자리)

 > 데이터 파일 번호 (3자리)

 > 블록번호 (6자리)

 > 로우번호 (3자리)

 

오파블 => 데이터 블록 어드레스(DBA)

 

Hash_Bucket = Hash Functin(DBA)

 > 디스크 주소를 찾아갈 때 해시 펑션을 사용하는데 해시 펑션은 나머지 구하기(MOD)와 같은 함수로 인자값이 달라도 결과가 동일한 기능을 갖는다.

 

따라서... 하나의 해시 버켓은 여러개의 디스크 주소를 찾을 수 있다 이말임.

1%3 => 1 ( 4%3과 같은 디스크 주소를 같는다는 의미 )

2%3 => 2

3%3 => 0

4%3 => 1 ( 1%3과 같은 디스크 주소를 같는다는 의미 )

 

점유 프로세스 기능 3가지

Latch 

 > 먼저 잡는 놈이 장땡

 

Lock

 > 큐 ( 선입선출 )

 

Mutex

 > 래치랑 흡사하지만, 래치보다 가볍다. ( 시험엔 안나옴 )

 

* Buffer Pinning   - Logical Read count로 잡히지 않음 ( Logical Read count = 블록 Read )
다음 번 Read 시 현재 읽은 동일 Block을 Read 할 경우 대상 Block이 Age-Out 되지 않도록 Pin을 걸어두고, 해당 주소인 DBA(DB Block Address)가 가리키는 메모리 번지수를 PGA에 저장하여 바로 찾아가는 기법

 

Clustering Factor ( 데이터 군집성 )

 > 정렬 잘돼있으면 공짜로 데이터를 읽을 수 있다.

 > 인덱스를 순차적으로 읽어 이전 Rowid 블록 과 다음 RowID 블록이 상이할 때 +1 증가

 

비용 (Cost ) = blevel +                                                            -- 인덱스 수직적 탐색 비용
                       (리프 블록 수 * 유효 인덱스 선택도 ) +            -- 인덱스 수평적 탐색 비용
                       (클러스터링 팩터 * 유효 테이블 선택도)          -- 테이블 Random 액세스 비용

---

6. 테이블 Random 액세스 최소화 튜닝

Where 절에 있는 컬럼이 모두 인덱스에 있다면 테이블 랜덤 액세스 비효율을 없앨 수 있다. ( 수평적 스캔을 늘리는 선택 ) (★ ★ ★ ★ ★ ★ ★ ★ ★)

 

위에 있는 조인문을 아래와 같이 나눠서 보면 훨씬 쉽게 이해할 수 있다. 즉, 1번 쿼리를 조회한 결과 1건을 2번쿼리에 적용 -> 이 과정을 1번 쿼리의 마지막 데이터까지 수행하는 거라고 이해하면 됨 ( FOR문 같은거지 )

 

SELECT 절을 위해 인덱스를 추가하면 절대 안된다. (★ ★ ★ ★ ★ ★ ★ ★ ★)

---

7. IOT와 클러스터

각 DBMS에서 부르는 명칭

SQL Server => Clustered Index

Oracle => IOT(Clustered Index)  

 

클러스터드 인덱스 테이블은 1개만 가능하다. ( 그래서 효율이 좋은 컬럼으로 만들어야 한다. )

 

클러스터드 테이블에 데이터가 순서대로 있다가 중간에 특정값이 사라졌다가 새로운 데이터가 들어올 경우 다시 정렬을 유지할까?

 

1, 2, 3, 4, 5 => 순서대로 정렬된 상태

1, 2, 3, 5 => 중간에 4가 빠진 상태

1, 2, 3, □, 5 => 빠진 4가 다시 입력되어 들어오려고 함

 

이때 클러스터드 테이블은 페이지 개편을 한다. => Index Split ( 페이지 개편을 통해 연결되는 데이터를 수직적 탐색으로 찾는다. )

 

중간에 4라는 데이터를 끼워서 다시 정렬을 맞출 수가 없으니, PK를 사용하여 1, 2, 3이후에 4라는 값을 다시 수직적 탐색으로 찾게된다. 일반 테이블은 RowID를 통해 값을 찾지만, 휘발성이기 때문에 클러스터드 테이블은 PK를 사용한다 라고 이해하기.

---

8. 인덱스 스캔 효율

심플하게 2가지를 지키면 됨.

1. Sequential 액세스의 선택도를 높인다.
2. Random 액세스 발생량을 줄인다.

 

드라이빙 => 선행 조건

체크 => 후행 조건

 

결합 인덱스 우선순위 결정 4가지 ( 객관식 시험에 잘나온다고 함. )

1. 항상(자주) 사용되는 가?

2. “=“ 조건
3. Cardinality (분포도)

4. 소트연산 대체

 

인덱스 매칭도 정리 (★ ★ ★ ★ ★ ★ ★ ★ ★)

 

 

실행계획 읽는 방법  (★ ★ ★ ★ ★ ★ ★ ★ ★)

 

---