[SQLD Part3] 03 SQL 최적화의 원리

1 옵티마이저와 실행 계획

1.1 옵티마이저
- SQL의 실행계획을 수립, 실행 => 성능 차이
 
1.2 옵티마이저 특징
- 데이터 딕셔너리에 있는 통계를 바탕으로 비용 산정하여 최저 비용 계획을 실행
 
1.3 옵티마이저의 필요성
- 큰 테이블 : EMP / 작은 테이블 : DEPT ===> 교집합을 AND로 도출하는 경우
- 작은 테이블 전체를 읽고 EMP와 같은 행을 찾는게 최적의 계획
- 옵티마지어에게 힌트를 줘서 실행계획 변경 가능
 
1.4 옵티마이저 실행계획 확인
- SQL은 실행계획을 PLAN_TABLE에 저장
- TOAD > Exception Plan Current SQL 메뉴에서 확인가능
ex) TABLE ACCESS FULL  => EMP 테이블을 모두 읽었다는 표시

개발자SQL >
Parsing(문법검사,구문분석) >
비용기반/규칙기반 옵티마이저(데이터딕셔너리에 있는 통계를 기반으로 선택. 기본값은 비용기반) >
실행계획 (PlanTable에 저장) >
SQL실행 >
Fetch (데이터 인출)

* 옵티마이저 엔진 구조
- Query Transformer : 효율적 실행 위해 옵티마이저가 변환? SQL 변환되어 도 그 결과는 동일
- Estimator : 통계정보를 사용하여 실행비용 계산
- Plan Generator : 실행 계획 수립
 
1.5 규칙기반 옵티마이저
- 15개의 우선순위 (1위가 ROWID를 사용한 단일 행)

SELECT /* RULE */ *    -- 규칙기반 옵티마이저라는 힌트
FROM EMP
WHERE ROWID = 'AAAHYhAABAAALNJAAN';

 
1.6 비용기반 옵티마이저
- 기본적으로 사용
- 오브젝트 통계와 시스템 통계를 사용해서 총비용(자원사용량,소요시간) 계산
- 총비용 적은쪽으로 실행 계획 수립
- 통계정보 부적절하면 성능 저하
 

2 인덱스

2.1 인덱스
- 데이터 빠른 검색 방법 제공
- 오름차순/내림차순 탐색 가능
- 하나의 테이블에 여러개 인덱스 / 여러개 인덱스에 하나의 칼럼
* 인덱스 구조
- Root Block : 가장 상위 노드
- Branch Block : 다음 단계 주소를 가지고 있는 포인터
- Leaf Block : 인덱스키와 ROWID(물리적 행주소)로 구성, Double linked list로 되어 있어서 양방향 탐색 가능, 인덱스 키 읽으면 ROWID를 사용하여 EMP 테이블 행을 읽을 수 있다.
 
2.2 인덱스 생성
- 한 개 이상의 컬럼을 사용해서 생성

CREATE INDEX IND EMP
ON EMP(ENAME ASC,SAL DESC);

-- ENAME은 오름차순(ASC 생략가능), SAL은 내림차순으로 인덱스 생성
-- IND EMP 라는 이름을 가진 인덱스

 
2.3 인덱스 스캔
① 인덱스 유일 스캔
- 중복되는 값이 없는 컬럼인 경우 Index Unique Scan 발생

SELECT * FROM EMP WHERE EMPNO = 1000;

-- 1000인 값이 하나 밖에 없다.

② 인덱스 범위 스캔
- 특정 범위를 조회하는 Where문 사용
- Leaf Block의 특정 범위를 스캔

SELECT EMPNO
FROM EMP
WHERE EMPNO >= 1000;

③ 인덱스 전체 스캔
- 인덱스 키가 많은 경우에 LeafBlock의 처음과 끝까지 전체를 읽음

SELECT ENAME, SAL
FROM EMP
WHERE ENAME, Like '%' AND SAL > 0;

* High Watermark : 테이블에 데이터가 저장된 블록에서 최상위 위치 의미
 
2.4 실행 계획
- 번호 순서대로 읽으면 된다.
- 외부 테이블(Outer table) : 먼저 조회되는 테이블
- 내부 테이블(Inner table) : 그 다음 조회되는 테이블
 
2.5 옵티마이저 조인
① Nested Loop 조인
- 2개 이상의 테이블이 있을 때 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 방식.
- 외부 테이블에서 데이터를 먼저 찾고 테이블을 조인하는 방식
- 선행 테이블의 처리 범위를 액세스하면서 추출된 값으로 연결할 테이블을 조인
- 좁은 범위에서 유리한 성능을 보여줌
- 순차적으로 처리하며 랜덤액세스 위주
- 후행 테이블에는 조인을 위한 인덱스 생성 필요
- 랜덤액세스의 양을 줄여야 성능향상
- 실행속도 : 선행 테이블 사이즈 * 후행 테이블 접근 횟수
==> 자바에서 중첩 for문 수행하는 것과 같음

