[MySQL 8.0] BNL 조인 제거와 복합 인덱스로 쿼리 성능 개선하기

1. 문제 상황 (Problem)

슬로우 쿼리에 대한 확인을 해달라는 DB팀으로부터 문의가 들어왔습니다.

여러 테이블을 조인하는 복합 쿼리에서 데이터량이 늘어남에 따라 응답 속도가 눈에 띄게 느려지는 현상이 발생했습니다. 특히 4개의 테이블(sme1_0, gme1_0, gmmae1_0, gmmne1_0)이 얽히면서 MySQL 옵티마이저가 비효율적인 경로를 선택하고 있었습니다.

[문제의 쿼리]

---

2. 원인 분석: EXPLAIN을 통한 디버깅

1. EXPLAIN 결과, 성능 저하의 결정적인 원인은 Block Nested Loop (BNL) 조인이었습니다.

• gmmne1_0 테이블의 Full Table Scan : 조인 조건에 인덱스가 없어 27,796건의 데이터를 매번 풀 스캔하고 있었습니다.
• 비효율적인 조인 버퍼 활용 : 인덱스가 없다 보니 MySQL은 데이터를 메모리(Join Buffer)에 쌓아두고 대조하는 차선책을 택했고, 이는 CPU와 I/O에 큰 부하를 주었습니다.

2. 추가적으로 gmmae1_0 테이블의 과도한 데이터 탐색이 비효율을 야기 했습니다.

• 인덱스가 조인 조건(group_id, member_no)이나 필터 조건(authority_id, permission)을 모두 커버하지 못해, 일단 많은 양의 데이터를 읽어온 뒤 메모리에서 하나하나 대조하며 버리는 작업을 반복했습니다.
• 불필요한 데이터 페이지 접근 : 인덱스에 없는 컬럼(permission 등)을 확인하기 위해 인덱스 페이지에서 실제 데이터가 담긴 테이블 페이지로 이동하는 랜덤 I/O(Random I/O)가 수만 번 발생했습니다.
• gmmae1_0 테이블의 과도한 데이터 탐색 : 총 35,612행을 읽었지만 실제 조건에 맞는 데이터는 0.19%에 불과했습니다.

[ 잠깐만!!! ] 왜 BNL(Block Nested Loop) 조인이 문제였을까?

1. BNL 조인의 원리

일반적인 Nested Loop Join(NLJ) 은 드라이빙 테이블(Outer Table)에서 행을 하나 읽을 때마다 드리븐 테이블(Inner Table)의 인덱스를 타고 데이터를 바로 찾아옵니다.

 

하지만 조인 조건에 인덱스가 없다면 어떻게 될까요?

 

최악의 경우, 드라이빙 테이블의 행 수만큼 드리븐 테이블 전체를 풀 스캔해야 합니다. 이를 보완하기 위해 MySQL이 사용하는 방식이 바로BNL입니다.

1. Join Buffer 활용: 드라이빙 테이블의 데이터를 하나씩 처리하는 대신, 메모리 내 Join Buffer에 가능한 많이 담습니다(Block 단위).
2. 스캔 횟수 감소: 버퍼에 데이터를 가득 채운 뒤, 드리븐 테이블을 한 번 풀 스캔하면서 버퍼에 담긴 모든 데이터와 대조합니다.
3. 반복: 드라이빙 테이블의 남은 데이터가 있다면 다시 버퍼를 채우고 스캔을 반복합니다.

2. BNL이 성능에 미치는 타격

비록 풀 스캔하는 것보다는 낫지만, BNL은 인덱스 조인에 비해 압도적으로 비효율적입니다.

• I/O 폭발: 인덱스가 있다면 단 몇 페이지만 읽으면 될 것을, BNL은 드리븐 테이블의 모든 데이터 페이지를 반복해서 디스크에서 읽어와야 합니다. 이번 사례의 경우 27,796건을 매 블록마다 풀 스캔하는 부하가 발생했습니다.
• CPU 과부하: 인덱스 조인은 '주소'를 찾아가는 방식이지만, BNL은 '버퍼 내 데이터 수 × 드리븐 테이블 데이터 수' 만큼의 모든 조합을 CPU가 일일이 비교 연산 해야 합니다. 데이터가 많아질수록 CPU 점유율이 기하급수적으로 상승합니다.

