[MySQL] 서브 쿼리, 파생(Derived) 테이블 및 뷰 참조 최적화

+ IN (or = ANY) 서브 쿼리의 경우 옵티마이저는 다음 선택 사항을 갖습니다.

- Semijoin

- Materialization(구체화)

- EXISTS 전략

 

+ NOT IN (or <> ALL) 서브 쿼리의 경우 옵티마이저는 다음 선택 사항을 갖습니다.

- Materialization(구체화)

- EXISTS 전략

 

파생 테이블의 경우 옵티마이저에는 다음과 같은 선택 사항이 있습니다 (참조보기에도 적용됨).

+ 파생 테이블을 외부 쿼리 블록으로 병합

+ 파생 테이블을 내부 임시 테이블로 구체화

 

다음 설명에서는 이전 최적화 전략에 대한 자세한 정보를 제공합니다.

 

노트

서브 쿼리를 사용하여 단일 테이블을 수정하는 UPDATE DELETE 문의 제한 사항은 옵티마이저가 semijoin 또는 materialization 서브 쿼리 최적화를 사용하지 않는다는 것입니다. 문제를 해결하려면 하위 쿼리가 아닌 조인을 사용하는 다중 테이블 UPDATE DELETE 문으로 다시 작성해야 합니다.

 

세미 조인 변환을 사용하여 서브 쿼리, 파생 테이블(Derived Tables) 참조 최적화

semijoin 테이블 fullout, 중복 weedout, 번째 일치, loose(느슨한) 스캔 구체화(materialization) 같은 여러 실행 전략을 가능하게하는 준비 시간 변환입니다. 옵티마이저는 섹션에 설명 된대로 세미 조인 전략을 사용하여 하위 쿼리 실행을 향상시킵니다.

 

테이블 사이의 내부 조인의 경우, 조인은 다른 테이블에 일치하는 횟수만큼 테이블의 행을 반환합니다. 그러나 일부 질문의 경우 중요한 정보는 일치 횟수가 아니라 일치 여부입니다. 코스 커리큘럼 클래스 명단 ( 클래스에 등록한 학생) 클래스를 각각 나열하는 class roster라는 이름의 테이블이 있습니다. 실제로 학생들이 등록한 수업을 나열하려면 다음 조인을 사용할 있습니다.

SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;

그러나 결과는 등록된 학생에 대해 수업을 나열합니다. 묻는 질문에 대해 이것은 불필요한 정보 복제입니다.

 

class_num 클래스 테이블의 기본 키라고 가정하면 SELECT DISTINCT 사용하여 중복 억제를 수행 있지만 나중에 중복을 제거하기 위해 모든 일치하는 행을 먼저 생성하는 것은 비효율적입니다.

 

하위 쿼리를 사용하여 중복되지 않은 결과를 얻을 있습니다.

SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);

여기서 옵티마이 저는 IN절이 서브 쿼리가 로스터 테이블에서 클래스 번호의 인스턴스 하나만 리턴하도록 요구함을 인식할 있습니다. 경우 쿼리는 semijoin 사용할 있습니다. , 명단의 행과 일치하는 클래스의 행의 인스턴스 하나만 반환하는 작업입니다.

 

외부 조인 내부 조인 구문은 외부 쿼리 사양에서 허용되며 테이블 참조는 기본 테이블, 파생 테이블 또는 참조 있습니다.

 

MySQL에서 하위 쿼리는 다음 기준을 충족해야 세미 조인으로 처리됩니다.

+ WHERE또는 ON절의 최상위 레벨에 나타날 수있는 IN(또는 = ANY) 서브 쿼리 여야 하며 AND 표현식의 용어로 가능합니다. 예를 들면 다음과 같습니다.

SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);

여기서 ot_i it_i 쿼리의 외부 내부 부분에 있는 테이블을 나타내고 oe_i ie_i 외부 내부 테이블의 컬럼을 참조하는 표현식을 나타냅니다.

+ UNION 구문이없는 단일 SELECT 여야합니다.

