MySQL Ver. 5.7 에서 View 기능 개선

1. View에 포함된 Order by 절 적용 방법

MySQL Ver. 5.7부터 View에 포함된 Order by의 내용이 Outer Query에 적용되는 기준이 바뀌었다. MySQL Ver. 5.6 까지는 가능하면 Outer Query의 결과에 Order by 절의 내용이 적용되게 하였다. 하지만, Outer Query에서 해당 순서가 거의 의미가 없는 경우가 많았다. 그래서 이제는 다음의 경우에 View안에 포함된 Order by 내용을 무시하게끔 데이터 추출 작업이 진행된다.

조건은 다음과 같다.

-- 집합 함수를 사용하지 않는다.

-- 2개 이상의 테이블을 참조한다.

테스트를 위해 다음과 같은 View를 만들어보자.

create view v1 as select * from titles where from_date > '1990-01-01' order by from_date;

이제 MySQL Ver. 5.6에서 다음과 같이 쿼리를 실행해 보자.

select de.emp_no, de.dept_no, t.from_date from tit t join dept_emp de on ( t.emp_no = de.emp_no)limit 20;

MySQL Ver. 5.6 에서는 다음과 같이 결과가 보여진다.

+--------+---------+------------+

| emp_no | dept_no | from_date |

+--------+---------+------------+

| 465362 | d007 | 1990-01-02 |

| 205871 | d007 | 1990-01-02 |

| 223561 | d001 | 1990-01-02 |

| 85209 | d001 | 1990-01-02 |

| 433482 | d008 | 1990-01-02 |

| 405285 | d005 | 1990-01-02 |

| 107936 | d007 | 1990-01-02 |

| 223251 | d003 | 1990-01-02 |

| 243198 | d007 | 1990-01-02 |

| 454944 | d004 | 1990-01-02 |

| 262694 | d005 | 1990-01-02 |

| 215359 | d003 | 1990-01-02 |

| 38960 | d005 | 1990-01-02 |

| 295278 | d005 | 1990-01-02 |

| 90648 | d002 | 1990-01-02 |

| 487802 | d005 | 1990-01-02 |

| 12923 | d005 | 1990-01-02 |

| 223561 | d003 | 1990-01-02 |

| 421200 | d009 | 1990-01-02 |

| 252907 | d008 | 1990-01-02 |

+--------+---------+------------+

20 rows in set (0.82 sec)

MySQL Ver. 5.7에서는 다음과 같이 결과가 보여진다.

+--------+---------+------------+

| emp_no | dept_no | from_date |

+--------+---------+------------+

| 10002 | d007 | 1996-08-03 |

| 10003 | d004 | 1995-12-03 |

| 10004 | d004 | 1995-12-01 |

| 10005 | d003 | 1996-09-12 |

| 10006 | d005 | 1990-08-05 |

| 10007 | d008 | 1996-02-11 |

| 10008 | d005 | 1998-03-11 |

| 10009 | d006 | 1990-02-18 |

| 10009 | d006 | 1995-02-18 |

| 10010 | d004 | 1996-11-24 |

| 10010 | d006 | 1996-11-24 |

| 10011 | d009 | 1990-01-22 |

| 10012 | d005 | 1992-12-18 |

| 10012 | d005 | 2000-12-18 |

| 10014 | d005 | 1993-12-29 |

| 10015 | d008 | 1992-09-19 |

| 10016 | d007 | 1998-02-11 |

| 10017 | d001 | 2000-08-03 |

| 10017 | d001 | 1993-08-03 |

| 10018 | d004 | 1995-04-03 |

+--------+---------+------------+

20 rows in set (0.02 sec)

2. Derived Table을 포함한 Veiw 만들기

MySQL Ver. 5.6 에서는 Dervied Table을 포함한 View를 만들 수 없었지만 MySQL Ver. 5.7에서는 만들수 있게 제한이 풀어졌다.

다음과 같은 쿼리를 각각 실행해 보자.

CREATE VIEW v1 AS SELECT de.* FROM dept_emp de JOIN (SELECT * FROM titles WHERE from_date > '1990-01-01') t ON (de.emp_no = t.emp_no);

위와 같은 쿼리를 실행하면 MySQL Ver. 5.6에서는 다음과 같은 에러가 발생한다.

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

MySQL Ver. 5.7에서는 제대로 실행되는 것을 확인할 수 있다.

[root@my57-db1][employees 15:45:13] > create view v1 as select de.* from dept_emp de join (select * from titles where from_date > '1990-01-01') t on (de.emp_no = t.emp_no);

Query OK, 0 rows affected (0.01 sec)

[root@my57-db1][employees 16:28:04] > show create view v1;