3. 해결책은 결국 '인덱스'

이번 트러블슈팅에서도 확인했듯이, BNL을 해결하는 가장 완벽한 방법은 옵티마이저가 BNL이라는 '차선책'을 택하지 않도록 조인 컬럼에 적절한 인덱스를 제공하는 것입니다.

 

인덱스 추가 후 gmmne1_0 테이블의 접근 방식이 ALL(BNL)에서 ref로 바뀌면서, 수만 번의 비교 연산이 단 3번의 인덱스 탐색으로 대체되었습니다.

---

3. 해결 방안: 전략적 복합 인덱스 추가

1. gmmae1_0 에 커버링 인덱스 적용

조치 전 EXPLAIN 결과를 보면, gmmae1_0 테이블에서 35,612행을 읽었지만 실제 결과로 남은 데이터는 0.19%에 불과했습니다.

• 커버링 인덱스은 이 문제는 쿼리를 수행하는 데 필요한 모든 컬럼이 인덱스 자체에 포함되어 있는 상태 '커버링 인덱스' 기술로 해결합니다.
• MySQL은 이 인덱스만 읽고도 조인을 수행하고, 권한(authority_id)을 체크하고, 세부 권한(permission)까지 필터링할 수 있습니다.
• 결과적으로 실제 테이블 데이터(Heap)를 읽으러 디스크에 가지 않아도 되기 때문에 엄청난 속도 향상을 얻게 됩니다. (EXPLAIN 결과의 Using index)

2. 조인 병목 구간 제거

• BNL을 피하기 위해 조인 경로와 필터링 조건을 모두 포함하는 복합 인덱스(Composite Index)를 설계하여 적용했습니다.

[적용한 인덱스 DDL]

---

4. 개선 결과

인덱스 적용 후, 실행 계획은 완전히 바뀌었습니다. 비효율적인 스캔이 사라지고 모든 조인이 인덱스를 기반으로 수행됩니다.

① 실행 시간(Execution Time)의 극적인 단축

조치전

 

조치후

 

[표] 500개의 데이터를 조회했을 때의 성능 변화입니다.

항목	조치 전	조치 후	개선율
순수 실행 시간(Execution)	70 ms	17 ms	약 76% 단축
전체 응답 시간(Execution + Fetch)	381 ms	328 ms	-

• Fetching 시간(311ms)은 데이터 전송량에 따른 고정 비용임을 감안할 때, 데이터베이스 엔진이 쿼리를 처리하는 순수 연산 속도를 4배 이상(70ms → 17ms) 끌어올렸습니다. 이는 불필요한 연산을 수행하던 CPU의 부하가 그만큼 감소했음을 뜻합니다.

② 데이터 탐색량 비교 (Full Scan vs Index Lookup)

테이블	개선 전 (Rows)	개선 후 (Rows)	감소율
gmmae1_0 (권한 체크)	35,612 건	1 건	99.99%
gmmne1_0 (알림 테이블)	27,796 건	3 건	99.98%

---

③ 주요 기술적 변화

1. BNL 조인 완전 제거: Extra 항목에서 Block Nested Loop 문구가 사라지고 ref 방식의 인덱스 참조로 전환되었습니다.
2. 커버링 인덱스(Covering Index) 적용: gmmae1_0 테이블은 Using index가 표시되며 실제 테이블 데이터 페이지에 접근하지 않고 인덱스만으로 쿼리를 완료합니다.
3. 조인 순서 최적화: 필터링 효율이 좋아지자 옵티마이저가 자동으로 더 최적화된 조인 순서를 선택하여 불필요한 연산을 줄였습니다.

---

5. 마치며

이번 최적화를 통해 쿼리 실행 시 탐색해야 할 행의 수가 수만 건에서 단 몇 건 수준으로 줄어들었습니다. 단순히 인덱스를 거는 것을 넘어, EXPLAIN을 통해 옵티마이저가 왜 BNL을 선택했는지 원인을 파악하고, 그에 맞는 복합 인덱스를 설계하는 것이 얼마나 중요한지 다시 한번 깨달을 수 있었습니다.

 

MySQL 8.0 환경에서 조인 성능 이슈를 겪고 있다면 반드시 실행 계획에서 Block Nested Loop나 ALL 스캔 여부를 먼저 확인해 보시기 바랍니다.