MySQL GROUP BY 성능과 대규모 그룹화

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

테스트 테이블에서는 밀리초 만에 돌아오는 GROUP BY가 실제 테이블에서는 몇 분이 걸릴 수 있으며, 쿼리 텍스트는 동일합니다. 차이는 서버가 인덱스를 그룹 순서로 따라갈 수 있는지, 아니면 먼저 임시 테이블을 만들어야 하는지에 있습니다. 이 가이드는 그 차이와, 어느 쪽을 얻게 되는지를 결정하는 선택에 관한 것입니다. 절 자체를 배우는 중이라면, 튜토리얼이 샘플 데이터베이스를 대상으로 가르칩니다: 집계 함수, GROUP BY, HAVING, COUNT DISTINCT, GROUP_CONCAT, WITH ROLLUP. 이 페이지의 모든 쿼리와 플랜은 Sakila 샘플 데이터베이스를 대상으로 한 서버 1대에서 얻은 것입니다. 비용과 행 추정치는 InnoDB의 샘플링된 통계와 optimizer_switch 설정에서 나오므로, 여러분의 수치는 다를 것입니다. 각 플랜의 형태가 그대로 적용되는 부분입니다.

인덱스 없는 그룹화는 임시 테이블을 만든다

행을 그룹화하려면 서버는 각 그룹의 행을 한곳에 모아야 합니다. 그룹화 열에 인덱스가 없으면 임시 테이블을 만들어서 그렇게 합니다:

EXPLAIN SELECT rating, COUNT(*) FROM film GROUP BY rating\G

         type: ALL
possible_keys: NULL
          key: NULL
         rows: 1000
        Extra: Using temporary

Using temporary가 찾아야 할 줄입니다. 1,000행에서는 비용이 들지 않습니다. 테이블은 메모리에 들어가는 동안은 메모리에 유지되고, 들어가지 않으면 디스크로 넘치는데, 그룹화된 보고서가 느려지기 시작하는 지점이 바로 여기입니다. 그 메모리를 제한하는 설정은 서버가 내부 임시 테이블에 사용하는 엔진에 따라 다릅니다. 무엇이든 바꾸기 전에 자신의 설정을 확인하세요:

SELECT @@internal_tmp_mem_storage_engine AS engine,
       @@temptable_max_ram, @@temptable_max_mmap, @@tmp_table_size;

이들은 서로 다른 일을 하며, TempTable에서는 모두 적용됩니다. temptable_max_ram과 temptable_max_mmap은 엔진이 모든 세션에 걸쳐 보유할 수 있는 양을 제한합니다. tmp_table_size는 개별 내부 임시 테이블 각각을 제한합니다: 1 KB에서는 1,000행에 대한 그룹화 쿼리가 Created_tmp_disk_tables를 0에서 1로 올렸고, 64 MB에서는 같은 쿼리가 메모리에 머물렀습니다. 추측하는 대신 그 카운터를 지켜보세요:

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

그룹화 열에 인덱스가 있으면 이 단계가 완전히 사라집니다. 인덱스가 이미 그 순서로 되어 있기 때문입니다:

EXPLAIN SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id\G

         type: index
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
         rows: 16500
        Extra: Using index

Using temporary도 없고 Using filesort도 없습니다. 서버는 인덱스를 순서대로 읽고, 같은 값이 이어지는 구간마다 개수를 세어 행을 내보냅니다. Using index는 테이블 자체를 전혀 건드리지 않았다는 뜻입니다. 따라서 느린 GROUP BY에서 가장 먼저 확인할 것은, 그룹화한 것과 같은 순서로 그룹화 열로 시작하는 인덱스가 있는지입니다. (a, b)에 대한 복합 인덱스는 GROUP BY a와 GROUP BY a, b에 쓰입니다. GROUP BY b에서는 서버가 여전히 그것을 커버링 인덱스로 스캔할 수 있어 type: index와 Using index로 보고되지만, 행이 그룹 순서를 벗어나 도착하므로 Using temporary가 함께 돌아옵니다.

WHERE와 HAVING은 스타일 선택이 아니다

