SQL Engineering & Query Optimization

1. Overview

SQL 엔지니어링과 쿼리 최적화(SQL Engineering & Query Optimization)는 단순한 데이터 조회를 넘어, 관계형 대수의 선언적 언어(Declarative Language)인 SQL을 하드웨어(CPU, 디스크 I/O)가 효율적으로 처리할 수 있도록 제어하고 튜닝하는 소프트웨어 공학 영역입니다.

학습자는 SELECT, JOIN, GROUP BY의 문법 이면에 있는 논리적 실행 순서(Logical Execution Order)를 살펴봅니다. 나아가 B-Tree 기반 **인덱스(Index)**의 스캔 방식(Index Seek vs Index Scan)과 인덱스 타기(SARGability)의 제약 조건을 정리합니다. 마지막으로 데이터베이스의 **옵티마이저(Optimizer)**가 비용(Cost) 기반으로 실행 계획(Execution Plan)을 세우는 방식과, Nested Loop Join, Hash Join, Sort Merge Join의 물리적 동작 원리까지 익혀 대용량 트래픽 서버의 쿼리 병목을 줄이는 엔지니어링 역량을 확보합니다.

2. Scope & Boundaries

In-Scope

• SQL 핵심 기전 (SQL Mechanics): 선언적 패러다임, 논리적 실행 순서(FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ SELECT $\rightarrow$ ORDER BY), 서브쿼리, 윈도우 함수(Window Functions).
• 인덱스 구조와 스캔 (Index Architecture): B-Tree / B+Tree, Clustered Index vs Non-Clustered Index, Index Seek, Full Table Scan, 커버링 인덱스(Covering Index).
• 물리적 조인 알고리즘 (Physical Joins): Nested Loop Join(NL Join), Hash Join, Sort Merge Join.
• 옵티마이저와 실행 계획 (Optimizer & Execution Plan): 비용 기반 옵티마이저(CBO), 힌트(Hints), EXPLAIN / EXPLAIN ANALYZE 리포트 해석.

Out-of-Scope

• 특정 벤더 종속 기능: Oracle PL/SQL, SQL Server T-SQL의 프로시저 작성 등 RDBMS 벤더 특화 스크립트 언어 → Database Administration 영역.
• RDBMS 스토리지 엔진 내부: InnoDB의 Page 분할, MVCC 로우 레벨 상세 구현 → 06-01-04 RDBMS Implementation 영역으로 위임.

Boundaries

• 인덱스의 딜레마 (Read vs Write): 인덱스를 추가하면 SELECT 쿼리(읽기)는 빨라지지만, INSERT, UPDATE, DELETE(쓰기) 시 B-Tree를 재정렬하고 Page Split(페이지 분할)을 처리해야 하므로 쓰기 성능이 저하됩니다. 또한 인덱스 자체가 큰 메모리(디스크) 공간을 차지합니다. 무조건적인 인덱스 생성은 쓰기 병목을 유발하므로 쿼리 빈도와 수정 빈도를 저울질하는 트레이드오프 결정이 필요합니다.

3. Counterexample

• Non-SARGable 쿼리로 인한 인덱스 미사용 (SARGability Failure): SELECT * FROM users WHERE YEAR(created_at) = 2023. created_at 컬럼에 B-Tree 인덱스가 걸려 있어도, 함수(YEAR())로 컬럼을 가공해버리면 인덱스 트리를 탐색할 수 없습니다(Non-SARGable). 옵티마이저는 결국 테이블의 모든 데이터 100만 건을 꺼내 함수를 실행해보는 Full Table Scan으로 처리합니다. WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01' 형태로 컬럼을 순수하게 유지해야 Index Seek를 탈 수 있습니다.
• 의도치 않은 Nested Loop Join 과부하 (Cartesian Product Disaster): 대용량 A 테이블(10만 건)과 B 테이블(10만 건)을 조인하는데 인덱스가 없거나 조인 조건이 모호한 경우. 옵티마이저가 Nested Loop Join을 선택하면, A의 1건마다 B의 10만 건을 풀스캔하며 탐색($10만 \times 10만 = 100억$ 번 비교). 서버 CPU가 100%로 치솟고 해당 쿼리가 30분 넘게 끝나지 않아 서비스가 멈출 수 있습니다. 쿼리 실행 계획(Explain)을 확인하여 인덱스를 추가하거나 Hash Join으로 유도해야 합니다.

4. Prerequisites

• 관계형 모델 (Basic): 테이블, 외래키, 정규화 등 관계형 DB 기초. (06-01-01 Relational Modeling)
• 자료구조 B-Tree (Recommended): 인덱스 탐색의 근간이 되는 트리 구조. (04-02-01 Binary Trees & AVL)

5. Learning Map

6. Learning Topics

Basic

Core Topic 01: 선언적 언어의 이면, SQL 논리적 실행 순서 (SQL Logical Execution)

