2-3. SQL 최적화 기본원리

1절. 옵티마이저와 실행계획

옵티마이저

SQL문에 대한 최적의 실행방법을 결정하여 실행 계획 도출

 

※ SQL문 실행 순서 ① 파싱(Parsing): SQL 문법 검사 및 구문 분석 작업 ② 실행(Execution): 옵티마이저의 실행 계획에 따라 ③ 인출(Fetch): 데이터를 읽어 전송

 

 

옵티마이저의 종류

• 규칙기반 옵티마이저: 우선순위 규칙에 따라 실행계획 생성, 인덱스가 있으면 반드시 인덱스 사용
• 비용기반 옵티마이저: 처리 비용이 가장 적은 실행계획 선택, 데이터 딕셔너리(Data Dictionary)의 통계정보나 DBMS의 차이로 같은 쿼리도 다른 실행계획이 생성될 수 있음, 실행계획의 예측 및 제어가 어려움

 

SQL 처리 흐름도

SQL문의 처리절차를 시각적으로 표현한 도표

 

 

옵티마이저 실행계획

SQL에서 요구한 사항을 처리하기위한 절차와 방법

 

 

---

 

2절. 인덱스 기분

인덱스

검색 조건에 부합하는 데이터를 효과적으로 검색할 수 있도록 돕는 기능, 인덱스키로 정렬되어 있어 조회 속도가 빠름, DML 작업 효율은 저하함

 

 

트리기반 인덱스

DBMS에서 사용하는 가장 일반적인 인덱스, 루트 블록(Root Block)/브랜치 블록(Branch Block)/리프 블록(Leaf Block)로 구성

인덱스 스캔 효율화: 랜덤 액세스 최소화 (인덱스 스캔 후 추가 정보를 가져오기 위한 랜덤 액세스는 DBMS 성능 부하를 유발함)

※ 인덱스 칼럼의 순서는 랜덤 액세스와 무관함

 

 

스캔 방법

• 전체 테이블 스캔(Full Table Scan): 테이블의 모든 데이터를 읽으며 데이터 추출, 읽은 블록의 재사용성을 낮다고 판단하여 메모리 버퍼에서 제거
• 인덱스 스캔(Index Scan): 인덱스를 구성하는 칼럼의 값을 기반으로 데이터 추출, 인덱스를 읽어 ROWID를 찾고 해당 데이터를 찾기 위해 테이블을 읽음. 랜덤 액세스에 의한 부하 발생, 중복 스캔 비효율
  • 인덱스 범위 스캔(Index Range Scan): 특정 범위에 인덱스 스캔 적용
  • 인덱스 유일 스캔(Index Unique Scan): 인덱스키가 중복되지 않을 때 단 한 건의 데이터 추출, 등호 조건으로 조회함, 검색 속도가 가장 빠름
  • 인덱스 전체 스캔(Index Full Scan): 리프 블록을 모두 읽으며 데이터 추출

 

 

---

3절. 조인 수행 원리

NL 조인(Nested Loop Join)

선행 테이블의 데이터 하나씩 순차적으로 조인 (중첩 반복문과 유사함), 선행 테이블 처리 범위가 성능을 결정함 (~ 해시 조인, ↔ 소트 머지 조인은 순서에 무관함), 랜덤 액세스 위주이므로 대용량 데이터 처리 시 불리, 유니크 인덱스를 이용하여 소량 테이블 조인할 때 유리

선행 테이블/외부 테이블(Outer Table) : 반복문의 외부에 있는 테이블 후행 테이블/내부 테이블(Inner Table) : 반복문의 내부에 있는 테이블

• 절차 ① 선행 테이블에서 조건을 만족하는 행을 찾음 ② 후행 테이블에 선행 테이블의 조인키가 존재하는지 확인함 ③ 후행 테이블 인덱스에 선행 테이블의 조인키가 존재하는지 확인함 ④ 인덱스에서 추출한 ROWID로 후행 테이블을 액세스함

 

 

소트 머지 조인(Sort Merge Join)

두 테이블을 개별적으로 스캔한 후 조인 (↔ NL 조인은 선행 테이블을 랜덤 액세스 방식으로 조회하며 조인), 대용량 데이터 처리 시 디스크에서 정렬이 진행되므로 성능상 불리, 인덱스 유무가 성능에 큰 영향을 주지 않음 (↔ NL 조인은 인덱스 구성에 크게 영향을 받음)

 

 

해시 조인(Hash Join)

조인 칼럼을 기준으로 동일한 해시 값을 갖는 데이터의 실제 값을 비교하며 조인, 두 테이블의 데이터 차이가 클 때 유리, NL 조인의 랜덤 액세스 & 소트 머지 조인의 정렬 작업 부담 해결, 등가 조인에서만 사용할 수 있음, 해시 메모리에서 해시 테이블을 생성하므로 선행 테이블이 작을 때 유리