-
3장 SQL의 조건 분기DB & SQL/SQL 레벨업 2023. 8. 7. 10:09
8. UNION을 사용한 쓸데없이 긴 표현
- SQL 에서 조건 분기를 할 때 UNION을 사용해도 좋을지 여부는 신중히 검토해야 한다.
1. UNION을 사용한 조건 분기와 관련된 간단한 예제
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;
- 문제점
ㆍ1. 쓸데없이 길다.
ㆍ2. 성능이 좋지 않다.
- UNION 쿼리는 Items 테이블에 2회 접근하고 있다.
ㆍ이 두번도 TABLE ACCESS FULL 이 발생하므로, 읽어들이는 비용도 테이블에 크기에 따라 선형으로 증가
- 정확한 판단 없이 SELECT 구문 전체를 여러 번 사용해서 코드를 길게 만드는 것은 쓸데없이 테이블 접근을 발생시키며 SQL의 성능을 나쁘게 만든다.
ㆍ또한 물리 자원(저장소의 I/O 비용)도 쓸데없이 소비하게 된다.
2. WHERE 구에서 조건 분기를 하는 사람은 초보자
- SQL 격언
ㆍ"조건 분기를 WHERE 구로 하는 사람들은 초보자다. 잘 하는 사람은 SELECT 구만으로 조건 분기를 한다."
SELECT item_name, year, CASE WHEN year <= 2001 THEN price_tax_ex WHEN year >= 2002 THEN price_tax_in END AS price FROM Items;
3. SELECT 구를 사용한 조건 분기의 실행 계획
- 위의 쿼리를 보면 Items 테이블에 대한 접근이 1회로 줄어들었다.
- SQL 구문의 성능이 좋은지 나쁜지는 반드시 실행 계획 레벨에서 판단해야 한다.
- UNION을 사용한 분기는 SELECT '구문'을 기본 단위로 분기하고 있다.
ㆍ구문을 기본 단위로 사용하고 있다는 점에서, 아직 절차 지향형의 발상을 벗어나지 못한 방법이라고 말할 수 있다.
- 반면 CASE식을 사용한 분기는 문자 그대로 '식'을 바탕으로 하는 사고이다.
- 이렇게 '구문'에서 '식'으로 사고를 변경하는 것이 SQL을 마스터하는 열쇠 중 하나이다.
9. 집계와 조건 분기
1. 집계 대상으로 조건 분기
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 prefecturn, NULL AS pop_men, pop AS pop_wom FROM Population WHERE sex = '2' ) TMP GROUP BY prefecture;
- 서브쿼리 TMP는 남성과 여성의 인구가 별도의 레코드가 나온다.
- 이러한 쿼리의 가장 큰 문제는 WHERE 구에서 sex 필드로 분기를 하고, 결과를 UNION 으로 머지한다는 절차 지향적인 구성에 있다.
UNION 의 실행 계획
- 실행 계획을 보면 Population 테이블에 풀 스캔이 2회 수행되는 것을 확인할 수 있다.
집계의 조건 분기도 CASE 식을 사용
- 이 문제는 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;
CASE 식의 실행계획
- 중요한 것은 외관이 간단해질 뿐만 아니라 성능도 향상된다는 것이다.
- Population 테이블로의 풀 스캔이 1회로 감소
ㆍ따라서 UNION을 사용한 경우의 2회에 비해 I/O 비용이 절반으로 감소
- CASE 식은 SQL에서 굉장히 중요한 도구이다.
ㆍ다양한 표현을 할 수 있는 것은 물론 성능적으로 큰 힘을 발휘한다.
2. 집약 결과로 조건 분기
UNION을 사용한 조건 분기
SELECT emp_name, MAX(team) AS team FROM Employees GROUP BY emp_name HAVING COUNT(*) = 1 UNION SELECT emp_name, '2개를 겸무' AS team FROM Employees GROUP BY emp_name HAVING COUNT(*) = 2 UNION SELECT emp_name, '3개 이상을 겸무' AS team FROM Employees GROUP BY emp_name HAVING COUNT(*) >= 3;
UNION의 실행 계획
- 3개의 쿼리를 머지하는 쿼리이므로 예상대로 Employees 테이블에 대한 접근도 3번 발생
CASE 식을 사용한 조건 분기
SELECT emp_name, CASE WHEN COUNT(*) = 1 THEN MAX(team) WHEN COUNT(*) = 2 THEN '2개를 겸무' WHEN COUNT(*) >= 3 THEN '3개 이상을 겸무' END AS team FROM Employees GROUP BY emp_name;
CASE 식을 사용한 조건 분기의 실행계획
- 이렇게 CASE 식을 사용하면 테이블에 접근 비용을 3분의 1로 줄일 수 있다.
- 추가적으로 GROUP BY의 HASH 연산도 3회에서 1회로 줄어들었다.
- 앞에서 'WHERE 구에서 조건 분기를 하는 사람은 초보자' 라고 말했는데, 마찬자기로 'HAVING 구에서 조건 분기를 하는 사람도 초보자' 다.
10. 그래도 UNION이 필요한 경우
- 지금까지 UNION을 사용하는 것이 나쁘다는 식으로 소개했다.
- 하지만 UNION을 사용하지 않으면 안되는 경우도 있다.
ㆍ또한 UNION을 사용하는 것이 오히려 성능적으로 좋은 경우도 있다.
1. UNION을 사용할 수 밖에 없는 경우
- 여러 개의 테이블에서 검색한 결과를 머지하는 경우
SELECT col_1 FROM Table_A WHERE col_2 = 'A' UNION ALL SELECT col_3 FROM Table_B WHERE col_4 = 'B';
- FROM 구에서 테이블을 결합하면 CASE식을 사용해 원하는 결과를 구할 수 있다.
ㆍ하지만 그렇게 하면 필요 없는 결합이 발생해서 성능적으로 악영향이 발생한다. ( UNION 을 사용한다면 발생하지 않는다. )
2. UNION을 사용하는 것이 성능적으로 더 좋은 이유
- UNION을 사용하는 편이 더 성능이 좋을 수 있는 경우는 인덱스와 관련된 경우이다.
- UNION을 사용했을 때 좋은 인덱스(압축을 잘 하는 인덱스)를 사용하지만, 이외의 경우에는 테이블 풀 스캔이 발생하면, UNION을 사용한 방법이 성능적으로 좋을 수 있다.
UNION을 사용한 방법
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';
- 머지되는 3개의 SELECT 구문에서 다른 것은 WHERE 구 뿐이다.
- 이때 포인트가 인덱스이다.
ㆍ이 쿼리를 최적의 성능으로 수행하려면 다음과 같은 필드 조합에 인덱스가 필요하다.
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);
- 이러한 인덱스가 WHERE 구에서 (date_n, flg_n) 라는 필드 조합을 사용할 때 빠르게 만들어 준다고 생각하자.
OR을 사용한 방법
- 이러한 문제를 UNION을 사용하지 않고 푼다면 어떻게 풀까?
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') OR (date_2 = '2013-11-01' AND flg_2 = 'T') OR (date_3 = '2013-11-01' AND flg_3 = 'T');
- SELECT 구문이 하나로 줄어들었기 때문에 ThreeElements 테이블에 대한 접근이 1회로 줄었다.
ㆍ하지만 이때 인덱스가 사용되지 않고, 그냥 테이블 풀 스캔이 수행된다.
ㆍ이렇게 WHERE 구문에서 OR을 사용하면 해당 필드에 부여된 인덱스를 사용할 수 없다.
- 따라서 이러한 경우에 UNION과 OR의 성능 비교는 결국 3회의 인덱스 스캔 VS 1회의 테이블 풀 스캔 중에서 어떤 것이 더 빠른지에 대한 문제가 된다.
ㆍ이는 테이블 크기와 검색 조건에 따른 선택 비율(레코드 히트율)에 따라 답이 달라진다.
ㆍ하지만 테이블이 크고, WHERE 조건으로 선택되는 레코드의 수가 충분히 작다면 UNION이 더 빠르다.
ㆍ따라서 UNION을 사용하는 경우가 더 빠를 수 도 있다.
IN을 사용한 방법
SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3 FROM ThreeElements WHERE ('2013-11-01', 'T') IN ((date_1, flg_1), (date_2, flg_2), (date_3, flg_3));
- 이는 다중 필드(multiple fields, 또는 행식(row expression)) 라는 기능을 사용한 방법이다.
- IN의 매개변수로는 단순한 스칼라뿐만 아니라, 이렇게 (a, b, c)와 같은 값의 리스트(배열)을 입력할 수도 있다.
- OR을 사용했을 때보다 간단하고 이해하기 쉬울 수 있지만 실행 계획은 OR을 사용할 때와 같다.
ㆍ따라서 성능적인 문제도 같다.
CASE 식을 사용한 방법
SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3 FROM ThreeElements WHERE CASE WHEN date_1 = '2013-11-01' THEN flg_1 WHEN date_2 = '2013-11-01' THEN flg_2 WHEN date_3 = '2013-11-01' THEN flg_3 ELSE NULL END = 'T';
- 실행 계획은 OR, IN을 사용할 때와 같다.
ㆍ따라서 성능적으로 같은 문제를 안게 된다.
- 이러한 쿼리를 사용할 때는 주의점이 있다.
ㆍ비즈니스 룰을 조금 변경하면 UNION, OR, IN을 사용할 때와 다른 결과가 나온다.
11. 절차 지형형과 선언형
- 예외적인 몇 가지 상황을 제외하면 UNION을 사용하지 않는 것이 성능적으로도 좋고 가독성도 좋다는 것이다.
- 원래 UNION이 조건 분기를 위해 만들어진 것이 아니므로 당연한 결과이다.
- 반대로 CASE 식은 조건 분기를 위해 만들어졌으므로, CASE식을 사용하는 것이 훨씬 자연스러운 것이다.
1. 구문 기반과 식 기반
- SQL 초보자는 절차 지향적인 세계에서 살고 있다.
ㆍ이는 대부분 처음 배우는 프로그래밍 언어가 절차 지향형 프로그래밍 언어이기 때문이다.
ㆍ그 세계에서 생각의 기본 단위는 '구문(statement)' 이다.
- 하지만 SQL 중급자 이상은 선언적인 세계이다.
ㆍ여기서의 기본 단위는 '식(expression)'이다.
- SQL의 기본적인 체계는 선언형이다.
-이 세계의 주역은 '구문'이 아니라 '식'이다.
- 절차 지향형 언어가 CASE 구문으로 분기하는 것을, SQL은 CASE식으로 분기한다.
2. 선언형의 세계로 도약
마치며..
- SQL의 성능은 저장소의 I/O를 얼마나 감소시킬지 있을지가 열쇠
- UNION에서 조건 분기를 표현한다면 "내가 쓸데없이 길게 쓰고 있는 것은 아닐까?" 라는 것을 항상 의식할 것
- IN 또는 CASE 식으로 조건 분기를 표현할 수 있다면, 테이블에의 스캔을 크게 감소시킬 가능성이 있음
- 이를 위해서라도, 구문에서 식으로의 패러다임 전환을 연습
'DB & SQL > SQL 레벨업' 카테고리의 다른 글
6장 결합 (0) 2023.08.08 5장 반복문 (0) 2023.08.07 4장 집약과 자르기 (0) 2023.08.07 2장 SQL 기초 (0) 2023.08.04 1장 DBMS 아키텍처 (0) 2023.08.04