조인 최적화

조인은 한 입력을 읽고, 다른 입력에서 짝이 맞는 행을 찾아 붙이는 과정이다. MySQL 8.4에서는 인덱스를 쓰는 NL(Nested Loop, 중첩 루프) 조인과 해시 조인(hash join)을 중심으로 본다. 어느 쪽을 먼저 읽을지는 "작은 테이블이 먼저"라는 감보다, 조건으로 걸러진 뒤 남을 예상 행 수와 짝을 찾는 접근 비용으로 판단한다.

주요 조인 방식

먼저 읽어 기준이 되는 바깥쪽 테이블을 드라이빙 테이블(driving table), 뒤따라 짝을 찾는 안쪽 테이블을 드리븐 테이블(driven table)이라 한다. BKA(Batched Key Access, 배치 키 접근)는 NL을 보완한 방식으로, 바깥 행의 조인 키를 모아 MRR(Multi-Range Read, 다중 범위 읽기)로 안쪽 인덱스를 한꺼번에 조회한다. 디스크의 여기저기를 한 건씩 읽는 랜덤 I/O(Input/Output, 입출력)를 묶어 줄이는 것이 목적이다.

방식	동작	유리한 경우
NL	바깥 행마다 안쪽 입력 탐색	안쪽 조인 키 인덱스를 사용할 수 있을 때
BKA	바깥 키를 모아 MRR로 안쪽 인덱스 조회	랜덤 I/O를 묶어 줄일 수 있을 때
해시 조인	한 입력으로 해시 테이블(hash table)을 만들고 다른 입력을 탐색	적용할 조인 인덱스가 없고 해시 비용이 유리할 때

다이어그램 로딩…

과거에 쓰던 BNL(Block Nested Loop, 블록 중첩 루프) 방식은 MySQL 8.0.20부터 해시 조인으로 대체됐다. 핵심은 조건으로 걸러진 뒤의 행 수, 짝을 찾는 비용, 쓸 수 있는 메모리다. 옵티마이저(optimizer)가 통계로 조인 순서를 정한다. 순서가 잘못됐을 때는 먼저 통계와 인덱스를 고친 뒤, 적힌 순서대로 조인하게 강제하는 STRAIGHT_JOIN이나 힌트를 제한적으로 검토한다.

세미 조인과 안티 조인

WHERE id IN (SELECT ...)나 EXISTS (SELECT ...)처럼 짝이 있는지만 보는 서브쿼리(subquery)를, 옵티마이저는 세미 조인(semi-join)으로 바꿔 실행한다. 세미 조인은 짝이 여러 개여도 바깥 행을 한 번만 내보내는 조인이다. 서브쿼리를 조인으로 바꾸면 조인 순서와 방식을 비용으로 고를 수 있으므로, 바깥 행마다 서브쿼리를 따로 실행하는 것보다 싸지는 경우가 많다. EXISTS는 8.0.16부터 IN과 같은 변환 대상이다. 변환은 서브쿼리가 WHERE나 ON 절의 최상위 조건(또는 AND로 이은 조건 중 하나)이고, UNION·HAVING·집계 함수·LIMIT이 없는 단일 SELECT일 때만 일어난다.

일반 조인으로 붙이면 짝의 수만큼 바깥 행이 늘어난다. 그래서 옵티마이저는 다음 전략 중 하나를 비용으로 골라 중복을 막는다. 일부 전략은 쿼리를 처리하는 동안에만 잠깐 만드는 내부 테이블인 임시 테이블(temporary table)을 쓴다.

전략	중복을 막는 방법	EXPLAIN 표시
Table pullout	서브쿼리 테이블을 바깥으로 꺼내 일반 조인으로	특별한 표시 없음
Duplicate Weedout	조인한 뒤 임시 테이블로 중복 제거	Start/End temporary
FirstMatch	짝을 하나 찾으면 그 바깥 행의 탐색 중단	FirstMatch(t)
LooseScan	인덱스로 값마다 한 행만 읽음	LooseScan(m..n)
Materialization	서브쿼리 결과를 인덱스 달린 임시 테이블로	MATERIALIZED·<subqueryN>

표시는 기본 표 형식 EXPLAIN의 Extra 칼럼(Materialization은 select_type과 table 칼럼)에 나온다. 서브쿼리가 조인으로 합쳐졌는지는 EXPLAIN 직후 SHOW WARNINGS가 보여 주는 재작성된 쿼리에서 확인한다. 전략마다 optimizer_switch의 semijoin·firstmatch·loosescan·duplicateweedout·materialization 스위치가 있고 기본은 모두 켜져 있다.

안티 조인(anti-join)은 반대로 짝이 없는 바깥 행만 남기는 조인이다. 8.0.17부터 NOT IN (SELECT ...)·NOT EXISTS (SELECT ...)도 안티 조인으로 바뀐다. 바깥 행마다 짝을 하나라도 찾는 순간 그 행을 버리면 되므로 끝까지 훑지 않아도 된다. 단 비교하는 식이 NULL을 허용하면 대부분 이 변환을 받지 못한다. NOT IN은 NULL 때문에 뜻 자체가 달라지기도 하므로 NOT EXISTS로 쓰는 편이 낫다(서브쿼리·CTE·윈도우 함수, 조인 종류와 작성 2).

옵티마이저 스위치

MySQL은 optimizer_switch 설정으로 개별 최적화 기법을 켜고 끌 수 있다.

SET optimizer_switch = 'block_nested_loop=off';  -- 해시 조인 비활성화

• 대표적인 스위치는 block_nested_loop, index_merge(인덱스 여러 개를 함께 쓰는 최적화), mrr, batched_key_access 등이다. 8.4에서 hash_join 스위치와 HASH_JOIN 힌트는 효과가 없다. 해시 조인을 제어하려면 block_nested_loop 스위치나 BNL·NO_BNL 힌트를 쓴다. 이름은 BNL이지만, BNL이 해시 조인으로 대체되면서 해시 조인을 켜고 끄는 용도가 됐다.
• 스위치를 끄는 것은 원인을 진단하거나 잠시 우회하는 용도다. 근본 해결은 통계·인덱스·쿼리를 고치는 것이다. 스위치로 고쳤으면 원인(통계)을 메모해 둔다.

면접 포인트

• "조인이 느려요" → 필터 후 행 수 → 매칭 방식(NL/BKA/해시) → 조인 키 인덱스 순서로.
• "조인 키 인덱스가 없으면?" → 해시 조인 후보가 된다. 실제 선택은 비용과 메모리·조건 종류에 달린다.
• "IN 서브쿼리는 어떻게 실행되나요?" → 세미 조인으로 바꿔 Table pullout·Duplicate Weedout·FirstMatch·LooseScan·Materialization 중 비용이 싼 전략으로 중복을 막는다. NOT EXISTS는 안티 조인.
• "해시 조인은 언제?" → 적용 가능한 조인 인덱스가 없고 해시 구축·탐색 비용이 유리할 때. EXPLAIN ANALYZE¹로 실제 행 수를 확인한다.

참고

1. 쿼리를 실제로 실행해 보고, 단계마다 예상 행 수와 실제 행 수·걸린 시간을 함께 보여 주는 명령. ↩︎