[RDBMS] JOIN의 드라이빙 테이블과 드리븐 테이블 정리

// 드라이빙 = A, 드리븐 = B라고 가정
for each row in A (드라이빙):             // A를 한 번 순회
    for each matching row in B (드리븐):  // B를 A의 row 수만큼 반복 탐색
        output(A.row + B.row)

드라이빙 결과 = N건
드리븐 탐색 비용 = 1회당 C

전체 비용 = N × C

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING';

1. orders에서 status = 'PENDING' 필터 -> 500건 추출
2. 500건의 customer_id로 customers 테이블 인덱스 탐색 -> 500회
총 탐색 횟수 = 500회

1. customers 전체 읽기 -> 100,000건
2. 100,000건 각각으로 orders 테이블 탐색 -> 100,000회
총 탐색 횟수 = 100,000회

✅ WHERE 조건 적용 후 결과 건수가 적은 테이블
✅ 필터 효과가 큰 조건을 가진 테이블
✅ 조인 전에 row 수를 많이 줄일 수 있는 테이블

orders 테이블: 1,000,000건
status = 'PENDING' 조건 후 결과: 500건

조건 일치 비율 = 500 / 1,000,000 = 0.0005 (0.05%)
-> 남는 row 비율이 매우 낮으므로 필터 효과가 큼

✅ JOIN 컬럼(ON 절 컬럼)에 인덱스가 걸려 있는 테이블
✅ 단건 또는 소수건 lookup 역할을 하는 테이블
✅ 테이블이 커도 JOIN 컬럼 인덱스로 빠르게 찾을 수 있는 테이블

-- 테이블 구조
CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status      VARCHAR(20) NOT NULL,
    created_at  DATETIME NOT NULL
);

CREATE TABLE customers (
    id    BIGINT PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(200)
);

-- 인덱스 추가
CREATE INDEX idx_orders_status      ON orders(status);        -- WHERE 조건용
CREATE INDEX idx_orders_customer_id ON orders(customer_id);   -- JOIN/FK 컬럼용

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING';

Step 1. orders에서 idx_orders_status 사용
        -> status = 'PENDING' 에 해당하는 row만 빠르게 추출 (드라이빙)

Step 2. 추출된 N건의 customer_id 각각으로
        customers의 PK 인덱스 탐색 (드리븐)
        -> 매 반복마다 index seek

결과: 전체 비용 ≈ N × lookup_cost

-- 최악에 가까운 시나리오
SELECT * FROM small_ref_table s
JOIN huge_table h ON h.non_indexed_col = s.id;

-> small_ref_table 결과가 N건이라면
-> huge_table을 N번 Full Table Scan
-> 전체 비용 = N × M  (M = huge_table 전체 건수)
-> N=1,000, M=5,000,000이면 row 비교 수가 폭증

FK가 있으니 인덱스도 있겠지 -> 위험한 가정
실제 인덱스 정의 + EXPLAIN 결과를 확인 -> 안전한 접근

-- 필요 시 명시적으로 생성
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

특징:
- 드라이빙 테이블 한 번 순회
- 드리븐 테이블을 드라이빙 row 수만큼 반복 탐색

최적 조건:
- 드라이빙: 소량 결과, 필터 효과 큼
- 드리븐:   JOIN 컬럼에 인덱스가 매우 중요

적합 상황: OLTP, 비교적 작은 결과 집합, 인덱스 잘 갖춰진 환경

-- NLJ 동작 의사코드
for each row r1 in driving_table:
    lookup driven_table where join_col = r1.join_col
    if found:
        emit(r1, matched_row)

동작 방식:
Phase 1 (Build):  보통 더 작은 입력을 해시 테이블로 적재
Phase 2 (Probe):  다른 입력을 스캔하며 해시 테이블과 매칭

특징:
- 드리븐 인덱스가 없어도 동작 가능
- 메모리 사용량이 큼
- 메모리를 초과하면 디스크 spill이 발생할 수 있음

적합 상황: 대용량 데이터, equi-join 중심, 인덱스가 약한 환경

-- MySQL 8.4에서 Hash Join 동작 확인
EXPLAIN FORMAT=TREE
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- 출력에 "Hash" 또는 "Inner hash join" 이 보이면 Hash Join 사용 중

-- MySQL: hash join 메모리와 관련된 대표 설정
SET join_buffer_size = 256 * 1024 * 1024;  -- 256MB

