Join 조인

1. Driving, Driven Table

드라이빙 테이블 (Driving Table)	조인을 시작할 때 먼저 읽는 테이블. 즉, 기준이 되는 테이블.
드리븐 테이블 (Driven Table)	드라이빙 테이블의 각 행에 대해 조인 조건으로 탐색되는 테이블. 즉, 두 번째로 읽는 테이블.

MySQL 옵티마이저는 조인 실행 전에 통계 정보(카디널리티, 인덱스, 조건 절 등) 를 분석해서
다음과 같은 기준으로 드라이빙 테이블을 결정합니다.

필터 조건	WHERE 절 조건으로 결과 행이 적은 테이블을 먼저(=드라이빙) 읽는 게 효율적
인덱스 존재 여부	드리븐 테이블은 조인 조건 열에 인덱스가 있으면 빠르게 검색 가능
조인 유형	INNER JOIN은 옵티마이저가 순서를 바꿀 수 있지만, LEFT JOIN은 바꿀 수 없음

 

조인의 종류에 따라 “순서 변경 가능 여부”가 달라집니다.

INNER JOIN	옵티마이저가 판단해 변경 가능	✅ 가능
LEFT JOIN	왼쪽 테이블이 반드시 드라이빙 테이블	❌ 불가능
RIGHT JOIN	오른쪽 테이블이 반드시 드라이빙 테이블	❌ 불가능

 

예시

EXPLAIN 
 SELECT e.emp_no, e.first_name, s.salary
   FROM employees e
   JOIN salaries s ON e.emp_no = s.emp_no
  WHERE e.gender = 'M';

결과의 id와 table, type 컬럼을 보면 같은 id값은 조인이 되었다는 것이고 같은 id값 중 상위에 있는게 드라이빙 테이블이다.

• 먼저 나오는 테이블이 드라이빙 테이블 (e 테이블은 employees)
• 그다음 테이블이 드리븐 테이블 (s 테이블은 salaries)

id | select_type | table | type  | possible_keys | key  | rows
---+--------------+-------+-------+----------------+------+------
 1 | SIMPLE       | e     | ref   | idx_gender     | ...  | 10000
 1 | SIMPLE       | s     | ref   | idx_emp_no     | ...  | 10

조회 결과 row수가 적은 테이블이 드라이빙 테이블이 되는게 성능상 유리하다. 

 

2. Join ( 조인 )

조인은 두 개(혹은 그 이상)의 테이블을 합쳐서 필요한 데이터를 찾는 방법이다.

 SELECT e.emp_no, e.first_name, s.salary
   FROM employees e
   JOIN salaries s 
     ON e.emp_no = s.emp_no;

 

• employees 테이블에는 직원 정보가 있고
• salaries 테이블에는 급여 정보가 있다.
• 두 테이블을 emp_no를 기준으로 합치는 것이다.

 

 

3. Nested Loop Join ( NLJ, 중첩 루프 조인 )

“중첩 루프”라는 말 그대로,
하나의 테이블을 기준으로(loop 1),
그 안의 각 행마다 다른 테이블(loop 2)을 반복해서 조회하는 방식이다.

즉, 이렇게 동작한다.

(1) 바깥 테이블(Outer Table) 의 첫 번째 행을 가져온다.
(2) 안쪽 테이블(Inner Table) 을 검색해서 조건(ON e.emp_no = s.emp_no)에 맞는 행을 찾는다.
(3) 일치하는 행이 있으면 결과에 추가한다.
(4) Outer Table의 다음 행으로 이동해서 같은 과정을 반복한다.

예를 들어, 아래와 같은 쿼리문을 실행했을 때

 SELECT * 
   FROM tableA 
   JOIN tableB 
     ON tableA.id = tableB.a_id;

MySQL 내부에서는 아래 자바 코드와 유사한 처리를 한다.

// tableA가 Outer Table (바깥쪽 루프)
// tableB가 Inner Table (안쪽 루프)

for (Row rowA : tableA) {
    for (Row rowB : tableB) {
        if (rowA.id == rowB.a_id) {
            // 조인 조건이 맞으면 결과에 추가
            add_to_result(rowA, rowB);
        }
    }
}

 

4. Indexed Nested Loop Join ( INLJ, 인덱스 기반 중첩 루프 조인 )

