[SQLD] 옵티마이저optimazer / 인덱스index / 조인join

1. 옵티마이저와 실행계획

(1) 의미

- 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다.

즉, SQL실행시 실행계획을 수립하는 것이다.

- 사용자 요구사항을 만족하는 결과를 추출할 수 있는 다양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 것이 옵티마이저의 역할이다.

- 옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향을 미치게 된다.

- 옵티마이저는 실제로 SQL문을 처리해보지 않은 상태에서 최적의 실행 방법을 결정해야 하는 어려움이 있다.

(2) 옵티마이저 종류

① 규칙기반 옵티마이저 (RBO)

- 규칙 : 보편 타당성에 근거한 것들

- 우선순위

순위	엑세스기법	설명
1	Single row by rowid	ROW ID를 통해 테이블에 하나의 행(단일행)을 엑세스 하는 방식
2	Single row by cluster join	​클러스터 조인에 의한 단일행 엑세스하는 방식
3	Single row by hash cluster key with unique or primary key	​유일.기본키 가진 해시 클러스터 키 의한 단일행 엑세스하는 방식
4	Single row by unique or primary key	유일 인덱스를 통해 하나의 행을 엑세스 하는 방식
5	Cluster join	​클러스터 조인
6	Hash cluster key	​해시 클러스터 조인
7	Indexed cluster key	​인덱스 클러스터 키
8	Composite index	​복합 칼럼 인덱스
9	Single Column join	단일 칼럼 인덱스에 = 조건으로 검색하는 경우
10	Bounded range search on indexed columns	인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색하는 방식 BETWEEN, LIKE .. -제한범위 검색
11	Unbounded range search on indexed columns	인덱스가 생성되어 있는 칼럼에 한쪽 범위만 한정하는 형태로 검색하는 방식 >, >=, <= 등.. - 무제한 범위 검색
12	Sort merge join	​정렬 - 병합 조인
13	MAX or MIN of indexed column	​인덱스 컬럼에서 MAX / MIN 실행
14	ORDER BY on indexed column	​인덱스 컬럼에서 ORDER BY 실행
15	Full table scan	전체 테이블 스캔 -조건절에 주어진 조건을 만족하는 행만을 결과로 추출

② 비용기반 옵티마이저 (CBO)

- SQL문을 처리하는데 필요한 비용( 소요시간 또는 자원 사용량)이 가장 적은 실행 계획을 선택하는 방식

- 비용을 예측하기 위해서 규칙기반 옵티마이저는 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계 정보 등을 이용한다.

- 정확한 통계정보를 유지하는 것은 비용기반 최적화에서 중요한 요소임.

- 동일 SQL문도 서로 다른 실행 계획이 생성될 수 있음 / 다양한 한계들로 인해 실행계획의 예측 및 제어가 어려움

- 구성 요소

ㆍ 질의 변환기 : 사용자가 작성한 SQL문을 작업하기가 보다 용이한 형태로 변환하는 모듈

ㆍ 대안 계획 생성기

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

: 현실적인 제약으로 인해 생성된 대안 계획들 중에서 최적의 대안 계획이 포함되지 않을 수도 있음

ㆍ 비용 예측기 : 대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모들

③ 실행계획

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

- 실행계획을 생성: SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 방법

- 옵티마이저는 다양한 처리 방법들 중에서 최적의 실행계획을 예측해서 생성해 준다.

- 실행계획을 보고 SQL이 어떻게 실행되는지 정확히 이해할 수 있다면 보다 향상된 SQL 이해 및 활용을 할 수 있다.

- 최적화 정보 : 실제로 SQL을 실행하고 얻은 결과가 아니라 통계 정보를 바탕으로 옵티마이저가 계산한 예상치이다.

- 연산(Operation) : 여러 가지 조작을 통해서 원하는 결과를 얻어 내는 일련의 작업

조인 기법 : 두 개의 테이블을 조인할 때 사용할 수 있는 방법

엑세스 기법 : 하나의 테이블을 엑세스 할 때 사용할 수 있는 방법

최적화 정보

ㆍCost : 상대적인 비용

ㆍCard : 주어진 조건을 만족한 결과 집합 혹은 조인 조건을 만족한 결과 집합의 건수

ㆍBytes : 결과 집합이 차지하는 메모리 양을 바이트로 표시한 것

④ SQL 처리 흐름도

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

- SQL문 처리를 위한 조인 순서, 테이블 엑세스 기법과 조인 기법 등을 표현할 수 있다.

2. 인덱스 index

(1) 인덱스 특징

- 빠른 검색 및 조회 가능.

- 기본적인 목적 : 성능의 최적화

- 인덱스의 칼럼 순서는 질의 성능에 중요한 영향을 미치는 요소

(2) B트리 기반 인덱스

① 루트 블록 ROOT

- 브랜치 블록 중에 최상위에 있는 블록

- 인덱스를 구성하는 칼럼의 데이터와 레코드 식별자로 구성되어 있음

② 브랜치 블록 BRANCH

- 다음 단계의 블록을 가리키는 포인트를 가지고 있음

③ 리프 블록 LEAF

- 트리의 최하위 단계에 존재

