MySQL JOIN 원리에 대한 자세한 소개

1. Join 구문 개요

join은 여러 테이블의 필드를 연결하는 데 사용됩니다. 구문은 다음과 같습니다.

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON Conditiona

table1: 왼쪽 테이블: 오른쪽 테이블 .

JOIN은 기능에 따라 크게 다음 세 가지로 구분됩니다.

INNER JOIN(내부 조인, 또는 등가 조인): 두 테이블에서 연결 일치 관계가 있는 레코드를 얻습니다.

LEFT JOIN(왼쪽 조인): 왼쪽 테이블(table1)의 전체 레코드를 가져옵니다. 즉, 오른쪽 테이블(table2)에 해당하는 일치 레코드가 없습니다.

RIGHT JOIN(오른쪽 조인): LEFT JOIN과 달리 오른쪽 테이블(table2)의 완전한 레코드를 얻습니다. 즉, 왼쪽 테이블(table1)에는 일치하는 해당 레코드가 없습니다.

참고: mysql은 Full Join을 지원하지 않지만 UNION 키워드를 사용하여 LEFT JOIN과 RIGHT JOIN을 결합하여 FULL Join을 시뮬레이션할 수 있습니다.

먼저 실험의 두 테이블을 살펴보세요.

Comments 테이블, 총 개수 of 행은 28856

테이블 comments_for, 총 행 수는 57, comments_id가 색인화되고 ID 열이 기본 키입니다.

위 두 테이블은 테스트의 기초입니다. 그런 다음 comments_for 테이블 comments_id가 색인화되어 있으며 ID가 기본 키입니다.

최근 회사의 한 개발자가 MySQL JOIN의 JOIN 문제에 대해 문의했습니다. MySQL JOIN에 대한 이해가 그다지 깊지 않아서 많은 문서를 확인했고 마침내 InsideMySQL 공식 계정에 관한 두 개의 기사를 보았습니다. JOIN 분석 결과 매우 잘 작성되었다고 생각됩니다. 실제 JOIN 테스트에 대해 공유하겠습니다. 세 가지 유형으로 나누어지는 MySQL의 JOIN 알고리즘을 먼저 소개하겠습니다. (출처: InsideMySQL):

MySQL은 이를 지원하는 다른 상용 데이터베이스와 달리 Nested-Loop Join(중첩 루프 링크)이라는 하나의 JOIN 알고리즘만 지원합니다. 병합 조인이지만 MySQL의 Nested-Loop Join(중첩 루프 링크)에는 MySQL이 JOIN 작업을 보다 효율적으로 수행하는 데 도움이 될 수 있는 다양한 변형이 있습니다.

(1) Simple Nested-Loop Join(InsideMySQL에서 찍은 사진)

이 알고리즘은 비교적 간단합니다. 드라이버 테이블에서 R1을 가져와 S 테이블의 모든 열을 일치시킨 다음 R 테이블의 모든 데이터가 일치할 때까지 R2, R3을 가져온 다음 데이터를 병합하는 것을 볼 수 있습니다. S 테이블에 대한 RN 액세스가 필요합니다. 간단하지만 비용은 여전히 ​​상대적으로 높습니다

(2) Index Nested-Loop Join, 구현은 다음과 같습니다.

Index Nested 관계 설정을 위한 비구동 테이블에서는 비교할 때 더 이상 레코드를 하나씩 비교할 필요가 없으며 대신 인덱스를 사용하여 비교를 줄여 쿼리 속도를 높일 수 있습니다. 이는 일반적으로 관련 쿼리를 수행할 때 관련 필드에 인덱스가 있어야 하는 주된 이유 중 하나입니다.

이 알고리즘이 링크 쿼리를 수행하면 드라이버 테이블은 관련 필드의 인덱스를 기반으로 검색합니다. 인덱스에서 일치하는 값이 발견되면 쿼리를 위해 테이블로 반환됩니다. 인덱스가 일치하는 경우. 드라이버 테이블 선택에 있어서 MySQL 옵티마이저는 일반적으로 레코드 수가 적은 드라이버 테이블을 선택하지만 SQL이 특히 복잡할 경우 잘못된 선택을 배제할 수 없습니다.

인덱스 중첩 링크 방식에서는 비구동 테이블의 연관 키가 기본 키인 경우, 기본 키가 아닌 경우 반환되는 행 수가 많아지면 효율성이 매우 떨어집니다. 여러 테이블 반환 작업이 필요하기 때문에 특히 낮습니다. 먼저 인덱스를 연결한 다음 보조 인덱스의 기본 키 ID를 기반으로 테이블 반환 작업을 수행합니다. 이 경우 성능은 상대적으로 좋지 않습니다.

(3) 다음과 같이 구현된 블록 중첩 루프 조인:

인덱스가 있는 경우 MySQL은 인덱스 중첩 루프 조인 알고리즘을 사용하려고 시도할 경우 조인 열이 없을 수 있습니다. .index를 사용한다면, 이때 MySQL의 선택은 먼저 소개된 Simple Nested-Loop Join 알고리즘이 아니라 Block Nested-Loop Join 알고리즘에 우선순위를 부여할 것입니다.

