테이블 조인

데이터베이스에서 데이터 중복을 줄이고 테이블을 분리(정규화)하면서 테이블 사이에 관계를 가지게 되었다

그렇다면 그 분리된 테이블에서 데이터를 합쳐서 하나의 레코드로 읽어오려면 테이블의 데이터를 조합해야 한다

관계형 데이터베이스는 이러한 연산을 조인이라고 하며 어떻게 데이터를 결합하여 원하는 데이터를 추출하는지 여러 조인에 대해 알아보자

 

환경: MySQL 8.0 이상 버전

---

조인이란?

조인에 대한 개념은 다음과 같다

• 두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는것
• JOIN은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 기능이라고 할 수 있다
• 일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립
  • 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능

필요성?

 

여러 테이블에 분리된 데이터는 어떤 키값(결정자)을 기준으로 관계를 이루고 있을 것이다

만약 여러 테이블에 대해 각각의 연산으로 데이터를 읽어오고 결과를 도출할 때만 합친다면? 

 

그 사이에 발생할 수 있는 위험 요소들을 관리하는 것은 복잡하고 비용이 클 것이라고 생각한다

• 동시성 문제로 인한 일관성
• 이상현상
• 비정상 종료에 대한 처리

따라서 하나의 쿼리에서 여러 테이블의 데이터를 결합하여 가져올 수 있다면 관리하기 용이할 것이다

 

그렇기 때문에 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 기능이라고 표현하는 것 같다

 

---

조인 연산의 종류와 사용법

 

조인 연산의 종류와 예시 쿼리를 한눈에 알아볼 수 있는 대표적인 사진이다

 

크게 구분하면 Inner Join과 Outer Join으로 구분 지을 수 있으며 이는 논리적 조인이라고 하며 실제 DBMS의 옵티마이저가 조인을 하는 연산에서 어떻게 데이터를 결합해서 가져올 것인지는 물리적 조인에 의해 결정된다

---

물리적 조인

물리적 조인의 종류는 Nested Loop Join, Merge Join, Hash Join, Index Join이 있다 간단하게 각각을 비교해 보자

 

• Nested Loop Join
  • 하나의 테이블을 완전히 스캔하면서 다른 테이블을 순차적으로 스캔하며 일치하는 행을 찾는다
  • 작은 테이블과 큰 테이블 간의 조인에 적합
  • 후행 테이블의 인덱스를 사용하여 효율적으로 수행될 수 있다

 

• Merge Join
  • 테이블을 정렬한 후 조인을 진행
  • 데이터 처리량이 많을 때 이점이 있는 대신 정렬 시 메모리 사용량이 커짐(인덱스 테이블을 이용하면 될 것 같음)

 

 

• Hash Join
  • 상대적으로 작은 테이블로 Hash Table을 생성
  • 다른 테이블을 해싱하고 해시테이블을 탐색하여 조인하는 방식
  • 해싱테이블을 만드는 비용이 크다

 

• Index Join
  • 인덱스를 사용하여 두 테이블 간의 조인을 수행하는 방식
  • 인덱스 테이블은 정렬되어 있으므로 Merge Join에서 정렬 과정이 필요 없음

그렇다면 물리적 조인 방식을 확인했으니 논리적인 조인을 알아보고 실제 데이터가 있는 DB에 테스트하며 실행계획을 통해 물리적인 조인이 어떻게 사용되고 있는지 확인해 보자 

---

테스트용 DB

 

Employees 테이블

 

DepartMent 테이블

 

테이블 관계

Mysql Workbench에서 제공하는 Reverse Engineer 연결된 키는 department_id인데 조금 아쉬운 것 같다

---

Inner Join

Inner Join

select E.employee_id, E.first_name, E.last_name, E.email, D.department_id, D.department_name, D.manager_id
	from employees as E
	inner join departments as D on E.department_id = D.department_id
	where D.department_id = 30;

 

• 두 테이블의 공통된 데이터를 기준으로 레코드를 읽어오는 방식

inner join을 사용하지 않고도 where절로 읽어오는 방식이 존재하며 이를 묵시적인 명시라고 한다

