MySQL5.6 중첩 된 결합의 최적화

8.2.1.11 중첩 된 결합의 최적화

결합을 나타내는 구문은 중첩 된 결합을 사용할 수 있습니다. 다음 설명은 섹션 13.2.9.2 "JOIN" 에 설명하는 조인 구문에 대해 언급하고 있습니다.

table_factor 구문은 SQL 표준에 비해 확장되고 있습니다. 후자는 table_reference 만을 허용 괄호 안에 그 목록은 접수하지 않습니다. 이것은 table_reference 항목 목록의 각 쉼표를 내부 조인과 동등하게 보는 경우 보수적 인 확장입니다. 예 :

 SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                  ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

다음과 같습니다.

 SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                  ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

MySQL에서는 CROSS JOIN 은 INNER JOIN 구문 상 동일합니다 (그들은 서로 대체 가능). 표준 SQL에서는 그들은 동등하지 않습니다. INNER JOIN 은 ON 절과 함께 사용합니다. CROSS JOIN 은 그렇지 않은 경우에도 사용할 수 있습니다.

일반적으로 내부 조인 연산만을 포함 조인 식의 괄호는 무시할 수 있습니다. 괄호를 제거하고 작업을 왼쪽으로 그룹화하면 결합 식은 :

 t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.b OR t2.b IS NULL)
    ON t1.a = t2.a

다음의 식으로 변환됩니다.

 (t1 LEFT JOIN t2 ON t1.a = t2.a) LEFT JOIN t3
     ON t2.b = t3.b OR t2.b IS NULL

아직 두 표현식은 동일하지 않습니다. 이를 확인하려면 테이블 t1 , t2 , t3 가 다음의 상태라고합니다.

테이블 t1 에 라인 (1) , (2) 이 포함되어 있습니다
테이블 t2 는 행 (1,101) 가 포함되어 있습니다
테이블 t3 는 행 (101) 이 포함되어 있습니다

이 경우 첫 번째 표현식은 행 (1,1,101,101) , (2,NULL,NULL,NULL) 을 포함한 결과 세트를 반환하고, 두 번째 표현식은 행 (1,1,101,101) , (2,NULL,NULL,101) 을 반환합니다.

mysql> SELECT *
    -> FROM t1
    ->      LEFT JOIN
    ->      (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
    ->      ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
    -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
    ->      LEFT JOIN t3
    ->      ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

다음 예제에서는 외부 조인 작업이 내부 조인 연산과 함께 사용되어 있습니다.

 t1 LEFT JOIN (t2, t3) ON t1.a = t2.a

그 식은 다음의 식으로 변환 할 수 없습니다.

 t1 LEFT JOIN t2 ON t1.a = t2.a, t3.

지정된 테이블 상태는 다음 두 표현식은 다른 행 집합을 반환합니다.

mysql> SELECT *
    -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
    -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

따라서 외부 조인 연산자를 포함한 결합 식의 괄호를 생략하면 원래 표현식의 결과 집합이 변경 될 수 있습니다.

정확히 말하면 왼쪽 외부 조인 연산의 오른쪽 피연산자와 오른쪽 조인 연산의 왼쪽 피연산자의 괄호를 무시할 수 없습니다. 즉, 외부 조인 조작의 내부 테이블 식의 괄호를 무시할 수 없습니다. 다른 피연산자 (외부 테이블의 피연산자) 괄호는 무시할 수 있습니다.

다음 식 :

 (t1, t2) LEFT JOIN t3 ON P (t2.b, t3.b)

는 다음 식과 같습니다.

 t1, t2 LEFT JOIN t3 ON P (t2.b, t3.b)

모든 테이블 t1,t2,t3 및 특성 t2.b 및 t3.b 대한 모든 조건 P 의 경우.

결합 식 ( join_table )의 결합 작업의 실행 순서가 왼쪽에서 오른쪽 않으면 항상 중첩 된 결합이라고합니다. 다음 쿼리를 고려합니다.

 SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.b) ON t1.a = t2.a
   WHERE t1.a> 1

 SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a = t2.a
   WHERE (t2.b = t3.b OR t2.b IS NULL) AND t1.a> 1

