[MySQL] 조인 (2)

카테시안 조인

카테시안 조인은 FULL JOIN 또는 CROSS JOIN이라고도 한다. 

일반적으로는 조인을 수행하기 위해 하나의 테이블에서 다른 테이블로 찾아가는 연결 조건이 필요하다.

하지만 카테시안 조인은 조인 조건 자체가 없이 2개 테이블의 모든 레코드 조합을 결과로 가져오는 방식이다.

조인이 되는 테이블의 레코드 건수가 1 ~ 2건 정도로 많지 않을때라면 문제가 되지는 않는다. 하지만 레코드 건수가 많아지면 조인의 결과 건수가

기하급수적으로 늘어나므로 Mysql서버 자체를 응답 불능 상태로 만들어버릴 수도 있다.

조인의 양쪽 테이블이 모두 레코드 1건인 쿼리를 제외하면 애플리케이션에서 사용되는 카테시안 조인은 의도하지 않았던 경우가 대부분이다.

Mysql에서 CROSS JOIN은 INNER JOIN과 같은 조인 방식을 의미한다.

CROSS JOIN을 사용하는 경우  INNER JOIN과 같이 ON 절이나 WHERE 절에 조인 조건을 부여하는 것이 가능하며 이렇게 작성된

CROSS JOIN은 INNER JOIN과 같은 방식으로 동작한다.

그래서 Mysql에서 CROSS JOIN은 카테시안 조인이 될 수도 있고 아닐 수도 있다. 아래의 예는 둘다 같은 결과를 가져온다.

SELECT d.*, e.* 

FROM departments d 

INNER JOIN employees e ON d.emp_no = e.emp_no;

SELECT d.*, e.*

FROM department d

CROSS JOIN employess e ON d.emp_no = e.emp_no;

Mysql에서 카테시안 조인과 이너 조인은 문법으로 구분되는 것이 아니다. 

조인으로 연결되는 조건이 적절히 있다면 이너 조인으로 처리되고 연결되는 조건이 없다면 카테시안 조인이 된다. 그래서 특별히 구분해서 사용할 필요는 없다.

잡소리 : 

Mysql조인의 결과는 드라이빙 테이블을 읽은 순서대로 레코드가 정렬되어 반환되는 것이다.

조인에서 드리븐 테이블들은 단순히 드라이빙 테이블의 레코드를 읽는 순서대로 검색만 할 뿐이다(룩업!)

조인 버퍼를 이용한 조인(Using Join buffer, 실행 계획에 보인다!)

조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리한다.

즉, 드라이빙 테이블은 한 번에 쭉 읽게 되지만 드리븐 테이블은 여러번 읽는 다는 것을 의미한다.

예를들어 드라이빙 테이블에서 일치하는 레코드가 1000건이었는데 

드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면

드리븐 테이블에서 연결되는 레코드를 찾기 위해 1000번의 풀 테이블 스캔을 해야한다.

드리븐 테이블을 검색할떄 인덱스를 사용할 수 없는 쿼리는 상당히 느려지며 Mysql옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를

사용할 수 있게 실행 계획을 수립한다

그런데 어떤 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다.

이떄 사용하는 메모리의 캐시를 조인버퍼 (Join Buffer)라고 한다.

(조인버퍼는 join_buffer_size라는 시스템 설정 변수로 크기를 제한할 수 있다. 조인이 완료되면 바로 해제된다.)

아래 예를 보자

SELECT * FROM dept_emp de, employees e

WHERE de.from_date > `1995-01-01` AND e.emp_no < 109004;

각각 테이블에 대한 조인은 WEHRE절에 있지만 두 테이블간의 연결 고리 열할을 하는 조인 조건은 없다.

그래서 dept_emp테이블에서 from_date > 1995-01-01인 레코드(99999건)와 employees 테이블에서 emp_no < 109004 조건을 만족하는 레코드(88888건)는 카테시안 조인을 수행한다.

조인버퍼가 없다면? dept_emp테이블이 드라이빙 테이블이 되고 employees테이블이 드리븐 테이블이 되어 조인된다.

결과는 같지만 매번 같은 반복 작업(풀 스캔)을 하게 된다.

같은 처리를 조인버퍼를 사용하게 되면 어떻게 달라지는가??

실행 계획을 보면 dept_emp 테이블이 드라이빙 테이블이 되어 조인되고 employees테이블을 읽을 때는 조인 버퍼를 이용한다는 것을 실행계획에서 볼 수 있다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	de	range	ix_fromdate	3		20550	using where
1	SIMPLE	e	range	PRIMARY	4		148336	using where; using join buffer

1. dept_emp테이블의 ix_fromdate 인덱스를 이용해 조건을 만족하는 레코드를 검색한다.

2. 조인에 필요한 나머지 칼럼을 모두 dept_emp 테이블로부터 읽어서 조인버퍼에 저장한다.

3. employees 테이블의 프라이머리 키를 이용해 조건을 만족하는 레코드를 검색한다.

4. 3번에서 검색된 결과에 2번의 캐시된 조인 버퍼의 레코를 결합해서 반환한다.

여기서 중요한것은 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행된다는 것.

위의 4번째 단계가 employees 테이블의 결과를 기준으로 dept_emp 테이블의 결과를 결합(병합)한다는 것을 의미한다.

실제 이 쿼리의 실행 계획상으로는 dept_emp 테이블이 드라이빙 테이블이 되고 employees 테이블이 된다.

하지만 1) 실제 드라이빙 테이블의 결과는 조인버퍼에 담아두고 2) 드리븐 테이블을 먼저 읽고 3) 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다.

순서는 조인은 2 -> 1 -> 3의 결과로 진행된다.

일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가

흐트러질 수 있음을 기억해야한다.

* 조인버퍼가 사용될때 너무 결과 커서 조인버퍼에 전부 담지못하면 과정을 여러번 반복한다.