Hash 조인, NL 조인

서론

---

프로젝트 성능 향상을 위해 JOIN 관련해서 공부하던 중 의문점이 생겼습니다.

 

문제 상황은 아래와 같습니다.

 

1. 10^4건의 데이터를 가진 테이블 A와 B가 있다.

2. 두 테이블을 인덱스가 없는 칼럼을 기준으로 JOIN 할 경우 10^4 * 10^4 의 시간복잡도를 가지기 때문에 최소 1초는 걸릴 것이라 생각했다.

3. 실행 결과 15ms가 나왔다.

 

쿼리문은 아래와 같습니다.

SELECT p.content
FROM post AS p
JOIN study AS s ON p.posted_user_id = s.master_user_id;

 

 

쿼리 실행계획을 통해 왜 예상과 다른 결과가 나왔는지 확인해보겠습니다.

 

 

 

 

본론

---

 

EXPLAIN ANALYZE 키워드로 확인한 결과 Inner hash join을 사용해 join하고 있었습니다.

 

해시 조인은 인덱스가 없는 칼럼으로 조회할 때 성능을 높일 수 있기 때문에 옵티마이저가 임의로 해시 조인을 하는 방식을 택한 것으로 보입니다.

 

확인을 위해 인덱스가 있는 칼럼인 s.study_id를 기준으로 JOIN 해보겠습니다.

EXPLAIN ANALYZE SELECT p.content
FROM post AS p
JOIN study AS s ON p.posted_user_id = s.study_id;

 

인덱스가 있는 칼럼을 기준으로 JOIN하니 Nested loop join을 하고있습니다.

 

그렇다면 왜 해시조인을 해서 성능이 좋아진걸까요??

 

이는 해시 조인의 동작 방식 때문입니다.

 

 

 

 

해시 조인 동작 방식

---

 

SELECT p.content
FROM post AS p
JOIN study AS s ON p.posted_user_id = s.master_user_id;

 

위 쿼리문에서 해시 조인을 하게될 경우 순서를 살펴보겠습니다.

 

1. post 테이블을 build input으로 삼아서 해시 영역에 저장한다.

2. 해시 영역으로 올라가면서 join 컬럼인 posted_user_id를 기준으로 hash function이 적용된다.

3. study 테이블의 master_user_id를 hash function을 적용해 해시 영역에 해당 값이 있는지 확인한다.

 

이 같은 방식으로 진행되기 때문에 10^4개의 데이터를 JOIN 할 때 O(n^2)이 아닌 O(n)의 시간복잡도만 가지게 되어 성능이 빨라지는 것입니다.

 

 

 

 

NL 조인 했을때의 성능

---

해시 조인 했을 때 성능이 빨라지는 것은 알겠는데 과연 NL 조인을 하면 얼마나 느려질지 궁금했습니다.

 

 

MySQL 공식문서를 보면 8.0.18 버전 이후로 해시 조인을 사용할 수 있으면 기본적으로 사용한다고 나와있습니다.

 

강제로 해시 조인을 사용하지 않기 위해 set optimizer_switch='block_nested_loop=off'; 쿼리문을 실행한 뒤 아래 쿼리문의 성능을 다시 측정해보겠습니다.

 

EXPLAIN ANALYZE SELECT p.content
FROM post AS p
JOIN study AS s ON p.posted_user_id = s.master_user_id;

 

 

강제로 NL 조인을 실행시킨 결과 28.171s로 측정되었습니다.

 

 

 

 

결론

---

해시 영역에 들어가는 테이블의 크기가 너무 크게되면 디스크 영역을 사용하기 때문에 성능이 더 나빠지는 결과를 초래할 수 있습니다.

 

옵티마이저의 실행계획을 따라가되 성능이 나쁘게 측정된다면 쿼리 튜닝을 고려해 보는 것이 좋겠습니다.