• Why to Learn: SELECT count as c FROM table GROUP BY ... HAVING c > 10 같은 쿼리가 왜 문법 에러를 내는지, SQL 문법의 '작성 순서'가 아닌 '실행 순서'를 이해해 쿼리 작성의 혼선을 줄이기 위함입니다.
• What to Learn:
  • Concepts: 선언적 언어(Declarative vs Imperative), 논리적 실행 순서(FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ SELECT $\rightarrow$ ORDER BY $\rightarrow$ LIMIT).
  • Skills: Alias(별칭) 사용 시점의 한계 이해, 서브쿼리로 실행 순서 제어.
• How to Learn:
  • 1단계: 실행 순서 분해: RDBMS는 어떤 데이터를 가져올지(FROM, JOIN) 먼저 결정하고, 필터링(WHERE)한 뒤, 묶고(GROUP BY), 그제서야 최종 출력할 컬럼(SELECT)을 계산합니다. 따라서 SELECT에서 만든 별칭을 앞 단계인 WHERE나 GROUP BY에서 쓸 수 없는 실행 흐름을 살펴봅니다.
  • 2단계: WHERE vs HAVING: WHERE는 데이터 집계(그룹화) 이전에 원본 레코드를 버리고, HAVING은 그룹화와 집계 함수(SUM, COUNT)가 끝난 후의 결과 그룹을 버립니다. 이 둘을 구별하여 데이터 처리량(I/O 부하)을 줄이는 쿼리 작성법을 살펴봅니다.
• Implement: 파이썬 리스트/딕셔너리 기반 SQL 논리 엔진 시뮬레이터 구현. execute_query(data, from_tbl, where_cond, group_by, having_cond, select_cols) 함수 작성. 내부에서 순서대로 filter(), groupby(), map() 함수를 연쇄 호출하여, RDBMS가 데이터를 조작하는 정확한 순서적 파이프라인 콘솔 데모 시연.

Recommended

Core Topic 02: 디스크 I/O를 줄이는 B-Tree 인덱스와 커버링 (B-Tree Indexes & SARG)

• Why to Learn: 데이터가 백만 건을 넘어갈 때 서버가 느려지는 99%의 원인은 인덱스 설계 오류입니다. 디스크 액세스를 최소화하는 인덱스의 원리와 SARGable 제약 조건을 이해하기 위함입니다.
• What to Learn:
  • Concepts: B+Tree 자료구조, Clustered Index(테이블 데이터 자체 정렬) vs Non-Clustered Index(별도 포인터 트리), Index Seek(트리 탐색) vs Index Scan(리프 노드 풀스캔), SARGability (Search ARgument ABLE).
  • Skills: 복합 인덱스(Composite Index) 순서 전략, 커버링 인덱스 최적화.
• How to Learn:
  • 1단계: B+Tree 인덱스: WHERE name = 'Alice'. 루트 노드에서 키 범위를 비교하며 리프 노드까지 $O(\log N)$ 깊이로 디스크 페이지를 타고 내려가, 원본 데이터 주소를 얻는 물리적 탐색 방식을 살펴봅니다.
  • 2단계: 커버링 인덱스와 복합 인덱스: SELECT id, name FROM users WHERE age = 20. 인덱스를 (age, name, id) 3가지로 묶어서 만들면, 인덱스 리프 노드만 읽고도 쿼리가 요구하는 모든 데이터를 얻을 수 있습니다. 원본 테이블 디스크로 점프(Random I/O)하는 과정 자체를 생략(Covering)하여 속도를 높이는 기법을 살펴봅니다.
• Implement: B-Tree 스캔 횟수 계산 시뮬레이터. 100만 건의 데이터가 100개 단위의 Page(노드)에 담겨있다고 가정. 인덱스가 없을 때(Full Scan: 10,000 페이지 읽기)와 트리 깊이가 3인 인덱스를 탈 때(Index Seek: 3~4 페이지 읽기) 발생하는 디스크 I/O 비용 차이를 수치 및 막대그래프로 로깅하는 스크립트 작성.

Practical

Core Topic 03: 데이터를 엮는 3가지 물리적 조인 알고리즘 (Physical Join Algorithms)

• Why to Learn: SQL JOIN 하나가 내부적으로 CPU와 메모리를 다르게 소모하는 세 가지 물리적 알고리즘으로 나뉨을 이해하고, 비효율적인 조인을 튜닝하기 위함입니다.
• What to Learn:
  • Concepts: Nested Loop Join(NL 조인, 이중 for 루프), Block NL Join, Hash Join(메모리 해시맵 구축 후 프로빙), Sort Merge Join(정렬 후 병합).
  • Skills: 조인 건수와 인덱스 유무에 따른 알고리즘 선택 트레이드오프.
