[SQLD SP02] 물리적 JOIN(조인)의 모든 것

 

논리적 조인 포스트:

 

2025.02.13 - [자격증/SQLD] - [SQLD] 3-3: 논리적 JOIN (조인)의 모든 것

[SQLD] 3-3: 논리적 JOIN (조인)의 모든 것

 

🔹 논리적 조인 vs. 물리적 조인

SQL에서 조인은 크게 두 가지 관점에서 분류할 수 있습니다.

 

구분	설명	종류
논리적 조인 (Logical Join)	데이터가 어떻게 결합되는지를 정의	INNER JOIN, OUTER JOIN (LEFT, RIGHT, FULL), CROSS JOIN, SELF JOIN
물리적 조인 (Physical Join)	SQL 엔진이 실제로 조인을 수행하는 방법	Nested Loop Join, Sort Merge Join, Hash Join

 

즉, 논리적 조인은 데이터베이스 사용자가 SQL을 작성할 때 사용하는 개념,
반면 물리적 조인은 SQL 엔진이 내부적으로 SQL을 실행할 때 선택하는 방식입니다.

 

 

---

 

[SQLD SP02] 물리적 조인(JOIN)의 모든 것

---

🔹 해시 조인(Hash Join)이란?

해시 조인은 데이터베이스에서 대량의 데이터를 조인할 때 효과적인 기법으로, 해시 함수(Hash Function)를 사용하여 조인을 수행하는 방식입니다.
특히 조인 칼럼에 인덱스가 없는 경우에도 효과적으로 사용할 수 있는 조인 방법입니다.

---

🔸 Hash Join의 동작 원리

Hash Join은 두 개의 입력 테이블에서 조인 연산을 수행하는 방식으로, **두 단계(Build 단계, Probe 단계)**로 진행됩니다.

1️⃣ Build 단계 (선행 테이블 - Build Input)

• **선행 테이블(Smaller Table)**의 조인 키 값을 기반으로 해시 테이블(Hash Table)을 생성합니다.
• 이때, **해시 함수(Hash Function)**를 사용하여 같은 값은 같은 해시 버킷(Hash Bucket)에 저장되도록 합니다.
• 해시 테이블은 메모리에 저장되며, 크기가 클 경우 일부는 디스크에 저장될 수 있습니다.

2️⃣ Probe 단계 (후행 테이블 - Probe Input)

• **후행 테이블(Larger Table)**을 순차적으로 읽으면서, Build 단계에서 만든 해시 테이블을 참조하여 조인된 데이터를 찾음.
• 후행 테이블의 조인 키 값을 해시 함수에 적용하여 해당 키가 해시 테이블에 있는지 검사하는 방식.

 

---

🔸 Hash Join의 특징

✅ 해시 조인은 인덱스가 없어도 수행 가능

• 조인 키에 인덱스가 존재하지 않아도 해시 함수를 이용하여 조인을 수행할 수 있음.
• 따라서, 대량의 데이터를 인덱스 없이도 빠르게 조인할 수 있는 장점이 있음.

✅ '=' (동등 조인)만 지원

• Hash Join은 해시 함수가 동일한 값을 동일한 해시 버킷으로 매핑하는 원리를 사용하기 때문에,
  비교 연산자(=, EQUI JOIN)를 사용하는 동등 조인(Equal Join)에서만 사용 가능.
• 범위 조인 (<, >, BETWEEN)에서는 사용할 수 없음.

✅ CPU 연산이 많이 발생

• 해시 테이블을 만들고 해시 함수를 적용하는 과정에서 CPU 연산 부담이 큼.
• 따라서, 작은 테이블을 선행 테이블(Build Input)로 사용하는 것이 유리.

✅ 메모리 사용량이 중요

• 해시 테이블을 메모리에 적재해야 하므로, 메모리가 충분한 경우에 효과적.
• 하지만, 해시 테이블이 **메모리를 초과하면 디스크의 임시 공간(TEMP 영역)**을 사용하게 되어 성능이 저하될 수 있음.

✅ 선행 테이블(Build Input)은 작은 테이블이 유리

• 해시 테이블을 만들 때 작은 테이블을 Build Input으로 설정하면 메모리 사용을 줄이고 성능을 향상시킬 수 있음.
• 후행 테이블(Probe Input)은 큰 테이블이더라도 조인 과정에서 빠르게 탐색 가능.

✅ 병렬처리에 유리

