본문 바로가기
카테고리 없음

[SQLD] 이론 - 옵티마이저, 인덱스, 조인실행 방법

by 꾸리_No9 2024. 2. 29.

 

SQL 옵티마이저 인덱스

 

절차형 SQL : 

(1) Procedure :   DML, DCL  구분에서 수행됨
입력, 출력 가능

특징:시스템에서 매일 반복되는 작업을 프로시저를 통해 관리 및 수행됨 
         (ex. 월 마감 리포트, KPI 리포트, 대시보드 리포트) 
         리턴값 있을 수도 있고, 없을 수도 있음. 여러개 리턴값 있을 수 있음
(2) Trigger : 일반적 DML 구분에서 수행됨

특징: 이벤트가 있어야 실행된다. 
Auto commit으로 Commit과 Roll back 안됨
(2) User Defined
    사용자 정의 함수 :
DBMS가 만든 내장 함수가 아닌, 사용자가 만든 함수
입력만 가능,Select 함수로 제한

특징 : 리턴값 있음 



옵티마이저 (최적화) :

대상 : 사용자가 질의한 SQL 구문 중 최적 (Optimized) 실행방법을 결정하는 역할
(1) 규칙 기반 옵티마이저 (+) 우선순위 있음, 예측 가능, 경로 유도 쉬움
(+) 인덱스 access > 전체 access 우선순위 높음

2개 테이블 모두 인덱스가 없는 경우 : Short Merge Join
2개 테이블 중 1개라도 인덱스가 있는 경우 : Nested Loop Join
(2) 비용 기반 옵티마이저 (+) 처리 비용이 가장 낮은 실행계획 생성 

비용 예측을 위해 통계 정보 사용, 통계정보 없을 시 불확 실한 실행계획 생성할 수 있음



인덱스 (목차) :

책 읽을 때 Index 란에 어려운 용어 개념/정의 수록집
(1)Tree 기반 인덱스 : B tree index  root > branch > leaf block > 테이블 

특징 : 같은 형제들 끼리 양방향 링크가 되어 있음
(2) 전체 테이블 스캔 조건에 맞으면 추출, 아니면 버리는 방식

When : where 절이 없음. 주어진 조건에 사용 가능한 index가 없는 경우
(3) Index 스캔  index unique scan
index range scan 

 

 

조인 실행 기법:

(1) Nested Loop join   When :소량 데이터 통계/ 조회 시 or Join 할 테이블이 적을 때

-. 선행, 후행 테이블로 중첩된 반복문처럼 생긴 조인
-. 선행에 추출되는 건수가 적을 수록 유리 
-. 2개 테이블 중 1개 데이터 량이 작을 때, 작은 테이블을 드라이빙 테이블로 선택, 처리

-. 중첩된 반복문과 유사한 형식
-. 선행 테이블 조건을 만족하는 건수만큼 반복수행
(2) Short Merge Join  When : PK - FK  관계 중 FK 인덱스가 없을 때 사용
-. 추출 되야 하는 것들을 빼서 정렬 후 합침

  
(1) 선행 T: 조건 만족 행 찾기 
(2) 선행 T: Join Key
(3) 후행 T: Join Key  찾고, Join 시도 
(4) 후행 T : Index
(5) Index 추출한 레코드 식별자 사용
(6) Table access 시
(3) Hash Join  When : 대량 데이터 통계/조회 시 

-. 추출하고 가운데서 합치는 것은 동일 (sm)
-. 해쉬함수를 적용 > 동일한 해쉬값 적용
-. 대량 - 대량 비교시 효과

-. Column, index 없어도 사용 가능
-. '='동등 조건만 사용가능
-. Hash table을 메모리에 생성 필요, 메모리가 모자르면 임시 (디스크)에 젖장
-. CPU 많이 사용함