SQL 레벨업 3장 SQL의 조건 분기

Bryan Lee 2022. 4. 11. 20:29

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을 사용할 때와 같습니다.

따라서 성능적으로 같은 문제를 안게 됩니다.