• 해시 함수는 여러 개의 해시 버킷으로 데이터를 나누므로, **병렬 처리(Parallel Execution)**에 유리함.

 

---

🔸 Hash Join 실행 예제

다음 SQL을 실행하면 Hash Join이 사용될 가능성이 있음.

SELECT E.EMPLOYEE_ID, E.EMPLOYEE_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

• 위 쿼리는 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 부서 ID (DEPARTMENT_ID) 기준으로 조인합니다.
• EMPLOYEES가 큰 테이블이고, DEPARTMENTS가 작은 테이블이라면 DEPARTMENTS가 Build Input이 되고 EMPLOYEES가 Probe Input이 됨.

---

🔸 Hash Join의 장점과 단점

✅ 장점

✔ 대량의 데이터를 빠르게 조인 가능
✔ 인덱스가 없어도 조인 가능
✔ 병렬 처리에 유리

❌ 단점

✖ 해시 테이블을 만들기 때문에 CPU 연산이 많이 발생
✖ 해시 테이블이 커질 경우 메모리 초과로 인해 디스크 I/O 비용 증가
✖ 동등 조인(=)에서만 사용 가능하며, 범위 조인(<, >, BETWEEN)에서는 불가능

 

 

---

🔹 Nested Loop Join (NL Join)

Nested Loop Join은 **하나의 테이블(외부 테이블)**을 순회하면서 **다른 테이블(내부 테이블)**을 반복적으로 조회하는 방식의 조인입니다.
일반적으로 인덱스가 있는 경우 빠르게 동작하며, 데이터 양이 적을 때 효과적입니다.

---

1️⃣ Nested Loop Join의 동작 원리

두 개의 테이블(외부 테이블과 내부 테이블)을 사용하여 반복문을 수행하면서 조인하는 방식입니다.

✅ 1. 외부 테이블(Outer Table) 선택

• 먼저, 외부 테이블에서 한 행을 선택합니다.

✅ 2. 내부 테이블(Inner Table)에서 매칭되는 값 찾기

• 내부 테이블에서 조인 조건을 만족하는 행을 찾음 (주로 인덱스를 활용하여 빠르게 검색).

✅ 3. 반복 수행

• 외부 테이블의 모든 행에 대해 내부 테이블을 반복 조회하면서 조인을 수행.

 

---

2️⃣ Nested Loop Join의 특징

✅ 인덱스가 있는 경우 빠름

• 내부 테이블(Inner Table)의 조인 칼럼에 인덱스가 존재하면 빠르게 검색 가능.
• 하지만, 인덱스가 없으면 성능 저하.

✅ 모든 비교 연산(=, <, >, BETWEEN)에서 사용 가능

• 비교 연산자를 자유롭게 사용할 수 있어 다양한 조인 조건을 적용 가능.

✅ 소규모 데이터에서 적합

• 데이터 양이 적거나 선행 테이블(Outer Table)의 행 수가 적을 때 유리.
• 하지만, 대량의 데이터를 조인하면 성능이 급격히 저하됨 (O(N*M) 성능).

✅ 랜덤 I/O 발생 가능

• 인덱스를 사용하지 않으면 매번 내부 테이블을 전체 검색(Full Scan) 하게 되어 성능이 크게 떨어질 수 있음.

 

---

3️⃣ Nested Loop Join 실행 예제

SELECT E.EMPLOYEE_ID, E.EMPLOYEE_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

• 외부 테이블(EMPLOYEES)의 한 행을 선택한 후,
• 내부 테이블(DEPARTMENTS)의 인덱스를 사용하여 조인되는 행을 찾음.

---

4️⃣ Nested Loop Join의 장점과 단점

✅ 장점

✔ 인덱스를 활용하면 빠르게 수행 가능
✔ 모든 비교 연산(=, <, >, BETWEEN) 사용 가능
✔ 작은 데이터셋에서는 빠른 성능

❌ 단점

✖ 대량 데이터 조인 시 비효율적 (O(N*M) 연산)
✖ 내부 테이블에 인덱스가 없으면 Full Scan 발생 → 성능 저하
✖ 랜덤 I/O 증가로 인해 디스크 I/O가 많아질 수 있음

 

 

---

🔹 Sort Merge Join (SMJ)

Sort Merge Join은 두 개의 테이블을 정렬한 후, 병합하여 조인하는 방식입니다.
인덱스가 없어도 대량의 데이터 조인에서 효율적이며, 모든 비교 연산(=, <, >, BETWEEN)에서 사용 가능합니다.

---

