MySQL8 - Derived Condition Pushdown Optimization

Last Updated on 1월 27, 2024 by Jade(정현호)

안녕하세요
이번 포스팅은 MySQL 8 버전에서의 Derived Condition Pushdown Optimization 에 대해서 확인해보려고 합니다.

Contents

Derived Condition Pushdown Optimization
- Derived Condition Pushdown 사용 가능 조건
- Derived Condition Pushdown 사용 불가 조건
Reference

Derived Condition Pushdown Optimization

MySQL 8.0.22 버전 이상 부터는 적합한 서브 쿼리에 대한 Derived Condition Pushdown 기능을 지원합니다.

Derived Condition Pushdown 에 대한 내용은 아래 예시를 통해서 알아보도록 하겠습니다.

참고1) 포스팅에서 사용한 MySQL 버전은 8.0.25 와 8.0.31 버전입니다.
참고2) 포스팅에서 사용한 departments 샘플 테이블은 아래 github 을 통해서 다운로드 받을 수 있습니다.

GitHub - ofenloch/mysql-employees: MySQL Employee Sample Database

MySQL Employee Sample Database. Contribute to ofenloch/mysql-employees development by creating an account on GitHub.

참고 3) 테스트 과정에서는 derived condition pushdown 의 기능을 중점으로 확인하기 위해서 derived_merge는 OFF 설정 후 진행하였습니다.

SET session optimizer_switch='derived_merge=off';

• 테이블 데이터 정보

mysql> select * from departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d010    | Office Facility    |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+

다음 쿼리는 MySQL에서 파생(derived) 테이블(from 절의 서브쿼리 형태)이 있고, WHERE 절 조건이 외부에 있을 경우입니다.

select *
from (select dept_no,dept_name
from departments
group by dept_no) AS dt
where dept_no > 'd004';

위의 쿼리와 같은 많은 케이스에서 외부의 WHERE 조건을 파생(derived) 테이블로 밀어 넣을 수 있으며, MySQL 8.0.22 이상 버전에서 다음과 같이 변환되게 됩니다.
• 변환된 쿼리

 /* select#1 */
select `dt`.`dept_no` AS `dept_no`
     , `dt`.`dept_name` AS `dept_name`
  from( /* select#2 */select `test`.`departments`.`dept_no` AS `dept_no`
             , `test`.`departments`.`dept_name` AS `dept_name`
          from `test`.`departments`
         where(`test`.`departments`.`dept_no` > 'd004')
         group by `test`.`departments`.`dept_no`) `dt`

파생(derived) 테이블을 외부 쿼리로 병합(merged)할 수 없는 경우(예를 들어서 파생 테이블이 집계를 사용하는 경우, 위의 예시), 외부 WHERE 조건을 파생 테이블 안으로 밀어 넣으면 처리해야 하는 행 수가 줄어들어 쿼리의 더 빠른 실행을 기대할 수 있습니다.

Derived Condition Pushdown 사용 가능 조건

다음과 같은 상황에서 외부 WHERE 조건을 derived materialized(파생된 구체화된) 테이블로 밀어 넣을 수 있습니다.

1) 파생 테이블이 집계(aggregate) 또는 window functions를 사용하지 않는 경우 외부 WHERE 조건을 파생 테이블로 직접 밀어 넣을 수 있습니다. 여기에는 AND, OR 또는 둘 다와 결합된 여러 predicates 있는 WHERE 조건이 포함됩니다.

• SQL

select *
from (select *
from departments) AS dt
where dept_no > 'd004'
and dept_name<'Finance';

• 변환된 쿼리

explain
select *
from (select *
from departments) AS dt
where dept_no > 'd004'
and dept_name<'Finance';

