이 문서는 오라클의 MySQL서버 팀 블로그에 올라온 글을 번역하여 정리한 문서입니다.
http://mysqlserverteam.com/derived-tables-in-mysql-5-7/
MySQL5.7 부터 derived Table과 View에 대한 성능 개선이 많이 이루어졌다. 여기에서는 어떻게 성능개선이 이루어졌는지 알아보려고 한다.
Derived Table: 쿼리가 실행될 때, from절 아래에 서브쿼리로 만들어진 테이블을 말한다. 또는 쿼리 실행 시 내부 작업으로 인해 임시적으로 생성되는 쿼리도 Derived Table이라고 한다. 간단하게는 "subquery in From classe"로 생각하면 된다.
Derived Table은 View로 변경하는 것도 가능하다. 그래서, View를 named Derived Table이라고도 부른다.
다음의 두 쿼리 셋은 동일한 일을 하는 쿼리이다.
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT * FROM v1 JOIN t2 USING (a);
SELECT * FROM (SELECT * FROM t1) AS dt1 JOIN t2 USING (a);
꽤 오랜 시간동안 view와 derived table은 다른 방식으로 처리되었다. view는 outer query와 병합되어 처리되는게 가능했지만, derived table은 구체화되어 outer query안에 temporary table을 사용하듯이 처리되었다.
이것은 2가지 이유로 비용이 많이 든다. : 먼저, materialized temporary table을 만들고 읽는데에 많은 시간이 필요하다. 그리고, outer query와 derived table 사이에 조건을 미리 공유하여 필요한 데이터만 필터링 하는것이 힘들다. (push down condition)
MySQL Ver. 5.7에서는 view와 derived table을 동일하게 처리하기 위한 고민을 하였다. 양쪽 모두 그들이 포함된 쿼리가 해결될때 같이 해결된다. (여기서 해결된다는 것은 딕셔너리에서 테이블과 컬럼 정보를 찾고, 쿼리가 정확한지 찾는 일련의 과정을 의미한다. 문서에서 이 과정을 resolve한다고 표현했다.) 그리고, 같은 장소에서 derived table과 view를 병합할 지, 구체화 할 지를 결정한다. 사실, 세미 조인 작업을 하고자 결정했을 때 사용하는 공간과 동일한 공간이다.
Derived Table이 view와 같이 동일하게 병합되어 작업이 일어날 수 있으나, 다음과 같은 타입의 서브 쿼리가 포함되면, 동일하게 진행되지는 않는다.
- UNION 절
- Group by
- DISTINCT
- Aggregation
- LIMIT or OFFSET
- 사용자가 생성한 변수가 포함된 구문.
다음과 같은 쿼리가 Ver. 5.6과5.7에서 어떻게 실행계획이 다르게 보이는지 확인해 보도록 하자.
SELECT * FROM (SELECT * FROM t1) AS dt1 JOIN t2 USING (a);
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 1 | PRIMARY |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
How to control whether to merge or materialize
사용자는 view를 사용할 때, ALGORITHM 절을 사용하여 병합할지 구체화할지 결정할 수 있다. ALGORITHM=MERGE를 선택하면 가능한 경우 outer query 와 병합이 일어나게 되고, ALGORITHM=TEMPORARY를 선택하여 사용하게 되면 항상 뷰를 구체화 하여 사용한다. 여기에 3번째 옵션이 있다. 아무런 선택도 하지 않게 되는 경우를 말하는데, 이런 경우에는 MERGE를 선택한 경우와 비슷하게 view가 처리된다.
Dervied table을 쿼리에 작성할 때, 그것을 어떻게 생성하여 처리할 것인지 명시할 수는 없다. 그래서, 쿼리 단위로 처리할 수는 없고 옵티마이져에 추가할 수 있게 하였다. 즉, named derived table을 사용할 것인가 말것인가를 설정하는 것인데 즉, 이 말은 derived table을 병합해서 처리할 것인가 아닌가로 다시 설명할 수 있다. 그래서 dervied table을 병합으로 처리하고 싶으면 optimizer_switch='derived_merge=on'와 같이 설정하고 구체화 하고 싶으면, optimizer_switch='derived_merge=off'와 같이 설정하게 하는 것이다.
optimizer_switch에 설정된 값은 view에도 적용된다. 그래서, ALGORITHM을 선택하지 않으면 optimizer_switch에 설정된 대로 동작하게 된다.
ORDER BY in derived table or view
subquery나 view에 있는 ORDER BY는 Ver. 5.6에서 실행되는 것과 다르게 5.7에서 처리된다. 이것은 outer query가 집합적으로 사용되지 않고, 하나의 테이블만 참조하는 경우에만 무시하지 않고, 그 외의 경우에는 그냥 무시된다. 그리고, outer query가 ORDER BY를 가지고 있는 경우에도 무시된다.
다음의 쿼리를 보자.
CREATE VIEW v1 AS SELECT * FROM t1 WHERE x > 100 ORDER BY y;
SELECT * FROM v1;
view의 ORDER BY 절은 select 쿼리에 포함되어있지 않지만, V1 view만 사용하기 때문에 그 영향이 select 쿼리에 보여져야 한다.
이런 동작 방식은 조금 의심스럽다. : 집계작업을 하는 outer query에 ORDER BY를 전파하는 것은 거의 불가능하다. outer query가 join query인 경우, outer query에 전파된 ORDER BY를 사용할 수 있는 가능성이 거의 없다. 하지만, 이전부터 사용하던 방식이기 때문에 간단한 경우에만 동작하게 계속 사용해 왔었다.
Derived tables inside views
이전에는 FROM 절 안에 derived table을 포함한 view를 만들수 없었다. 하지만, Ver. 5.7부터 이 제한이 풀어졌다.
다음과 같은 쿼리가 있다고 가정하자.
CREATE VIEW v AS
SELECT t1.a, t1.b, dt.c
FROM t1 JOIN (SELECT * FROM t2) AS dt ON t1.a=dt.a;
SELECT * FROM v WHERE c > 10;
위 쿼리가 Ver. 5.7에서는 다음과 같이 실행계획이 보여지게 된다.
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
데이터를 변경하는 쿼리에서 view를 사용하는 경우 drived table이 읽기 전용이 되도록 처리하였다. 그것으로 다음과 같은 효과를 얻을 수 있다.
- join이 포함된 view에서 DELETE 구문이 처리되지 못하도록 한다.
- derived table이 포함된 view에서 INSERT 구문이 처리되지 못하도록 한다.
- UPDATE는 join view에서 처리가 되도록 허용하는데, Derived table을 포함한 view의 수정 작업을 복사하는 것 뿐이다.
derived table을 포함하면 view는 insert가 되지 않는다.
INSERT INTO v(a, b, c) VALUES (1, 10, 100);
ERROR 1471 (HY000): The target table v of the INSERT is not insertable-into
join view의 경우에도 삭제 작업이 진행되지 않는다.
DELETE FROM v WHERE a > 0;
ERROR 1395 (HY000): Can not delete from join view 'test.v'
view에 수정작업은 가능하지만 dervied table 부분이 아니어야 한다.
UPDATE v SET b= b + 1 WHERE a > 0;
Refactored resolver order
MySQL의 예전 버젼에서는 테이블이 열린 상태이어야만 view와 derived table이 분석될 수 있었다. 여기서 말하는 분석 쿼리 실행 전에 진행하는 쿼리 분석 작업을 의미한다. 즉, 쿼리 분석을 할때, analyze, optimze, execute하는 전체 순서 안에서의 resolver 순서를 최적화 하기 위한 작업을 진행하는 것을 말한다. 즉, 여깃 derived table과 view가 포함되어있을 경우 조금은 효율적이지 못한 방법으로 analyze되는게 있었는데, 그 부분에 대해 자연적으로 진행될 수 있게 개선을 진행하였다.
Refactored column privilege checking
쿼리 분석시 컬럼에 대한 접근 권한을 체크하는 로직은 매우 복잡한 알고리즘으로 진행된다. 그래서, 몇가지 버그들도 있다. 그래서 다음과 같이 컬럼 권한 부분을 강화하였다.
- dervied table과 view을 분석하는 단계에서는 컬럼에 대한 권한을 확인하지 않는다. outer query가 분석될때 처리가 가능하기 때문이다.
- 전에, 컬럼에 요구되는 권한은 테이블에 연결된 GRANT_INFO 라는 객체에 할당하였다. 그러나, 다른 컬럼은 다른 권한을 필요로 한다. (입력 작업에 사용되는 컬럼은 INSERT 권한이 필요하고, 조회작업에 사용되는 컬럼은 SELECT 권한이 필요하다. ) 그래서, Ver. 5.7 에서는 권한을 체크하는 함수를 이용하여 확인할 수 있게 수정하였다.
- 그래서 내부에서 사용하던 GRANT_INFO::orig_want_privilege 와 GRANT_INFO::want_privilege를 삭제하고 디버깅에서만 사용하도록 수정하였다.
Refactored read_set/write_set processing
쿼리에 포함된 테이블들을 각각 컬럼 셋을 등록하는데 하나는 읽기용도의 컬럼 셋, 하나는 쓰기용도의 컬럼 셋이다. 이것을 우리는 read_set, write_set이라고 한다.
TABLE::mark_column_used()함수의 내부 값과 walker인 Item::mark_field_in_map()을 설정하는 것도 고려해야 한다. 권한 체크를 하는 것과 마찬가지로 resolve할 때 read_set과 write_set을 설정하는 것은 시간이 많이 소요된다. 그래서, 우리는 미리 알고있는 derived table에서 사용하는 컬럼목록과 outer query에서 사용하는 컬럼 목록을 가지고 사용하지 않은 컬럼의 목록을 스토리지 엔진에서 데이터 추출할 때 제외시킬 수 있다.
다음의 쿼리를 보자.
CREATE TABLE t1(a INTEGER, b BLOB);
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT a FROM v1;
위 쿼리는 현재 방법으로 처리되면 blob인 b를 제외하고 a만 추출하게 된다.
'MySQL Internal' 카테고리의 다른 글
| MySQL Ver. 5.7 에서 Derived Tables 동작 방식 (0) | 2016.02.26 |
|---|