단축 실행 경로로 수행함. (서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행됨)
루스 스캔
루스 인덱스 스캔와 비슷한 방식으로 스캔함.
구체화
서브쿼리를 통째로 구체화 하여 최적화 함. → 내부 임시테이블을 생성함.
중복 제거
세미조인 서브쿼리 → INNER JOIN + 중복 제거 처리를 최적화함.
메모
세미조인 최적화 1-1
테이블 풀-아웃(Table Pull-out)
Table pullout 최적화는 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화임.
이는 서브쿼리 최적화 도입 전에 수동으로 쿼리를 튜닝하던 대표적인 방법이었음.
mysql> EXPLAIN
SELECT * FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');
+----+-------------+-------+--------+---------+--------+-------------+
| id | select_type | table | type | key | rows | Extra |
+----+-------------+-------+--------+---------+--------+-------------+
| 1 | PRIMARY | de | ref | PRIMARY | 46012 | Using index |
| 1 | SUBQUERY | e | eq_ref | PRIMARY | 1 | NULL |
+----+-------------+-------+--------+---------+--------+-------------+
위 조회 쿼리와 같이 IN(subquery) 형태의 세미 조인이 가장 빈번하게 사용되는 형태의 쿼리임.
실행계획이 dept_emp 테이블과 employees 테이블이 순서대로 표시되어있음.
가장 중요한 부분은 id 칼럼 값이 모두 1이라는 것임.
즉, 이 값이 동일하다는 건, 두 테이블이 서브쿼리 형태가 아니라 조인으로 처리됐음을 의미함.
Table pullout 최적화는 별도로 실행 계획의 Extra 칼럼에 “Using table pullout” 과 같은 문구가 출력되지 않는다.
그래서 Table pullout 최적화가 사용됐는지는 실행 계획에서 해당 테이블의 id 칼럼이 같은지 다른지 비교해 보는 것이 가장 간단한 방법임.
Table pullout 최적화가 사용됐는지 더 정확하게 확인하는 방법은 EXPLAIN 명령을 실행한 직후, SHOW WARNINGS 명령으로 MySQL 옵티마이저가 재작성(Re-Write)한 쿼리를 살펴볼 수 있음.
Table pullout 최적화는 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아님.
아래 Table pullout 최적화의 몇 가지 제한 사항 및 특성이 있다.
Table pullout 최적화는 세미 조인 서브쿼리에서만 사용 가능함.
Table pullout 최적화는 서브쿼리 부분이 Unique 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능함.
Table pullout이 적용된다고 하더라도 기존 쿼리에서 가능했던 최적화 방법이 사용 불가능한 것은 아니므로 MySQL 에서는 가능하다면 Table pullout 최적화를 최대한 적용함.
Table pullout 최적화는 서브쿼리의 테이블을 아우터 쿼리로 가져와서 조인으로 풀어쓰는 최적화를 수행함. 만약, 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면, 서브쿼리 자체는 없어짐.
MySQL에서는 “최대한 서브쿼리를 조인으로 풀어서 사용해라” 는 튜닝 가이드가 많음. Table pullout 최적화는 사실, 이 가이드를 그대로 실행하는 것임.
퍼스트 매치(firstmatch)
First Match 최적화 전략은 IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행됨.
mysql> EXPLAIN SELECT *
FROM employees e WHERE e.first_name='Matt'
AND e.emp_no IN (
SELECT t.emp_no FROM titles t
WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
);
+----+-------+------+--------------+------+-----------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+--------------+------+-----------------------------------------+
| 1 | t | ref | ix_firstname | 233 | NULL |
| 1 | e | ref | PRIMARY | 1 | Using where; Using index; FirstMatch(e) |
+----+-------+------+--------------+------+-----------------------------------------+
위 쿼리의 실행 계획에서, id 칼럼 값이 모두 1로 표시됨.
위 FirstMatch 최적화 예제에서, titles 테이블이 서브쿼리 패턴으로 실행되지 않고, 조인으로 처리됐다는 걸 알 수 있음.
Extra 칼럼에 “FirstMatch(e)” 문구가 출력됨.
employees 테이블의 레코드에 대해 titles 테이블에 일치하는 레코드 1건만 찾으면 더이상의 titles 테이블 검색을 하지 않음을 의미함.
즉, 실제 의미론적으로 EXISTS(subquery)와 동일하게 처리는 것임.
하지만, FirstMatch는 서브쿼리가 아니라, 조인으로 풀어서 실행하면서 일치하는 첫 번째 레코드만 검색하는 최적화를 실행한 것임.
employees 테이블에서 first_name 칼럼 값이 ‘Matt’인 사원 정보를 ix_firstname 인덱스를 이용하여 레인지 스캔으로 읽은 결과가 위 그림의 왼쪽 테이블인 employees 테이블임.
employees 테이블의 emp_no를 titles 과 조인하여 from_date의 조건에 일치하는지 확인하여 일치하면 더이상 titles 테이블을 검색하지고 바로 레코드를 반환함.
p340에 예제가 잘 설명되어 있음.
First Match 최적화는 MySQL 5.5에서 수행했던 최적화 방법인 IN-to-EXISTS 변환과 거의 비슷한 처리 로직을 수행함.
5.5의 IN-to-EXISTS 변환에 비해, First Match 최적화 전략은 다음과 같은 장점이 있음.
여러 테이블이 조인되는 경우, 원래 쿼리에 없던 동등 조건을 옵티마이저가 자동으로 추가하는 형태의 최적화가 실행되기도 함.
기존의 IN-to-EXISTS 최적화는 동등 조건 전파(Equality propagation)가 서브쿼리 내에서만 가능했음.
FirstMatch는 조인 형태로 처리하므로 서브쿼리뿐만 아니라 아우터 쿼리의 테이블까지 전파될 수 있음.
따라서, FirstMatch 최적화로 실행되면 더 많은 조건이 주어지는 것이므로 더 나은 실행 계획을 수립할 수 있음.
In-to-EXISTS 변환 최적화 전략에서는 아무런 조건 없이 변환이 가능한 경우에는 무조건 그 최적화를 수행했음.
FirstMatch 최적화에서는 서브쿼리의 모든 테이블에 대해 FirstMatch 최적화를 수행할지 아니면 일부 테이블에 대해서만 수행할지 취사선택할 수 있다는 것이 장점임.
FirstMatch 최적화 또한 특정 형태의 서브쿼리에서 자주 사용되는 최적화임.
아래 FirstMatch 최적화의 몇 가지 제한 사항 및 특성이 있다.
FIrstMatch는 서브쿼리에서 하나의 레코드만 검색되면 더이상 검색을 멈추는 단축 실행 경로(Short-cut path)이므로 FirstMatch 최적화에서 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행됨.
FirstMatch 최적화가 사용되면 실행 계획의 Extra 칼럼에 “FirstMatch(table-N)” 문구가 표시됨.
FirstMatch 최적화는 상관 서브쿼리(Correlated subquery)에서도 사용될 수 있음.
FirstMatch 최적화는 GROUP BY나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없음.
FirstMatch 최적화는 optimizer_switch 시스템 변수에서 semijoin 옵션과 firstmatch 옵션이 모두 ON으로 활성화된 경우에만 사용할 수 있다.
루스 스캔(loosescan)
루스 인덱스 스캔(Loose Index Scan)과 비슷한 읽기 방식을 사용함.
mysql> EXPLAIN
SELECT * FROM departments d WHERE d.dept_no IN (
SELECT de.dept_no FROM dept_emp de);
+----+-------+--------+---------+--------+------------------------+
| id | table | type | key | rows | Extra |
+----+-------+--------+---------+--------+------------------------+
| 1 | de | index | PRIMARY | 331143 | Using index; LooseScan | |
| 1 | e | eq_ref | PRIMARY | 1 | NULL |
+----+-------+--------+---------+--------+------------------------+
departments 테이블의 레코드 건수는 9건밖에 없음.
dept_emp 테이블의 레코드 건수는 무려 33만건 가까이 저장되어 있음.
dept_emp 테이블은 (dept_no, emp_no) 칼럼 조합으로 프라이머리 키 인덱스가 만들어져 있음.
dept_no로 그루핑하면 9건 밖에 없는 상황임.
이때, dept_emp 테이블의 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 아주 효율적으로 서브쿼리 부분을 실행할 수 있음. (중복된 레코드까지 제거하면서 가능함)
위 그림에서, 서브쿼리에 사용된 dept_emp 테이블이 드라이빙 테이블로 실행됨.
dept_emp 테이블의 프라이머리 키를 dept_no 부분에서 유니크하게 한 건씩만 읽고 있음을 보여줌.
루스 인덱스 스캔 의 “Using index for group-by”도 위 그림과 같이 dept_emp 테이블의 프라이머리 키를 읽는 방식과 동일하게 작용함.
위 쿼리의 실행 계획에서 Extra 칼럼에 “LooseScan” 문구가 표시됨.
실행계획 칼럼의 id 값이 동일하게 1인 것도, MySQL 내부적으로 조인처럼 처리됐음을 알려줌.
LooseScan 최적화는 다음과 같은 특성을 가짐.
LooseScan 최적화는 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행함. 그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는 최적화임.