그 쿼리는 다음의 중첩 된 결합을 포함하는 것으로 간주됩니다.

 t2 LEFT JOIN t3 ON t2.b = t3.b
 t2, t3

첫 번째 쿼리는 왼쪽 조인 연산에 의해 중첩 된 결합이 형성되고, 두 번째 쿼리는 내부 조인 연산에 의해 그것이 형성됩니다.

첫 번째 쿼리는 괄호를 생략 할 수 있습니다. 결합 식의 문법 구조에 의해 결합 조작의 실행 순서가 결정됩니다. 두 번째 쿼리는 괄호를 생략 할 수 있지만, 그들이 없어도 여기 결합 식은 의적으로 해석 할 수 있습니다. (여기의 확장 구문에서는 두 번째 쿼리의 (t2, t3) 괄호는 필요하지만 이론상없이 분석 할 수 있습니다. LEFT JOIN 및 ON 은 수식 (t2,t3) 의 왼쪽과 오른쪽 구분자의 역할을하므로 쿼리 구문 구조가 유일합니다.)

앞의 예에서이 점을 설명합니다.

내부 조인 만 포함 (외부 결합을 포함하지 않는다) 결합 식의 경우 괄호는 삭제할 수 있습니다. 괄호를 제거하고 왼쪽에서 오른쪽으로 평가할 수 있습니다 (실제로는 임의의 순서로 테이블을 평가할 수 있습니다).
일반적으로 외부 조인 또는 내부 조인 혼합 된 외부 조인의 경우에는 마찬가지 없습니다. 괄호를 제거하면 결과가 달라질 수 있습니다.

중첩 된 외부 결합을 포함하는 쿼리는 내부 조인을 포함 쿼리와 동일한 파이프 라인 방식으로 실행됩니다. 정확하게는 Nested Loop 조인 알고리즘의 변형이 사용됩니다. Nested Loop 조인 쿼리를 실행하는 데 사용하는 알고리즘 스키마를 기억하십시오. 예를 들어 다음과 같은 형식의 3 개의 테이블 T1,T2,T3 에 대한 결합 쿼리가 있다고합니다.

 SELECT * FROM T1 INNER JOIN T2 ON P1 (T1, T2)
                  INNER JOIN T3 ON P2 (T2, T3)
   WHERE P (T1, T2, T3).

여기에서는 P1(T1,T2) 및 P2(T3,T3) 가 어떤 조인 조건 (식의)에서 P(T1,T2,T3) 는 테이블 T1,T2,T3 컬럼에 대한 조건입니다.

Nested Loop 조인 알고리즘은이 쿼리를 다음과 같이 실행합니다.

 FOR each row t1 in T1 {
   FOR each row t2 in T2 such that P1 (t1, t2) {
     FOR each row t3 in T3 such that P2 (t2, t3) {
       IF P (t1, t2, t3) {
          t : = t1 || t2 || t3; OUTPUT t;
       }
     }
   }
 }

표기 t1||t2||t3 는 "행 t1 , t2 및 t3 의 컬럼을 연결시켜 줄 구축되는 "것을 의미합니다. 다음의 몇 가지 예는 행 이름이 표시되는 위치 NULL 은 행의 각 컬럼에 NULL 이 사용되는 것을 의미합니다. 예를 들어, t1||t2||NULL 은 행 " t1 과 t2 의 컬럼과 t3 의 각 컬럼의 NULL 을 연결시켜 줄 구축되는 "것을 의미합니다.

여기에서 중첩 된 외부 조인이있는 쿼리를 고려하자.

 SELECT * FROM T1 LEFT JOIN
               (T2 LEFT JOIN T3 ON P2 (T2, T3))
               ON P1 (T1, T2)
   WHERE P (T1, T2, T3).

