[SQL] SQL 최적화의 원리

옵티마이저(Optimizer)

• SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어
• 동일한 결과가 나오는 SQL도 어떻게 실행하는냐에 따라 성능이 달라짐

옵티마이저 특징

• 데이터 사전(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해 예상되는 비용
• 여러 개의 실행 계획 중 최저비용을 갖고 있는 계획을 선택해서 SQL 실행

 

옵티마이저의 필요성

출처) https://starrykss.tistory.com/1622

건수가 많은 EMP테이블을 실행하고 찾은 행을 DEPT테이블에서 찾으면 불필요한 비교 횟수가 증가하게 된다. DEPT테이블을 머저 읽고, EMP테이블을 읽게되면 비교횟수를 줄일 수 있다. 옵티마이저는 이러한 실행 계획을 수립하는 것이며, 비효율적으로 실행 계획을 수립하면 옵티마이저에게 실행 계획을 변경하도록 요청할 수 있는데, 이 때 힌트(HINT) 를 사용한다.

 

옵티마이저 실행계획

• 옵티마이저는 SQL 실행 계획을 PLAN_TABLE 에 저장

DESC PLAN_TABLE;

• 실행 계획을 통해 알 수 있는 정보 : 액세스 기법, 질의처리 예상 비용, 조인 순서, 최적화 정보, 조인 기법, 연산 ➜ 예상정보
• 실행 계획을 읽는 순서는 위에서 아래로, 안에서 밖으로 읽는다
• 인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환 X

 

옵티마이저의 실행방법

1. 개발자가 SQL을 실행하면 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문 분석을 수행

2. 구문 분석이 완료되면, 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획 수립

• 기본적으로 비용 기반 옵티마이저 사용해서 통계정보를 활용한 실행 계획 수립

3. 실행 계획 수립이 완료되면 최종적으로 SQL 실행하고, 완료되면 데이터를 인출(Fetch)

 

옵티마이저 엔진

옵티마이저	설명
Query Transformer	SQL문을 효율적으로 실행하기 위해 옵티마이저가 변환 SQL이 변환되어도 결과 동일
Estimator	통계 정보를 사용해서 SQL 실행 비용 계산 총비용은 최적의 실행 계획을 수립하기 위해 구함
Plan Generator	SQL을 실행할 실행 계획 수립

 

SQL 처리 흐름도

SQL 처리 흐름도(Access Flow Diagram)는 SQL 실행계획을 시각화 해서 표현

• 인덱스 스캔, 테이블 전체 스캔 등과 같은 액세스 기법 표현, 성능적인 측면도 표현
• SQL의 내부적인 처리절차를 시각적 표현

💡 실행 시간을 알 수는 없다.

 

옵티마이저 종류

규칙 기반 옵티마이저(Rule Base Optimizer)

• 15개의 우선순위를 기준으로 실행 계획 수립
• 제일 높은 우선순위는 행에 대한 고유 주소 ROWID를 활용하여 테이블 엑세스
• 제일 낮은 우선순위는 전체 테이블 스캔

💡 최신 Oracle 버전은 규칙 기반 옵티마이저 보다 비용 기반 옵티마이저를 기본적으로 사용

비용 기반 옵티마이저(Cost Base Optimize)

• 오브젝트 통계 및 시스템 통계를 사용해서 총 비용 계산
• 총 비용 : SQL문을 실행하기 위해 예상되는 소요시간 혹은 자원의 사용량
• 총 비용이 적은 쪽으로 실행계획 수립
• 통계정보가 부적절한 경우, 성능 저하가 발생할 수 있음

 

인덱스(Index)

[SQL] 인덱스 (INDEX)

• 인덱스 키로 정렬되어 있기 때문에, 원하는 데이터를 빠르게 조회 가능
• 오름차순 및 내림차순 탐색이 가능
• 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고, 하나의 인덱스는 여러 개의 칼럼으로 구성
• 테이블을 생성할 때 기본키는 자동으로 인덱스가 만들어진다
• 기본 인덱스는 UNIQUE & NOT NULL 의 제약조건을 가진다
• 보조 인덱스는 UNIQUE 인덱스가 아니라면 중복 데이터 입력이 가능
• 자주 변경되는 속성은 인덱스 후보로 적절하지 않다
• 테이블 전체 데이터를 읽는 경우는 인덱스를 사용하지 않는 FTS 사용
• 인덱스는 조회의 속도를 향상시키며, 삽입, 삭제, 갱신의 경우 오히려 부하 가중
• 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 삽입이 끝난 후 인덱스를 다시 생성
• 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식이 랜덤 엑세스인데, 많은 양의 데이터를 읽는 경우에는 인덱스 스캔보다 테이블 전체 스캔이 유리할 수도 있다.
• 인덱스를 구성하는 컬럼 이외의 데이터가 UPDATE 될 때는 인덱스로 인한 부하가 발생하지 않는다.

