Portfolio

개념

SQL을 DB에 보내면 바로 테이블부터 읽는다고 생각하기 쉽다. 실제로는 그 전에 파싱 -> 검증 -> 플래닝 -> 실행 단계를 거친다. DB는 먼저 SQL 문장이 맞는지, 테이블과 컬럼이 실제로 존재하는지 확인하고, 그다음에야 어떤 방식으로 읽고 조인할지를 결정한다.

여기서 자주 헷갈리는 게 두 가지다. 하나는 SELECT ... FROM ... WHERE ... 같은 SQL 문법/논리 순서이고, 다른 하나는 DB 엔진이 내부에서 거치는 처리 단계다. EXPLAIN은 전자가 아니라 후자, 정확히는 실행 계획 쪽을 보여준다.

왜 필요한가

쿼리가 느릴 때 가장 많이 나오는 질문은 두 가지다.

왜 인덱스를 안 탔지
왜 이런 조인 방식을 골랐지

이 둘은 감으로 답하면 거의 틀린다. DB는 "인덱스가 있으니 무조건 쓰자" 식으로 움직이지 않는다. 조건으로 걸러지는 row 수, 데이터 분포, 조인 순서, 정렬 여부를 보고 가장 싸 보이는 계획을 고른다. EXPLAIN을 읽는다는 건 그 판단 근거를 읽는다는 뜻이다.

처리 흐름

예를 들어 이런 쿼리가 있다고 하자.

SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age = 25;

DB는 대략 이런 순서로 처리한다.

1. 파싱

SQL 문자열을 읽어서 문법 구조를 만든다. 괄호가 안 맞거나 키워드 위치가 틀리면 여기서 바로 에러가 난다.

2. 검증과 해석

users, orders 테이블이 실제로 있는지, u.age, o.user_id 컬럼이 존재하는지, 타입이 맞는지 확인한다. 이 단계가 끝나야 "이 쿼리가 무엇을 하려는지"를 DB가 정확히 안다.

3. 플래닝과 최적화

옵티마이저가 여러 후보 계획을 비교한다.

users를 Seq Scan할지 Index Scan할지
users를 먼저 읽을지 orders를 먼저 읽을지
조인을 Nested Loop, Hash Join, Merge Join 중 무엇으로 할지

이때 근거로 쓰는 게 통계 정보와 카디널리티 추정이다.

4. 실행

최종 선택된 계획대로 실제 테이블과 인덱스를 읽는다. EXPLAIN ANALYZE까지 붙이면 이 단계의 실제 실행 시간과 실제 row 수도 같이 볼 수 있다.

통계 정보와 카디널리티 추정

옵티마이저는 매 쿼리마다 데이터를 전부 세고 판단하지 않는다. 대신 미리 수집된 통계 정보를 보고 결과를 예측한다.

통계 정보

테이블과 컬럼에 대해 DB가 미리 알고 있는 분포 정보다.

테이블 전체 row 수
컬럼 값의 분포
값이 얼마나 치우쳐 있는지
NULL 비율
값의 종류가 얼마나 다양한지

카디널리티 추정

통계 정보를 바탕으로 이 조건을 걸면 결과가 몇 row쯤 나올지 예측하는 것이다.

예를 들어 users가 100만 row인데 age = 25 결과가 10건쯤 나올 것 같으면, 옵티마이저는 인덱스가 유리하다고 볼 수 있다. 반대로 age > 20처럼 대부분의 row가 걸릴 것 같으면, 인덱스를 따라 많이 점프하는 것보다 Seq Scan이 더 싸다고 볼 수 있다.

관계는 이 순서로 보면 된다.

통계 정보 -> 카디널리티 추정 -> 비용 계산 -> 실행 계획 선택

문제는 이 추정이 틀릴 수 있다는 점이다. 예상 row 수가 크게 빗나가면 스캔 방식과 조인 방식도 잘못 고를 수 있다.

스캔 방식

스캔 방식은 테이블이나 인덱스를 어떻게 읽을지를 뜻한다. EXPLAIN에서 가장 먼저 볼 항목이다.

