Using join buffer에 대한 상세한 설명

Using join buffer는 EXPLAIN 출력의 Extra 컬럼에 표시되는 메시지로, MySQL이 조인 처리 시 조인 버퍼(Join Buffer)를 사용한다는 의미입니다.

조인 버퍼는 드라이빙 테이블(첫 번째 테이블)의 레코드를 메모리에 임시로 저장하여, 드리븐 테이블(두 번째 테이블)과의 조인 시 디스크 I/O를 줄이는 최적화 기법입니다.

드라이빙 테이블 vs 드리븐 테이블

• 드라이빙 테이블: 조인 시 먼저 읽는 테이블 (Outer table)
• 드리븐 테이블: 드라이빙 테이블의 각 레코드마다 매칭되는 레코드를 찾는 테이블 (Inner table)

SELECT * FROM A JOIN B ON A.id = B.a_id;
-- A가 드라이빙 테이블이면, B가 드리븐 테이블

조인 처리 방식

• Nested Loop Join: 드라이빙 테이블의 각 행마다 드리븐 테이블을 스캔
• Block Nested Loop: 드라이빙 테이블 여러 행을 버퍼에 저장 후 드리븐 테이블 스캔 (조인 버퍼 사용)
• Hash Join (8.0.18+): 해시 테이블을 사용한 조인

join_buffer_size 시스템 변수

조인 버퍼의 크기를 설정하는 변수로, 기본값은 256KB입니다.

-- 조인 버퍼 크기 확인
SHOW VARIABLES LIKE 'join_buffer_size';

-- 세션별로 조정 가능
SET SESSION join_buffer_size = 1048576; -- 1MB

조인 버퍼가 사용되는 경우

• 드리븐 테이블에 적절한 인덱스가 없을 때
• 조인 조건이 인덱스를 사용할 수 없을 때
• 드리븐 테이블의 풀 테이블 스캔이 필요할 때

조인 버퍼 없이 (인덱스 사용)

-- employees.dept_id에 인덱스 있음
SELECT * FROM departments d
JOIN employees e ON d.id = e.dept_id;

-- 실행 과정:
-- 1. departments 테이블 스캔 (드라이빙)
-- 2. 각 department마다 employees의 인덱스로 빠르게 찾기

조인 버퍼 사용 (인덱스 없음)

-- employees.name에 인덱스 없음
SELECT * FROM departments d
JOIN employees e ON d.manager_name = e.name;

-- 실행 과정:
-- 1. departments 레코드들을 조인 버퍼에 저장
-- 2. employees 테이블을 한 번 스캔하면서
-- 3. 버퍼의 모든 레코드와 비교 (메모리 내 연산)

조인 버퍼의 종류 (MySQL 8.0+)

버퍼 타입	EXPLAIN 표시	특징
Block Nested Loop	Using join buffer (Block Nested Loop)	블록 단위로 버퍼 사용
Hash Join	Using join buffer (hash join)	해시 테이블로 조인 (8.0.18+)
Batched Key Access	Using join buffer (Batched Key Access)	인덱스 룩업 배치 처리

성능 영향

• 긍정적: 드리븐 테이블 스캔 횟수 감소
• 부정적: 버퍼 크기가 작으면 여러 번 나눠서 처리 (멀티 패스)
• 해결책: 드리븐 테이블에 적절한 인덱스 추가가 근본 해결

조인 버퍼 크기 계산

-- 드라이빙 테이블 레코드 수 * 각 레코드의 조인 컬럼 크기
-- 예: 1,000 레코드 * 100 바이트 = 100KB

-- 버퍼가 부족하면 멀티 패스 발생
-- EXPLAIN에서 rows_examined_per_scan으로 확인 가능

실행 계획 예시

EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_name = c.name;

-- id | table | type | Extra
-- 1  | o     | ALL  | NULL
-- 1  | c     | ALL  | Using where; Using join buffer (hash join)

이 경우 customer_name과 name에 인덱스가 없어 해시 조인이 사용되었습니다.

최적화 전략

1. 최선: 드리븐 테이블의 조인 컬럼에 인덱스 추가
2. 차선: join_buffer_size 증가 (하지만 근본 해결은 아님)
3. 쿼리 재작성: 조인 순서 변경, 서브쿼리 활용 등

조인 버퍼는 인덱스가 없을 때 드리븐 테이블의 반복 스캔을 줄여주지만, 인덱스 사용만큼 빠르지는 않습니다.