+ GROUP BY 또는 HAVING 절을 포함해서는 안됩니다.

+ 암시적으로 그룹화되어서는 안됩니다 (집계 함수를 포함하지 않아야 ).

+ LIMIT 함께 ORDER BY 없어야 합니다.

+ 명령문은 외부 쿼리에서 STRAIGHT_JOIN 조인 유형을 사용해서는 안됩니다.

+ STRAIGHT_JOIN 수정자가 없어야합니다.

+ 외부 내부 테이블수는 결합에 허용되는 최대 테이블 수보다 작아야합니다.

 

서브쿼리는 상관되거나 상관되지 않을 있습니다. ORDER BY 사용하지 않으면 LIMIT 같이 DISTINCT 허용됩니다.

 

하위 쿼리가 위의 기준을 충족하면 MySQL 쿼리를 세미 조인으로 변환하고 다음 전략에서 비용 기반 선택을합니다.

+ 하위 쿼리를 조인으로 변환하거나 테이블 아웃을 사용하고 하위 쿼리 테이블과 외부 테이블 간의 내부 조인으로 쿼리를 실행합니다. 테이블 아웃은 하위 쿼리에서 외부 쿼리로 테이블을 가져옵니다.

+ Weedout 중복 : semijoin 조인처럼 실행하고 임시 테이블을 사용하여 중복 레코드를 제거하십시오.

+ FirstMatch : 조합에 대해 내부 테이블을 스캔할 주어진 그룹의 인스턴스가 여러개 있을 경우 모두 반환하지 않고 하나를 선택합니다. "바로 가기"스캔은 불필요한 생성을 제거합니다.

+ LooseScan : 하위 쿼리의 그룹에서 단일 값을 선택할 있는 인덱스를 사용하여 하위 쿼리 테이블을 스캔합니다.

+ 하위 쿼리를 조인을 수행하는 사용되는 인덱스 임시 테이블로 구체화합니다. 여기서 인덱스는 중복을 제거하는 사용됩니다. 임시 테이블을 외부 테이블과 조인할 인덱스는 나중에 조회에 사용될 수도 있습니다. 그렇지 않으면 테이블이 스캔됩니다.

 

다음 전략은 다음 optimizer_switch 시스템 변수 플래그를 사용하여 활성화 또는 비활성화 있습니다.

플래그는 기본적으로 활성화되어 있습니다.

+ semijoin 플래그는 semijoin 사용 여부를 제어합니다.

+ semijoin 활성화되면 firstmatch, loosescan, duplicateweedout materialization 플래그를 통해 허용된 semijoin 전략을보다 세밀하게 제어할 있습니다.

+ duplicateweedout semijoin 전략이 비활성화된 경우 다른 모든 해당 전략을 비활성화하지 않으면 사용되지 않습니다.

+ duplicateweedout 비활성화되면 경우에 따라 옵티마이저가 최적이 아닌 쿼리 계획을 생성 있습니다. 이는 탐욕스러운(greedy) 검색 휴리스틱 정리로 인해 발생하며, optimizer_prune_level = 0 설정하면 피할 있습니다.

 

 

옵티마이저는 파생 테이블 처리의 차이점을 최소화합니다. 이는 STRAIGHT_JOIN 수정자를 사용하는 쿼리와 세미 조인으로 변환할 수있는 IN 하위 쿼리가 있는 뷰에 영향을 줍니다. 다음 쿼리는 처리 변경으로 인해 변환이 변경되어 실행 전략이 달라지기 때문에 이를 보여줍니다.

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

 

옵티마이저는 먼저 뷰를 보고 IN서브 쿼리를 semijoin으로 변환한 다음 뷰를 외부 쿼리로 병합할 있는지 확인합니다. 외부 쿼리의 STRAIGHT_JOIN 수정자가 semijoin 방지하므로 옵티마이저는 병합을 거부하여 구체화된 테이블을 사용하여 파생 테이블을 평가합니다.

 

EXPLAIN 출력은 다음과 같이 semijoin 전략의 사용을 나타냅니다.