이 쿼리는 Nested Loop 패턴을 변경하고 다음을 가져옵니다.

 FOR each row t1 in T1 {
   BOOL f1 : = FALSE;
   FOR each row t2 in T2 such that P1 (t1, t2) {
     BOOL f2 : = FALSE;
     FOR each row t3 in T3 such that P2 (t2, t3) {
       IF P (t1, t2, t3) {
         t : = t1 || t2 || t3; OUTPUT t;
       }
       f2 = TRUE;
       f1 = TRUE;
     }
     IF (! f2) {
       IF P (t1, t2, NULL) {
         t : = t1 || t2 || NULL; OUTPUT t;
       }
       f1 = TRUE;
     }
   }
   IF (! f1) {
     IF P (t1, NULL, NULL) {
       t : = t1 || NULL || NULL; OUTPUT t;
     }
   }
 }

일반적으로 외부 조인 조작의 첫 번째 내부 테이블의 중첩 루프는 루프 전에 취소 된 루프 후에 검사하는 플래그가 도입됩니다. 플래그는 외부 테이블의 현재 행에서 내부 피연산자를 나타내는 테이블에서 일치가 발견 될 때 선택됩니다. 루프 사이클의 마지막에 플래그가 여전히 꺼져 있으면 외부 테이블의 현재 행에 일치를 찾을 수 없습니다. 이 예제에서는 행이 내부 테이블의 컬럼의 NULL 값으로 보완됩니다. 결과 행은 출력의 최종 점검 또는 다음 중첩 된 루프로 전달되지만 행이 포함 된 모든 외부 조인의 조인 조건을 충족하는 경우에 한정됩니다.

이 예에서는 다음 식으로 표현 된 외부 조인 테이블이 포함되어 있습니다.

 (T2 LEFT JOIN T3 ON P2 (T2, T3))

내부 조인을 포함 쿼리는 최적화 프로그램은 다음과 같은 중첩 루프 다른 순서를 선택할 수 있습니다.

 FOR each row t3 in T3 {
   FOR each row t2 in T2 such that P2 (t2, t3) {
     FOR each row t1 in T1 such that P1 (t1, t2) {
       IF P (t1, t2, t3) {
          t : = t1 || t2 || t3; OUTPUT t;
       }
     }
   }
 }

외부 조인을 포함한 쿼리는 최적화 프로그램은 외부 테이블의 루프가 내부 테이블의 루프 전에 실행되는 순서만을 선택할 수 있습니다. 즉, 외부 조인을 포함한 쿼리는 하나의 중첩 순서 밖에 사용할 수 없습니다. 다음 쿼리는 최적화 프로그램은 두 가지 중첩을 평가합니다.

 SELECT * T1 LEFT JOIN (T2, T3) ON P1 (T1, T2) AND P2 (T1, T3)
   WHERE P (T1, T2, T3)

중첩은 다음과 같습니다.

 FOR each row t1 in T1 {
   BOOL f1 : = FALSE;
   FOR each row t2 in T2 such that P1 (t1, t2) {
     FOR each row t3 in T3 such that P2 (t1, t3) {
       IF P (t1, t2, t3) {
         t : = t1 || t2 || t3; OUTPUT t;
       }
       f1 : = TRUE
     }
   }
   IF (! f1) {
     IF P (t1, NULL, NULL) {
       t : = t1 || NULL || NULL; OUTPUT t;
     }
   }
 }

및 :

 FOR each row t1 in T1 {
   BOOL f1 : = FALSE;
   FOR each row t3 in T3 such that P2 (t1, t3) {
     FOR each row t2 in T2 such that P1 (t1, t2) {
       IF P (t1, t2, t3) {
         t : = t1 || t2 || t3; OUTPUT t;
       }
       f1 : = TRUE
     }
   }
   IF (! f1) {
     IF P (t1, NULL, NULL) {
       t : = t1 || NULL || NULL; OUTPUT t;
     }
   }
 }

모두의 중첩에서 T1 은 외부 조인에서 사용되어 있기 때문에 외부 루프에서 처리되어야합니다. T2 와 T3 는 내부 조인에서 사용되고 있기 때문에 그 결합은 내부 루프에서 처리되어야합니다. 그러나 결합은 내부 결합이기 때문에 T2 와 T3 는 어떤 순서로 처리 할 수 있습니다.

