SQL을 작성할 때 놓치기 쉬운 5가지

in SQL on Sql


많은 분석가분들이 어떻게 하면 더 나은 쿼리를 작성할 수 있을까? 고민하실 것이라 생각합니다. “SQL 코딩의 기술(영문 제목: Effective SQL)”을 읽고 더 효율적인 쿼리를 작성하고 실수를 줄이기 위해 주의해야할 점들을 정리해봤습니다. 더욱 효율적이고, 정확한 쿼리를 작성하고자 하는 분석가 분들에게 도움이 되었으면 좋겠습니다.

1. 약간의 차이로 인덱스 사용여부가 결정된다.

A)

SELECT user_id, order_date
FROM orders
WHERE YEAR(order_date) = 2020

B)

SELECT user_id, order_date
FROM orders
WHERE order_date >= CAST('2020-01-01' AS DATE)
AND order_date < CAST('2021-01-01' AS DATE)

첫 번째 A 쿼리와 두 번째 B 쿼리의 차이점은 무엇일까요? 같은 의도를 가지고, 같은 데이터를 조회하는 쿼리이지만 두 쿼리는 다릅니다. order_date 컬럼이 인덱스에 해당할 때, A 쿼리는 인덱스를 사용해 수행시간이 줄어들지만 B 쿼리는 인덱스를 사용하지 못해 수행시간이 길어질 수 있습니다. 인덱스를 사용하여 쿼리 실행속도를 높일 수 있는 A 쿼리와 같은 쿼리를 sargable(Search ARGument ABLE) 쿼리라고 합니다. 반면, 인덱스를 사용하지 못하는 B 쿼리와 같은 쿼리를 non-sargable 쿼리라고 합니다.

아래와 같은 경우 인덱스를 활용하지 못하는 non-sargable 쿼리가 됩니다.

  • WHERE 절에서 컬럼에 대해 연산하는 함수를 사용하는 쿼리
  • WHERE 절에서 컬럼에 대해 수치 연산을 하는 쿼리
  • LIKE ‘%<문자열>%’을 사용하는 쿼리

첫 번째, WHERE 절에서 컬럼에 대해 연산하는 함수를 사용하는 쿼리는 위 예제처럼 컬럼에 함수를 적용하는 경우입니다.

SELECT user_id, order_date
FROM orders
WHERE YEAR(order_date) = 2020

위 쿼리가 인덱스를 활용할 수 없는 이유는 YEAR 함수가 반환하는 값을 인덱스 페이지에서 찾을 수 없기 때문입니다. 또한, 위 경우 WHERE 절에서 YEAR 함수가 먼저 평가(evaluate)되어야 하고, 이를 위해 해당 컬럼의 데이터를 모두 불러와야 할 것입니다. 결과적으로 인덱스를 활용할 수 없고, 더 긴 수행시간이 걸릴 것입니다. 위 예제에 대해 인덱스 활용여부를 비교하는 경우는 아래 글에서 자세히 확인하실 수 있습니다.

How to use sargable expressions in T-SQL queries; performance advantages and examples
https://www.sqlshack.com/how-to-use-sargable-expressions-in-t-sql-queries-performance-advantages-and-examples/

두 번째, WHERE 절에서 컬럼에 대해 수치 연산을 하는 경우입니다. 아래 쿼리의 경우 역시 첫 번째 경우와 같이 컬럼의 값을 가공하므로 인덱스를 사용할 수 없습니다.

SELECT product_id, product_name, product_price
FROM products
WHERE product_price * 1.1 > 1000000;

인덱스를 활용하는 쿼리로 수정하고 싶다면 아래와 같이 작성하는 것이 적절합니다.

SELECT product_id, product_name, product_price
FROM products
WHERE product_price > 1000000 / 1.1;

세 번째, LIKE ‘%<문자열>%’을 사용하는 경우입니다.

SELECT word
FROM words
WHERE word LIKE '%get%';

실행 결과)

vegetable
nugget
...

이 경우 get을 포함하는 모든 단어를 테이블에서 찾아야 합니다. 하지만 위 쿼리를 아래와 같이 바꾸면 get으로 시작하는 단어만을 찾아 탐색 범위를 상당히 줄일 수 있고, sargable 쿼리가 됩니다.

SELECT word
FROM word
WHERE word LIKE 'get%';

실행 결과)

get
getter

옵티마이저가 인덱스를 사용하게 하려면 WHERE 절에 값을 비교할 수 있는 조건이 들어가야 합니다. 인덱스는 값의 대소 비교를 통해 트리 구조를 구성하기 때문입니다. 트리 구조를 통해 값을 비교해서 탐색 영역을 줄여가며 값을 찾아가는 방식입니다. (인덱스의 구조 등에 대해 더욱 궁금하시다면 아래 글을 참고하시길 바랍니다.)