SELECT /* ordered use nl(b) */ *
FROM EMP a, DEPT b
WHERE a.DEPTNO = b.DEPTNO
AND a.DEPTNO = 10;

-- use nl은 FROM 절에 테이블 순서대로 조인을 하라는 뜻. 네스티드 루프 조인이 강제 수행된다.

② Sort Merge 조인
- 두 개의 테이블을 SORT_AREA라는  메모리 공간에 모두 로딩하고 정렬 시작
- 정렬 완료되면 두 테이블을 병합(Merge)
- 랜덤 액세스 하지 않고 스캔하면서 수행
- 데이터 양이 많으면 성능이 떨어진다.

SELECT /*+ ordered use_merge(b) */ *
FROM EMP a, DEPT b
WHERE a.DEPTNO = b.DEPTNO
AND a.DEPTNO = 10;

-- use_merge 힌트로 SORT_MERGE 강제 수행

③ Hash 조인
- 두 테이블 중 작은 테이블을 HASH 메모리에 로딩 (작은 테이블이 메모리에 로딩돼야 하는 크기여야 한다.)
- 조인키 사용해서 해시 테이블 생성
- 해시함수 사용해서 주소 계산
- 주소로 테이블 조인하기 때문에 CPU 연산이 많다.
- Equal Join에서만 사용 가능
- 조인컬럼에 인덱스 없어도 사용가능

SELECT /*+ ordered use hash(b) */ *
FROM EMP a, DEPT b
WHERE a.DEPTNO = b.DEPTNO
AND a.DEPTNO = 10;

* 파티션 인덱스
① Global index : 여러개 파티션에서 하나의 인덱스 사용
② Local Index : 해당 파티션 별로 각자의 인덱스 사용
③ Prefixed Index : 파티션 키와 인덱스 키가 동일
④ Non Previxed Index : 파티션 키와 인덱스 키가 다름
 
* 힌트 종류
- ORDERED : FROM 절에 테이블 순서대로 접근
- LEADING(A,B) : FROM 절에 종속적이지 않고 테이블 순서 접근 명시적
 
* 외래키 추가
CREATE TABLE 가족
(이름 varchar2(20),
나이 number(10) default 1,
부양사번 varchar2(20),
CONSTRAINT faily_pk PRIMARY KEY(이름, 나이),
CONSTRAINT family_fk Foreign key (부양사번)  REFERENCES 사원(사번)
);
 
ALTER TABLE EMP
ADD (CONSTRAINT FK_DEPT FOREIGN KEY (DPET) REFERENCES DEPT(DEPTNO));
 
* 인덱스 변형 되는 경우 (인덱스 사용 안함)
- 컬럼에 직접 산술연산
- NOT, NULL, LIKE 앞에 % 붙은 경우
- IN : IN 안에 데이터 비율 높으면 FULL SCAN을 수행해서 인덱스 타지 않음
- 복합 인덱스에서 정의한 순서대로 실행 되지 않았을 경우
- 문자값 데이터 컬럼에는 정확한 타입을 붙여줘야됨? ex) WHERE age = to_number('30')
 
* count(*) : null 값까지 센다
* count(컬럼명) : null 값 제거
 
* null 값은 내림차순 일때 최상위
 
* SQL 서버 : null 값을 인덱스 맨 앞에 저장
* ORACLE : null 값을 인덱스 맨 뒤에 저장
 
* ORDER BY ~ NULLS LAST : NULL을 마지막 행으로 강제 이동
* ORDER BY ~ NULLS FIRST : NULL을 첫번째 행으로 강제 이동
 
* in : 일치하는게 있으면 반환
* not in : null이 있으면 false를 반환하여 아무것도 조회 안됨
 
* exists : 서브쿼리가 true이면 메인쿼리 수행
* not exists : 서브쿼리가 false이면 메인쿼리 수행 (서브쿼리 수행시 null이 있으면 false를 반환함으로 메인쿼리가 수행된다.)
 
* lag(empno,2,9999) over(ordery by empno) => 현재 행 중심으로 두번째 이전 행의 값을 표시하되 가져올 값 없으면 기본값인 9999
* lead(empno,2,9999) over(ordery by empno) => 현재 행 중심으로 두번째 다음 행의 값을 표시하되 가져올 값 없으면 기본값인 9999