6장 실행계획 (6.3.6 테이블 조인)

6.3.6 테이블 조인

INNER JOIN(일반적으로 부르는 JOIN) : 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로, 옵티마이저가 조인의 순서를 결정해서 최적화 할 수 있다.

 SELECT * FROM emp e, sal s WHERE e.emp_no = s.emp_np;

 SELECT * FROM emp e INNER JOIN sal s ON s.emp_no = e.emp_np;

 SELECT * FROM emp e INNER JOIN sal s USING (emp_no) 

 문법은 다르지만, 전부 같은 쿼리이다. 

OUTER JOIN : 반드시 OUTER가 되는 테이블을 먼저 읽어야 해서 최적화 할 수 없다. (어느 테이블을 먼저 읽느냐는 조인에서 아주 중요한 요소이다)

 outer table은 반드시 먼저 읽어야 하며, 주도적인 역할을 한다고 해서 driving table이라고 한다. inner table은 driven table이라고도 한다.

 INNER JOIN pseudo - IF처럼 매칭되는것만 조인의 결과로 가져온다.

 FOR ( r1 in TABLE1)

    FOR ( r2 in TABLE2 ){

      IF(r1.c == r2.c) {

 found(r1.* , r2.*);

      }else{

          not_found();

     ...

OUTTER joint pseudo  - INNER JOIN과 달리 IF의 매칭이 안되더라도 OUTER TABLE의 결과를 가져온다.

 FOR ( r1 in TABLE1)

    FOR ( r2 in TABLE2 ){

      IF(r1.c == r2.c) {

 found(r1.* , r2.*);

      }else{

          found(r1.*, NULL)

     ...

* inner 테이블의 조인의 결과에 전혀 영향을 미치지 않고, outer에 따라 결과가 결정된다. (결과는 최소한 outer 의 갯수보다 같거나 크다)

* left outer join : SELECT * FROM employees e LEFT OUTER JOIN salaries s ON s.emp_np = e.emp_no;

  (outer : employees)

* right outer join : SELECT * FROM salaries s  RIGHT OUTER JOIN employees e ON s.emp_np = e.emp_no;

  (outer : employees)

* 둘은 같은 결과를 낸다. 그래서 혼동을 막기 위해 보통 LEFT OUTER JOIN으로 통일해서 사용하는것이 일반적

* 유의사항

 - 실행계획은 inner join인지 outer join인지 알려주지 않는다. 

 - 최적화 계획에 의해 outer -> inner join으로 실행 될 수 있으므로 '반드시 ON 절에 사용되는 조건을 명시적으로 작성하는 것'이 좋다.

   SELECT  * FROM employees e LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no WHERE s.salary > 5000

   ===> (위를 실행하면 최적화에 의해 inner join이 되므로 아래처럼 명시적으로 작성)

   SELECT  * FROM employees e LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no AND s.salary > 5000

* LEFT OUTER JOIN 이 아닌 쿼리는 검색조건,조인조건을 WHERE / ON 어느곳에 명시해도 결과&성능 차이가 나지 않는다.

카테시안 조인 (FULL JOIN , CROSS JOIN)

- 조건 자체가 없이, 2개의 테이블의 모든 조합을 결과로 가져온다. (MySQL을 응답 불능 상태로 만들 수 있다)

- 한건만 조회하는 여러 쿼리를 한번에 실행하기 위해 사용하기 도 한다

  SELECT * FROM de WHERE dept_no = 'd0001';

  SELECT * FROM emp WHERE emp_no = '1000001'; 

  ==>

  SELECT d.*, e.* from dep d , emp e WHERE dept_no = 'd0001' AND emp_no= 1000001;

  (조인 조건이 없는것을 확인 할 수 있다, 그리고 사용에 주의를 해야한다)

NATURAL JOIN

  SELECT * FROM emp e NATURAL JOIN sal s;

  emp와, sal테이블에 존재하는 칼럼중에서 이름이 같은 칼럼을 모두 조인 조건으로 사용한다!

  조인 조건을 명시 안해도 되는 편리함이 있지만, 칼럼명이 바뀔 이슈가 없는지 꼭 확인을 해야한다.

  이런 방식이 있다는 것 정도로 알고 , 사용은 안해도 될듯하다.

*** 조인에서 중요한 2가지 ***

1. 실행 결과의 정렬 순서

  

2. INNER, OUTER 조인의 선택

 실제로 가져와야하는 레코드가 같다면 성능의 차이는 거의 없다. 용도가 다르므로 적절한 사용을 하는것이 더 중요하다.

6.4. 실행 계획 분석 시 주의사항

6.4.1 Select_type

  DERIVED : FROM절에 사용된 서브쿼리로부터 발생한 '임시테이블'을 뜻한다. (만약 디스크에 저장된다면 성능 저하)

  UNCACHEABLE SUBQUERY : FROM절 이외에서 사용되는 서브 쿼리는 가능한 MySQL이 재사용할 수 있게 유도하지만, 없을때 이렇게 노출

  DEPENDENT SUBQUERY : 외부쿼리에서 값을 받아올 경우, 이 쿼리는 먼저 실행되지 못하고 의존적이라 전체 성능 저하. (제거하는 것이 좋다)

6.4.2 Type

 index:인덱스 풀 스캔, ALL: 풀 테이블 스캔

6.4.5 Extra 

  쿼리 실행 계획이 좋은 경우

    Distinct

    Using index

    Using index for group by

   쿼리 실행 계획이 좋지 않은 경우

    Using filesort

    Using join buffer

    Using temporary

    등이 나오면 좀 더 최적화 할 수 없는지 검토

   쿼리를 검수해봐야 하는 경우

    Full scan on NULL key

    Unique row not found 

   쿼리가 요구사항을 제대로 반영했는지 , 버그가 없는지 확인해야한다.