Derived Table 의 정의
1. 쿼리의 from 절에 서브쿼리로 만들어진 임시 테이블
2. 쿼리 실행 시 내부 작업으로 인해 임시적으로 생성되는 서브쿼리
==> Subquery in From Clause
3. View == Named Derived Table
MySQL Ver. 5.6 까지의 처리 방식
1. View : Outer Query 와 병합되어 처리됨.
2. Dervied Table : Outer Query와 병합되지 못하고 Temporary Table과 같이 나누어서 처리됨.
2번 방식의 문제점
1. materialized Temporary Table을 따로 만들어야 하므로 시간과 비용이 많이 든다.
2. Outer Query와 Derived Table사이에 미리 조건을 공유하여 필터링할 수 있는 push down condition이 거의 불가능하다.
MySQL Ver. 5.7의 변경된 처리 방식
1. 쿼리 실행 중에 Resolve 과정에서 Outer Table 과 Derived Table을 병합할지 구체화 할지를 결정함.
2. 다음과 같은 경우에는 병합되어 처리되지 않고 기존 방식으로 처리된다.
---- UNION 절
---- Group By 절
---- DISTINCT 사용 시
---- Aggregation
---- LIMIT or OFFSET 사용 시
---- 사용자가 생성한 변수가 포함된 경우
실행계획 예제
다음의 쿼리를 각각 MySQL Ver. 5.6 MySQL Ver. 5.7에서 실행해 보도록 하자.
explain select * from (select emp_no, first_name from employees) as em join dept_emp using (emp_no);
MySQL Ver. 5.6에서는 다음과 같이 실행계획이 보여진다.
+----+-------------+------------+------+---------------+-------------+---------+---------------------------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+-------------+---------+---------------------------+--------+-------+
| 1 | PRIMARY | dept_emp | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | employees.dept_emp.emp_no | 2996 | NULL |
| 2 | DERIVED | employees | ALL | NULL | NULL | NULL | NULL | 299689 | NULL |
+----+-------------+------------+------+---------------+-------------+---------+---------------------------+--------+-------+
3 rows in set (0.00 sec)
MySQL Ver. 5.7에서는 다음과 같이 실행계획이 보여진다.
+----+-------------+-----------+------------+------+---------------+---------+---------+----------------------------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------+---------+----------------------------+--------+----------+-------+
| 1 | SIMPLE | employees | NULL | ALL | PRIMARY | NULL | NULL | NULL | 299423 | 100.00 | NULL |
| 1 | SIMPLE | dept_emp | NULL | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+---------+---------+----------------------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
[root@my57-db1][employees 18:38:41] > show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,`employees`.`employees`.`first_name` AS `first_name`,`employees`.`dept_emp`.`dept_no` AS `dept_no`,`employees`.`dept_emp`.`from_date` AS `from_date`,`employees`.`dept_emp`.`to_date` AS `to_date` from `employees`.`employees` join `employees`.`dept_emp` where (`employees`.`dept_emp`.`emp_no` = `employees`.`employees`.`emp_no`) |
즉, MySQL Ver. 5.7에서는 병합되어 한꺼번에 처리된 것을 확인할 수 있다.
View에서 동작 방식 선택하기.
MySQL Ver. 5.6 부터 View를 생성할 때 ALGORITHM 값을 선택하여 쿼리 실행을 할 때 어떤 식으로 처리할 지 결정할 수 있다.
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
3가지 옵션 중 하나를 선택할 수 있다.
-- UNDEFINED : 기본값으로 MySQL 이 어떤 알고리즘을 선택할지 결정하게 한다는 것이다.
-- MERGE : Outer query와 View를 병합한 후, 데이터를 추출한다는 것이다.
-- TEMPTABLE : Outer query와 병합하지 않고 임시 테이블을 만들어서 데이터를 추출하는 알고리즘을 사용한다는 것이다.
Derived Table에서의 동작 방식
Derived Table은 View와는 다르게 쿼리 작성 시 어떤 알고리즘을 사용할지 선택할 수 없다. 즉, 쿼리 단위로는 알고리즘을 선택할 수 없다는 것이다. 그래서, 다른 방식으로 지원하는데, 바로 옵티마이져에 어떤 식으로 동작하게 할 것인지 지정하는 방식을 사용한다. 즉, Derived Table을 병합으로 처리하고 싶으면 다음과 같이 설정하면 된다.
optimizer_switch='derived_merge=on'
또는
optimizer_switch='derived_merge=off'
이 시스템 변수의 값은 Derived Table과 ALGORITHM 값이 UNDEFINED로 설정된 View에 모두 적용된다.
Dervied Table의 성능 개선
MySQL Ver. 5.7 부터는 View와 Derived Table의 효율적인 쿼리 분석을 위해 다음과 같은 기능이 개선되었다.
먼저 쿼리 분석 단계에서의 기능 개선 작업이 이루어졌는데, 쿼리 분석인 analyze, optimize, execute 순서안에서 analyze 중 resolver라는 작업에 대한 성능 개선이 이루어 졌다. 이전 보다 좀 더 자연스럽게 resolver 작업이 진행될 수 있게 기능 개선이 되었다. (resolver 작업은 딕셔너리에서 테이블과 컬럼 정보를 찾꼬, 쿼리가 정확한지 체크하는 일련의 과정을 의미한다. )
두번째로는 컬럼에 대한 권한 체크 부분에 대한 기능이 개선되었다. 이전에는 각 컬럼별 권한 정보를 갖는 객체를 만들어서 처리했는데, MySQL Ver. 5.7 부터는 함수로 처리하게 변경하여 불필요한 객체 정보를 만들지 않아도 되게 수정하였다.
세번째로 분석 작업 후 데이터를 추출하기 위한 컬럼들을 정의할때, 사용하지 않은 컬럼을 제외할 수 있게 컬럼 필터링 기능이 개선되었다. 이전에는 각 Dervied Table별로 읽기용과 쓰기용 컬럼 셋을 만들었다. 이 작업은 많은 시간이 소요되는 작업이었다. 이제는 컬럼 샛을 만들기 전에 미리 알고 있는 Dervied Table에서 사용하는 컬럼 목록과 Outer Query에서 사용하는 컬럼 목록을 가지고 사용하지 않은 컬럼의 목록을 스토리지 엔진에서 데이터 추출을 할때 제외시킬 수 있게 되었다.