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

들어가며

작년 10월 이후로 회사에서 프로젝트가 본격적으로 시작되면서 벨로그에 글을 거의 남기지 못했다.
프로젝트 진행하면서 벨로그에 가끔씩 들어와 트러블슈팅 조각들을 남겨두긴 했지만 글을 발행할 정도로 정리가 된 글들을 아닌 것 같아 임시글로만 잔뜩 쌓여 있다.

그러던 중 2024년이 되고... 정신없이 프로젝트가 진행되는 와중에 부족함을 많이 느껴서 자연스럽게 몇 가지 공부 목표들을 세워가는 중이다. 첫째, 프론트엔드 이해를 쌓기 위해 리액트 공부 + 개인 프로젝트 진행하기, 둘째, 데이터베이스 OR 인프라 공부하기. 셋째, 컴퓨터 구조와 운영체제 공부하기.

특히 두 번째 목표의 경우 데이터베이스 쪽으로는 SQLP를, 인프라 쪽으로는 AWS 자격증을 염두에 두고 조금씩 시작해보려고 한다. 먼저 SQLP 준비 겸 '친절한 SQL'이라는 책이 평이 좋길래 틈틈히 읽으면서 벨로그에 정리를 할 예정이다.

1.1 SQL 파싱과 최적화

1.1.1 구조적, 집합적, 선언적 질의 언어

• SQL: Structured Query Language → '구조적 질의 언어'의 줄임말
  ⇒ 구조적(SQL의 원래 이름인 SEQUEL(Structured English Query Language)에서 온 말. SQL 문법이 영어와 구조적으로 같다는 의미.

• SQL is a set-based, declarative query language, not an imperative language such as C or BASIC (= SQL은 C나 BASIC과 같은 명령형 언어가 아닌 집합적, 선언적인 질의 언어다.) (출처: 위키피디아)
  ⇒ 원하는 결과를 데이터베이스에 질의(Query)하면 결과 집합(set-based)으로 주기 때문.
  ⇒ SQL은 어떻게 수행할지(= imperative, 명령형 프로그래밍)가 아니라 어떤 것이 필요한지(= declarative, 선언적으로) 정의하기 때문.

• 하지만, 원하는 결과집합을 만드는 과정은 절차적일 수밖에 없는데 (= 프로시저가 필요한데) 이를 DBMS 내부 엔진인 옵티마이저가 대신 프로그래밍해줌.

1.1.2 SQL 최적화 과정

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

  [SQL 최적화 과정]

  1. SQL 파싱 (by. SQL 파서)

  • 파싱 트리 생성: SQL 문의 개별 구성요소를 분석해서 트리 구조 생성
  • Syntax 체크: 문법적 오류가 없는지 확인
  • Semantic 체크: 의미상 오류가 없는지 확인 (존재하지 않는 테이블, 컬럼 사용 여부, 권한 여부 확인)
    
    

  2. SQL 최적화 (by. 옵티마이저)

  • 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택함.
    
    

  3. 로우 소스 생성 (by. 로우 소스 생성기)

  • 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅.

1.1.3 SQL 옵티마이저

• 쿼리를 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS 핵심 엔진

  [옵티마이저의 최적화 단계]

  1. 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아냄
  2. 데이터 딕셔너리에 수집해둔 오브젝트 통계 및 시스템 통계쩡보를 이용해 예상비용 산정
  3. 최저 비용의 실행계획 선택

• 데이터 딕셔너리: 테이블 및 뷰들의 집합으로 데이터베이스 전반에 대한 정보를 제공 (메타데이터, MySQL에서는 information_schema라는 데이터베이스에 존재하는 읽기 전용 데이터)

1.1.4 실행계획과 비용

# 실행계획 보는 명령어
EXPLAIN PLAN

# AutoTrace 활성화 명령어
set autotrace traceonly exp;

-- 오라클 기준

• 실행계획을 확인하면 인덱스 선택 여부와 선택한 인덱스, 그리고 비용 등을 확인할 수 있음.
• 실행계획에 표시되는 Cost는 예상치. 실제 수행 시 결과가 달라질 수 있음.

1.1.5 옵티마이저 힌트

• 옵티마이저 힌트: 개발자가 데이터 액세스 경로를 바꿀 수 있음.
• 힌트를 사용하고 싶다면 쿼리문에 /*+ 힌트 명령어 */ 와 같이 작성. (주석 시작 부분에 + 추가)
• 힌트 명령어에 따라 최적화 목표, 액세스 방식, 조인 순서, 조인 방식, 서브쿼리 팩토링, 쿼리 변환, 병렬 처리 등을 할 수 있음.