[SQLD 요약] II. SQL 기본 및 활용 - 03. SQL 최적화의 원리 (1) - 옵티마이저 & 인덱스

 

 

SQLD 요약 / SQL Developer 요약

🔑 옵티마이저/ SQL 실행계획/ 힌트/ 규칙기반 옵티마이저/ 비용기반 옵티마이저/ 인덱스/ 인덱스 스캔

 

II. SQL 기본 및 활용

   03. SQL 최적화의 원리

          [1] 옵티마이저와 실행계획

          [2] 옵티마이저 종류

          [3] 인덱스 (Index)

 

 

---

---

 

03. SQL 최적화의 원리

 

[1] 옵티마이저와 실행계획

(1) 옵티마이저 (Optimizer) | SQL 실행계획 수립 & 실행

• 옵티마이저는 데이터베이스 관리 시스템(DBMS)의 소프트웨어
• 개발자가 SQL 작성하면 → 옵티마이저가 실행계획 수립하고 SQL 실행
• 결과는 동일해도, 어떻게 실행하느냐에 따라서 성능 달라짐 → 실행계획은 SQL 성능에 있어서 중요!
• 옵티마이저는 PLAN_TABLE에 실행계획을 저장함 → 개발자가 조회하여 확인 가능 (DESC PLAN_TABLE;)
  
  
• 힌트(HINT): 개발자가 옵티마이저에게 실행방법을 알려주는 것
  
  
• 옵티마이저의 특징

옵티마이저의 계획	옵티마이저의 필요성
오브젝트 통계, 시스템 통계 등의 정보를 사용 → 예상 비용 산정	개발자가 작성한 SQL문을 효율적으로 실행하기 위해 계획 수립
여러 실행계획 중에서, 최저비용의 실행계획을 선택함	개발자는 힌트를 사용하여 실행계획 변경을 요청할 수 있음

 

---

 

 

(2) 옵티마이저 실행 | Oracle 기준 SQL 최적화 과정

• 옵티마이저 실행/구동 단계

Optimizer

 

① SQL 실행	② 파싱 (Parsing)	③ 실행계획 수립	④ 실행계획 저장	⑤ SQL 실행 및 인출
개발자가 SQL문을 작성하고 실행함	SQL의 문법검사 및 구문분석 수행	옵티마이저가 SQL 실행계획 수립	실행계획 수립 및 선정 완료 후 저장함	SQL 실행 및 데이터 인출 (Fetch)

 

 

• 옵티마이저 엔진/ 서브엔진별 역할

Query Transformer	Estimator	Plan Generator
- SQL문을 효율적으로 실행하기 위함 - 좀 더 일반적이고 표준적인 형태로 변환 - 변환되어도 결과는 동일함	- 최적의 실행계획을 위해 총비용을 계산 - 통계정보를 사용하여 SQL 실행비용 계산 - 각 단계의 선택도, 카디널리티, 비용 계산	- SQL을 실행할 실행계획 수립 - 하나의 쿼리를 수행하는 데에   후보군이 될만한 실행계획들을 생성

 

---

 

 

(3) 옵티마이저 종류 | 규칙기반 or 비용기반으로 실행계획 수립

• 최신 버전 Oracle은 비용기반 옵티마이저를 기본으로 사용함

규칙기반 옵티마이저 (Rule)	비용기반 옵티마이저 (Cost)
15개 우선순위를 기준으로 실행계획을 수립함	총비용: SQL문 실행에 예상되는 소요시간 or 자원사용량
SELECT문에서 힌트로 실행 가능 SELECT /*+ RULE */ * FROM TABLE	통계정보를 사용하여 총비용을 계산함 → 최저비용 계획수립 통계정보가 부적절한 경우라면 성능 저하 발생할 수 있음

 

 

---

 

 

[2] 인덱스 (INDEX)

(1) 인덱스 | 데이터를 빠르게 검색할 수 있게 해줌

• 인덱스 특징
  

인덱스 특징	인덱스 생성: "CREATE INDEX" 문
- 인덱스는 인덱스 키로 정렬되어 있음 (SORT)	- 1개 이상의 칼럼을 사용하여 생성
- 인덱스는 오름차순 or 내림차순 탐색 가능함	- 기본적으로 오름차순 정렬
- 하나의 테이블에 여러 개 인덱스 생성 가능	- 정렬: 오름차순(ASC), 내림차순(DESC)
- 하나의 인덱스는 여러 개의 칼럼으로 구성 가능	CREATE INDEX IND EMP ON                  EMP (ENAME ASC, SAL, DESC);
- 테이블 생성 시, 기본키는 자동으로 인덱스가 생성됨

 

 

• 인덱스 구조
  
  
• Leaf Block: 인덱스 키와 ROWID로 구성
  - Double Linked List 형태 → 양방향 탐색 가능
  - 인덱스 키를 읽으면 → ROWID를 사용하여 → TABLE의 행을 직접 읽을 수 있음

트리 인덱스 구조	노드 종류	설명
	Root Block	- 가장 상위 노드
	Branch Block	- 분기를 목적으로 함 - 다음단계 주소를 가진 포인터
	Leaf Block	- 가장 하위 노드 - 구성: 인덱스 키, ROWID - 인덱스 키: 인덱스를 구성하는 칼럼 - ROWID: 데이터 행의 물리적 주소

 

---

 

 

(2) 인덱스 스캔 (INDEX SCAN)

• 인덱스를 사용하여 테이블 검색 (TABLE SCAN)

트리 인덱스 검색 과정	과정
	① 찾으려는 값보다 Branch Block 가장 왼쪽 값이        작거나 같으면 왼쪽 포인터로 이동
	② 찾으려는 값이 Brach Block의 값 사이에 존재하면       가운데 포인터로 이동
	③ 찾으려는 값이 Brach Block 가장 오른쪽 값보다       크면 오른쪽 포인터로 이동
	Leaf Block으로 오면, Double Linked List 형태이므로 양방향 탐색 및 이동이 가능함

 

 

• 인덱스 스캔 종류: 유일, 범위, 전체

인덱스 유일 스캔 (Index Unique SCAN)	인덱스 범위 스캔 (Index Range SCAN)	인덱스 전체 스캔 (Index Full SCAN)
- 인덱스 키 값이 중복되지 않는 경우	- 인덱스 Leaf Block의 특정 범위를 스캔	- 검색되는 인덱스 키가 많은 경우
- 특정한 하나의 행을 조회함	- 범위 조회 WHERE문, LIKE, BETWEEN	- Leaf Block의 처음~끝까지 전체를 읽음
SELECT * FROM EMP WHERE EMPNO = 1001;	SELECT EMPNO FROM EMP WHERE EMPNO >= 1001;	SELECT ENAME, SAL FROM EMP WHERE ENAME Like '%' AND SAL > 0;

 

---

 

참고도서: SQL 개발자 이론서+기출문제_이기적, 2020

참고링크 1: wiki.gurubee.net/pages/viewpage.action?pageId=26744587

참고링크 2: dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=364