기능적 관점으로 구분하는 결합의 종류
결합 = JOIN
등가 결합: 결합 조건으로 등호(=)를 사용하는 경우입니다.
비등가 결합: 결합 조건으로 부등호(>=, < 등)를 사용하는 경우입니다.
자연 결합: 내부 결합이면서 등가 결합입니다.
•
자연 결합 예시
SELECT *
FROM Employees NATURAL JOIN Departments;
SQL
복사
내부 결합+ 등가 결합 쿼리와 같은 결과를 도출합니다
SELECT * FROM Employees E INNER JOIN Departments D
ON E.dept_id=D.dept_id;
SQL
복사
USING 구를 사용한 경우
SELECT * FROM Employees INNER JOIN Departments
USING(dept_id);
SQL
복사
특별한 사정이 없다면 자연 결합보다 내부 결합을 사용할 것을 추천합니다
1. 크로스 결합
SELECT * FROM Employees
CROSS JOIN Departments;
SQL
복사
2개의 테이블의 레코드에서 가능한 모든 조합을 구하는 연산
(사원 테이블 레코드) * (부서 테이블 레코드) 만큼의 레코드 결과 생성 ⇒ 24개
•
실무에서 사용되지 않는 이유
◦
이러한 결과가 필요한 경우가 없습니다
◦
연산 비용이 많이 들어갑니다
•
실수로 사용한 크로스 결합
SELECT * FROM Employees, Departments;
SQL
복사
◦
결합 조건을 적지 않으면 크로스 결합이 됨
◦
표준 SQL에 맞게 결합 구문을 사용하면 실수를 방지할 수 있음
2. 내부 결합
SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
FROM Employees E INNER JOIN Departments D
ON E.dept_id=D.dept_id;
SQL
복사
내부 결합의 결과는 크로스 결합의 부분 집합입니다
내부 = 데카르트 곱의 부분 집합
•
내부 결합과 같은 기능을 하는 상관 서브쿼리
SELECT E.emp_id, E.emp_name, E.dept_id,
(SELECT D.dept_name FROM Departments D
WHERE E.dept_id=D.dept_id) AS dept_name
FROM Employees E;
SQL
복사
◦
dept_id는 부서 테이블의 기본 키이므로 레코드는 반드시 하나일 것입니다.
◦
기본 키를 사용하면 상관 서브쿼리를 스칼라 서브쿼리로 사용할 수 있습니다.
◦
상관 서브쿼리보다는 결합을 사용하는 것이 실행 비용이 적습니다.
▪
레코드 수만큼 상관 서브쿼리로 사용하게 되어 비용이 높아짐
3. 외부 결합
종류
•
왼쪽 외부 결합: 마스터 테이블을 왼쪽에 위치시키고, 해당 테이블과 결합된 테이블의 데이터를 모두 가져옵니다.
•
오른쪽 외부 결합: 마스터 테이블을 오른쪽에 위치시키고, 해당 테이블과 결합된 테이블의 데이터를 모두 가져옵니다.
•
완전 외부 결합: 마스터 테이블과 결합된 테이블의 데이터를 모두 가져오며, 어느 한 쪽에만 존재하는 데이터도 포함됩니다.
◦
마스터 테이블에만 존재하는 키가 있을 경우에는 제거하지 않고 결과로 보존합니다.
◦
이는 키를 모두 가진 레이아웃의 리포트를 만들 때 자주 사용됩니다.
◦
위와 같은 레코드로 인해 경우에 따라서는 데카르트 곱의 부분 집합이 되기도 한다는 의미
4. 외부 결합과 내부 결합의 차이
•
외부 결합 결과는 마스터 테이블의 정보를 모두 보존하고자 NULL값이 든 레코드를 생성
•
따라서 외부 결합은 크로스 결합의 결과에 없는 레코드를 생성
5. 자기 결합
•
연산의 대상으로 무엇을 사용하는지에 대한 분류임, 생성되는 결과 기준으로 분류하는 것 아님
◦
연산의 대상 = 자신
•
같은 테이블에 별칭을 붙여 다른 테이블인 것처럼 다룸
•
(자기 결합) + (크로스/외부/내부 결합) 의 조합으로 사용 가능
SELECT D1.digit + (D2.digit * 10) AS seq
FROM Digits D1 CROSS JOIN Digits D2;
SQL
복사
◦
0~99까지의 결과가 반환됨
◦
물리레벨에서는 같은 테이블과 결합, but 논리적으로는 ㄷ
결합 알고리즘과 성능
옵티마이저가 선택 가능한 결합 알고리즘
1.
Nested Loop
2.
Hash
3.
Sort Merge
•
옵티마이저는 데이터의 크기, 결합 키의 분산이라는 요인에 의존하여 결합 알고리즘 선택
•
위의 알고리즘 중 지원을 안해주는 DBMS도 존재
◦
MySQL : Hash, Sort Merge 지원 x
Nested Loops
•
결합 알고리즘의 기본
•
중첩 반복을 사용
•
실행 시간은 레코드 수에 비례함
•
한 번의 단계에서 처리하는 레코드 수가 적으므로 Hash, Sort Merge에 비해 메모리 소비가 적음
•
모든 DBMS에서 지원
•
Table A → driving table
•
Table B → inner table
•
구동 테이블의 모든 레코드 하나 하나마다 내부 테이블의 레코드를 하나씩 스캔 후, 결합 조건에 맞으면 리턴
•
A, B 중 어떤 테이블을 구동 테이블로 선언할지가 성능에 큰 요인 (구동 테이블이 작을 수록)
•
구동 테이블(Driving Table)의 중요성
◦
내부 테이블의 결합 키 필드에 인덱스가 존재한다면 내부 테이블을 완전히 순환하지 않고 건너뛸 수 있음
◦
구동 테이블을 작은 테이블로 선택하면 내부 테이블에 결합 키의 인덱스로 접근하여 실행 시간이 줄어들 수 있음
•
구동 테이블을 작게 = 내부 테이블의 결합 키 필드에 인덱스가 존재
◦
이상적인 경우의 예시
▪
구동 테이블의 레코드 한 개에 내부 테이블의 레코드 한 개가 대응하면서 내부 테이블 인덱스를 사용해 찾을 수 있는 경우
▪
접근하는 레코드 수: R(A) * 2 개
▪
내부 테이블의 결합 키 인덱스가 사용될 경우 → NL의 성능 향상
▪
하지만 내부 테이블의 결합 키 인덱스가 사용되지 않으면 드라이빙 테이블이 작아봤자 의미가 없어짐
•
seq scan → full scan
내부 테이블의 반복을 완전하게 생략하는 경우
•
결합키가 내부 테이블에 대해 유일한 경우
•
등치 결합이라면 내부 테이블의 접근 대상 레코드를 한 개로 한정 가능하므로 이중 반복의 내측에 있는 반복을 완전하게 생략
◦
ex) 오라클의 INDEX UNIQUE SCAN
•
만약 결합 키가 내부 테이블에 대해 유일하지 않은 경우 인덱스로 내부 테이블에 접근하는 경우라도 여러 개의 레코드가 히트되어 반복을 적용해야 할 가능성 있음
요약:
(구동 테이블이 작은 Nested Loops) + (큰 내부 테이블의 결합 키에 인덱스)
이 때, 내부 테이블이 커야, 인덱스 사용의 반복 생략 효과가 커짐
Nested Loops의 단점
•
결합 키로 내부 테이블에 접근 할 때, 히트되는 레코드가 너무 많을 경우, 성능이 안나옴
◦
결합 키가 내부 테이블에 대해 유일하지 않은 경우
•
예시: 한 개의 점포 당 받는 주문이 많는 경우
◦
점포 테이블은 작지만, 한 개의 점포당 주문이 수백만…수천만…레코드일 경우
▪
내부 테이블의 선택률이 높다
◦
점포 테이블을 구동 테이블로 만들고 점포 ID를 결합 키로 사용
◦
-> 수백만~수천만 건의 주문 레코드가 히트된다면 내부 테이블에 대해 반복 횟수가 많아져 NL의 성능 저하
•
해결방법:
◦
1) 구동 테이블로 큰 테이블을 선택해 항상 하나의 레코드로 접근함을 보장
▪
주문 테이블을 구동 테이블로..?
◦
2) 해시 사용
Hash
•
작은 테이블을 스캔하고 결합 키에 해시 함수 적용해 해시값으로 변환 후
◦
다른 테이블을 스캔해 결합 키가 해시값에 존재하는지 확인하며 결합 수행
•
해시는 어떤 한 쪽의 데이터 테이블이 극단적으로 작거나 크지 않기 때문에 구동 테이블의 의미가 없음
•
주요 특징
◦
해시 테이블은 DBMS의 워킹 메모리에 저장되므로 작은 테이블에서 해시 테이블을 만듦
◦
Nested Loops에 비해 메모리를 크게 소모
◦
메모리가 부족하면 저장소(디스크)를 사용하므로 지연 발생
◦
출력되는 해시값은 입력값의 순서를 알지 못하므로 등치 결합에만 사용 가능
•
Hash가 유용한 경우
◦
Nested Loops에서 적절한 구동 테이블이 존재하지 않는 경우
◦
Nested Loops에서 구동 테이블로 사용할만한 작은 테이블은 있지만 내부 테이블에서 히트되는 레코드 수가 너무 많은 경우
◦
Nested Loops에서 내부 테이블에 인덱스가 존재하지 않는 경우
•
NL의 차선책 느낌이 Hash Join
•
Hash의 트레이드 오프
◦
양쪽 테이블을 모두 읽어야 하므로, 테이블 풀 스캔이 일어남 (seq scan 2번)
◦
동시 실행성이 높은 OLTP 처리(사용자 요구에 시스템이 바로 응답해야 하는 처리)할 때 메모리 부족으로 지연 발생 리스크 있으므로 사용 불가
◦
테이블 풀 스캔이 사용되는 경우가 많으므로 테이블 규모가 크다면 접근 시간 고려해야 함
Sort Merge
•
결합 대상 테이블들을 각각 결합 키로 정렬하고 일치하는 결합 키를 찾으면 결합
•
대상 테이블을 모두 정렬하므로 많은 메모리 소비
◦
(Hash보다 많은 메모리 사용 가능: Hash는 한쪽 테이블에 대해서만 테이블을 만듦)
◦
메모리 부족으로 TEMP 탈락 발생 시, I/O 비용이 늘어나고 지연이 발생할 위험이 존재
▪
TEMP 탈락
•
임시 데이터(TEMP)가 메모리 공간 부족으로 인해 디스크로 옮겨지는 상황
•
이는 주로 SQL 쿼리 처리 중 임시 결과를 저장하는 데 사용되는 메모리가 부족할 때 발생
•
등치, 부등호를 사용한 결합에 사용 가능, 부정(not) 조건 결합에서는 사용 불가
•
만약… 테이블이 결합 키로 정렬되어 있다면 정렬 생략 가능 (이상론)
•
테이블을 정렬하므로 한쪽 테이블을 모두 스캔한 시점에 결합 완료됨
•
Sort Merge 결합 자체는 레코드가 많더라도 결합 시간 비용이 높지 않지만,
•
테이블 정렬에 대한 많은 시간과 리소스를 요구할 가능성이 높아, Nested Loop과 Hash를 우선적으로 고려
의도하지 않은 크로스 결합
•
삼각 결합
SELECT A.col_a, B.col_b, C.col_c
FROM Table_A A
INNER JOIN Table_B B
ON A.col_a=B.col_b
INNER JOIN Table_C C
ON A.col_a=C.col_c;
SQL
복사
◦
테이블 B와 테이블 C의 결합 조건이 없음
▪
위의 실수는 정말 많이 한 것 같다..!
◦
실행 계획에 따라 테이블 B와 C를 크로스 결합 한 후 테이블 A와 결합할 수 있음
▪
이는 테이블의 크기에 따라 옵티마이저가 실행 계획을 작성하기 때문 (b,c 가 작다고 판단해서 풀 스캔)
•
의도하지 않은 크로스 결합 회피하기
◦
작은 테이블끼리의 크로스 조인은 문제가 되지 않지만, 큰 테이블끼리는 문제가 다르다
◦
결합 조건이 존재하지 않는 테이블 사이에서 결과에 아무 영향을 주지 않는 불필요한 결합 조건을 추가
▪
불필요한 결합 조건 (결과에 영향을 주지 않는 결합 조건)
결합이 느릴 때
1. 상황에 따른 최적의 결합 알고리즘
결합 대상 레코드 수에 따른 최적의 결합 알고리즘
•
소규모 & 소규모: 어떤 알고리즘을 사용해도 성능 차가 크지 않음
•
소규모 & 대규모: 소규모 테이블을 구동 테이블로 하는 Nested Loops 사용, 대규모 테이블의 결합 키에 인덱스 생성
◦
내부 테이블의 결합 대상 레코드가 너무 많다면 구동 테이블과 내부테이블 swap 혹은 Hash 사용
•
대규모 & 대규모: 일단 Hash 사용, 결합 키로 정렬되어 있는 상태라면 Sort Merge 사용
2. 실행 계획 제어
•
DBMS에 따라 힌트 구를 사용해 사용자가 원하는대로 실행 계획을 제어할 수 있는 여부가 다름
◦
postgresql → pg_hint_plan
▪
서버 매개변수 : enable_xx (xx = nestloop, hashjoin, mergejoin)
•
옵티마이저는 완벽하지 않으므로 아직까지는 사용자가 실행 계획을 변경하곤 함
•
하지만 데이터 양과 카디널리티가 계속 변경되므로 사용자가 정의한 실행 계획이 시점에 따라 적절하지 않을 수 있음
◦
DBMS에서는 비용 기반에 따른 동적 실행 계획 도입
3. 흔들리는 실행 계획
•
옵티마이저의 실패
◦
장기적 운용 중 실행 계획이 안 좋은 방향으로 변화해버리는 것
▪
데이터베이스의 통계 정보가 변했을 때 실행 계획을 변화시키는데 사전에 예측하기 어렵고 돌발적인 슬로다운을 일으킴
◦
SQL 성능의 변동 위험을 줄이려면 결합을 피하는 것이 좋다!
•
참고로 데이터베이스의 쿼리가 느린 경우는 대부분, 저장소 io 비용과 메모리 부족이 문제
◦
스케일 업을 고려하거나 자원 점검이 필요
EXISTS, NOT EXISTS
•
Exists의 경우, 주목해야할 부분 : SEMI → 준결합
◦
Exists를 사용할 때, 쓰이는 특수한 알고리즘
◦
결과에는 구동 테이블의 데이터만 포함됩니다. 1개의 레코드는 반드시 1개의 결과만 생성합니다.
▪
일반적인 결합에서는 일대다 결합일 때 레코드 수가 늘어나는 경우가 있습니다
◦
EXISTS를 사용할 수 있는 경우에는, 내부 테이블에서 조건에 맞는 레코드를 하나라도 발견한 시점에서 남은 레코드의 검색을 생략하므로, 일반적인 결합보다 성능이 좋습니다.