2024-06-26

Group by deep dive

`GROUP BY`은 어떤 조건에서 실행되는가

GROUP BY 절은 특정 열을 기준으로 데이터를 그룹화하여 집계 함수(예: MAX, SUM, COUNT, AVG)를 사용할 때 유용
하지만 MySQL에서는 sql_mode 설정에 따라 GROUP BY 절의 동작 방식이 달라질 수 있다.

SELECT 
    user_id,
    MAX(view_datetime) AS last_view_datetime,
    artwork_id
FROM 
    artwork_view_log
GROUP BY 
    user_id;

하지만 위 쿼리는 실행되지 않는다. 이유는 artwork_id가 GROUP BY 절에 포함되지 않았고, 집계 함수로 처리되지 않은 컬럼이기 때문이다.
MySQL의 sql_mode 설정에 따라 이런 경우 오류가 발생할 수 있다.

sql_mode에 only_full_group_by가 설정되어 있는 경우, GROUP BY에 포함되지 않은 필드를 SELECT 절에 포함하면 오류가 발생
하지만 함수적 종속성을 고려하면 only_full_group_by 설정을 바꾸지 않아도 그룹바이가 실행된다.
RDS, AURORA에는 ONLY_FULL_GROUP_BY 를 직접적으로 바꿀 수 없다.

함수적 종속성은 데이터베이스 이론에서 특정 속성 집합이 다른 속성 집합을 유일하게 식별할 수 있는 관계를 의미한다.
- 예를 들어, 테이블에서 A -> B가 성립하면, A 값이 주어졌을 때 B 값이 유일하게 결정된다는 뜻
데이터 무결성과 관련이 깊음

SELECT 
    user_id,
    MAX(user_email) AS max_email,
    user_type
FROM 
    users
GROUP BY 
    user_id;

user_type 필드가 GROUP BY 절에 포함되지 않았고, 집계 함수로 처리되지 않았기 때문에 원래대로라면 쿼리는 위의 논리대로라면 실행되면 안된다.
그러나 user_id가 기본 키이므로, user_id가 주어지면 user_email과 user_type이 유일하게 식별된다. 따라서 쿼리 실행이 가능하다.

함수적 종속성: 특정 속성 집합이 다른 속성 집합을 유일하게 결정할 수 있는 관계.
함수적 종속성이 있다면GROUP BY 절에 집계되지 않은 필드를 SELECT 절에 포함할 수 있음.
sql_mode 설정: ONLY_FULL_GROUP_BY 설정이 활성화된 경우, GROUP BY 절에 포함되지 않은 필드를 SELECT 절에 포함할 수 없음.

인덱스 구성: UNIQUE(user_id, artwork_id, view_datetime)와 같은 복합 인덱스를 사용하면 쿼리 성능이 향상
정렬 순서: GROUP BY 절의 필드 순서가 인덱스의 필드 순서와 일치하면 인덱스를 효과적으로 사용 가능
EXPLAIN: 쿼리 실행 계획을 확인하여 인덱스가 제대로 사용되는지 확인 가능
- 순서를 맞추지 않으면 Using temporary 발생
- 인덱스 정렬 순서가 탐색에 반영되지 못해서, 임시 테이블을 만들고 거기서 임시로 정렬을 한다. -> 비효율적
- 반면에 순서를 맞추면 index full search가 일어남. -> 적용이 됨
이미 충분히 효율적이지만 COUNT(DISTINCT)를 통해서 더 최적화 가능

SELECT 
    user_id, 
    artwork_id, 
    COUNT(DISTINCT view_datetime)
FROM 
    views
GROUP BY 
    user_id, artwork_id;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	views	range	PRIMARY	PRIMARY	12	NULL	1000	Using index for group-by