[SQLD] SQL최적화와 기본원리 : 옵티마이저, 실행계획, 인덱스, 테이블 스캔, Nested Loop Join, Sort Merge Join, Hash Join

https://lotuus.tistory.com/42

[SQLD] 21.11.20 제43회 SQL개발자 후기, 공부방법, 정리본, 43회 출제문제

 

[목차]

💚 옵티마이저 Optimizer : 최적의 실행계획을 결정하는 것
   옵티마이저 종류 : 규칙기반/비용기반
   실행계획 EXECUTION PLAN
💚 인덱스 INDEX : 찾아보기
   B-TREE 인덱스 : 가장 일반적인 인덱스 
      SQL Server의 클러스터형 인덱스 (저장구조에 따른 분류)
💚 테이블 스캔
   FULL TABLE SCAn
   INDEX SCAN
💚 JOIN 조인 기법의 종류
   Nested Loop Join
   Sort Merge Join
   Hash Join

 

💚 옵티마이저 Optimizer : 최적의 실행계획을 결정하는 것

옵티마이저는 SQL문에 대해 최적의 실행방법(계획)을 결정한다.

관계형 데이터베이스는 옵티마이저가 결정한 실행 계획대로 처리하여 결과를 사용자에게 전달한다.

= 옵티바이저의 실행 계획은 수행 속도에 가장 큰 영향을 미친다.

 

옵티마이저 종류

	규칙기반 옵티마이저 Rule Based Optimizer	비용기반 옵티마이저 Cost Based Optimizer
실행계획 생성방법	SQL문의 인덱스 유무, 연산자 종류, 참조객체 등의 정보에 따라 우선순위가 정해져있고,  우선순위를 기반으로 실행계획을 생성한다.  우선순위가 높은 규칙이 적은 일량으로 작업을 수행하는 것이라 판단하는 것이다. 1. ROWID를 사용한 단일 행인 경우 2. 클러스터 조인에 의한 단일 행인 경우 3. 유일하거나 PK를 가진 해시 클러스터 키에 의한 단일 행인 경우.....	SQL문을 처리하는데 필요한 비용(소요시간, 소요자원 등)이 가장 적은 실행계획을 선택한다. 현재는 대부분 비용기반 옵티마이저만 제공한다. 비용을 예측하기 위해 규칙기반 옵티마이저가 사용하지 않는 각종 통계정보를 참고한다. (=정확한 통계정보를 유지해야한다)

 

 

 

실행계획 EXECUTION PLAN

SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 나타낸다.

실행계획 구성요소	조인순서	조인작업 수행 시 참조하는 테이블의 순서
	조인기법	두 테이블을 조인할 때 사용하는 방법. NL JOIN, HASH JOIN, SORT MERGE JOIN...등
	액세스기법	한 테이블을 액세스할 때 사용할 수 있는 방법. INDEX SCAN, FULL TABLE SCAN... 등
	최적화정보	각 단계마다 예상되는 비용사항. 비용사항이 표시된 것은 비용기반 최적화 방식으로 실행계획을 세웠다는 것을 의미하고, COST(상대적인 비용정보), CARD(조인을 만족한 집합의 건수), BYTES(결과집합이 차지하는 메모리양)가 있다.
	연산	여러 조작을 통해 원하는 결과를 얻어내는 일련의 작업

 

📌 옵티마이저와 실행계획에 대한 설명으로 부적절한 것을 2개 고르시오

1) SQL처리 흐름도는 성능적인 측면의 표현은 고려하지 않는다.    //성능측면도 표현할 수 있다.

2) 규칙기반 옵티마이저에서 제일 높은 우선순위는 행에 대한 고유주소를 사용하는 방법이다.

3) SQL처리 흐름도는 인덱스 스캔 및 전체 테이블 스캔 등의 액세스 기법을 표현할 수 있다.

4) 인덱스 범위 스캔은 항상 여러건의 결과가 반환된다.            //결과 건 수 만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있다.

 

💚 인덱스 INDEX : 찾아보기

원하는 데이터를 쉽게 빠르게 찾을 수 있도록 하는 '찾아보기'와 유사한 개념이다.

테이블을 기반으로 선택적으로 생성할 수 있고, 여러개를 생성할 수 있다.

단, DML작업은 테이블과 인덱스를 함께 변경해야하기때문에 오히려 느려질 수 있다.

 

B-TREE 인덱스 : 가장 일반적인 인덱스

Root Block	루트블록. 가장 최상위에 위치한 블록
Branch Block	브랜치블록. 다음 단계 블록을 가리키는 포인터를 가지고 있다.
Leaf Block	리프블록. 가장 하위에 위치한 블록. 인덱스 데이터는 칼럼값, RID(RowID)순서로 정렬된다. 양방향링크를 가지고 있어 오름차순, 내림차순 검색이 쉽다.

 

 

SQL Server의 클러스터형 인덱스 (저장구조에 따른 분류)

