MySQL 5.6에서 5.7로 이행한 이후 MySQL JOIN은 < > 연산자를 사용하여 WHERE 절을 필터링하지 않았습니다.

MySQL 5.6에서 5.7로 이행한 이후 MySQL JOIN은 < > 연산자를 사용하여 WHERE 절을 필터링하지 않았습니다.

DB 시스템을 MySQL 5.6에서 제공하는 MySQL 5.7로 업그레이드하려고 하는데, 업그레이드 이후 몇 가지 쿼리의 실행 속도가 매우 느립니다.

조사 결과, 몇 개의 JOIN 쿼리로 좁혀졌습니다.이 쿼리는 '보다 큼' 또는 '보다 작음' 연산자를 사용할 때 갑자기 'WHERE' 절을 듣지 않게 되었습니다.'=' 연산자를 사용하면 예상대로 작동합니다.이로 인해 큰 테이블을 쿼리할 때 CPU 사용률이 100%로 일정하게 유지되었습니다.

현재 문제를 설명하기 위해 쿼리가 간소화되었습니다. expiry를 사용하면 다음과 같은 출력이 나옵니다.

explain 
        select * from TableA as A
                left join
                (
                    select
                        DATE_FORMAT(created_at,'%H:%i:00') as `time`
                    FROM
                        TableB
                    WHERE
                        created_at < DATE_ADD(CURDATE(), INTERVAL -3 HOUR) 
                )
                as V ON V.time = A.time

산출량

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  A   NULL    ALL NULL    NULL    NULL    NULL    10080   100.00  NULL
1   SIMPLE  TableB  NULL    index   created_at  created_at  4   NULL    488389  100.00  Using where; Using index; Using join buffer (Block Nested Loop)

보시는 바와 같이 488389 행을 조회/조회하고 있으며 where 구를 사용하지 않습니다.이것은 테이블 내의 레코드의 합계이기 때문입니다.

이제 LIMIT 9999999 명령을 사용하거나 '=' 연산자를 사용하여 동일한 쿼리를 실행합니다.

explain 
        select * from TableA as A
                left join
                (
                    select
                        DATE_FORMAT(created_at,'%H:%i:00') as `time`
                    FROM
                        TableB
                    WHERE
                        created_at < DATE_ADD(CURDATE(), INTERVAL -3 HOUR) LIMIT 999999999
                )
                as V ON V.time = A.time

산출량

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY A   NULL    ALL NULL    NULL    NULL    NULL    10080   100.00  NULL
1   PRIMARY <derived2>  NULL    ALL NULL    NULL    NULL    NULL    244194  100.00  Using where; Using join buffer (Block Nested Loop)
2   DERIVED TableB  NULL    range   created_at  created_at  4   NULL    244194  100.00  Using where; Using index

표의 일부인 '244194' 행 또는 '=' 연산자와 갑자기 일치하는 것을 볼 수 있습니다.

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  A   NULL    ALL NULL    NULL    NULL    NULL    10080   100.00  NULL
1   SIMPLE  TableB  NULL    ref created_at  created_at  4   const   1   100.00  Using where; Using index

역시 한 줄만.

이제 문제는 잘못된 방식으로 쿼리하고 업그레이드 중에 발견한 것인지 MySQL 5.6 이후 변경된 것인지 여부입니다.= 연산자가 작동하는 것은 이상해 보이지만, LIMIT를 사용하지 않는 한 어떤 이유로든 < 및 >는 무시됩니다?..

우리는 이 문제의 원인을 찾아봤지만 찾을 수 없었습니다.또한 명백한 이유로 코드의 제한 9999999 솔루션을 사용하지 않는 것이 좋습니다.

주의: 조인 내에서 쿼리만 실행하는 경우에도 예상대로 작동합니다.

주의: MariaDB 10.1에서도 동일한 테스트를 실행했습니다.

explain row출력은 몇 행에 히트할지를 추측하는 것에 불과합니다.업데이트와 함께 재설정된 통계 데이터를 기반으로 합니다.그리고 어제 오후 9시보다 더 오래된 기존 행이 몇 개인지 추측해야 한다면 "일부 행"보다는 "모든 행"에 가깝습니다.'limit 9999999'가 다른 행 수를 표시하는 이유는 같습니다.이 경우 제한은 영향을 미칠 것으로 추측됩니다.mysql에서는 행의 절반(참일 경우 이상한 일치)이 될 것으로 추측됩니다.물론 9999999는 제한 값을 실제로는 제한하지 않습니다.500k 행이 있습니다. "="의 경우 "1"도 추측일 뿐입니다(일반적으로 1보다 0이 많거나 더 많을 수도 있음).

