MySQL JOIN 성능과 흔한 실수

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

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

JOIN은 처음에는 잘 동작하다가 나중에 잘못됩니다. 쿼리는 올바른 SQL이고, 서버는 아무 오류도 내지 않으며, 결과는 예상하지 못한 행 수이거나 40초가 걸리는 보고서입니다. 이 가이드는 그런 경우를 다룹니다: 무엇이 조인을 느리게 만드는지, 그리고 조인이 조용히 잘못된 행을 반환하는 4가지 방식입니다. 어떤 종류의 조인을 쓸지 아직 결정 중이라면, 튜토리얼이 샘플 데이터베이스를 대상으로 각각을 가르칩니다: 조인의 동작 방식, INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, 셀프 조인.

이 가이드에서 사용하는 테이블

CREATE TABLE customers (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,    -- nullable: orders without a customer
    amount      DECIMAL(10,2) NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES
    (1, 1, 99.00),   -- Alice's order
    (2, 1, 45.00),   -- Alice's second order
    (3, 2, 120.00),  -- Bob's order
    (4, NULL, 15.00); -- order with no customer

조인 열에 인덱스 만들기

조인하는 열에 인덱스가 없으면, 서버는 한쪽에 대해 해시 테이블을 만들고 다른 쪽을 스캔합니다. EXPLAIN FORMAT=TREE가 그것을 이름으로 보여줍니다:

EXPLAIN FORMAT=TREE SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id\G

*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (o.customer_id = c.id)  (cost=2 rows=3)
    -> Table scan on o  (cost=0.117 rows=4)
    -> Hash
        -> Table scan on c  (cost=0.55 rows=3)

해시 조인은 각 테이블을 한 번씩 읽습니다. 7개 행에서는 아무것도 아닙니다. 실제 테이블에서는 2번의 전체 테이블 스캔에 해시를 담을 메모리가 더해지고, 빌드 쪽이 join_buffer_size에 들어가지 않으면 서버는 그것을 배치 단위로 디스크에 흘려보냅니다. 인덱스를 추가하면 그 모든 것이 인덱스 조회로 바뀝니다:

CREATE INDEX idx_orders_customer ON orders (customer_id);

같은 플랜을 다시 요청하면 해시가 사라집니다:

EXPLAIN FORMAT=TREE SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id\G

*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=1.95 rows=4)
    -> Table scan on c  (cost=0.55 rows=3)
    -> Index lookup on o using idx_orders_customer (customer_id=c.id)  (cost=0.378 rows=1.33)

일대다 관계의 “다” 쪽에 있는 외래 키 열에 인덱스를 만드세요. “일” 쪽의 기본 키는 이미 인덱스가 있으므로, 그쪽은 아무것도 필요하지 않습니다. 기본 EXPLAIN 출력에서는 같은 2개의 플랜이 Extra: Using join buffer (hash join)과 type: ref로 읽힙니다. 그 표는 서버가 선택한 조인 순서도 보여주는데, 쿼리에 테이블이 3개 이상 있으면 곧바로 중요해집니다: 서버는 위에서 아래로 읽고, 첫 번째 테이블의 행 추정치가 그 아래 모든 것에 곱해집니다. EXPLAIN 출력 읽기가 플랜의 나머지를 다룹니다. 해시 조인이 조회할 수 있는 것은 동등 조건입니다. 대신 범위나 부등식으로 조인하면 서버는 여전히 해시를 만들지만 조회에 쓸 조건이 없습니다: 플랜은 Inner hash join (no condition)으로 읽히고 그 위에 Filter로 검사가 놓이므로, 모든 행 쌍이 만들어진 다음 버려집니다. 그것이 인덱스된 동등 조인이 피하는 비용입니다.

WHERE 절이 LEFT JOIN을 다시 INNER JOIN으로 바꿉니다

가장 흔한 잘못된 행 수 버그이고, 아무것도 경고하지 않습니다.

-- Excludes customers with no orders, so this is an inner join now
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 50;

-- Keeps customers with no orders
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 50;

조인이 먼저 실행되어 일치하지 않는 고객에게 NULL o.amount를 줍니다. 그다음 WHERE가 각 행을 검사하는데, NULL > 50은 참이 아니라 NULL이므로 그 행들은 버려집니다. 조건을 ON으로 옮기면 대신 그것이 일치로 간주되는 것의 일부가 됩니다. 규칙: 외부 조인에서 내부 테이블에 대한 조건은 ON에, 보존되는 테이블에 대한 조건은 WHERE에 속합니다. 예외는 WHERE <inner table key> IS NULL로, 일치한 행을 버리는 것이 목적인 경우입니다.

조인 열의 NULL은 아무것과도 일치하지 않습니다

orders.customer_id는 NULL 허용이고, 주문 4에는 고객이 없습니다. 내부 조인은 그것을 버리는데, NULL은 다른 NULL을 포함해 어떤 것과도 같지 않기 때문입니다.

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.amount;

+-------+--------+
| name  | amount |
+-------+--------+
| Alice |  45.00 |
| Alice |  99.00 |
| Bob   | 120.00 |
+-------+--------+
3 rows in set (0.00 sec)