구분	클러스터드 인덱스	넌클러스터드 인덱스
공통점	둘다 B-TREE 방식을 이용한다.
RID 필요여부	- 리프페이지 = 데이터페이지    => 따라서 탐색에 필요한 RID가 필요없다.	- 데이터의 위치정보(RID)를 인덱스로 구성한다.
정렬	인덱스 칼럼으로  인덱스도 정렬 테이블의 데이터도 정렬	인덱스 칼럼으로  인덱스만 정렬 테이블의 데이터는 그대로
인덱스 수	테이블 당 1개 인덱스 생성가능	테이블 당 여러개의 인덱스 생성 가능
검색속도	빠르다	느리다
인덱스 공간	적게 차지한다	많이 차지한다

클러스터드 인덱스

 

 

💚 테이블 스캔

구분	FULL TABLE SACN 전체 테이블 스캔	INDEX SCAN 인덱스 스캔
설명	모든 데이터를 읽어가며 조건에 맞으면 추출, 조건에 안맞으면 버리는 방식으로 검색한다. 재사용성이 떨어지며, 블록들이 메모리에서 곧 제거될 수 있도록 관리한다. 따라서 SQL문에 조건이 존재하지 않는 경우, 조건에 사용가능한 인덱스가 존재하지 않는 경우 등에 사용한다.	인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법이다. 인덱스에 존재하지 않는 칼럼의 값이 필요한 경우에는 현재 읽은 RID를 이용해 테이블에 엑세스 해야한다. = 사용 가능한 인덱스가 존재해야 사용할 수 있다. 인덱스 구성 칼럼의 순서, RID로 정렬된다. 데이터를 읽으면 결과도 정렬되어 반환된다.
	모든 테이블의 위치를 알고서 데이터를 읽는다 = 불필요하게 다른 데이터를 읽어야한다 = 한 요청에 여러 블록을 읽는다	인덱스에 존재하는 RID를 이용해 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽는다. = 불필요하게 다른 데이터를 읽을 필요 없다. = 한 요청에 한 블록씩 읽는다
	테이블의 대부분의 데이터를 찾을 때 효과적이다.	테이블의 일부 데이터를 찾을 때 효과적이다.

 

 

💚 JOIN 조인 기법의 종류

A와 B 두 테이블을 먼저 조인하고, 결과와 C 테이블을 조인하는 것(A-B-C)과

A와 C 두 테이블을 먼저 조인하고, 결과와 B 테이블을 조인하는 것(A-C-B)의 결과는 다르다!

 

두 테이블을 조인할 때는 서로 다른 조인 기법을 사용할 수 있다.

(A와 B를 조인할때는 NL 조인으로, B와 C를 조인할때는 HASH 조인으로 처리 가능)

구분	Nested Loop Join	Sort Merge Join	Hash Join
조인 방법	선행 테이블의 결과를 추출하고 후행 테이블을 읽으면서 조인을 수행한다. (중첩된 반복문과 유사) 선행 테이블의 조건을 만족하는 행 수만큼 반복됨. 조인이 성공하면 바로 결과를 보여줄 수 있다.	조인 칼럼을 기준으로 테이블을 정렬하여 조인을 수행한다. 조인할 테이블이 이미 정렬되어있다면 정렬 작업은 발생하지 않을수도 있다.	조인 칼럼을 기준으로 해쉬함수를 수행하여 서로 동일한 해쉬값을 갖는 것들 사이에서 실제 값이 같은지를 비교하며 조인을 수행한다. NL join의 랜덤액세스 문제점과 Sort Merge Join의 정렬작업 부담 해결의 대안으로 등장했다. CPU연산을 많이하여 충분한 메모리 공간 필요하다.
조인 제한		동등조인, 비동등조인에서 사용가능	동등조인에서만 사용할 수 있다.
데이터 읽는방법	랜덤 액세스 방식	스캔 방식
처리범위	좁은 범위가 유리하다.	NL 조인에서 부담되던 넒은 범위의 데이터를 처리할 때 이용된다. (그러나 정렬이 많아지면 성능이 떨어질 수 있다)	정렬작업이 없어 정렬이 부담되는 대량 배치작업에 유리하다.
동작방법	- 선행 테이블에서 주어진 조건을 만족하는 행을 찾음 - 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행 - 선행 테이블의 조건을 만족하는 모든 행에 대해 1번 작업 반복 수행	- 선행 테이블 조인 키를 기준으로 정렬 작업을 수행 - 후행 테이블 조인 키를 기준으로 정렬 작업을 수행 - 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출버퍼에 넣음	- 선행 테이블에서 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블을 생성 - 후행 테이블에서 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음 - 조인에 성공하면 추출버퍼에 넣음 - 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행

 

 

 

 

#SQLD #옵티마이저 #Optimizer #실행계획 #인덱스 #B-Tree인덱스 #클러스터드인덱스 #논클러스터드인덱스 #테이블스캔 #FullTableScan #IndexScan #JOIN기법 #NestedLoopJoin #SortMergeJoin #HashJoin