MySQL에서 STRAIGHT_JOIN을 사용하는 경우

MySQL에서 STRAIGHT_JOIN을 사용하는 경우

---

작업 중이었던 상당히 복잡한 쿼리를 실행하는 데 8 초가 걸렸습니다. EXPLAIN은 이상한 테이블 순서를 보여 주었고 FORCE INDEX 힌트를 사용해도 인덱스가 모두 사용되지 않았습니다. STRAIGHT_JOIN 조인 키워드를 발견하고 일부 INNER JOIN 키워드를이 키워드로 교체하기 시작했습니다. 나는 상당한 속도 향상을 발견했습니다. 결국이 쿼리에 대해 모든 INNER JOIN 키워드를 STRAIGHT_JOIN으로 바꾸었고 이제 .01 초 안에 실행됩니다.

제 질문은 언제 STRAIGHT_JOIN을 사용하고 언제 INNER JOIN을 사용합니까? 좋은 쿼리를 작성하는 경우 STRAIGHT_JOIN을 사용하지 않을 이유가 있습니까?

---

정당한 이유없이 STRAIGHT_JOIN을 사용하지 않는 것이 좋습니다. 내 경험으로는 MySQL 쿼리 최적화 프로그램이 내가 원하는 것보다 더 자주 잘못된 쿼리 계획을 선택하지만 일반적으로이를 우회해야 할만큼 충분하지 않다는 것입니다. 항상 STRAIGHT_JOIN을 사용한다면 그렇게 할 것입니다.

내 권장 사항은 모든 쿼리를 일반 JOIN으로 남겨 두는 것입니다. 한 쿼리가 차선의 쿼리 계획을 사용하고 있음을 발견하면 먼저 쿼리를 다시 작성하거나 재구성하여 최적화 프로그램이 더 나은 쿼리 계획을 선택하는지 확인하는 것이 좋습니다. 또한 적어도 innodb의 경우 인덱스 통계가 최신 상태가 아닌지 확인하십시오 ( ANALYZE TABLE ). 이로 인해 최적화 프로그램이 잘못된 쿼리 계획을 선택할 수 있습니다. 일반적으로 옵티 마이저 힌트는 마지막 수단이어야합니다.

쿼리 힌트를 사용하지 않는 또 다른 이유는 데이터 분포가 시간이 지남에 따라 변경되거나 테이블이 커짐에 따라 인덱스 선택성이 변경 될 수 있기 때문입니다. 현재 최적 인 쿼리 힌트는 시간이 지남에 따라 차선이 될 수 있습니다. 그러나 최적화 프로그램은 현재 오래된 힌트 때문에 쿼리 계획을 조정할 수 없습니다. 옵티마이 저가 결정을 내 리도록 허용하면 더 유연하게 유지됩니다.

---

에서 MySQL을 참조 가입 :

"STRAIGHT_JOIN은 왼쪽 테이블이 항상 오른쪽 테이블보다 먼저 읽힌다는 점을 제외하면 JOIN과 유사합니다. 이것은 조인 최적화 프로그램이 테이블을 잘못된 순서로 배치하는 경우에 사용할 수 있습니다."

---

MySQL은 복잡한 쿼리에서 조인 순서를 선택하는 데 꼭 필요한 것은 아닙니다. 복잡한 쿼리를 straight_join으로 지정하면 쿼리가 지정된 순서대로 조인을 실행합니다. 테이블을 최소 공통 분모로 먼저 배치하고 straight_join을 지정하면 쿼리 성능을 향상시킬 수 있습니다.

---

다음은 최근에 직장에서 발생한 시나리오입니다.

A, B, C의 세 테이블을 고려하십시오.

A에는 3,000 개의 행이 있습니다. B에는 300,000,000 개의 행이 있습니다. C에는 2,000 개의 행이 있습니다.

외래 키는 B (a_id), B (c_id)로 정의됩니다.

다음과 같은 쿼리가 있다고 가정합니다.

select a.id, c.id
from a
join b on b.a_id = a.id
join c on c.id = b.c_id

내 경험상 MySQL은이 경우 C-> B-> A로 이동할 수 있습니다. C는 A보다 작고 B는 거대하며 모두 동등 조인입니다.

문제는 MySQL이 (C.id와 B.c_id) 대 (A.id와 B.a_id) 사이의 교차점 크기를 반드시 고려하지 않는다는 것입니다. B와 C 사이의 조인이 B만큼 많은 행을 반환하는 경우 매우 좋지 않은 선택입니다. A로 시작하여 B를 A만큼 많은 행으로 필터링했다면 훨씬 더 나은 선택이었을 것입니다. straight_join이 명령을 다음과 같이 강제하는 데 사용할 수 있습니다.

select a.id, c.id
from a
straight_join b on b.a_id = a.id
join c on c.id = b.c_id

이제 a이전에에 가입해야합니다 b.