+---+----------+-------------+-------+-------------------+----------+--------+----+--------+--------------------------+
| id| sel_type | table       | type  | possible_keys     | key      | key_len|rows|filtered| Extra                    |
+---+----------+-------------+-------+-------------------+----------+--------+----+--------+--------------------------+
| 1 | PRIMARY  | <derived2>  | ALL   | NULL              | NULL     | NULL   |  2 | 100.00 | NULL                     |
| 2 | DERIVED  | departments | range | PRIMARY,dept_name | dept_name| 162    |  2 |  66.67 | Using where; Using index |
+---+----------+-------------+-------+-------------------+----------+--------+----+--------+--------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message:  /* select#1 */
select `dt`.`dept_no` AS `dept_no`
     , `dt`.`dept_name` AS `dept_name`
  from( /* select#2 */select `test`.`departments`.`dept_no` AS `dept_no`
             , `test`.`departments`.`dept_name` AS `dept_name`
          from `test`.`departments`
         where((`test`.`departments`.`dept_no` > 'd004')
                   and(`test`.`departments`.`dept_name` < 'Finance'))) `dt`

2) 파생 테이블(derived table )에 GROUP BY가 있고 window functions를 사용하지 않는 경우 GROUP BY의 일부가 아닌 하나 이상의 열을 참조하는 외부 WHERE 조건을 HAVING 조건으로 파생 테이블로 밀어 넣을 수 있습니다.

• SQL

select *
from (select dept_no,count(dept_name) dept_name_cnt
from departments
group by dept_no) AS dt
where dept_name_cnt > 1;

• 변환된 쿼리

select `dt`.`dept_no` AS `dept_no`
     , `dt`.`dept_name_cnt` AS `dept_name_cnt`
  from( /* select#2 */select `departments`.`dept_no` AS `dept_no`
             , count(`departments`.`dept_name`) AS `dept_name_cnt`
          from `departments`
         group by `departments`.`dept_no`
        having(count(`departments`.`dept_name`) > 1)) `dt`

외부 조건이 파생 테이블(derived table ) 안으로 들어가서 HAVING 조건으로 사용되었습니다.

3) 파생 테이블(derived table)이 GROUP BY를 사용하고 외부 WHERE 조건의 열이 GROUP BY 열인 경우 해당 열을 참조하는 WHERE 조건을 파생 테이블로 직접 밀어 넣을 수 있습니다.

• SQL

select *
from (select dept_no,count(dept_name)
from departments
group by dept_no) AS dt
where dept_no > 'd004';

• 변환된 쿼리

select `dt`.`dept_no` AS `dept_no`
     , `dt`.`count(dept_name) ` AS `count(dept_name) `
  from( /* select#2 */select `departments`.`dept_no` AS `dept_no`
             , count(`departments`.`dept_name`) AS `count(dept_name) `
          from `departments`
         where(`departments`.`dept_no` > 'd004')
         group by `departments`.`dept_no`) `dt`

4) 외부 WHERE 조건에는 GROUP BY의 일부인 열을 참조하는 predicates와 GROUP BY 열을 참조하지 않는 predicates가 모두 있는 경우, 전자의 경우 predicates는 WHERE 조건으로 전달되고, 후자의 경우 predicates는 HAVING 조건으로 전달됩니다.

• SQL

select *
from (select dept_no,dept_name,count(dept_name)
from departments
group by dept_no,dept_no) AS dt
where dept_no > 'd004';

• 변환된 쿼리

select `dt`.`dept_no` AS `dept_no`
     , `dt`.`dept_name` AS `dept_name`
     , `dt`.`count(dept_name) ` AS `count(dept_name) `
  from( /* select#2 */select `departments`.`dept_no` AS `dept_no`
             , `departments`.`dept_name` AS `dept_name`
             , count(`departments`.`dept_name`) AS `count(dept_name) `
          from `departments`
         where(`departments`.`dept_no` > 'd004')
         group by `departments`.`dept_no`) `dt`

GROUP BY 열을 참조하지 않는 predicates 조건으로 사용될 경우 HAVING 으로 조건이 전달됩니다.

derived condition pushdown을 사용하려면 optimizer_switch 시스템 변수의 derived_condition_pushdown 플래그를 on으로 설정해야 합니다.

optimizer_switch에 의해 이 최적화가 비활성화된 경우 derive_condition_pushdown 최적화 SQL 힌트를 사용하여 특정 쿼리에 대해 derive_condition_pushdown 를 활성화할 수 있습니다.

