SQL 레벨업 Ch.3 — SQL의 조건 분기
UNION으로 나누던 조건 분기를 CASE 식으로 바꿔 테이블 접근을 줄이는 방법과 UNION이 유리한 경우를 정리했다.
시리즈 · BackEnd 서적2 / 17
- SQL 레벨업 Ch.1 — DBMS 아키텍처
- SQL 레벨업 Ch.3 — SQL의 조건 분기
- SQL 레벨업 Ch.4~5 — 집약과 자르기, 반복문
- SQL 레벨업 Ch.6 — 결합
- SQL 레벨업 Ch.7 — 서브쿼리
- SQL 레벨업 Ch.8 — SQL의 순서
- SQL 레벨업 Ch.9 — 갱신과 데이터 모델
- SQL 레벨업 Ch.10 — 인덱스 사용
- 오브젝트 Ch.1 — 객체, 설계
- 오브젝트 Ch.2 — 객체지향 프로그래밍
- 오브젝트 Ch.3~4 — 역할, 책임, 협력 / 설계 품질과 트레이드오프
- 오브젝트 Ch.5 — 책임 할당하기
- 오브젝트 Ch.6 — 메시지와 인터페이스
- 오브젝트 Ch.8 — 의존성 관리하기
- 오브젝트 Ch.9 — 유연한 설계
- 오브젝트 Ch.10, 12 — 상속과 코드 재사용, 다형성
- 오브젝트 Ch.13 — 서브클래싱과 서브타이핑
Ch.2는 기본적인 SQL 문법을 설명하는 단원이라 읽기만 하고 정리는 건너뛰었다.
쓸데없는 UNION
UNION은 겉보기에는 하나의 SQL이지만 내부적으로는 여러 개의 SELECT 구문을 실행하는 실행 계획으로 해석된다. 그만큼 I/O 비용이 늘어난다.
SELECT item_name, year, price_tax_ex AS price
FROM Items
WHERE year <= 2001
UNION ALL
SELECT item_name, year, price_tax_in AS price
FROM Items
WHERE year >= 2002;거의 같은 쿼리를 두 번 쓰는 것도 문제지만, 실행 계획을 보면 Items 테이블에 2회 접근하고 TABLE ACCESS FULL(인덱스 없이 테이블 전체를 스캔)도 2번 발생한다. 읽는 비용은 테이블 크기에 비례해 늘어난다.
CASE 식으로 개선
SELECT item_name, year,
CASE WHEN year <= 2001 THEN price_tax_ex
WHEN year >= 2002 THEN price_tax_in END AS price
FROM Items;결과는 같지만 테이블 접근과 풀 스캔이 각각 1회로 줄어든다.
UNION의 기본 단위는 SELECT ’구문’이고, 이는 절차 지향의 발상에서 벗어나지 못한 방식이다. 반면 CASE의 기본 단위는 ’식’이다.
’구문’에서 ’식’으로 사고를 바꾸는 것이 SQL을 마스터하는 열쇠 중 하나다.
집계와 조건 분기
| prefecture(지역) | sex(성별) | pop(인구) |
|---|---|---|
| 성남 | 1 | 60 |
| 성남 | 2 | 40 |
| 수원 | 1 | 30 |
| 수원 | 2 | 40 |
| 광명 | 1 | 50 |
| 광명 | 2 | 60 |
| 일산 | 1 | 20 |
| 일산 | 2 | 15 |
성별 1은 남성, 2는 여성이다. 지역별 남녀 인구 합계를 한 행으로 뽑고 싶다.
UNION을 사용하면 다음과 같다.
SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_wom) AS pop_wom
FROM (SELECT prefecture, pop AS pop_men, NULL AS pop_wom
FROM Population
WHERE sex = '1'
UNION
SELECT prefecture, NULL AS pop_men, pop AS pop_wom
FROM Population
WHERE sex = '2') TMP
GROUP BY prefecture;남성 인구를 구하고, 여성 인구를 구한 뒤 합치는 절차 지향적인 방식이고 테이블 접근도 2회다. CASE 식을 집약 함수 안에 넣으면 한 번에 해결된다.
SELECT prefecture,
SUM(CASE WHEN sex = '1' THEN pop ELSE 0 END) AS pop_men,
SUM(CASE WHEN sex = '2' THEN pop ELSE 0 END) AS pop_wom
FROM Population
GROUP BY prefecture;쿼리가 간단해질 뿐 아니라 테이블 접근도 절반으로 줄어든다. 책의 표현을 빌리면, 조건 분기를 WHERE 구나 HAVING 구로 하는 사람은 초보자이고 잘하는 사람은 SELECT 구에서 한다.
UNION이 필요한 경우
대표적인 경우는 SELECT 구문마다 사용하는 테이블이 다를 때다. CASE 식으로도 쓸 수는 있지만 불필요한 결합이 생겨 오히려 성능이 나빠질 수 있으므로 실행 계획을 비교해 판단해야 한다.
인덱스를 탈 수 있는 경우
UNION으로 나누면 각 SELECT가 레코드를 잘 좁혀 주는 인덱스를 사용하고, 그렇지 않으면 테이블 풀 스캔이 발생하는 상황이라면 UNION이 더 빠를 수 있다.
| key | name | date_1 | flg_1 | date_2 | flg_2 | date_3 | flg_3 |
|---|---|---|---|---|---|---|---|
| 1 | a | 2013-11-01 | T | ||||
| 2 | b | 2013-11-01 | T | ||||
| 3 | c | 2013-11-01 | F | ||||
| 4 | d | 2013-12-30 | T | ||||
| 5 | e | 2013-11-01 | T | ||||
| 6 | f | 2013-12-01 | F |
날짜가 2013-11-01이고 플래그가 T인 레코드만 추출하고 싶다.
SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3
FROM ThreeElements
WHERE date_1 = '2013-11-01'
AND flg_1 = 'T'
UNION
SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3
FROM ThreeElements
WHERE date_2 = '2013-11-01'
AND flg_2 = 'T'
UNION
SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3
FROM ThreeElements
WHERE date_3 = '2013-11-01'
AND flg_3 = 'T';여기에 다음 인덱스가 있다면 세 SELECT 모두 인덱스 스캔으로 처리된다.
CREATE INDEX IDX_1 ON ThreeElements (date_1, flg_1);
CREATE INDEX IDX_2 ON ThreeElements (date_2, flg_2);
CREATE INDEX IDX_3 ON ThreeElements (date_3, flg_3);반면 OR, IN, CASE로 한 문장에 쓰면 테이블 풀 스캔 1회가 일어난다. 결국 인덱스 스캔 3회 vs 테이블 풀 스캔 1회의 비교이고, 답은 테이블 크기와 검색 조건의 선택률에 따라 달라진다. 테이블이 크고 WHERE 조건으로 선택되는 레코드가 충분히 적다면 UNION이 더 빠르다.
마치며
SQL을 무척 싫어했는데, 실무에서 데이터를 조회하며 JOIN을 비롯한 여러 쿼리를 직접 쓰다 보니 재미가 붙었다. 1장에서 배운 DBMS의 메모리 구조도 인상 깊어서 하루 종일 생각났다.