[MySQL]MySQL 벼락치기(9) - 조인(JOIN)

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

조인의 종류

조인의종류는 크게 INNER JOIN 과 OUTER JOIN 으로 구분할 수 있고, OUTER JOIN 은 다시 LEFT OUTER JOIN 과 RIGHT OUTER JOIN 그리고 FULL OUTER JOIN 으로 구분 할 수 있다. 
조인의 처리에서 어느 테이블을 먼저 읽을지를 결정하는 것은 상당히 중요하다, 그에 따라 처리할 작업량이 상당히 달라진다. INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로 MySQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다. 하지만 OUTER JOIN 은 반드시 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 조인 순서를 옵티마이저가 선택할 수 없다. 

INNER JOIN

일반적으로 조인이라 하면 INNER JOIN을 지칭힌다. MySQL 에서 조인은 네스티드-루프 방식만 지원한다. 네스티드-루프 란 일반적으로 프로그램을 작성할때 두개의 FOR나 WHILE 과 같은 반복 루프 문장을 실행하는 형태로 조인이 처리되는 것을 의미한다. 

FOR( record1 IN TABLE1 ){ // 외부 루프 (OUTER)
    FOR( record2 IN TABLE 2){ // 내부 루프 (INNER)
        IF( record1.join_column == record2.join_column ){
            join_record_found(record1.*, record2.*);
        }ELSE{
            join_record_notfound();
        }
    }
}

두개의 FOR 문장에서 바깥쪽을 아우터(OUTER) 테이블 이라고 하며, 안쪽을 이너(INNER) 테이블이라고 표현한다. 또한 아우터 테이블은 이너 테이블보다 먼저 읽어야 하며 조인에서 주도적인 역할을 한다고해서 드라이빙(Driving) 테이블이라고 하고 이너 테이블은 조인에서 끌려가는 역할을 한다고 해서 드리븐(Driven) 테이블이라고 한다. 

OUTER JOIN 

OUTER JOIN은 위에서 살펴본 의사코드를 조금만 수정하면 된다. 

FOR( record1 IN TABLE1 ){ // 외부 루프 (OUTER)
    FOR( record2 IN TABLE 2){ // 내부 루프 (INNER)
        IF( record1.join_column == record2.join_column ){
            join_record_found(record1.*, record2.*);
        }ELSE{
            join_record_found(record1.*, NULL);
        }
    }
}

TABLE2 에 일치하는 레코드가 있으면 INNER 조인과 같은 결과를 만들어내지만, TABLE2 에 조건을 만족하는 레코드가 없는 경우에는 TABLE2 의 컬럼을 모두 NULL로 채워서 가져온다. 이처럼 INNER 테이블이 조인의 결과에 전혀 영향을 미치지 않고 OUTER 테이블의 내용에 따라 조인의 결과가 결정되는 것이 OUTER JOIN의 특징이다. 
그리고 OUTER JOIN 은 조인의 결과를 결정하는 아우터 테이블이 조인의 왼쪽에 있는지 오른쪽에 있는지에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN 그리고 FULL OUTER JOIN으로 다시 나뉜다.

