블록 네스티드 루프 조인(Block nested loop join), 누구냐 넌?

발생 이슈

지난 달 다른 서비스 팀의 친한 백엔드 개발자로부터 원인을 알 수 없는 이상한(?) 현상이 나타나고 있다는 것을 전해 듣고 해당 팀에 가서 어떤 이상한 현상인지 보러갔다. 해당 이슈는 동일한 조회 쿼리를 사용했음에도 불구하고 로컬 환경에서는 데이터 정렬이 정상적으로 처리되어 조회되는데, 개발 서버 환경에서는 데이터 정렬이 비정상적으로 처리되어 조회된다는 것이었다. 👀

 

 

쿼리 분석

우선, 그 조회 쿼리가 무엇인지 살펴보았다. 해당 쿼리의 형태는 대략적으로 다음과 같았다.

 

WITH
            temp_a_tbable AS (
                SELECT ... FROM a_table WHERE deleted_at IS NULL
            ),
            temp_b_table AS (
                SELECT ... FROM b_table WHERE deleted_at IS NULL
            ),
            temp_joined_table AS (
                SELECT ... FROM (
                     SELECT ... FROM c_table WHERE deleted_at IS NULL AND xxx_id =:xxxId ORDER BY ... DESC, ... DESC LIMIT ... OFFSET ...
                 ) temp_c_table 
                LEFT JOIN temp_a_table ON temp_a_table.table_c_id = temp_c_table.id
                LEFT JOIN temp_b_table ON temp_b_table.id = temp_c_table.table_b_id
            )
SELECT ... FROM temp_joined_table;

 

 

임시 테이블을 만들어 각각의 테이블을 조인한 후, 최종적으로 조인된 테이블을 조회하는 쿼리이다. 👀 이때, 문의하신 분께서 이슈로 삼으신 것은 c_table 테이블의 데이터를 조회 시 사용되는 ORDER BY 절의 정렬 규칙이 로컬 환경에서는 잘 적용된다는데, 개발 서버 환경에서는 적용이 제대로 되지 않는다는 것이었다. 

 

우선, 각각의 환경별로 실행계획을 뽑아 보기로 했다.

 

로컬 환경에서의 실행 계획은 다음과 같았다.

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	PRIMARY	<derived3>	ALL					3	100	Using temporary
1	PRIMARY	a_table	ref	fk_a_table_c_table_id	fk_a_table_c_table_id	4	temp_c_table.id	1	100	Using where
1	PRIMARY	b_table	eq_ref	PRIMARY	PRIMARY	3	temp_c_table.table_b_id	1	100	Using where
3	DERIVED	c_table	ref	fk_c_table_xxx_id	fk_c_table_xxx_id	3	const	30	10	Using where; Using filesort

 

그리고 개발 서버 환경에서의 실행 계획은 다음과 같았다.

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	PRIMARY	<derived3>	ALL					2	100	Using temporary
1	PRIMARY	a_table	ref	fk_a_table_c_table_id	fk_a_table_c_table_id	4	temp_c_table.id	1	100	Using where
1	PRIMARY	b_table	ALL	PRIMARY				2	100	Using where; Using join buffer (Block Nested Loop)
3	DERIVED	c_table	ALL	fk_c_table_xxx_id				14	10	Using where; Using filesort

 

각각의 실행 계획을 비교했을 때, 유난히 도드라지게 차이나는 부분이 있다. 바로 3번째 레코드에 SQL 문을 어떻게 수행할 것인지에 관한 추가 정보를 보여주는 항목인 extra 칼럼 부분이다. 로컬 환경에서와 달리, 개발 서버 환경에서는 Using join buffer (Block Nested Loop) 가 존재하는 것이다.

 

 

블록 네스티드 루프 조인(Block nested loop join)이란?

일반적으로 MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인(Nested loop join)이다. 이때, 네스티드 루프 조인은 드라이빙 테이블의 데이터 1건당 드리븐 테이블을 반복 검색하며 최종적으로 양쪽 테이블에 공통된 데이터를 출력한다. 반면, 블록 네스티드 루프 조인은  조인 버퍼(Join buffer)를 사용하는 조인 방식이다. 그렇다면 조인 버퍼는 왜 사용하는 것일까?

 

앞서 네스티드 루프 조인의 경우, 드라이빙 테이블을 먼저 조회하고 조회된 레코드별로 드리븐 테이블을 반복적으로 검색하게 된다. 이때, 드라이빙 테이블은 한 번에 쭉 읽는 반면, 드리븐 테이블은 드라이블 테이블의 레코드 수만큼 반복적으로 읽게 되는데, 검색 조건에 사용되는 칼럼이 드리븐 테이블의 인덱스로 설정되어 있다면 다행이지만, 인덱스를 사용할 수 없게 된다면 쿼리 성능이 악화된다. 이에, 옵미타이저는 최대한 드리븐 테이블 검색 시 인덱스를 사용할 수 있게 실행 계획을 수립한다고 한다.

 

