[ 글또 7기 ] LeetCode는 이상하고 MySQL은 모르겠어

블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.

본 제목은 이예린 가수님의 사람은 이상하고 사랑은 모르겠어를 응용했습니다.

도입

아래와 같이 테이블 Orders 가 존재한다고 생각해보자. 그리고 간단하게 MySQL에서 user_id 별 주문량의 합계, 다시 말해 amount 필드의 합계를 구한다고 생각해보자. 단, 이때 출력되는 user_id 필드 명을 customer 라 하고 싶다.

/*
Table: Orders
Primary Key: order_id
Foreign Key: user_id FROM Users
*/
+----------+---------+--------+------------+
| order_id | user_id | amount | order_date |
+----------+----------+-------+------------+
| 1        | 1        | 4     | 2022-01-03 |
| 2        | 1        | 5     | 2022-01-24 |
| 3        | 1        | 6     | 2022-02-05 |
| 4        | 2        | 1     | 2022-01-24 |
| 5        | 2        | 2     | 2022-02-20 |
+----------+----------+-------+------------+

무척 간단하다. user_id 필드를 기준으로 하여 GROUP BY 구를 정의하고 SUM 함수를 써서 amont 필드의 합계를 구하면 될 것 같다. 그리고 SELECT 에서 AS 키워드를 통해 user_id 필드의 별칭을 customer 로 변경하면 된다.

그렇다면 아래 쿼리의 결괏값으로 무엇이 나올까?

SELECT
	user_id AS customer,
    SUM(amount) AS total_amount
FROM Orders
GROUP BY customer;

무언가 이상하다. user_id 필드로 GROUP BY 구를 정의한 게 아니라 SELECT 구에서 사용한 별칭 customer 필드로 정의했다. 자연스레 쿼리 실행 순서가 떠오를 것이며 GROUP BY 구가 먼저 실행되고 나서 SELECT 가 실행되기 때문에 당연히 해당 쿼리는 오류가 날 것이다.

+----------+--------------+
| customer | total_amount | 
+----------+--------------+
| 1        | 15           |
| 2        | 3            |
+----------+--------------+

하지만 애석하게도 아니다. MySQL에서는 정상적으로 위와 같이 원하는 결괏값을 출력해준다.

갑자기 알고 있던 지식이 흔들리기 시작한다. 그동안 그러면 인터넷 상에 존재하는 블로그 글들이 잘못된 정보를 전달해주고 있던 걸까?

우선은 표준 SQL에 대해 다시 한번 정리해야겠다.

표준 SQL

표준 SQL에서 GROUP BY 구에 사용할 수 없는 필드는 아래와 같이 세 가지다.

별칭

먼저 앞선 예시에서 본 별칭(Alias)이다.

위에서 AS 키워드를 통해 SELECT 구에서 정의했던 별칭을 표준 SQL에서는 GROUP BY 구에 사용하지 못한다. 실행 순서가 GROUP BY 구가 SELECT 구보다 앞서기 때문에 아직 정의되지 않은 필드에 접근하려 해서 오류가 발생한다. 따라서 아래와 같이 수정해야 한다.

SELECT
	user_id AS customer,
    SUM(amount) AS total_amount
FROM Orders
GROUP BY user_id

집계되지 않은 컬럼

만약 위 쿼리에서 order_date 필드도 반환하고 싶다면 어떻게 해야할까?

GROUP BY 구의 기준은 user_id 필드를 기준으로 되어 있고 amount 필드의 경우 SUM 함수를 통해 집계(Aggregate) 되는데 order_date 필드는 따로 집계를 안 하면 복수의 값들 중 어떤 값을 사용해야 하는지 내부적으로 알 수 없기 때문에 오류가 발생한다.

쉽게 말해 아래와 같은 쿼리는 오류를 반환한다.

SELECT
	user_id AS customer,
    SUM(amount) AS total_amount,
    order_date
FROM Orders
GROUP BY user_id

따라서 만약 order_date 필드를 굳이 사용하고 싶다면 아래와 같이 MAX 함수를 사용하여 가장 최신의 주문일자나, MIN 함수를 사용하여 맨 처음의 주문일자 등 집계를 해서 반환해야 한다.

SELECT
	user_id AS customer,
    SUM(amount) AS total_amount,
    MAX(order_date) AS latested_order_date -- 가장 최신 주문일자
FROM Orders
GROUP BY user_id

컬럼이 아닌 것

끝으로 컬럼이 아닌 것이다.