- 양방향 링크를 가지고 있음-> 이것을 통해 오름차순과 내림차순 검색 가능.

(3) 전체 테이블 스캔

① 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식으로 검색

② 옵티마이저가 연산으로서 FTS(full table scan)방식을 선택하는 이유

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

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

- 옵티마이저의 취사 선택

- 그 밖의 경우 : 병렬처리 방식으로 처리하는 경우 / 전체 테이블 스캔 방식의 힌트를 사용한 경우

(4) 인덱스 스캔

① 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 엑세스 기법

② 인덱스에 존재하지 않는 컬럼의 값이 필요한 경우에는 현재 읽은 레코드 식별자를 이용하여 테이블을 엑세스 해야 함

③ 인덱스 스캔 방법

- 인덱스 유일 스캔

: 유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식

: 중복을 허락하지 않는 인덱스임

: 유일 인덱스 구성 칼럼에 대해 모두 = 로 값이 주어진 경우에만 가능한 인덱스 스캔 방식

- 인덱스 범위 스캔

: 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식

(5) 전체 테이블 스캔 vs 인덱스 스캔 방식 비교

전체 테이블 스캔	인덱스 스캔 방식
인덱스의 존재 유무와 상관없이 항상 이용 가능	사용 가능한 적절한 인덱스가 존재할 때만 이용가능
극히 일부의 데이터를 찾을 때, 비효율적인 검색을 하게 됨	극히 일부의 데이터를 찾을 때, 몇 번의 I/O 만으로 원하는 데이터를 쉽게 찾을 수 있음
대부분의 데이터를 찾을 때, 어차피 대부분의 데이터를 읽을 거라면 더 유리할 수 있음	대부분의 데이터를 찾을 때, 검색 시간이 오래 걸림
한번의 I/O 요청으로 여러 블록을 한꺼번에 읽음	불필요하게 다른 블록을 더 읽을 필요가 없음 한 번에 I/O 요청에 한 블록씩 데이터를 읽음

3. 옵티마이저 조인

- 두 개 이상의 테이블을 하나의 집합으로 만드는 연산

- 두 테이블 사이에서 수행

- 조인을 수행할 때 조인 단계별로 다른 조인 기법을 사용할 수 있음

(1) Nested Loop Join

- 랜덤엑세스가 발생하는데, 소량의 업무 처리에 적합. 양줄여야 성능향상됨.

※ 랜덤엑세스란? 선행테이블 인덱스 검색후, 후행 테이블의 인덱스를 찾는다.

- 반복문과 유사한 방식으로 조인을 수행

- 선행 테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행함 (선행테이블 먼저찾은후, 후행 테이블 조인)

- 선행: outer table / 후행: inner table

- 선행 테이블의 크기가 작은것 먼저 찾는것이 중요함. 스캔의 범위를 줄일 수 있기때문 -> 성능 향상.

- 처리 범위가 좁은 것이 유리함

- 조인 작업 방법

ㆍ선행 테이블에서 주어진 조건을 만족하는 행을 찾음

ㆍ선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행

ㆍ선행 테이블의 조건을 만족하는 모든 행에 대해 반복 작업 수행

(2) Sort Merge Join

- 양쪽 테이블의 처리범위를 각자 엑세스하여 정렬한 결과를 차례로 스캔하면서 순차비교 함으로 인덱스를 사용하지 않음

- 두 테이블을 Sort-Area라는 메모리공간에 모두 로딩-> Sort 수행 -> Merge 수행.

- 정렬 비용 필요 / 드라이빙 테이블 의미 없음 / Non-Equi 조인 가능

- 정렬 발생하기 때문에 데이터가 많으면 성능저하 될 수 있음

- 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행

- 주로 스캔 방식으로 데이터를 읽음

- 넓은 범위의 데이터를 처리할 때 주로 이용됨

- 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능함

- 조인 작업 방법

ㆍ 독립적으로 각각의 테이블에서 데이터를 추출할 수 있음

ㆍ 독립적으로 각각의 테이블에서 조인키를 기준으로 정렬 작업 수행

ㆍ 정렬 결과를 이용하여 조인을 수행하여 조인에 성공하면 추출 버퍼에 넣음

​(3) Hash Join

- 두 테이블중 작은 테이블(선행테이블)을 HASH메모리에 로딩하고, 두 테이블의 조인키 사용해서 해시테이블 생성한다.

- 일반적으로 조인되는 두 집합의 크기 차이가 나지 않는 경우나 하드웨어 자원이 부족하지 않을 경우에는 성능이 좋음

- 선행테이블이 충분히 메모리에 로딩되는 크기여야 한다. CPU 연산이 많이 듦.

- 조인을 수행할 테이블의 조인 칼럼을 기준으로 해쉬 함수를 수행하여 서로 동일하나 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지를 비교하면서 조인을 수행

- NL JOIN의 랜덤 엑세스 문제점과 SMJ의 문제점인 정렬 작업의 부담을 해결하기 위한 대안으로 등장

- 동등 조인에서만 사용할 수 있음

- 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋음

- 조인 작업 방법

ㆍ 선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 모든 데이터에 대한 해쉬 테이블 생성

ㆍ 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 잡음

ㆍ 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행