select E.employee_id, E.first_name, E.last_name, E.email, D.department_id, D.department_name, D.manager_id
from employees as E, departments as D
where E.department_id = D.department_id and D.department_id = 30;

where 절로 풀어서 사용할 수 있다 

 

실행계획을 확인해 보면 inner join과 where절 모두 동일한 실행 계획을 가지고 처리한다

explain format=tree select E.employee_id, E.first_name, E.last_name, E.email, D.department_id, D.department_name, D.manager_id
from employees as E
inner join departments as
    D on E.department_id = D.department_id;

-> Nested loop inner join  (cost=47.3 rows=241)
     -> Table scan on D  (cost=2.95 rows=27)
     -> Index lookup on E using emp_department_ix (department_id=d.department_id)  (cost=0.783 rows=8.92)

explain format=tree select E.employee_id, E.first_name, E.last_name, E.email, D.department_id, D.department_name, D.manager_id
from employees as E, departments as D
where E.department_id = D.department_id;
  
-> Nested loop inner join  (cost=47.3 rows=241)
     -> Table scan on D  (cost=2.95 rows=27)
     -> Index lookup on E using emp_department_ix (department_id=d.department_id)  (cost=0.783 rows=8.92)

 

inner join을 호출하나, where로 호출하나 옵티마이저에서 최적화를 통해 실행 계획을 세우기 때문에 같은 결과가 나타난다는 것이다

 

Explain으로 나온 실행계획을 보자

• Table scan on D: 두 테이블 중 비교적 작은 크기의 D 테이블을 읽는다
• Index lookup on E using index
  • E의 인덱스를 조회한다
  • E의 인덱스를 사용하여 D 테이블에서 읽은 데이터와 같은지 확인한다
• Nested loop inner join: 위의 두 테이블 데이터를 비교할 때 사용되는 물리적인 조인으로 중첩 반복문을 의미
  • 중첩 반복으로 하나의 레코드 당(D) 반대 테이블의 모든 레코드(Index)와 체크한다

Nested loop inner join이 선택된 이유는 departments 테이블이 상대적으로 작고, employees 테이블의 emp_department_ix 인덱스를 사용하여 빠르게 검색할 수 있기 때문

이러한 상황에서는 Nested loop inner join이 효율적인 조인 알고리즘이 될 수 있다

---

Outer Join

outer join의 경우 조인을 했을 때 한쪽의 테이블에 데이터가 없어도 조인 결과에 포함시키는 방식

Inner Join과 다르게 공통된 데이터만 조회하는 것이 아니라 기준이 되는 테이블에 대해 공통 데이터를 가져오면서 없으면 NULL로 채워서 가져온다

select E.employee_id, E.first_name, E.last_name, D.department_name, D.manager_id
from employees as E left outer join departments as D on E.department_id = D.department_id;

• Employees 테이블의 모든 데이터와 Departments와 매칭이 되는 데이터를 포함하여 결과를 반환

Right Join을 하고 싶다면 테이블의 위치를 바꿔도 되고, right outer join으로 실행해도 된다

select E.employee_id, E.first_name, E.last_name, D.department_name, D.manager_id
from employees as E right outer join departments as D on E.department_id = D.department_id;

• Departments 테이블의 모든 데이터와 Employees와 매칭이 되는 데이터를 포함하여 결과를 반환한다

Employees와 Departments의 모든 데이터를 출력하고 싶다면 full outer join을 사용하면 된다

select E.employee_id, E.first_name, E.last_name, D.department_name, D.manager_id
from employees as E left outer join departments as D on E.department_id = D.department_id

union

select E.employee_id, E.first_name, E.last_name, D.department_name, D.manager_id
from employees as E right outer join departments as D on E.department_id = D.department_id;

• Mysql에선 직접적인 full outer join을 제공하지 않기 때문에 각 outer join을 UNION 한 결과와 같다
• 각 테이블을 기준으로 모든 데이터와 매칭되는 데이터를 출력한 결과를 UNION 한다

---

Join의 장점

 

