[친절한 SQL 튜닝/1장] SQL 처리 과정과 I/O - SQL 파싱과 최적화

1. SQL 파싱과 최적화

💭 옵티마이저가 SQL을 어떻게 처리하는지 알아봅시다.

1) 구조적, 집합적, 선언적 질의 언어

• SQL: Structured Query Language, 구조적 질의 언어
  • 오라클 PL/SQL, SQL Server T-SQL처럼 절차적 프로그래밍 기능을 구현할 수 있는 확장 언어도 제공하지만, SQL은 기본적으로 구조적(structured), 집합적(set-based), 선언적(declarative) 질의 언어입니다.
  • 하지만 그 결과집합을 만드는 과정은 절차적입니다. 즉, 프로시저가 필요한데 그러한 프로시저를 만들어내는 DBMS 내부엔진이 바로 SQL 옵티마이저입니다.

 

2) SQL 최적화

ℹ️ DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정 == SQL 최적화 라고 합니다.

1. SQL 파싱
   : 사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서가 파싱을 진행합니다.
   (파싱 요약) 파싱 트리 생성 → Syntax 체크(문법 오류) → Semantic 체크(의미 오류)
   
   
2. SQL 최적화
   : 옵티마이저는 미리 수집한 시스템, 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성 → 비교 → 가장 효율적인 하나를 선택합니다. 데이터베이스 성능을 결정하는 가장 핵심적인 엔진입니다.
   
   
3. 로우소스 생성
   : 로우소스 생성기, SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅합니다.
   
   

3) SQL 옵티마이저

ℹ️ 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS의 핵심 엔진입니다.

 

옵티마이저 최적화 단계 요약

1. 쿼리 수행 후보군이 될만한 실행계획들을 찾아냅니다.
2. 데이터 딕셔너리에 미리 수집해 둔 오브넥트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정합니다.
3. 최저 비용을 나타내는 실행계획을 선택합니다.

 

 

4) 실행계획과 비용

• 실행계획(Execution Plan)
  • SQL 실행경로 미리보기 기능이라고 생각하면 쉽습니다.
  • SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한 것입니다.
• 옵티마이저가 특정 실행계획을 선택하는 근거
  
  • 통계정보를 수집하였을 때 T_X01 인덱스의 Cost는 2, T_X02 인덱스의 Cost는 19입니다.
  • 이때 옵티마이저가 T_X01 인덱스를 선택한다면, 그 선택 근거가 비용임을 알 수 있습니다.
  • 비용(Cost): 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값입니다.

 

5) 옵티마이저 힌트

• 통계정보에 담을 수 없는 데이터 또는 업무특성을 활용해 개발자가 직접 더 효율적인 엑세스 경로를 찾아낼 수 있습니다.
• 이때 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있습니다.
• 사용법: 주석 기호에 '+' 붙이기

-- 권장 방식
SELECT /*+ INDEX(A_고객_PK) */
		고객명, 연락처, 주소, 가입일시
	FROM 고객 A
    WHERE 고객ID = '000000008'
    
-- 아래 방식은 지양할 것
-- 사유: 코딩하는 과정에서 줄바꿈 오류가 발생할 수 있기 때문
SELECT --+ INDEX(A_고객_PK)
	   고객명, 연락처, 주소, 가입일시
	FROM 고객 A
    WHERE 고객ID = '000000008'

 

• 주의사항
  • 힌트 안에 인자를 나열할 때는 ','를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안 됩니다.
  • 테이블을 지정할 때 스키카명까지 명시하면 안 됩니다.
  • FROM절 테이블명 옆에 ALIAS를 지정했다면 힌트에도 반드시 ALIAS를 사용해야 합니다.

-- 인자 나열 시엔 콤마 가능, 힌트와 힌트 사이에는 불가능
/*+ INDEX(A A_X01) INDEX(B, B_X03) */ -- 모두 유효
/*+ INDEX(C), FULL(D) */ -- 첫번째 힌트만 유효

