[mysql] MySQL-SELECT WHERE 필드 IN (하위 쿼리)-왜 느리게?

Program / 글쓴이

검사하려는 데이터베이스에 두 개의 중복 항목이 있으므로 중복 항목을 확인하기 위해 수행 한 작업은 다음과 같습니다.

SELECT relevant_field
FROM some_table
GROUP BY relevant_field
HAVING COUNT(*) > 1

이렇게하면 related_field가있는 모든 행을 두 번 이상 가져옵니다. 이 쿼리는 실행하는 데 밀리 초가 걸립니다.

이제 각 중복 항목을 검사하고 싶었으므로 위 쿼리에서 related_field를 사용하여 some_table의 각 행을 선택할 수 있다고 생각했습니다.

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)

이것은 어떤 이유로 외부 적으로 느리게 나타납니다 (분이 걸립니다). 그것을 느리게 만들기 위해 여기서 정확히 무슨 일이 일어나고 있습니까? related_field가 색인됩니다.

결국 첫 번째 query에서 “temp_view”보기 (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1)를 만든 다음 두 번째 쿼리를 다음과 같이 만들었습니다.

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM temp_view
)

그리고 그것은 잘 작동합니다. MySQL은 몇 밀리 초 안에이를 수행합니다.

무슨 일이 일어나고 있는지 설명 할 수있는 SQL 전문가가 있습니까?

답변

이 질문을 다시 작성하십시오

SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id  /* list a unique sometable field here*/
HAVING COUNT(*) > 1

st2.relevant_field그렇지 않으면 having절에 오류가 발생 하기 때문에 선택에 있어야 한다고 생각 하지만 100 % 확신 할 수는 없습니다.

IN하위 쿼리와 함께 사용하지 마십시오 . 이것은 매우 느립니다. 고정 된 값 목록
만 사용하십시오 IN.

더 많은 팁

  1. 쿼리 속도를 높이려면 SELECT *실제로 필요한 필드 만 선택 하지 마십시오 .
  2. relevant_field동등 조인 속도를 높이려면 인덱스가 있어야합니다 .
  3. group by기본 키 를 확인하십시오 .
  4. 당신은 이노에있는 경우 와 만 인덱스 필드를 선택 (그리고 상황이 너무 복잡하지 않은) 의 MySQL은 인덱스를 사용하여 쿼리를 해결할 수보다 일의 길을 속도.

IN (select 쿼리의 90 %를위한 일반 솔루션

이 코드를 사용하십시오

SELECT * FROM sometable a WHERE EXISTS (
  SELECT 1 FROM sometable b
  WHERE a.relevant_field = b.relevant_field
  GROUP BY b.relevant_field
  HAVING count(*) > 1)

답변

하위 쿼리는 상관 된 쿼리이므로 각 행에 대해 실행됩니다. 하위 쿼리에서 다음과 같이 모든 항목을 선택하여 상관 쿼리를 상관되지 않은 쿼리로 만들 수 있습니다.

SELECT * FROM
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
) AS subquery

최종 쿼리는 다음과 같습니다.

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT * FROM
    (
        SELECT relevant_field
        FROM some_table
        GROUP BY relevant_field
        HAVING COUNT(*) > 1
    ) AS subquery
)

답변

서브 쿼리 및 조인

http://www.scribd.com/doc/2546837/New-Subquery-Optimizations-In-MySQL-6

답변

SELECT st1.*
FROM some_table st1
inner join
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)st2 on st2.relevant_field = st1.relevant_field;

내 데이터베이스 중 하나에서 쿼리를 시도했으며 하위 쿼리에 대한 조인으로 다시 작성했습니다.

이것은 훨씬 빨리 작동했습니다. 사용해보십시오!

답변

이 시도

SELECT t1.*
FROM
 some_table t1,
  (SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT (*) > 1) t2
WHERE
 t1.relevant_field = t2.relevant_field;

답변

www.prettysql.net으로 느린 SQL 쿼리를 다시 포맷했습니다.

SELECT *
FROM some_table
WHERE
 relevant_field in
 (
  SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT ( * ) > 1
 );

쿼리와 하위 쿼리 모두에서 테이블을 사용할 때 항상 다음과 같이 별칭을 지정해야합니다.

SELECT *
FROM some_table as t1
WHERE
 t1.relevant_field in
 (
  SELECT t2.relevant_field
  FROM some_table as t2
  GROUP BY t2.relevant_field
  HAVING COUNT ( t2.relevant_field ) > 1
 );

도움이 되나요?

답변

먼저 중복 행을 찾고 행 수를 찾는 횟수는 다음과 같이 번호별로 정렬됩니다.

SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

스 니펫 확장

그런 다음 테이블을 작성하고 결과를 삽입하십시오.

create table CopyTable
SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

스 니펫 확장

마지막으로 중복 행을 삭제합니다 .No는 시작 0입니다. 각 그룹의 첫 번째 숫자를 제외하고 모든 중복 행을 삭제합니다. 

delete from  CopyTable where No!= 0;

스 니펫 확장