둘 다 필터를 표현할 수 있습니다. 비용은 다른데, WHERE는 그룹화 전에 실행되고 HAVING은 그룹화 후에 실행되기 때문입니다. 그룹화 열 자체에 대한 조건은 어느 쪽으로든 쓸 수 있고 같은 의미이므로, 공정한 비교가 됩니다. WHERE에서는 서버가 먼저 인덱스 범위를 좁히고 읽은 것만 그룹화합니다:

EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment WHERE customer_id <= 100 GROUP BY customer_id\G

-> Group aggregate: count(0)  (cost=814 rows=599)
    -> Filter: (payment.customer_id <= 100)  (cost=543 rows=2710)
        -> Covering index range scan on payment using idx_fk_customer_id over (customer_id <= 100)  (cost=543 rows=2710)

HAVING에서는 같은 제한이 너무 늦게 도착하여 아무것도 좁히지 못합니다:

EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id HAVING customer_id <= 100\G

-> Filter: (payment.customer_id <= 100)  (cost=3324 rows=599)
    -> Group aggregate: count(0)  (cost=3324 rows=599)
        -> Covering index scan on payment using idx_fk_customer_id  (cost=1674 rows=16500)

나오는 행은 같지만 추정 비용은 4배입니다. WHERE 버전은 추정 2,710개의 인덱스 항목을 읽고, HAVING 버전은 인덱스 전체를 읽은 뒤 대부분의 그룹을 버립니다. 이 추정치는 InnoDB의 샘플링된 통계와 mysql.server_cost의 비용 상수에서 나오므로, 여러분의 수치는 이것과 다를 것입니다. 두 플랜의 형태가 그대로 적용되는 부분입니다. 규칙: 조건이 행을 검사하는 것이라면 언제나 WHERE에 두세요. HAVING은 그룹을 검사하는 것에만 사용하며, 실무에서는 집계를 포함하는 모든 것이 여기에 해당합니다. 집계가 없는 HAVING 절은 거의 항상 잘못된 자리에 놓인 WHERE 절이고, 집계가 들어간 WHERE는 ERROR 1111 (HY000): Invalid use of group function입니다.

ONLY_FULL_GROUP_BY와 탈출구

only_full_group_by는 기본적으로 켜져 있으며, 그룹화되지도 집계되지도 않은 열을 선택하는 쿼리를 ERROR 1055로 거부합니다. 그 이유는 그런 쿼리에는 정의된 답이 없기 때문입니다: 그룹에는 많은 행이 있고, 어느 것을 보여줄지 아무것도 말해주지 않습니다. ANY_VALUE()는 임의의 하나를 받아들이겠다고 서버에 알리며, 그러면 쿼리가 실행됩니다:

SELECT rating, ANY_VALUE(title) AS a_title, COUNT(*) AS films
FROM film
GROUP BY rating
ORDER BY rating;

여기에는 의도적으로 출력을 보여주지 않습니다. 어떤 제목이 돌아올지 정의되어 있지 않으므로, 이 페이지에 인쇄된 어떤 결과도 여러분이 재현할 수 없는 값이 됩니다. ANY_VALUE()는 열이 그룹화 열에 함수적으로 종속되어 있는데 서버가 그것을 증명할 수 없을 때 사용하세요. 그것이 정당한 경우입니다. 그룹 안에서 실제로 값이 달라지는 열에 대해 오류를 잠재우려고 사용하지 마세요: 얻는 값은 첫 번째도, 가장 작은 것도, 그 밖에 의지할 수 있는 어떤 것도 아니며, 플랜이 바뀌면 달라질 수 있습니다. MIN()이나 MAX()는 의도를 말해주며 비용은 같습니다. only_full_group_by를 전역으로 끄면 이 오류 하나하나가 아무도 보지 못하는 잘못된 답으로 바뀝니다. 잘못된 해결책입니다. 이 검사는 그룹화된 열에 기반한 표현식은 꿰뚫어 보므로, SELECT COALESCE(rating, 'unrated') ... GROUP BY rating은 허용됩니다. 그룹화된 표현식에 기반한 표현식은 꿰뚫어 보지 못합니다. DATE_FORMAT(payment_date, '%Y-%m')으로 그룹화하고 COALESCE(DATE_FORMAT(payment_date, '%Y-%m'), 'ALL')을 선택하면, 같은 ERROR 1055가 payment_date를 지목합니다. 서버가 그 안의 열이 아니라 두 표현식을 비교하기 때문입니다. 이것이 흔한 보고서 사례이며, 해결 방법은 파생 테이블에서 그룹화하고 그 바깥에서 결과를 감싸는 것입니다:

