본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
- 포스팅에 포함된 Query 결과는 AWS EC2 FreeTier(CentOS7.2) 환경에 MySQL v5.7.17 에서 실행한 결과를 포함하였습니다. 실행환경에 따라 Query 실행시간이나 RDBMS 실행계획이 다를 수 있습니다.
집합연산
조인이 테이블의 칼럼을 연결하는 것이라면 집합 연산은 여러 테이블의 레코드를 연결하는 방법이다. 이렇게 결과의 레코드를 확장하는 집합 연산자로는 UNION과 INTERSECT, 그리고 MINUS가 있다.
- UNION은 두 개의 집합을 하나로 묶는 역할을 한다. UNION 연산자는 다시 두 집합에서 중복되는 레코드를 제거할지 말지에 따라 UNION DISTINCT와 UNION ALL로 나뉜다.
- INTERSECT는 두 집합의 교집합을 반환한다.
- MINUS 연산자는 첫 번째 집합에서 두 번째 집합을 뺀 나머지 결과만 반환한다.
집합 연산도 모두 임시 테이블이 필요한 작업이다. 집합 연산 대상 레코드 건수가 적다면 빠르게 처리되겠지만 레코드 건수가 많다면 디스크를 사용하기 때문에 성능상 문제가 될 수도 있다.
MySQL 에서는 집합 연산자 가운데 가장 자주 사용되는 UNION 기능만 제공한다.
UNION (DISTICT와 ALL)
UNION 연산자를 사용하는 경우 사용자들이 가장 많이 간과하는 점은 두 집합 간의 중복 레코드에 대한 처리를 간과한다는 점이다.
- UNION ALL은 두 개의 집합에서 중복된 레코드에 대해 별도의 처리 과정을 거치지 않고 바로 반환한다.
- UNION DISTINCT는 두 개의 집합에서 중복된 레코드를 제거한 후 합집합을 사용자에게 반환한다.
두 집합 간의 레코드 중복은 어떻게 판단될까? 일단 UNION의 생성 결과 임시 테이블은 중복 체크의 기준이 될 프라이머리 키가 없다. 그래서 집합 연산에서 레코드가 똑같은지 비교하려면 임시 테이블의 모든 칼럼을 비교해야 한다. UNION 연산은 대상 레코드의 건수가 많아져도 처리 성능이 떨어지지만, 비교해야 하는 칼럼 값의 길이가 길어지면 더 느려진다.
UINON ALL이나 UINON (DISTINCT) 모두 두 집합의 합을 만들어 내기 위해 버퍼 역할을 하는 임시 테이블을 사용한다. 하지만 UNION ALL은 단순히 임시 테이블만 사용하는 반면 UNION (DISTINCT)는 집합의 모든 칼럼을 이용해 UNIQUE 인덱스를 생성한다.
UNION ALL 과 UNION (DISTINCT) 의 차이는 단순히 유니크 인덱스를 가지느냐 아니냐의 차이지만 실제로 유니크 인덱스로 인한 성능 차이는 작지 않다.
사용자는 아무 옵션이 없는 UNION만 사용하는데, 운 나쁘게도 옵션이 없는 UNION은 UNION DISTINCT와 동일하므로 느린 집합 연산이 사용된다.
두 개의 쿼리는 중복된 레코드가 발생할 수 없다는 것을 쿼리 조건으로 알 수 있다. 중복된 레코드가 발생할 가능성이 없음에도 UNION ALL이 아니라 UNION (DISTINCT)이 사용된 경우가 의외로 많다. 중복된 결과가 있을 수 없다는 것이 보장된다면 UNION ALL 연산자를 이용해 MySQL 서버가 불필요하게 중복 제거 작업을 하지 않고 빨리 처리되게 할 수 있다.
INTERSECT
INTERSECT 집합 연산은 두 개의 집합에서 교집합 부분만을 가져오는 쿼리다. INTERSECT 연산은 INNER JOIN과 동일하다.
이 쿼리는 Marketing 부서(d001)에 소속됐던 적도 있고 Finace 부서 (d002)에 소속됐던 적도 있는 사원을 조회하는 쿼리다. MySQL 에서는 INTERSECT 집합 연산을 제공하지 않기 때문에 이 쿼리는 실행되지 않는다. 이러한 쿼리는 INNER JOIN으로 쉽게 해결할 수 있다.
MINUS
MINUS 집합 연산자 또한 첫 번째 집합에서 두번째 결과 집합의 내용을 빼는 것이다. 첫 번째 결과 집합에서 교집합 부분만 제거한 결과를 반환하는 연산자다. 표현을 바꾸면 첫 번째 집합에는 잇찌만 두 번째 집합에는 없는 레코드만 가져오는 연산이다.
SELECT emp_no FROM dept_emp WHERE dept_no='d001'
MINUS
SELECT emp_no FROM dept_emp WHERE dept_no='d002';
위의 쿼리를 간단히 NOT EXISTS를 이용해 구현하면 다음과 같다.
하지만 처리 대상 레코드 건수가 많아지면 NOT EXISTS 형태보다는 LEFT JOIN을 이용한 ANTI-JOIN 형태가 더 빠른 성능을 보여준다.
위 쿼리에서는 d001에 소속된 적이 있는 직원과 d002에 소속된 적이 있는 사원을 LEFT (OUTER) JOIN으로 조인한 결과에서 de2.dept_no IS NULL 조건으로 d002부서에서 일했던 적이 없는 사원만을 뽑아내면 MINUS 집합 연산자와 같은 결과를 가져올 수 있다.