성능 향상을 위한 SQL 작성법
https://d2.naver.com/helloworld/1155

‘get%’의 경우 알파벳 순서로 정렬된 트리에서 값을 비교하며 빠르게 탐색 영역을 좁혀갈 수 있습니다. 영어사전이라면 g로 시작하는 영역만 찾아가면 됩니다. 하지만 ‘%get%’의 경우 a부터 z까지 모든 사전을 살펴보는 수고를 해야합니다. 이러한 차이로 같은 LIKE 구문을 사용하지만 특정 쿼리는 sargable 쿼리가 되고, 특정 쿼리는 non-sargable 쿼리가 됩니다. 아주 작은 차이로 쿼리의 수행속도가 차이날 수 있기 때문에 non-sargable 쿼리가 되는 이유를 숙지하고 있다면 쿼리 수행 속도를 높이는 데 도움이 될 것입니다.

2. LEFT JOIN 후 RIGHT TABLE에 대한 조건을 추가하면 OUTER JOIN의 효과가 사라진다.

A)

SELECT user.id, user.name
FROM user
LEFT JOIN subscription
ON user.id = subscription.user_id
WHERE subscription.payment_cycle = 'Monthly'

B)

SELECT u.id, u.name, s.payment_cycle
FROM user AS u
LEFT JOIN (
     SELECT user_id, payment_cycle
     FROM subscription
     WHERE payment_cycle = 'Monthly'
) AS s
ON a.id = s.application

첫 번째 A 쿼리와 두 번째 B 쿼리의 차이점은 무엇일까요? 얼핏 보면 같은 결과를 출력하는 두 개의 쿼리로 보입니다. 하지만 이 두 쿼리의 수행 결과는 다릅니다. A 쿼리는 아래 쿼리와 같이 LEFT JOIN 절 대신 INNER JOIN 절을 작성해도 수행결과가 같습니다.

SELECT user.id, user.name
FROM user
INNER JOIN subscription
ON user.id = subscription.user_id
WHERE subscription.payment_cycle = 'Monthly'

이유는 합쳐진 테이블 중 우측 테이블에 해당하는 subscription 테이블에 조건을 추가해주었기 때문입니다. 예를 들어 사용자가 회원가입만 하고 구독 결제를 하지 않았다면 subscription 테이블에 해당 사용자의 정보는 존재하지 않을 것입니다. 그러므로 아래 쿼리가 수행된 상황에서 subscription.payment_cycle의 값은 NULL 값일 것입니다.

SELECT user.id, user.name
FROM user
LEFT JOIN subscription
ON user.id = subscription.user_id

이 때 WHERE 절로 “subscription.payment_cycle = ‘Monthly’”을 추가하면 NULL 값은 어떠한 값과도 비교할 수 없기 때문에 NULL인 데이터는 제외하고 subscription.payment_cycle가 ‘Monthly’인 데이터만 남게 됩니다. 결국 user 테이블과 subscription 테이블 모두에 정보가 있어야 하는 INNER JOIN과 같은 결과를 반환하게 됩니다.

반면, 두 번째 B 쿼리의 수행 결과에는 서브쿼리에서 WHERE 절을 통해 추출한 데이터를 조인해주었기 때문에 구독 정보가 없는 사용자도 남아 있게 됩니다. LEFT JOIN의 동작 방식을 이해하고 있다면 당연하게 느껴질 수 있는 내용입니다. 하지만 무심코 작성한 쿼리가 의도와 다른 결과를 출력할 수 있다는 점에서 주의를 기울일 필요가 있습니다.

3. 가능하면 데이터는 WHERE 절을 사용해 필터링한다.

GROUP BY를 포함한 쿼리가 실행되는 순서는 아래와 같습니다.

  1. FROM 절에서 데이터 집합을 만든다.
  2. WHERE 절은 FROM 절에서 만든 데이터 집합을 조건에 맞게 걸러낸다.
  3. GROUP BY 절은 WHERE 절에서 조건에 맞게 걸러낸 데이터 집합을 집계한다.
  4. (HAVING 절이 있는 경우) HAVING 절은 GROUP BY 절에서 집계한 데이터 집합을 다시 조건에 맞게 걸러낸다.
  5. SELECT 절은 집계하고 필터링한 데이터 집합을 변환한다.
  6. ORDER BY 절은 변환된 데이터 집합을 정렬한다.

가능하면 데이터를 WHERE 절을 사용해 필터링해야 하는 이유는 집계의 대상이 되는 데이터를 줄여 주기 때문입니다. 위 내용 역시 사소할 수 있지만 명시적인 실행 순서를 인지하고 있다면 더욱 효율적인 SQL을 작성하는데 도움이 될 것입니다.

