[MySQL] 7. 복수의 테이블 다루기

<!doctype html>

31. 집합 연산

 
SELECT ...
UNION [ALL | DISTINCT] SELECT ...[UNION [ALL | DISTINCT] SELECT ...]

UNION은 다중 SELECT 문을 하나의 결과 집합으로 결합할 때 사용된다.
SELECT로 지정된 튜플에 속해 있는 모든 컬럼의 값들 자체가 중복 체크의 기준이 된다.

UNION ALL
UNION [DISTINCT]

union과 union all의 실행 계획을 보면 union는 실행 시 임시 테이블을 생성한다는 것을 확인할 수 있다.
union에서 중복제거를 위한? 추가 연산을 수행한다.

MySQL 내부적으로 UNION/UNION ALL 처리과정

최종 UNION [ALL | DISTINCT] 결과에 적합한 임시 테이블(Temporary table)을 메모리 테이블로 생성
UNION 또는 UNION DISTINCT 의 경우, Temporary 테이블의 모든 컬럼으로 Unique Hash 인덱스 생성
서브쿼리1 실행 후 결과를 Temporary 테이블에 복사
서브쿼리2 실행 후 결과를 Temporary 테이블에 복사
만약 3,4번 과정에서 Temporary 테이블이 특정 사이즈 이상으로 커지면 Temporary 테이블을 Disk Temporary 테이블로 변경 (이때 Unique Hash 인덱스는 Unique B-Tree 인덱스로 변경됨)
Temporary 테이블을 읽어서 Client에 결과 전송
Temporary 테이블 삭제

임시테이블 참고

Using temporary (Real MySQL p322, p354) MySQL이 쿼리를 처리하는 동안 중간 결과를 담아두기 위해서 임시 테이블(Temporary table)을 사용 임시 테이블은 메모리상에 생성될 수도 있고, 디스크상에 생성될 수도 있다 임시 테이블이 메모리에 생성됐었는지, 디스크에 생성됐었는지는 실행 계획만으로 판단할 수 없다 (MySQL 서버의 상태 변수 값으로 확인할 수 있다) !주의 “Using temporary”가 표시되지 않지만, 실제 내부적으로는 임시 테이블을 사용할 때도 많다 따라서 “Using temporary” 가 표시되지 않았다고 해서 임시 테이블을 사용하지 않는다라고 판단하지 않도록 주의해야한다 대표적으로 메모리나 디스크에 임시 테이블을 생성하는 쿼리는 다음과 같다 FROM 절에 사용된 서브 쿼리는 무조건 임시 테이블을 생성한다. 이 테이블을 파생 테이블(Derived table)이라고 부르긴 하지만 실체는 임시 테이블이다 “COUNT(DISTINCT column1)”을 포함하는 쿼리도 인덱스를 사용할 수 없는 경우에는 임시 테이블이 만들어진다 UNION이나 UNION ALL이 사용된 쿼리도 항상 임시 테이블을 사용해서 결과를 병합한다 인덱스를 사용하지 못하는 정렬 작업 또한 임시 버퍼 공간을 사용하는데, 정렬해야 할 레코드가 많아지면 결국 디스크를 사용한다. 정렬에 사용되는 버퍼도 결국 실체는 임시 테이블과 같다. (쿼리가 정렬을 수행할 때는 실행 계획의 Extra 칼럼에 “Using filesort”라고 표시된다

UNION의 경우 중복 제거를 위한 임시테이블에 인덱스를 생성한다..

참 고1 참 고2

32. 테이블 결합

1) 곱집합과 교차결합

카티전곱

 
​x
 
mysql> SELECT * FROM X;
+------+| num  |+------+|    1 ||    2 ||    3 |+------+3 rows in set (0.00 sec)​mysql> SELECT * FROM Y;+------+| ch   |+------+| A    || B    || C    |+------+3 rows in set (0.00 sec)​mysql> SELECT * FROM X, Y;+------+------+| num  | ch   |+------+------+|    1 | A    ||    2 | A    ||    3 | A    ||    1 | B    ||    2 | B    ||    3 | B    ||    1 | C    ||    2 | C    ||    3 | C    |+------+------+9 rows in set (0.00 sec)

UNION 연산은 이렇게 나온다.

 
xxxxxxxxxx
 
mysql> SELECT * FROM X
    -> UNION    -> SELECT * FROM Y;+------+| num  |+------+| 1    || 2    || 3    || A    || B    || C    |+------+

2) 내부결합

 
xxxxxxxxxx
 
