MySQL 5.6.5 현재 최적화는이 섹션에서 설명하도록 준 결합 전략을 사용하여 서브 쿼리의 실행을 개선합니다.

두 테이블 간의 내부 결합의 경우 결합은 다른 테이블에 일치하는 횟수만큼 한 테이블에서 행을 반환합니다. 그러나 문제에서는 중요한 정보는 일치의 숫자가 아닌 일치가 있는지 만의 경우가 있습니다. 코스 커리큘럼의 클래스와 클래스 명단 (각 클래스에 등록되어있는 학생)을 각각 나열 class 와 roster 라는 테이블이 있다고합니다. 실제로 학생들이 등록되어있는 클래스를 나열하려면 다음 결합을 사용할 수 있습니다.

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

그러나 결과는 등록 된 학생마다 각 클래스가 1 회씩 나열됩니다. 여기에서 문제는 이것은 불필요한 정보의 중복입니다.

class_num 이 class 테이블의 기본 키하면 중복 억제는 SELECT DISTINCT 를 사용하여 얻을 수 있지만 나중에 중복을 제거하는 것만 먼저 모든 일치하는 행을 생성하는 것은 비효율적 이다.

같은 고유 한 결과는 다음 서브 쿼리를 사용하여 얻을 수 있습니다.

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

여기에서 최적화 IN 절에 roster 테이블에서 각 클래스 번호의 인스턴스를 하나만 반환 서브 쿼리가 필요하다는 것을 알 수 있습니다. 이 경우 쿼리는 준 결합으로 실행할 수 있습니다. 즉, roster 의 행에 일치하는 class 의 각 행의 인스턴스를 하나만 반환 작업입니다.

MySQL 5.6.6 이전에는 외부 쿼리의 지정은 간단한 테이블 스캔이나 쉼표 구문을 사용한 내부 결합에 제한되어 있으며, 뷰 참조는 불가능했습니다. 5.6.6 현재 외부 쿼리 지정 외부 조인과 내부 조인 구문을 사용할 수 테이블 참조가 기본 테이블 않으면 안된다는 제한은 없습니다.

MySQL은 서브 쿼리는 준 결합으로 처리되므로 다음 조건을 충족해야합니다.

서브 쿼리는 상관하는 것과 상관하지 않을 수 있습니다. LIMIT 와 마찬가지로 ORDER BY 를 사용하지 않으면 DISTINCT 를 사용할 수 있습니다.

서브 쿼리가 전제 조건을 충족하는 경우, MySQL은 그것을 준 결합으로 변환 다음 전략에서 비용에 근거한 선택합니다.

중복 제거를 제외하고 이러한 각 전략을 활성화 또는 비활성화하려면 optimizer_switch 시스템 변수를 사용합니다. semijoin 플래그는 준 조인을 사용할지 여부를 제어합니다. 이것이 on 으로 설정되어있는 경우 firstmatch , loosescan 및 materialization 플래그는 사용 가능한 준 결합 전략을 세밀하게 제어 할 수 있습니다. 이러한 플래그는 기본적으로 on 입니다. 섹션 8.8.5.2 "전환 가능한 최적화 제어" 를 참조하십시오.

준 결합 전략의 사용은 EXPLAIN 출력에 다음과 같이 표시됩니다.

MySQL 5.6.5 현재 최적화는 서브 쿼리 처리의 효율 향상을 가능하게하는 전략으로 서브 쿼리 구체화를 사용합니다.

실체화를 사용하지 않는 경우, 옵티마이 저는 비 상관 서브 쿼리를 상관 서브 쿼리로 다시 작성할 수 있습니다. 예를 들어, 다음 IN 서브 쿼리는 비 상관입니다 ( where_condition 는 t2 에서의 컬럼 만이 포함되어 t1 에서 포함되지 않습니다).

 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은 처음 서브 쿼리 결과를 필요로 할 때 그 결과를 임시 테​​이블에 구체화합니다. 나중에 결과가 필요할 때 MySQL은 다시 임시 테​​이블을 참조합니다. 테이블은 조회를 빠르게하고 부하를 줄이기 위해 해시 인덱스로 인덱스 설정됩니다. 이 인덱스는 고유 중복이 없기 때문에 테이블을 줄입니다.

서브 쿼리 실체화는 가능한 한 인 메모리 임시 테​​이블을 사용하려고하고 테이블이 너무 커질 경우 디스크 스토리지로 돌아갑니다. 섹션 8.4.4 "MySQL이 내부 임시 테이블을 사용하는 방법" 을 참조하십시오.