+ semijoined 테이블이 외부 선택에 표시됩니다. 확장된 EXPLAIN 출력의 경우 다음 SHOW WARNINGS 표시되는 텍스트는 다시 조인된 쿼리를 표시하고 세미 조인 구조를 표시합니다. 이후 semijoin에서 어떤 테이블을 가져 왔는지 있습니다. 서브 쿼리가 semijoin으로 변환된 경우 서브 쿼리 술어가 없어지고 해당 테이블과 WHERE 절이 외부 쿼리 조인 목록 WHERE 절로 병합되었음을 있습니다.

+ 중복 weedout 대한 임시 테이블 사용은 추가 컬럼에 임시 시작 임시 종료로 표시됩니다. 꺼내지 않았고 임시 시작 임시 종료로 설명되는 EXPLAIN 출력 범위에 있는 테이블은 임시 테이블에 rowid 갖습니다.

+ 추가 열의 FirstMatch (tbl_name) 조인 바로 가기를 나타냅니다.

+ 추가 열의 LooseScan (m..n) LooseScan 전략 사용을 나타냅니다. m n 핵심 부품 번호입니다.

+ 구체화에 임시 테이블 사용은 select_type 값이 MATERIALIZED 행과 테이블 값이 <subqueryN> 행으로 표시됩니다.

 

 

 

■ 구체화(Materialization) 서브쿼리 최적화

최적화 프로그램은 구체화(materialization) 사용하여 보다 효율적인 서브 쿼리 처리를 가능하게합니다. 구체화는 일반적으로 메모리에서 하위 테이블 결과를 임시 테이블로 생성하여 쿼리 실행 속도를 높입니다. MySQL 서브 쿼리 결과를 처음으로 필요로 , 결과를 임시 테이블로 구체화합니다. 결과가 필요할 때마다 MySQL 다시 임시 테이블을 참조합니다. 옵티마이저는 조회를 빠르고 저렴하게 하기 위해 해시 인덱스로 테이블을 인덱스 있습니다. 인덱스에는 중복을 제거하고 테이블을 작게 만드는 고유 값이 포함되어 있습니다.

 

서브 쿼리 구체화는 가능한 경우 메모리 임시 테이블을 사용하고 테이블이 너무 커지면 디스크 스토리지로 falling back합니다.

 

구체화가 사용되지 않는 경우, 옵티마이저는 때때로 상관되지 않은 서브 쿼리를 상관 서브 쿼리로 다시 작성합니다. 예를 들어, 다음 IN 부속 조회는 상관되지 않습니다 (where_condition t1 아닌 t2 열만 포함 ).

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

 

옵티마이저는 이를 EXISTS 상관 서브 쿼리로 다시 작성할 있습니다.

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

임시 테이블을 사용하는 하위 쿼리 구체화는 이러한 다시 쓰기를 피하고 외부 쿼리의 행당 번이 아니라 번만 하위 쿼리를 실행할 있습니다.

 

MySQL에서 하위 쿼리 구체화를 사용하려면 optimizer_switch 시스템 변수 구체화 플래그를 활성화해야합니다. (8.9.2 .“전환 가능한 최적화 참조하십시오.) materialization 플래그를 사용하면 materialization 다음과 같은 술어에 대해 선택 목록 (WHERE, ON, GROUP BY, HAVING 또는 ORDER BY) 나타나는 서브 쿼리 술어에 적용됩니다. 다음 사용 사례 하나에 해당합니다.

+ 외부 표현식 oe_i 또는 내부 표현식 ie_i 입력 가능하지 않은 경우 술어는 양식을 갖습니다. N 1이거나 큽니다.

