여러 테이블의 SQL과 내부 조인의 차이점

Inner Join 작성 방법은 다음과 같습니다

<code>SELECT
    B.dict_data_name,
    SUM(A.pv) AS pv
FROM
    shw_mo_health_news A
INNER JOIN bas_dict_data B ON A.third_name_dictid = B.item_id
WHERE
    A.class_level = 3
AND B.class_id = 1012
AND A.collect_date >= '2016-04-01'
AND A.collect_date <= '2016-05-31'
GROUP BY
    A.third_name_dictid
ORDER BY
    pv DESC;</code>

사용자의 실제 쿼리 시간은 약 0.6초

설명과 함께 보기:

---

여러 테이블에서 쓰는 방법은 다음과 같습니다

<code>SELECT
    B.dict_data_name,
    A.PV
FROM
    (
        SELECT
            hn.third_name_dictid,
            SUM(hn.pv) AS PV
        FROM
            shw_mo_health_news hn
        WHERE
            hn.class_level = 3
        AND hn.collect_date >= '2016-04-01'
        AND hn.collect_date <= '2016-05-31'
        GROUP BY
            hn.third_name_dictid
    ) A,
    (
        SELECT
            dd.item_id,
            dd.dict_data_name
        FROM
            bas_dict_data dd
        WHERE
            dd.class_id = 1012
    ) B
WHERE
    A.third_name_dictid = B.item_id
ORDER BY
    PV DESC</code>

실제 시간은 약 0.03초

보기 설명

---

이 두 가지 쿼리 방법의 효율성에 왜 그렇게 큰 차이가 있습니까?
인터넷에서 많은 사람들이 이 두 가지 작성 방법의 효율성이 거의 동일하다고 말하지만 제 것은 0.6이고 다른 것은 0입니다. 0.03, 둘의 차이는 아직 꽤 크네요. 제가 작성한 SQL문에 문제가 있어서 그런걸까요?

답글 내용:

Inner Join 작성 방법은 다음과 같습니다

<code>SELECT
    B.dict_data_name,
    SUM(A.pv) AS pv
FROM
    shw_mo_health_news A
INNER JOIN bas_dict_data B ON A.third_name_dictid = B.item_id
WHERE
    A.class_level = 3
AND B.class_id = 1012
AND A.collect_date >= '2016-04-01'
AND A.collect_date <= '2016-05-31'
GROUP BY
    A.third_name_dictid
ORDER BY
    pv DESC;</code>

사용자의 실제 쿼리 시간은 약 0.6초

설명과 함께 보기:

---

여러 테이블에서 쓰는 방법은 다음과 같습니다

<code>SELECT
    B.dict_data_name,
    A.PV
FROM
    (
        SELECT
            hn.third_name_dictid,
            SUM(hn.pv) AS PV
        FROM
            shw_mo_health_news hn
        WHERE
            hn.class_level = 3
        AND hn.collect_date >= '2016-04-01'
        AND hn.collect_date <= '2016-05-31'
        GROUP BY
            hn.third_name_dictid
    ) A,
    (
        SELECT
            dd.item_id,
            dd.dict_data_name
        FROM
            bas_dict_data dd
        WHERE
            dd.class_id = 1012
    ) B
WHERE
    A.third_name_dictid = B.item_id
ORDER BY
    PV DESC</code>

실제 시간은 약 0.03초

보기 설명

---

이 두 가지 쿼리 방법의 효율성에 왜 그렇게 큰 차이가 있습니까?
인터넷에서 많은 사람들이 이 두 가지 작성 방법의 효율성이 거의 동일하다고 말하지만 내 것은 0.6이고 다른 것은 입니다. 0.03, 둘의 차이가 꽤 크네요. 제가 작성한 SQL 문에 문제가 있어서 그런 건가요?

사용한 조건에 인덱스가 없어서 연결이 끊어진 것 같아요. 필드가 적절하게 색인화되면 MySQL이 자동으로 SQL 문을 최적화하므로 최종 쿼리 문도 동일하고 성능도 동일합니다. 이때 소위 최적화는 존재하지 않습니다. , 최종 쿼리 문은 기본적으로 제출한 SQL과 동일합니다.
시도해 보세요

<code>SELECT
    B.dict_data_name,
    SUM(A.pv) AS pv
FROM
    shw_mo_health_news A
INNER JOIN bas_dict_data B ON A.class_level = 3 and A.collect_date >= '2016-04-01'
AND A.collect_date <= '2016-05-31' and B.class_id=1012 and  A.third_name_dictid = B.item_id

GROUP BY
    A.third_name_dictid
ORDER BY
    pv DESC;</code>

공연이 눈에 띄게 달라질 것 같아요.

동일하게, SQL-89와 SQL-92의 다른 사양에 속합니다. https://en.wikipedia.org/wiki...

를 참조하세요.

---

관련 질문과 답변을 찾았는데 그 답변 중 하나가 바로 귀하의 질문입니다 https://community.microstrate...

두 번째 SQL에는 하위 쿼리로 인해 추가 오버헤드(임시 테이블)가 발생합니다.
두 번째 SQL이 첫 번째 SQL보다 나은 이유는 무엇인가요? 실행 계획을 보면 아무것도 보이지 않고 그냥 예외인 것처럼 느껴집니다

이론적으로 하위 쿼리와 JOIN 사이에는 본질적인 차이가 없으며 쿼리 분석기를 통해 합리적으로 최적화한 후에는 동일해야 합니다. 그러나 때로는 데이터베이스의 일부 버전이 하위 쿼리를 더 잘 지원하고 일부는 JOIN 더 잘 지원하는 것은 쿼리 분석기의 다양한 결함으로 인해 발생합니다. MySQL의 경우 내가 본 대부분의 하위 쿼리 버전은 JOIN과 동일하지만 하위 쿼리가 WHERE에 있는 경우 주의하세요. 예:

<code>-- 查询1
SELECT * FROM table_a
WHERE A IN (
    SELECT A FROM table_b
    WHERE B = 'x'
)
-- 查询2
SELECT table_a.* FROM table_a A
    INNER JOIN table_b B ON a.A = b.A
WHERE B.B = 'x'</code>

이것은 일반적인 MySQL 쿼리 분석기 실패 시나리오입니다. 이론적으로는 table_a의 A 필드에 인덱스가 있을 때 쿼리 1과 쿼리 2가 동일해야 하지만 실제로는 MySQL 5.x 버전에서 쿼리 2의 성능이 쿼리 1보다 훨씬 좋습니다. 이 문제는 Stackoverflow에서 논의되었으며 거의 ​​10년 동안 지속된 알려진 문제입니다. 수정사항을 얻으려면 6.0.x로 업그레이드해야 합니다. 따라서 어느 것이 더 나은지에 대한 질문은 결론을 내리기 전에 explain 직접 결과를 확인하는 것이 가장 신뢰할 수 있는 방법입니다.
두 쿼리의 경우 실제로 실행 계획이 다릅니다. 두 번째 쿼리의 소비량이 더 많고, 추가 두 단계에서 얻은 데이터의 양이 그리 크지 않기 때문에 시간도 거의 동일합니다. . 게다가 이 두 쿼리는 실제로 동일하지 않으며 비교할 수 없습니다.