VIEW 알고리즘 이해하기 - TEMPTABLE 편

이전 글 : https://monny.tistory.com/223

VIEW 알고리즘 이해하기 - MERGE 편

이전 글에서 MERGE에 대해서 알아보았습니다. 오늘은 TEMPTABLE 전략에 대해 알아보려고 하는데 TEMPTABLE 전략은 아래 글의 Materialize와 같습니다.

옵티마이저의 파생 테이블 관련 전략

옵티마이저는 파생 테이블 참조를 두 가지 전략을 이용하여 다룹니다.

• Merge (병합) : 외부 쿼리에 파생 테이블을 병합.
• Materialize (구체화) : 파생 테이블을 임시 테이블에 저장.

이전 글에서 이미 보았겠지만 Merge의 예시를 되짚어 보도록 하겠습니다.

예제 1:

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

파생된 테이블 derived_t1을 merge 전략으로 다룬다면 쿼리는 아래와 비슷한 형태로 실행이 됩니다.

SELECT * FROM t1;

예제 2:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

마찬가지로 derived_t2를 merge 전략으로 다루면 아래와 같은 형태로 실행이 됩니다.

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

예시 1과 예시 2를 Materialization 전략으로 적용하면 derived_t1 과 derived_t2는 각 쿼리안에서 분리된 테이블로 간주됩니다.

옵티마이저는 derived(파생) 테이블과 뷰 참조를 동일한 방식으로 다룹니다. 가능한 Materialization 전략을 방지하기 위해 외부 쿼리에서 파생된 테이블로 조건을 내리는 것(제가 이해하기로는 외부 쿼리 조건을 파생테이블 안으로 옮기는걸로 이해했습니다)을 할 수 있으며 더 효율적인 실행 계획을 생성시킬 수 있습니다.

위를 이해하기 위해서는 https://dev.mysql.com/doc/refman/5.7/en/subquery-materialization.html 이쪽을 좀 더 봐야할 것 같습니다.

병합하면 61개 이상의 기존 테이블을 참조하는 외부 쿼리 블록이 생성될 경우 옵티마이저는 Materialization 전략을 선택하도록 되어있습니다.

옵티마이저는 파생테이블 혹은 뷰 참조에 ORDER BY 구문을 외부 쿼리 블럭으로 전파할 수 있습니다. 단 아래의 조건이 모두 참이어야 합니다.

• 외부 쿼리가 그룹화 혹은 집계화를 하지 않는 경우
• 외부 쿼리에 DISTINCT, HAVING, ORDER BY를 명시하지 않은 경우
• 외부 쿼리가 FROM절안에서만 파생 테이블 혹은 뷰를 참조하는 경우

만약 위의 조건과 부합하지 않는다면 옵티마이저는 ORDER BY 절을 무시합니다.

 

옵티마이저가 Merge 전략을 사용하지 않은 경우

아래의 방법은 옵티마이저가 파생 테이블 혹은 뷰 참조를 외부로 쿼리 블럭으로 병합하는 것에 대해 영향을 줄 수 있습니다.

• 다른 규칙이 병합을 방해하지 않는다는 가정하에 optimizer_switch 시스템 변수의 derived_merge 플래그가 사용될 수 있습니다. 기본적으로 플래그는 병합을 허용하도록 되어있습니다. 플래그를 사용하지 않도록 설정하면 병합이 방지고 ER_UPDATE_TABLE_USED 오류가 방지됩니다. 
• 또한 derived_merge 플래그는 ALGORITHM이 포함되있지 않는 뷰에도 적용됩니다. 그러므로 서브 쿼리에 동일한 표현식을 사용하는 뷰 참조에 대해 ER_UPDATE_TABLE_USED 오류가 발생하는 경우 뷰 정의에 ALGORITHM=TEMPTABLE 를 추가하면 병합을 방지하고 derived_merge 플래그보다 우선시됩니다. 
• 병합을 방지하는 구성을 하위 쿼리에서 사용하여 병합을 비활성화할 수 있습니다. 병합을 방지하는 구문을 파생 테이블과 뷰 참조에 대해 동일하게 적용됩니다. 
  • 집계 함수 - SUM(), MIN(), MAX(), COUNT(*)
  • DISTINCT
  • GROUP BY
  • ORDER BY
  • HAVING
  • LIMIT
  • UNION
  • SELECT 절의 서브 쿼리
  • 유저 변수 지정
  • 리터럴 값만 참조

