1. 조인 연산방식 용어
데이터가 다수의 테이블에 흩어져 있을 때, 필요한 데이터끼리 결합할 때 join이라는 방식을 사용한다. 분리된 데이터 간의 공통된 정보(동일한 컬럼값 또는 키값)를 기준으로 논리적으로 연결할 수 있다.
조인 방식에는 위와 같이 여러 가지가 있다. 양쪽 테이블에 같은 데이터가 있을 때만 결합하는 내부 조인(INNER JOIN), 각각 어느 한쪽에만 데이터가 있어도 데이터를 결합하는 왼쪽 외부 조인(LEFT OUTER JOIN) 또는 오른쪽 외부 조인(RIGHT OUTER JOIN), 그리고 두 테이블의 모든 값을 리턴하는 전체 외부 조인(FULL OUTER JOIN)이 있다. 그 외에 교차 조인(CROSS JOIN), 자연 조인(NATURAL JOIN)이 있다.
FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN이 통합된 조인방식으로, MySQL과 MariaDB에서는 지원하지 않는다.이제 학생 테이블, 지도교수 테이블이 일대일 관계를 유지하며 다음과 같은 레코드가 있다고 가정하고 조인에 대해 살펴보자.
| 학번 | 이름 | 성별 |
|---|---|---|
| 1 | 이순신 | 남 |
| 2 | 신사임당 | 여 |
| 3 | 유재석 | 남 |
| 4 | 강감찬 | 여 |
| 학번 | 지도교수명 |
|---|---|
| 1 | 이황 |
| 2 | 세종대왕 |
| 4 | 김유신 |
| 99 | 이황 |
Inner join
INNER JOIN은 교집합에 해당하는 방식으로, 양쪽에 모두 존재하는 데이터만 반환합니다. 예를 들어, 학생 테이블과 지도교수 테이블에서 학번 열로 INNER JOIN을 수행하면 1, 2, 4라는 학번이 출력된다.
INNER JOIN을 명시적 조인으로 표현한 SQL 문은 다음과 같다. JOIN 키워드 이후에는 조인 대상 테이블을, ON 이후에는 조인할 비교조건을 작성한다.
SELECT
학생.학번,
학생.이름,
지도교수.교수명
FROM
학생
JOIN
지도교수
ON
학생.학번 = 지도교수.학번참고로 위 SQL 문을 암시적 조인(눈에 보이지 않는 형태)으로 바꿔 작성할 수 있다.
SELECT
학생.학번,
학생.이름,
지도교수.교수명
FROM
학생, 지도교수
WHERE
학생.학번 = 지도교수.학번위 쿼리로 INNER JOIN을 수행한 결과는 다음과 같다.
| 학번 | 이름 | 교수명 |
|---|---|---|
| 1 | 이순신 | 이황 |
| 2 | 신사임당 | 세종대왕 |
| 4 | 강감찬 | 김유신 |
Left Outer join
LEFT OUTER JOIN은 왼쪽 테이블(=먼저 작성된 테이블) 기준으로 오른쪽 테이블(나중에 작성된 테이블)과 조인을 수행하지만, 조인 조건과 일치하지 않더라도 왼쪽 테이블의 결과는 최종 결과에 포함된다. 다음 벤 다이어그램을 보면, 학생 테이블에만 존재하는 학번 3이 출력되고 있다.
SELECT
학생.학번,
학생.이름,
지도교수.교수명
FROM
학생
LEFT OUTER JOIN
지도교수
ON
학생.학번 = 지도교수.학번위 쿼리로 LEFT OUTER JOIN을 수행한 결과는 다음과 같다. 학번 3은 지도교수 테이블에는 없는 데이터이므로, 교수명 열에는 NULL 값으로 출력된다.
| 학번 | 이름 | 교수명 |
|---|---|---|
| 1 | 이순신 | 이황 |
| 2 | 신사임당 | 세종대왕 |
| 3 | 유재석 | NULL |
| 4 | 강감찬 | 김유신 |
Right Outer join
RIGHT OUTER JOIN은 LEFT OUTER JOIN과 정반대로 동작한다. 오른쪽 테이블(나중에 작성된 테이블) 기준으로 왼쪽 테이블(먼저 작성된 테이블)과 조인을 하지만, 조인 조건과 일치하지 않더라도 오른쪽 테이블의 결과는 최종 결과에 포함된다. 다음 벤 다이어그램을 보면, 지도교수 테이블에만 존재하는 학번 99가 출력되고 있다.
SELECT
지도교수.학번,
학생.이름,
지도교수.교수명
FROM
학생
RIGHT OUTER JOIN
지도교수
ON
학생.학번 = 지도교수.학번위 쿼리로 RIGHT OUTER JOIN을 수행한 결과는 다음과 같다. 학번 99는 학생 테이블에는 없는 데이터이므로, 이름 열에는 NULL 값으로 출력된다.
| 학번 | 이름 | 교수명 |
|---|---|---|
| 1 | 이순신 | 이황 |
| 2 | 신사임당 | 세종대왕 |
| 4 | 강감찬 | 김유신 |
| 99 | NULL | 이황 |
RIGHT OUTER JOIN은 사실 LEFT OUTER JOIN에서 조인 순서만 바꾼 것이다. 따라서 LEFT, RIGHT를 혼용해서 쓰기보다는 일관성 있는 SQL 문으로 작성하는 편이 관리 편의성 측면에서 유리할 것이다. 참고로 사람의 인지적 특성상 보통 왼쪽 -> 오른쪽을 정방향으로 인식하므로, LEFT OUTER JOIN을 주로 사용한다.
Cross join
CROSS JOIN이란 데카르트 곱(cartesian product)이라고 하는 곱집합 개념으로, 조인에 참여하는 테이블에서 발생할 수 있는 모든 조합을 찾아내어 반환한다. 모든 경우의 수가 출력 대상이므로, INNER JOIN에 비해 훨씬 더 많은 데이터 양을 얻을 수 있다. 단, 조인 연산의 시간적, 공간적 리소스 점유 측면에서 오버헤드가 발생하는 만큼 주의해야 한다.
학생 테이블의 1, 2, 3, 4 학번과 지도교수 테이블의 1, 2, 4, 99학번에 대해 CROSS JOIN을 수행하면 다음과 같이 가지의 결과가 도출된다.
(1, 1), (1, 2), (1, 4), (1, 99),
(2, 1), (2, 2), (2, 4), (2, 99),
(3, 1), (3, 2), (3, 4), (3, 99),
(4, 1), (4, 2), (4, 4), (4, 99)## 명시적 교차 조인
SELECT
학생.학번,
학생.이름,
지도교수.학번,
지도교수.교수명
FROM
학생
CROSS JOIN
지도교수
ON
학생.학번 = 지도교수.학번
## 암시적 교차 조인
SELECT
학생.학번,
학생.이름,
지도교수.학번,
지도교수.교수명
FROM
학생, 지도교수위 쿼리로 CROSS JOIN을 수행한 결과는 다음과 같다.
| 학번 | 이름 | 학번 | 교수명 |
|---|---|---|---|
| 1 | 이순신 | 1 | 이황 |
| 2 | 신사임당 | 1 | 이황 |
| 3 | 유재석 | 1 | 이황 |
| 4 | 강감찬 | 1 | 이황 |
| 1 | 이순신 | 2 | 세종대왕 |
| 2 | 신사임당 | 2 | 세종대왕 |
| 3 | 유재석 | 2 | 세종대왕 |
| 4 | 강감찬 | 2 | 세종대왕 |
| 1 | 이순신 | 4 | 김유신 |
| 2 | 신사임당 | 4 | 김유신 |
| 3 | 유재석 | 4 | 김유신 |
| 4 | 강감찬 | 4 | 김유신 |
| 1 | 이순신 | 99 | 이황 |
| 2 | 신사임당 | 99 | 이황 |
| 3 | 유재석 | 99 | 이황 |
| 4 | 강감찬 | 99 | 이황 |
Natural join
NATURAL JOIN은 2개의 테이블에 동일한 컬럼명이 있을 때 JOIN 조건절을 따로 작성하지 않아도 자동으로 조인을 수행해주는 방식이다. 조인이 제대로 성사되면 INNER JOIN과 동일한 결과가 출력된다. 이때 조인하는 열들의 데이터 유형이 서로 달라도 조인이 수행된다.
예시의 학생, 지도교수 테이블에는 서로 동일한 학번 컬럼이 있으므로, 두 테이블의 NATURAL JOIN을 수행하고 결과를 확인해 보자.
SELECT
학생.*,
지도교수.*
FROM
학생
NATURAL JOIN
지도교수 # 조인 조건절을 알아서 찾아주므로 JOIN 조건문을 작성하지 않는다.| 학번 | 이름 | 성별 | 학번 | 교수명 |
|---|---|---|---|---|
| 1 | 이순신 | 남 | 1 | 이황 |
| 2 | 신사임당 | 여 | 2 | 세종대왕 |
| 4 | 강감찬 | 남 | 4 | 김유신 |
두 테이블에 동일하게 존재하는 학번 컬럼을 토대로 조인되었음을 알 수 있다.
만약 공통으로 존재하는 컬럼명이 하나도 없다면, 발생 가능한 경우의 수를 모두 조합하는 CROSS JOIN으로 수행된다. 이처럼 NATURAL JOIN은 컬럼명 변경에 따라 결과가 의도치 않게 출력될 가능성이 높아, 활용도가 떨어지는 편이다.
2. 조인 알고리즘 용어
다수의 테이블에서 조인을 수행할 때는 접근하는 우선순위를 정하게 된다. 이때 테이블에 접근하는 선후관계에 따라 드라이빙 테이블, 드리븐 테이블이라는 용어로 구분한다.
드라이빙 테이블, 드리븐 테이블
2개 테이블로 분리된 데이터에서 원하는 결과를 추려 결합하는 조인을 수행할 때, 테이블의 데이터에 접근하는 우선순위가 존재한다. 그 중 먼저 접근하는 테이블을 드라이빙 테이블(=outer table), 드라이빙 테이블의 검색 결과를 통해 뒤늦게 데이터를 검색하는 테이블을 드리븐 테이블(=inner table) 이라고 부른다.
조인을 수행할 때 성능을 최대한 살리려면 두 가지를 기억해야 한다.
1. 드라이빙 테이블에서 너무 많은 데이터가 반환되면 드리븐 테이블에서 데이터를 가져올 때 부담이 되므로, 드라이빙 테이블은 가급적 적은 결과가 반환될 것으로 예상되는 것을 선정해야 한다.
2. 조인 조건절의 열이 인덱스로 설정되어야 한다.
중첩 루프 조인
중첩 루프 조인(Nested loop join = NL join)은 드라이빙 테이블의 데이터 1건당 드리븐 테이블을 반복해 검색하며 최종적으로는 양쪽 테이블에 공통된 데이터를 출력하는 방식이다.
이때 조인 조건절 열의 인덱스 설정 유무가 검색의 효율을 좌우한다. 만약 드라이빙 테이블, 드리븐 테이블에 각각 100건, 1,000건의 데이터가 존재하고 SQL 문의 조건이 WHERE driving_table.id IN (1, 25) 라고 가정하고 검색 효율을 비교해 보자.
id 열에 인덱스가 설정되지 않은 경우, 먼저 SQL 문 조건절에 해당하는 데이터를 찾기 위해 드라이빙 테이블 전체를 모두 검색한다. 그 후 드라이빙 테이블 검색 결과와 동일한 데이터가 드리븐 테이블에 있는지 하나하나 차례로 비교하기 시작한다. 이 경우 driving_table을 기준으로 id=1인 데이터를 찾기 위해 (100 + 1,000)건의 데이터에 접근하고, id 25인 데이터를 찾기 위해 (100 + 1,000)건의 데이터에 접근한다. 총 2,200건의 데이터에 접근하는 것이다.
그러나 인덱스가 설정되어 있다면 드라이빙 테이블에서는 인덱스를 이용해 한 번에 id=1인 데이터에 접근할 수 있고, 드리븐 테이블에서도 조인하려는 컬럼에 설정된 인덱스를 이용해 값이 1인 데이터에만 접근한다. 즉 1,000건의 데이터가 모두 id=1 인덱스에 해당하는 것이 아닌 이상, 두 테이블 모두에서 full scan은 일어나지 않는 것이다.
다만, 기본 키 인덱스가 아닌 비고유 인덱스를 사용할 경우 인덱스를 이용해 테이블의 데이터를 찾아가는 과정에서 랜덤 액세스(Random access)가 발생하여, 데이터에 임의로 접근하게 된다. 따라서 랜덤 액세스를 줄일 수 있도록 데이터의 액세스 범위를 좁히는 방향으로 인덱스를 설계하고 조건절을 작성해야 한다.
블록 중첩 루프 조인
블록 중첩 루프 조인(Block nested loop join = BNL join)중첩 루프 조인의 효율성을 높이고자 탄생했다. 드라이빙 테이블에 조인 버퍼(Join buffer)라는 개념을 도입하여 조인 성능을 향상시킨다.
블록 중첩 루프 조인이 작동하는 방식은 다음과 같다.
- 드라이빙 테이블에서 조건에 해당하는 데이터를 검색한다.
- 검색된 데이터를 조인 버퍼에 가득 채워질 때까지 적재한다.
- 이후 조인 버퍼와 드리븐 테이블의 데이터를 비교한다. 이때 드리븐 테이블의 데이터 하나하나를 조인 버퍼와 조인하는 과정을 반복하여, 드리븐 테이블을 full scan한다.
이처럼 조인 버퍼의 데이터들과 드리븐 테이블의 한 번의 테이블 풀 스캔으로 원하는 데이터를 모두 찾을 수 있다. 이렇게 함으로써 드리븐 테이블의 테이블 풀 스캔을 줄일 수 있어 성능 저하를 개선할 수 있다.
배치 키 액세스 조인
배치 키 액세스 조인(Batched key access join = BKA join)은 액세스할 데이터의 범위가 넓을 때 중첩 루프 조인 방식의 비효율성을 개선하기 위해 고안된 알고리즘이다. 랜덤 액세스가 발생하는 중첩 루프 조인 방식은 넓은 데이터 범위에서는 다소 불리하다. 배치 키 액세스 조인 방식은 접근할 데이터를 미리 예상하고 가져옴으로써 이러한 비효율을 해결한다.
배치 키 액세스 조인에서는 블록 중첩 루프 조인에서 활용한 드라이빙 테이블의 조인 버퍼를 그대로 사용하는 한편, 드리븐 테이블에 필요한 데이터를 미리 에측하고 정렬된 상태로 담는 랜덤 버퍼라는 개념을 도입한다.
배치 키 액세스 조인이 작동하는 방식은 다음과 같다.
- 드라이빙 테이블에서 필요한 데이터를 추출하여 조인 버퍼에 적재한다.
- 드리븐 테이블의 인덱스 기반으로 필요한 데이터를 예측하여 랜덤 버퍼에 적재한다.
- 드리븐 테이블의 데이터를 예측하고 정렬된 상태로 버퍼에 적재하는 기능을 다중 범위 읽기(Multi Range Read, MRR) 이라고 한다.
- 미리 예측된 데이터를 가져와 정렬된 상태로 랜덤 버퍼에 담으므로, 드리븐 테이블에 랜덤 액세스가 아닌 시퀀셜 액세스를 수행하는 방식이다.
- 조인 조건절로 조인 버퍼와 랜덤 버퍼를 비교한다.
- 비교 결과 동일한 데이터가 있다고 판단되면 드리븐 테이블의 데이터가 접근하고 결과를 조인하여 반환한다.
해시 조인
해시 조인(Hash join)은 선후 관계를 두고 조인을 수행하는 중첩 루프 조인 방식과 달리, 조인에 참여하는 각 테이블의 데이터를 내부적으로 해시값으로 만들어 내부 조인을 수행한다. 조인 수행 결과는 조인 버퍼에 저장되므로 조인 열의 인덱스가 필수사항이 아니다.
해시 조인은 보통 대용량 데이터의 동등 비교 연산('=')에서 자주 사용된다. 또한 내부적으로 해시값으로 만드는 시간이 필요하므로, 적은 양의 데이터를 찾을 때는 오히려 중첩 루프 조인 방식보다 효율이 떨어질 수 있다.
- 출처: 업무에 바로 쓰는 SQL 튜닝
