[MySQL] WHERE IN vs. JOIN / JOIN 종류

Suhyeon Lee·2024년 10월 27일

WHERE IN vs. JOIN

MySQL where in (서브쿼리) vs 조인 조회 성능 비교 (5.5 vs 5.6)

MySQL 5.5 까지는 서브쿼리 최적화가 최악이라 웬만하면 Join으로 전환하자
- 메인테이블의 row 수 만큼 서브 쿼리를 수행한다
MySQL 5.6 에서 서브 쿼리가 대폭 최적화 되었다.
- 다만 최적화가 적용 안되는 조건들이 다수 존재한다
버전/조건 관계 없이 좋은 성능을 내려면 최대한 Join을 이용하자
Join을 사용하기가 너무 어렵다면 Subquery는 사용하되, MySQL 5.5 이하라면 절대 사용하지 않는다.
- 차라리 쿼리를 나눠서 2번 실행 (메인쿼리/서브쿼리)하고 애플리케이션에서 조립하는게 낫다.

MySQL의 서브쿼리 최적화가 적용되는 조건

IN(subquery) 또는 = ANY(subquery) 형태

UNION 없는 단일 SELECT

집계함수와 HAVING 절을 가지지 말아야 함

서브쿼리의 WHERE 조건이 외부쿼리의 다른 조건과 AND로 연결

조인을 사용한 UPDATE나 DELETE가 이니어야 함

미리 수립된 실행계획을 사용하지 않는 경우(PreparedStatement 사용 시 실행 계획 재사용됨)

외부쿼리와 서브쿼리가 실제 테이블 사용(가상 테이블 사용시 세미 조인(semi-join) 최적화 안됨)

외부쿼리와 서브쿼리가 straight_join 힌트 미사용

WHERE IN 보다는 JOIN 문의 성능이 더 좋다
- WHERE IN 절은 실행 계획에서 OR절로 변환되어 N번의 동등(=) 비교를 수행하기 때문에 성능이 나쁘다
MySQL 5.6버전부터는 실행 계획에서 IN절을 JOIN으로 변환하여 성능을 개선하였다
서브쿼리 존재 유무만 판별할 때는 IN 대신 EXISTS 문을 사용하여 성능을 개선할 수 있다
- 서브쿼리를 반복해서 실행하는 단점이 있다
- 그러나 서브쿼리 WHERE 조건에 인덱스가 걸려 있다면 IN보다 빠를 것으로 예상된다
  - IN: 동등 비교를 서브쿼리 결과 수만큼 수행, 반복
    vs EXISTS: 인덱스 타고 가서 데이터 존재 여부 판별을 반복
- 서브쿼리 결과 row 수가 많다면 EXISTS가 유리, 적다면 IN이 유리할 것으로 예상된다
  - 서브쿼리 결과 row 수가 증가하면 EXISTS의 hit rate는 증가하는 반면, IN에서는 비교해야 하는 row 수가 증가하기 때문이다
  - 인덱스의 시간 복잡도 O(logN)과 서브쿼리 결과로 나오는 row 수를 비교해야 할 것 같다.
  - 유저의 사용 패턴에 따라 결과가 달라질 것 같으니 실제 데이터를 기반으로 튜닝하는 것이 좋겠다.
EXPLAIN으로 실행 계획을 확인하고, 수행 시간을 측정해서 DB 요건에 따라 어떤 구문이 가장 효율적일 지 판단하자.

JOIN 종류

출처

내부 조인, 외부 조인, 동등 조인, 안티 조인, 셀프 조인, 세미 조인, 카타시안 조인(Catasian Product), ANSI 조인 → 8가지 방법
상대적으로 독립적인 개념은 아님
- 내부 조인의 상대 개념이 외부 조인
- 외부 조인을 제외한 셀프 조인, 안티 조인 등은 모두 내부 조인에 포함됨
- ANSI 조인은 7가지 조인을 모두 포함한 개념으로 ANSI SQL을 사용한 점만 다름
  - 따라서 일반적으로 ANSI 조인이라고 굳이 구분하지 않음

