SQL 최적화 기본 원리 (옵티마이저와 실행계획, 인덱스, 조인수행원리)

옵티마이저와 실행계획

옵티마이저

디양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 것

규칙기반 옵티마이저 (RBO, Rule Based Optimizer)

비용기반 옵티마이저 (CBO, Cost Based Optimizer) R-DB 대부분 비용기반!!

가. 규칙기반 옵티마이저

규칙 1. Single row by rowid : rowId를 통해서 테이블에서 하나의 행을 엑세스

규칙 4. Single row by unique or primary key : 유일 인덱스(Unique Index)를 통해서 하나의 행을 엑세스 (PK)

규칙 8. Composite index : 복합 인덱스에 동등 조건으로 검색 A=1 B=2 

우선순위 A+B 더 높음 > A+B+C (A, B가 =조건있기 때문)

규칙 9. Single column index : 단일 칼럼 인덱스 A=1

규칙 10. 인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색 EX) BETWEEN, LIKE

규칙 11. 한쪽 범위를 한정하는 형태로 검색 EX) A>10

규칙 15. 전체 테이블 스캔

엑세스는 INDEX 있는애 부터

조인은 INDEX없는애 부터

나. 비용기반 옵티마이저

비용기반은 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.

비용이란 SQL문을 처리하기 위해 예상되는 소유시간 또는 자원 사용량을 의미한다.

비용기반은 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보 등을 이용한다.

비용기반은 통계정보가 없는 경우 불확실한 실행계획을 생성 할 수 있으므로 정확한 통계정보를 유지하는것이 중요한 요소이다.

* 질의변환기 : 사용자가 작성, SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈

* 대안생성기 : 동일한 결과를 생성하는 다양한 대안 계획 생성 모듈

  -> 연산 적용순서변경, 영산방법변경, 조인순서변경 등을 통해 생성

  -> 대안계획이 많아지면 최적화 하는데 수행시간 오래걸림

* 비용예측기 : 대안 계획생성기에 의해 생성된 대안 계획 비용 예측 모듈

* 비용기반은 다양한 변수로 인해 실행계획 예측이 어려움

실행계획

SQL에서 요구한 사항을 처리하기 위한 절차와 방법

* 구성 요소 : 조인 순서 (Join Order) , 조인기법 (Join Method) , 

액세스 기법 (Access Method) , 최적화 정보(Optimization Information) , 연산 (Operation) 등

FROM절에 존재하는 테이블수 : N

* 논리적으로 가능한 조인순서 : N!

* 최적화 정보 : 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것

* Cost : 상대적인 비용

* Card : Cardinality, 주어진 조건을 만족한 결과집합 건수

* Bytes : 결과 집합이 차지하는 메모리 양

* 비용사항이 표시된다는 것 -> 비용기반 최적화 방식으로 실행계획을 생성했다는 것

??? NESTED LOOPS 가 어떤 동작 하는지?

SQL 처리 흐름도(Access Flow Diagram)

SQL의 내부적인 처리 절차를 시각적으로 표현한 도표

조인 순서 : TAB1 -> TAB2

TAB1 : Outer Table 또는 Driving Table

엑세스 방식 : Scan

TAB2 : 1nner Table 또는 Lookup Table

액세스 방식 : 인덱스를 통한 랜덤 엑세스

TAB1과 TAB2 전체 테이블 스캔 -> 액세스 건 : TAB1 테이블의 총 건수

TAB1을 액세스한 후 즉， 테이블에서 읽은 해당 건에 대해 A. COL1 = :condition1 조건을

만족한 건만이 TAB2와 조인을 시도한다.

따라서 조인 시도 건수는 TAB1 에 주어진 조건인

A.COL1= : condition1을 만족한 건수가 된다.

인덱스 기본

인덱스

가. 트리 기반 인덱스

B-트리 인덱스

* Root Block, Branch Block, Leaf Block으로 구성

* Leaf Block = 인덱스 구성 칼럼 데이터+ 행의 위치를 가리키는 레코드 식별자(RID)

  리프 블록은 양방향 링크를 가짐 -> 오름,내림차순 쉬움

* 인덱스로 검색하는 일치 검색과 등가같은 범위 검색에 모두 적합

나. SQL Server의 클러스터형 인덱스

* 인덱스의 리프 페이지 = 데이터 페이지

* RID가 없다

* 사전에 비유

* 리프 페이지의 모든 로우( =데이티 )는 인텍스 키 칼럼 순으로 물리적으로 정렬되어 저장된다.

테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다.

그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

(전화번호부 한 권을 상호와 인명으로 동시에 정렬할 수 없는 것과 마찬가지다.)

전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

테이블에 존재하는 모든 데이터를 읽어 가면서 조건이 맞으면 결과로 추출, 아니면 버리는 방식.

* 고수위 마크 HWM : 데이터 블록 상의 최상위 위치

* I/O 한번에 여러 테이블 읽음

* 전체 스캔 방식을 선택하는 이유

1) SQL문에 조건이 존재하지 않는 경우

2) SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우

3) 옵티마이저의 취사 선택 : 조건을 만족하는 데이터가 많은 경우

4) 그 외.. 병렬 처리 방식 등

나. 인덱스 스캔

* 인덱스의 리프 블록은 인덱스 구성하는 칼럼과 레코드 식별자로 구성되어 있다

* I/O 한번에 한 테이블 읽음

1) 인덱스 유일 스캔 : 중복을 허락하지 않음,"="로 값이 주어짐

2) 인덱스 범위 스캔 : 한건 이상의 데이터 추출, "="사용 안하거나 비유일 인덱스 에서

3) 인덱스 역순 범위 스캔 : 내림차순

4) 인덱스 전체 스캔, 인덱스 고속 전체 스캔, 인덱스 스킵 스캔 등이 있다.

다. 전체 테이블 스캔과 인덱스 스캔방식의 비교

전체 테이블 스캔 : 인덱스 유무와 상관없이 사용 가능.

인덱스 스캔 : 사용 가능한 적절한 인덱스가 존재할 때 사용 가능

조인 수행 원리

NL Join

* 범위가 좁은 선행 테이블의 키값으로 후행 테이블에서 조인 수행

* 선행 테이블의 조건을 만족하는 행 만큼 후행 테이블 조인 반복

* 랜덤 엑세스 방식

* 결과를 가능한 빨리 화면에 보여줘야 하는 온라인 프로그램에 적당한 조인 기법

Sort Merge Join

* 스캔 방식으로 데이터 읽음

* 조인 컬럼을 기준으로 정렬한 뒤 조인 수행

* 인덱스 사용 안함

* 임시 영역(디스크) 사용 -> 정렬작업 부담

* 비동등 조인에서도 가능

Hash Join

* 대량의 조인 정렬 작업에 적합

* 두 조인의 문제 해결

* 선행테이블 결과로 해쉬 테이블 생성

  후행 테이블도 해쉬 테이블 생성

  두개 조인

* 인덱스 사용 안함

* "=" 동등 조인에서만 사용 가능

* 선행 테이블 : 먼저 해쉬 테이블 생성 : Build Input

  후행 테이블 : 해쉬 값의 존재 여부 검사 : Prove Input

3) 규칙기반은 항상 ( 인덱스 스캔 > 전체 테이블 스캔 )

4) 인덱스 범위 스캔은 결과 건수 만큼 반환, 한 건도 반환 안할 수 있다.