SELECT *
FROM XINNER JOIN Y    ON X.num = Y.num;+------+------+| num  | num  |+------+------+|    2 |    2 ||    3 |    3 |+------+------+2 rows in set (0.00 sec)

3) 외부결합

MySQL에는 FULL OUTER JOIN이 없다.

 
xxxxxxxxxx
 
SELECT *
FROM XLEFT JOIN Y    ON X.num = Y.num;+------+------+| num  | num  |+------+------+|    2 |    2 ||    3 |    3 ||    1 | NULL |+------+------+​SELECT *FROM XRIGHT JOIN Y    ON X.num = Y.num;+------+------+| num  | num  |+------+------+|    2 |    2 ||    3 |    3 || NULL |    4 || NULL |    5 |+------+------+

INNER JOIN과 OUTER JOIN은 적절히 사용해야 한다.

OUTER JOIN의 부하가 더 크기 때문에 I

JOIN에서의 인덱스 사용

JOIN 연산의 경우 여러 테이블에 접근하여 대량의 데이터를 조회할 수 있기 떄문에 인덱스의 사용 여부에 따라 수행시간이 크게 차이난다. 일단 MySQL의 JOIN은 Nested-Loop 방식이다.

 
xxxxxxxxxx
 
Pseudo Code
for ( record1 : Table1 ) {      // 드라이빙 테이블  for ( record2 : Table2) {     // 드리븐 테이블    if (record1[join_column1] == record2[join_colum2]) {      add_record(record1, record2) // join 조건 만족    } else {      pass // join 조건 불만족    }  }}

인덱스 레인지 스캔으로 레코드를 읽는 순서는 다음과 같다.

인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index seek)이라고 한다.
1번에서 탐색된 위치부터 필요한 만큼 인덱스를 죽 읽는다. 이 과정을 인덱스 스캔(Index scan)이라고 한다.
2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

인덱스 풀 스캔이나 테이블 풀 스캔은 인덱스 탐색(Index seek)과정이 거의 없지만 실제 인덱스나 테이블의 모든 레코드를 읽기 때문에 부하가 높다. 하지만 인덱스 레인지 스캔 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔(Index scan) 과정은 부하가 작지만 특정 인텍스 키를 찾는 인덱스 탐색 과정이 상대적으로 부하가 높은 편이다. 조인 작업에서 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한 번만 수행하고, 그 이후부터는 스캔만 실행하면 된다. 하지만 드리븐 테이블에서는 인덱스 탐색/스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다. 드라이빙/드리븐 테이블이 1:다로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 큰 부하를 차지한다. 옵티마이저는 항상 드라이빙 테이블이 아닌 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.

인덱스 없이 INNER JOIN을 수행을 EXPLAIN으로 확인하면 다음과 같다.

인덱스가 없을 때는 레코드 건수가 적은 테이블을 드리븐 테이블로 선택한다.

인덱스 생성 후 JOIN을 EXPLAIN 출력은 다음과 같다.

INNER JOIN의 경우 테이블 결합 순서와 상관없이 인덱스가 없는 테이블 부터 처리하도록 최적화가 자동으로 이루어진다. 만일 인덱스가 없는 테이블이 드리븐 테이블로 선택되면, 다른 테이블의 레코드 수 만큼 풀 스캔해야 하기 떄문에 속도 저하가 발생한다. 그래서 옵티마이저는 항상 인덱스가 있는 테이블을 드라이빙 테이블로 선택한다. : 풀스캔 하는 것을 최소화 하도록 한다.

LEFT/OUTER JOIN 시 Y의 테이블을 읽기 위해 풀스캔을 한다.

인덱스가 두 테이블에 모두 존재하면 인덱스를 사용하여 LEFT/OUTER JOIN을 수행한다.

또한 조일 칼럼의 비교에서 각 칼럼의 데이터타입이 일치하지 않으면 인덱스를 효율적으로 이용할 수 없다.

Y테이블의 num을 CHAR(4)로 변경한 후 인덱스를 생성하여 INNER JOIN을 다시 실행해봤다. Extra 필드에 Using join buffer를 확인할 수 있다. 인덱스를 활용하지 못하고 있다.

인덱스 사용에 영향을 미치는 데이터 타입 불일치 비교 패턴의 대표적인 예는 아래와 같다.

CHAR 타입과 INT타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우
같은 CHAR 타입이더라도 문자집합이나 콜레이션이 다른 경우
같은 INT 타입이더라도 부호(Sign)가 있는지 여부가 다른 경우

출 처

33. 관계형 모델

