[DB] - Deep To UNION, CASE (3장)

[Irisation23]

0. 들어가기에 앞서 🤔

UNION을 사용한 조건 분기는 WHERE 구만 조금씩 다른 여러 개의 SELECT 구문을 합쳐서 복수의 조건에 일치하는 하나의 결과 집합을 얻고 싶을 때 사용한다.
하지만 이는 성능적인 측면에서 굉장히 큰 단점을 가지고 있다.
외부적으로는 하나의 SQL 구문을 실행하는 것처럼 보이지만, 내부적으로는 여러 개의 SELECT 구문을 실행하는 실행 계획으로 해석되기 때문이다.
따라서 테이블에 접근하는 횟수가 많아져서 I/O 비용이 크게 늘어난다.

자 그럼 이제 UNION의 조건 분기는 좀 더 신중해야 함을 깨닫고, 언제 UNION을 사용하고 CASE를 사용할지 알아보자.

해당 3장에서 다뤄질 테이블의 구조는 아래와 같다.

• Items(상품)

item_id(상품 ID)	year(연도)	item_name(상품 이름)	price_tax_ex(세전 가격)	price_tax_in(세후 가격)
100	2000	머그컵	500	525
100	2001	머그컵	520	546
100	2002	머그컵	600	630
100	2003	머그컵	600	630
101	2000	티스푼	500	525
101	2001	티스푼	500	525
101	2002	티스푼	500	525
101	2003	티스푼	500	525

1. UNION을 사용한 조건 분기

위의 테이블에서 year 필드를 사용하여, 2001년 이전과 2002년 이후의 데이터를 구분해서 가격을 출력하는 쿼리를 만든다고 가정해 보자.

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;

다음의 결과는 아래와 같다.

              item_name              | year | price
-------------------------------------+------+-------
 머그컵                              | 2000 |   500
 머그컵                              | 2001 |   520
 티스푼                              | 2000 |   500
 티스푼                              | 2001 |   500
 나이프                              | 2000 |   600
 나이프                              | 2001 |   550
 머그컵                              | 2002 |   630
 머그컵                              | 2003 |   630
 티스푼                              | 2002 |   525
 티스푼                              | 2003 |   525
 나이프                              | 2002 |   577
 나이프                              | 2003 |   420
(12 rows)

조건이 배타적이므로 중복 레코드가 발생하지 않는다. 그리고 정렬이 필요하지 않아, UNION ALL을 사용한다.
하지만 해당 쿼리는 문제를 안고있다.

첫째, 쓸데 없이 긴 쿼리가 작성되어 있다. 그리고 거의 같은 쿼리를 두 번이나 실행하고 있다.
둘째, 성능에 문제가 있다.

1.1 UNION 을 사용했을 때의 실행 계획 문제

UNION을 사용한 쿼리의 성능 문제를 명확히 하기 위해 실행 계획을 살펴보면 PostgreSQL은 아래와 같은 실행 계획을 가지게 된다.

                               QUERY PLAN
------------------------------------------------------------------------
 Append  (cost=0.00..36.09 rows=306 width=140)
   ->  Seq Scan on items  (cost=0.00..15.75 rows=153 width=140)
         Filter: (year <= 2001)
   ->  Seq Scan on items items_1  (cost=0.00..15.75 rows=153 width=140)
         Filter: (year >= 2002)
(5 rows)

해당 결과를 보게 되면 UNION 쿼리는 Items 테이블에 2회 접근한다는 것을 알 수 있다. 그리고 TABLE ACCESS FULL이 발생하므로, 읽어들이는 비용도
테이블의 크기에 따라 선형으로 증가하게 된다.

만약 데이터 캐시에 테이블의 데이터가 있으면 어느정도 그런 증상이 완화될 수 있지만, 테이블의 크기가 커지면 캐시 히트율이 낮아지므로 비용 선택이 어려워 진다.

1.2 정확한 판단 없는 UNION 사용 회피

정확한 판단 없이 SELECT 구문 전체를 여러 번 사용해서 코드를 길게 만드는 것은 쓸데없는 테이블 접근을 발생 시키며 SQL 성능을 나쁘게 한다.
또한 물리 자원(저장소의 I/O 비용)의 소비도 불가피 하다.

2. WHERE 구에서 조건 분기는 초보자이다.

SQL에서 전해 내려오는 격언이다. "조건 분기를 WHERE 구로 하는 사람들은 초보자다. 잘 하는 사람은 SELECT 구만으로 조건 분기를 한다."
현재까지 살펴봤던 문제(TABLE ACCESS FULL 을 두번 하는 상황)은 사실 SELECT 구만으로 조건 분기를 하면 다음과 같이 최적화 할 수 있다.

• SELECT 구문에서 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;

해당 쿼리의 실행 계획은 아래와 같다.

기존에 일어났던 두번의 테이블 ACCESS를 한번의 ACCESS로 해결하게 되며, 성능의 개선을 이뤄낼 수 있다.

SQL 구문은 어떻게 데이터를 검색할지를 나타내는 접근 경로가 쓰여 있지 않다.
SQL 구문의 성능이 좋은지 나쁜지는 반드시 실행 계획 레벨에서 판단해야한다.

즉 EXPLAIN 키워드를 통해서 한번 체크 해주는게 좋다.

4. 정리 📚

UNION을 사용한 분기는 SELECT 구문을 기본 단위로 분기하고 있다.
구문을 기본 단위로 사용하고 있다는 점에서, 아직 절차 지향형 발상을 벗어나지 못한 것이다.

반면 CASE 식을 사용한 분기는 문자 그대로 '식'을 바탕으로 하는 사고이다.
이렇게 '구문'에서 '식'으로 사고를 변경하는 것이 SQL에서의 중요한 핵심이다.

해당 키워드에서 기억할 점은 IF 조건문을 고민하며, CASE로는 어떻게 해결할 수 있을까에 대한 고민을 해 보는것을 추천한다.