4개의 주문에서 3개의 행입니다. Carol은 주문이 없어서 빠졌는데, 이것은 예상되는 부분입니다. 주문 4는 customer_id가 NULL이라서 빠졌는데, 이것이 사람들을 놀라게 하는 부분입니다: 행은 존재하고, 금액은 실제이며, 어떤 조인 조건도 그것에 닿을 수 없습니다. 그 조인 위에 만든 매출 합계는 15.00만큼 모자라고 아무것도 그렇다고 말해주지 않습니다. 조인 열이 NULL 허용이면, 조인을 쓰기 전에 고아 행이 어떻게 되어야 하는지 결정하세요. orders에서 시작하는 LEFT JOIN은 NULL 고객 이름과 함께 그 행을 유지합니다. MySQL의 NULL이 그 뒤에 있는 비교 규칙을 다룹니다.

일대다 조인은 행을 곱합니다

조인은 일치당 1개의 행을 반환하므로, 일치하는 행을 여러 개 가진 테이블에 조인하면 왼쪽 행이 일치마다 한 번씩 반복됩니다. 그 결과 위에서 계산한 합계는 틀리고, 보통 너무 크며, 쿼리는 여전히 올바르게 보입니다.

-- Alice appears twice, because she has 2 orders
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

중복 행을 버그로 취급하기 전에, 관계가 정말로 일대일인지 확인하세요. 일대다이고 고객당 1개의 행을 원한다면 집계하세요: 조인된 열에 대해 SUM이나 COUNT와 함께 GROUP BY c.id를 사용합니다. MySQL GROUP BY 성능을 보세요. 같은 산술이 한 쿼리에 2개의 일대다 조인이 있으면 더 빨리 잘못되는데, 2개의 일치 집합이 서로 곱해지기 때문입니다. 둘을 한 번에 조인하지 말고 각각을 자체 서브쿼리에서 집계하세요.

ON이 빠지면 모든 조합을 얻습니다

MySQL은 ON 절이 없는 INNER JOIN을 받아들이고 교차 조인으로 읽으므로, 오타가 오류 대신 두 테이블의 곱을 만들어냅니다. 10,000개 행의 테이블 2개는 1억 개의 행을 만듭니다. 조인이 의심스럽게 딱 떨어지고 의심스럽게 큰 숫자를 반환하면, 다른 무엇보다 먼저 빠진 ON을 확인하세요. FROM의 테이블 사이에 있는 쉼표는 옛 구문에서 같은 것이며, 일치 조건은 보통 더 아래 WHERE에 있습니다.

MySQL에는 FULL OUTER JOIN이 없습니다

MySQL에는 FULL OUTER JOIN이 없습니다. UNION으로 2개의 반쪽을 결합하세요:

SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.amount FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;

UNION은 중복 행을 제거하는데, 그것이 2개의 반쪽을 깔끔하게 이어 붙이는 것입니다. UNION ALL은 전체 외부 조인을 전혀 흉내 내지 못합니다: 일치한 모든 행을 각 반쪽에서 한 번씩, 두 번 반환합니다. 데이터에 유지해야 하는 중복 행이 있다면, UNION ALL에 손을 뻗는 대신 키 열을 기준으로 중복을 제거하세요.

JOIN 또는 서브쿼리

조인과 IN 서브쿼리는 종종 같은 필터를 표현하고, 옵티마이저가 어느 쪽을 선호하는지는 데이터에 따라 다릅니다. 관련 행이 존재하는지만 알면 되고 그 열은 하나도 필요 없을 때는 보통 EXISTS가 더 읽기 쉽고 서버가 첫 번째 일치에서 멈출 수 있게 합니다. MySQL의 서브쿼리 대 JOIN이 플랜과 함께 둘을 비교합니다.

문제 해결

문제	원인과 해결 방법
`LEFT JOIN`이 예상보다 적은 행을 반환	오른쪽 테이블에 대한 `WHERE` 조건이 NULL 행을 버리고 있음. `ON`으로 옮기기.
큰 테이블에 대한 조인이 느림	”다” 쪽의 조인 열에 인덱스가 없음. `EXPLAIN`에서 `type: ALL`을 확인.
예상하지 못한 중복 행	관계가 일대다임. `GROUP BY`로 집계하거나, 일대일이어야 하는지 확인.
합계가 너무 큼	같은 원인: 조인이 왼쪽 행을 반복해서 합계가 그것을 여러 번 셈.
조인이 거대하고 딱 떨어지는 행 수를 반환	`ON` 절이 빠져서 조인이 교차 조인임.
두 테이블 모두에 존재하는 행이 빠짐	한쪽의 조인 열이 NULL이어서 아무것도 그것과 일치하지 않음.
`RIGHT JOIN`이 따라가기 어려움	테이블을 바꿔서 `LEFT JOIN`으로 다시 쓰기. 결과는 같음.

함께 보기

EXPLAIN 출력 읽기 — 조인이 어떤 인덱스를 사용했는지 알려주는 플랜
MySQL의 외래 키 — 어떤 열에 인덱스를 만들지 결정하는 제약 조건
MySQL GROUP BY 성능 — 일대다 조인을 다시 1개의 행으로 집계하기
조인의 동작 방식 — 기초를 먼저 원한다면 튜토리얼

MySQL Tutorial

가이드

데이터 저장

쿼리 작성

데이터 보안

성능 최적화

AI로 구축

MySQL 운영

애플리케이션 연결

확장 작성