관계형 모델의 기본적인 요소는 릴레이션이다.

속성(Attribute) : 열(이름, 타입)
튜플 : 행

관계형 모델은 데이터 구조에 대해 정의한다. 릴레이션은 튜플의 집합이며, 릴레이션에 대한 연산이 집합에 대한 연산에 대으오딘다는 이론을 관계대수라고 한다. 관계대수의 기본규칙은 다음과 같다.

하나 이상의 관계를 바탕으로 연산한다.
연산한 결과, 반환되는 것 또한 관계이다.
연산을 중첩구조로 실행해도 상관없다.

테스트 테이블

 
xxxxxxxxxx
 
CREATE TABLE tableA (str varchar(8), PRIMARY KEY (str));
CREATE TABLE tableB (str varchar(8), PRIMARY KEY (str));INSERT INTO tableA (str) VALUES ('A'), ('B'), ('C'), ('D');INSERT INTO tableB (str) VALUES ('C'), ('D'), ('E'), ('F');

- 합집합

 
xxxxxxxxxx
 
SELECT str FROM tableA
UNIONSELECT str FROM tableB;​+-----+| str |+-----+| A   || B   || C   || D   || E   || F   |+-----+

- 차집합

 
xxxxxxxxxx
 
SELECT str FROM tableA WHERE str NOT IN (
    SELECT DISTINCT str FROM tableB);+-----+| str |+-----+| A   || B   |+-----+​SELECT a.str FROM tableA a LEFT JOIN tableB b ON a.str = b.strWHERE b.str IS NULL;+-----+| str |+-----+| A   || B   |+-----+

- 교집합

 
xxxxxxxxxx
 
SELECT a.str
FROM tableA a, tableB bWHERE a.str = b.str;+-----+| str |+-----+| C   || D   |+-----+​SELECT a.str FROM tableA AS aINNER JOIN tableB AS b    ON a.str=b.str;+-----+| str |+-----+| C   || D   |+-----+

- 곱집합

 
xxxxxxxxxx
 
SELECT *
FROM tableA, tableB;+-----+-----+| str | str |+-----+-----+| A   | C   || B   | C   || C   | C   || D   | C   || A   | D   || B   | D   || C   | D   || D   | D   || A   | E   || B   | E   || C   | E   || D   | E   || A   | F   || B   | F   || C   | F   || D   | F   |+-----+-----+

- 선택

튜플의 추출

 
xxxxxxxxxx
 
SELECT *
FROM tableAWHERE str BETWEEN 'B' AND 'C';+-----+| str |+-----+| B   || C   |+-----+

- 투영

속성의 추출 : 열

 
xxxxxxxxxx
 
SELECT str
FROM tableA+-----+| str |+-----+| A   || B   || C   || D   |+-----+

- 결합

릴레이션끼리 교차결합해 계산된 곱집합에서 결합조건을 만족하는 튜플을 추출하는 연산

 
xxxxxxxxxx
 
SELECT *
FROM tableA AS aINNER JOIN tableB AS b    ON a.str = b.str;+-----+-----+| str | str |+-----+-----+| C   | C   || D   | D   |+-----+-----+

참 고

저작자표시 비영리 변경금지

'DB > MySQL' 카테고리의 다른 글

[MySQL] DATETIME, TIMEZONE (0)	2021.04.29
[MySQL]8. 데이터베이스 설계 (0)	2019.05.17
[MySQL] 6. 데이터베이스 객체 작성과 객체 (0)	2019.05.12
[MySQL] 5. 집계와 서브쿼리 (0)	2019.05.04
[MySQL] 4. 데이터의 추가, 삭제, 갱신 (0)	2019.05.03

이것저것끄적

[MySQL] 7. 복수의 테이블 다루기

31. 집합 연산

32. 테이블 결합

1) 곱집합과 교차결합

2) 내부결합

3) 외부결합

JOIN에서의 인덱스 사용

33. 관계형 모델

- 합집합

- 차집합

- 교집합

- 곱집합

- 선택

- 투영

- 결합

'DB > MySQL' 카테고리의 다른 글

티스토리툴바

[MySQL] 7. 복수의 테이블 다루기

31. 집합 연산

32. 테이블 결합

1) 곱집합과 교차결합

2) 내부결합

3) 외부결합

JOIN에서의 인덱스 사용

33. 관계형 모델

- 합집합

- 차집합

- 교집합

- 곱집합

- 선택

- 투영

- 결합

'DB > MySQL' 카테고리의 다른 글

'DB/MySQL' Related Articles

티스토리툴바