하지만, 드리븐 테이블 검색 시 인덱스를 사용할 수 없거나 (풀 테이블 스캔을 하거나) 인덱스 풀 스캔을 할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리하는데, 이때 사용되는 메모리의 캐시를 조인 버퍼라고 한다. 참고로, 이러한 조인 버퍼의 크기는 MySQL 시스템 환경 변수 'join_buffer_size' 로 제한할 수 있다.

 

 

원인 분석

뭔가 옵티마이저가 알아서 조인 성능을 최적화해준다니 좋아 보이기만 하지만 여기에 함정이 하나 있다. 바로, 조인 버퍼가 사용되는 쿼리에서는 정렬 순서가 바뀔 수 있기 때문이다. 일반적으로 조인을 통해 조회된 데이터 정렬 순서는 드라이빙 테이블의 순서에 의존적인데, 조인 버퍼를 사용할 경우, 드리븐 테이블의 결과를 조회한 후 조인 버퍼에 담겨진 드라이빙 테이블의 결과를 조인하기 때문이다. 즉, 조인 버퍼를 사용하지 않았을 때와 달리 조인의 순서가 반대가 되는 것이다. 

 

앞서 개발 서버 환경에서의 쿼리 실행 계획을 다시 보면, b_table 의 조회 결과를 c_table 의 조회 결과와 조인함에 있어 c_table 의 조회 결과가 조인 버퍼에 들어갔으며 b_table 을 조인 버퍼 레코드와 조인 후 반환한다는 사실을 알 수 있다. 즉, 앞서 ORDER BY 절을 통해 c_table 의 결과를 정렬했지만, 조인 시 이 정렬 결과가 흐트러지는 것이다. 반면, 로컬 환경에서의 쿼리 실행 계획은 조인 버퍼를 사용하지 않기에, 정렬 결과가 당초 의도한대로 나왔던 것이다.

 

그렇다면 로컬 환경에서와 달리 개발 서버 환경에서는 왜 블록 네스티드 루프 조인이 적용된 것일까? 그 이유는 로컬 환경에서의 MySQL 버전과 개발 서버 환경에서의 MySQL 버전이 달랐기 때문이었다. 🤣

 

MySQL 8.0.20 부터는 블록 네스티드 루프 조인이 더이상 사용되지 않고 해시 조인 알고리즘이 대체되어 사용된다고 한다. 아니나 다를까, 해당 서비스 팀의 개발 서버 환경에서의 MySQL 버전은 8.0.15 였으며, 로컬 환경에서의 MySQL 버전은 8.0.33 이었다. 👀 

 

이를 통해, 개발 서버 환경에서의 MySQL 에서는 옵티마이저가 기존 네스티드 루프 조인의 성능을 최적화하기 위해 블록 네스티드 루프 조인 기반의 조인 버퍼를 사용했다고 생각해볼 수 있다. 반면, 로컬 환경에서는 조인 시 조인 버퍼를 사용하지 않았으며, 해시 조인 방식을 취하지도 않았다고 생각해볼 수 있다. 참고로, 실행 계획 상 해시 조인을 사용 시에는 extra 칼럼에 Using join buffer (hash join) 가 표기된다.

 

 

결론

MySQL 에서 제공하는 실행 계획을 통해 이슈의 원인을 찾는데 첫 발자국 내딛을 수 있었다. 아울러, 이전에 얕게(?) 학습했었던 MySQL 관련 기술 서적들의 지식이 있었기에 원인의 실마리를 찾을 수 있었다. 참고로, 해당 이슈는 우리 팀의 이슈와는 직접적인 관련이 없었기에, 위와 같은 표면적인 원인만 찾아주고 그 이후의 해결 방안은 해당 팀에 맡기기로 했다.

 

개인적으로는 '개발 환경에서는 꼭 조인 버퍼(블록 네스티드 루프 조인)를 사용해야만 했었을까'와 '로컬 환경에서는 왜 조인 버퍼(해시 조인)를 사용하지 않았을까'에 대해서도 궁금했지만, 아직 해시 조인 알고리즘에 대한 학습이 부족하고 각각의 환경별 데이터 set, 인덱스 등 현황 파악을 완벽히 할 수는 없었기에 (무엇보다 현재 우리 팀의 일을 하기에도 급급한...😅) 일단은 이정도까지만 정리하고 넘어가고자 했다. ☕

 

비록 다른 팀의 이슈였지만, 해당 이슈의 원인을 찾아가는 과정에서 나 역시 많은 지식과 경험을 쌓을 수 있어 매우 유익했었다. 때로는 다른 팀의 이슈를 간접적으로(?) 체험해보는 것도 인사이트를 넓히는데 좋은 것 같다는 생각이 든다. 이 이후로도 스프링에서 제공하는 @Async 와 관련된 이슈로 내게 또 다른 문의가 오기도 했는데, 해당 이슈의 원인을 찾아가는 과정에서도 많은 것들을 배울 수 있었다. 이에 대해서는 다음에 시간될 때 정리해보고자 한다. ✍

 

 

참고자료

• 위키북스 "Real MySQL 8.0 - 1"
• 한빛미디어 "업무에 바로 쓰는 SQL 튜닝"