MySQL 실행 계획 : MySQL의 주요 처리 방식(5)

MySQL 실행 계획 : MySQL의 주요 처리 방식(5)

6. 테이블 조인

카테시안 조인

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

조인의 양쪽 테이블이 모두 레코드 1건인 쿼리를 제외하면, 애플리케이션에서 사용되는 카테시안 조인은 의도하지 않았던 경우가 대부분이다. N개 테이블의 조인이 수행되는 쿼리에서는 반드시 조인 조건은 N-1개(또는 그 이상)가 필요하며 모든 테이블은 반드시 1번 이상 조인 조건에 사용돼야 카테시안 조인을 피할 수 있다. 조인되는 테이블이 많아지고 조인 조건이 복잡해질수록 의도하지 않은 카테시안 조인이 발생할 가능성이 크기 때문에 주의해야 한다.

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건씩을 조회하지만 전혀 연관이 없다. 이 각각의 쿼리를 하나로 묶어서 실행하기 위해 세 번째 쿼리와 같이 하나의 쿼리로 두 테이블을 조인해서 한번에 결과를 가져오고 있다. 하지만 employees 테이블과 departments 테이블을 연결해주는 조인 조건은 없음을 알 수 있다. 위와 같이 2개의 쿼리를 하나의 쿼리처럼 빠르게 실행하는 효과를 얻을 수도 있다. 하지만 카테시안 조인으로 묶은 2개의 단위 쿼리가 반환하는 레코드가 항상 1건이 보장되지 않으면 아무런 결과를 못 가져오거나 또는 기대했던 것보다 훨씬 많은 결과를 받게 될 수도 있으므로 주의하자.

SQL 표준에서 CROSS JOIN은 카테시안 조인과 같은 조인 방식을 의미하지만 MySQL에서 CROSS JOIN은 INNER JOIN과 같은 조인 방식을 의미한다. MySQL에서 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 departments d
  CROSS JOIN employees e ON d.emp_no=e.emp_no;

사실 MySQL에서 카테시안 조인과 이너 조인은 문법으로 구분되는 것이 아니다. 조인으로 연결되는 적절한 조건이 있다면 이너 조인으로 처리되고, 연결 조건이 없다면 카테시안 조인이 된다. 그래서 CROSS JOIN이나 INNER JOIN을 특별히 구분해서 사용할 필요는 없다.

NATURAL JOIN

MySQL에서 INNER JOIN의 조건을 명시하는 방법은 여러 가지가 있다. 다음 예제의 쿼리를 한번 살펴보자.

SELECT *
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no;

SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no;

SELECT *
FROM employees e
  INNER JOIN salaries s USING (emp_no);

위 예제의 세 쿼리는 모두 표기법만 조금 차이가 있을 뿐, 전부 같은 쿼리다. 세 번째의 "USING(emp_no)"는 두 번째 쿼리의 "ON s.emp_no=e.emp_no"과 같은 의미로 사용된다. USING 키워드는 조인되는 두 테이블의 조인 칼럼이 같은 이름을 가지고 있을 때만 사용할 수 있다.

여기서 살펴볼 NATURAL JOIN 또한 INNER JOIN과 같은 결과를 가져오지만 표현 방법이 조금 다른 조인 방법 중 하나다. 다음의 쿼리로 NATURAL JOIN의 특성을 살펴보자.

SELECT *
FROM employees e
  NATURAL JOIN salaries s;

위의 예제 쿼리도 employees 테이블의 emp_no 칼럼과 salaries 테이블의 emp_no 칼럼을 조인하는 쿼리다. NATURAL JOIN은 employees 테이블에 존재하는 칼럼과 salaries 테이블에 존재하는 칼럼 중에서 서로 이름이 같은 칼럼을 모두 조인 조건으로 사용한다. Employees 테이블과 salaries 테이블에는 이름이 같은 칼럼으로 emp_no만 존재하기 때문에 결국 "NATURAL JOIN salaries s"는 "INNER JOIN salaries s ON s.emp_no=e.emp_no"와 같은 의미다.

NATURAL JOIN은 조인 조건을 명시하지 않아도 된다는 편리함이 있지만 사실 각 테이블의 칼럼 이름에 의해 쿼리가 자동으로 변경될 수 있다는 문제가 있다. 즉, NATURAL JOIN으로 조인하는 테이블은 같은 칼럼명을 사용할 때 자동으로 조인의 조건으로 사용돼버릴 수 있다는 점을 항상 고려해야 한다. 또한, 애플리케이션이 변경되면서 테이블의 구조를 변경할 때도 NATURAL JOIN으로 조인되는 테이블이 있는지, 그리고 그 테이블의 칼럼과 비교하면서 같은 칼럼명이 존재하는지 확인해야 한다. 이는 상당히 성가신 작업이 될 것이며, 유지보수를 위한 비용만 높이는 역효과를 가져올 가능성이 크다. 단지 이러한 방식의 조인이 있다는 것만 알아두면 충분할 것으로 보인다.