특정 쿼리에 대해서 derived condition pushdown 최적화를 사용하지 않도록 하려면 no_derived_condition_pushdown 옵티마이저 힌트를 사용합니다.

또는 set_var 힌트를 사용해서 optimizer_switch에서 다음과 같이 제어할 수 있습니다.

/*+ set_var(optimizer_switch = 'derived_merge=on') */
/*+ set_var(optimizer_switch = 'derived_merge=off') */

Derived Condition Pushdown 사용 불가 조건

Derived Condition Pushdown Optimization에는 다음과 같은 제한 사항과 제한 사항이 적용됩니다.

1) 파생된 테이블에 UNION 이 포함된 경우에는 최적화를 사용할 수 없습니다. 다만 이 제한은 MySQL 8.0.29에서 해제되었습니다.

• SQL

select *
from (select dept_no,dept_name
from departments t1
union all
select dept_no,dept_name
from departments t2
) AS dt
where dept_no > 'd005';

• MySQL 8.0.25 버전
explain format=tree 으로 출력 결과

| -> Filter: (dt.dept_no > 'd005')  (cost=4.17..4.53 rows=6)
    -> Table scan on dt  (cost=0.15..2.73 rows=18)
        -> Union materialize  (cost=4.25..6.82 rows=18)
            -> Index scan on t1 using dept_name  (cost=1.15 rows=9)
            -> Index scan on t2 using dept_name  (cost=1.15 rows=9)

• MySQL 8.0.25 버전
변환된 쿼리

select `dt`.`dept_no` AS `dept_no`
     , `dt`.`dept_name` AS `dept_name`
  from( select `t1`.`dept_no` AS `dept_no`
             , `t1`.`dept_name` AS `dept_name`
          from `departments` `t1` union all
select `t2`.`dept_no` AS `dept_no`
             , `t2`.`dept_name` AS `dept_name`
          from `departments` `t2`) `dt`
 where(`dt`.`dept_no` > 'd005')

• MySQL 8.0.31 버전
explain format=tree 으로 출력 결과

| -> Table scan on dt  (cost=3.78..6.15 rows=10)
    -> Union all materialize  (cost=3.52..3.52 rows=10)
        -> Filter: (t1.dept_no > 'd005')  (cost=1.26 rows=5)
            -> Index range scan on t1 using PRIMARY over ('d005' < dept_no)  (cost=1.26 rows=5)
        -> Filter: (t2.dept_no > 'd005')  (cost=1.26 rows=5)
            -> Index range scan on t2 using PRIMARY over ('d005' < dept_no)  (cost=1.26 rows=5)

• MySQL 8.0.31 버전
변환된 쿼리

select `dt`.`dept_no` AS `dept_no`
     , `dt`.`dept_name` AS `dept_name`
  from( select `t1`.`dept_no` AS `dept_no`
             , `t1`.`dept_name` AS `dept_name`
          from `departments` `t1`
         where(`t1`.`dept_no` > 'd005') union all
select `t2`.`dept_no` AS `dept_no`
             , `t2`.`dept_name` AS `dept_name`
          from `departments` `t2`
         where(`t2`.`dept_no` > 'd005')) `dt`

EXPLAIN 출력 결과 및 변환된 쿼리에서 확인할 수 있듯이 조건절이 두 쿼리 블럭으로 모두 들어갈 수 있습니다.

이와 같이 MySQL 8.0.29 이상에서 Derived Condition Pushdown Optimization는 다음 예외를 제외하고 UNION 쿼리에 사용할 수 있습니다.

UNION의 일부인 구체화된 파생 테이블이 재귀 공통 테이블 식인 경우 UNION 쿼리에 조건 푸시다운을 사용할 수 없습니다.(재귀 공통 테이블 식 참조)
nondeterministic expressions이 포함된 조건은 파생 테이블로 밀어 넣을 수 없습니다.

MySQL 8.0.29 버전은 중대한 결함에 의해서 파일 다운로드는 불가능한 상태입니다. 관련된 정보는 다음 포스팅을 참조하시면 됩니다.