(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

+ The predicate has this form, when there is a single outer expression oe and inner expression ie. The expressions can be nullable.

+ 술어에는 단일 외부 표현식 oe 내부 표현식이있을 때이 양식이 있습니다. 표현식은 입력 가능할 있습니다.

oe [NOT] IN (SELECT ie ...)

+ 술어가 IN 또는 NOT IN이고 UNKNOWN (NULL) 결과는 FALSE 결과와 동일한 의미를 갖습니다.

 

다음 예는 UNKNOWN FALSE 술어 평가의 동등 요구 사항이 서브 쿼리 구체화를 사용할 있는지 여부에 영향을주는 방식을 보여줍니다. where_condition t1 열만 포함하고 t1 열은 포함하지 않으므로 부속 조회가 상관되지 않는다고 가정합니다.

 

쿼리는 실현 있습니다 :

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

IN 술어가 UNKNOWN 또는 FALSE 리턴하는지 여부는 중요하지 않습니다. 어느 쪽이든, t1 행은 쿼리 결과에 포함되지 않습니다.

 

서브쿼리 구체화(materialization) 사용되지 않는 예는 다음 조회이며, 여기서 t2.b 입력 가능 열입니다.

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE where_condition);

 

서브 쿼리 구체화 사용에는 다음 제한 사항이 적용됩니다.

+ 내부 외부 표현식의 유형이 일치해야 합니다. 예를 들어, 표현식이 모두 정수이거나 10 진수 경우 옵티마이저는 구체화(materialization) 사용할 있지만 표현식이 정수이고 다른 표현식이 10진수인 경우 사용할 없습니다.

+ 내부 표현식은 BLOB 없습니다.

 

쿼리와 함께 EXPLAIN 사용하면 옵티마이저가 하위 쿼리 구체화를 사용하는지 여부를 있습니다.

+ materialization 사용하지 않는 쿼리 실행에 비해 select_type DEPENDENT SUBQUERY에서 SUBQUERY 변경될 있습니다. 이는 외부 (outer row) 실행되는 하위 쿼리의 경우 구체화를 통해 하위 쿼리를 번만 실행할 있음을 나타냅니다.

+ 확장 EXPLAIN 출력의 경우 다음 SHOW WARNINGS 표시되는 텍스트에는 구체화 구체화 하위 쿼리가 포함됩니다.

 

EXISTS전략에 따른 서브 쿼리 최적화

특정 최적화는 하위 쿼리 결과를 테스트하기 위해 IN(또는 =ANY) 연산자를 사용하는 비교에 적용할 있습니다. 글에서는 특히 NULL 값이 나타내는 문제와 관련하여 이러한 최적화에 대해 설명합니다. 마지막 부분은 옵티마이저에 도움을 있는 방법을 알아봅니다.

 

Consider the following subquery comparison:

다음과 같은 서브쿼리 비교 문법이 있습니다.

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL "외부에서 내부로"쿼리를 평가합니다. , 먼저 외부 표현식 outer_expr 값을 얻은 다음 하위 쿼리를 실행하고 생성된 행을 캡처합니다.

 

매우 유용한 최적화는 내부 표현식 inner_expr outer_expr 동일한 부분만 관심 행이라는 서브쿼리에 "알리는" 것이다. 하위 쿼리의 WHERE 절에 비슷한 내용을 적용하여 보다 제한적으로 만듭니다. 변환된 비교는 다음과 같습니다.

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

변환 MySQL 푸시 다운을 이용한 동일한지 확인해야 한다. 이때 하위 쿼리 평가를 위해 검사해야하는 수를 제한 있습니다.

 

보다 일반적으로 N- 행을 반환하는 하위 질의에 N 값을 비교하는 것은 동일한 변환의 대상이 됩니다. oe_i ie_i 해당 외부 내부 표현식 값을 나타내는 경우 하위 쿼리를 비교합니다.

(oe_1, ..., oe_N) IN
  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

 

실행가능:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

 

다음 설명에서는 단일 쌍의 외부 내부 표현식 값을 가정합니다.

방금 설명한 변환에는 한계가 있습니다. 가능한 NULL 값을 무시하는 경우에만 유효합니다. , "푸시 다운"전략은 다음 조건이 모두 충족될때 작동합니다.

+ outer_expr inner_expr NULL 없습니다.

