SQL 이론(5): 옵티마이저와 최적화, 조인 수행 원리

1.

옵티마이저: 사용자가 질의한 SQL문에 대한 최적의 실행방법 결정(실행 계획)

 -비용기반 옵티마이저: 비용이란 SQL문을 처리하는데 예상되는 시간 또는 자원, 통계정보를 이용하여 최적계획 도출

  ->인덱스 사용 비용 > 테이블 풀 스캔 비용 --> 테이블 풀 스캔 유도

• 옵티마이저 구성 요소: 질의 변환기, 비용 예측기, 대안계획생성기
• 위에서 아래로, 바깥쪽에서 안쪽으로 읽는다
• 실행계획에는 사용 객체, 조인방법, 조인순서, 액세스패턴 등의 정보가 출력된다

B Tree 인덱스:

 ＊한 테이블에 과도하게 많은 인덱스가 존재하면 DML 작업 시 부하 발생

• 루트 블록, 브랜치 블록, 리프 블록으로 구성(Decison Tree개념) / 가장 상위: 루트, 중간: 브랜치, 리프: 가장 아래
• 리프 블록: 인덱스 컬럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자로 구성

인덱스 구조 상세:

•  루프와 브랜치 블록의 각 레코드는 하위 블록에 대한 주소 값을 가진다 / 키 값은 하위 블록에 저장된 키 값의 범위
• 리프 블록에 저장된 각 레코드는 키 값 순으로 정렬 / 테이블 레코드를 가리키는 주소 ROWID로 구성
• ROWID: 데이터 블록 주소 + 로우 번호 / 데이터 블록 주소: 데이터 파일 번호 + 블록 번호
• 블록 번호: 데이터 파일 내에서 부여한 상대적 순번 / 로우번호: 블록 내 순번

테이블 풀 스캔 VS 인덱스 스캔:

•  재사용성이 낮아 메모리 버퍼 캐시에서 금방 제거 / Full scan
• 인덱스를 읽어 대상 ROWID를 찾으면 해당 테이블을 다시 찾음 -> Table random access 발생 / Index scan
• 풀 스캔에 비해 블록 버퍼 캐시에 더 오래 남아있는다 / Index scan

테이블 풀 스캔	인덱스 스캔
항상 이용 가능	인덱스가 존재해야만 이용가능
한 번에 여러 개의 블록을 읽음	한 번에 한 개의 블록만 읽음
많은 데이터를 조회 시 성능 상 유리	극히 일부분의 데이터 조회 시 유리
테이블 랜덤 어세스 부하 없음	테이블 랜덤 어세스 부하 발생
읽었던 블록을 반복해서 읽는 경우 없음	읽었던 블록을 반복해서 읽는 비효율 발생

인덱스 유일 스캔: 중복 불가, 반드시 하나만 추출, '='조건으로만 추출해야함

인덱스 전체 스캔: 인덱스를 처음부터 끝까지 전체를 읽으면서 조건에 맞는 데이터 추출

인덱스 스킵 스캔: 인덱스 선두 컬럼이 조건절이 없어도 인덱스를 활용하는 스캔 / 조건에 부합하는 리프 블록만 액세스

인덱스 고속 전체 스캔: 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 멀티 블록 I/O 방식 스캔(병렬 가능)

인덱스 역순 범위 스캔: 뒤에서부터 앞으로, 스캔 순서를 제외하고는 Range Scan과 동일, 내림차순 정렬된 결과 집합

---

NL조인(작은 집합이 드랑비ㅣㅇ 되어야 하고 이너 테이블의 인덱스 스캔이 매우 중요):

• 랜덤 액세스 위주(인덱스 구성이 완벽해도 대량 데이터 조인 시 불리)
• 한 레코드 씩 순차 진행(부분 범위 처리 유도해야 효율적 수정)
• 인덱스 유무, 인덱스 구성에 크게 영향을 받음
• 소량의 데이터 처리 또는 부분범위 처리가 가능한 OLTP 환경에 적합

소트 머지 조인(정렬 작업을 생략할 수 있는 인덱스가 존재하는 경우 사용):

• 실시간 인덱스 생성(양쪽 집합을 정렬)
• 인덱스 유무에 영향을 받지 않음(정렬된 인덱스가 있을 시 좀 더 빠르게 수행)
• 양쪽 집합을 개별적으로 읽고 나서 조인(인덱스가 없는 상황에서 조인 대상 집합을 줄일 수 있을 때 아주 유리)
• 스캔 위주의 액세스 방식

해시 조인(작은 집합을 build input으로 하고 큰 집합을 probe input으로 하는 것이 중요):

• 대량의 데이터 처리가 필요하고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인
• 랜덤 액세스 부하 없음
• 정렬 부하 없음
• 해시 테이블을 생성하는 비용에 따라 build input이 작을 때 써야 효과적

NL 조인	Sort Merge 조인	Hash 조인
순차적 랜덤 액세스 조인 조건이 중요 조인 방향성 부분 범위 처리 가능	동시적 전체 범위 처리(일부분 제외) 조인 조건 무관 조인 무 방향성 SORT 부하 PGA(메모리) 과다 사용 우려	HASH Area Size 중요 등치 조인 중요(=) 대량 범위 처리 유리 배치, SP처리 유리
OLTP(온라인)	-	OLAP(대용량)