JOIN 최적화

1. Nested Loop Join

=> 우선, JOIN을 최적화하기 위해서는 NLJ에 대해 알아야 한다.

=> Nested Left Join은 2개의 조인된 테이블이 있다고 가정했을 때,

=> 인덱스로 조인 조건을 필터링(?)해 더 빠르게 찾도록 최적화한다.

=> 아래 쿼리로 NLJ에 대해 조금 더 알아보자.

SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no;

1-1. CREATE INDEX index1 ON employees(emp_no) + CREATE INDEX index2 ON dept_emp(emp_no)

=> 두 테이블에 인덱스가 존재한다.

=> 이때는, 옵티마이저가 통계 정보를 이용해 적절히 드라이빙 테이블을 선택한다.

=> 사용자가 이때, 그리 최적화할 것이 많지 않겠다.

 

1-2. CREATE INDEX index1 ON dept_emp(emp_no)

=> 한 테이블에만 인덱스가 존재한다.

=> 이때는, 옵티마이저가 인덱스가 없는 쪽을 드라이빙 테이블(employees)로 정할 것이다.

=> 드라이빙 테이블(employees)이 N개의 레코드가 있고, 드리븐 테이블(dept_emp)이 M개의 레코드가 있다고 생각해보자.

=> 우선, 인덱스가 없었다면,

=> Loop로 O(N)을 수행한다.(드라이빙 테이블 풀 스캔)

=> 각 드라이빙 테이블 레코드 당 모든 드리븐 테이블 스캔(드리븐 테이블 풀 스캔)

=> 즉, O(N * M)이다.

=> 하지만, 인덱스를 사용한다면,

=> Loop로 O(N)을 수행한다.(드라이빙 테이블 풀 스캔)

=> 각, 드라이빙 테이블 레코드 당 한 번의 인덱스 SEEK(드리븐 테이블 인덱스 탐색)

=> 즉, O(N + M)이다. (다를 수 있지만, 이해 쉽게, 어쨋튼 NLJ에서 인덱스의 중요성이다.)

 

1-3. 두 테이블 모두 인덱스 없음

=> 아마, Hash Join을 수행할 것이다.

 

2. JOIN 안티 패턴

2-1. 조인 조건절을 다른 타입으로 비교하기

CREATE TABLE a1(user_id INT, user_type INT, PRIMARY KEY(user_id));

CREATE TABLE a2(user_type CHAR(1), type_desc VARCHAR(10), PRIMARY KEY(user_type));

SELECT * FROM a2, a1 WHERE a1.user_type=a2.user_type;

=> 이런 식의 SELECT 절은 찾아지긴 찾아진다.

=> 하지만, a2가 드리븐 테이블로 선택되어, 인덱스 NLJ로 인한 성능 향상을 원했다면, 도로묵인 쿼리이다.

=> a1의 user_type과 a2의 user_type은 타입이 다르다.

=> 때문에, 드리븐 테이블 탐색 시 인덱스를 변형시켜야 하지만, 인덱스를 변형 시키며 스캔은 MySQL에서 불가능하기에

=> 풀 테이블 스캔이 발생한다.(해시조인으로 탐색을 할 것이다.)

2-2. INNER JOIN을 OUTER JOIN으로 굳이 사용하기

=> INNER JOIN과 다르게, MySQL은 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';

=> 이 쿼리를 살펴보자.

=> departments 테이블에 dept_name으로 인덱스가 잡혀있는 상태이다.

=> OUTER 조인으로 조인을 하고 있다.

=> employees의 emp_no는 PK이기 때문에, 클러스터링 인덱스 탐색을 할 수 있기에, 드리븐 테이블로 선택되면 좋을 듯 하다.

=> departments의 dept_no는 PK이기 떄문에, 클러스터링 인덱스 탐색을 할 수 있기에, 드리븐 테이블로 선택되면 좋을 듯 하다.