+ NULL FALSE 하위 쿼리 결과와 구분할 필요가 없습니다. 하위 쿼리가 WHERE 절에서 OR 또는 AND 표현식의 일부인 경우 MySQL 사용자가 신경 쓰지 않는다고 가정합니다. 옵티마이저가 NULL FALSE 서브 쿼리 결과를 구별할 필요가 없음을 인식하는 다른 인스턴스는 다음과 같습니다.

... WHERE outer_expr IN (subquery)

경우 WHERE 절은 IN(하위 조회) NULL 또는 FALSE 리턴하는지 여부에 따라 행을 거부합니다.

 

이러한 조건중 하나 또는 다가 유지되지 않으면 최적화가 복잡합니다.

+ NULL, if the SELECT produces any row where inner_expr is NULL

+ FALSE, if the SELECT produces only non-NULL values or produces nothing

outer_expr NULL 아닌 값으로 알려져 있지만 서브쿼리가 outer_expr = inner_expr 같은 행을 생성하지 않는다고 가정합니다. 그러면 outer_expr IN (SELECT ...) 다음과 같이 평가됩니다.

+ NULL : SELECT inner_expr NULL 행을 생성하는 경우

+ FALSE : SELECT NULL 아닌 값만 생성하거나 아무것도 생성하지 않는 경우

 

상황에서 outer_expr = inner_expr 행을 찾는 방법은 이상 유효하지 않습니다. 이러한 행을 찾아야 하지만 찾지 못하면 inner_expr NULL 행도 찾아야 합니다. 대략적으로 말하면, 하위 쿼리는 다음과 같이 변환 있습니다.

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr=inner_expr OR inner_expr IS NULL))

 

여분의 IS NULL 조건을 평가할 필요가 있는 이유는 MySQL ref_or_null 액세스 방법이 있는 이유입니다.

mysql> EXPLAIN
       SELECT outer_expr IN (SELECT t2.maybe_null_key
                             FROM t2, t3 WHERE ...)
       FROM t1;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref_or_null
possible_keys: maybe_null_key
          key: maybe_null_key
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Using index
...

unique_subquery index_subquery 하위 쿼리 액세스 방법에도 "또는 NULL"변형이 있습니다.

 

추가 OR ... IS NULL 조건은 쿼리 실행을 약간 복잡하게 만들고 (하위 쿼리 내의 일부 최적화는 적용 없게 ) 일반적으로 허용됩니다.

 

outer_expr NULL 수있는 상황은 훨씬 나쁩니다. NULL " 수없는 "으로 해석한 SQL 따르면 NULL IN (SELECT inner_expr ...) 다음과 같이 평가되어야합니다.

+ NULL : SELECT 행을 생성하는 경우

+ FALSE : SELECT 행을 생성하지 않는 경우

 

적절한 평가를 위해서는 SELECT 행을 생성했는지 여부를 확인할 있어야하므로 outer_expr = inner_expr 하위 쿼리로 푸시 다운 없습니다. outer_exprinner_expr  푸쉬다운 없다면 많은 실제 세계 하위 쿼리가 매우 느려지기 때문에 문제가됩니다.

 

본질적으로 outer_expr 값에 따라 서브 쿼리를 실행하는 다른 방법이 있어야 합니다.

 

옵티마이저는 속도에 대한 SQL 준수를 선택하므로 outer_expr NULL 있습니다.

+ outer_expr NULL 경우 다음 표현식을 평가하려면 행을 생성하는지 여부를 판별하기 위해 SELECT 실행해야합니다.

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

앞에서 언급한 종류의 쿼리에 푸시 다운없이 원래의 SELECT 여기에서 실행해야 합니다.

 

+ 반면에, outer_expr NULL 아닌 경우, 비교는 반드시 필수적입니다 :

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

 

푸시 다운 조건을 사용하는 표현식으로 변환합니다.

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

변환이 없으면 하위 쿼리가 느려집니다.

 

조건을 하위 쿼리로 푸시할지 여부에 대한 딜레마를 해결하기 위해 조건은 "트리거"기능에 포함됩니다. 따라서 다음 형식으로 표현합니다 :

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

 