앞선 Orders 테이블에서 주문일자를 의미하는 order_date 필드, 그 중에서도 월(Month)을 기준으로 GROUP BY 구를 사용해서 월별 주문총량을 구한다고 가정해보자. MONTH 함수 또는 DATE_FORMAT 함수 등을 사용해서 order_date 필드의 월 값만 추출한 다음 그 결과를 기준으로 GROUP BY 구를 사용하면 될 것 같다.

따라서 아래와 같이 서브쿼리를 활용해서 GROUP BY 구를 사용해야 한다. MONTH 함수를 직접적으로 GROUP BY 구에 사용할 수 없기 때문에 FROM 구에 서브쿼리를 활용해야 한다.

SELECT
	order_month,
    SUM(amount) AS total_amount
FROM (
	SELECT
    	amount,
    	MONTH(order_date) AS order_month
    FROM Orders
) AS MonthlyAmounts
GROUP BY order_month;

MySQL의 확장성

이제 우리가 생각했던 것과 다르게 작동하던 MySQL을 한번 살펴보자.

MySQL은 기본적으로 표준 SQL을 확장하기 때문에 앞서 표준 SQL에서 불가능했던 별칭 및 컬럼이 아닌 것 모두 GROUP BY 구에서 사용 가능하다.

별칭

앞서 처음 도입 때 작성했던 쿼리처럼 -아래와 같이- GROUP BY 구에 SELECT 구에서 AS 키워드를 통해 정의한 별칭인 customer 컬럼명을 사용해도 정상적으로 결괏값을 반환한다.

SELECT
	user_id AS customer,
    SUM(amount) AS total_amount
FROM Orders
GROUP BY customer;

컬럼이 아닌 것

다음으로 MySQL에서는 컬럼이 아닌 것, 이를 테면 함수 등의 반환 값을 GROUP BY 구에 사용할 수 있다.

따라서 컬럼이 아닌 것을 사용하기 위해 서브쿼리를 활용했던 앞선 예시와 달리 서브쿼리 없이 아래와 같이 GROUP BY 구에 바로 MONTH 함수를 사용해도 정상적으로 결괏값을 반환한다.

SELECT
	MONTH(order_date) AS order_month,
    SUM(amount) AS total_amount
FROM Orders
GROUP BY MONTH(order_date);

위 쿼리를 더 깔끔하게 작성하면 아래와 같이 별칭을 사용하면 된다.

SELECT
	MONTH(order_date) AS order_month,
    SUM(amount) AS total_amount
FROM Orders
GROUP BY order_month

집계되지 않은 컬럼

문제는 집계되지 않은 컬럼이다. MySQL에서도 집계되지 않은 컬럼은 오류를 발생한다. 앞서 말했던 것처럼 어떤 값을 반환해야 하는지 모르기 때문이다.

그런데 LeetCode에서는 된다. 다시 말해 아래 쿼리를 실행했을 때 집계되지 않은 order_date 필드의 값이 정상적으로 반환이 되어 오류가 발생하지 않는다.

SELECT
	user_id AS customer,
    SUM(amount) AS total_amount,
    order_date
FROM Orders
GROUP BY user_id

그렇다면 그 숱한 order_date 값들 중에서 어떤 값을 반환하는 걸까? 결론부터 말하면 무작위로 선택되는데 MySQL에서 GROUP BY 구는 내부적으로 정렬을 수행하기 때문에 보통 가장 마지막 값을 반환한다.

MySQL에서 GROUP BY 구가 내부적으로 정렬을 한다는 점에 있어 추가적으로 알아두면 좋은 부분은 DISTINCT 키워드와의 차이다.

GROUP BY 구는 어떤 특정 컬럼들을 기준으로 그룹핑을 하기 때문에 이는 다시 말해 그 특정 컬럼들의 중복을 제거하는 것과 같다. 중복을 제거한다는 부분으로만 생각하면 DISTINCT 키워드를 사용하는 것과 동일한 작업을 수행하는 것인데 MySQL에서는 GROUP BY 구의 경우 내부적으로 정렬을 수행하고, DISTINCT 키워드를 사용하는 경우 내부적으로 정렬을 수행하지 않기 때문에 DISTINCT 키워드의 퍼포먼스가 훨씬 좋다.

그런데 문제는 해당 쿼리를 로컬 MySQL에 테이블을 간단하게 만들어서 구해보면 오류를 반환한다.

잠깐, LeetCode에서도 MySQL을 사용했고 로컬에서도 MySQL을 썼는데 왜 둘 사이에 차이가 발생한 걸까?

문제는 바로 MySQL의 서버 SQL 모드 중 ONLY_FULL_GROUP_BY 옵션 때문이다.

서버 SQL 모드

