[과목 II] 제 3장 SQL 최적화 기본 원리

옵티마이저(Optimizer)

가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.

옵티마이저 종류

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

오라클 8 이하의 버전에서 기본으로 설정된 옵티마이저이며, 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 선택한다.

• 사전에 정의된 규칙 기반
• 실행 우선순위(Ranking)
• 인덱스 존재 시 가장 우선시 사용
• 사용자 SQL 숙련도
• 판단이 매우 규칙적이어서 실행 예상 가능
• 예측 통계 정보 요소 무시
• AND 중심으로 양쪽 '=' 시 Index Merge 사용

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

테이블 및 인덱스 등의 통계 정보를 활용하여 SQL 문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행 계획을 선택하는 옵티마이저이다.

• 최소 비용 계산 실행 계획 수립
• 액세스 비용(Cost)
• Cost에 의한 결정
• 옵티마이저 예측 성능
• 통계 정보를 통한 현실 요소 적용
• 최소 성능 보장 계획의 예측 제어가 어려움
• AND 중심으로 양쪽 '='시 분포도 별 Index 선택

실행계획

사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업 절차이다.

즉 예상 정보이다.

실행계획 정보의 구성요소

• 조인 기법
• 연산
• 액세스 기법
• 최적화 정보

실행계획의 실행 순서

1. 위에서 아래로 읽어 내려가면서 제일 먼저 읽을 스텝을 찾는다.
2. 내려가는 과정에서 같은 들여 쓰기가 존재한다면 무조건 위 → 아래 순으로 읽는다.
3. 읽고자 하는 스텝보다 들여 쓰기가 하위 스텝에 존재한다면, 가장 안쪽으로 들여 쓰기 된 스텝을 시작으로 하여 한 단계씩 상위 스텝으로 읽어 나온다.

정리하면, 실행 계획을 읽는 순서는 위에서 아래로, 안에서 밖으로 읽는다.

관계형 데이터베이스

서로 관련된 데이터 지점에 대한 접근을 저장 및 제공하는 데이터베이스 유형이다.

• 데이터를 테이블에 직관적으로 간단하게 나타내는 관계형 모델을 기반으로 한다.
• 테이블의 각 행은 키라는 고유 ID가 포함된 레코드이다.

인덱스(Index)

데이터베이스 테이블에 대한 검색 성능 속도를 높여주는 자료구조이다.

• 기본 인덱스는 UNIQUE & NOT NULL의 제약 조건을 갖는다.
• 보조 인덱스는 고유한 키 값들만 나타날 수 있다.
• 자주 변경되는 속성은 인덱스를 정의할 좋은 후보이다.
• 테이블의 전체 데이터를 읽는 경우는 인덱스가 거의 불필요하다.(FTS, Full Table Scan 사용)
• B 트리는 관계형 데이터베이스의 주요 인덱스 구조이다.
• 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋다.
• 인덱스는 인덱스 구성 칼럼으로 항상 내림차순으로 정렬된다.
• 규칙 기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 한다.
• 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있다.

B-트리(B-tree)

데이터베이스와 파일 시스템에서 널리 사용되는 트리 자료구조의 일종으로, 이진트리를 확장해 하나의 노드가 가질 수 있는 자식 노드의 최대 숫자가 2보다 큰 트리구조이다.

Nested Loop Join

루프를 돌며 두 테이블의 로우를 연결하는 방식이다.

• 조인 칼럼에 적당한 인덱스가 있어 자연 조인이 효율적일 때 유용하다.
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식이다.
• 유니크 인덱스를 활용하여 수행 시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용하다.
• 선택도가 낮은(결과 행의 수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리하다.

Sort Merge Join

참조할 인덱스가 없는 두 테이블을 정렬하여, 순차적으로 연결하는 방식이다.

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

주로 스캔 방식으로 데이터를 읽는다.

• Driving Table의 개념이 중요하지 않은 조인 방식이다.
• 조인 조건의 인덱스의 유무에 영향을 받지 않는다.
• Non-EQUI Join 조건에서도 사용할 수 있다.

Hash Join

두 테이블이 너무 큰 경우, 해쉬 맵에 저장하여 연결하는 방식이다.

조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있다.

'='로 수행하는 동등 조인에서만 사용할 수 있다.

• 조인 칼럼에 적당한 인덱스가 없어서 자연 조인이 비효율적일 때 사용한다.
• 자연 조인 시 드라이빙 집합 쪽으로 조인 액세스 량이 많아 Random 액세스 부하가 심할 때 사용한다.
• 소트 머지 조인을 하기엔 두 테이블이 너무 커서 소트 부하가 심할 때 사용한다.
• 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 유리하다.