📚 지식 노트

SQL이 실행되기까지

SQL은 결과의 모양만 말하는 선언형 언어다(관계형 모델 노트). 그래서 데이터베이스 안에서는 문장 하나가 여러 단계를 거쳐 "어떻게 실행할지"를 담은 실행 계획(plan)이 된다.

1. 파싱: 문법을 검사하고 구문 트리를 만든다.
2. 분석과 재작성: 테이블·열 이름이 실제로 있는지, 자료형이 맞는지 확인하고 뷰를 그 정의로 펼친다.
3. 최적화: 같은 답을 내는 여러 계획(테이블 읽는 순서, 인덱스 사용 여부, 조인 방법)의 비용을 통계로 어림해 가장 싼 것을 고른다.
4. 실행: 계획 트리의 각 노드가 "다음 행을 달라"는 요청에 답하며 행을 하나씩 위로 넘긴다. 이 방식을 반복자 모델(iterator, Volcano 모델)이라 한다. 분석용 엔진은 행 대신 수천 행 묶음을 넘겨(벡터화) 함수 호출 비용을 줄인다.

인덱스의 종류

인덱스는 테이블과 따로 둔 정렬된(또는 해시된) 사본이다. 찾기는 빨라지지만 행을 넣고 고칠 때마다 인덱스도 함께 고쳐야 하고 공간도 차지한다. 그래서 "자주 쓰는 질의의 조건"에 맞춰 골라 만든다.

• B+ 트리: 기본값. 같음(=)과 범위(<, BETWEEN), 정렬(ORDER BY), 앞부분 일치(LIKE 'abc%')에 쓴다. 구조는 저장 엔진 노트에 있다.
• 해시: 같음 비교만 한다. 범위나 정렬에는 못 쓴다. 원리는 해싱 노트에 있다.
• 커버링 인덱스: 질의가 쓰는 열이 모두 인덱스에 있으면 테이블을 읽지 않고 인덱스만으로 답한다. PostgreSQL은 이를 Index-Only Scans라 부르고, INCLUDE로 검색에 안 쓰는 열을 잎에만 얹을 수 있다.
• 그 밖: 전문 검색용 역색인(GIN), 공간 데이터용 GiST, 아주 큰 표의 물리 순서를 이용한 BRIN 등이 있다. PostgreSQL 문서의 Index Types가 목록을 준다.

여러 열을 묶은 복합 인덱스(composite index)는 첫 열로 정렬하고, 첫 열이 같은 것끼리 둘째 열로 정렬한다. 그래서 왼쪽 열부터 조건이 걸려야 연속된 구간으로 좁혀진다. 이를 왼쪽 앞부분 규칙(leftmost prefix)이라 한다.

범위 조건 뒤의 열은 좁히는 데 쓰이지 않는다. (도시, 나이, 이름) 인덱스에서 도시 = '서울' AND 나이 > 30 AND 이름 = '김하나'는 도시와 나이로 구간을 잡고 이름은 그 구간을 훑으며 거른다. 그래서 같음 조건 열을 앞에, 범위 조건 열을 뒤에 두는 것이 보통 유리하다. Markus Winand의 Use The Index, Luke의 Concatenated Keys와 PostgreSQL 문서 Multicolumn Indexes가 같은 규칙을 설명한다.

선택도와 비용 기반 옵티마이저

인덱스가 있다고 늘 쓰는 것은 아니다. 인덱스로 찾은 행마다 테이블 페이지를 따로 읽어야 하므로, 고르는 행이 많으면 차라리 테이블을 처음부터 끝까지 이어 읽는 편이 싸다. 조건에 맞는 행의 비율을 선택도(selectivity)라 하고, 옵티마이저는 이것을 어림해 계획을 고른다.

비용은 페이지 읽기와 CPU 처리의 가중합이다. PostgreSQL의 기본값은 순차 페이지 읽기 1, 무작위 페이지 읽기 4, 행 하나 처리0.01, 조건 연산 하나 0.0025다(Query Planning 설정). 문서의 예인 345페이지·1만 행 테이블을 모두 훑는 비용은 345 × 1.0 + 10000 × 0.01 = 445이고, WHERE 조건 하나를 붙이면 행마다 연산 비용이 더해져 470가 된다.

어림의 바탕은 통계다. PostgreSQL은 ANALYZE가 표본을 읽어 열마다 빈 값 비율, 서로 다른 값의 수, 가장 흔한 값 목록(MCV)과 그 빈도, 나머지 값의 히스토그램을 모아 둔다(Statistics Used by the Planner). 나이 < 30의 선택도는 히스토그램에서 30 아래 칸의 비율로, 도시 = '서울'은 MCV 목록의 빈도로 어림한다.

비용 기반 최적화는 IBM System R의 1979년 논문 「Access Path Selection in a Relational Database Management System」(Selinger 등)이 틀을 잡았다. 조인 순서를 동적 계획법으로 고르고, 정렬된 결과처럼 뒤에서 쓸모 있는 성질("흥미로운 순서")을 가진 부분 계획을 함께 남기는 방식은 지금 옵티마이저에도 그대로 남아 있다. 약점도 그대로다. 열끼리 상관이 있으면(도시 = '서울'과 지역번호 = '02'처럼) 독립이라 가정한 곱셈이 행 수를 크게 틀리고, 조인이 겹칠수록 오차가 곱해진다. 계획이 갑자기 나빠지면 통계가 오래됐거나 상관을 놓친 경우가 많다.

