친절한 SQL 튜닝 - 1일차

1.1 SQL 파싱과 최적화

SQL이란?

• SQL은 'Structured Query Language'의 줄임말이다.

오라클 PL / SQL, SQL Server T-SQL처럼 절차적 프로그래밍 기능을 구현할 수 있는
확장 언어도 제공하지만, SQL은 기본적으로 구조적이고 집합적이고 선언적인 질의 언어다.

원하는 결과 집합을 구조적,집합적으로 선언하지만, 그 결과집합을 만드는 과정은 절차적일 수 밖에 없다.

즉, 프로시저가 필요한데, 프로시저를 만들어 내는 DBMS 내부 엔진을 SQL 옵티마이저 라고 한다.

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

1.1.2 SQL 최적화

SQL을 실행하기 전 최적화 과정

1. SQL 파싱
   사용자로 부터 SQL을 전달받으면 가장 먼저 SQL 파서(Parser)가 파싱을 진행한다.

• 파싱 트리 생성
• Syntax 체크
• Semantic 체크

2. SQL 최적화
   그다음 단계가 SQL 최적화이다. 옵티마이저가 그 역할을 맡는다.
   SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한후 가장 효율적인 하나를 선택한다.
   최적화 과정을 포함해 'SQL 파싱'이라고 표현하기도 하고, 파싱 과정을 포함해 'SQL 최적화'라고 표현한다.'

3. 로우 소스 생성
   SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계이다. 로우 소스 생성기가 그 역할을 맡는다.

1.1.3 SQL 옵티마이저

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 엑세스 경로를 선택해주는 DBMS의 핵심 엔진이다.

옵티마이저의 최적화 단계

• 1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 수집
• 2. 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용 산정
• 3. 최저 비용을 나타내는 실행계획을 선택

1.1.4 실행계획과 비용

DBMS에도 'SQL 실행경로 미리보기' 기능이 있다.
실행계획을 사용하면 SQL 옵티마이저가 생성한 처리 절차를 사용자가 확인할 수 있게 트리구조로 표현되어 있다.

미리보기 기능을 사용하면 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.

비용(Cost) 이란?

쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는
예상 소요시간을 표현한 값

SQL 실행계획에 표시되는 Cost도 어디까지나 예상치다.
실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 난다.

1.1.5 옵티마이저 힌트

개발자가 직접 더 효율적인 엑세스 경로를 찾아내서 데이터 엑세스 경로를 바꿀 수 있게 해주는 것을 옵티마이저 힌트라고 한다.

옵티마이저 힌트 사용법은 주석 기호에 '+'를 붙이면 된다.

EX)

> SELECT /*+ INDEX(A 고객_PK) */
	FROM 고객 A
    WHERE 고객 ID = '000000008'

힌트 사용시 주의 사항

1. 힌트안에 인자를 나열할 땐, ','(콤마)를 사용할 수 있지만, 힌트와 힌트 사이에는 사용하면 안된다.

/*+ INDEX(A A_X01) INDEX(B, B_X03) */ -> 모두 유효
/*+ INDEX(C), FULL(D) */   		      -> 첫 번째 힌트만 유효

2. 테이블을 지정할 때 아래와 같이 스키마명까지 명시하면 안된다.

SELECT /*+ FULL(SCOTT.EMP) */ -> 무효
	FROM EMP

3. FROM 절 테이블명 옆에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 한다.

SELECT /*+ FULL(EMP) */ -> 무효
	FROM EMP E

출처 : 친절한 SQL 튜닝(디비안 : 조시형 저)