동작 방식:
1. 양쪽 입력을 JOIN 컬럼 기준으로 정렬
2. 정렬된 두 목록을 순차 스캔하며 병합

특징:
- 양쪽 입력이 정렬돼 있어야 함
- 정렬 비용이 들 수 있음
- 범위 조건이나 정렬된 입력 활용에 유리할 수 있음
- 드라이빙/드리븐 개념이 NLJ보다 덜 중요함

-- MySQL
EXPLAIN SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING';

+----+-------------+-------+--------+-------------------------+-------------------+---------+-------------------+------+
| id | select_type | table | type   | possible_keys           | key               | key_len | ref               | rows |
+----+-------------+-------+--------+-------------------------+-------------------+---------+-------------------+------+
|  1 | SIMPLE      | o     | ref    | idx_orders_status       | idx_orders_status | 22      | const             |  500 |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                 | PRIMARY           | 8       | mydb.o.customer_id|    1 |
+----+-------------+-------+--------+-------------------------+-------------------+---------+-------------------+------+

-- MySQL
ANALYZE TABLE orders;
ANALYZE TABLE customers;

-- PostgreSQL
ANALYZE orders;
ANALYZE customers;

-- Oracle
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'orders');

-- STRAIGHT_JOIN: FROM 절 순서를 고정
SELECT STRAIGHT_JOIN *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- JOIN_ORDER: 더 세밀한 조인 순서 지정
SELECT /*+ JOIN_ORDER(o, c) */ *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- MySQL 8.4에서는 HASH_JOIN/NO_HASH_JOIN 대신 BNL/NO_BNL 사용
SELECT /*+ NO_BNL(c) */ *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- 조인 재정렬을 강하게 줄이기
SET join_collapse_limit = 1;

-- 특정 알고리즘 비선호
SET enable_hashjoin = off;
SET enable_mergejoin = off;

-- LEADING 힌트: 조인 순서 시작점 지정
SELECT /*+ LEADING(o) */ *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- USE_NL: 지정한 테이블을 inner table로 nested loop 사용
SELECT /*+ LEADING(o) USE_NL(c) */ *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

SELECT *
FROM orders o
INNER LOOP JOIN customers c ON o.customer_id = c.id
OPTION (FORCE ORDER);

✅ WHERE 조건으로 결과가 많이 줄어드는 테이블을 드라이빙 후보로 본다
✅ 드리븐 테이블 JOIN 컬럼(ON 절)에 인덱스가 있는지 확인한다
✅ FK 컬럼은 "자동 인덱스가 있을 것"이라고 가정하지 말고 실제 인덱스를 확인한다
✅ 3개 이상 JOIN 시 EXPLAIN으로 실행 순서를 확인한다
✅ SELECT * 를 피하고 필요한 컬럼만 선택해 커버링 인덱스 가능성을 높인다

Step 1. EXPLAIN 실행
        -> JOIN 순서와 access type 확인
        -> 드리븐 쪽이 ALL인지 먼저 본다

Step 2. rows / filtered 확인
        -> 드라이빙 후보의 결과 건수가 비정상적으로 큰지 점검

Step 3. 통계 정보 갱신
        -> ANALYZE 또는 DBMS_STATS 후 실행계획 재확인

Step 4. 인덱스 추가
        -> 드리븐 JOIN 컬럼 인덱스
        -> 필요 시 드라이빙 필터 컬럼 인덱스

Step 5. 그래도 느리면
        -> DBMS별 힌트 또는 planner 설정으로 예외적으로 보정

-- 나쁜 예
SELECT * FROM small_table s
JOIN huge_table h ON h.non_indexed_col = s.id;

-- 해결
CREATE INDEX idx_huge_non_indexed ON huge_table(non_indexed_col);

-- 나쁜 예
SELECT * FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PENDING';

-- 확인 후 조인 순서 보정
SELECT STRAIGHT_JOIN *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING';

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 실제 스키마 확인 후 필요 시 명시적으로 생성
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-03-31';

-- 드라이빙 후보용: WHERE 조건 컬럼
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- 드리븐 후보용: JOIN 컬럼
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 커버링 인덱스 예시
CREATE INDEX idx_orders_covering ON orders(customer_id, status, created_at);

작은 드라이빙 × 인덱스 있는 드리븐 = 빠른 JOIN
큰 드라이빙   × 인덱스 없는 드리븐 = 느린 JOIN