인덱스의 구조

• Root Block : 인덱스 트리에서 최상위 노드
• Branch Block : 다음 단계의 주소를 가지고 있는 포인터(Pointer)
• Leaf Block : 인덱스 키와 ROWID로 구성되고, 인덱스 키는 정렬되어 저장, 양방향 연결리스트 형태

인덱스 구조

인덱스의 종류

B-Tree 인덱스

• 브랜치 블록과 리프 블록으로 구성
• 브랜치 블록은 분기를 목적, 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬
• OLTP 시스템 환경에서 가장 많이 사용
• 일치 및 범위 검색에 적절한 구조
• 테이블 내의 데이터 중 10% 이하의 데이터를 검색할 때 유리
• B 트리는 관계형 데이터베이스의 주요 인덱스 구조 (가장 많이 사용)

BITMAP 인덱스

• 시스템에서 사용된 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계
• 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조

CLUSTERED 인덱스

• 인덱스의 리프 페이지가 곧 데이터 페이지
• 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장
• SQL Server의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사

 

옵티마이저 조인

Nested Loop 조인

• 하나의 테이블에서 데이터를 먼저 찾고 그 다음 테이블을 조인하는 방식으로 실행
• 랜덤 엑세스 방식으로 데이터 조회
• 조인 칼럼에 적당한 인덱스가 있어 자연조인이 효율적일 때 유용
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식
• UNIQUE 인덱스를 활용하여 수행시간이 적게 걸리는 소량 케이블을 온라인 조회하는 경우 유용
• OLTP의 목록 처리 업무에 많이 사용 ➜ 먼저 나오는 테이블의 선택도가 낮은 테이블을 참조하는 것이 유리

💡 드라이빙 테이블(DRIVING TABLE) : 조인 시 먼저 엑세스 되어서 ACCESS PATH를 주도하는 테이블
💡 드리븐 테이블(DRIVEN TABLE) : 나중에 엑세스 되는 테이블

 

Sort Merge 조인

• 조인 칼럼을 기준으로 데이터를 정렬하여 조인 수행
• 스캔방식으로 데이터 조회
• 랜덤 엑세스로 NL조인(Nested Loop)에서 부담이 되던 넓은 범위 데이터를 처리할 때 이용
• 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역(디스크)을 사용하기 때문에 성능이 떨어지게 된다.
• Driving Table의 개념이 중요하지 않은 조인 방식
• 조인 조건의 인덱스 유무에 영향 받지 않음
• 집계 업무에서 많이 사용

 

Hash 조인

• 2개의 테이블 중 작은 테이블을 HASH메모리에 로딩하고, 테이블의 조인 키를 사용해 해시테이블을 생성
• 해시 함수를 사용해서 주소 계산 후 해당 주소를 사용해 테이블을 조인하기 때문에 CPU 연산을 많이 함
• 조인 칼럼의 인덱스 유무에 영향 받지 않음
• EQUI = 조인 조건에서만 사용 가능
• 집계 업무에서 많이 사용
• 행의 수가 작은 테이블을 선행 테이블로 선택하는 것이 유리
• Hash조인은 Sort Merge조인 보다 일반적으로 더 우수한 성능을 보이지만, 조인 대상 테이블이 조인 키 컬럼으로 정렬되어 있을 때는 Sort Merge조인 이 더 우수한 성능을 낼 수도 있다

Hash 조인이 효과적일 수 있는 조건

• 조인 컬럼에 적당한 인덱스가 없어 자연조인(Natural join)이 비효율적일 때
• 자연조인 시 드라이빙 집합 쪽으로 조인 액세스량이 많아 Random액세스 부하가 심할 때
• SMJ(Sort Merge join)을 하기에 두 테이블이 너무 커서 부하가 심할 때
• 한 쪽 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복값이 적을 때

💡 EXISTS 절은 실행계획에 주로 SEMI JOIN으로 나타난다.