• How to Learn:
  • 1단계: NL Join의 비용: 2중 루프. 드라이빙 테이블(A)에서 1건 읽고 1건마다 드리븐 테이블(B)에 접속해 탐색. B에 인덱스가 없다면 A가 1만 건, B가 1만 건일 때 1억 번 탐색. 소규모 데이터나 B에 적절한 인덱스가 있을 때만 빠른 방식을 살펴봅니다.
  • 2단계: Hash Join의 대용량 처리: B에 인덱스가 없을 때 대용량 A와 B를 조인하는 법. A 데이터를 메모리에 올려 해시 테이블(Hash Map)을 만듭니다. 그 다음 B를 쭉 읽으면서 해시 맵에 조회합니다(Probe). 메모리를 대량으로 쓰지만(Memory Grant), CPU 탐색 비용을 줄이는 최신 데이터 분석(OLAP) 조인의 핵심을 살펴봅니다.
• Implement: 파이썬 NestedLoopJoin 함수와 HashJoin 함수 구현. $O(N \times M)$ 복잡도의 이중 for 루프와, $O(N+M)$ 복잡도의 해시맵 구축+탐색 함수 작성. A(1만 건), B(1만 건) 리스트 생성 후 각각 조인 함수 실행 시간(Time) 비교. Hash Join이 빠른 이유를 증명하고, 단 메모리 초과(Spill to disk)의 위험성을 주석으로 덧붙임.

Advanced

Core Topic 04: 옵티마이저와 실행 계획 읽기 (Optimizer & Execution Plan)

• Why to Learn: 쿼리 튜닝의 핵심은 RDBMS가 수천 가지 쿼리 실행 경로 중 비용(Cost)을 계산하여 적절한 경로를 선택하는 방식에 있습니다. 통계 기반 옵티마이저의 메커니즘과 EXPLAIN 리포트를 해석하기 위해서입니다.
• What to Learn:
  • Concepts: 파서(Parser), 비용 기반 옵티마이저(CBO: Cost-Based Optimizer), 딕셔너리 통계(Statistics: 카디널리티, 데이터 분포), 실행 계획(Execution Plan).
  • Skills: EXPLAIN (PostgreSQL/MySQL) 읽기 (Cost, Rows, Node Types), 옵티마이저 힌트(Hint) 적용.
• How to Learn:
  • 1단계: 비용(Cost)의 추정: 옵티마이저는 테이블의 '인덱스 유무', '컬럼 데이터의 중복도(Cardinality)', '데이터 건수' 통계를 기반으로 경로의 예상 CPU/I/O 비용을 계산합니다. 통계가 오래되어(업데이트 누락) 옵티마이저가 잘못된 판단(인덱스 대신 풀스캔)을 내리는 장애 원리를 살펴봅니다.
  • 2단계: EXPLAIN 분석: -> Nested Loop (cost=100.5 rows=10) $\rightarrow$ -> Index Scan using idx_name. 트리 구조 하단(안쪽)부터 실행되며 위로 데이터를 올려보내는 실행 계획 리포트를 해석하여, 쿼리 내 어느 부분(서브쿼리, 조인 순서)에서 대규모 비용이 발생했는지 추적하는 법을 살펴봅니다.
• Implement: Postgres/MySQL 엔진 EXPLAIN 결과 JSON 파일 파싱 툴 작성. 복잡한 쿼리 플랜 JSON 구조체를 재귀적으로 순회하여, Node Type이 Seq Scan (풀스캔)이면서 Rows가 10만 건 이상인 병목 노드만 찾아내 붉은색 텍스트로 경고(Alert)를 출력하는 쿼리 플랜 린터(Linter) 시뮬레이터 데모.

7. Terminology

8. References

Primary

• [P1] CS2023 - Information Management (IM) - Query Processing and Optimization
• [P5] SFIA - Database Design (DBDS) - Query Optimization

Secondary

• [Database Management Systems] Raghu Ramakrishnan - Relational Operators & Query Optimization
• [SQL Performance Explained] Markus Winand - Indexing & SARGability

Industry

• [MySQL 8.0 Reference Manual] - Query Execution Plans (EXPLAIN) & Join Algorithms
• [PostgreSQL Documentation] - Indexes and Cost-Based Optimizer

9. Final Checklist

Primary

• SQL 쿼리의 논리적 실행 순서(FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ SELECT)를 바탕으로 쿼리를 디버깅할 수 있는가?
• EXPLAIN 또는 EXPLAIN ANALYZE 리포트에서 Full Table Scan과 Index Seek 노드를 식별할 수 있는가?

Secondary

• 복합 인덱스(Composite Index) 생성 시 컬럼 순서가 B-Tree 탐색 범위(SARGability)에 미치는 영향을 증명할 수 있는가?
• Nested Loop Join이 대용량 데이터에서 $O(N \times M)$ 비용 증가를 일으키는 원리를 설명할 수 있는가?

Industry

• Hash Join이 메모리를 추가 소모하면서도 인덱스 없는 대규모 조인 성능을 개선하는 물리적 이유를 논증할 수 있는가?
• 데이터 빈도(Cardinality) 통계가 틀어졌을 때 옵티마이저가 잘못된 실행 계획을 세우는 장애 상황을 추적하고 튜닝할 수 있는가?