[데이터베이스] 조인의 종류와 원리

조인(Join)이란 하나의 테이블이 아닌 두 개 이상의 테이블을 묶어서 하나의 결과물을 만드는 것을 의미합니다.

MySQL에서는 JOIN이라는 쿼리로, MongoDB에서는 lookup이라는 쿼리로 이를 처리할 수 있습니다.

MongoDB를 사용할 때는 lookup은 되도록 사용하지 말아야 합니다. MongoDB는 조인 연산(lookup)에 대해 관계형 데이터베이스보다 성능이 떨어진다고 여러 벤츠마크에서 알려져 있습니다.

 

따라서 여러테이블을 조인하는 작업이 많을 경우에는 MongoDB보다는 관계형 데이터베이스를 써야 한다고 합니다.

조인의 종류

조인의 종류 중 대표적인 조인은 내부 조인(inner join), 왼쪽 조인(left outer join), 오른쪽 조인(right outer join), 합집합 조인(full outer join)가 있습니다.

 

 

내부 조인(inner join) : 왼쪽 테이블과 오른쪽 테이블의 두 행이 모두 일치하는 행이 있는 부분만 표기합니다.
왼쪽 조인(left outer join) : 왼쪽 테이블의 모든 행이 결과 테이블에 표기됩니다.
오른쪽 조인(right outer join) : 오른쪽 테이블의 모든 행이 결과 테이블에 표기됩니다.
합집합 조인(full outer join) : 두 개의 테이블을 기반으로 조인 조건에 만족하지 않는 행까지 모두 표기합니다.

내부 조인(inner join)

내부 조인은 두 테이블 간에 교집합을 의미합니다.

SELECT *
FROM TABLEA A INNER JOIN TABLEB B
ON A.KEY = B.KEY

왼쪽 조인(left outer join)

왼쪽 조인은 테이블 B의 일치하는 부분의 레코드와 함께 테이블 A를 기준으로 완전한 레코드 집합을 생성합니다. 만약 테이블 B에 일치하는 항목이 없으면 해당 값은 null 값이 됩니다.

SELECT *
FROM TABLEA A LEFT JOIN TABLEB B
ON A.KEY = B.KEY

오른쪽 조인(right outer join)

오른쪽 조인은 테이블 A에서 일치하는 부분의 레코드와 함께 테이블 B를 기준으로 완전한 레코드 집합을 생성합니다. 만약 테이블 A에 일치하는 항목이 없으면 해당 값은 null 값이 됩니다.

SELECT *
FROM TABLEA A RIGHT JOIN TABLEB B
ON A.KEY = B.KEY

합집합 조인(full outer join)

합집합 조인은 양쪽 테이블에서 일치하는 레코드와 함께 테이블 A와 테이블 B의 모든 레코드 집합을 생성합니다. 이때 일치하는 항목이 없으면 누락된 쪽에 null 값이 포함되어 출력됩니다.

SELECT *
FROM TABLEA A FULL OUTER JOIN TABLEB B
ON A.KEY = B.KEY

조인의 원리

앞서 설명한 조인들은 조인의 원리를 기반으로 작업이 이루어집니다. 조인의 원리의 종류로는 중첩 루프 조인(NLJ), 정렬 병합 조인, 해시 조인이 있습니다.

중첩 루프 조인(NLJ, Nested Loop Join)

중첩 루프 조인(NLJ, Nested Loop Join)은 중첩 for문과 같은 원리로 조건에 맞는 조인을 하는 방법을 의미합니다. 랜덤 접근에 대한 비용이 많이 증가하므로 대용량의 테이블에서는 사용하지 않습니다.

 

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

 

참고로 중첩 루프 조인에서 발전한 조인할 테이블을 작은 블록으로 나눠서 블록 하나씩 조인하는 블록 중첩 루프 조인(BNL, Block Nested Loop)라는 방식도 있습니다.

정렬 병합 조인

정렬 병합 조인이란 각각의 테이블을 조인할 필드를 기준으로 정렬하고 정렬이 끝난 이후에 조인 작업을 수행하는 조인입니다. 조인할 때 쓸 적절한 인덱스가 없고 대용량의 테이블들을 조인하고 조인 조건으로 >, < 등 범위 비교 연산자기 있을 때 사용합니다.

해시 조인

해시 조인은 해시 테이블을 기준으로 조인하는 방법입니다. 두 개의 테이블을 조인한다고 했을 때 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다 더 효울적입니다.(만약 메모리에 올릴 수 없을 정도로 크다면 디스크를 사용하는 비용이 발생됩니다.) 또한, 동등(=) 조인에서만 사용할 수 있습니다.

 

MySQL의 경우 MySQL8.0.18 릴리스와 함께 이 기능을 사용할 수 있게 되었으며 이를 기반으로 해시 조인의 과정을 살펴보겠습니다. MySQL의 해시 조인 단계는 빌드 단계, 프로브 단계로 나뉩니다.

빌드 단계

빌드 단계는 입력 테이블 중 하나를 기반으로 메모리 내 해시 테이블을 빌드하는 단계입니다.
예를 들어 persons와 countries 테이블을 조인한다고 하였을 때둘 중에 바이트가 더 작은 테이블을 기반으로 해서 테이블을 빌드합니다.

 

 

또한, 조인에 사용되는 필드가 해시 테이블의 키로 사용됩니다. countries.country_id가 키로 사용되는 것을 볼 수 있습니다.

프로브 단계

프로드 단계 동안 레코드 읽기를 시작하며 각 레코드에서 persons.country_id에 일치하는 레코드를 찾아서 결괏값으로 반환합니다.

 

 

이를 통해 각 테이블은 한 번씩만 읽게 되어 중첩해서 두 개의 테이블을 읽는 중첩 루프 조인보다 보통은 성능이 더 좋습니다. 참고로 사용 가능한 메모리양은 시스템 변수 join_buffer_size에 의해 제어되며 런타임 시에 조정할 수 있습니다.