Single-sweep multi join

MySQL의 네스티드-루프 조인을 자주 "Single-sweep multi join"이라고 표현하기도 한다. 예전의 MySQL 매뉴얼에서는 조인 방식을 "Single-sweep multi-join"이라고 설명했는데, 난해하다는 이유로 "네스티드-루프 조인"이라는 표현으로 바뀌었다. "Single-sweep multi-join"의 의미는 조인에 참여하는 테이블의 개수만큼 FOR이나 WHILE과 같은 반복 루프가 중첩되는 것을 말한다. 다음 쿼리와 실행 계획을 예제로 살펴보자.

SELECT d.dept_name, e.first_name
FROM departments d, employees e, dept_emp de
WHERE de.dept_no=d.dept_no
  AND e.emp_no=de.emp_no;

위의 쿼리는 3개의 테이블을 조인하고 있는데, 이 쿼리의 실행 계획은 다음과 같다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	d	index	ux_deptname	123	NULL	9	Using index
1	SIMPLE	de	ref	PRIMARY	12	employees.d.dept_no	18603	Using index
1	SIMPLE	e	eq_ref	PRIMARY	4	employees.de.emp_no	1

이 실행 계획을 보면, 제일 먼저 d 테이블(departments)이 읽히고, 그다음으로 de 테이블(dept_emp), 그리고 e 테이블(employees)이 읽혔다는 사실을 알 수 있다. 또한 de 테이블과 e 테이블이 읽힐 때 어떤 값이 비교 조건으로 들어왔는지를 ref 칼럼에 표시하고 있다. 이 실행 계획을 FOR 반복문으로 표시해 보면 다음과 같다.

FOR (record1 IN departments) {
  FOR (record2 IN dept_emp && record2.dept_no = record1.dept_no) {
    FOR (record3 IN employees && record3.emp_no = record2.emp_no) {
      RETURN {  record1.dept_name, record3.first_name }
    }
  }
}

위의 의사 코드에서 알 수 있듯이, 3번 중첩이 되긴 했지만 전체적으로 반복 루프는 1개다. 즉, 반복 루프를 돌면서 레코드 단위로 모든 조인 대상 테이블을 차례대로 읽는 방식을 "Single-sweep multi join"이라고 한다. MySQL 조인의 결과는 드라이빙 테이블을 읽은 순서대로 레코드가 정렬되어 반환되는 것이다. 조인에서 드리블 테이블들은 단순히 드라이빙 테이블의 레코드를 읽는 순서대로 검색(Lookup)만 할 뿐이다.

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

조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리된다. 즉, 드라이빙 테이블은 한 번에 쭉 읽게 되지만 드리븐 테이블은 여러 번 읽는다는 것을 의미한다. 예를 들어 드라이빙 테이블에서 일치하는 레코드가 1,000건이었는데, 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면 드리븐 테이블에서 연결되는 레코드를 찾기 위해 1,000번의 풀 테이블 스캔을 해야 한다. 그래서 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 상당히 느려지며, MySQL 옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립한다.

그런데 어떤 방식으로 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 조인한다. 이때 사용되는 메모리의 캐시를 조인 버퍼(Join buffer)라고 한다. 조인 버퍼는 join_buffer_size라는 시스템 설정 변수로 크기를 제한할 수 있으며, 조인이 완료되면 조인 버퍼는 바로 해제된다.

두 테이블이 조인되는 다음 예제 쿼리에서, 각각 테이블에 대한 조건은 WHERE 절에 있지만 두 테이블 간의 연결 고리 역할을 하는 조인 조건은 없다. 그래서 dept_emp 테이블에서 from_date > '2000-01-01'인 레코드(10,616건)과 employees 테이블에서 emp_no < 109004 조건을 만족하는 레코드 (99,003건)는 카테시안 조인을 수행한다.

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

아래 그림은 이 쿼리가 조인 버퍼 없이 실행된다면 어떤 절차를 거쳐 결과를 가져오는지 보여준다. dept_emp 테이블이 드라이빙 테이블이 되고, employees 테이블이 드리븐 테이블이 되어 조인이 수행되는 것으로 가정했다.

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