Seq Scan

테이블을 처음부터 끝까지 순서대로 읽는다. 흔히 "풀스캔"이라고 부른다.

무조건 나쁜 계획은 아니다. 조건에 맞는 row가 많거나, 테이블 자체가 작거나, 인덱스를 타도 결국 대부분을 읽어야 하면 Seq Scan이 더 낫다.

Index Scan

인덱스를 이용해 필요한 row 위치를 찾고, 그 위치를 따라 실제 테이블 row를 읽는다. 조건으로 걸러지는 양이 적을수록 유리하다.

다만 이것도 무조건 빠른 건 아니다. 결과 row가 많아지면 인덱스를 따라가며 테이블을 반복 접근하는 비용이 커져서 Seq Scan보다 불리할 수 있다.

Index Only Scan

필요한 컬럼이 인덱스 안에 다 들어 있으면, 테이블까지 안 내려가고 인덱스만 읽어 해결하는 방식이다. 커버링-인덱스와-특수-인덱스에서 이어진다.

조인 방식

조인 방식은 읽은 두 입력을 어떻게 매칭할지를 뜻한다.

Nested Loop

가장 단순한 방식이다. 한쪽 row를 하나 꺼내고, 다른 쪽에서 매칭되는 row를 반복해서 찾는다.

작은 결과 집합에는 강하다. 특히 바깥쪽에서 row를 적게 뽑고, 안쪽 조인 키에 인덱스가 있으면 빠를 수 있다. 반대로 양쪽이 다 크면 반복 비용이 커져서 비효율적이다.

Hash Join

보통 한쪽 입력을 해시 테이블로 만든 뒤, 다른 쪽 입력을 읽으며 같은 키를 빠르게 찾는다. 그래서 A.id = B.user_id 같은 등가 조인에 강하다.

정렬은 필요 없지만, 해시 테이블을 만드는 비용과 메모리가 든다. <, >, BETWEEN 같은 범위 조인에는 잘 맞지 않는다.

Merge Join

양쪽 입력이 조인 키 기준으로 정렬되어 있을 때, 앞에서부터 비교하면서 맞는 row를 붙여 나간다. 정렬이 이미 되어 있거나, 인덱스 덕분에 정렬된 순서로 읽을 수 있을 때 유리하다.

핵심 전제는 정렬이다. 정렬이 안 되어 있으면 먼저 정렬 비용을 내야 해서 오히려 불리할 수 있다.

EXPLAIN은 어떻게 읽나

처음부터 모든 숫자를 보려고 하면 금방 막힌다. 최소한 아래 순서로 보면 된다.

각 테이블이 Seq Scan인지 Index Scan인지 본다
예상 row 수가 얼마나 되는지 본다
조인이 있으면 Nested Loop, Hash Join, Merge Join 중 무엇인지 본다
왜 그런 선택이 나왔는지 생각한다

질문을 이렇게 바꾸면 읽기가 쉬워진다.

이 테이블은 왜 풀스캔했지
이 조건은 왜 인덱스를 타지 않았지
왜 Hash Join이 아니라 Nested Loop가 나왔지
옵티마이저는 결과가 몇 건 나올 거라고 본 거지

즉 EXPLAIN은 "문법이 맞나"를 보는 도구가 아니라, DB가 이 쿼리를 어떤 그림으로 실행하려고 하는지를 보는 도구다.

언제 쓰나

다음 상황이면 거의 바로 EXPLAIN을 보는 게 맞다.

인덱스를 만들었는데 쿼리가 여전히 느릴 때
조인이 들어간 쿼리의 성능이 갑자기 나빠졌을 때
운영 데이터가 커지면서 예전에는 빠르던 쿼리가 느려졌을 때
조건 하나 바꿨는데 실행 시간이 크게 달라질 때

이때 중요한 건 "인덱스를 탔는지"만 보는 게 아니다. 예상 row 수가 현실과 크게 다른지, 그리고 그 오판 때문에 스캔이나 조인 전략이 틀어졌는지를 같이 봐야 한다.

쿼리 실행과 EXPLAIN

개념