search

mojh7 / real-mysql-study

:orange_book: Real MySQL 8.0 study
0 stars 0 forks source link

2023/01/17 ~ 2023/01/24 #38

Open mojh7 opened 1 year ago

mojh7 commented 1 year ago

다음 스터디

2023-01-24 pm 10:00 화요일

정리 범위

mojh7 commented 1 year ago

11.4.7.3 OUTER JOIN의 성능과 주의사항

1. inner join으로 사용해도 되는 쿼리를 outer join으로 작성한 경우

이너 조인은 조인 대상 테이블에 모두 존재하는 레코드만 결과 집합을 반환한다

이 같은 특성 때문에 아우터 조인으로만 조인을 실행하는 쿼리들이 자주 보인다

SELECT *
FROM employees e
    LEFT JOIN dept_emp de ON de.emp_no=e.emp_no
    LEFT JOIN departments d ON d.dept_no=de.dept_no AND d.dept_name='Development';

실행 계획을 보면 employee 테이블을 풀 스캔하면서 dept_emp 테이블과 departments 테이블을 드리븐 테이블로 사용한다

만약 employee 테이블에 존재하는 사원 중에서 dept_emp 테이블에 레코드를 갖지 않는 경우가 있다면 아우터 조인이 필요하다

MySQL 옵티마이저는 아우터로 조인되는 테이블을 드라이빙 테이블로 선택하지 못하기 때문에 풀 스캔이 필요한 employees 테이블을 드라이빙 테이블로 선택한다


89p 이너 조인을 이용했다면 departments 테이블에서 부서명이 "Development"인 레코드 1건만 찾아서 조인을 실행하는 실행 계획을 택했을 것

결론


2. outer로 join 되는 테이블에 대한 조건을 where 절에 명시한 경우

SELECT *
FROM employees e
    LEFT JOIN dept_manager mgr ON mgr.emp_no=e.emp_no
WHERE mgr.dept_no='d001';

ON 절에 조인 조건이 명시됐지만 아우터로 조인되는 테이블인 dept_manager 의 비교 조건이

WHERE 절에 명시됐기에 옵티마이저가 LEFT JOIN을 INNER JOIN으로 변환해서 실행해버린다

SELECT *
FROM employees e
    INNER JOIN dept_manager mgr ON mgr.emp_no=e.emp_no
WHERE mgr.dept_no='d001';

정상적으로 아우터 조인이 되게 하려면 WHERE 절에 조건을 LEFT JOIN의 ON 절로 옮겨야 한다

SELECT *
FROM employees e
    LEFT JOIN dept_manager mgr ON mgr.emp_no=e.emp_no AND mgr.dept_no='d001';


예외적으로 아우터 조인을 연결되는 테이블의 칼럼에 대한 조건을 WHERE 절에 사용해야 하는 경우가 있다

SELECT *
FROM employees e
    LEFT JOIN dept_manager dm ON dm.emp_no=e.emp_no
WHERE dm.dept_no IS NULL
LIMIT 10;

사원 중에서 매니저가 아닌 사용자들만 조회하는 쿼리이다

아우터로 조인된 dept_manager 테이블의 emp_no 칼럼이 NULL인 레코드들만 조회한다

이런 형태가 유일하게 사용할 수 있는 경우이고 그 외에는 MySQL 서버가 LEFT JOIN을 INNER JOIN으로 자동 변환한다


11.4.7.4 Join과 외래키 (Foreign Key)

외래키가 생성돼 있어야만 조인할 수 있는가?

외래키를 생성하는 주 목적


예시

부서 테이블, 사원 테이블이 존재할 때

사원 테이블의 부서 코드는 반드시 부서 테이블에 존재하는 부서 정보만 사용해야 한다

앱의 버그 등의 이유로 부서 테이블에는 존재하지 않은 부서 코드가 사원 테이블에 생긴다면?


SQL로 테이블 간의 조인을 수행할 때

데이터 모델링을 할 때는?

하지만 실제로 그 데이터 모델을 DB에 생성할 때는 외래키를 생성하지 않을 때가 많다

테이블 간의 조인을 사용하기 위해 외래키가 필요한 것은 아님


11.4.7.5 지연된 조인 (Delayed Join)

조인을 사용하여 데이터를 조회하는 쿼리에서 GROUP BY 또는 ORDER BY를 사용할 때 인덱스를 사용하고 있다면

하지만 인덱스를 사용하지 못하고 있다면?

조인은 대체로 실행되면 될수록 결과 레코드 건수가 늘어남


지연된 조인이란?

SELECT e.*
FROM salaries s, employees e
WHERE e.emp_no=s.emp_no
AND s.emp_no BETWEEN 10001 AND 13000
GROUP BY s.emp_no
ORDER BY SUM(s.salary) DESC
LIMIT 10;

employees 테이블을 드라이빙 테이블로 선택하고 emp_no BETWEEN 10001 AND 13000 조건을 만족하는 레코드 3000건을 읽어 salaries 테이블을 조인

조인된 결과 1.2만 건의 레코드를 임시 테이블에 저장하고 GROUP BY 처리를 통해 3000건으로 줄였고

ORDER BY를 처리해서 상위 10건만 최종적으로 반환한다


위에 내용을 지연된 조인으로 변경하면

SELECT e.*
FROM 
    (SELECT s.emp_no
    FROM salaries s
    WHERE s.emp_no BETWEEN 10001 AND 13000
    GROUP BY s.emp_no
    ORDER BY SUM(s.salary) DESC
    LIMIT 10) x,
    employee e
WHERE e.emp_no=x.emp_no;

salaries 테이블에서 가능한 모든 처리(WHERE, GROUP BY, ORDER BY, LIMIT)를 수행한 다음

그 결과를 임시 테이블에 저장하고 이 결과를 employees 테이블과 조인하도록 고침

FROM 절의 서브쿼리를 위해 28606건 읽어 임시테이블에 저장하고

GROUP BY를 통해 3000건

ORDER BY를 처리해 상위 10건만 임시 테이블 <derived2>에 저장함

최종적으로 임시 테이블 10건을 ㄱ읽어서 employees 테이블과 조인을 10번만 수행해서 결과를 반환한다


개선된 쿼리가 임시 테이블을 한 번 더 사용했기 때문에 느리다고 예상할 수도 있지만 실제로는 3~4배 정도 더 빠르게 실행됨


OUTER JOIN, INNER JOIN에 대해 다음 조건이 갖춰져야 지연된 쿼리로 변경해서 사용할 수 있다


주의

danbi5228 commented 1 year ago

11.4.7.6 래터럴 조인(Lateral Join)

11.4.7.7 실행 계획으로 인한 정렬 흐트러짐