Simple Nested-Loop Join과 비교하여 Block Nested-Loop Join에는 조인 버퍼인 조인 버퍼를 사용하여 드라이버 테이블의 모든 쿼리 JOIN 관련 열을 JOIN BUFFER에 버퍼링한 후 추가 중간 처리 프로세스가 있습니다. 배치 테이블과 비드라이버 테이블을 비교하는 경우 여러 비교를 하나로 병합하여 비구동 테이블의 액세스 빈도를 줄일 수 있습니다. 즉, S 테이블은 한 번만 액세스하면 됩니다. 이런 방식으로 비구동 테이블은 여러 번 액세스되지 않으며, 이 경우에만 조인 버퍼에 액세스됩니다.

MySQL에서는 Join_buffer_size 매개변수를 통해 조인 버퍼의 값을 설정한 후 작업을 수행할 수 있습니다. 기본적으로 Join_buffer_size=256K로 MySQL은 연결된 열을 캐싱하는 대신 선택한 열을 포함하여 검색 중에 필요한 모든 열을 조인 버퍼에 캐시합니다. N개의 JOIN 연관이 있는 SQL에서는 실행 중에 N-1개의 조인 버퍼가 할당됩니다. the 위의 소개는 끝났습니다. 구체적인 예를 살펴 보겠습니다

(1) 전체 테이블 join

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id;

출력 정보를 살펴 보겠습니다. 전체 테이블 테이블에서 볼 수 있다. 스캔 시 comments_for를 드라이버 테이블로 사용하므로 관련 필드가 인덱싱되므로 비구동 테이블 코멘트와 일치하도록 인덱스 idx_commentsid에 대해 전체 인덱스 스캔을 수행하여 한 행을 검색할 수 있다. 매번 일치했습니다. 이때 Index Nested-Loop Join을 사용하며, index를 통해 전체 테이블을 일치시키는 것을 볼 수 있는데, comments_for 테이블의 크기가 comments보다 훨씬 작기 때문에 MySQL은 작은 테이블인 comments_for를 구동으로 우선시한다는 것을 알 수 있습니다. 테이블.

(2) 전체 테이블 JOIN + 필터링 조건

SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id
WHERE gc.comments_id =2056

이때 Index Nested-Loop Join을 먼저 사용하며, 드라이버 테이블 주석의 기본 키를 필터링합니다. match one, non-driver comments_for 테이블의 idx_commentsid 인덱스가 탐색 일치하고 최종 일치 결과가 하나의 항목에 영향을 미칠 것으로 예상됩니다. 이렇게 하면 non-drive 테이블의 idx_commentsid 인덱스에 대해 단 한 번의 액세스 작업이 수행됩니다. 효율성이 상대적으로 높습니다.

(3) 관련 필드에 인덱스가 없는 상황을 살펴보겠습니다.

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.order_id=gcf.product_id

실행 계획을 살펴보겠습니다.

실행 계획에서 우리는 이 테이블 JOIN은 테이블 연결을 수행하는 데 사용되는 블록 중첩 루프 조인입니다. 먼저 작은 테이블 comments_for(단 57개 행)가 드라이버 테이블로 사용된 다음 comments_for의 필수 데이터가 JOIN 버퍼에 캐시됩니다. 그리고 코멘트 테이블은 일괄적으로 스캔됩니다. 즉, 조인 버퍼가 comments_for의 캐시된 데이터를 저장할 만큼 충분히 크다면 A 일치만 수행됩니다.

그리고 실행 계획에서 매우 명확한 프롬프트를 볼 수 있습니다: 조인 버퍼 사용(블록 중첩 루프)

일반적으로 이런 일이 발생하면 SQL을 최적화해야 함을 증명합니다.

이 경우 MySQL은 Simple Nested-Loop Join이라는 폭력적인 방법도 선택한다는 점에 유의해야 합니다. 이 최적화 프로그램을 어떻게 선택하는지 이해하지 못했지만 일반적으로 CBO를 기반으로 하기 때문에 Block Nested-Loop Join을 사용합니다. 오버헤드에서는 Block Nested-Loop Join의 성능이 Simple Nested-Loop Join의 성능보다 훨씬 좋습니다.

(4) 왼쪽 조인을 살펴보세요

EXPLAIN SELECT * FROM comments gc
LEFT JOIN comments_for gcf ON gc.comments_id=gcf.comments_id

실행 계획을 살펴보세요.

이 경우 관련 필드가 인덱스되어 있으므로 Index Nested-Loop라고 합니다. Join 이지만 필터링 조건이 없는 경우 첫 번째 테이블이 JOIN을 위한 구동 테이블로 선택되고, 비구동 테이블의 인덱스가 Index Nested-Loop Join을 위해 연결됩니다.

필터 조건 gc.comments_id =2056을 추가하면 비구동 테이블에 대한 인덱스 중첩 루프 조인이 필터링되어 매우 효율적입니다.

다음과 같은 경우:

EXPLAIN SELECT * FROM comments_for gcf
LEFT JOIN comments gc ON gc.comments_id=gcf.comments_id
WHERE gcf.comments_id =2056

gcf 테이블을 필터링하면 기본적으로 gcf 테이블이 드라이버 테이블로 선택됩니다. 일치하는 조건이 거의 없습니다. 자세한 내용은 실행 계획을 참조하세요.

이 시점에서 조인은 기본적으로 매우 명확합니다. 누구나 오류를 지적할 수 있으며 심각하게 수정하겠습니다. . . . .

위 내용은 MySQL JOIN 원리에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!