드라이빙 테이블과 드라이븐 테이블
다수의 테이블에서 조인을 수행할 때는 동시에 여러 개의 테이블에 접근할 수 없는 만큼 접근하는 우선순위를 정하게 된다.
다수의 테이블에서 첫 번째로 접근할 테이블, 두 번째로 접근할 테이블, 세 번째로 접근할 테이블 등 내부적으로 순번을 정하고, 차례로 테이블에 접근한 결과를 다음 순번의 테이블로 전달한다.
SELECT 학생.학번, 학생.이름,
비상연락망.관계, 비상연락망.연락처
FROM 학생
JOIN 비상연락망
ON 학생.학번 = 비상연락망.학번
WHERE 학생.학번 IN (1,100)이 예제에서는 학생.학번(1,100) 조건이 있으므로 학생 테이블의 데이터를 먼저 찾아볼 것이다.
학생 테이블에서 찾은 결과로 비상연락망 테이블에서 학번 1과 100을 검색한다.
이처럼, 먼저 접근하는 테이블을 드라이빙 테이블(driving table)이라고 하고, 그 결과를 이용하여 뒤늦게 데이터를 검색하는 테이블을 드리븐 테이블(driven table)이라고 한다.
위의 예제에서는 드라이빙 테이블이 학생, 드리븐 테이블이 비상연락망이 된다.
중요한 점❗️
드라이빙 테이블에서 많은 건수가 반환되면 해당 결과를 가지고 드리븐 테이블에 접근하게 되기 때문에, 사실상 드라이빙 테이블을 무엇으로 선정할지는 매우 중요한 문제이다.
가능하면 적은 결과가 반환될 것으로 예상되는 드라이븐 테이블을 선정하고, 조인 조건절의 열이 인덱스로 설정되도록 구성해야 한다.
NL 조인(중첩 루프 조인)
-
기본적으로 Mysql에서 사용하는 조인 방식이다.
-
NL(Nested Loop) 조인은 드라이빙 테이블의 데이터 1건당 드리븐 테이블을 반복해 검색하며 최종적으로는 양쪽 테이블에 공통된 데이터를 출력한다. 마치 2중 for문을 떠올리면 좋을 것 같다.
중첩 로프 조인의 과정을 더 잘 이해해보기 위해 시나리오를 설정해서 이해해보자.
시나리오
학생 데이터: 100개
비상 연락망 데이터: 1000개
1번 학생: 2개의 비상연락망(아버지, 어머니)를 가지고 있음
100번 학생: 1개의 비상연락망을 가지고 있음
Case 1: 학생, 비상연락망 테이블에 인덱스 모두 X
- 학생 테이블에서 학번 1인 데이터를 검색. (테이블 풀 스캔으로 데이터 100건 접근)
- 학번 1과 동일한 데이터를 가졌는지 비교하려고 비상연락망 테이블의 데이터를 찾음.(테이블 풀 스캔으로 데이터 1000건 접근)
- 학생 테이블에서 학번 100인 데이터를 검색. (테이블 풀 스캔으로 데이터 100건 접근)
- 학번 100과 동일한 데이터를 가졌는지 비교하려고 비상연락망 테이블의 데이터 찾음(테이블 풀 스캔으로 데이터 1000건 접근)
이처럼 학번 1데이터(100 + 1000)와 학번 100데이터(100 + 1000)를 찾기 위해 대략 2,200건의 데이터에 접근하였다.
결론: 매우 비효율적인 것을 알 수 있다. 우선 인덱스를 걸고 생각해봐야 할 것 같다.
Case 2: 학생, 비상연락망 테이블에 인덱스 O
학생 테이블: 학번이 고유 인덱스
비상연락망 테이블: 학번이 비고유 인덱스(즉, 하나의 학생이 여러 비상연락망을 가질 수 있음)
- 학생 테이블에서 학번 1인 데이터를 검색. (인덱스를 이용해 학번 1인 학생 데이터에 1번만에 접근)
- 학번 1과 동일한 데이터를 가졌는지 비교하려고 비상연락망 테이블의 데이터를 찾음.(Index Range Scan을 사용해 학번 1인 데이터 2개 바로 접근)
- 학생 테이블에서 학번 100인 데이터를 검색. (인덱스를 이용해 학번 100인 학생 데이터 1번만에 접근)
- 학번 100과 동일한 데이터를 가졌는지 비교하려고 비상연락망 테이블의 데이터 찾음(Index Range Scan을 사용해 학번 100인 데이터 1개 바로 접근)
학번 1인 데이터(1 + 2)와 학번 100인 데이터(1 + 1)를 찾기 위해 6번의 데이터에 접근하게 된다.
❗️하지만 인덱스를 통해 테이블의 데이터를 찾아가는 과정에서 발생하는 Random Access를 고려해야 한다.
결론: Random Access를 고려하더라도, 위의 2,200번의 데이터 접근과 비교해 봤을때 6번은 훨씬 효율적이라는 생각이 든다.
하지만 드라이븐 테이블(학생테이블)에서 조회하고자하는 학생의 수만큼 학생 테이블을 접근해야 한다.(마치 for문과 같다). 버퍼를 이용해 학생테이블 자체 조회를 한번만 할 수 있으면 더 좋을것 같다는 생각이 든다.
다음 시간에 NL 조인보다 더 좋은 성능을 보이는 조인 방식에 대해 공부해 보도록 하자.
Tuning Tip
- Random Accesss를 줄일 수 있도록 데이터의 엑세스 범위를 좁히는 방향으로 설계를 하고 조건절을 작성해야 한다.
- 추가적으로, Random Access를 유발하는 인덱스는 기본키가 아닌 비고유 인덱스의 경우에 해당한다.
기본키는 클러스터형 인덱스이므로 대체적으로 기본 키의 순서대로 테이블의 데이터가 적재되어 있을 것이다.
하지만 삭제가 일어나서 공간이 비고, 새로운 데이터가 그 자리를 대신해 저장되기도 하여, 항상 기본키의 순서대로 저장되는 것은 아니라는 점을 명심하자.
