무럭무럭 STUDY - LEFT JOIN 주의사항 그리고 튜닝 (MySQL)

LEFT JOIN을 사용하였지만 INNER JOIN?

제목 그대로 left join을 사용하였지만 inner join이 동작한 것처럼 결과가 반환되는 경우가 있다. 예를 들어, user table과 coupon table이 있다고 가정해보자. 이때 user table과 coupon table은 하기와 같은 user_coupone table이란 매핑 테이블로 연관 지어져 있다.

create table user_coupone (
  user_id int not null,
  coupon_id int not null,
  ...
  
  primary key (user_id, coupon_id)
  key idx_coupon_id (coupon_id)
);

 

이때 user table의 데이터 건수는 약 10,000 rows이고 user_coupon table의 데이터 건수는 약 1,000 rows가 있고 user_coupon table에는 쿠폰 아이디 별(1, 2, 3, 4, 5)로 200 rows가 있다고 가정해보자. 그리고 하기와 같이 select 쿼리를 실행하면 10,000 건의 데이터가 반환된다. 이는 user table에 필터링하는 조건이 없고 left join을 사용하고 있기 때문에 왼쪽에 위치한 user table의 모든 데이터가 결과로 반환되기 때문이다.

-- 10,000 rows in set (... sec)
select u.id, u.name. uc.coupon_id, uc.use_yn
  from user u
  left join user_coupon uc on uc.user_id = u.id and uc.coupon_id = 1

 

하지만 하기와 같이 on 절 조건을 where로 변경하면 200 rows 건수만 반환되는 것을 확인할 수 있다. on 절에 조건이 주어졌을 땐 user table과 user_coupon table의 데이터를 연결하는 역할을 수행했던 반면 where일 경우에는 결과로 반환되는 데이터를 필터링하는 역할을 하기 때문에 inner join을 사용했을 때와 동일한 결과가 반환된 것이다.

-- 200 rows in set (... sec)
select u.id, u.name. uc.coupon_id, uc.use_yn
  from user u
  left join user_coupon uc on uc.user_id = u.id
 where uc.coupon_id = 1

tip)
실제로 위처럼 쿼리가 동작하게 되면 MySQL에서는 옵티마이저가 쿼리 최적화를 수행하면서 INNER JOIN으로 쿼리를 변경하게 된다.

 

LEFT JOIN과 INNER JOIN의 처리 방식

하기와 같이 left join의 경우 실행 계획을 보면 드라이빙 테이블인 user table을 항상 먼저 읽게 된다. 즉 드라이빙 테이블이 기준이 되기 때문에 항상 이 드라이빙 테이블이 먼저 읽히게 되고 where 절에 조건이 없기 때문에 user table에 대해 전체 테이블을 스캔하면서 user_coupon table과 left join을 수행하게 된다.

explain select u.id, u.name. uc.coupon_id, uc.use_yn
          from user u
     left join user_coupon uc on uc.user_id = u.id and uc.coupon_id = 1;

id	table	type	possible_keys	key	key_len	ref	rows	extra
1	u	all	null	null	null	null	29372	null
1	uc	eq_ref	primary, idx_coupon_id	primary	8	test.u.id, const	1	null

tip)
left join 왼쪽에 있는 테이블을 아우터 테이블 또는 드라이빙 테이블이라고 하고 오른쪽에 위치한 테이블을 이너 테이블 또는 라이븐 테이블이라 한다.

 

반면 하기와 같이 inner join의 경우 실행 계획을 보면 user_coupon table을 먼저 읽는다. 이는 특정 쿠폰 데이터에 대해서만 결과를 조회하는 것이므로 user table에 먼저 접근해 데이터를 읽기보단 효율적인 처리를 위해 user_coupon table의 coupon_id 인덱스를 사용해 1번 쿠폰 데이터를 먼저 읽고 그 다음에 user table과 조인하는 형태로 처리한다.

explain select u.id, u.name. uc.coupon_id, uc.use_yn
          from user u
    inner join user_coupon uc on uc.user_id = u.id and uc.coupon_id = 1;

id	table	type	possible_keys	key	key_len	ref	rows	extra
1	u	all	null	null	null	null	29372	null
1	uc	eq_ref	primary, idx_coupon_id	primary	8	test.u.id, const	1	null

tip)
Inner join은 조인에 참여하는 테이블의 교집합 데이터를 결과로 반환하는 것이므로 기준이 되는 드라이빙 테이블을 항상 먼저 읽는 left join 처럼 항상 고정되어 있는 것은 아니다. 즉 옵티마이저에 의해 최적화되면서 좀 더 효율적으로 처리될 수 있도록 테이블 접근 순서가 변경될 수 있다.

 

count(*)와 left join

조회 대상 데이터의 전체 데이터 건수를 확인하기 위해 하기와 같이 사용하는 경우가 있다고 가정해보자. 하지만 이런 경우 실제로 left join이 필요한가 생각해보면 그렇지 않다.

-- left join이 필요없는 경우
select count(*)
  from user u
  left join user_coupon uc on uc.user_id = u.id and uc.coupon_id = 1;
  
-- left join이 필요한 경우
select count(*)
  from user u
  left join user_coupon uc on uc.user_id = u.id
 where uc.user_id is null

left join 특성상 기준이 되는 왼쪽에 위치한 드라이빙 테이블에 대해서는 데이터를 모두 반환하게 되므로 사용자 별로 조인된 데이터가 여러 건이 아닌 이상 user table 데이터 건수와 동일한 데이터가 반환된다. 따라서 left join을 하던 안하던 결과 데이터 건수가 동일하므로 left join을 제거해서 더 빠르게 쿼리가 처리될 수 있다.

 

정리

• left join을 사용하고자 한다면 driven table(inner table) 컬럼의 조인 조건은 반드시 on 절에 명시해서 사용하자. 단 Is null 조건은 예외이다.
• left join과 inner join의 결과 데이터 및 쿼리 처리 방식 등이 매우 다르므로, 필요에 맞게 사용하자.
• left join 쿼리에서 count를 사용하는 경우 left join이 굳이 필요하지 않다면 left join을 제거하여 더 빠르게 처리하자.

 

---

Reference

Real MySQL - 이성욱님, 백은빈님