다음과 같이 변환됩니다 :

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(outer_expr=inner_expr))

 

일반적으로, 부속 쿼리 비교가 여러 쌍의 외부 내부 표현식을 기반으로 하는 경우 변환은 다음 비교를 수행합니다.

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

 

그리고 이것을 다음 식으로 변환합니다 :

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(oe_1=ie_1)
                          AND ...
                          AND trigcond(oe_N=ie_N)
       )

trigcond (X) 다음 값으로 평가되는 특수 함수입니다.

 + X : "linked(연결된)"외부 표현식 oe_i NULL 아닌 경우

+ TRUE : "linked(연결된)"외부 표현식 oe_i NULL 경우

 

노트

트리거 기능은 CREATE TRIGGER 작성하는 종류의 트리거가 아닙니다.

 

trigcond() 함수로 랩핑된 동등성은 쿼리 최적화 프로그램의 번째 클래스 술어가 아닙니다.대부분의 최적화는 쿼리 실행 시간에 켜거나 있는 술어를 처리할 없으므로, 그들은 어떤 trigcond (X) 없는 함수로 가정하고 무시합니다. 트리거링 동일성은 이러한 최적화에 의해 사용될 있습니다.

+ 참조 최적화 : trigcond (X = Y [OR Y IS NULL]) 사용하여 ref, eq_ref 또는 ref_or_null 테이블 액세스를 구성할 있습니다.

+ 인덱스 조회 기반 하위 쿼리 실행 엔진 : trigcond(X = Y) 사용하여 unique_subquery 또는 index_subquery 액세스를 구성할 있습니다.

+ 테이블 조건 생성기 : 하위 쿼리가 여러 테이블의 조인인 경우 트리거된 조건이 가능한 빨리 확인됩니다.

 

옵티마이저가 트리거된 조건을 사용하여 일종의 인덱스 조회 기반 액세스를 작성하는 경우 (이전 목록의 처음 항목과 같이) 조건이 해제된 경우에 대한 대체 전략이 있어야 합니다. 대체 전략은 항상 동일합니다. 전체 테이블 스캔을 수행합니다. EXPLAIN 출력에서 대체는 Extra 열에 NULL 키에 대한 전체 스캔으로 표시됩니다.

mysql> EXPLAIN SELECT t1.col1,
       t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
        ...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: index_subquery
possible_keys: key1
          key: key1
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Full scan on NULL key

 

EXPLAIN 실행 다음 SHOW WARNINGS 실행하면 트리거된 조건을 있습니다.

*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
         <in_optimizer>(`test`.`t1`.`col1`,
         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
         on key1 checking NULL
         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
         from `test`.`t1`

트리거된 조건을 사용하면 성능에 영향을 미칩니다. NULL IN(SELECT ...) 표현식으로 인해 이전에 그렇지 않은 경우 전체 테이블 스캔(조회가 느린 상황) 발생할 있습니다. 이는 올바른 결과에 대해 소요된 비용입니다 (트리거 조건 전략의 목표는 속도가 아니라 컴플라이언스를 향상시키는 것입니다).

 

다중 테이블 서브 쿼리의 경우, 조인 옵티마이저에 의해 외부 표현식이 NULL 경우 최적화하지 않기 때문에 NULL IN (SELECT ...) 실행이 특히 느립니다. 왼쪽에 NULL 사용하는 서브쿼리 평가는 달리 나타내는 통계가 있어도 매우 드물다고 가정합니다. 반면에 외부 표현식이 NULL 수는 있지만 실제로는 존재하지 않으면 성능 저하가 없습니다.

 

쿼리 최적화 프로그램이 쿼리를 실행하려면 다음을 참고합니다.

실제로는 컬럼을 NOT NULL 선언합니다. 또한 컬럼에 대한 조건 테스트를 단순화하여 옵티마이저의 다른 측면을 지원합니다.

 

NULL FALSE 서브 쿼리 결과와 구별할 필요가 없으면 느린 실행 경로를 쉽게 피할 있습니다. 다음과 같은 비교를 바꿉니다.

outer_expr IN (SELECT inner_expr FROM ...)

 

표현으로 :

(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))