+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `de`.`emp_no` AS `emp_no`,`de`.`dept_no` AS `dept_no`,`de`.`from_date` AS `from_date`,`de`.`to_date` AS `to_date` from (`employees`.`dept_emp` `de` join (select `employees`.`titles`.`emp_no` AS `emp_no`,`employees`.`titles`.`title` AS `title`,`employees`.`titles`.`from_date` AS `from_date`,`employees`.`titles`.`to_date` AS `to_date` from `employees`.`titles` where (`employees`.`titles`.`from_date` > '1990-01-01')) `t` on((`de`.`emp_no` = `t`.`emp_no`))) | utf8mb4 | utf8mb4_bin |

1 row in set (0.00 sec)

[root@my57-db1][employees 16:29:07] >

3. View의 성능 개선

MySQL Ver. 5.6에서 SELECT 영역안에 Subquery를 생성하면 병합되지 못하고 Material View로 생성하여 처리되었다. 하지만, MySQL Ver. 5.7부터는 그 제한이 풀어졌다.

다음과 같은 View를 생성해 보자.

CREATE VIEW v2 AS SELECT de.*, (SELECT COUNT(*) FROM departments) total FROM dept_emp de;

위 View 를 생성하고 MySQL Ver. 5.6에서 실행 계획을 확인하면 다음과 같이 출력되는 것을 확인할 수 있다.

[root@c6-mysql56-db1][employees 17:10:24] > explain select * from v2;

+----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+

+----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+

+----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+

3 rows in set (0.00 sec)

위 View를 생성하고 MySQL Ver. 5.7에서 실행 계획을 확인하면 다음과 같이 출력되는 것을 확인할 수 있다.

[root@my57-db1][employees 17:10:03] > explain select * from v2;

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+

| 1 | PRIMARY | de | NULL | ALL | NULL | NULL | NULL | NULL | 331570 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+

2 rows in set, 1 warning (0.00 sec)

그럼 다음과 같은 View도 되는지 확인해 보자.

CREATE VIEW v1 AS SELECT de.*, (SELECT dept_name FROM departments d WHERE d.dept_no=de.dept_no) FROM dept_emp de;

위 View를 생성하고 MySQL Ver.5.6에서 실행 계획을 확인하면 다음과 같이 출력되는 것을 확인할 수 있다.

[root@c6-mysql56-db1][employees 16:43:23] > explain select * from v1;

+----+--------------------+------------+--------+---------------+---------+---------+----------------------+------+-------+

+----+--------------------+------------+--------+---------------+---------+---------+----------------------+------+-------+

+----+--------------------+------------+--------+---------------+---------+---------+----------------------+------+-------+

3 rows in set (0.00 sec)

위 View를 생성하고 MySQL Ver. 5.7에서 실행 계획을 확인하면 다음과 같이 출력되는 것을 확인할 수 있다.

[root@my57-db1][employees 17:17:02] > explain select * from v1;

+----+--------------------+------------+------------+--------+---------------+---------+---------+----------------------+--------+----------+-------+

+----+--------------------+------------+------------+--------+---------------+---------+---------+----------------------+--------+----------+-------+

| 2 | DERIVED | de | NULL | ALL | NULL | NULL | NULL | NULL | 331570 | 100.00 | NULL |

| 3 | DEPENDENT SUBQUERY | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | employees.de.dept_no | 1 | 100.00 | NULL |

+----+--------------------+------------+------------+--------+---------------+---------+---------+----------------------+--------+----------+-------+

3 rows in set, 2 warnings (0.00 sec)

[root@my57-db1][employees 17:30:59] > show warnings;

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |

| Note | 1276 | Field or reference 'employees.de.dept_no' of SELECT #3 was resolved in SELECT #2 |

| Note | 1003 | /* select#1 */ select `v1`.`emp_no` AS `emp_no`,`v1`.`dept_no` AS `dept_no`,`v1`.`from_date` AS `from_date`,`v1`.`to_date` AS `to_date`,`v1`.`Name_exp_5` AS `Name_exp_5` from `employees`.`v1` |

2 rows in set (0.00 sec)

Outer Query의 값을 참조하는 Subquery는 SELECT 영역에 있어도 소용이 없음을 확인할 수 있다.

저작자표시 비영리 변경금지

'MySQL Internal > MySQL Internal' 카테고리의 다른 글

MySQL Internal 구조 설명 (1)	2014.03.10

MySQL DBA를 위한

MySQL Ver. 5.7 에서 View 기능 개선

'MySQL Internal > MySQL Internal' 카테고리의 다른 글

티스토리툴바

MySQL Ver. 5.7 에서 View 기능 개선

'MySQL Internal > MySQL Internal' 카테고리의 다른 글

관련글

티스토리툴바