서브쿼리 vs 조인

서브쿼리 vs 조인

다른 회사로부터 상속받은 애플리케이션의 느린 섹션을 수정하여 다음과 같은 하위 쿼리 대신 내부 조인(inner join)을 사용했습니다.

WHERE id IN (SELECT id FROM ...)

리팩터링된 쿼리는 약 100배 빠르게 실행됩니다.(~50초~0.3) 개선을 기대했는데, 왜 그렇게 급격한지 설명해주실 분?where 절에서 사용된 열은 모두 색인화되었습니다.SQL은 where 절의 쿼리를 행별로 1회 실행합니까?

업데이트 - 결과 설명:

차이는 "where id in()" 쿼리의 두 번째 부분에 있습니다.

2   DEPENDENT SUBQUERY  submission_tags ref st_tag_id   st_tag_id   4   const   2966    Using where

vs 조인 포함 인덱스된 행 1개:

    SIMPLE  s   eq_ref  PRIMARY PRIMARY 4   newsladder_production.st.submission_id  1   Using index

"상관된 하위 쿼리"(즉, 포함된 쿼리의 행에서 얻은 값에 따라 조건이 달라지는 하위 쿼리)는 각 행에 대해 한 번 실행됩니다.비상관 서브쿼리(where 조건이 포함 쿼리와 독립되어 있는 서브쿼리)는 처음에 한 번 실행됩니다.SQL 엔진에 의해 이 구별이 자동으로 이루어집니다.

하지만, 그래, 설명 계획은 더러운 세부사항을 알려줄거야.

조인(join)은 인덱스에서 수행되지만 하위 쿼리는 각 행에 대해 한 번 실행됩니다.

다음은 MySQL 6.0에서 하위 쿼리를 평가하는 방법의 예입니다.

새로운 옵티마이저는 이러한 종류의 서브쿼리를 결합으로 변환합니다.

쿼리가 데이터셋에 대해 실행되기 전에 쿼리 옵티마이저는 결과 세트에서 가능한 한 많은 튜플(행)을 제거할 수 있는 방식으로 쿼리를 구성하려고 시도합니다.하위 쿼리(특히 불량 쿼리)를 사용하는 경우 외부 쿼리가 실행될 때까지 결과 집합에서 튜플을 제거할 수 없습니다.

쿼리를 보지 않고서는 원본의 어떤 점이 그렇게 나쁘다고 말하기는 어렵지만, 옵티마이저가 더 나은 것을 만들 수 없을 것 같습니다.'desplain'을 실행하면 데이터를 검색하기 위한 옵티마이저 방법이 표시됩니다.

각 쿼리의 쿼리 계획을 확인합니다.

Where in과 Join은 일반적으로 동일한 실행 계획을 사용하여 구현할 수 있으므로 일반적으로 이들 간에 변경 속도가 향상되지 않습니다.

Optimizer는 잘하지 못했습니다.일반적으로 이들은 아무런 차이 없이 변환될 수 있으며 최적기는 이를 수행할 수 있습니다.

This question is somewhat general, so here's a general answer:

Basically, queries take longer when MySQL has tons of rows to sort through.

Do this:

Run an EXPLAIN on each of the queries (the JOIN'ed one, then the Subqueried one), and post the results here.

I think seeing the difference in MySQL's interpretation of those queries would be a learning experience for everyone.

The where subquery has to run 1 query for each returned row. The inner join just has to run 1 query.

Usually its the result of the optimizer not being able to figure out that the subquery can be executed as a join in which case it executes the subquery for each record in the table rather then join the table in the subquery against the table you are querying. Some of the more "enterprisey" database are better at this, but they still miss it sometimes.

With a subquery, you have to re-execute the 2nd SELECT for each result, and each execution typically returns 1 row.

With a join, the 2nd SELECT returns a lot more rows, but you only have to execute it once. The advantage is that now you can join on the results, and joining relations is what a database is supposed to be good at. For example, maybe the optimizer can spot how to take better advantage of an index now.

It isn't so much the subquery as the IN clause, although joins are at the foundation of at least Oracle's SQL engine and run extremely quickly.

The subquery was probably executing a "full table scan". In other words, not using the index and returning way too many rows that the Where from the main query were needing to filter out.

Just a guess without details of course but that's the common situation.

Taken from the Reference Manual (14.2.10.11 Rewriting Subqueries as Joins):

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.

So subqueries can be slower than LEFT [OUTER] JOINS.

ReferenceURL : https://stackoverflow.com/questions/141278/subqueries-vs-joins