이 견적은 올바른 실행 계획을 선택하는 데 도움이 됩니다.이 견적이 틀리는 것은 잘못된 실행 계획을 선택하는 경우 문제일 뿐입니다.실행 계획은 정상입니다.그렇지 않으면 실행할 수 있는 옵션이 별로 없습니다.예상대로 동작합니다.created_at 의 .결합을 에 이 값은 할 수 .tableA7에서 .)(옵티마이저는 실제로 5.7로 변경되어 있습니다만, 여기에서는 효과가 없습니다.)

실제 쿼리일 경우 이전보다 느려질 이유는 없습니다(이 쿼리에 대해서만 해당됩니다.캐싱 전략, 버퍼 크기 등 간접적인 영향을 미칠 수 있는 일반적인 퍼포먼스 옵션이 많이 있지만 표준 옵션에서는 영향을 주지 않습니다).

않은 , 을 사용할 수 있습니다.TableB서브쿼리에서는 (어떤 중요한 것이 질문에서 "증명"되어 있는지 추측하기 어렵기 때문에 실제 표에 액세스해야 합니다) 데이터가 어떻게 구조화되어 있는지에 따라(또는 어떤 순서로 추가되었는지) 다를 수 있습니다. 도 한번 시도해보세요.Optimize table TableB테이블과 인덱스를 신선하고 새로운 것으로 만들기 위해 문제될 것은 없습니다(단, 잠시 동안 테이블을 잠급니다).

5열을 할 수 mysql 5.7을 .time as DATE_FORMAT(created_at,'%H:%i:00')계산은 불필요합니다.색인에 해서 더 될 것 요.그러면 더 이상 정렬을 하지 않아도 됩니다.block nested join그러나 실제 쿼리와 사용 빈도에 따라 달라질 수 있습니다(스팸 인덱스를 사용하면 오버헤드가 증가하고 공간이 사용됨).

MySQL 5.7에서는 가능하면 파생 테이블(FROM 절의 하위 쿼리)이 외부 쿼리에 병합됩니다.서브쿼리 결과가 임시 테이블에 저장되지 않도록 하기 때문에 일반적으로 이 방법이 유리합니다.단, 쿼리의 경우 MySQL 5.6은 가입 실행에 사용할 수 있는 인덱스를 이 임시 테이블에 만듭니다.

Marge 쿼리의 문제는 열이 함수의 파라미터인 경우 TableB.created_at의 인덱스를 사용할 수 없다는 것입니다.조인 왼쪽에 있는 컬럼으로 변환하도록 쿼리를 변경할 수 있는 경우 인덱스를 사용하여 오른쪽에 있는 테이블에 액세스할 수 있습니다.예를 들어 다음과 같습니다.

   select * from TableA as A
            left join
            (
                select created_at as time
                FROM TableB
                WHERE created_at < DATE_ADD(CURDATE(), INTERVAL -3 HOUR) 
            )
            as V ON V.time = func(A.time)

또는 왼쪽 조인 대신 내부 조인(inner join)을 사용할 수 있는 경우 MySQL은 조인 순서를 반대로 하여 테이블A의 인덱스를 만들 수 있습니다.가입에 시간을 사용할 수 있습니다.

하위 쿼리가 LIMIT을 사용하는 경우 병합할 수 없습니다.따라서 LIMIT을 사용하면 MySQL 5.6과 동일한 쿼리 플랜을 얻을 수 있습니다.

JOINLEFT JOIN네, 네, 네, 네.

JOIN ( SELECT ... )과 5몇 되었지만, 심플한 5.6으로 하는 것이 좋습니다.JOIN.

당신의 시간 표현은 어제 오후 9시로 이어지는데, "3시간 전"을 말하는 건가요?

원하는 결과를 얻을 수 있고 실행 속도가 빨라지는지 확인합니다.

select  A.*, DATE_FORMAT(B.created_at,'%H:%i:00') as `time`
    from  TableA as A
    JOIN  TableB as B  ON B.time = A.time
    WHERE  B.created_at < NOW() - INTERVAL 3 HOUR   -- (assuming "3 hours ago")

5.은 「 모델옵티마이저를 하고 있습니다.5.6 5 5.7 5 5.7 " " 용 " ' " ' " ' " ' " ' " ' " ' " ' " ' " ' " ' ' ' , 。그러나 특정 질문으로 인해 옵티마이저가 적절한 비용을 책정하는 것은 사실상 불가능합니다.은 더 5.6에서 것 요.EXPLAIN은 더 5.7은 더 나쁜 경우에 일어났어요.쿼리를 단순화함으로써 두 옵티마이저 모두 쿼리를 보다 빠르게 실행할 수 있는 가능성이 높아졌다고 생각합니다.

다음 인덱스가 필요합니다.

B:  INDEX(time, created_at) -- in that order
A:  INDEX(time)

언급URL : https://stackoverflow.com/questions/37481820/mysql-join-not-filtering-on-where-clause-with-operators-since-moving-from-m