간단 정리

JOIN 연산자에 따른 구분
- 동등 조인
  - JOIN 연산자
  - ,로 구분하여 작성? → CROSS JOIN
- 안티 조인 ↔ 세미 조인
  - NOT IN, NOT EXIST 연산자
JOIN 대상에 따른 구분
- 셀프 조인
  - 하나의 테이블을 마치 다른 테이블인 것처럼 별도의 별칭을 부여하여 조인
JOIN 조건에 따른 구분
- 내부 조인
  - (INNER) JOIN {테이블} ON {컬럼}
  - 내부 조인을 할 테이블을 고르고 어떤 컬럼을 기준으로 할 것인지 ON에 설정
  - 동등 조인을 할 때 사용
  - ON에 적어주는 조건이 동등 조인에서 WHERE 조건절에 달아준 것과 동일함
- 외부 조인
  - 매칭되는 값이 없어도 NULL을 넣어서 반환
  - 왼쪽, 완전, 오른쪽 3가지 경우가 존재
- 세미 조인
  - 자연조인을 진행한 결과에 대하여 한쪽 테이블만 반환
    e.g.
    주문 내역이 있는 고객만 조회
- 카타시안 조인(ORACLE) = CROSS JOIN(ANSI)
  - WHERE절에서 공통 속성에 대해 조건을 걸어주지 않으면 다대다 조인, 즉 카티전 프로덕트가 됨
  - Cartesian product(데카르트 곱)인 집합 A와 B를 곱한 집합을 반환
  - 모든 가능한 행들의 조합을 결과로 반환
  - 반드시 필요한 경우가 아니면 사용하지 말 것
기타
- ANSI 조인

EQUI-JOIN(동등 조인)

가장 기본이며 일반적인 조인 방법
WHERE 절에서 등호(=) 연산자를 사용해 2개 이상의 테이블이나 뷰를 연결한 조인
- 등호 연산자를 사용한 WHERE절 조건에 만족하는 데이터를 추출
- 이때 WHERE절에 기술한 조건을 조인 조건이라고 함
조인 조건은 컬럼 단위로 기술
e.g.
A와 B 테이블이 있다고 한다면 두 테이블에서 공통된 값을 가진 컬럼을 등호 연산자로 연결해 조인 조건에 일치하는 조인 조건 결과가 참에 해당되는 두 컬럼 값의 같은 행을 추출

CROSS JOIN = CARTESIAN JOIN

집합 A의 레코드의 개수와 집합 B의 레코드의 개수의 곱만큼 결과값이 반환 → 경우의 수라고 생각하면 이해하기 쉽다!
합집합인 UNION과는 완전 다름
inner join, left join, right join의 경우와 달리 모든 경우의 수를 도출하므로 연결 조건인 on 절이 없고, where로 검색결과의 조건을 제한
, 쓰면 CROSS JOIN임
MySQL에서는 cross join = inner join = join 이다. CROSS JOIN에 ON(or USING)을 같이 쓰면 inner join으로 동작한다.

NATURAL JOIN(자연 조인)

동등 조인을 진행하는데 중복되는 모든 컬럼은 제거해 보여줌
NATURAL JOIN

예제

사원과 부서 테이블에 공통적으로 존재하는 부서번호(department_id)를 등호 연산자를 사용해 조회조건에 명시
부서번호 컬럼은 부서 테이블에서 키(Primary Key)에 해당해 필수 값이지만 사원 테이블에서는 필수 값이 아니므로 쿼리 결과는 사원 테이블에서 부서번호 컬럼 값이 있는 건만 추출됨
- 사원 테이블의 전체 건수가 107건이지만 부서번호가 없는 사원이 한 건 존재하므로 106건이 조회됨