그런 다음 MySQL 표현 결과가 명확 해지면 AND 부분 평가가 중지되므로 NULL IN (SELECT ...) 평가되지 않습니다.

 

다른 방법의 재작성 :

EXISTS (SELECT inner_expr FROM ...
        WHERE inner_expr=outer_expr)

NULL FALSE 서브 쿼리 결과와 구별할 필요가 없을 적용됩니다. 경우 실제로 EXISTS 원할 있습니다.

 

optimizer_switch 시스템 변수의 subquery_materialization_cost_based 플래그를 사용하면 하위 쿼리 구체화와 IN-to-EXISTS 하위 쿼리 변환 중에서 선택을 제어 있습니다.

 

 

 

병합 또는 구체화(Materialization) 사용하여 파생 테이블 참조 최적화

옵티마이저는 가지 전략을 사용하여 파생 테이블 참조를 처리 있습니다 (이는 참조에도 적용됨).

+ 파생 테이블을 외부 쿼리 블록으로 병합

+ 파생 테이블을 내부 임시 테이블로 구체화

 

예제 1:

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

 

파생 테이블 파생 _t1 병합하면 해당 쿼리가 다음과 유사하게 실행됩니다.

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 병합하면 해당 쿼리가 다음과 유사하게 실행됩니다.

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

materialization 사용하면 derived_t1 derived_t2 각각 해당 쿼리 내에서 별도의 테이블로 처리됩니다.

 

옵티마이저는 파생 테이블과 참조를 동일한 방식으로 처리합니다. 가능하면 불필요한 구체화를 피하여 외부 쿼리에서 파생 테이블로 조건을 푸시하고 보다 효율적인 실행 계획을 생성합니다.

 

병합하면 61 이상의 기본 테이블을 참조하는 외부 쿼리 블록이 생성되는 경우 옵티마이저가 대신 구체화를 선택합니다.

 

최적화 프로그램은 이러한 조건이 모두 충족되는 경우 파생 테이블 또는 참조에서 ORDER BY 절을 외부 쿼리 블록에 전파합니다.

+ 외부 쿼리는 그룹화되거나 집계되지 않습니다.

+ 외부 쿼리는 DISTINCT, HAVING 또는 ORDER BY 지정하지 않습니다.

+ 외부 쿼리에는 파생 테이블 또는 참조가 FROM 절의 유일한 소스로 있습니다.

 

그렇지 않으면 옵티마이저는 ORDER BY 절을 무시합니다.

 

옵티마이저가 파생 테이블을 병합하고 참조를 외부 쿼리 블록으로 보려고 시도하는지 여부에 영향을주는 사용할 수있는 방법은 다음과 같습니다.

+ 다른 규칙이 병합을 방해하지 않는다고 가정하면 optimizer_switch 시스템 변수의 derived_merge 플래그를 사용할 있습니다. 기본적으로 플래그는 병합을 허용하도록 활성화되어 있습니다. 플래그를 비활성화하면 병합을 방지하고 ER_UPDATE_TABLE_USED 오류를 피할 있습니다.

 

derived_merge 플래그는 ALGORITHM 절이 없는 뷰에도 적용됩니다. 따라서 서브 쿼리와 동등한 표현식을 사용하는 참조에 대해 ER_UPDATE_TABLE_USED 오류가 발생하면 ALGORITHM = TEMPTABLE 정의에 추가하면 병합이 방지되고 derived_merge 값보다 우선합니다.

 

+ 구체화(materialization) 미치는 영향이 명시 적이지는 않지만 서브 쿼리에서 병합을 방해하는 구성을 사용하여 병합을 비활성화 있습니다. 병합을 방지하는 구문은 파생 테이블 참조에서 동일합니다.

- 집계 함수 (SUM(), MIN(), MAX(), COUNT(), )

- DISTINCT