MySQL 실행계획은 INNER JOIN 을 사용했는지 OUTER JOIN을 사용했는지를 알려주지 않으므로 OUTER JOIN을 의도한 쿼리가 INNER JOIN으로 실행되지 않는지 주의해야 한다. OUTER JOIN 에서 레코드가 없을 수도 있는 쪽(드리븐)의 테이블에 대한 조건은 반드시 LEFT JOIN의 ON 절에 명시하자 그렇지 않으면 옵티마이저는 OUTER JOIN을 내부적으로 INNER JOIN으로 변형시켜서 처리해 버릴 수도 있다. (http://ftp.nchu.edu.tw/MySQL/doc/refman/5.5/en/left-join-optimization.html)(이부분에 대해선 조금더 확인해보자..)
LEFT OUTER JOIN의 ON 절에 명시되는 조건은 조인되는 레코드가 있을때만 적용되지만 WHERE 절에 명시되는 조건은 OUTER JOIN이나 INNER JOIN 에 관계없이 조인된 결과에 대해 모두 적용된다. 

SELECT * 
FROM employees e
LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no
WHERE s.salary > 5000;

보다는 ON 조건에 명시하자!

SELECT * 
FROM employees e
LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no AND s.salary > 5000;

LEFT OUTER JOIN이 아닌 쿼리에서는 검색 조건이나 조인 조건을 WHERE 절이나 ON 절 중에서 어느곳에 명시해도 성능상의 문제나 결과의 차이가 나지 않는다. 

카테시안 조인(FULL JOIN)

카테시안 조인은 FULL JOIN 또는 CROSS JOIN 이라고도 한다. 일반적으로는 조인을 수행하기위한 하나의 테이블에서 다른 테이블로 찾아가는 연결 조건이 필요하다. 하지만 카테시안 조인은 이 조인 조건 자체가 없이 2개의 테이블의 모든 레코드 조합을 결과로 가져오는 조인 방식이다. 카테시안 조인은 조인이 되는 테이블의 레코드 건수가 1~2건 정도로 많지 않을때라면 특별히 문제가 되지 않지만 레코드 건수가 많아지면 조인의 결과 건수가 기하급수적으로 늘어나므로 MySQL 서버 자체를 응답불능 상태로 만들어 버릴수도 있다. 

SELECT * FROM departments WHERE dept_no = 'd001';

SELECT * FROM employees WHERE emp_no = 1000001;

SELECT d.*, e.*
FROM departments d, employees e
WHERE dept_no = 'd001' AND emp_no = 1000001;

또한 카테시안 조인은 레코드 한 건만 조회하는 여러개의 쿼리(전혀 연관이 없는 쿼리)를 하나의 쿼리로 모아서 실행하기 위해 사용되기도 한다. 위 예제의 첫번째와 두번째 쿼리는 각각 레코드1건씩을 조회하지만 전혀 연관이 없다. 이 각각의 쿼리를 하나로 묶어서 실행하기 위해 세번째 쿼리와 같이 하나의 쿼리로 두 테이블을 조인해서 한번의 결과를 가져오고 있다. 하지만 카테시안 조인으로 묶은 2개의 단위 쿼리가 반환하는 레코드가 항상 1건이 보장되지 않으면 아무런 결과도 못가져오거나 또는 기대했던 것보다 훨씬 많은 결과를 받게 될 수도 있으므로 주의하자. 

조인 버퍼를 이용한 조인(Using join buffer)

조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리된다. 즉, 드라이빙 테이블은 한번에 쭉 읽게 되지만 드리븐 테이블은 여러번 읽는 다는 것을 의미한다. 예를들어 드라이빙 테이블에서 일치한는 레코드가 100건 이 있는데 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면 드리븐 테이블에서 연결되는 레코드를 찾기위해 100번의 풀 테이블 스캔을 해야 한다. 그래서 MySQL옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립한다. 
그런데 어떤 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽을 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인 하는 형태로 처리한다. 이때 사용되는 메모리 캐시를 조인버퍼(Join buffer) 라고 한다.

SELECT * 
FROM dept_emp de, employees e
WHERE de.from_date > '2000-01-01' AND e.emp_no<109004;

위와 같은 쿼리에서 dept_emp 의 조건(from_date > '2000-01-01')을 만족하는 데이터는 10616건 이고 employees 의 조건("emp_no<109004")을 만족하는 데이터는 99003건 일때 조인버퍼 없이 조인되는 경우의 처리 내용은 아래와같다.

dept_emp 테이블에서 조건(from_date > '2000-01-01')을 만족하는 각 레코드 별로 employees 테이블에서 조건("emp_no<109004")을 만족하는 레코드 99,003건씩 가져온다. 즉, dept_emp 테이블의 각 레코드에 대해 employees 테이블을 읽을 때 드리븐 테이블에서 가져오는 결과는 매번 같지만 10616번 이나 이 작업을 실행한다는 것을 알 수 있다. 

같은 처리를 조인버퍼를 사용하게 되면 어떻게 달라지는지 단계적으로 살펴보자. 조인 버퍼를 이용한다는 것은 Extra 컬럼(Using join buffer)을 통해서 알 수 있다.

1. dept_emp 테이블의 ix_fromdate 인덱스를 이용해(from_date > 2000-01-01) 조건을 만족하는 레코드를 검색한다.
2. 조인에 필요한 나머지 컬럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장한다. 
3. employees 테이블의 프라이머리 키를 이용해 (emp_no<109004) 조건을 만족하는 레코드를 검사한다. 
4. 3번에서 검색된 결과에 2번의 캐시된 조인버퍼 레코드를 결합하여 반환한다.

이 쿼리의 실행 계획상으로는 dept_emp 테이블이 드라이빙 테이블이 되고, employees 테이블이 드리븐 테이블이 된다 . 하지만 실제 드라이빙 테이블의 결과는 조인 버퍼에 담아두고 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는레코드를 찾는 방식으로 처리된다. 이때문에 일반적으로는 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만 조인 버퍼가 사용되는 조인에서는 결과 정렬 순서가 흐트러질수 있음을 기억해야 한다. 

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~