내부 조인의 Nested Loop 알고리즘을 설명 할 때 쿼리 실행 성능에 미치는 영향이 클 수 있다는 자세한 내용은 생략했습니다. 소위 "푸시 다운"조건에 대해서는 설명하지 않았습니다. 예를 들어, WHERE 조건 P(T1,T2,T3) 을 논리적 표준형 의해 표현할 수 있다고합니다.

 P (T1, T2, T2) = C1 (T1) AND C2 (T2) AND C3 (T3).

이 경우, MySQL은 실제로 내부 조인을 포함한 쿼리의 실행에 다음의 Nested Loop 스키마를 사용합니다.

 FOR each row t1 in T1 such that C1 (t1) {
   FOR each row t2 in T2 such that P1 (t1, t2) AND C2 (t2) {
     FOR each row t3 in T3 such that P2 (t2, t3) AND C3 (t3) {
       IF P (t1, t2, t3) {
          t : = t1 || t2 || t3; OUTPUT t;
       }
     }
   }
 }

동등한 항 C1(T1) , C2(T2) , C3(T3) 가 각각 가장 안쪽의 루프에서 평가 가능한 가장 바깥 쪽 루프까지 밀려 나오는 것을 알 수 있습니다. C1(T1) 이 매우 제한적인 조건 인 경우이 조건문 푸시 다운은 테이블 T1 에서 내부 루프에 전달 된 행 수가 크게 줄어들 수 있습니다. 결과적으로, 쿼리의 실행 시간이 크게 단축 될 수 있습니다.

외부 조인을 포함한 쿼리는 외부 테이블의 현재 행에서 내부 테이블에 일치가있는 것이 발견 된 후에 만 WHERE 조건이 확인됩니다. 따라서 내부의 중첩 루프에서 푸시 조건의 최적화는 외부 조인을 포함한 쿼리에 직접 적용 할 수 없습니다. 여기에서는 일치가 발견 된 때 선택되는 플래그에 의해 보호 된 조건부 푸시 다운 술어를 도입해야합니다.

다음 외부 조인있는 예를 들면 :

 P (T1, T2, T3) = C1 (T1) AND C (T2) AND C3 (T3)

보호 된 푸시 다운 조건을 사용하여 Nested Loop 스키마는 다음과 같이됩니다.

 FOR each row t1 in T1 such that C1 (t1) {
   BOOL f1 : = FALSE;
   FOR each row t2 in T2
       such that P1 (t1, t2) AND (f1? C2 (t2) : TRUE) {
     BOOL f2 : = FALSE;
     FOR each row t3 in T3
         such that P2 (t2, t3) AND (f1 && f2? C3 (t3) : TRUE) {
       IF (f1 && f2? TRUE : (C2 (t2) AND C3 (t3))) {
         t : = t1 || t2 || t3; OUTPUT t;
       }
       f2 = TRUE;
       f1 = TRUE;
     }
     IF (! f2) {
       IF (f1? TRUE : C2 (t2) && P (t1, t2, NULL)) {
         t : = t1 || t2 || NULL; OUTPUT t;
       }
       f1 = TRUE;
     }
   }
   IF (! f1 && P (t1, NULL, NULL)) {
       t : = t1 || NULL || NULL; OUTPUT t;
   }
 }

일반적으로 푸시 다운 술어는 P1(T1,T2) 및 P(T2,T3) 등의 결합 조건에서 추출 할 수 있습니다. 이 경우, 푸시 다운 술어는 해당 외부 조인 연산에 의해 생성 된 NULL 이 보완 된 행의 술어 검사를 막는 플래그에 따라 보호됩니다.

여기에있는 내부 테이블에서 같은 중첩 된 결합의 다른 내부 테이블에 키 액세스는 그것이 WHERE 조건에서의 술어에 의해 발생하는 경우에 금지됩니다. (이 예제에서는 조건부 키 액세스를 사용할 수 있지만이 기술은 아직 MySQL 5.6에 채용되어 있지 않습니다.)