[MySQL] JOIN이 느릴 때 실행계획으로 튜닝하는 방법

MySQL에서 여러 테이블을 조인하는 쿼리는 데이터 규모가 커질수록 성능 이슈로 이어지기 쉽다. 특히 인덱스가 적절히 설정되지 않거나, 잘못된 조인 순서가 설정되면 실행 시간이 수 초 이상 소요될 수 있다.

🧱 예제 테이블 생성

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount INT,
  created_at DATETIME,
  INDEX idx_user_id (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

🐢 느린 JOIN 예제

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE YEAR(o.created_at) = 2023;

orders.created_at에 인덱스가 없음 → WHERE 조건을 처리하려면 전체 스캔이 발생함.

🔍 EXPLAIN으로 실행계획 확인

EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE YEAR(o.created_at) = 2023;

결과 예시:

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	u	ALL	NULL	NULL	1000
1	SIMPLE	o	ALL	idx_user_id	NULL	50000	Using where; Using join buffer

문제점

• users 테이블이 Full Table Scan
• orders 테이블도 Using where, Using join buffer가 발생해 인덱스 미활용

반응형

⚙️ 튜닝 1: WHERE 조건 컬럼에 인덱스 추가

CREATE INDEX idx_created_at ON orders(created_at);

하지만 YEAR(created_at)은 여전히 인덱스를 사용할 수 없음.

⚙️ 튜닝 2: WHERE 절에서 함수 제거

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at BETWEEN '2023-01-01' AND '2023-12-31';

이제 idx_created_at 인덱스가 사용됨.

⚙️ 튜닝 3: 조인 순서 변경 유도

MySQL은 기본적으로 작은 테이블부터 조인하려고 한다. 때로는 STRAIGHT_JOIN 힌트를 통해 조인 순서를 고정하면 성능이 향상될 수 있다.

SELECT u.name, o.amount
FROM orders o
STRAIGHT_JOIN users u ON o.user_id = u.id
WHERE o.created_at BETWEEN '2023-01-01' AND '2023-12-31';

반응형

📈 EXPLAIN 결과 비교

항목	BEFORE	AFTER
type	ALL + join buffer	ref + range
key	NULL	idx_user_id, idx_created_at
rows	50,000 이상	5,000 이하
Extra	Using join buffer	Using index

✅ 실무에서 자주 마주치는 JOIN 문제 원인

• 조인 키에 인덱스 없음
• WHERE 조건에 함수 사용
• 테이블의 조인 순서 비효율
• SELECT * 로 인해 커버링 인덱스 미적용
• LEFT JOIN인데 WHERE에서 NULL 제거로 INNER JOIN처럼 작동

🧠 정리

튜닝 포인트	설명
조인 조건에 인덱스	ON 조건의 컬럼에 반드시 인덱스 설정
WHERE 조건 최적화	함수 제거, 인덱스 사용 가능한 범위 지정
복합 인덱스 활용	조인 + 필터링 + 정렬 순서를 고려한 인덱스 구성
EXPLAIN 분석	key, type, Extra에 나타나는 힌트를 집중 분석

📎 참고 공식문서

MySQL Optimizing JOINs:
https://dev.mysql.com/doc/refman/8.0/en/join-optimization.html