MySQL(13)
이번 주 MySQL
9.3 고급최적화 내용.
9.3.1.9 세미조인
다른 테이블과 실제 조인하지 태의 쿼리. 않고, 조건에 일치하는 레코드가 있는지만 체크하는 형태의 쿼리
5.7 서버는 이 부분이 취약했다.
세미조인 최적화 도입 이전에 어떻게 처리했는지 보자.
SELECT *
FROM employees e
WHERE e.emp_no IN
(SELECT de.emp_no
FROM dept_emp de
WHERE de.from_date='1995-01-01')
일반적으로 서브쿼리가 실행되고, 그 다음 employees가 돌면서 일치하는 레코드만 검색할 것으로 기대된다.
그러나 employeess 테이블 풀스캔을 하면서 한건한건 서브쿼리 조건과 비교했다.
이 부분에서 상식적으로는 서브쿼리가 한번만 돌고, emp_no로 인덱스 서칭하면서 하면 57(employees)+57(employees) = 114번 정도로 됐을건데,
최적화가 잘 안되서 300363(employees 레코드 개수)를 읽으면서 하나하나 서브쿼리를 실행하고 그것과 비교한다는 말.
세미조인 형태의 쿼리, 안티 세미조인 쿼리는 최적화 방법이 차이가 있다. =(subQUery)와 IN(subquery) 형태의 세미 조인에 대해 3가지 최적화 방법이 있다.
- 세미 조인 최적화
- IN-to-EXISTS 최적화
- MATERIALIZATION 최적화
그리고 <>(subquery) 형태와 NOT IN (subquery) 형태의 안티 세미 조인 쿼리는 2가지 최적화 방법이 있다.
- IN-to-EXISTS 최적화
- MATERIALIZATION 최적화
여기선 세미 조인 최적화만 보겠다. 아래 5가지 전략들을 모아서 세미 조인 최적화 라고 한다.
- Table Pull-out
- Duplicate Weed-out
- First Match
- Loose Scan
- Materialization
쿼리에 사용되는 테이블과 조인 특성에 따라 옵티마이저는 전략을 선별적으로 사용한다.
Table pull out은 사용 가능하면 항상 세미조인보다 좋은 성능이므로 별도 옵티마이저 옵션이 없다.
9.3.1.10 테이블 풀-아웃, 세미조인 최적화 전략 1, 질문 1
세미조인의 서브쿼리를 아우터 쿼리로 끄집어내고 조인쿼리로 재작성 하는 최적화.
서브쿼리 최적화 도입 이전에 수동으로 튜닝하던 대표적인 방법
SELECT * FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no
FROM dept_emp de
WHERE de.dept_no='d009')
8.0에서 이거 실행계획을 보면 ID가 모두 1이다. 동일한 값을 가진다는 것은 두 테이블의 서브쿼리가 아니라 조인으로 처리했음을 의미한다.
이게 모든 형태의 서브쿼리에서 사용될 수 있는것은 아니다.
- TPO는 세미조인 서브쿼리에서만 사용가능하다.
-
TPO는 서브쿼리가 UNIQUE인덱스나 PK룩업 결과가 1건인 경우에만 사용가능하다.
- TPO가 된다 하더라도 기존 쿼리의 최적화 방법이 사용 불가능인건 아니므로 MySQL은 가능하면 TPO를 최대한 적용한다.
-
TPO는 서브쿼리 테이블을 아우터로 가져와서 Join으로 풀어쓰는 최적화를 하는데, 만약 서브쿼리 모든 테이블이 아우터로 끄집어 낼 수 있다면, 서브쿼리 자체는 없어진다.
최대한 서브쿼리를 조인으로 풀어서 사용해라라는 튜닝가이드가 많은데, tPO는 그냥 이 가이드를 실행하는거다.
테이블 풀 아웃이 적용된 쿼리
SELECT *
FROM employees e join dept_emp de on e.emp_no=de.emp_no
WHERE de.emp_no='d009'
질문 1 : TPO를 적용한 쿼리가 뭔가.
9.3.1.11 퍼스트 매치, 세미조인 최적화 전략 2
IN형태의 세미조인을 EXISTS 형태로 튜닝한 것과 비슷한 방법으로 실행된다.
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가 1이다.