[MySQL 튜닝] IN vs INNER JOIN vs EXISTS 성능비교

ㅁ 들어가며

 [MySQL 튜닝] EXISTS를 이용한 SQL 튜닝에서 EXISTS를 통한 튜닝작업을 알아보았습니다. 하지만 EXISTS가 항상 성능을 향상시키는 것은 아닙니다. EXISTS와  동일한 작업을 수행할 수 있는 IN, INNER JOIN도 있지만 성능에는 차이가 있을 수 있습니다. 이 번 글에서 질의 조건 3가지(전체, 부서, 부서 외)를 나누어 는  IN, INNER JOIN, EXISTS 성능을 비교해 보았습니다. 

 

 

ㅁ 일러두기

 ㅇ 쿼리 테스트를 위해 [MySQL] 실습환경 구성하기에서 생성한 MySQL이며, 버젼은 5.7.37입니다.

 ㅇ 테스트 데이터는 [MySQL] 대용량 샘플 데이터 사용하기에서 생성된 데이터를 사용하였습니다.

 ㅇ salaries 테이블: 2844047건

 ㅇ dept_emp 테이블: 221603건 

 

 

ㅁ 테스트 방법

 테스트를 위한 질의 조건은 부서 전체의 판매한 건수를 구하기 입니다.

질의 조건에 3가지(전체, 특정 부서, 특정 부서 외) 상황을 나누어 IN, INNER JOIN, EXISTS의 성능을 비교해 보았습니다.

 

 

---

1. 질의 조건: 전체

ㅇ IN

# 43.334
SELECT count(*)
FROM salaries s
WHERE s.emp_no IN (
    SELECT emp_no
    FROM dept_emp de
#     WHERE de.dept_no='d001'
);

ㅇ INNER JOIN

# 48.114
SELECT count(*)
FROM dept_emp de INNER JOIN salaries s
                            ON de.emp_no = s.emp_no
# WHERE de.dept_no='d001';

ㅇ EXISTS

# 22.957
SELECT count(*)
FROM salaries s
WHERE EXISTS (
    SELECT 1
    FROM dept_emp de
    WHERE de.emp_no = s.emp_no
#     AND de.dept_no='d001'
);

 

---

2. 질의 조건: 부서 

Marketing(d001) 부서에서 판매한 건수를 구하기

 

ㅇ IN

# 3.035
SELECT count(*)
FROM salaries s
WHERE s.emp_no IN (
    SELECT emp_no
    FROM dept_emp de
    WHERE de.dept_no='d001'
);

ㅇ INNER JOIN

# 3.604
SELECT count(*)
FROM dept_emp de INNER JOIN salaries s
    ON de.emp_no = s.emp_no
WHERE de.dept_no='d001';

ㅇ EXISTS

# 35.643
SELECT count(*)
FROM salaries s
WHERE EXISTS (
    SELECT 1
    FROM dept_emp de
    WHERE de.dept_no='d001'
    AND de.emp_no = s.emp_no
);

 

---

3 질의 조건: 부서 외

Marketing(d001) 부서에서 판매한 건수를 구하기

 

ㅇ IN

# 41.912
SELECT count(*)
FROM salaries s
WHERE s.emp_no IN (
    SELECT emp_no
    FROM dept_emp de
    WHERE de.dept_no != 'd001'
);

ㅇ INNER JOIN

# 45.441
SELECT count(*)
FROM dept_emp de INNER JOIN salaries s
     ON de.emp_no = s.emp_no
WHERE de.dept_no != 'd001';

ㅇ EXISTS

# 25.842
SELECT count(*)
FROM salaries s
WHERE EXISTS (
    SELECT 1
    FROM dept_emp de
    WHERE de.emp_no = s.emp_no
      AND de.dept_no !='d001'
);

 

ㅁ 실행 결과 비교

종류 / 초	전체	부서	부서 외
IN	43.334	3.035	41.912
INNER JOIN	48.114	3.604	45.441
EXISTS	22.957	35.643	25.842

 ㅇ 전체의 IN과 INNER JOIN, EXISTS은 dept_emp에 대해 Full Index scan이 이루어 졌다. 하지만 Exists의 경우 emp_no 일치하는 하나의 salaries 데이터만 확인 후 이후에는 색인을 하지 않기 때문에 시간이 23초 나왔습니다.

 

 ㅇ 특정부서를 조회 시 IN, INNER JOIN의 경우 MySQL 옵티마이저는 Nested Loops에서 부서로 필터된 데이터를 기준으로 색인이 이루어진다. 하지만 EXISTS는 오히려 전체 조회보다 더 오랜 시간이 걸렸다. 왜냐하면 EXISTS는 한번 True가 된 대상은 색인 대상에서 제외가 되지만, NL의 조건이 EXISTS의 서브쿼리의 대상보다 적을 경우 True로 색인되지 않는 데이터까지 색인을 하기 때문에 전체보다 불필요한 색인을 더 많이 하게 되는 것이다. 

 

 

ㅁ 함께 보면 좋은 사이트

 ㅇ [MySQL 튜닝] EXISTS를 이용한 SQL 튜닝