제 5장. 인덱스와 조인
- 제 1절 : 인덱스 기본 원리와 활용
- 인덱스 구조
- 인덱스 기본 원리
- 다양한 인덱스 스캔 방식
- Oracle DBMS 구조
- 테이블 Random Access 부하
- 테이블 Random Access 최소화 튜닝
- IoT와 클러스터
- 인덱스 스캔 효율
1. 인덱스 구조
* 인덱스 Search 구조 ( 인덱스 Range Scan : Root -> Branch -> Leaf -> Table )
- Root Node : (다음 Branch Block 첫번째 주소, Name1 ~ Name99)
- Branch Node : (다음 Leaf Block 첫번째 주소, Name1 ~ Name99)
- Leaf Node : ( 테이블 Key + RowID, 키 순서대로 정렬), 리프 블록끼리는 이중연결리스트 구조
* 인덱스 탐색
(1) 수직적 탐색
Root -> Branch -> Leaf, 조건을 만족하는 시작점을 검색, Random access, 귤을 하나씩 가져오는 것,
(2) 수평적 스캔
Leaf Block 읽을 때, 시작점부터 찾고자 하는 데이터가 더 나타나지 않을 때까지 찾는 과정, Sequential Access, 귤을 박스째 가져오는 것, 적은 비용
*인덱스 비용
테이블 Random Access > 인덱스 수직적 탐색 >인덱스 수평적 탐색
2. 인덱스의 기본 원리
* 인덱스 사용, Range Scan 이 불가능한 경우
- 인덱스 선두 컬럼 가공하면 안됨
- NULL 검색 ( => 복합 인덱스에서 WHERE 조건에 Null + 인덱스 경우는 생성 가능)
- 묵시적 형 변환 ( => 문자 -> 숫자로 변환, 문자 -> 날짜로 변환. LIKE 연산자 경우 숫자 -> 문자로 변경)
- 부정 검색 (not) , (하지만, not between은 가능하다?)
3. 다양한 인덱스 스캔 방식
1) Index Range Scan
2) Index Full Scan
3) Index Unique Scan
4) Index Skip Scan
5) Index Fast Full Scan
1) Index Range Scan
- 가장 일반적인 탐색 방법, 인덱스 수직 탐색 후 필요한 범위까지만 탐색
- 리프 블록에서 다음 리프 블록의 정보를 갖고 있어 바로 다음 데이터를 읽을 수 있음
- 항상 빠른 속도를 보장하지는 않음
* 인덱스 구성하는 선두 컬럼을 조건절(WHERE) 에 사용
- LIKE "%대한%" 과 같은 포함값 스캔 불가
- 인덱스 스캔 범위 줄이기
- 테이블로 엑세스 하는 횟수 줄이기
- 복합 인덱스가 모두 NULL인 경우 불가, 하나 이상 NOT NULL 이어야 가능
- OR 방식의 옵션 조건은 불가 ( UNION ALL로 분기해 다른 한쪽 브랜치에서 선두 인덱스 조건이어야 함)
2) Index Full Scan
- 첫번째 리프 블록까지 수직적 탐색, 인덱스 구조를 따라 스캔하며 인덱스 전체 탐색
- Table Full Scan의 부담이 크지만 적당한 인덱스가 없을 때
- 조건절에 인덱스가 있지만 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능
- 결과 집합 순서 보장
- 최종 결과값이 적으면 Index Full Scan이 효율적
( 결과값이 많으면 Full Table Scan이 효율적 >> Multi Block Read 가능하기 때문 )
- 힌트 따로 없음
3) Index Unique Scan
- 수직적 스캔만 발생
- Unique 인덱스일 경우 사용, 하나의 건이 반환
- 인덱스 구성 컬럼 모두 "=" 조건일 경우만 사용
4) Index Skip Scan
- 조회 조건이 인덱스 선두 컬럼이 아님
- 인덱스 선두 컬럼의 distinct 가 매우 낮을 때 (성별)
- 인덱스 선두 컬럼이 조건절에서 Between, Like, 부등호일 때도 가능 (IN 불가)
- index_ss 힌트
5) Index Fast Full Scan
- 전체 Index Full Scan
- 인덱스에 포함된 컬럼으로 SELECT 때 사용 가능
- Multi-Block IO, 병렬 스캔 가능
- Multi-Block read_count 갯수만큼 한번에 Read
- 논리적 순서와 무관, 물리적 순서대로 Read
- 결과는 인덱스 키 컬럼의 순서와 무관
- index_ffs 힌트
4. Oracle DBMS 구조
* Instance = 프로세스 + 메모리
* 프로세스
- 사용자 프로세스 : 로그 버퍼에 로그를 기록
- 서버 프로세스 : SQL 파싱, 권한 확인 등
- 백그라운드 프로세스 ( PMON, SMON, DBWR, LGWR, CKPT)
(1) PMON : 프로세스 모니터
(2) SMON : 시스템 모니터
(3) DBWR : 데이터베이스 기록자, 데이터베이스 버퍼에 저장된 내용을 데이터 파일에 기록
(4) LGWR : 로그 기록자
- 사용자 프로세스가 데이터 블록을 변경한 이후에 리두 로그 버퍼를 디스크의 리두 로그 파일에 기록하는 역할,
- Commit 명령으로 저장
(5) CKPT : 체크포인트. 마지막 체크포인트 이후에 변경된 모든 블록 데이터 파일에 쓰도록 유도
* 메모리 (Shared Pool, Data Buffer Cache, Redo Log Buffer)
(1) Shared Pool : 동일한 SQL 문장 재사용 위해 프로세스 간 정보 공유
- Library Cache : SQL, DB 저장형 함수/프로시저, 트리거 캐싱
- Dictionary Cache(로우 캐시) : 데이터베이스 구조, 테이블명, 뷰명, 컬럼명, 데이터 유형, 사용자 권한
(2) Redo Log Buffer
- 데이터베이스에서 일어난 모든 변화 저장하는 메모리 공간
- append 형식으로 write해 빠르고 적은 부하
(3) Data Buffer Cache
- 디스크 블록 단위의 데이터 복사본 보관하는 메모리 영역, 테이블 블록, 인덱스 블록, Undo 블록 캐싱
> SGA : 공유 메모리 영역, 경합, 대기, lock
> PGA : 프로세스 전용 메모리 영역, 공유되지 않는 독립 메모리 공간, Sort Area
* 스토리지 (DataFile, Control files, Redo Log Files)
- 데이터 파일 : 데이터베이스 유저, 응용프로그램 데이터, 테이블 등 실제 데이터 및 오브젝트 저장
- 컨트롤 파일 : 물리 구성요소 저장된 바이너리 파일, 데이터 파일, 리두 로그 파일 정보 가지고 있음
- 리두 로그 파일 : 데이터 파일 변경 사항 기록, 커밋된 데이터 복구 가능
- 아카이브 리두 로그 파일 : 리두 장기간 보관
INSERT [LGWR, DBWR이 관여]
1. 서버 프로세스가 SQL문의 문법을 확인합니다
(문법 검사 또는 키워드 검사: INSERT, SELECT, FROM, WHERE 등)
2. SQL문에 있는 테이블과 컬럼을 검사합니다(의미 검사).
3. SQL문을 실행한 계정의 권한을 검사합니다(권한 검사).
(+) Shared Pool에 입력하는 쿼리를 재사용 위해 저장
4. Redo Log Buffer에 기록합니다(INSERT, UPDATE, DELETE문에 해당).
-> (Commit 명령 시) LGWR(Log Writer)이 Redo Log File에 기록합니다.
5. DB Buffer Cache에 기록합니다(INSERT, UPDATE, DELETE문에 해당).
-> Checkpointer는 Data File에 저장하기위해 DBWR(Database Writer) 에게 신호 후
-> DBWR(Database Writer)이 Data File에 기록합니다.
SELECT [서버 프로세스가 관여]
1. 서버 프로세스가 SQL문의 문법을 확인합니다
(문법 검사 또는 키워드 검사: INSERT, SELECT, FROM, WHERE 등)
2.SQL문에 있는 테이블과 컬럼을 검사합니다(의미 검사).
3.SQL문을 실행한 계정의 권한을 검사합니다(권한 검사).
(+) Shared Pool에 입력하는 쿼리를 재사용 위해 저장
4.서버 프로세스가 DB Buffer Cache에서 테이블 데이터를 찾습니다.
-> DB Buffer Cache에 없을 경우, 디스크에서 찾아 DB Buffer Cache로 복사합니다.
-> SELECT 결과 데이터를 유저프로세스로 전송합니다.
* 참고자료 :
https://support.sqlgate.com/hc/ko/articles/900000184506-%EC%A0%9C2%ED%9A%8C-SQL-%EB%AC%B8%EB%B2%95%EB%B3%B4%EB%8B%A4-%EC%95%84%ED%82%A4%ED%85%8D%EC%B2%98-%EC%98%A4%EB%9D%BC%ED%81%B4-%EC%95%84%ED%82%A4%ED%85%8D%EC%B2%98%EC%9D%98-%EC%9D%B4%ED%95%B4-%EC%83%81
5. 테이블 Random Access 부하
RowID 구조 : 오파블로
- 데이터 오브젝트 번호 (6)
- 데이터 파일 번호 (3)
- 블록 번호 (6)
- 로우 번호 (3)
- 해시 체인 Latch 획득 부하
- Buffer Block 대기
- LRU 알고리즘(가장 최신 데이터 위주 사용)에 의해 메모리 Age out되면 LRU Latch 획득
* Buffer Pinning
- 다음 Read 시에 동일 Block Read 할 경우에 해당 Block이 Age-Out 되지 않도록 Pin 걸어둠
- DB 블록 주소가 가리키는 메모리 번지수 PGA에 저장해 찾아가는 기법
* Cluster Factor
- 인덱스 오픈, 변수 선언,
- 인덱스를 순차적으로 읽어 이전의 RowID 블록과 다음 RowID 블록이 상이하면 +1 증가함
- 인덱스에 저장된 순서와 테이블에 저장된 데이터의 저장 순서가 얼마나 일치하는지를 나타내는 값
* CF 좋을 때 (낮을수록) 테이블 블록 수에 근접하며 랜덤 액세스 효율이 좋다.
인덱스 정렬 순서, 테이블 저장된 행 저장 순서가 일치
버퍼 피닝 효과로 Random IO 미발생
* CF 나쁠 때 (높을수록) 인덱스 내 행 개수에 근접, 랜덤 액세스 효율이 매우 나쁨.
인덱스 정렬 순서, 테이블 저장된 행 저장 순서가 일치
- Table Full Scan 과 Index Scan의 손익 분기점을 좌우
- IOT : Clustered Index
* 비용 (Cost) =
리프 블록 도달까지 읽게 될 루트 및 브랜치 블록 개수 (blevel - 인덱스 수직적 탐색 비용)
+ 리프 블록 수 * 유효 인덱스 선택도 (인덱스 수평적 탐색 비용)
+ 클러스터링 팩터 * 유효 테이블 선택도 (테이블 Random Access 비용)
6. 테이블 Random Access 최소화 튜닝
* I/O 튜닝의 핵심 원리
- Sequential Access 의 선택도 높임
- Random Access 발생량 줄임
* 인덱스 컬럼 추가
* PK 인덱스에 컬럼 추가
* 컬럼 추가에 따른 클러스터링 팩터 변화
* 인덱스만 읽고 처리
- 테이블 랜덤 엑세스 절차
1. 인덱스 스캔 후 RowID 획득
2. RowID가 가리키는 테이블 블록을 버퍼 캐시에서 먼저 찾아봄
3. 못찾으면 디스크에서 찾아 블록 읽음
7. IOT와 클러스터
* IOT : Index Organized Table
- 랜덤 액세스가 발생하지 않도록 인덱스 자체에 테이블 데이터를 갖는 방식
- 데이터를 정렬 상태로 유지해 클러스터링 팩터가 좋음
- 인덱스는 한 개만 생성 가능
- PK : Key + RowID / Secondary Key : Key+PK
- PK 조회 속도가 매우 빠르고 Secondary Index 속도는 낮음
- 테이블의 RowID : 휘발성
* IOT 활용
- 크기가 작고 NL 조인으로 반복 룩업(Lookup) 하는 테이블
- Row 수가 많은 테이블
- 넓은 범위를 주로 검색하는 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블
* 인덱스 클러스터 테이블
- 클러스터형 인덱스는 하나만 생성 가능
- 클러스터 키(부서번호) 값이 같은 레코드를 한 블록에 모아 저장하는 방식
- 같은 클러스터 키 값의 데이터가 많을 경우 효율적임
-- 인덱스 클러스터 생성
create cluster c_dept# (deptno number(2) ) index;
-- 클러스터 인덱스 생성
create index c_dept#_idx on cluster c_dept#;
-- 클러스터 테이블 생성
create table dept (deptno number(2) not null , ... ) cluster c_dept#( deptno );
8. 인덱스 스캔 효율
(1) 인덱스 매칭도
* 결합 인덱스 우선순위
- "=", "IN" 이 아닌 조건 이후에는 무조건 체크 조건
- 자주 사용되는가 ?
(2) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성 (인덱스 매칭도)
(3) 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 발생하는 비효율 (인덱스 매칭도)
- sequential 액세스 효율은 선택도에 의해 결정 -> 얼마나 적은 레코드를 읽는지가 중요
- 인덱스 컬럼이 조건절에 모두 "=" 조건이면 가장 높은 효율
(4) Between 조건을 In-List로 바꾸었을 때 인덱스 스캔 효율
* IN ('1', '2') 이면 UNION ALL로 붙이는 효과
S~F~WHERE A.index = '1'
UNION ALL --> 수직적 탐색
S~F~WHERE A.index = '2'
- IN-List 개수가 많지 않아야 함
- 수직적 탐색이 IN-List 횟수만큼 발생
- 수평적 스캔 비효율( Sequential Access)보다 수직적 탐색 비효율(Random Access)
- 상황에 따라 체크조건 앞의 컬럼들이 변별력이 좋아 검색구간을 줄였다면 Between 조건이 오히려 유리
(5) Index Skip Scan을 이용한 비효율 해소
- 수직적 탐색이 많은 IN-List 보다 Index Skip Scan이 근소하게 유리함
(6) 범위조건을 남용할 때 발생하는 비효율
-1. Index : 상품코드+주문일자+주문유형, (주문일자는 Null 가능)* 지역 조건 참여 여부에 따른 SQL 분기가 필요
SELECT 상품코드, 주문일자, 주문유형
FROM 주문
WHERE :ORDER_DT IS NOT NULL
AND 상품코드 = :PORD_CD
AND 주문일자 = :ORDER_DT
AND 주문유형 = :ORDER_TYPE
UNION ALL
SELECT 상품코드, 주문일자, 주문유형
FROM 주문
WHERE :ORDER_DT IS NULL
AND 상품코드 = :PORD_CD
AND 주문유형 = :ORDER_TYPE
-2. Index : 상품코드+주문일자+주문유형, (주문일자 Not Null ) > SQL 단순화 가능 , OR Expand
SELECT 상품코드, 주문일자, 주문유형
FROM 주문
AND 상품코드 = :PORD_CD
AND 주문일자 = NVL(:ORDER_DT, 주문일자)
AND 주문유형 = :ORDER_TYPE
* 참고
1=1 : TRUE이지만 Null = NUll : FALSE 이기 때문에 주의
(7) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항
- 유리한 쪽에 드라이빙 조건 주기
(8) Between 과 Like 스캔 범위 비교
- Between 사용하는 것이 정확한 방식 VS. LIKE 개발자들의 편의
- Leaf Node 존재하는 조건 검색시 Between은 시작, 끝점을 찾는 경우에 '=' 같은 효과
- Like, 부등호 조건은 Leaf Node에 없는 조건이 나올 수 있기 때문에 계속 스캔, 검색
(9) 선분 이력의 인덱스 스캔 효율
- 최근 데이터를 읽을 때 인덱스 설정 : 종료일자 + 시작일자
- 과거 데이터 읽을 때 인덱스 설정 : 시작일자 + 종료일자
- 인덱스 수정 불가한 상황 : Index_Desc 힌트
- 중간지점을 읽을 때 : 어떤 인덱스든 비효율 발생, rownum <= 1 조건 활용(1건 나오면 멈추기)
(10) Access Predicate와 Filter Predicate
(11) Index Fragmentation
'Database > [Oracle] SQL 튜닝' 카테고리의 다른 글
| 1-(1). 인덱스 튜닝 (0) | 2025.01.09 |
|---|
댓글