8강 UNION을 사용한 쓸데없이 긴 표현
- UNION을 사용한 조건 분기는 SQL 초보자가 좋아하는 기술 중 하나입니다.
하지만 이런 방법은 성능적인 측면에서 굉장히 큰 단점을 가지고 있습니다.
외부적으로는 하나의 SQL 구문을 실행하는 것처럼 보이지만,
내부적으로는 여러 개의 SELECT 구문을 실행하는 실행 계획으로 해석되기 때문입니다.
따라서 테이블에 접근하는 횟수가 많아져서 I/O 비용이 크게 늘어납니다.
-> 따라서 SQL에서 조건 분기를 할 때 UNION을 사용해도 좋을지 여부는 신중히 검토해야 합니다.
아무 생각 없이 무조건 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;이 코드는 굉장히 큰 문제점을 안고 있습니다
-> 첫 번째 문제는 쓸데없이 길다는 것입니다
거의 같은 두 개의 쿼리를 두 번이나 실행하고 있습니다.
이는 SQL을 쓸데없이 길고, 읽기 힘들게 만들 뿐입니다.
-> 두 번째 문제는 성능입니다.
- UNION을 사용한 쿼리의 성능 문제를 명확히 하기 위해 실행 계획을 살펴보겠습니다.
-> 어떤 것을 사용하더라도 UNION 쿼리는 Items 테이블에 2회 접근한다는 것을 알 수 있습니다.
-> 그리고 그때마다 TABLE ACCESS FULL이 발생하므로,
읽어들이는 비용도 테이블의 크기에 따라 선형으로 증가하게 됩니다.
2. WHERE 구에서 조건 분기를 하는 사람은 초보자
- SQL에는 이러한 격언들이 있습니다.
"조건 분기를 WHERE 구로 하는 사람은 초보자다.
잘하는 사람은 SELECT 구만으로 조건 분기를 한다."
-> 지금 살펴보고 있는 문제도 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;- 이 쿼리도 UNION을 사용한 쿼리와 같은 결과를 출력합니다.
-> 하지만 성능적으로 이번 쿼리가 훨씬 좋습니다.
3. SELECT 구를 사용한 조건 분기의 실행 계획
- CASE 식을 사용하면 Items 테이블에 대한 접근이 1회로 줄어듭니다.
-> 이전의 UNION을 사용한 구문보다 성능이 2배 좋아졌다고 할 수 있습니다.
또한 SQL 구문 자체의 가독성도 굉장히 좋아졌습니다.
- SQL 구문의 성능이 좋은지 나쁜지는 반드시 실행 계획 레벨에서 판단해야 합니다.
-> 이유는 SQL 구문에는 어떻게 데이터를 검색할지를 나타내는 접근 경로가 쓰여 있지 않기 때문입니다.
- UNION과 CASE의 쿼리를 구문적인 관점에서 비교할 수 있습니다.
->UNION을 사용한 분기는 SELECT '구문'을 기본 단위로 분기하고 있습니다.
구문을 기본 단위로 사용하고 있다는 점에서,
아직 절차 지향형의 발상을 벗어나지 못한 방법이라고 말할 수 있습니다.
- 반면 CASE 식을 사용한 분기는 문자 그대로 '식'을 바탕으로 하는 사고입니다.
이렇게 '구문'에서 '식'으로 사고를 변경하는 것이 SQL을 마스터하는 열쇠 중 하나입니다.
9강 집계와 조건 분기
1. 집계 대상으로 조건 분기
- 이 문제를 풀 때 절차 지향적인 사고 방식을 가진다면,
일단 남성의 인구를 지역별로 구하고,
여성의 인구를 지역별로 구한 뒤 머지(merge)하는 방법을 생각할 것입니다.
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;
- 하지만 이러한 쿼리의 가장 큰 문제는 WHERE 구에서 sex 필드로 분기를 하고,
결과를 UNION으로 머지한다는 절차지향적인 구성에 있습니다.
- 집계의 조건 분기도 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 식으로 조건 분기를 잘 사용하면 UNION을 사용하지 않을 수 있습니다.
그렇게 되면 테이블에 대한 접근을 줄일 수 있습니다.
CASE 식은 SQL에서 굉장히 중요한 도구입니다.
다양한 표현을 할 수 있는 것은 물론 성능적으로도 큰 힘을 발휘하기 때문입니다.
2. 집약 결과로 조건 분기
- 집약에 조건 분기를 적용하는 또 하나의 패턴으로,
집약 결과에 조건 분기를 수행하는 경우가 있습니다.
1) 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번 발생합니다.
2) 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 식을 사용하면 테이블에 접근 비용을 3분의 1로 줄일 수 있습니다.
추가적으로 GROUP BY의 HASH 연산도 3회에서 1회로 줄어들었습니다.
10강. 그래도 UNION이 필요한 경우
1) UNION을 사용할 수 밖에 없는 경우
- 머지 대상이 되는 SELECT 구문들에서 사용하는 테이블이 다른 경우가 대표적입니다.
SELECT col_1
FROM Table_A
WHERE col_2 = 'A'
UNION ALL
SELECT col_3
FROM Table_B
WHERE col_4 = 'B';- CASE 식을 사용할 수 없다는 것은 아니지만,
그렇게 하면 필요 없는 결합이 발생해서 성능적으로 악영향이 발생합니다.
2) UNION을 사용하는 것이 성능적으로 더 좋은 경우
- UNION을 사용했을 때 좋은 인덱스(압축을 잘 하는 인덱스)를 사용하지만,
이외의 경우에는 테이블 풀 스캔이 발생한다면,
UNION을 사용한 방법이 성능적으로 더 좋을 수 있습니다.
- 예를 들어, 3개의 날짜 필드 data_1~date_3과,
그것과 짝을 이루는 플래그 필드 flg_1~flg_3을 가진 테이블 ThreeElements를 생각해봅시다.
1) 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';- 이 때, 포인트가 인덱스입니다.
이 쿼리를 최적의 성능으로 수행하려면 다음과 같은 필드 조합에 인덱스가 필요합니다.
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);
2) OR을 사용한 방법
- OR를 사용해 조건을 연결할 수 있습니다.
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회로 줄어들었습니다.
하지만 이 때 인덱스가 사용되지 않고, 그냥 테이블 풀스캔이 수행됩니다.
- 따라서 이러한 경우에 UNION과 OR의 성능 비교는 결국
3회의 인덱스 스캔 VS 1회의 테이블 풀 스캔
중에서 어떤 것이 더 빠른지에 대한 문제가 됩니다.
-> 이는 테이블 크기와 검색 조건에 따른 선택 비율(레코드 히트율)에 따라
답이 달라집니다.
3) IN을 사용한 방법
- OR 쿼리를 IN 쿼리로 변환해서 다음과 같은 코드를 만들 수도 있습니다.
SELECT key, name
date_1, flg_1,
date_2, flg_2,
date_3, flg_3
FROM ThreeElements
WHERE ('2013-11-01', 'T')
INT ((date_1, flg_1),
(date_2, flg_2),
(date_3, flg_3));- OR을 사용했을 때보다 간단하고 이해하기 쉬울 수 있지만,
실행 계획은 OR을 사용할 때와 같습니다.
따라서 성능적인 문제도 같습니다.
4) 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을 사용할 때와 같습니다.
따라서 성능적으로 같은 문제를 안게 됩니다.
'DB' 카테고리의 다른 글
| SQL 레벨업 6장 결합 (0) | 2022.04.25 |
|---|---|
| SQL 레벨업 5장 반복문 (2) (0) | 2022.04.25 |
| SQL 레벨업 5장 반복문 (1) (0) | 2022.04.18 |
| SQL 레벨업 4장 집약과 자르기 (1) | 2022.04.18 |
| SQL 레벨업 1장 DBMS 아키텍쳐 (2) | 2022.04.04 |