들어가며
MySQL에서 JOIN UPDATE는 다른 테이블의 데이터를 참조하여 특정 테이블을 업데이트할 때 사용하는 강력한 기능이다. 그런데 여기에 GROUP BY를 사용하려 하면 갑자기 문법 오류가 발생한다. 왜 그럴까? 이번 글에서는 JOIN UPDATE에서 GROUP BY가 왜 안 되는지, 그리고 이 문제를 해결하기 위한 파생 테이블 , STRAIGHT_JOIN , JOIN_ORDER 힌트 , LATERAL 조인 까지 깊이 있게 다뤄본다. 각 방식의 실행 계획을 직접 비교하여 어떤 차이가 있는지도 확인해보자.
문제 상황: GROUP BY가 포함된 JOIN UPDATE
부서별 직원 수를 departments 테이블에 저장하고 싶다고 가정하자.
-- 먼저 emp_count 칼럼 추가
ALTER TABLE departments ADD emp_count INT;
UPDATE departments d, dept_emp de
SET d.emp_count = COUNT(*)
WHERE de.dept_no = d.dept_no
GROUP BY de.dept_no;하지만 이 쿼리는 다음과 같은 에러를 발생시킨다.
Error Code: 1064. You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY de.dept_no' at line 4
왜 안 될까?: JOIN UPDATE의 구조적 한계
JOIN UPDATE(다중 테이블 UPDATE)에서 GROUP BY와 ORDER BY, LIMIT은 사용할 수 없다. 이것은 MySQL 공식 문서에도 명시되어 있다.
"For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used."
그렇다면 왜 이런 제약이 있을까? 이해하려면 UPDATE 문의 동작 방식을 알아야 한다.
단일 테이블 UPDATE의 동작
UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = 1
ORDER BY hire_date
LIMIT 10;단일 테이블 UPDATE는 다음과 같이 동작한다.
- WHERE 조건에 맞는 레코드를 찾는다
- ORDER BY로 정렬한다
- LIMIT 개수만큼 업데이트한다
이 과정은 명확하고 예측 가능 하다. 어떤 레코드가 업데이트될지 정확히 알 수 있다.
다중 테이블 UPDATE의 동작
UPDATE employees e, departments d
SET e.dept_name = d.name
WHERE e.dept_id = d.id;다중 테이블 UPDATE는 조인 결과 를 기반으로 동작한다.
- 테이블들을 조인한다
- 조인 결과의 각 행에 대해 SET 절을 적용한다
여기서 문제가 발생한다. 조인 결과가 항상 일정하지 않다. 옵티마이저가 어떤 테이블을 먼저 읽느냐(드라이빙 테이블)에 따라 조인 순서와 중간 결과가 달라질 수 있다.
GROUP BY가 안 되는 이유
GROUP BY를 사용한 집계(COUNT, SUM 등)는 여러 행을 하나로 묶는 작업 이다. 그런데 UPDATE는 개별 행을 변경하는 작업 이다. 이 두 가지는 개념적으로 충돌한다.
- COUNT(*)는 그룹 단위로 계산된다
- 하지만 UPDATE는 행 단위로 실행된다
- 집계 결과를 "어떤 행에" 적용해야 하는지 모호해진다
JOIN UPDATE에서 GROUP BY, ORDER BY, LIMIT을 사용할 수 없는 것은 문법적 제약이 아니라, 논리적으로 모호한 상황을 방지 하기 위한 설계상의 결정이다.
드라이빙 테이블과 드리븐 테이블
해결책을 알아보기 전에, 조인 성능에 핵심적인 드라이빙/드리븐 테이블 개념을 이해해야 한다.
MySQL의 조인은 기본적으로 Nested Loop Join 방식으로 동작한다.
for each row in 드라이빙_테이블:
for each row in 드리븐_테이블 where 조인조건:
// 결과 출력드라이빙 테이블 (Driving Table)은 조인의 기준이 되는 외부 루프의 테이블이고, 드리븐 테이블 (Driven Table)은 내부 루프에서 검색되는 테이블이다.
───────────────────────────────────────────────────────────
Nested Loop Join
───────────────────────────────────────────────────────────
드라이빙 테이블 (Driving Table)
┌─────┐
│ A │ → 외부 루프: 각 행을 순차적으로 읽음
│ B │
│ C │ for each row in 드라이빙:
└─────┘ 드리븐 테이블에서 조인 조건 검색
│
▼
드리븐 테이블 (Driven Table)
┌─────────┐
│ A - 1 │
│ A - 2 │ ← 내부 루프: 인덱스로 빠르게 검색
│ B - 1 │
│ C - 1 │
│ C - 2 │
└─────────┘
───────────────────────────────────────────────────────────왜 드라이빙 테이블 선택이 중요한가?
- 드라이빙 테이블은 풀 스캔 또는 인덱스 레인지 스캔으로 읽힌다
- 드리븐 테이블은 드라이빙 테이블의 각 행마다 조인 조건으로 검색된다
- 드리븐 테이블에 적절한 인덱스가 있으면 빠르게 검색된다
일반적인 최적화 원칙
- 결과 행 수가 적은 테이블을 드라이빙으로 선택
- 드리븐 테이블의 조인 칼럼에는 인덱스가 있어야 함
- 파생 테이블(임시 테이블)은 인덱스가 없으므로 드라이빙으로 선택하는 것이 유리할 수 있음
이 쿼리를 다른 방식으로 작성하여 해결할 수 있다.
1. 서브쿼리를 활용한 파생 테이블
가장 기본적인 해결책은 서브쿼리로 미리 집계한 결과 를 파생 테이블(Derived Table)로 만들어 조인하는 것이다.
UPDATE departments d,
(SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
GROUP BY de.dept_no) dc -- 부서별 인원수 표를 만들어놓은 뒤 조인
SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no;이 쿼리의 실행 과정은 다음과 같다.
- 서브쿼리가 먼저 실행되어 부서별 직원 수를 계산한다
- 그 결과가 임시 테이블(파생 테이블
dc)로 생성된다 - departments와 파생 테이블을 조인한다
- 조인 결과에 따라 UPDATE를 수행한다
여기서 GROUP BY는 서브쿼리 내부에서 실행되므로 JOIN UPDATE의 제약에 걸리지 않는다.
2. STRAIGHT_JOIN으로 조인 순서 강제
옵티마이저가 선택한 조인 순서가 비효율적일 수 있다. 이때 STRAIGHT_JOIN 을 사용하면 조인 순서를 강제할 수 있다.
UPDATE (SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
GROUP BY de.dept_no) dc
STRAIGHT_JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;STRAIGHT_JOIN이란?
STRAIGHT_JOIN 은 MySQL 전용 키워드로, FROM 절에 명시된 순서대로 조인을 수행 하도록 강제한다.
-- 일반 조인: 옵티마이저가 순서 결정
SELECT * FROM A INNER JOIN B ON A.id = B.id;
-- STRAIGHT_JOIN: A → B 순서 강제
SELECT * FROM A STRAIGHT_JOIN B ON A.id = B.id;STRAIGHT_JOIN은 FROM 절에 명시된 모든 테이블 의 조인 순서를 고정한다. 일부만 고정하고 나머지는 옵티마이저에게 맡기는 것이 불가능하다.
3. JOIN_ORDER 옵티마이저 힌트
MySQL 8.0부터는 STRAIGHT_JOIN의 단점을 보완한 옵티마이저 힌트 가 추가되었다.
UPDATE /*+ JOIN_ORDER(dc, d) */
(SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
GROUP BY de.dept_no) dc
INNER JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;조인 순서 힌트 종류
| 힌트 | 설명 |
|---|---|
| JOIN_FIXED_ORDER | STRAIGHT_JOIN과 동일. FROM 절 순서대로 강제 |
| JOIN_ORDER(t1, t2, ...) | 지정한 순서대로 조인 권고 (강제는 아님) |
| JOIN_PREFIX(t1, t2) | 처음에 조인될 테이블 지정 |
| JOIN_SUFFIX(t1) | 마지막에 조인될 테이블 지정 |
STRAIGHT_JOIN vs JOIN_ORDER
-- STRAIGHT_JOIN: 키워드 방식, 모든 테이블 순서 고정
SELECT STRAIGHT_JOIN * FROM A, B, C, D WHERE ...;
-- JOIN_ORDER: 힌트 방식, 지정한 테이블만 순서 지정
SELECT /*+ JOIN_ORDER(A, C) */ * FROM A, B, C, D WHERE ...;
-- A와 C의 순서만 고정, B와 D는 옵티마이저가 결정JOIN_ORDER의 장점:
- 일부 테이블의 순서만 지정하고 나머지는 옵티마이저에게 위임 가능
- 힌트 문법이므로 무시되어도 쿼리 실행에 문제 없음
- 가독성이 더 좋음
옵티마이저 힌트는 권고 사항이다. 옵티마이저가 더 나은 실행 계획을 찾으면 힌트를 무시할 수도 있다. 반면 STRAIGHT_JOIN은 강제 사항이다.
4. LATERAL 조인 (MySQL 8.0.14+)
지금까지의 방법은 모두 서브쿼리를 먼저 실행하고 그 결과를 파생 테이블로 만드는 방식이었다. LATERAL 조인 은 완전히 다른 접근 방식이다.
UPDATE departments d
INNER JOIN LATERAL (
SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
WHERE de.dept_no = d.dept_no -- 외부 테이블 참조
) dc ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;LATERAL이란?
일반적인 파생 테이블(서브쿼리)은 독립적 으로 실행된다. 외부 쿼리의 테이블을 참조할 수 없다.
-- 이건 에러! 서브쿼리에서 외부의 d를 참조할 수 없음
SELECT *
FROM departments d,
(SELECT COUNT(*) AS cnt
FROM dept_emp de
WHERE de.dept_no = d.dept_no) sub; -- 에러 발생하지만 LATERAL 키워드를 붙이면, 서브쿼리가 외부 테이블의 각 행을 참조 할 수 있게 된다.
-- LATERAL을 붙이면 가능!
SELECT *
FROM departments d,
LATERAL (SELECT COUNT(*) AS cnt
FROM dept_emp de
WHERE de.dept_no = d.dept_no) sub;LATERAL의 동작 방식
LATERAL 서브쿼리는 for-each 루프 처럼 동작한다.
for each row in departments d:
execute (SELECT COUNT(*) FROM dept_emp WHERE dept_no = d.dept_no)
// 각 부서마다 서브쿼리 실행LATERAL은 "상관 서브쿼리(Correlated Subquery)의 조인 버전"이라고 이해하면 쉽다. 외부 테이블의 값을 참조하여 행마다 다른 결과를 생성할 수 있다.
실행 계획으로 비교하기
실제로 각 방식의 실행 계획을 비교해보자. 테스트 환경은 departments 9건, dept_emp 10,000건이다.
1. 기본 파생 테이블
EXPLAIN ANALYZE
UPDATE departments d,
(SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
GROUP BY de.dept_no) dc
SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no;-> Update d (immediate) (actual time=6.02..6.02 rows=0 loops=1)
-> Nested loop inner join (cost=9537 rows=93033) (actual time=6..6.01 rows=9 loops=1)
-> Table scan on d (cost=1.15 rows=9) (actual time=0.0236..0.0315 rows=9 loops=1)
-> Index lookup on dc using <auto_key0> (dept_no=d.dept_no) (cost=3107..3134 rows=103) (actual time=0.664..0.664 rows=1 loops=9)
-> Materialize (cost=3107..3107 rows=10337) (actual time=5.97..5.97 rows=9 loops=1)
-> Group aggregate: count(0) (cost=2073 rows=10337) (actual time=0.67..5.95 rows=9 loops=1)
-> Covering index scan on de using idx_dept_no (cost=1039 rows=10337) (actual time=0.0075..5.19 rows=10000 loops=1)이를 해석하면
-> Nested loop inner join
-> Table scan on d (rows=9) ← 드라이빙: departments
-> Index lookup on dc using <auto_key0> ← 드리븐: 파생 테이블 (자동 인덱스)
-> Materialize
-> Group aggregate: count(0)
-> Covering index scan on de using idx_dept_no (rows=10000)분석:
- 옵티마이저가 departments(9건)를 드라이빙 으로 선택
- 파생 테이블에
<auto_key0>자동 인덱스가 생성 됨 - 서브쿼리는 1번만 실행 되어 Materialize됨
2. STRAIGHT_JOIN / JOIN_ORDER
-- STRAIGHT_JOIN (파생 테이블 → departments 순서 강제)
EXPLAIN ANALYZE
UPDATE (SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
GROUP BY de.dept_no) dc
STRAIGHT_JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;
-- JOIN_ORDER 힌트 사용
EXPLAIN ANALYZE
UPDATE /*+ JOIN_ORDER(dc, d) */
(SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
GROUP BY de.dept_no) dc
INNER JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;STRAIGHT JOIN 의 실행계획은
-> Update d (buffered) (actual time=6.59..6.59 rows=0 loops=1)
-> Nested loop inner join (cost=6857 rows=10337) (actual time=6.57..6.59 rows=9 loops=1)
-> Table scan on dc (cost=3107..3239 rows=10337) (actual time=6.56..6.56 rows=9 loops=1)
-> Materialize (cost=3107..3107 rows=10337) (actual time=6.56..6.56 rows=9 loops=1)
-> Group aggregate: count(0) (cost=2073 rows=10337) (actual time=0.764..6.54 rows=9 loops=1)
-> Covering index scan on de using idx_dept_no (cost=1039 rows=10337) (actual time=0.0235..5.71 rows=10000 loops=1)
-> Single-row index lookup on d using PRIMARY (dept_no=dc.dept_no) (cost=0.25 rows=1) (actual time=0.00296..0.00297 rows=1 loops=9)다음과같고,
JOIN_ORDER 의 실행계획은
-> Update d (buffered) (actual time=6.52..6.52 rows=0 loops=1)
-> Nested loop inner join (cost=6857 rows=10337) (actual time=6.5..6.51 rows=9 loops=1)
-> Table scan on dc (cost=3107..3239 rows=10337) (actual time=6.48..6.48 rows=9 loops=1)
-> Materialize (cost=3107..3107 rows=10337) (actual time=6.48..6.48 rows=9 loops=1)
-> Group aggregate: count(0) (cost=2073 rows=10337) (actual time=0.687..6.47 rows=9 loops=1)
-> Covering index scan on de using idx_dept_no (cost=1039 rows=10337) (actual time=0.0236..5.65 rows=10000 loops=1)
-> Single-row index lookup on d using PRIMARY (dept_no=dc.dept_no) (cost=0.25 rows=1) (actual time=0.00303..0.00304 rows=1 loops=9)다음과 같다.
둘 다 실행계획이 거의 유사한데 이를 해석하면
-> Nested loop inner join
-> Table scan on dc ← 드라이빙: 파생 테이블
-> Materialize
-> Group aggregate: count(0)
-> Covering index scan on de using idx_dept_no (rows=10000)
-> Single-row index lookup on d using PRIMARY ← 드리븐: departments (PK 사용!)분석:
- 파생 테이블이 드라이빙 으로 변경됨
- departments에서 PRIMARY 인덱스 를 사용하여 검색
- 서브쿼리는 여전히 1번만 실행
3. LATERAL 조인
EXPLAIN ANALYZE
UPDATE departments d
INNER JOIN LATERAL (
SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
WHERE de.dept_no = d.dept_no
) dc ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;실행계획은 다음과 같고
-> Update d (immediate) (actual time=6.29..6.29 rows=0 loops=1)
-> Nested loop inner join (cost=7.1 rows=9) (actual time=0.744..6.29 rows=9 loops=1)
-> Invalidate materialized tables (row from d) (cost=1.15 rows=9) (actual time=0.0251..0.0337 rows=9 loops=1)
-> Table scan on d (cost=1.15 rows=9) (actual time=0.0249..0.033 rows=9 loops=1)
-> Index lookup on dc using <auto_key0> (dept_no=d.dept_no) (cost=0.842..1.13 rows=2) (actual time=0.694..0.694 rows=1 loops=9)
-> Materialize (invalidate on row from d) (cost=0.55..0.55 rows=1) (actual time=0.693..0.693 rows=1 loops=9)
-> Aggregate: count(0) (cost=0.45 rows=1) (actual time=0.691..0.691 rows=1 loops=9)
-> Covering index lookup on de using idx_dept_no (dept_no=d.dept_no) (cost=0.35 rows=1) (actual time=0.00472..0.658 rows=1111 loops=9)
이를 해석하면
-> Nested loop inner join
-> Invalidate materialized tables (row from d)
-> Table scan on d (rows=9) ← 드라이빙: departments
-> Index lookup on dc using <auto_key0>
-> Materialize (invalidate on row from d) ← ⚠️ 행마다 재실행!
-> Aggregate: count(0)
-> Covering index lookup on de (rows=1111, loops=9) ← loops=9!분석:
Invalidate materialized tables: 외부 행이 바뀔 때마다 서브쿼리 재실행loops=9: 서브쿼리가 9번 반복 실행 됨- 각 루프에서 약 1,111건씩 스캔
실행 계획 비교 요약
| 방식 | 드라이빙 | 드리븐 인덱스 | 서브쿼리 실행 | 특징 |
|---|---|---|---|---|
| 기본 파생 테이블 | departments | auto_key0 | 1번 | 옵티마이저가 결정 |
| STRAIGHT_JOIN | 파생 테이블 | PRIMARY | 1번 | PK 인덱스 활용 |
| JOIN_ORDER | 파생 테이블 | PRIMARY | 1번 | STRAIGHT_JOIN과 동일 |
| LATERAL | departments | auto_key0 | N번 (loops) | 행마다 재실행 |
핵심 차이점
────────────────────────────────────────────────────────────────
일반 파생 테이블 vs LATERAL 파생 테이블
────────────────────────────────────────────────────────────────
[일반 파생 테이블] - 1번 실행
1. 서브쿼리 독립 실행 (전체 데이터 집계)
┌───────────────────────┐
│ dept_no │ emp_count │
│ d001 │ 1,111 │
│ d002 │ 1,111 │
│ ... │ ... │
└───────────────────────┘
2. 결과를 departments와 조인
────────────────────────────────────────────────────────────────
[LATERAL 파생 테이블] - N번 실행 (loops)
departments의 각 행에 대해 서브쿼리 반복:
d001 → (SELECT COUNT(*) WHERE dept_no='d001') → 1,111
d002 → (SELECT COUNT(*) WHERE dept_no='d002') → 1,111
d003 → (SELECT COUNT(*) WHERE dept_no='d003') → 1,111
... → (9번 반복)
────────────────────────────────────────────────────────────────LATERAL 주의사항: 외부 테이블의 행 수만큼 서브쿼리가 반복 실행된다. 부서가 9개면 9번, 1,000개면 1,000번 실행된다. 전체 집계 후 조인하는 경우에는 일반 파생 테이블이 훨씬 효율적이다.
언제 어떤 방식을 사용할까?
일반 파생 테이블 (기본 선택)
- 전체 데이터를 집계한 후 조인하는 경우
- 대부분의 GROUP BY + JOIN UPDATE 상황
STRAIGHT_JOIN / JOIN_ORDER
- 옵티마이저가 비효율적인 조인 순서를 선택할 때
- 파생 테이블을 드라이빙으로 사용하고 싶을 때
- 드리븐 테이블에 좋은 인덱스(PK 등)가 있을 때
LATERAL
- 외부 테이블의 값에 따라 조건부 집계 가 필요할 때
- 각 행마다 TOP-N 을 조회해야 할 때
- 외부 테이블의 행 수가 적을 때
-- LATERAL이 유용한 예: 각 부서의 최근 입사자 1명
SELECT d.*, latest.*
FROM departments d
INNER JOIN LATERAL (
SELECT e.emp_no, e.hire_date
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE de.dept_no = d.dept_no
ORDER BY e.hire_date DESC
LIMIT 1 -- 각 부서마다 1명만!
) latest ON TRUE;마치며
JOIN UPDATE에서 GROUP BY를 사용할 수 없는 것은 단순한 문법 제약이 아니라, 집계와 행 단위 변경이라는 두 가지 상충되는 개념 을 조화시키기 어렵기 때문이다.
핵심 내용을 정리하면 다음과 같다.
문제의 원인:
- JOIN UPDATE는 행 단위 변경, GROUP BY는 그룹 단위 집계
- 집계 결과를 "어떤 행에" 적용할지 모호함
- MySQL은 이런 모호한 상황을 문법적으로 차단
해결 방법:
- 파생 테이블 : 서브쿼리에서 미리 집계 후 조인 (가장 일반적)
- STRAIGHT_JOIN : 조인 순서 강제 (FROM 절 순서대로)
- JOIN_ORDER 힌트 : 조인 순서를 힌트로 권고 (MySQL 8.0+)
- LATERAL 조인 : 행마다 서브쿼리 실행 (MySQL 8.0.14+)
성능 관점:
- 일반 파생 테이블은 서브쿼리를 1번 실행
- LATERAL은 외부 테이블 행 수만큼 N번 실행
- 전체 집계에는 파생 테이블, 행별 조건부 처리에는 LATERAL
실행 계획은 EXPLAIN ANALYZE 로 확인하고, 상황에 맞는 방식을 선택하자.
References
