많은 분석가분들이 어떻게 하면 더 나은 쿼리를 작성할 수 있을까? 고민하실 것이라 생각합니다. “SQL 코딩의 기술(영문 제목: Effective SQL)”을 읽고 더 효율적인 쿼리를 작성하고 실수를 줄이기 위해 주의해야할 점들을 정리해봤습니다. 더욱 효율적이고, 정확한 쿼리를 작성하고자 하는 분석가 분들에게 도움이 되었으면 좋겠습니다.
SELECT user_id, order_dateFROM ordersWHERE YEAR(order_date) = 2020
SELECT user_id, order_dateFROM ordersWHERE 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 절에서 컬럼에 대해 연산하는 함수를 사용하는 쿼리는 위 예제처럼 컬럼에 함수를 적용하는 경우입니다.
SELECT user_id, order_dateFROM ordersWHERE YEAR(order_date) = 2020
위 쿼리가 인덱스를 활용할 수 없는 이유는 YEAR 함수가 반환하는 값을 인덱스 페이지에서 찾을 수 없기 때문입니다. 또한, 위 경우 WHERE 절에서 YEAR 함수가 먼저 평가(evaluate)되어야 하고, 이를 위해 해당 컬럼의 데이터를 모두 불러와야 할 것입니다. 결과적으로 인덱스를 활용할 수 없고, 더 긴 수행시간이 걸릴 것입니다. 위 예제에 대해 인덱스 활용여부를 비교하는 경우는 아래 글에서 자세히 확인하실 수 있습니다.
How to use sargable expressions in T-SQL queries; performance advantages and examples
두 번째, WHERE 절에서 컬럼에 대해 수치 연산을 하는 경우입니다. 아래 쿼리의 경우 역시 첫 번째 경우와 같이 컬럼의 값을 가공하므로 인덱스를 사용할 수 없습니다.
SELECT product_id, product_name, product_priceFROM productsWHERE product_price * 1.1 > 1000000;
인덱스를 활용하는 쿼리로 수정하고 싶다면 아래와 같이 작성하는 것이 적절합니다.
SELECT product_id, product_name, product_priceFROM productsWHERE product_price > 1000000 / 1.1;
세 번째, LIKE ‘%<문자열>%’을 사용하는 경우입니다.
SELECT wordFROM wordsWHERE word LIKE '%get%';
실행 결과)
vegetablenugget...
이 경우 get을 포함하는 모든 단어를 테이블에서 찾아야 합니다. 하지만 위 쿼리를 아래와 같이 바꾸면 get으로 시작하는 단어만을 찾아 탐색 범위를 상당히 줄일 수 있고, sargable 쿼리가 됩니다.
SELECT wordFROM wordWHERE word LIKE 'get%';
실행 결과)
getgetter
옵티마이저가 인덱스를 사용하게 하려면 WHERE 절에 값을 비교할 수 있는 조건이 들어가야 합니다. 인덱스는 값의 대소 비교를 통해 트리 구조를 구성하기 때문입니다. 트리 구조를 통해 값을 비교해서 탐색 영역을 줄여가며 값을 찾아가는 방식입니다. (인덱스의 구조 등에 대해 더욱 궁금하시다면 아래 글을 참고하시길 바랍니다.)
‘get%’의 경우 알파벳 순서로 정렬된 트리에서 값을 비교하며 빠르게 탐색 영역을 좁혀갈 수 있습니다. 영어사전이라면 g로 시작하는 영역만 찾아가면 됩니다. 하지만 ‘%get%’의 경우 a부터 z까지 모든 사전을 살펴보는 수고를 해야합니다. 이러한 차이로 같은 LIKE 구문을 사용하지만 특정 쿼리는 sargable 쿼리가 되고, 특정 쿼리는 non-sargable 쿼리가 됩니다. 아주 작은 차이로 쿼리의 수행속도가 차이날 수 있기 때문에 non-sargable 쿼 리가 되는 이유를 숙지하고 있다면 쿼리 수행 속도를 높이는 데 도움이 될 것입니다.
SELECT user.id, user.nameFROM userLEFT JOIN subscriptionON user.id = subscription.user_idWHERE subscription.payment_cycle = 'Monthly'
SELECT u.id, u.name, s.payment_cycleFROM user AS uLEFT JOIN (SELECT user_id, payment_cycleFROM subscriptionWHERE payment_cycle = 'Monthly') AS sON a.id = s.application
첫 번째 A 쿼리와 두 번째 B 쿼리의 차이점은 무엇일까요? 얼핏 보면 같은 결과를 출력하는 두 개의 쿼리로 보입니다. 하지만 이 두 쿼리의 수행 결과는 다릅니다. A 쿼리는 아래 쿼리와 같이 LEFT JOIN 절 대신 INNER JOIN 절을 작성해도 수행결과가 같습니다.
SELECT user.id, user.nameFROM userINNER JOIN subscriptionON user.id = subscription.user_idWHERE subscription.payment_cycle = 'Monthly'
이유는 합쳐진 테이블 중 우측 테이블에 해당하는 subscription 테이블에 조건을 추가해주었기 때문입니다. 예를 들어 사용자가 회원가입만 하고 구독 결제를 하지 않았다면 subscription 테이블에 해당 사용자의 정보는 존재하지 않을 것입니다. 그러므로 아래 쿼리가 수행된 상황에서 subscription.payment_cycle의 값은 NULL 값일 것입니다.
SELECT user.id, user.nameFROM userLEFT JOIN subscriptionON user.id = subscription.user_id
이 때 WHERE 절로 “subscription.payment_cycle = ‘Monthly’”을 추가하면 NULL 값은 어떠한 값과도 비교할 수 없기 때문에 NULL인 데이터는 제외하고 subscription.payment_cycle가 ‘Monthly’인 데이터만 남게 됩니다. 결국 user 테이블과 subscription 테이블 모두에 정보가 있어야 하는 INNER JOIN과 같은 결과를 반환하게 됩니다.
반면, 두 번째 B 쿼리의 수행 결과에는 서브쿼리에서 WHERE 절을 통해 추출한 데이터를 조인해주었기 때문에 구독 정보가 없는 사용자도 남아 있게 됩니다. LEFT JOIN의 동작 방식을 이해하고 있다면 당연하게 느껴질 수 있는 내용입니다. 하지만 무심코 작성한 쿼리가 의도와 다른 결과를 출력할 수 있다는 점에서 주의를 기울일 필요가 있습니다.
GROUP BY를 포함한 쿼리가 실행되는 순서는 아래와 같습니다.
가능하면 데이터를 WHERE 절을 사용해 필터링해야 하는 이유는 집계의 대상이 되는 데이터를 줄여 주기 때문입니다. 위 내용 역시 사소할 수 있지만 명시적인 실행 순서를 인지하고 있다면 더욱 효율적인 SQL을 작성하는데 도움이 될 것입니다.
SQL-92 표준까지는 집계 연산을 수행하지 않는 모든 컬럼은 반드시 GROUP BY 절에 기술해야 했습니다. 현재 표준에서는 더 이상 요구하지 않지만 몇몇 DBMS의 경우 여전히 집계되지 않는 컬럼을 GROUP BY 절에 추가해주어야 합니다.
SELECTc.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_amountFROM customers AS cLEFT JOIN orders AS oON c.customer_id = o.customer_idGROUP BY c.customer_id, c.customer_first_name, c.customer_last_name, c.customer_state;
SELECTc.customer_id,c.customer_first_name,c.customer_last_name,c.customer_state,o.last_order_date,o.order_count,o.total_amountFROM customers AS cLEFT JOIN (SELECTcustomer_id,MAX(order_date)AS last_order_date,COUNT(order_number)AS order_count,SUM(order_total)AS total_amountFROM ordersGROUP BY customer_id) AS oON c.customer_id = o.customer_id;
A 쿼리와 같이 작성하기보다는 B 쿼리와 같이 가능하다면 실제로 집계에 필요한 컬럼만 GROUP BY 절에 작성하는 방식이 좋습니다. GROUP BY 절에 컬럼을 과도하게 기술하면 쿼리 성능에 악영향을 미치고, 실제 집계 의 기준이 되는 컬럼을 이해하기 어려워지기 때문입니다.
아래 COUNT 함수에 대한 설명처럼 COUNT 함수는 값이 NULL이 아닌 행의 개수를 반환합니다. 그러므로 COUNT(*)은 모든 행의 개수, COUNT(<컬럼명>)은 NULL이 아닌 행의 개수를 반환합니다.
COUNT(expr) [over_clause]Returns a count of the number of non-NULL values of expr in the rowsretrieved by a SELECT statement.
COUNT 함수가 NULL이 아닌 행의 개수를 반환한다는 사실을 인지하고도 OUTER JOIN 등과 결합되면 개수를 잘못 세는 실수를 할 수 있습니다.
user 테이블에는 회원가입 시의 사용자 정보가 저장되고, subscription 테이블에는 구독 결제 시 구독 정보가 저장되는 구조를 가정해보겠습니다. 이 때 사용자별로 월구독횟수를 구하는 쿼리를 작성했다고 했을 때, 아래 A 쿼리와 B 쿼리는 다른 결과를 출력합니다.
SELECT u.id, count(*) as `monthly_subscription_count`FROM user AS uLEFT JOIN (SELECT user_id, payment_cycleFROM subscriptionWHERE payment_cycle = 'Monthly') AS sON a.id = s.user_idGROUP BY u.id
SELECT u.id, count(s.user_id) as `monthly_subscription_count`FROM user AS uLEFT JOIN (SELECT user_id, payment_cycleFROM subscriptionWHERE payment_cycle = 'Monthly') AS sON a.id = s.user_idGROUP BY u.id
A 쿼리의 경우 LEFT TABLE과 연결되는 RIGHT TABLE의 정보가 없는, 즉 사용자의 구독 정보가 없는 행도 포함하여 월구독횟수를 세게 됩니다. 의도에 맞는 결과를 출력하려면 B 쿼리와 같이 NULL 값을 제외한 행들의 수를 집계하는 것이 적절합니다. 위 예제의 경우 사용자 테이블에 구독 테이블을 결합하지만 LEFT TABLE의 기준이 되는 데이터가 많지 않다면 아래와 같이 서브쿼리 형태로 작성하는 것도 방법일 것입니다.
SELECT user.id, (SELECT count(user_id)FROM subscriptionWHERE payment_cycle = 'Monthly'AND user.id = subscription.user_id) AS `monthly_subscription_count`FROM user
SQL이 익숙한 분들에게는 너무나 익숙하고 당연한 내용일 수 있습니다. 하지만 의도와 다르게 수행 시간이 길어지거나 수행 결과가 달라지지 않도록 주의를 기울인다면 실수를 미연에 방지하는데 도움이 될 것이라 생각합니다. 더 나은 쿼리를 작성하기 위해 고려해야할 것은 정말 많겠지만 하나씩 챙겨나가면 좋겠습니다. 도움이 되었으면 좋겠습니다.