일반적으로 결과 집합의 행 수를 최소화하는 순서로 조인을 수행하려고합니다. 따라서 작은 테이블로 시작하여 결과 조인도 작아 지도록 조인하는 것이 이상적입니다. 작은 테이블로 시작하여 더 큰 테이블에 결합하면 큰 테이블만큼 커지면 모든 것이 배 모양으로 변합니다.

하지만 통계에 따라 다릅니다. 데이터 분포가 변경되면 계산이 변경 될 수 있습니다. 또한 결합 메커니즘의 구현 세부 사항에 따라 다릅니다.

필자가 본 최악의 경우는 필수 straight_join이거나 공격적인 인덱스 힌팅을 제외하고는 모두 가벼운 필터링을 사용하여 엄격한 정렬 순서로 많은 데이터에 페이지를 매기는 쿼리입니다. MySQL은 정렬보다 필터 및 조인에 인덱스를 사용하는 것을 강력히 선호합니다. 이는 대부분의 사람들이 전체 데이터베이스를 정렬하려고하는 것이 아니라 쿼리에 응답하는 행의 제한된 하위 집합을 가지고 있기 때문에 이치에 맞습니다. 그리고 제한된 하위 집합을 정렬하는 것이 정렬 여부에 관계없이 전체 테이블을 필터링하는 것보다 훨씬 빠릅니다. 아니. 이 경우 인덱싱 된 열이있는 테이블 바로 뒤에 스트레이트 조인을 배치하여 고정 된 항목을 정렬하고 싶었습니다.

---

STRAIGHT_JOIN, using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

---

I will tell you why I had to use STRAIGHT_JOIN :

• I had a performance issue with a query.
• Simplifying the query, the query was suddently more efficient
• Trying to figure out which specific part was bringing the issue, I just couldn't. (2 left joins together were slow, and each one was independently fast)
• I then executed the EXPLAIN with both slow and fast query (addind one of the left joins)
• Surprisingly, MySQL changed entirely the JOIN orders between the 2 queries.

Therefore I forced one of the joins to be straight_join to FORCE the previous join to be read first. This prevented MySQL to change the execution order and worked like a charm !

---

If your query ends with ORDER BY... LIMIT..., it may be optimal to reformulate the query to trick the optimizer into doing the LIMIT before the JOIN.

(This Answer does not apply only to the original question about STRAIGHT_JOIN, nor does it apply to all cases of STRAIGHT_JOIN.)

Starting with the example by @Accountantم, this should run faster in most situations. (And it avoids needing hints.)

SELECT  whatever
    FROM  ( SELECT id FROM sales
                ORDER BY  date, id
                LIMIT  50
          ) AS x
    JOIN  sales   ON sales.id = x.id
    JOIN  stores  ON sales.storeId = stores.id
    ORDER BY  sales.date, sales.id;

Notes:

• First, 50 ids are fetched. This will be especially fast with INDEX(date, id).
• Then the join back to sales lets you get only 50 "whatevers" without hauling them around in a temp table.
• since a subquery is, by definition, unordered, the ORDER BY must be repeated in the outer query. (The Optimizer may find a way to avoid actually doing another sort.)
• Yes, it is messier. But it is usually faster.

I am opposed to using hits because "Even if it is faster today, it may fail to be faster tomorrow."

---

In my short experience, one of the situations that STRAIGHT_JOIN has reduced my query from 30 seconds to 100 milliseconds is that the first table in the execution plan was not the table that has the order by columns

-- table sales (45000000) rows
-- table stores (3) rows
SELECT whatever
FROM 
    sales 
    INNER JOIN stores ON sales.storeId = stores.id
ORDER BY sales.date, sales.id 
LIMIT 50;
-- there is an index on (date, id)

IF the optimizer chooses to hit stores first it will cause Using index; Using temporary; Using filesort because

if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

source

here the optimizer needs a little help by telling him to hit sales first using

sales STRAIGHT_JOIN stores

---

I know it's a bit old but here's a scenario, I've been doing batch script to populate a certain table. At some point, the query ran very slow. It appears that the join order was incorrect on particular records:

• In correct order

• Incrementing the id by 1 messes up the order. Notice the 'Extra' field

• Using straight_join fixes the issue

Incorrect order runs for about 65 secs while using straight_join runs in milliseconds

---

--use 120s, 18 million data
    explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d, tvassist_taid_all t
    WHERE d.taid = t.taid
      AND t.client_version >= '21004007'
      AND t.utdid IS NOT NULL
      AND d.recommend_day = '20170403'
    LIMIT 0, 10000

--use 3.6s repalce by straight join
 explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d
    STRAIGHT_JOIN 
      tvassist_taid_all t on d.taid = t.taid 
    WHERE 
     t.client_version >= '21004007'
       AND d.recommend_day = '20170403'

      AND t.utdid IS NOT NULL  
    LIMIT 0, 10000

참고URL : https://stackoverflow.com/questions/512294/when-to-use-straight-join-with-mysql