23.12.11 조인의 종류 및 원리

1. 조인

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

    - MySQL 에서는 JOIN 이라는 쿼리로, MongoDB 에서는 looup 이라는 쿼리로 처리 가능

    - MongoDB 를 사용할 때 lookup 은 되도록 사용하지 말아야 한다 조인 연산에 대해 관계형 DB보다

      성능이 떨어진다고 여러 벤치마크 테스트에서 알려져 있으므로 여러 테이블을 조인하는 작업이 많은 경우

      관계형 DB를 사용해야 한다

    - 내부 조인, 왼쪽 조인, 오른쪽 조인, 합집합 조인이 있다

  1) 내부 조인

       - 왼쪽 테이블과 오른쪽 테이블의 두 행이 모두 일치하는 행이 있는 부분만 표기 한다

       - 두 테이블 간에 교집합을 나타냄

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

 

   2) 왼쪽 조인

       - 왼쪽 테이블의 모든 행이 결과 테이블에 표기된다

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

       - 만약 오른쪽 테이블에 일치하는 항목이 없으면 해당 값은 null 값이 됨

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

  

   3) 오른쪽 조인

       - 오른쪽 테이블의 모든 행이 결과 테이블에 표기된다

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

       - 만약 왼쪽 테이블에 일치하는 항목이 없으면 해당 값은 null 값이 됨

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

  

   4) 합집합 조인

       - 두개의 테이블을 기반으로 조인 조건에 만족하지 않는 행까지 모두 표기된다

       - 양쪽 테이블에서 일치하는 레코드와 함께 왼쪽 테이블과, 오른쪽 테이블의 모든 레크도 집합을 생성

       - 일치하는 항목이 없으면 누락된 쪽에 null 값이 포함되어 출력 됨

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

  

1. 조인의 원리

    - 중첩 루프 조인, 정렬 병합 조인, 해시 조인

  1) 중첩 루프 조인

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

       - 랜덤 접근에 대한 비용이 많이 증가하므로 대용량의 테이블에서는 사용하지 않는다

       - 예) t1, t2 테이블을 조인한다 라고 했을 때, 첫번째 테이블에서 행을 한번에 하나씩 읽고 그다음 테이블에서도

              행을 하나씩 읽어 조건에 맞는 레코드를 찾아 결과값 반환

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

       - 중첩 루프 조인에서 발전한 조인할 테이블을 작은 블록으로 나눠서 블록 하나씩 조인하는 블록 중첩 루프 조인

         이라는 방식도 있다

 

   2) 정렬 병합 조인

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

       - 조인 할 때 적절하게 사용하 ㄹ인덱스가 없고 대용랴으이 테이블들을 조인하고 조인 조건으로 <, > 등

         범위 비교 연산자가 있을 때 사용

 

   3) 해시 조인

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

       - 두개의 테이블을 조이한다고 했을 때, 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다

          더 효율적(메모리에 올릴 수 없을 정도로 크다면 디스크를 사용하는 비용이 발생)

       - 동등(=) 조인에서만 사용 가능

       - MySQL의 경우 MySQL 8.0.18 릴리스와 함께 이 기능을 사용할 수 있게 되었으며 이를 기반으로 한 해시조인의

         과정은 아래와 같다

     (1) 빌드 단계

           - 입력 테이블 중 하나를 기반으로 메모리 내 해시 테이블을 빌드하는 단계

           - 예) persons 와 counries 라는 테이블을 조인한다고 했을 때 둘 중 바이트가 더 작은 테이블을 기반으로 해서

                  테이블을 빌드

           - 또한, 조인에 사용되는 필드가 해시 테이블의 키로 사용됨 'countries.country_id' 가 키로 사용됨

       

     

     (2) 프로브 단계

           - 레코드 읽기를 시작하며, 각 레크도에서 'persoons.country_id' 에 일치하는 레코드를 찾아서 결과값으로 반환

           - 각 테이블은 한번씩만 읽게 되어 중첩에서 두개의 테이블을 읽는 중첩 루프 조인보다 보통은 성능이 더 좋다

           - 사용 가능한 메모리양은 시스템 변수 join_buffer_size 에 의해 제어되고, 런타임 시에 조정 가능