4. GROUP BY 절은 되도록 간단히 한다.

SQL-92 표준까지는 집계 연산을 수행하지 않는 모든 컬럼은 반드시 GROUP BY 절에 기술해야 했습니다. 현재 표준에서는 더 이상 요구하지 않지만 몇몇 DBMS의 경우 여전히 집계되지 않는 컬럼을 GROUP BY 절에 추가해주어야 합니다.

A)

SELECT 
	c.customer_id, 
	c.customer_first_name,
    c.customer_last_name, 
	c.customer_state,
	MAX(o.order_date)AS last_order_date,
	COUNT(o.order_number)AS order_count,
	SUM(o.order_total)AS total_amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_first_name, c.customer_last_name, c.customer_state;

B)

SELECT 
	c.customer_id, 
	c.customer_first_name,
    c.customer_last_name, 
	c.customer_state,
	o.last_order_date,
	o.order_count,
	o.total_amount
FROM customers AS c
LEFT JOIN (
	SELECT 
		customer_id, 
		MAX(order_date)AS last_order_date,
		COUNT(order_number)AS order_count,
		SUM(order_total)AS total_amount
	FROM orders
	GROUP BY customer_id
) AS o
ON c.customer_id = o.customer_id;

A 쿼리와 같이 작성하기보다는 B 쿼리와 같이 가능하다면 실제로 집계에 필요한 컬럼만 GROUP BY 절에 작성하는 방식이 좋습니다. GROUP BY 절에 컬럼을 과도하게 기술하면 쿼리 성능에 악영향을 미치고, 실제 집계의 기준이 되는 컬럼을 이해하기 어려워지기 때문입니다.

5. OUTER JOIN 후 COUNT 함수 사용 시 NULL 값을 포함해서 세지 않도록 주의한다.

아래 COUNT 함수에 대한 설명처럼 COUNT 함수는 값이 NULL이 아닌 행의 개수를 반환합니다. 그러므로 COUNT(*)은 모든 행의 개수, COUNT(<컬럼명>)은 NULL이 아닌 행의 개수를 반환합니다.

COUNT(expr) [over_clause]

Returns a count of the number of non-NULL values of expr in the rows 
retrieved by a SELECT statement.

MySQL COUNT함수에 대한 설명
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count

COUNT 함수가 NULL이 아닌 행의 개수를 반환한다는 사실을 인지하고도 OUTER JOIN 등과 결합되면 개수를 잘못 세는 실수를 할 수 있습니다.
user 테이블에는 회원가입 시의 사용자 정보가 저장되고, subscription 테이블에는 구독 결제 시 구독 정보가 저장되는 구조를 가정해보겠습니다. 이 때 사용자별로 월구독횟수를 구하는 쿼리를 작성했다고 했을 때, 아래 A 쿼리와 B 쿼리는 다른 결과를 출력합니다.

A)

SELECT u.id, count(*) as `monthly_subscription_count`
FROM user AS u
LEFT JOIN (
     SELECT user_id, payment_cycle
     FROM subscription
     WHERE payment_cycle = 'Monthly'
) AS s
ON a.id = s.user_id
GROUP BY u.id

B)

SELECT u.id, count(s.user_id) as `monthly_subscription_count`
FROM user AS u
LEFT JOIN (
     SELECT user_id, payment_cycle
     FROM subscription
     WHERE payment_cycle = 'Monthly'
) AS s
ON a.id = s.user_id
GROUP BY u.id

A 쿼리의 경우 LEFT TABLE과 연결되는 RIGHT TABLE의 정보가 없는, 즉 사용자의 구독 정보가 없는 행도 포함하여 월구독횟수를 세게 됩니다. 의도에 맞는 결과를 출력하려면 B 쿼리와 같이 NULL 값을 제외한 행들의 수를 집계하는 것이 적절합니다. 위 예제의 경우 사용자 테이블에 구독 테이블을 결합하지만 LEFT TABLE의 기준이 되는 데이터가 많지 않다면 아래와 같이 서브쿼리 형태로 작성하는 것도 방법일 것입니다.

SELECT user.id, (
    SELECT count(user_id)
    FROM subscription
    WHERE payment_cycle = 'Monthly'
    AND user.id = subscription.user_id
) AS `monthly_subscription_count`
FROM user

나가며

SQL이 익숙한 분들에게는 너무나 익숙하고 당연한 내용일 수 있습니다. 하지만 의도와 다르게 수행 시간이 길어지거나 수행 결과가 달라지지 않도록 주의를 기울인다면 실수를 미연에 방지하는데 도움이 될 것이라 생각합니다. 더 나은 쿼리를 작성하기 위해 고려해야할 것은 정말 많겠지만 하나씩 챙겨나가면 좋겠습니다. 도움이 되었으면 좋겠습니다.