같은 처리를 조인 버퍼(join buffer)를 사용하게 되면 어떻게 달라지는지 한번 살펴보자. 실제 이 쿼리의 실행 계획을 살펴보면 다음과 같아 dept_emp 테이블이 드라이빙 테이블이 되어 조인되고, employees 테이블을 읽을 때는 조인 버퍼(Join buffer)를 이용한다는 것을 Extra 칼럼의 내용으로 알 수 있다.

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

이 그림에서 중요한 점은 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행된다는 것이다. 위에서 설명한 절차의 4번 단계가 "employees" 테이블의 결과를 기준으로 dept_emp 테이블의 결과를 결합(병합)한다는 것을 의미한다. 실제 이 쿼리의 실행 계획상으로는 dept_emp 테이블이 드라이빙 테이블이 되고, employees 테이블이 드리븐 테이블이 된다. 하지만 실제 드라이빙 테이블의 결과는 조인 버퍼에 담아 두고, 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다. 일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있음을 기억해야 한다.

조인 버퍼가 사용되는 경우, 처음 읽은 테이블의 결과가 너무 많아서 조인 버퍼에 전부 담지 못하면 위의 1~4번까지의 과정을 여러 번 반복한다. 그리고 조인 버퍼에서는 조인 쿼리에서 필요로 하는 칼럼만 저장되고, 레코드에 포함된 모든 칼럼(쿼리 실행에 불필요한 칼럼)은 저장되지 않으므로 상당히 효율적으로 사용된다고 볼 수 있다.

조인 관련 주의 사항

MySQL의 조인 처리에서 특별히 주의해야 할 부분은 "실행 결과의 정렬 순서"와 INNER JOIN과 OUTER JOIN의 선택"으로 2가지 정도일 것이다.

1. 조인 실행 결과의 정렬 순서
   일반적으로 조인으로 쿼리가 실행되는 경우, 드라이빙 테이블로부터 레코드를 읽는 순서가 전체 쿼리의 결과 순서에 그대로 적용되는 것이 일반적이다. 이는 네스티드-루프 조인 방식의 특징이기도 하다. 다음 쿼리를 한번 살펴보자.

SELECT de.dept_no, e.emp_no, e.first_name
FROM dept_emp de, employees e
WHERE e.emp_no=de.emp_no
  AND de.dept_no='d005';

이 쿼리의 실행 계획을 보면, dept_emp 테이블의 프라이머리 키로 먼저 읽었다는 것을 알 수 있다. 그리고 dept_emp 테이블로부터 읽을 결과를 가지고 employees 테이블의 프라이머리 키를 검색하는 과정으로 처리되었다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	de	ref	PRIMARY	12	const	53288	Using where; Using index
1	SIMPLE	e	eq_ref	PRIMARY	4	de.emp_no	1

이 실행 계획 순서대로 살펴보면 dept_emp 테이블의 프라이머리 키는 (dept_no + emp_no)로 생성돼 있기 때문에 dept_emp 테이블을 검색한 결과는 dept_no 칼럼 순서대로 정렬되고 다시 emp_no로 정렬되어 반환된다는 것을 예상할 수 있다. 그런데 이 쿼리의 WHERE 조건에 dept_no='d005'로 고정돼 있으므로 emp_no로 정렬된 것과 같다. 결국 이 쿼리는 "ORDER BY de.emp_no ASC"를 명시하지는 않았지만 emp_no로 정렬된 효과를 얻을 수 있다. 주로 조인이 인덱스를 이용해 처리되는 경우에는 이러한 예측을 할 수 있다.

하지만 결과가 이 순서로 반환된 것은 옵티마이저가 여러 가지 실행 계획 중에서 위의 실행 계획을 선택했기 때문이다. 만약 옵티마이저가 다른 실행 계획을 선택했다면 이러한 결과는 보장되지 않는다. 당연히 인덱스를 이용해 검색하고 조인하는 것이 당연할 것 같은 쿼리에서도 테이블의 레코드 건수가 매우 적거나 통계 정보가 잘못돼 있을 때는 다른 실행 계획을 선택할 수도 있다. 이처럼 옵티마이저가 선택하는 실행 계획에 의존한 정렬은 피하는 것이 좋다. 쿼리의 실행 계획은 언제 변경될지 알 수 없기 때문이다. 테이블에 있는 대부분의 레코드가 어느 날 삭제됐다거나 인덱스가 삭제되거나 추가되어 실행 계획에 바뀌는 것은 충분히 가능한 일이기 때문이다.