조인 알고리즘

두 테이블 R(바깥)과 S(안쪽)를 잇는 방법은 크게 셋이다. 디스크 기반 비용은 페이지 I/O 수로 센다. 아래 숫자는 Carnegie Mellon 대학 데이터베이스 강의의 조인 강의 노트와 같은 예(R 1000페이지·10만 행, S 500페이지·4만 행)로 계산했다.

• 중첩 루프(nested loop): R의 행마다 S를 훑는다. 그대로 하면 50,001,000번, 페이지 단위로 묶어도 501,000번이다. 버퍼 100장을 R 묶음에 쓰면 6,500번까지 준다. S의 조인 열에 인덱스가 있으면 R의 행마다 인덱스를 찾는 인덱스 중첩 루프가 되어, R이 작을 때 가장 빠르다.
• 정렬-병합(sort-merge): 두 쪽을 조인 열로 정렬한 뒤 지퍼처럼 맞물려 훑는다. 정렬에 6,000번, 병합에 1,500번으로 7,500번. 이미 정렬된 입력(인덱스 순서)이면 정렬 비용이 빠진다.
• 해시 조인(hash join): 작은 쪽으로 해시 테이블을 만들고 큰 쪽으로 찾는다. 메모리에 다 안 들어가면 두 쪽을 같은 해시로 조각내 디스크에 썼다가 조각끼리 조인한다(Grace 해시 조인). 3 × (1000 + 500) = 4,500번. 같음 조건에만 쓴다.

옵티마이저는 테이블 크기, 조인 조건 종류, 정렬된 입력이 있는지, 메모리(PostgreSQL의 work_mem)를 보고 고른다. 결과가 몇 행뿐인 OLTP 질의는 인덱스 중첩 루프, 큰 테이블끼리의 분석 질의는 해시 조인이 흔히 뽑힌다.

EXPLAIN 읽기

옵티마이저가 무엇을 골랐는지는 EXPLAIN으로 본다. 출력은 계획 트리를 들여쓰기로 보여 주고, 가장 안쪽 노드부터 실행되어 바깥으로 행을 올린다. 아래는 PostgreSQL 문서 Using EXPLAIN의 예다.

• 노드 이름이 접근 방법이다. Seq Scan은 전체 훑기, Index Scan은 인덱스 순서로 행 읽기, Index Only Scan은 테이블을 안 읽는 커버링, Bitmap Heap Scan은 그 중간이다. 조인은 Nested Loop·Hash Join·Merge Join으로 나온다.
• EXPLAIN ANALYZE는 질의를 실제로 실행해 노드마다 실제 시간, 실제 행 수, 반복 횟수(loops)를 함께 적는다. 어림 행 수와 실제 행 수가 열 배 넘게 다른 노드가 보통 문제의 출발점이다. 쓰기 질의에 쓰면 실제로 고쳐지므로 트랜잭션 안에서 실행하고 되돌린다.
• BUFFERS 옵션을 더하면 노드마다 버퍼 풀에서 찾은 페이지(hit)와 디스크에서 읽은 페이지(read) 수가 나온다(EXPLAIN 명령).

흔한 함정

인덱스가 있는데도 안 쓰이거나, 질의 하나하나는 빠른데 화면이 느린 경우는 대개 몇 가지 유형으로 나뉜다.

• 인덱스 열을 함수로 감싸기: WHERE lower(email) = 'a@b.c'나 WHERE date(created_at) = '2026-10-30'는 인덱스에 든 값이 아니라 함수 결과를 비교하므로 일반 인덱스를 못 쓴다. 범위로 바꾸거나(created_at >= '2026-10-30' AND created_at < '2026-10-31') 같은 식으로 표현식 인덱스를 만든다. Use The Index, Luke의 Obfuscated Conditions 장이 이런 경우를 모았다.
• 형 변환: 문자열 열을 숫자와 비교하면 열 쪽이 변환되어 함수로 감싼 것과 같아질 수 있다. 상수의 형을 열에 맞춘다.
• 앞이 열린 LIKE: LIKE '%abc'는 정렬 순서를 쓸 수 없다. 전문 검색 인덱스나 트라이그램 인덱스가 필요하다.
• 큰 OFFSET: OFFSET 100000 LIMIT 20은 앞의 10만 행을 읽고 버린다. 마지막으로 본 키 다음부터 읽는 키 기반 페이지 나누기(WHERE id > ? ORDER BY id LIMIT 20)가 일정한 속도를 낸다.
• N+1 질의: 목록을 한 번 읽고 항목마다 딸린 데이터를 다시 묻는 패턴이다. ORM이 연관 객체를 늦게 불러올 때 흔히 생긴다. 질의 하나는 1ms여도 100개면 100ms다.