NL 조인 이해하기 With MySQL

이명우·2024년 1월 7일

쿼리 튜닝, MySQL

목록 보기

8/15

이전 포스팅들을 통해서 인덱스에 대한 전반적인 이해를 마쳤다. NL 조인은 이 인덱스를 활용한 조인 방식인데, 인덱스에 대한 이해가 따끈따끈한 시점에 공부를 해보면 좋을 것 같아서 이번 포스팅에서 다뤄보려고 한다.

NL 조인

NL 조인은 앞서 말했듯이, 인덱스를 활용한 조인으로 이해할 수 있다. 드라이빙 테이블에서 찾은 레코드를 통해서 드리븐 테이블의 레코드를 찾아내고자 할 때 주로 이용된다.

이 때, 두 테이블에 공통된 컬럼이 있어야하며, 이 컬럼은 인덱스가 생성되어있어야 한다. 인덱스가 없을 경우, 드리븐 테이블을 드라이빙 테이블에서 찾은 레코드 수 만큼 Table full scan 해야되기 때문에 드리븐 테이블의 컬럼에는 인덱스가 반드시 필요하다. 드라이빙 테이블의 경우, Table full scan 1회가 최대이기 때문에 인덱스가 반드시 필요하지는 않다.

드라이빙(driving) 테이블과 드리븐(driven) 테이블은 다음에 나올 예시를 살펴본 후에 설명하겠다.

예시를 통해서 NL 조인을 더 자세히 알아보겠다. 이 포스팅에서 생성한 스키마로 설명을 해보면 두 테이블이 있을 때, 닉네임 "User920508"인 유저가 작성한 게시글의 제목과 내용을 조회하는 SQL을 작성해보면 아래와 같다.

SELECT post.title, post.content
FROM user
         JOIN post ON user.id = post.user
WHERE user.nickname = 'User920508';

실행 계획을 살펴보면, 다음과 같다.

-> Nested loop inner join  (cost=4315.18 rows=9444) (actual time=0.099..5.375 rows=15 loops=1)
    -> Filter: (`user`.nickname = 'User920508')  (cost=1009.65 rows=985) (actual time=0.067..5.257 rows=1 loops=1)
        -> Table scan on user  (cost=1009.65 rows=9854) (actual time=0.063..3.520 rows=10000 loops=1)
    -> Index lookup on post using user (user=`user`.id)  (cost=2.40 rows=10) (actual time=0.031..0.116 rows=15 loops=1)

여기서 드라이빙 테이블은 Join을 위해서 먼저 탐색되는 테이블이고, 드리븐 테이블은 드라이빙 테이블에 의해서 join이 되는 테이블이다.user 테이블이 드라이빙 테이블로 이해하면 된다.

분석해보면, user 테이블에는 nickname 컬럼 인덱스가 따로 존재하지 않기 때문에 user 테이블을 Table full scan 하면서 nickname 조건에 맞는 row를 찾는다.

조건에 맞는 row를 찾았으면, Nested Loop Join을 이용해, post 테이블에서 조인 조건에 맞는 row를 탐색한다. 이 때, post 테이블의 user 컬럼 index를 활용하고 있다. 이를 통해서 user 컬럼의 값이 조인 조건과 알맞은 15개의 row를 탐색한 것을 알 수 있다.

inner join & outer join

실행 계획에 inner join이라는 키워드가 나와서 짚고 넘어가겠다.

inner join : inner join은 위 예시처럼, 조건에 부합하는 row만 결과집합에 포함하는 join이다.
outer join : outer join은 드라이빙 테이블의 모든 행과 드리븐 테이블의 공통된 결과를 결과집합에 포함하는 것이다. 이 때, 드라이빙 테이블의 행 중에서 드리븐 테이블 안에 조건에 맞는 행이 없을 경우 해당 컬럼은 Null 값으로 표시한다(MySQL 기준)

MySQL의 NL 조인

MySQL 공식문서에서 NL 조인 알고리즘을 설명하기 위해 사용한 예시이다.

A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. This process is repeated as many times as there remain tables to be joined. -MySQL 공식문서-

MySQL의 NL 조인도 다른 DBMS들과 유사하게 작동한다. 지금까지 살펴본 예시도 MySQl 환경에서의 NL 조인 예시였다. MySql에서는 조인시에 NL 조인을 사용하게 되는데, 조인의 연결 조건이 되는 컬럼(예시에서는 user.id)에 모두 인덱스가 있는 경우 사용되는 방식인데,

USER 테이블에서 id는 기본키이기 때문에 테이블 생성시에 클러스터드 인덱스가 생성된다.

MySQL은 테이블 생성 시에 외래키가 있을 경우, 해당 컬럼의 인덱스를 생성한다. 따라서 POST 테이블의 user 컬럼이 외래키이고, 조인 조건이 되는 컬럼이기 때문에 NL 조인이 사용된 것이다.

BNL 조인

그런데, MySQL엔 블록 네스티드 루프 조인(block_nested_loop)이라는 조인 또한 존재한다. NL 조인의 경우 인덱스가 있을 때 활용할 수 있었다면, BNL은 인덱스가 없는 환경에서 활용 가치가 높은 조인이라고 한다. 드라이빙 테이블을 조인 버퍼에 적재해두고, 드리븐 테이블에서 탐색한 결과 집합을 결합하는 방식으로 작동한다. 이러한 방식으로 드리븐 테이블의 접근 횟수를 줄여서 전체 쿼리 성능을 높이는 방식이라고 한다.