SELECT COALESCE(month, 'ALL') AS month, payments
FROM (SELECT DATE_FORMAT(payment_date, '%Y-%m') AS month, COUNT(*) AS payments
      FROM payment GROUP BY DATE_FORMAT(payment_date, '%Y-%m')) AS m
ORDER BY month;

일대다 조인 집계

그룹화 전에 조인하면 행이 늘어나므로, 그 결과에 대한 SUM은 이중 계산을 합니다. 증상은 아무도 설명할 수 없는 배수만큼 너무 큰 합계입니다. 먼저 조인하고 나중에 그룹화하는 대신, 각 쪽을 자체 서브쿼리에서 집계하고 그 결과를 조인하세요:

SELECT c.last_name, p.payments, r.rentals
FROM customer AS c
JOIN (SELECT customer_id, COUNT(*) AS payments FROM payment GROUP BY customer_id) AS p
  ON p.customer_id = c.customer_id
JOIN (SELECT customer_id, COUNT(*) AS rentals FROM rental GROUP BY customer_id) AS r
  ON r.customer_id = c.customer_id
ORDER BY c.last_name
LIMIT 5;

각 서브쿼리의 집계는 그것이 속한 행들에 대한 것입니다. 두 테이블을 먼저 조인하고 나중에 그룹화하면 결제 수에 대여 수가 곱해져 두 개수 모두 부풀려집니다. MySQL JOIN 성능과 흔한 실수가 이 곱셈 자체를 다룹니다.

행을 접고 싶지 않을 때

GROUP BY는 행들을 그룹당 1행으로 대체합니다. 집계를 각 행 옆에 두고 싶을 때, 윈도우 함수는 아무것도 접지 않고, 그리고 대개 임시 테이블 없이 그렇게 합니다:

SELECT customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM payment;

MySQL 8.4의 윈도우 함수가 이를 제대로 다룹니다.

문제 해결

문제	원인과 해결 방법
`GROUP BY`가 느림	그룹화 열로 시작하는 인덱스가 없습니다. `EXPLAIN`에서 `Using temporary`를 확인하세요.
느리고 디스크가 바쁨	임시 테이블이 메모리 예산을 넘어 디스크로 넘쳤습니다. 먼저 행을 좁힌 다음, 단일 테이블에는 `tmp_table_size`를, 서버 전체 합계에는 `temptable_max_ram`을 올리세요.
`ERROR 1055`	선택한 열이 그룹화되지도 집계되지도 않았습니다. 그룹화하거나, 집계하거나, 제거하세요.
`HAVING` 필터가 느림	집계가 없으므로 `WHERE`에 있어야 합니다.
합계가 너무 큼	일대다 조인이 그룹화 전에 행을 늘렸습니다.
`GROUP_CONCAT` 결과가 잘림	`group_concat_max_len`에 도달했으며, 이것은 오류가 아니라 경고를 냅니다.
그룹이 예상 밖의 순서로 돌아옴	`GROUP BY`는 순서를 보장하지 않습니다. `ORDER BY`를 추가하세요.

참고

MySQL 8.4의 윈도우 함수 — 행을 접지 않고 집계하기
MySQL JOIN 성능과 흔한 실수 — 일대다 조인이 합계를 부풀리는 이유
MySQL CTE가 파생 테이블보다 나은 경우 — 메인 쿼리 전에 미리 집계하기
흔한 MySQL 오류 — 맥락 속의 ERROR 1055와 ERROR 1111
GROUP BY — 절 자체를 먼저 보고 싶다면 튜토리얼

MySQL Tutorial

가이드

데이터 저장

쿼리 작성

데이터 보안

성능 최적화

AI로 구축

MySQL 운영

애플리케이션 연결

확장 작성