DB(1) SQL 최적화 기본원리: 실행계획
SQLT를 따기전 그냥 지식 공부
1. 실행계획? (Execution Plan)
실행계획 이란 SQL에서 요구한 사항을 처리하기 위한 __옵티마이저__가 수립하는 절차와 방법을 의미.
동일한 SQL에 대해 결과를 낼수 있는 다양한 처리 방법(실행계획) 이 존재할 수 있지만 각 처리 방법마다 실행 시간은 다를수가 있다
옵티마이저 는 다양한 처리 방법들 중에서 가장 효율적인 방법을 찾아준다. (최적의 실행계획을 생성)
2. 실행계획 구성요소
-
조인 순서 (JOIN ORDER)
-
조인 기법 (JOIN METHOD)
-
액세스 기법 (ACESS METHOD)
-
최적화 정보 (OPTIMIZATION INFORMATION)
-
연산 (OPERATION)
3. 조인 순서 (JOIN ORDER)
수행할 때 참조하는 테이블의 순서
FROM A
INNER JOIN B 과 같은 조인이 있을때, 조인 작업을 위해서는 먼저 A테이블을 읽고 B 테이블을 읽는 작업을 수행한다면 조인순서는 A->B 이다.
(논리적으로 가능한 조인 순서는 N! 만큼 존재하며, N은 FROM 절 다음에 나온 테이블 수이다.)
# 조인이란?
조인이란 두개 이상의 테이블을 __하나의 집합__으로 만드는 연산
조인 연산은 두 테이블 사이에서 수행된다.
# concat
4. 조인 기법 (JOIN METHOD)
두 개의 테이블을 조인할 때 사용할 수 있는 방법 NL JOIN, HASH JOIN, SORT MERGE JOIN 등
# 조인 수행원리?
FROM 절에서 ABC 라는 세개의 테이블이 존재하더라도 세개의 테이블이 동시에 조인이 수행되는것은 아니다. 세 개의 테이블 중에서 먼저 두개의 테이블에 대한 조인이 수행된 후 그 수행된 조인 결과와
나머지 테이블 사이에서 조인이 수행이 된다. 이러한 작업은 FROM 절에 나열된 모든 테이블을 조인할 때까지 반복 수행된다.
A-> B -> C가 될수도 있고 A->C->B가 될수도 있음
4-1. NL 조인
NL Join은 프로그래밍에서 사용하는 반복 문과 유사한 방식으로 조인을 수행한다.
반복문 외부에 있는 테이블을 선행테이블 또는 외부 테이블 (OUTER TABLE) 이라고 하며,
반복문 내부에 있는 테이블을 후행테이블 또는 내부 테이블 (INNER TABLE) 이라고 한다.
for (OuterRow oRow : OuterTable) {
for (InnerRow iRow : InnerTable)
}
요런 건가,?
먼저 선행테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다.
이 작업은 선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행된다.
NL Join에서는 선행 테이블의 조건을 만족하는 행의 수가 많으면, 그 만큼 후행 테이블의 조인 작업은 반복 수행되게 된다.
!!즉 결과 행의 수가 적은 테이블을 조인 순서상 선행 테이블로 선택하는것이 전체 일량을 줄일수 있는 방법이다!!
NL Join은 랜덤 방식으로 데이터를 액세스 하기 때문에 처리 범위가 좁은것이 유리!
NL Join 작업 방법
1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
-> 이때 선행 테이블에 주어진 조건을 만족하지 않는 경우에는 해당 데이터는 필터링 된다.
2-1. 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행
-> 조인 시도
2-2. 후행 테이블의 인덱스에 선행 테이블의 조인 키가 존재하는지 확인
-> 선행 테이블의 조인값이 후행 테이블에 존재 하지 않으면 선행 테이블 데이터는 필터링 된다. (조인작업x)
3. 선행 테이블의 조건을 만족하는 모든 행에 대해 1번 작업 반복 수행
-> 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 엑세스 -> 인덱스 스캔을 통한 테이블 액세스 후행 테이블에 주어진 조건까지 모두 ㅁ만족하면 해당 행을 추출버퍼에 넣음
-> 반복 수행
4. 조인에 성공하면 바로 조인 결과를 사용자에게 보여줄수 있다. 그래서 결과를 가능한 빨리 화면에 보여줘야 하는 온라인 프로그램에 적당한 조인 기법
;
4-2 Sort Merge Join
Sort Merge Join은 조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행
NLJoin -> 랜덤 엑세스 Sort Merge Join -> 스캔 방식 Sort Merge Join은 랜덤 엑세스로 NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되는 조인 기법
그러나 Sort Merge Join은 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영여(디스크)를 사용하기 때문에 성능이 떨어질 수 있다.
5. 액세스 기법 (ACCESS METHOD)
하나의 테이블을 액세스할 때 사용할 수 있는 방법
인덱스를 이용하는 __인덱스 스캔(index scan)__과 테이블 전체를 모두 읽는 __전체 테이블 스캔 (FULL TABLE SCAN)__등이 존재
6. 최적화 정보 (OPTIMIZATION INFORMATION)
옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것
최적화 정보에는 COST, CARDINALITY BYTE 가 존재
COST는 상대적인 비용정보만 적혀있음,
CARDINALITY는 주어진 조건을 만족한 결과 집합 혹은 조인 조건을 만족한 결과 , 집합의 건수를 의미
BYTES는 결과 집합이 차지하는 메모리 양을 바이트로 표시한 것
7. 연산
여러 가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업
연산에는 조인 기법, 액세스 기법, 필더, 정렬, 집계 뷰등 다양한 종류가 존재한다.