Last Updated on 6월 12, 2023 by Jade(정현호)
안녕하세요
이번 포스팅에서는 8.0 버전에서 추가된 Lateral Derived Table 기능에 대해서 살펴보도록 하겠습니다.
Derived Table
Derived Table는 FROM 절에 위치한 서브쿼리 내 SELECT 문이 반환하는 결과 집합이 하나의 테이블 역할을 하며, 괄호를 닫은 다음에는 Derived Table(파생 테이블)의 별칭(alias)을 명시해야 합니다.
예를 들어 아래와 같이 FROM 절의 SELECT 서브쿼리를 파생 테이블(Derived Table) 이라고 합니다.
SELECT ... FROM (subquery) [AS] tbl_name ...
Derived Table 특징
사용하는 이유나 쓰임세는 여러가지 유형이 있을 것이며, 보통의 경우는 특정 조건으로 데이터를 aggregation 를 먼저 하고 나서 다른 테이블과 조인을 하거나, 또는 테이블 과 테이블을 조인을 하여 데이터를 가공 후에 다시 다른 테이블을 조인 하는 하는 형태 등으로 다양한 유형으로 사용하게 되며, SELECT 를 통해서 별도의 결과 집합을 테이블화 하여 사용하게 됩니다.
참고1) 포스팅에서는 MySQL 8.0.23 버전을 사용하였습니다.
참고2) 포스팅에서는 employees 샘플 데이터를 통해 진행하였습니다. 아래 github 에서 다운로드 받을수 있습니다.
참고3) 오라클이나 다른 DBMS 에서는 인라인뷰(inline view) 라고 표현하고 있습니다.
아래와 같은 쿼리에서 sq 라고 별칭(alias) 를 사용한 쿼리 블럭이 Derived Table(파생 테이블)이 되게 됩니다.
select a.dept_name,sq.hire_year
, sq.sum_salary, sq.avg_salary
from departments a join
(SELECT d.dept_no,date_format(hire_date,'%Y') hire_year
, SUM(salary) sum_salary
, AVG(salary) avg_salary
from employees b join
salaries c on b.emp_no=c.emp_no join
dept_emp d on c.emp_no=d.emp_no
group by dept_no,date_format(hire_date,'%Y')
) sq on a.dept_no=sq.dept_no
where a.dept_name='Development';
실행계획은 아래와 같은 형태로 확인할 수 있습니다.
+----+-------------+------------+-------+-------------------+-------------+---------+--------------------+--------+----------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+-------+-------------------+-------------+---------+--------------------+--------+----------+-----------------+ | 1 | PRIMARY | a | const | PRIMARY,dept_name | dept_name | 162 | const | 1 | 100.00 | Using index | | 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 16 | const | 10 | 100.00 | NULL | | 2 | DERIVED | b | ALL | PRIMARY | NULL | NULL | NULL | 299335 | 100.00 | Using temporary | | 2 | DERIVED | d | ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 1 | 100.00 | Using index | | 2 | DERIVED | c | ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 9 | 100.00 | NULL | +----+-------------+------------+-------+-------------------+-------------+---------+--------------------+--------+----------+-----------------+ * 가로 길이에 따라서 일부 컬럼이 편집되어있습니다.
<derived2> 와 DERIVED 가 써있는 라인이 파생 테이블(Derived Table) 과 관련이 있는 내용 입니다.
sq 라고 별칭을 한 Derived Table 이 플랜상에서는 <derived2> 으로 확인 할 수 있으며 ID 2 를 보면 모두 DERIVED 인 것을 확인 할 수 있으며, 조인 순서는 b-d->c 순으로 진행되며, b 는 테이블은 인덱스를 사용할 수 있는 조건이 없어서 Full Scan 으로 수행됨을 확인 할 수 있습니다.
Derived Table 는 쿼리블럭 내에서 처리가 완료 된 후 다른 테이블과 조인이 되게 됩니다.
derived_merge
Derived Table 의 쿼리 블럭에 작성된 쿼리에 따라서 다르게 수행은 될 수 있습니다.
MySQL 5.7 에서 derived_merge 기능이 추가 되면서 오라클 에서 표현으로 View Merging 이 가능해졌습니다.
즉, Derived Table 와 다른 테이블과 Merge 또는 Derived Table 테이블이 Unnest 처리가 가능 합니다.
아래 쿼리와 플랜을 보시면 DERIVED 표현이 없는 것을 확인 할 수 있습니다.
explain
select a.dept_no , a.dept_name
, mng.emp_no , mng.first_name
from departments a join
(select b.dept_no
, b.emp_no
, c.first_name
from dept_manager b ,employees c
where b.emp_no = c.emp_no
and sysdate() between b.from_date
and b.to_date ) mng
where a.dept_no = mng.dept_no
order by 1;
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+----------+---------------------------------+
| 1 | SIMPLE | b | ALL | PRIMARY,dept_no | NULL | NULL | NULL | 24 | 11.11 | Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 16 | employees.b.dept_no | 1 | 100.00 | NULL |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 1 | 100.00 | Using where |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+----------+---------------------------------+
* 가로 길이에 따라 일부 컬럼이 편집되어 있습니다.
물론 Derived Table 테이블 형태로 처리하기 위해서는 옵티마이저에서 derived_merge=off 로 설정하면 기존과 같이 DERIVED 로 수행할 수 있습니다.
MySQL 8.0 버전에서는 아래와 같이 2가지 방법으로 사용할 수 있습니다.
-- optimizer_switch 시스템 변수를 설정
set session optimizer_switch='derived_merge=off';
explain
select a.dept_no , a.dept_name
, mng.emp_no , mng.first_name
from departments a join
(select b.dept_no
, b.emp_no
, c.first_name
from dept_manager b ,employees c
where b.emp_no = c.emp_no
and sysdate() between b.from_date
and b.to_date ) mng
where a.dept_no = mng.dept_no
order by 1;
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 16 | mng.dept_no | 1 | 100.00 | NULL |
| 2 | DERIVED | b | ALL | PRIMARY | NULL | NULL | NULL | 24 | 11.11 | NULL |
| 2 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 1 | 100.00 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
* 가로 길이에 따라 일부 컬럼이 편집되어 있습니다.
-- 또는 힌트 레벨로 조정하여 사용할 수 있습니다.
set session optimizer_switch='derived_merge=on';
explain
select /*+ set_var(optimizer_switch = 'derived_merge=off') */
a.dept_no , a.dept_name
, mng.emp_no , mng.first_name
from departments a join
(select b.dept_no
, b.emp_no
, c.first_name
from dept_manager b ,employees c
where b.emp_no = c.emp_no
and sysdate() between b.from_date
and b.to_date ) mng
where a.dept_no = mng.dept_no
order by 1;
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 16 | mng.dept_no | 1 | 100.00 | NULL |
| 2 | DERIVED | b | ALL | PRIMARY | NULL | NULL | NULL | 24 | 11.11 | NULL |
| 2 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 1 | 100.00 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
* 가로 길이에 따라 일부 컬럼이 편집되어 있습니다.
번외로 아래와 같이 옵티마이저 입장에서 merge 를 하기 힘든 조건을 추가 함으로 merge 를 막을 수도 있습니다.
explain
select a.dept_no , a.dept_name
, mng.emp_no , mng.first_name
from departments a join
(select b.dept_no
, b.emp_no
, c.first_name
from dept_manager b ,employees c
where b.emp_no = c.emp_no
and sysdate() between b.from_date
and b.to_date
limit 0,9999999999999999999
-- limit 조건 추가
) mng
where a.dept_no = mng.dept_no
order by 1;
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 16 | mng.dept_no | 1 | 100.00 | NULL |
| 2 | DERIVED | b | ALL | PRIMARY | NULL | NULL | NULL | 24 | 11.11 | NULL |
| 2 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 1 | 100.00 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+---------------------------------+
* 가로 길이에 따라 일부 컬럼이 편집되어 있습니다.
다른 테이블의 컬럼 참조 불가
Derived Table(파생 테이블)은 특징으로 일반적으로는 동일한 FROM 절에 있는 이전(다른) 테이블의 열을 참조(의존)할 수 없습니다.
위의 설명은 아래와 같은 쿼리 유형을 의미 합니다.
select ...
from tab1 a join (select ...
from tab2 b
where a.id=b.id) b
where a.code in (...);
일반적인 경우 Derived Table 안에서는 밖에 있는 테이블의 컬럼(열) 을 참조 할 수 없습니다.
위에서 예제로 확인한 쿼리의 경우도 "on a.dept_no=sq.dept_no" 조인 조건이 Derived Table 안에서 참고 할 수 있다면 추가적인 조건절로 인하여 더 좋은 성능으로 수행될수 있을 것 입니다.
하지만 조인 조건을 Derived Table 안으로 변경시 아래와 같이 에러가 발생 됩니다.
select a.dept_name, sq.hire_year
, sq.sum_salary, sq.avg_salary
from departments a join
(SELECT d.dept_no,date_format(hire_date,'%Y') hire_year
, SUM(salary) sum_salary
, AVG(salary) avg_salary
from employees b join
salaries c on b.emp_no=c.emp_no join
dept_emp d on c.emp_no=d.emp_no
and a.dept_no=d.dept_no
group by dept_no,date_format(hire_date,'%Y')
) sq
where a.dept_name='Development';
ERROR 1054 (42S22): Unknown column 'a.dept_no' in 'on clause'
derived merge 가 수행되지 못함으로 Derived Table 테이블은 외부의 테이블 컬럼을 참조 할 수 없는 것 입니다.
Lateral Derived Table
Lateral Derived Table 는 MySQL 8.0.14 에 추가된 기능으로 Derived Table(파생 테이블)이 다른(외부) 테이블 참조를 허용됨 지원하는 기능으로 사용하기 위해서는 지정하기 위해 LATERAL 구문으로 파생 테이블로 정의될 수 있습니다.
LATERAL Derived Table 구문 은 Derived Table(파생 테이블) 앞에 키워드가 지정 사용된다는 점을 제외하고는 보통의 Derived Table(파생 테이블)의 구문과 동일합니다
LATERAL Derived Table 사용하려면 LATERAL 키워드가 각 테이블 앞에 기술해야 합니다.
LATERAL Derived Table은 쉼표로 구분된 테이블 목록이나 조인 구문(JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN 또는 RIGHT [OUTER] JOIN) 에서 FROM 절에서만 사용할 수 있습니다.
그럼 LATERAL Derived Table 를 사용하였을 때 차이를 살펴보기 위해서 처음 사용한 쿼리를 다시 수행하여 소요시간을 살펴보도록 하겠습니다.
select a.dept_name,sq.hire_year
, sq.sum_salary, sq.avg_salary
from departments a join
(SELECT d.dept_no,date_format(hire_date,'%Y') hire_year
, SUM(salary) sum_salary
, AVG(salary) avg_salary
from employees b join
salaries c on b.emp_no=c.emp_no join
dept_emp d on c.emp_no=d.emp_no
group by dept_no,date_format(hire_date,'%Y')
) sq on a.dept_no=sq.dept_no
where a.dept_name='Development';
<결과 데이터 생략>
16 rows in set (4.09 sec)
포스팅에서 사용하는 시스템 기준 4초 가량 소요 되었습니다.
그 다음 LATERAL Derived Table 으로 수행 해보도록 하겠습니다.
-- 실행 계획
explain
select a.dept_name, sq.hire_year
, sq.sum_salary, sq.avg_salary
from departments a join
LATERAL (SELECT d.dept_no,date_format(hire_date,'%Y') hire_year
, SUM(salary) sum_salary
, AVG(salary) avg_salary
from employees b join
salaries c on b.emp_no=c.emp_no join
dept_emp d on c.emp_no=d.emp_no
and a.dept_no=d.dept_no
group by dept_no,date_format(hire_date,'%Y')
) sq
where a.dept_name='Development';
+----+-------------------+------------+--------+-----------------+-----------+---------+---------------------+--------+----------+-----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------------+------------+--------+-----------------+-----------+---------+---------------------+--------+----------+-----------------------------------------+
| 1 | PRIMARY | a | const | dept_name | dept_name | 162 | const | 1 | 100.00 | Using index; Rematerialize (<derived2>) |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 410707 | 100.00 | NULL |
| 2 | DEPENDENT DERIVED | d | ref | PRIMARY,dept_no | dept_no | 16 | employees.a.dept_no | 41392 | 100.00 | Using index; Using temporary |
| 2 | DEPENDENT DERIVED | b | eq_ref | PRIMARY | PRIMARY | 4 | employees.d.emp_no | 1 | 100.00 | NULL |
| 2 | DEPENDENT DERIVED | c | ref | PRIMARY | PRIMARY | 4 | employees.d.emp_no | 9 | 100.00 | NULL |
+----+-------------------+------------+--------+-----------------+-----------+---------+---------------------+--------+----------+-----------------------------------------+
* 가로 길이에 따른 일부 컬럼이 편집되어 있습니다.
-- 실제 수행
select a.dept_name, sq.hire_year
, sq.sum_salary, sq.avg_salary
from departments a join
LATERAL (SELECT d.dept_no,date_format(hire_date,'%Y') hire_year
, SUM(salary) sum_salary
, AVG(salary) avg_salary
from employees b join
salaries c on b.emp_no=c.emp_no join
dept_emp d on c.emp_no=d.emp_no
and a.dept_no=d.dept_no
group by dept_no,date_format(hire_date,'%Y')
) sq
where a.dept_name='Development';
< 결과 데이터 생략>
16 rows in set (0.96 sec)
먼저 소요시간은 1초 미만으로 기존과 4배 차이가 나는 것을 확인 할 수 있습니다.
PLAN 상에서 ID 1 에서 "Rematerialize (<derived2>)" 라는 extra 정보를 확인 할 수 있으며, ID 2 의 경우 DERIVED 에서 DEPENDENT DERIVED 으로 변경 된 것을 확인 할 수 있습니다. 단어 그대로 연관성 형태로 수행이 되었음을 의미 합니다.
그리고 derived2 의 첫번째 테이블이 b 에서 d 로 변경된것을 확인 할 수 있으며 dept_no 인덱스를 사용하는 것으로 변경 되었음을 알 수 있습니다.
dept_no 인덱스를 사용할 수 있는 것은 d 테이블 조건절에 a.dept_no 조건이 추가되었기 때문입니다.
MySQL 에서는 아직까지 가급적 서브쿼리, Derived Table 는 조인으로 변경 하여 수행하는 것이 대부분의 경우 조금이라도 성능적인 개선이 있기는 합니다.
select a.dept_name, date_format(hire_date,'%Y') hire_year
, SUM(salary) sum_salary
, AVG(salary) avg_salary
from departments a join employees b join
salaries c on b.emp_no=c.emp_no join
dept_emp d on c.emp_no=d.emp_no
and a.dept_no=d.dept_no
and a.dept_name='Development'
group by a.dept_name,date_format(b.hire_date,'%Y');
<조회 결과 제외>
16 rows in set (0.88 sec)
MySQL 에서는 가급적으로 먼저 조인문으로 SQL을 작성하는 것을 고려 해보시고, Derived Table(파생 테이블, 인라인뷰) 를 사용해야할 경우 쿼리에 따라서 위와 같은 예제와 같이 조인 조건을 Derived Table 안으로 추가하는 것이 유리할 경우 LATERAL 를 사용한다면 성능 개선에 도움이 되실 것이라고 생각 됩니다.
이번 포스팅은 여기서 정리하도록 하겠습니다.
Reference
Reference URL
• mysql.com/derived-tables
관련된 다른 글
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