- GROUP BY

- HAVING

- LIMIT

- UNION or UNION ALL

- 선택 목록의 서브 쿼리

- 사용자 변수에 할당

- 리터럴 값만 참조 ( 경우 기본 테이블이 없음)

 

derived_merge 플래그는 ALGORITHM 절이 없는 뷰에도 적용됩니다. 따라서 서브 쿼리와 동등한 표현식을 사용하는 참조에 대해 ER_UPDATE_TABLE_USED 오류가 발생하면 ALGORITHM = TEMPTABLE 정의에 추가하면 병합이 방지되고 현재 derived_merge 값보다 우선합니다.

 

옵티마이저가 파생 테이블을 병합하지 않고 구체화 전략을 선택하면 다음과 같이 쿼리를 처리합니다.

+ 옵티마이저는 쿼리 실행 중에 컨텐츠가 필요할 때까지 파생 테이블 구체화를 연기합니다. 구체화를 지연 시키면 전혀 수행하지 않아도 되기 때문에 성능이 향상됩니다. 파생 테이블의 결과를 다른 테이블에 조인하는 쿼리를 고려합니다. 옵티마이저가 다른 테이블을 먼저 처리하고 행을 리턴하지 않는 것을 발견하면 조인을 수행 필요가 없으며 옵티마이저는 파생 테이블을 구체화하는 것을 완전히 건너 있습니다.

+ 쿼리 실행 옵티마이저는 파생 테이블에 인덱스를 추가하여 검색 속도를 높일 있습니다.

 

파생 테이블이 포함 SELECT 쿼리에 대해 다음 EXPLAIN 문을 고려합니다.

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

옵티마이저는 SELECT 실행 중에 결과가 필요할 때까지 지연시킴으로써 파생 테이블(derived table) 구체화하는 것을 방지합니다. 경우 쿼리는 실행되지 않으므로(설명 문에서 발생하기 때문에), 결과는 필요하지 않습니다.

 

실행된 쿼리의 경우에도 파생 테이블(derived table) 구체화(materialization) 지연되면 옵티마이저가 구체화를 완전히 피할 있습니다. 경우 구체화를 수행하는 필요한 시간만큼 쿼리 실행이 빨라집니다. 파생 테이블의 결과를 다른 테이블에 조인하는 다음 쿼리가 있습니다.

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

 

옵티마이저가 t1 먼저 처리하고 WHERE 절이 결과를 생성하는 경우, 결합은 반드시 비어 있어야 하고 파생된 테이블은 구체화될 필요가 없습니다.

 

파생 테이블(derived table)에서 구체화가 필요한 경우 옵티마이저는 구체화된(materialization)테이블에 인덱스를 추가하여 액세스 속도를 높일 있습니다. 이러한 인덱스가 테이블에 대한 ref 액세스를 가능하게하면 쿼리 실행중 읽은 데이터 양을 크게 줄일 있습니다. 다음 쿼리가 있습니다.

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

옵티마이저는 derived_t2에서 f1 컬럼에 대한 인덱스를 구성합니다. 그렇게하면 최저 비용 실행 계획에 참조 액세스를 사용할 있습니다. 인덱스를 추가한 옵티마이저는 구체화된(materialized)파생 테이블(derived table) 인덱스가 있는 일반 테이블과 동일하게 처리할 있으며 생성된 인덱스의 이점과 유사합니다. 인덱스가 없는 쿼리 실행 비용과 비교하여 인덱스 생성 오버 헤드는 무시할 있습니다. 참조 액세스가 다른 액세스 방법보다 비용이 많이 드는 경우, 옵티마이저는 인덱스를 작성하지 않으며 아무것도 잃지 않습니다.

 

옵티마이저 추적 출력의 경우 병합된 파생 테이블(merged derived table) 또는 참조는 노드로 표시되지 않습니다. 기본 쿼리 계획에는 기본 테이블만 나타납니다.

 

도움이 되셨다면 광고클릭 한번 부탁드립니다.

 

Designed by JB FACTORY