-- 스키마명 명시 불가
SELECT /*+ FULL(SCOTT.EMP) */ -- 무효
FROM EMP

-- FROM절 테이블명 옆에 ALIAS 지정했다면 힌트에도 반드시 ALIAS 사용할 것
SELECT /*+ FULL(EMP) */ -- 무효
FROM EMP E

 

• 옵티마이저에게 지시하는 방식은 다양하고, 더 옳은 방식은 애플리케이션 환경에 따라 다릅니다.
• 만약 옵티마이저의 작은 실수가 기업에 큰 손실을 끼칠 수 있는 시스템이라면 빈틈없이 기술하는 것을 추천합니다.
  • 주문 테이블을 액세스할 때 주문일자 컬럼이 선두인 인덱스를 사용하도록 힌트를 지정하는 방식
    (조인 방식과 순서, 고객 테이블 액세스 방식은 옵티마이저가 알아서 판단)
  • 옵티마이저가 절대 다른 방식을 선택하지 못하도록 힌트를 빈틈없이 지정하는 방식

 

• 자주 사용하는 힌트 목록

분류	힌트	설명
최적화 목표	ALL_ROWS	전체 처리속도 최적화
	FIRST_ROWS(N)	최초 N건 응답속도 최적화
액세스 방식	FULL	Table Full Scan 유도
	INDEX	Index Scan 유도
	INDEX_DESC	Index 역순 Scan 유도
	INDEX_FFS	Index Fast Full Scan 유도
	INDEX_SS	Index Skip Scan 유도
조인순서	ORDERED	FROM 절에 나열된 순서대로 조인
	LEADING	LEADING 힌트 괄호에 기술한 순서대로 조인 ex: LEADING(T1 T2)
	SWAP_JOIN_INPUTS	해시 조인 시 BUILD INPUT을 명시적으로 선택 ex: SWAP_JOIN_INPUTS(T1)
조인방식	USE_NL	NL 조인으로 유도
	USE_MERGE	소트 머지 조인으로 유도
	USE_HASH	해시 조인으로 유도
	NL_SJ	NL 세미조인으로 유도
	MERGE_SJ	소트 머지 세미조인으로 유도
	HASH_SJ	해시 세미조인으로 유도
서브쿼리 팩토링	MATERIALIZE	WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도 ex: WITH /*+ MATERIALIZE */ T AS (SELECT ... )
	INLINE	WITH 문으로 정의한 집합을 물리적으로 생성핟지 않고 INLINE 처리하도록 유도 ex: WITH /*+ INLINE */ T AS (SELECT ... )
쿼리 변환	MERGE	뷰 머징 유도
	NO_MERGE	뷰 머징 방지
	UNNEST	서브쿼리 Unnesting 유도
	NO_UNNEST	서브쿼리 Unnesting 방지
	PUSH_PRED	조인조건 Pushdown 유도
	NO_PUSH_PRED	조인조건 Pushdown 방지
	USE_CONCAT	OR 또는 IN-List 조건을 OR-Expansion으로 유도
	NO_EXPAND	OR 또는 IN-List 조건에 대한 OR-Expansion 방지
병렬 처리	PARALLEL	테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도 ex: PARALLEL(T1 2) PARALLEL(T2 2)
	PARALLEL_INDEX	인덱스 스캔을 병렬방식으로 처리하도록 유도
	PQ_DISTRIBUTE	병렬 수행 시 데이터 분배 방식 결정 ex: PQ_DISTRIBUTE(T1 HASH HASH)
기타	APPEND	Direct-Path Insert 유도
	DRIVING_SITE	DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정(Local 또는 Remote)
	PUSH_SUBQ	서브쿼리를 가급적 빨리 필터링하도록 유도
	NO_PUSH_SUBQ	서브쿼리를 가급적 늦게 필터링하도록 유도

 

---

• 참고자료
  • https://bysql.net/index.php?mid=w201101B&document_srl=12245 
  • https://www.yes24.com/Product/Goods/61254539

친절한 SQL 튜닝 - 예스24