중첩 루프 조인(NLJ)은 2중 for문 방식이기 때문에 tableA와 tableB가 둘 다 크면 재앙에 가까운 성능을 낸다 (예: 드라이빙 테이블 10,000 row * 드리븐 테이블 10,000 row = 1억 번 비교)

그래서 MySQL은 훨씬 더 영리한, "인덱스(Index) 기반" 중첩 루프 조인을 사용한다. 이것이 MySQL 8.0에서 Nested Loop라고 하면 보통 의미하는 방식이다. 실행계획에서 type은 "ref", Extra는 "Using index"로 표시된다.

핵심은 "Inner Table(안쪽 루프)에 조인 조건 컬럼의 인덱스가 있는가?" 이다. 만약 tableB.a_id 컬럼에 인덱스가 있다면, MySQL의 동작은 2중 for문처럼 작동하지 않고 아래 자바 코드와 유사한 처리를 한다.

// tableB.a_id 에 인덱스(map처럼 동작)가 있다고 가정
Index indexOnB = tableB.getIndex("a_id");

// 바깥쪽 루프는 동일
for (Row rowA : tableA) {

    // 안쪽 루프가 테이블 전체 스캔(for)이 아님!
    // 인덱스를 사용해 바로 검색 (O(1) 또는 O(logN))
    Row rowB = indexOnB.lookup(rowA.id); // Map.get(key)과 유사

    if (rowB != null) {
        // 조인 성공!
        add_to_result(rowA, rowB);
    }
}

그래서 드라이빙 테이블은 row수가 적을수록 좋고 드리븐 테이블은 인덱스가 있는게 유리하다. 이 방식은 단순하고 메모리를 사용하지 않거나 사용하더라도 최소한만 사용하기 때문에 row수가 적은 조인을 처리할 때 유리하다.

 

5. Block Nested Loop Join ( 블록 중첩 루프 조인 )

중첩 루프 조인(NLJ)은 한 행씩 처리하지만 블록 중첩 루프 조인(BNLJ)은 여러 행(블록)을 한 번에 모아서 처리한다. MySQL 8.0.20 버전부터는 BNL 방식이 아예 삭제되고 Hash Join으로 대체되었다.

 

6. Batched Key Access Join ( BKA, 배치 키 액세스 조인 )

드라이빙 테이블의 여러 키를 한 번에 모아서 조인버퍼에 인덱스 순서로 정렬해서 저장한다. 조인 버퍼에 저장된 키를 이용해 드리븐 테이블 인덱스를 한 번에 일괄로 접근하여 인덱스 순서로 정렬이 되어 있기 때문에 순차 액세스( Sequential Access )로 처리된다. 메모리를 적극적으로 사용하고 키를 정렬해야하는 오버헤드가 발생되지만 대량의 row를 조인할 때 굉장히 성능이 좋다. 실행계획의 Extra에 "Using join buffer (Batched Key Access)"로 표시된다.

-- BKA를 명시적으로 활성화하는 명령어
SET optimizer_switch='batched_key_access=on';

 

7. Hash Join ( 해시 조인 )

MySQL 8.0.18 버전부터 해시 조인을 공식 지원한다. 🎉
조인 시 드리븐 테이블에 인덱스가 없을 때 중첩 루프 조인으로 처리하면 속도가 느리기 때문에 메모리에 한 쪽 테이블을 해시 테이블로 만들어 처리한다. 

즉, tableB.a_id에 인덱스가 없었다면

• 과거 (MySQL 8.0.18 이전): 어쩔 수 없이 2중 for문 방식(정확히는 Block Nested Loop, BNL)을 썼다. (매우 느림)
• 현재 (MySQL 8.0.18 부터): 한쪽 테이블을 메모리에 해시 테이블로 만들어서 빠르게 찾기

인덱스가 없는 상황에서는 BNL보다 해시 조인이 훨씬 효율적이다. 그래서 MySQL 8.0.20 버전부터는 BNL 방식이 아예 삭제되고, 인덱스가 없으면 무조건 해시 조인을 사용하도록 변경되었다.

사용자는 별도 설정 없이, 옵티마이저가 “이 조인은 해시 조인이 더 빠르겠다”고 판단하면 자동으로 사용한다.
EXPLAIN 실행계획 결과에서 Extra 컬럼에 "Using join buffer (hash join)" 내용이 보이면 Hash Join을 사용한 것이다.