[TIL] 데이터베이스 JOIN

조인이란?

하나의 테이블이 아닌 두 개 이상의 테이블을 묶어서 하나의 결과물을 만드는 것

 

 관계형 데이터베이스에서 각 테이블은 관계를 갖게 된다. 이때, 서로 관계있는 데이터가 여러 테이블로 나뉘어 저장되었을 때, 각 테이블에 저장된 데이터를 효과적으로 검색하기 위해 조인을 사용한다. 조인을 작성하는 방식에 따라 성능이 좌우되기 때문에 어떤 질의를 수행할 것인지 명확하게 정희한 후, 비효율을 제거하여 최적의 SQL을 작성해야 한다.

 

 조인의 종류로는 내부 조인(Inner Join), 왼쪽 조인(Left Outer Join), 오른쪽 조인(Right Outer Join), 합집합 조인(Full Outer Join)이 있다.

 

조인의 종류

1. 내부 조인(Inner Join)

교집합 => 기준 테이블과 조인 테이블의 중복된 값을 표기한다.

 

SELECT * FROM TableA A
INNER JOIN TableB B ON
A.key = B.key

 

 

2. 왼쪽 조인(Left Outer Join)

오른쪽 테이블의 일치하는 부분의 레코드와 함께 왼쪽 테이블을 기준으로 완전한 레코드 집합을 생성한다.

 

만약 오른쪽 테이블에 일치하는 항목이 없는 경우 해당 값은 null 값이 된다.

 

SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key

 

 

3. 오른쪽 조인(Right Outer Join)

왼쪽 테이블의 일치하는 부분의 레코드와 함께 오른쪽 테이블을 기준으로 완전한 레코드 집합을 생성한다.

 

만약 왼쪽 테이블에 일치하는 항목이 없는 경우 해당 값은 null 값이 된다.

 

SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key

 

 

4. 합집합 조인(Full Outer Join)

양쪽 테이블에서 일치하는 레코드와 양쪽 테이블의 모든 레코드 집합을 생성한다.

 

만약 일치하는 항목이 없으면 누락된 쪽에 null 값이 포함되어 출력된다.

 

SELECT * FROM TableA A
FULL OUTER TableB B ON
A.key = B.key

 

 

5. 교차 조인(Cross Join)

곱집합 => 각 테이블의 모든 행을 한 번씩 결합한다.

 

SELECT * FROM TableA
CROSS JOIN TableB

 

 

6. 자체 조인(Self Join)

자기 자신을 조인시킨다.

 

SELECT * FROM TableA A
JOIN TableA B ON
A.컬럼명 = B.컬럼명

 

조인의 원리

1. 중접 루프 조인(Nested Loop Join)

중첩 for문과 같은 원리로 조건에 맞는 조인을 하는 방법

 

드라이빙 테이블로 한 테이블을 선정하고 이 테이블로부터 where절에 정의된 검색 조건을 만족하는 데이터들을 걸러낸 후, 이 값을 가지고 조인 대상 테이블을 반복적으로 검색하면서 조인 조건을 만족하는 최종 결과값을 얻어낸다.

• 드라이빙 테이블 : 조인을 할 때 먼저 액세스 되는 테이블
• 드라이븐 테이블 : 조인을 할 때 나중에 액세스 되는 테이블
• 옵티마이저가 드라이빙 테이블을 결정하고 자연스럽게 다른 테이블이 드라이븐 테이블로 결정된다.

for each row in t1 matching reference key {
    for each row in t2 matching reference key {
        if row satisfies join conditions, send to client
    }
}

 

예를 들어, "t1, t2 테이블을 조인한다."라고 했을 때 첫 번째 테이블에서 행을 한 번에 하나씩 읽고 그 다음 테이블에서도 행을 하나씩 읽어 조건에 맞는 레코드를 찾아 결괏값을 반환한다.

따라서, 드라이빙 테이블과 드라이븐 테이블이 서로 바뀌어도 수행 속도에 큰 변화가 없다.

 

 

 하지만 만약 위와 같이 드라이븐 테이블의 조인 속성에 대해 인덱스가 존재하는 경우, 드라이빙 테이블의 row 수만큼 드라이븐 테이블의 인덱스를 검색하게 된다.

따라서 드라이빙 테이블의 조건을 만족하는 행 수가 많다면 그만큼 반복해서 드라이븐 테이블에 접근해야 하므로 성능이 나빠질 것이기 때문에 드라이빙 테이블의 선택이 중요하다.

 

 

2. 정렬 병합 조인

각각의 테이블을 조인할 필드 기준으로 정렬하고 정렬이 끝난 이후에 조인 작업을 수행하는 조인

 

 

 두 테이블을 동시에 액세스하여 인덱스가 있는 테이블은 인덱스 스캔을 하고 인덱스가 없는 테이블은 테이블 풀스캔을 한다.
이렇게 조회된 각 테이블의 데이터들을 조인을 위한 연결고리에 대하여 정렬을 수행한다.

 두 개의 정렬 작업이 모두 완료된 후 조인 작업을 수행한다. 해당 방법은 주로 조인할 때 쓸 적절한 인덱스가 없고 대용량의 테이블들을 조인하고 조인 조건으로 <, > 등 범위 비교 연산자가 있을 때 사용한다.

 

3. 해시 조인

해시 테이블을 기반으로 조인하는 방법

 

 

MySQL의 해시 조인 단계는 빌드 단계와 프로브 단계로 나뉜다.

 

1) 빌드 단계

 빌드 단계는 입력 테이블 중 하나를 기반으로 메모리 내 해시 테이블을 빌드하는 단계이다. 둘 중 바이트가 더 작은 테이블을 기반으로 해서 테이블을 빌드한다. 작은 테이블에서 해시 테이블을 빌드하는 경우, 해시 테이블의 크기가 작아지기 때문이다.

또한, 조인에서 사용되는 필드가 해시 테이블의 키로 사용된다.

 

2) 프로브 단계

 프로브 단계 동안 레코드 읽기를 시작하며 각 레코드에서 해시 테이블을 기반으로 일치하는 레코드를 찾아서 결괏값으로 반환한다. 두 개의 테이블을 읽는 중첩 루프 조인보다 성능이 좋다. 또한, 사용 가능한 메모리 양은 시스템 변수 join_buffer_size에 의해 제어되며, 런타임 시에 조정 가능하다.

 

 

References

https://doh-an.tistory.com/30

https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/

https://github.com/WeareSoft/tech-interview/blob/master/contents/db.md#join

https://coding-factory.tistory.com/756

https://insight-bgh.tistory.com/502

https://insight-bgh.tistory.com/503

https://limycat.tistory.com/4