MySQL에서 사용되는 서브 쿼리 실체화는 optimizer_switch 시스템 변수 materialization 플래그가 on 이어야합니다. 그 실체화은 아무 곳 (선택 목록, WHERE , ON , GROUP BY , HAVING 또는 ORDER BY 내)에있는 다음 중 하나의 유스 케이스로 분류되는 술어의 서브 쿼리 술어에 적용됩니다.

다음 예제는 UNKNOWN 및 FALSE 술어 평가의 동등성 요건이 하위 쿼리 구체화를 사용할 수 있는지 여부에 어떤 영향을 주는지를 보여줍니다. 서브 쿼리가 아닌 상관 관계가되도록, where_condition 에 t2 의 컬럼 만이 포함되어 t1 에서 포함되지 않는다고합니다.

이 쿼리는 실체화의 대상이됩니다.

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

여기에서는 IN 술어가 UNKNOWN 을 반환하거나 FALSE 를 반환하거나 문제가 없습니다. 모두 t1 에서의 행은 쿼리 결과에 포함되지 않습니다.

서브 쿼리 구체화가 사용되지 않는 예는 t2.b 가 NULL 허용 컬럼 인 다음 쿼리입니다.

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

쿼리에서 EXPLAIN 를 사용하면 옵티마이 저가 서브 쿼리 구체화를 사용하고 있는지 여부의 어떤 지침을 제공합니다. 실체화를 사용하지 않는 쿼리 실행과 비교하여 select_type 이 DEPENDENT SUBQUERY 에서 SUBQUERY 변경 될 수 있습니다. 이것은 외부 행마다 한 번씩 실행되는 서브 쿼리의 경우 실체화 의해 서브 쿼리가 한 번만 실행되도록 할 수 있음을 나타냅니다. 또한 EXPLAIN EXTENDED 의 경우 다음 SHOW WARNINGS 에 의해 표시되는 텍스트는 materialize materialize 및 materialized-subquery (MySQL 5.6.6 이전에는 materialized subselect )가 포함되어 있습니다.

MySQL 5.6.3 현재 최적화는 FROM 절의 서브 쿼리 (즉 파생 테이블)을보다 효율적으로 처리합니다.

SELECT 쿼리의 FROM 절에 서브 쿼리가 표시되는 다음 EXPLAIN 문을 고려합니다.

 EXPLAIN SELECT * FROM (SELECT * FROM t1);

최적화 프로그램은 SELECT 중에 결과가 필요할 때까지 하위 쿼리의 구체화를 지연시키고, 그것을 방지합니다. 이 예에서는 쿼리가 실행되지 않기 때문에 결과가 필요한 것은 아닙니다.

실행되는 쿼리의 경우에도 서브 쿼리 실체화 지연에 따라 최적화 실체화를 완전히 회피 될 수 있습니다. FROM 절의 서브 쿼리의 결과를 다른 테이블에 결합하는 다음 쿼리를 고려합니다.

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

최적화를 통해 t1 이 먼저 처리되고 WHERE 절에 빈 결과가 생성 된 경우 결합은 비어 있어야 하위 쿼리는 구체화 될 필요는 없습니다.

최악의 경우 (파생 테이블이 구체화되는) 추가 작업이 수행되지 않기 때문에 쿼리 실행에 MySQL 5.6.3 이전과 같은 시간이 걸립니다. 최선의 경우 (파생 테이블이 구체화되지 않음), 구체화의 실행에 필요한 시간만큼 쿼리 실행이 빨라집니다.

FROM 절의 서브 쿼리에 구체화가 필요한 경우 최적화 프로그램은 실체화 된 테이블에 인덱스를 추가하여 결과에 빠르게 액세스 할 수 있습니다. 그러한 인덱스에 의해 테이블에 ref 액세스 할 경우 쿼리 실행 중에 읽어야 할 데이터의 양을 크게 줄일 수 있습니다. 다음 쿼리를 고려하십시오.

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

최적화는 derived_t2 컬럼 f1 에 인덱스를 구축함으로써 최소 비용의 실행 계획에서 ref 액세스의 사용이 가능하게되는 경우에 그렇게합니다. 인덱스를 추가 한 후 최적화 프로그램은 실체화 된 파생 테이블을 인덱싱 된 일반 테이블처럼 처리 할 수​​ 있으며, 생성 된 인덱스에서 같은 이점이 있습니다. 인덱싱 오버 헤드는 인덱스를 사용하지 않는 쿼리 실행 비용과 비교하여 무시할 수 있습니다. ref 액세스가 다른 접근 방법보다 비용이 높아질 경우 인덱싱되지 않고 최적화 아무것도 잃지 않습니다.

