MySQL "IN" 쿼리는 하위 쿼리에서는 매우 느리지만 명시적 값에서는 빠릅니다.

MySQL "IN" 쿼리는 하위 쿼리에서는 매우 느리지만 명시적 값에서는 빠릅니다.

MySQL 쿼리(Ubu 10.04, Innodb, Core i7, 16Gb RAM, SSD 드라이브, MySQL 매개 변수 최적화):

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (SELECT l.id FROM em_link l WHERE l.campaignid = '2900' AND l.link != 'open')

em_link_data 테이블에는 약 700만 개의 행이 있고 em_link에는 몇 천 개의 행이 있습니다.이 쿼리를 완료하는 데 약 18초가 소요됩니다.그러나 서브쿼리의 결과를 대입하여 이렇게 하면 다음과 같습니다.

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (24899,24900,24901,24902);

그러면 쿼리가 1밀리초 미만으로 실행됩니다.하위 쿼리만 1ms 미만으로 실행되고 열 링크 ID는 인덱싱됩니다.

쿼리를 조인으로 다시 작성하면 역시 1ms 미만입니다.서브쿼리가 포함된 "IN" 쿼리는 왜 그렇게 느리고 값이 포함된 채로 왜 그렇게 빠릅니까?쿼리(소프트웨어 구입)를 다시 쓸 수 없기 때문에 쿼리 속도를 높일 수 있는 수정이나 힌트가 있으면 좋겠습니다!어떤 도움이든 감사히 받겠습니다.

서브쿼리는 평가할 때마다 실행됩니다(모든 RDBMs가 아닌 MySQL에서). 즉, 기본적으로 7백만 개의 쿼리를 실행하고 있는 것입니다!JOIN을 사용하면 가능하면 이 값이 1로 줄어듭니다.색인을 추가하면 색인의 성능이 향상되더라도 이를 실행 중입니다.

네.IN서브쿼리는 느립니다.대신 조인을 사용합니다.

SELECT
COUNT(DISTINCT subscriberid)
FROM em_link_data JOIN em_link ON em_link_data.linkid=em_link.id
WHERE em_link.campaignid = '2900' AND em_link.link != 'open'

그리고 다음에 대한 인덱스를 정의했는지 확인합니다.em_link_data.linkid그리고.em_link.id.

문제는 MySQL이 외부에서 내부로 쿼리를 실행하는 반면 하위 쿼리가 한 번 완료된 후 결과가 외부 쿼리의 WHERE 식으로 전달된다고 생각할 수 있다는 것입니다(MySQL 설명서 참조).

쿼리를 다시 쓸 수 없는 경우 다음 최적화 작업을 수행해야 합니다.

• 색인을 추가하다campaignid그리고.link좌절한 것처럼With Forms Designer가 말했습니다.
• 하위 쿼리가 인덱스를 올바르게 사용하는지 확인합니다.EXPLAIN SELECT ...
• 쿼리 캐시를 활성화하고 조정하면 하위 쿼리가 여러 번 호출되는 속도가 빨라집니다.

MySQL 프록시를 설치하고 쿼리를 가로채고 조인을 사용하기 위해 다시 쓰는 작은 스크립트를 작성하는 것도 방법입니다.

하위 쿼리가 빠르다면 campaignid와 링크는 완전히 색인화됩니다.l.id 은 PK이며 클러스터되어 있으므로 속도가 빠릅니다.하지만 제 기억으로는 (이 주제를 지난번에 확인했을 때부터)mysql은 성능을 향상시키기 위해 인덱스 종류의 하위 쿼리 결과를 사용하기 위해 "in" 하위 쿼리에 대한 내부 최적화에 대해 설명하고 또한 "IN"의 왼쪽에 캐시를 사용하여 하위 쿼리 내부로 더 빨리 끌어 들입니다. 인덱스가 true로 설정된 경우 캐시가 아닌 내부 조인 또는 "IN"을 사용하기 위해 차이가 없어야 하며 캐시 때문일 수 있습니다.e 문제와 방대한 데이터.http://dev.mysql.com/doc/internals/en/transformation-scalar-in.html

소프트웨어의 상황은 모르겠지만, INNER JOIN을 사용할 수 있고 외부 쿼리의 WHERE 절에 있는 IN 절 앞에 몇 가지 추가 정의가 있는 경우, 해당 절을 임시 INNER JOIN을 통해 주 INNER JOIN 앞으로 이동하고 순차적으로 "WHERE" 절과 유사하게 동작하도록 하십시오.es 이와 같은 JOIN의 교차 비교 횟수:

SELECT ... FROM t
INNER JOIN (SELECT 1) AS tmp ON t.asd=23
INNER JOIN t2 ON ...

정상 결합 조회와 임시 결합 조회의 표본 비교 : 1000 * 1000 > 1000 + (100 * 1000)

또한 하위 쿼리는 일정한 vals에 의해 필터링되는 것 같습니다. 따라서 만약 나였다면 나는 결과 집합을 생성하는 하위 쿼리에 절을 넣고 JOIN의 비교 횟수를 이렇게 줄일 것입니다.

SELECT ... FROM t
INNER JOIN (SELECT ... FROM t2 WHERE constant clauses) AS tbl2 ON ...

어쨌든 "IN" 쿼리에서 하위 쿼리에 있는 테이블의 열을 외부 쿼리에 있는 테이블의 열과 비교하려면 양쪽의 열이 (복합 인덱스와 관련하여) 정확하게 인덱싱되어야 하지만 여전히 캐시 문제일 수 있습니다.

편집 : 또한 궁금한 것이 있었습니다: l.jpid, l.link 및 l.id 에서 합성 지수를 만드는 것이 의미가 있습니까?

언급URL : https://stackoverflow.com/questions/5018284/mysql-in-queries-terribly-slow-with-subquery-but-fast-with-explicit-values