• 데이터 정규화: Join을 사용하여 데이터를 여러 테이블로 분할하고 관련 정보를 연결할 수 있습니다. 이렇게 하면 데이터 중복을 줄이고 데이터 일관성을 유지할 수 있습니다.
• 데이터 무결성: Join은 데이터 무결성을 유지하기 위한 중요한 도구입니다. 데이터베이스에서 외래 키(Foreign Key) 제약을 사용하여 관계를 강제화할 수 있으며, 이를 통해 데이터 일관성을 보장할 수 있습니다.
• 효율적인 데이터 저장: Join을 사용하면 관련성이 있는 데이터를 효율적으로 저장할 수 있습니다. 이로 인해 데이터베이스의 공간 사용이 최적화되고, 불필요한 데이터 중복이 줄어듭니다.
• 질의 복잡성 감소: Join을 사용하면 하나의 복잡한 쿼리 대신 여러 개의 간단한 쿼리를 사용하여 데이터를 검색할 수 있습니다. 이로 인해 쿼리 작성 및 유지 보수가 더 쉬워집니다.
• 유연성: Join을 통해 데이터를 여러 가지 방식으로 연결하고 다양한 관계를 모델링할 수 있습니다. 이를 통해 데이터베이스 스키마를 조정하거나 쿼리를 변경하지 않고도 새로운 비즈니스 요구 사항에 대응할 수 있습니다.
• 데이터 추출 및 분석: Join을 사용하면 데이터베이스에서 필요한 정보를 추출하고 분석하는 데 효율적으로 접근할 수 있습니다.
• 성능 최적화: 데이터베이스 관리 시스템은 Join 연산을 최적화하기 위한 여러 가지 방법을 제공합니다. 인덱스를 효율적으로 활용하거나 조인 알고리즘을 선택함으로써 성능을 향상시킬 수 있습니다.
• 일관된 데이터: Join을 사용하면 관련된 데이터를 연결하여 일관된 뷰를 제공할 수 있습니다. 이로써 데이터의 일관성이 유지되며, 사용자가 일관된 데이터를 볼 수 있습니다.

Join의 단점

• 성능 문제: Join 연산은 여러 테이블 간의 데이터를 결합하는 작업으로, 특히 큰 테이블과 복잡한 Join 조건을 가진 쿼리에서는 성능 문제가 발생할 수 있습니다. 이를 해결하기 위해 쿼리 튜닝과 인덱스 최적화 등의 작업이 필요할 수 있습니다.
• 복잡성: Join은 쿼리의 복잡성을 높일 수 있습니다. 특히 여러 개의 테이블을 조인하는 복잡한 쿼리를 작성하면 쿼리의 이해와 유지 보수가 어려울 수 있습니다.
• 무결성 유지: Join 연산을 사용하면 데이터 무결성을 유지하기 위한 추가적인 주의가 필요합니다. 올바른 외래 키(Foreign Key) 제약과 관계를 설정하고 관리해야 합니다.
• 인덱스 및 메모리 사용: Join을 수행할 때 인덱스를 사용하면 성능을 향상시킬 수 있지만, 인덱스를 관리하고 메모리를 사용하기 때문에 추가적인 리소스가 필요합니다.
• 결과 집합 크기: Join을 사용하면 결과 집합의 크기가 늘어날 수 있습니다. 특히 Cartesian Product(카티션 곱)를 유발하는 실수로 인해 의도치 않게 큰 결과 집합이 생성될 수 있습니다.
• 성능 예측의 어려움: Join 연산의 성능을 예측하기 어려울 수 있습니다. 데이터의 분포, 인덱스 사용 여부, 조인 알고리즘 등 다양한 요소가 성능에 영향을 미치기 때문입니다.
• 정규화의 한계: Join을 많이 사용하면 정규화된 데이터베이스 설계가 과도하게 복잡해질 수 있습니다. 이로 인해 쿼리 성능이 저하될 수 있습니다.
• 외부 데이터 소스와의 조인 어려움: 외부 데이터 소스와의 조인은 일반적으로 데이터베이스 시스템의 기능을 넘어서는 경우가 있어 어려울 수 있습니다.

---

Reference

 

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=356 

조인 수행 원리

https://advenoh.tistory.com/23

관계형 데이터베이스에서 조인(join)이란?