IN 연산자를 사용하여 (또는 동등한 =ANY 를 사용하여) 서브 쿼리의 결과를 테스트하는 비교에 특정 최적화를 적용 할 수 있습니다. 이 섹션에서는 이러한 최적화 특히 NULL 값이 존재하는 과제에 대해 설명합니다. 설명의 마지막 부분에서는 최적화를 지원하기 위해 수행 할 수있는 것에 대한 제안도 소개합니다.

다음과 같은 서브 쿼리의 비교를 고려합니다.

 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 값을 무시하는 경우에 한해 유효합니다. 즉, '푸시 다운'전략은 다음의 두 가지 조건이 모두 true 일 경우 작동합니다.

이러한 조건 중 하나 이상이 성립하지 않는 경우, 최적화는 복잡합니다.

outer_expr 가 NULL 이 아닌 값임을 알고 있지만, 서브 쿼리는 outer_expr = inner_expr 와 같은 줄을 생성하지 않는 것으로합니다. 이 경우 outer_expr IN (SELECT ...) 는 다음과 같이 평가됩니다.

이 상황에서는 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 서브 쿼리 고유의 접근 방식은 "or NULL "변형도 있습니다. 그러나 그들은 EXPLAIN 의 출력에 표시되지 않기 때문에 EXPLAIN EXTENDED 다음에 SHOW WARNINGS 으로 사용해야합니다 (경고 메시지 checking NULL 에주의하십시오).

 mysql> EXPLAIN EXTENDED
     -> SELECT outer_expr IN (SELECT maybe_null_key FROM t2) 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 : maybe_null_key
           key : maybe_null_key
       key_len : 5
           ref : func
          rows : 2
         Extra : Using index

 mysql> SHOW WARNINGS\G
 *************************** 1. row ******************** *******
   Level : Note
    Code : 1003
 Message : select (`test`.`t1`.`outer_expr`,
          (((`test`.`t1`.`outer_expr`) in t2 on
          maybe_null_key checking NULL))) AS`outer_expr IN (SELECT
          maybe_null_key FROM t2)`from`test`.`t1`

추가 OR ... IS NULL 조건에 따라 쿼리의 실행은 다소 복잡하고 하위 쿼리의 최적화 부분에도 적용 할 수 없게되지만, 일반적으로 이것은 허용 할 수 있습니다.

outer_expr 가 NULL 이 될 가능성이있는 경우 상황은 더 나빠집니다. "알 수없는 값"으로 NULL 의 SQL의 해석에 따르면, NULL IN (SELECT inner_expr ...) 는 다음과 같이 평가 될 것입니다.

올바른 평가는 SELECT 어쨌든 어떤 행을 생성 여부를 확인할 수 있도록 할 필요가 있기 때문에, outer_expr = inner_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 ) 는 다음의 값으로 평가되는 특수 함수입니다.

트리거 함수는 CREATE TRIGGER 에서 만드는 종류의 트리거는 없습니다.

trigcond() 함수에 래핑 된 등식은 쿼리 옵티 마이저에게 최고의 조건으로하지 않습니다. 대부분의 최적화에서는 쿼리 실행시 설정 또는 해제 될 수있는 조건을 처리 할 수 없기 때문에, trigcond( X ) 을 모두 알 수없는 함수로 간주 무시합니다. 현재 트리거 된 등식의 최적화에 사용할 수 있습니다.

최적화가 트리거 조건을 사용하여 어떤 종류의 인덱스 룩업 기반 액세스를 만들 때 (위 목록의 처음 두 항목에 대해) 조건이 해제 된 경우 대체 전략이 필요합니다. 이 폴백 전략은 항상 같고, 풀 테이블 스캔을 수행합니다. EXPLAIN 의 출력으로 대체는 Extra 컬럼에 Full scan on NULL key 로 표시됩니다.

 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 EXTENDED 에 이어 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 이 될 수 있어도 실제로 그렇게 될 수없는 경우 성능 저하는 없습니다.

쿼리 최적화 프로그램이 쿼리를보다 효율적으로 실행되도록하려면 다음 팁을 사용하십시오.

subquery_materialization_cost_based 하여 서브 쿼리 구체화 및 IN -> EXISTS 서브 쿼리 변환의 선택을 제어 할 수 있습니다. 섹션 8.8.5.2 "전환 가능한 최적화 제어" 를 참조하십시오.