위에서 살펴본 예제 쿼리에서 만약 사원 번호로 정렬되어 결과가 반환되기를 바란다면 반드시 "ORDER BY de.emp_no ASC" 절을 추가해서 정렬이 보장될 수 있게 하자. ORDER BY 절이 쿼리에 명시됐다고 해서 옵티마이저는 항상 정렬 작업을 수행하는 것이 아니다. 실행 계획상에서 이 순서를 보장할 수 있다면 옵티마이저가 자동으로 별도의 정렬 작업을 생략하고 결과를 반환한다. 만약 정렬이 보장되지 않는다면 강제로 정렬 작업을 통해 정렬을 보장해준다. ORDER BY 절이 사용된다고 해서 MySQL 서버가 항상 정렬을 수행하는 것은 아니다.

SQL 쿼리에서 결과의 정렬을 보장하는 방법은 ORDER BY 절을 사용하는 것밖에는 없다는 사실을 잊지 말자.

오라클과 같이 여러 가지 방법을 제공하는 DBMS에서는 조인 방법에 따라 반환되는 결과의 정렬이 달라질 수도 있다. 그래서인지 오라클 DBMS는 업그레이드할 때마다 "ORDER BY"가 항상 문제가 되는 것 같다.
아주 가끔은 MySQL이 네스티드-루프 조인 방법만 가지고 있다는 것이 다행스럽게 느껴질 수도 있을 것이다. 하지만 네스티드-루프 조인에서도 조인 버퍼를 사용할 때는 드라이빙 테이블의 순서와 관계없이 결과의 정렬 순서가 흐트러질 수도 있다. 결론적으로 어떤 DBMS를 사용하든, 어떤 조인 방식이 사용되든, 정렬된 결과가 필요할 때는 ORDER BY 절을 명시하는 것이 정답일 것이다.

INNER JOIN과 OUTER JOIN의 선택

INNER JOIN은 조인의 양쪽 테이블 모두 레코드가 존재하는 경우에만 레코드가 반환된다. 하지만 OUTER JOIN은 아우터 테이블에 존재하면 레코드가 반환된다. 쿼리나 테이블의 구조를 살펴보면 OUTER JOIN을 사용하지 않아도 될 것을 OUTER JOIN으로 사용할 때가 상당히 많다. DBMS 사용자 가운데 INNER JOIN을 사용했을 때, 레코드가 결과에 나오지 않을까 걱정하는 사람들이 꽤 있는 듯 하다. OUTER JOIN과 INNER JOIN 조인은 저마다 용도가 다르므로 적절한 사용법을 익히고 요구되는 요건에 맞게 사용하는 것이 중요하다.

때로는 그 반대로 OUTER JOIN으로 실행하면 쿼리의 처리가 느려진다고 생각하고, 억지로 INNER JOIN으로 쿼리를 작성할 때도 있다. 가끔은 인터넷에서도 OUTER JOIN과 INNER JOIN의 성능 비교를 물어보는 질문이 자주 올라오곤 한다. 사실 OUTER JOIN과 INNER JOIN은 실제 가져와야 하는 레코드가 같다면 쿼리의 성능은 거의 차이가 발생하지 않는다. 다음의 두 쿼리를 한번 비교해보자. 이 두 쿼리는 실제 비교를 수행하는 건수나 최종적으로 가져오는 결과 건수가 같다(쿼리에 포함된 SQL_NO_CACHE와 STRAIGHT_JOIN은 조건을 같게 만들어주기 위해 사용된 힌트다).

SELECT SQL_NO_CACHE STRAIGHT_JOIN COUNT(*)
FROM dept_emp de
  INNER JOIN employees e ON e.emp_no=de.emp_no;

SELECT SQL_NO_CACHE STRAIGHT_JOIN COUNT(*)
FROM dept_emp de
  LEFT JOIN employees e ON e.emp_no=de.emp_no;

PC에서 테스트해본 결과, 실행하는 데 걸린 대략적인 평균 시간은 INNER JOIN이 0.37초 정도이고, OUTER JOIN이 0.38초 정도였다. OUTER JOIN은 조인되는 두 번째 테이블(employees)에서 해당 레코드의 존재 여부를 판단하는 별도의 트리거 조건이 한 번씩 실행되기 때문에 0.01초 정도 더 걸린 것으로 보인다. 그 밖에 어떤 성능상의 이슈가 될 만한 부분은 전혀 없다.

INNER JOIN과 OUTER JOIN은 성능을 고려해서 선택할 것이 아니라 업무 요건에 따라 선택하는 것이 바람직하다. 레코드가 결과에 포함되지 않을까 걱정스러운 경우라면, 테이블의 구조와 데이터의 특성을 분석해 INNER JOIN을 사용해야 할지 OUTER JOIN을 사용해야 할지 결정하자. 데이터의 정확한 구조나 특성을 모르고 OUTER JOIN을 사용한다면 얼마 지나지 않아서 잘못된 결과가 화면에 표시되는 현상이 발생할 것이다.

참고

• Real MySQL