=> 또는, d.dept_name='Development'와 같은 조건이 있기에, 먼저 수행 후 이것에 조인해주면 좋을 듯하다.

=> 이제, EXPLAIN을 써서 실행 계획을 보자.

=> employees 테이블이 드라이빙 테이블이 되어, 풀 스캔을 진행하고 있음을 볼 수 있다.

=> 여기서 알 수 있는 점은 OUTER JOIN에서 조인되는 테이블은 절대로 드라이빙 테이블이 될 수 없다는 것이다.

=> 즉, de를 드라이빙 테이블로 하여, 인덱스를 활용하는 것이 효율적이지만, 그렇게 못하고 있다.

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

=> 이제 INNER JOIN을 살펴보자.

=> 예상대로 d가 드라이빙 테이블로 선택되었다.

=> d.dept_name으로 1개의 행을 추려내고, NLJ를 돌며 de와 조인했다.

=> 그 후, de.emp_no를 통해 employees의 PK와 비교하며, 조인했다.

=> rows들만 봐도 INNER JOIN이 더 효율적으로 인덱스를 활용하고 있음을 볼 수 있다.

2-3. OUTER JOIN으로 조인되는 테이블에 WHERE 절을 사용하기

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

=> 이 쿼리를 살펴보자.

=> OUTER JOIN이다.

=> mgr은 JOIN되는 테이블인데, WHERE절을 사용했다.

=> 이때는 옵티마이저가 INNER JOIN으로 바꿔서 실행해버린다.

2-4. NLJ는 정렬, Hash Join은 정렬X

=> Hash Join을 사용하면서 정렬을 기대하면 안된다.

=> NLJ는 인덱스를 이용할 수 있는 JOIN이기에, 대부분의 경우 정렬을 보장해준다.

=> 하지만, Hash Join은 풀 테이블 탐색으로 이뤄지기 때문에, 정렬을 보장해줄 수 없다.

3. 지연된 조인

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;

=> e를 들고온다.

=> s에는 emp_no가 PK로 되어있다.

=> 이 쿼리의 목적은 s의 emp_no가 10001과 13000 사이인 것들을 salary 합으로 정렬해 10개만 가져오는 것이다.

=> 실행계획을 보자.

=> id가 둘 다 1이니, e가 먼저 실행되었을 것이다.

=> e는 동등 조인으로 s.emp_no BETWEEN 10001 AND 13000을 e.emp_no BETWEEN 10001 AND 13000으로 바꿔 실행했을 것이다.

=> 그 후, 조인된 임시 테이블을 s로 GROUP BY하고, ORDER BY했을 것이다.

=> 그후 LIMIT을 적용했을 것이다.

얼추 맞는 것 같다.

=> 이는, 조인 후 GROUP BY와 ORDER BY를 함으로 효율적이지 못한 쿼리이다.

=> 지연된 조인을 통한 효율 적인 쿼리로 바꿔보자.

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, employees e --드리븐 테이블이 된다.
WHERE e.emp_no=x.emp_no;

=> s로 진행된 서브쿼리 결과 emp_no에 대해 e를 조인했다.

=> 이러한 서브쿼리를 활용한 조인을 지연된 조인이라 한다.

=> 지연된 조인은

=> 서브 쿼리의 결과는 드라이빙 테이블이 된다.(즉, NLJ에서 풀 스캔 대상이 된다는 뜻이다.)

=> 원본(서브쿼리로 만들어진 테이블 X) 테이블은 드리븐 테이블이 된다.

=> 서브 쿼리의 결과는 작아야 한다.(크다면, 풀 스캔이 길 것이다.)

=> NLJ에서 원본 테이블에 접근할 시 PK 또는 유니크 인덱스를 사용해야 한다.(즉, 서브쿼리 결과 : 원본 테이블은 N:1   1 <= N이 되어야 함)

=> 당연한 말이지만, 드라이빙 테이블(서브쿼리 결과)의 값은 드리븐 테이블에 무조건 있어야 한다.

4. 래터럴 조인