MySQL에는 서버 SQL 모드라는 게 존재한다. 이를 토대로 각각의 클라이언트에게 다른 종류의 SQL 형태를 제공할 수 있다.

예를 들어 POSTGRESQL 또는 ORACLE 등의 옵션 값을 통해서 표준 SQL을 기준으로 각 RDB(Relational Database) 마다의 차이를 적용시킬 수 있는 것이다. 그러면 어떤 클라이언트에서는 MySQL을 사용하더라도 PostgreSQL과 유사하게, 어떤 클라이언트에서는 Oracle과 유사하게 사용할 수 있다.

단순히 RDB 종류 외에도 문법적인 측면에도 개별적으로 설정을 변경할 수 있다. 예를 들어 아래 쿼리를 실행하면 어떤 결괏값을 얻을 수 있을까?

SELECT 2/0;

어떤 수를 0 으로 나눌 수 없기 때문에 DIVISION_BY_ZERO 오류를 반환할 것 같지만 결과는NULL 값을 반환한다. 이는 MySQL의 서버 SQL 모드 중 ERROR_FOR_DIVISION_BY_ZERO 옵션 때문이다.

해당 옵션이 만약 켜져 있다면 -공식 문서에서는 'enabled'라 표현- NULL 값과 함께 경고(Warning)를 반환한다. 만약 꺼져 있다면 -disalbed- NULL 값을 반환하는 것은 동일하지만 어떠한 경고도 반환하지 않는다. 끝으로 해당 옵션이 켜져 있으면서 동시에 STRICT 모드를 옵션으로 선택하면 경고가 아닌 오류(Error)를 반환하여 쿼리가 실행되지 않는다.

MySQL 5.6.17 버전 이후부터 ERROR_FOR_DIVISION_BY_ZERO 옵션은 없어졌다. 그리고 내부 sql_mode 자체에 포함된 상태로 되어 단순히 경고를 반환하는 것으로 변경되었다.

ONLY_FULL_GROUP_BY 옵션

GROUP BY 구도 이와 마찬가지다. GROUP BY 구와 관련된 서버 SQL 모드 옵션은 바로 ONLY_FULL_GROUP_BY 옵션이다.

만약 해당 옵션이 꺼져 있다면 앞서 집계되지 않은 컬럼을 SELECT 구에 사용할 수 있다. 다시 말해 아래 쿼리처럼 집계되지 않아 어떤 값을 반환해야 할 지 모르겠는 order_date 필드 또한 SELECT 구에 정상적으로 사용할 수 있는 것이다.

SELECT
	user_id AS customer,
    SUM(amount) AS total_amount,
    order_date
FROM Orders
GROUP BY user_id

만약 해당 옵션이 켜져 있다면 집계되지 않는 컬럼을 조회하려 했기 때문에 어떤 값을 조회해야 할 지 몰라 오류가 발생한다. 기본적으로 ONLY_FULL_GROUP_BY 옵션은 켜져 있기 때문에 로컬 MySQL의 경우 별도로 옵션을 건들지 않는 이상 ONLY_FULL_GROUP_BY 옵션이 켜져 있는 상태라 LeetCode와 달리 쿼리가 실행되지 않고 오류를 발생시킨 것이다.

결론

MySQL은 표준 SQL의 GROUP BY 구를 확장하여 GROUP BY 구의 대상으로 별칭(Alias) 및 컬럼이 아닌 것(Noncolumn)을 사용할 수 있게 한다. 이외에도 ONLY_FULL_GROUP_BY 옵션을 꺼서 집계되지 않은 컬럼(Nonaggregated Column)까지 SELECT 구에서 사용할 수 있다.

MySQL에서 GROUP BY 구를 사용하면 내부적으로 정렬을 하기 때문에 이처럼 불필요한 정렬과 그룹핑을 사용하지 않고 퍼포먼스를 높이기 위해서는 ONLY_FULL_GROUP_BY 옵션을 끄는 게 좋다고 이야기 한다.

하지만 생각하지 못한 값이 무작위로 반환 받는 경우가 무척 많을 수 있기 때문에 쿼리와 그 실행 계획(Execution Plan) 등에 익숙하지 않다면 기본 값으로 설정되어 있는 걸 건들지 말고 그대로 ONLY_FULL_GROUP_BY 옵션은 켜져 있는 상태로 사용하는 게 좋아 보인다.

추가적으로 GROUP BY 구 및 집계 함수(Aggregate Function)를 윈도우 함수(Window Function)와 함께 살펴보면 좋은데 관련해서 가능하면 다음에 다뤄보고자 한다.

참고

MySQL 공식 문서

• 5.1.10 Server SQL Modes
• 12.20.3 MySQL Handling of GROUP BY