TIL

9.3(5) 컨디션 팬아웃과 파생 테이블 머지

컨디션 팬아웃(condition_fanout_filter)

• 조인을 할 때 테이블 순서는 쿼리 성능에 큰 영향을 미치기 때문에 MySQL 옵티마이저는 레코드 건수가 적은 순서대로 조인을 실행한다.
  • 조건에 일치하는 드라이빙 테이블 레코드가 1만 건이면 드리븐 테이블을 1만건 읽어야 한다.
• 컨디편 팬아웃 옵션 유무에 따라 아래 쿼리가 어떻게 달라지는지 살펴 보자.

SELECT * 
FROM employees e
	INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE e.first_name='Matt'
	AND e.hire_Date BETWEEN '1995-11-21' AND 1986-11-21';

condition_fanout_filter=off

id	table	type	key	rows	filtered	Extra
1	e	ref	ix_firstname	233	100.00	Using where
1	s	ref	PRIMARY	10	100.00	NULL

1. employees 테이블에서 ix_firstname 인덱스로 조건에 맞는 233건 레코드를 검색
2. 233건 중 hire_date 조건에 맞는 레코드를 필터링하는데 이 실행 계획에서는 filered 칼럼이 100인 것으로 보아 233건 모두 만족할 것으로 예측했다.
3. 233건에 대해 salaries 테이블의 프라이머리 키를 이용해 slalaries 테이블의 레코드를 읽는데 옵티마이저는 employees 테이블 레코드 한 건당 salaries 테이블 레코드 10건이 일치할 것으로 예상했다.

condition_fanout_filter=on

id	table	type	key	rows	filtered	Extra
1	e	ref	ix_firstname	233	23.20	Using where
1	s	ref	PRIMARY	10	100.00	NULL

• rows 칼럼은 233으로 동일하나 filtered가 23.2%로 변했다.
  • first_name으로 거른 결과인 233건에서 hire_date 조건까지 만족하는 값은 233건에서 23%라고 예측한 것
• condition_fanout_filter를 활성화하면 인덱스를 사용할 수 없는 조건에 대해서도 얼마나 조건을 충족할지를 고려하게 된다.
  • 정교한 계산을 거치기 때문에 실행 계획 수립에 더 많은 시간과 컴퓨팅 자원을 사용하게 된다.
  • MySQL 서버가 처리하는 쿼리 빈도 가 매우 높다면 이 오버헤드가 클 수 있으므로 성능 테스트를 하는 것도 방법이다.
• 이렇게 조건에 만족하는 레코드 건수를 정확히 예측 가능하다면 더 빠른 실행 계획을 만들 수 있다.

파생 테이블 머지(derived_merge)

• 예전 버전에서는 FROM절에 서브 쿼리가 있으면 먼저 실행해서 임시 테이블로 만든 다음 외부 쿼리를 처리했다.

SELECT * FROM (
	SELECT * FROM employees WHERE first_name='Matt'
) derived_table
WHERE derived_table.hire_date='1996-04-03';

id	select_type	table	type	key
1	PRIMARY		ref
2	DERIVED	employees	ref	ix_firstname

• employees 테이블을 읽는 select_type이 DERIVED인 것을 보아 조건에 맞는 임시 테이블을 생성했다고 볼 수 있다.
  • 이 임시 테이블을 파생 테이블 (Derived Table)이라고 부른다.
• 임시 테이블은 처음엔 메모리에 생성하지만 임시 테이블에 저장될 레코드 건수가 많아지면 결국 디스크에 저장된다.
  • 오버헤드와 성능 이슈로 이어질 것
• 5.7 버전부터 도입된 derived_merge 옵션을 활성화하면 실행 계획이 아래와 같이 변한다.

id	select_type	table	type	key
1	SIMPLE	employees	index_merge	ix_hiredate, ix_firstname

• 서브 쿼리를 외부 쿼리와 병합해서 서브 쿼리를 제거하는 최적화한다.
• 아래의 경우엔 서브 쿼리를 외부 쿼리로 병합할 수 없기에 수동으로 쿼리를 병합해서 작성하는 것이 좋다.
  • 집계 함수와 윈도우 함수가 사용된 서브 쿼리
  • DISTINCT가 사용된 서브 쿼리
  • GROUP BY나 HAVING이 사용된 서브 쿼리
  • LIMIT가 사용된 서브 쿼리
  • UNION 또는 UNION ALL을 포함하는 서브 쿼리
  • SELECT 절에 사용된 서브 쿼리
  • 값이 변경되는 사용자 변수가 사용된 서브 쿼리