MySQL 8.0.29 INSTANT 컬럼 추가 삭제시 인스턴스 장애 발생 - Crash When INSTANT ADD/DROP columns - row versions permitted is 64

안녕하세요 이번 포스팅에서는 MySQL 8.0.29 버전으로 업그레이드 이후 INSTANT 방식으로 컬럼 추가/삭제 작업시 인스턴스 비정상 종료(장애) 에 대한 내용을 확인 해보려고 합니다.

2) 파생된 테이블에는 LIMIT 절을 사용할 수 없습니다.

3) 서브 쿼리가 포함된 조건은 푸시다운 할 수 없습니다.

4) 파생 테이블이 외부 조인의 내부 테이블인 경우에는 최적화를 사용할 수 없습니다.

5) 구체화된(materialized) 파생 테이블이 WITH (Common Table Expressions)인 경우 여러 번 참조되는 경우 조건이 해당 식으로 푸시되지 않습니다.

5) 조건이 뷰 자체가 아니라 ALGORITHM=TEMPTABLE을 사용하여 생성된 뷰의 테이블에 있는 쿼리의 경우 multiple equality이 인식되지 않으므로 조건을 푸시다운 할 수 없습니다.
Ref. mysql.com/doc/view-algorithms

6) MySQL 8.0.28부터 파생 테이블의 SELECT 목록에 사용자 변수에 대한 할당이 포함되어 있으면 조건을 푸시다운 할 수 없습니다. (Bug #104918에 의해 fix됨)

Reference

Reference URL
• mysql.com/derived-condition-pushdown-optimization

관련된 다른 글

MySQL8 - Semi Join 과 Anti Join - 안티조인 세미조인

안녕하세요 MySQL 8.0에서 SemiJoin 과 AntiJoin에 대해서 확인해보도록 하겠습니다.

MySQL Performance Schema - 성능 스키마

안녕하세요 이번 글에서는 MySQL 의 Performance Schema(성능 스키마)에 대해서 전반적으로 살펴보도록 하겠습니다. 해당 내용은 MySQL 성능최적화 책 과 MySQL Document 에 대해서 정리한 내용 입니다.

MySQL 과 Timestamp 그리고 Y2K38 이슈 - 2038년 이슈

안녕하세요 MySQL 의 Timestamp 날짜 데이터 타입에 대한 내용과 Unix Timestamp 그리고 Y2K38 이슈(2038년 이슈)에 관한 글을 핀다 회사 기술 블로그에 작성하였습니다.

MySQL - Derived Table 과 Lateral Derived Table - LATERAL JOIN

안녕하세요 이번 포스팅에서는 8.0 버전에서 추가된 Lateral Derived Table 기능에 대해서 살펴보도록 하겠습니다. Derived Table Derived Table는 FROM 절에 위치한 서브쿼리 내 SELECT 문이 반환하는 결과 집합이 하나의 테이블 역할

MySQL 8.0 - 상관 서브 쿼리의 쿼리 변환 1 - Transform correlated scalar subqueries

안녕하세요 이번 포스팅에서는 MySQL 서버 8.0.24 추가된 Transform correlated scalar subqueries 기능에 대해서 확인 해보려고 합니다. WL#13520 의 내용에 대한 확인/테스트 와 내용 번역으로 이루어져 있습니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io

bug.mysql.com

Statistics

MySQL8 - Derived Condition Pushdown Optimization

Derived Condition Pushdown Optimization

Derived Condition Pushdown 사용 가능 조건

Derived Condition Pushdown 사용 불가 조건

Reference

You may also like...

Derived Condition Pushdown Optimization

Derived Condition Pushdown 사용 가능 조건

Derived Condition Pushdown 사용 불가 조건

Reference

You may also like...

pt-online-schema-change 에서 plugin 을 이용한 단계별 일시중지 기능 사용

MySQL 데이터 압축 - Table Compress - Data Compress - 페이지 압축

MySQL - 파라미터 설정 비교 및 RDS Parameter Group 복제하기