1️⃣ Sort Merge Join의 동작 원리

Sort Merge Join은 **두 단계(Sort 단계, Merge 단계)**로 이루어집니다.

✅ 1. Sort 단계 (정렬)

• 조인 대상 테이블을 조인 키 기준으로 정렬합니다.
• 정렬이 완료된 후, 두 개의 테이블을 나란히 비교할 수 있는 상태가 됨.

✅ 2. Merge 단계 (병합)

• 정렬된 두 테이블을 하나씩 스캔하면서 일치하는 값들을 빠르게 매칭.
• 양쪽 테이블을 순차적으로 스캔하기 때문에 빠름.

 

---

2️⃣ Sort Merge Join의 특징

✅ 대량 데이터에서 효과적

• 인덱스가 없어도 성능이 우수하여 대량 데이터 조인에서 활용됨.
• 단, 정렬(Sort) 과정이 필요하기 때문에 데이터가 정렬되어 있지 않으면 비용 발생.

✅ 모든 비교 연산(=, <, >, BETWEEN)에서 사용 가능

• Nested Loop Join과 달리 동등 조인(=)뿐만 아니라, 범위 조인(<, >, BETWEEN)에서도 사용 가능.

✅ 순차적 접근이므로 디스크 I/O 감소

• 정렬 후 순차적으로 테이블을 읽으며 조인하므로 랜덤 I/O가 줄어듦.

✅ 정렬이 되어 있으면 빠르게 수행 가능

• 조인 대상이 이미 정렬되어 있다면 Sort 단계 없이 Merge 단계만 수행하므로 성능이 향상됨.

 

---

3️⃣ Sort Merge Join 실행 예제

SELECT E.EMPLOYEE_ID, E.EMPLOYEE_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY E.DEPARTMENT_ID, D.DEPARTMENT_ID;

• 두 테이블을 DEPARTMENT_ID 기준으로 정렬한 후,
• 정렬된 상태에서 병합(merge)하여 조인 수행.

 

---

4️⃣ Sort Merge Join의 장점과 단점

✅ 장점

✔ 인덱스가 없어도 효율적
✔ 모든 비교 연산(=, <, >, BETWEEN)에서 사용 가능
✔ 대량 데이터에서 효과적, 특히 정렬된 경우 더욱 빠름
✔ 순차적인 디스크 접근으로 랜덤 I/O 감소

❌ 단점

✖ 정렬(Sort) 단계가 필요하므로 추가적인 비용 발생
✖ 데이터가 정렬되어 있지 않으면 성능 저하 가능
✖ 메모리 사용량이 많을 수 있음

 

---

🔹 조인 방식 비교

 

조인 방식	인덱스 필요 여부	사용 가능한 조건	성능 특징
Nested Loop Join	✅ 필요 (인덱스 없으면 비효율적)	✅ 모든 비교 연산 (=, <, >, BETWEEN) 가능	작은 데이터에 적합, 인덱스 없으면 Full Scan 발생
Sort Merge Join	❌ 필요 없음 (정렬 필요)	✅ 모든 비교 연산 (=, <, >, BETWEEN) 가능	대량 데이터에서 유리, 정렬된 경우 매우 빠름
Hash Join	❌ 필요 없음	❌ = (동등 조인)만 가능	대량 데이터에서 효과적, CPU/메모리 사용 높음

 

 

---

🔹 결론

• Nested Loop Join:
  • 소량의 데이터에 적합.
  • 내부 테이블에 인덱스가 있으면 빠름, 하지만 없으면 비효율적.
  • 랜덤 I/O 발생 가능 → 디스크 I/O 성능에 영향을 받을 수 있음.
• Sort Merge Join:
  • 대량의 데이터에 적합.
  • 모든 비교 연산 가능(=, <, >, BETWEEN).
  • 정렬 비용이 있지만, 정렬된 데이터에서는 매우 빠름.
  • 순차적인 디스크 접근으로 I/O 성능이 우수.
• Hash Join:
  • 인덱스 없이 대량 데이터를 조인할 때 효과적.
  • 메모리 사용량이 많고, CPU 연산 부담이 크므로 신중하게 사용해야 함.
  • 동등 조인(=)에서만 사용 가능.

 

 

 

---

 

💡 도움이 되셨다면 댓글과 공감 부탁드립니다! 😊
📌 더 많은 알고리즘 풀이와 프로그래밍 자료는 블로그에서 확인하세요!
✉️ 문의나 피드백은 댓글이나 이메일로 남겨주세요.