Materialize 이해하기

만약 옵티마이저가 파생 테이블에 대하여 병합이 아닌 materialization 전략을 선택할 경우 쿼리를 아래와 같이 다룹니다.

• 옵티마이저는 쿼리 실행 중에 해당 파생 테이블이 필요할 떄까지 파생 테이블에 대한 구체화를 연기합니다. 사용하지 않을 때에 구체화를 지연시키는 것은 성능을 향상 시킬 수 있습니다. 파생 테이블의 결과를 다른 테이블에 결합는 쿼리를 고려해봅시다. 옵티마이저가 다른 테이블을 처리한 후 행을 반환하지 않는 경우 조인 작업을 더 수행할 필요가 없으며 옵티마지어가 파생된 테이블을 구체화하는 것을 완전히 건너뛸 수 있습니다.
• 쿼리 실행 중에 옵티마이저는 파생 테이블에 인덱스를 추가하여 행 검색 속도를 높일 수 있습니다

파생 테이블을 포함하는 SELECT 쿼리를 EXPLAIN 하는 구문을 고려해보자.

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

옵티마이저는 파생 테이블을 필요로 하는 SELECT가 실행되기 전까지 파생 테이블 구체화를 지연시켜 구체화되지 않도록 합니다. EXPLAIN의 경우 쿼리는 실행되지 않으므로 구체화 결과가 필요하지 않습니다.

실행되는 쿼리의 경우에도 파생 테이블 구체화의 지연으로 인해 옵티마이저가 구체화를 완전히 피할 수 있습니다. 이 경우 구체화를 하는데에 필요한 시간만큼을 아낄 수 있으므로 쿼리 실행이 빨라집니다. 파생 테이블의 결과를 다른 테이블에 조인하는 쿼리를 고려해보자

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

만약 옵티마이저가가 t1을 먼저 실행하고 WHERE 문을 실행했을때 결과값이 비어있는 경우 조인은 불필요하여 파생 테이블을 구체화할 필요가 없습니다.

파생 테이블 구체화가 요구되는 경우를 보도록 하죠. 옵티마이저는 구체화 테이블에 대한 접근을 빠르게 하기 위해 인덱스를 추가할 것입니다. 이러한 인덱스가 테이블에 대한 참조 엑세스를 가능하게 하면 쿼리 실행 중에 읽는 데이터의 양을 크게 줄일 수 있습니다. 아래의 쿼리를 고려해보죠.

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

옵티마이저는 derived_t2의 f1 컬럼을 인덱스로 구성하는데 이렇게하면 가장 낮은 비용의 실행 계획을 위한 참조 접근을 사용할 수 있습니다. 인덱스가 추가된 후 옵티마이저는 구체화된 파생 테이블을 인덱스가 존재하는 일반 테이블과 동일하게 간주할 수 있으며 유사한 이득을 얻을 수 있습니다. 인덱스를 생성하는 비용은 인덱스 없이 쿼리가 실행되는 것에 비하면 무시해도 될 수준입니다. 만약 참조 접근이 다른 접근 방법보다 더 높은 비용을 초래한다면 옵티마이저는 인덱스를 생성하지 않고 손실도 없습니다.

옵티마이저 trace 출력의 경우 병합된 파생 테이블 또는 뷰 참조가 노으로 표시되지 않습니다. 사아위 쿼리의 계획에는 기본 테이블만 표시됩니다.