반응형
UNION과 UNION ALL 의 차이 및 주의 사항
ANSI SQL에서 제안하는 집합 연산 "UNION", "INTERSECT", "MINUS" 중에서
MySQL에서는 UNION 집합 연산만 제공하고 있다.
(하지만 MySQL에서 INTERSECT나 MINUS를 다른 형태의 쿼리로 풀어서 사용할 수 있다.)
이 글에서는 UNION 에 대해서 좀 더 자세히 알아 보고자 한다.
UNION 집합 연산은 다시 아래와 같이 두가지 종류로 나누어진다.
- UNION ALL
- UNION DISTINCT
우리가 일반적으로 사용하는 방식인 아무런 추가 키워드 없이 UNION 만 사용하는 것은
UNION DISTINCT 를 줄여서 사용하고 있는 것이다.
UNION ALL과 UNION DISTINCT를 레코드가 많은 결과에 대해서 적용해본 사람은
아마도 둘의 처리 방식에 대해서 의구심을 가져본 적이 있을 것이다.
레코드 건수가 많아지면 많아질수록 그 성능 차이는 엄청난 차이를 보여줄 것이다.
우선, 아래와 같이 2개씩 동일한 레코드 데이터를 가지고 있는 tab1과 tab2라는 테이블이 있다.
mysql>SELECT fdpk, fddata FROM tab1;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
+------+--------+
2 rows in set (0.00 sec)
mysql>SELECT fdpk, fddata FROM tab2;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
+------+--------+
2 rows in set (0.01 sec)
그러면, 이 두개 테이블에 대해서 각각 UNION과 UNION ALL을 사용하는 쿼리를 실행해보자.
mysql>SELECT fdpk, fddata
-> FROM (
-> SELECT fdpk, fddata FROM tab1
-> UNION ALL
-> SELECT fdpk, fddata FROM tab2
-> ) x;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
| 1 | data1 |
| 2 | data2 |
+------+--------+
4 rows in set (0.00 sec)
mysql>SELECT fdpk, fddata
-> FROM (
-> SELECT fdpk, fddata FROM tab1
-> UNION
-> SELECT fdpk, fddata FROM tab2
-> ) x;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
+------+--------+
2 rows in set (0.00 sec)
두개의 퀴리 실행 결과 UNION은 레코드가 반으로 줄었다.
이미 다들 알고 있다시피 UNION은 UNION DISTINCT와 동일한 작업을 하기 때문에 중복되는 레코드를 제거했음을 알 수 있다.
하지만, UNION ALL의 경우에는 별도의 중복 제거 과정을 거치지 않고 그냥 결과를 내려준다.
아주 중요한 내용이지만, 사실 이 내용을 다들 별로 신경쓰지 않고 모두들 UNION을 즐겨 사용한다.
안타깝게도, MySQL의 실행계획에서는 둘의 차이를 전혀 느낄 수 없다.
+----+--------------+------------+------+..+------+..+------+------+-------+
| id | select_type | table | type |..| key |..| ref | rows | Extra |
+----+--------------+------------+------+..+------+..+------+------+-------+
| 1 | PRIMARY | <derived2> | ALL |..| NULL |..| NULL | 4 | |
| 2 | DERIVED | tab1 | ALL |..| NULL |..| NULL | 2 | |
| 3 | UNION | tab2 | ALL |..| NULL |..| NULL | 2 | |
|NULL| UNION RESULT | <union2,3> | ALL |..| NULL |..| NULL | NULL | |
+----+--------------+------------+------+..+------+..+------+------+-------+
하지만 중복 제거는 그냥 얻을 수 있는 결과가 아니다.그러면, MySQL이 내부적으로 어떻게 중복을 제거하는 것일까 ?
내부적인 처리를 알아보기 전에, 레코드의 중복이라는 표현을 했는데 이 중복의 기준이 무었일까 ?
1. 각 테이블의 Primary key ?
2. 전체 테이블의 모든 필드 ?
3. 각 서브 쿼리에서 SELECT된 튜플(레코드)의 모든 필드 ?
그렇다. 이미 SELECT된 결과를 가지고 UNION하기 때문에 SELECT되기 전의 테이블이나 레코드에 대한 정보는 알 수 없다.
그래서, 중복 여부의 판단은 SELECT된 튜플들에 속해있는 모든 컬럼의 값들 자체가 중복 체크의 기준이 되는 것이다.
자~, 그러면 이제 MySQL이 내부적으로 UNION ALL과 UNION을 처리하는 과정을 알아보자.
1. 최종 UNION [ALL | DISTINCT] 결과에 적합한 임시 테이블(Temporary table)을 메모리 테이블로 생성
2. UNION 또는 UNION DISTINCT 의 경우, Temporary 테이블의 모든 컬럼으로 Unique Hash 인덱스 생성3. 서브쿼리1 실행 후 결과를 Temporary 테이블에 복사
4. 서브쿼리2 실행 후 결과를 Temporary 테이블에 복사
5. 만약 3,4번 과정에서 Temporary 테이블이 특정 사이즈 이상으로 커지면
Temporary 테이블을 Disk Temporary 테이블로 변경
(이때 Unique Hash 인덱스는 Unique B-Tree 인덱스로 변경됨)
6. Temporary 테이블을 읽어서 Client에 결과 전송
7. Temporary 테이블 삭제
UNION 두 가지의 차이는 2번 과정 딱 하나이다. 중복 제거를 위해서 Temporary 테이블에 인덱스를 생성하느냐 ?. 그렇지 않느냐 ?.별로 중요하지 않은 것 같지만, 이 인덱스로 인해서 3,4번 과정의 작업이 작지 않은 성능 차이가 만들어 내게 된다.
실제 UNION을 실행하는 데이터의 건수에 따라서 다르겠지만, 1.5 ~ 4배 가량의 성능 차이로 UNION ALL이 빠르게 처리된다.
만약 처리중 데이터의 량이 작아서 5번 과정을 거치지 않는다면 메모리 Temporary 테이블에 Hash 인덱스를 사용하기 때문에
속도 차이가 아주 미세할 것이다.
하지만 데이터량이 커져서 5번 과정을 거치게 되면 Disk Temporary 테이블에 B-Tree 인덱스를 사용하기 때문에 큰 성능 차이를 보이게 될 것이다.
이 성능 차이는 UNION 하는 두 집합에 중복되는 레코드가 있든 없든 관계 없이 발생할 것이다.
위에서 잠깐 알아보았던, "중복의 기준"을 생각하면, UNION 하는 컬럼들의 수가 많아지고 레코드의 사이즈가 커질수록 두 작업 모두에게 불리하겠지만, UNION ALL보다는 UNION에 더 악영향이 클 것이다.
결론은,
0. UNION 이든지 UNION ALL이든지 사실 그리 좋은 SQL 작성은 아니다.
UNION이 필요하다는 것은 사실 두 엔터티(테이블)가 하나의 엔터티(테이블)로 통합이 되었어야
할 엔터티들이었는데, 알 수 없는 이유로 분리 운영되는 경우가 상당히 많다.
즉 모델링 차원에서 엔터티를 적절히 통합하여 UNION의 요건을 모두 제거하자.
1. 두 집합에 절대 중복된 튜플(레코드)가 발생할 수 없다는 보장이 있다면 UNION ALL을 꼭 사용하자.
두 집합에서 모두 각각의 PK를 조회하는데, 그 두 집합의 PK가 절대 중복되지 않는 형태
2. 중복이 있다 하더라도 그리 문제되지 않는다면 UNION 보다는 UNION ALL을 사용하자.
3. 만약 UNION이나 UNION ALL을 사용해야 한다면, 최소 필요 컬럼만 SELECT 하자.
MySQL에서는 UNION 집합 연산만 제공하고 있다.
(하지만 MySQL에서 INTERSECT나 MINUS를 다른 형태의 쿼리로 풀어서 사용할 수 있다.)
이 글에서는 UNION 에 대해서 좀 더 자세히 알아 보고자 한다.
UNION 집합 연산은 다시 아래와 같이 두가지 종류로 나누어진다.
- UNION ALL
- UNION DISTINCT
우리가 일반적으로 사용하는 방식인 아무런 추가 키워드 없이 UNION 만 사용하는 것은
UNION DISTINCT 를 줄여서 사용하고 있는 것이다.
UNION ALL과 UNION DISTINCT를 레코드가 많은 결과에 대해서 적용해본 사람은
아마도 둘의 처리 방식에 대해서 의구심을 가져본 적이 있을 것이다.
레코드 건수가 많아지면 많아질수록 그 성능 차이는 엄청난 차이를 보여줄 것이다.
우선, 아래와 같이 2개씩 동일한 레코드 데이터를 가지고 있는 tab1과 tab2라는 테이블이 있다.
mysql>SELECT fdpk, fddata FROM tab1;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
+------+--------+
2 rows in set (0.00 sec)
mysql>SELECT fdpk, fddata FROM tab2;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
+------+--------+
2 rows in set (0.01 sec)
그러면, 이 두개 테이블에 대해서 각각 UNION과 UNION ALL을 사용하는 쿼리를 실행해보자.
mysql>SELECT fdpk, fddata
-> FROM (
-> SELECT fdpk, fddata FROM tab1
-> UNION ALL
-> SELECT fdpk, fddata FROM tab2
-> ) x;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
| 1 | data1 |
| 2 | data2 |
+------+--------+
4 rows in set (0.00 sec)
mysql>SELECT fdpk, fddata
-> FROM (
-> SELECT fdpk, fddata FROM tab1
-> UNION
-> SELECT fdpk, fddata FROM tab2
-> ) x;
+------+--------+
| fdpk | fddata |
+------+--------+
| 1 | data1 |
| 2 | data2 |
+------+--------+
2 rows in set (0.00 sec)
두개의 퀴리 실행 결과 UNION은 레코드가 반으로 줄었다.
이미 다들 알고 있다시피 UNION은 UNION DISTINCT와 동일한 작업을 하기 때문에 중복되는 레코드를 제거했음을 알 수 있다.
하지만, UNION ALL의 경우에는 별도의 중복 제거 과정을 거치지 않고 그냥 결과를 내려준다.
아주 중요한 내용이지만, 사실 이 내용을 다들 별로 신경쓰지 않고 모두들 UNION을 즐겨 사용한다.
안타깝게도, MySQL의 실행계획에서는 둘의 차이를 전혀 느낄 수 없다.
+----+--------------+------------+------+..+------+..+------+------+-------+
| id | select_type | table | type |..| key |..| ref | rows | Extra |
+----+--------------+------------+------+..+------+..+------+------+-------+
| 1 | PRIMARY | <derived2> | ALL |..| NULL |..| NULL | 4 | |
| 2 | DERIVED | tab1 | ALL |..| NULL |..| NULL | 2 | |
| 3 | UNION | tab2 | ALL |..| NULL |..| NULL | 2 | |
|NULL| UNION RESULT | <union2,3> | ALL |..| NULL |..| NULL | NULL | |
+----+--------------+------------+------+..+------+..+------+------+-------+
하지만 중복 제거는 그냥 얻을 수 있는 결과가 아니다.그러면, MySQL이 내부적으로 어떻게 중복을 제거하는 것일까 ?
내부적인 처리를 알아보기 전에, 레코드의 중복이라는 표현을 했는데 이 중복의 기준이 무었일까 ?
1. 각 테이블의 Primary key ?
2. 전체 테이블의 모든 필드 ?
3. 각 서브 쿼리에서 SELECT된 튜플(레코드)의 모든 필드 ?
그렇다. 이미 SELECT된 결과를 가지고 UNION하기 때문에 SELECT되기 전의 테이블이나 레코드에 대한 정보는 알 수 없다.
그래서, 중복 여부의 판단은 SELECT된 튜플들에 속해있는 모든 컬럼의 값들 자체가 중복 체크의 기준이 되는 것이다.
자~, 그러면 이제 MySQL이 내부적으로 UNION ALL과 UNION을 처리하는 과정을 알아보자.
1. 최종 UNION [ALL | DISTINCT] 결과에 적합한 임시 테이블(Temporary table)을 메모리 테이블로 생성
2. UNION 또는 UNION DISTINCT 의 경우, Temporary 테이블의 모든 컬럼으로 Unique Hash 인덱스 생성3. 서브쿼리1 실행 후 결과를 Temporary 테이블에 복사
4. 서브쿼리2 실행 후 결과를 Temporary 테이블에 복사
5. 만약 3,4번 과정에서 Temporary 테이블이 특정 사이즈 이상으로 커지면
Temporary 테이블을 Disk Temporary 테이블로 변경
(이때 Unique Hash 인덱스는 Unique B-Tree 인덱스로 변경됨)
6. Temporary 테이블을 읽어서 Client에 결과 전송
7. Temporary 테이블 삭제
UNION 두 가지의 차이는 2번 과정 딱 하나이다. 중복 제거를 위해서 Temporary 테이블에 인덱스를 생성하느냐 ?. 그렇지 않느냐 ?.별로 중요하지 않은 것 같지만, 이 인덱스로 인해서 3,4번 과정의 작업이 작지 않은 성능 차이가 만들어 내게 된다.
실제 UNION을 실행하는 데이터의 건수에 따라서 다르겠지만, 1.5 ~ 4배 가량의 성능 차이로 UNION ALL이 빠르게 처리된다.
만약 처리중 데이터의 량이 작아서 5번 과정을 거치지 않는다면 메모리 Temporary 테이블에 Hash 인덱스를 사용하기 때문에
속도 차이가 아주 미세할 것이다.
하지만 데이터량이 커져서 5번 과정을 거치게 되면 Disk Temporary 테이블에 B-Tree 인덱스를 사용하기 때문에 큰 성능 차이를 보이게 될 것이다.
이 성능 차이는 UNION 하는 두 집합에 중복되는 레코드가 있든 없든 관계 없이 발생할 것이다.
위에서 잠깐 알아보았던, "중복의 기준"을 생각하면, UNION 하는 컬럼들의 수가 많아지고 레코드의 사이즈가 커질수록 두 작업 모두에게 불리하겠지만, UNION ALL보다는 UNION에 더 악영향이 클 것이다.
결론은,
0. UNION 이든지 UNION ALL이든지 사실 그리 좋은 SQL 작성은 아니다.
UNION이 필요하다는 것은 사실 두 엔터티(테이블)가 하나의 엔터티(테이블)로 통합이 되었어야
할 엔터티들이었는데, 알 수 없는 이유로 분리 운영되는 경우가 상당히 많다.
즉 모델링 차원에서 엔터티를 적절히 통합하여 UNION의 요건을 모두 제거하자.
1. 두 집합에 절대 중복된 튜플(레코드)가 발생할 수 없다는 보장이 있다면 UNION ALL을 꼭 사용하자.
두 집합에서 모두 각각의 PK를 조회하는데, 그 두 집합의 PK가 절대 중복되지 않는 형태
2. 중복이 있다 하더라도 그리 문제되지 않는다면 UNION 보다는 UNION ALL을 사용하자.
3. 만약 UNION이나 UNION ALL을 사용해야 한다면, 최소 필요 컬럼만 SELECT 하자.
출처 : http://intomysql.blogspot.com/2011/01/union-union-all.html
반응형
'프로그램 관련 > oracle' 카테고리의 다른 글
| 컬럼 문자(char), 바이트(byte) 단위 변경 (0) | 2018.09.11 |
|---|---|
| ORACLE DECODE , 사용기~ (0) | 2018.09.11 |
| oracle INTERSECT,MINUS 사용기 (0) | 2018.08.22 |
| oracle union all 입니다 (0) | 2018.08.22 |
| 오라클 권한부여/권한취소 (0) | 2018.08.14 |
