Hash Join 에 대하여

MySQL 8.0.18 부터 추가된 Hash Join에 대하여 알아보자.

Hash Join 이란
조인을 수행할 테이블의 조인 컬럼을 기준으로 해쉬함수를 수행한 후, 서로 동일한 해쉬 값을 갖는 것 중에서 실제 값이 같은지 비교하면서 조인을 수행하는 메커니즘이다. 

1. 선행 테이블에서 주어진 조건을 만족하는 로우 검색
2. 선행 테이블의 조인 키를 기준으로 해쉬 함수 적용 후, 해쉬 테이블 생성. 조인 컬럼과 검색하는 컬럼도 함께 저장
3. 선행 테이블의 조건을 만족하는 모든 로우를 1~2 과정 수행 후, 후행 테이블에서 주어진 조건을 만족하는 로우 검색
4. 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 선행 테이블과 비교
5. 조인 성공 시, 추출버퍼에 격납
6. 후행 테이블의 조건을 만족하는 모든 로우를 3~5 과정 수행

해쉬 테이블의 크기가 메모리에 적재할 수 있는 크기보다 클 경우, 임시 영역에 해쉬 테이블을 저장하므로
사이즈가 작은 테이블을 선행으로 사용하는 것이 좋다.

Hash Join 채용 조건
해쉬 조인은 등가조건의 조인이고, 조인하는 컬럼에 Index가 없는 경우에 사용된다
해쉬 조인의 사용유무는 EXPLAIN ANALYZE 또는 EXPLAIN FORMAT=TREE: 로 확인 가능하다.

# MySQL 8.0.20 에서 Hash Join이 강화되어 채용 조건이 확대되었다

• 등가 조인 이외의 INNER JOIN
• 세미조인
• 안티조인
• LEFT OUTER JOIN / RIGHT OUTER JOIN

상세한 내용은 공식 사이트 참고
https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

Hash Join 검증
t1, t2 테이블 작성 후 테스트용 데이터(랜덤으로 생성한 20문자) 16384건 등록하자.

mysql> CREATE TABLE t1 (a VARCHAR(20));
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t2 (a VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES ();
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
 
～(생략)～
 
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 8192 rows affected (0.11 sec)
Records: 8192  Duplicates: 0  Warnings: 0
 
mysql> UPDATE t1 SET a = SUBSTRING(MD5(RAND()), 1, 20);
Query OK, 16384 rows affected (0.81 sec)
Rows matched: 16384  Changed: 16384  Warnings: 0

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 16384 rows affected (0.58 sec)
Records: 16384  Duplicates: 0  Warnings: 0
 
mysql> UPDATE t2 SET a = SUBSTRING(MD5(RAND()), 1, 20);
Query OK, 16384 rows affected (0.30 sec)
Rows matched: 16384  Changed: 16384  Warnings: 0

작성한 테이블 t1, t2에 대하여 아래의 쿼리를 실행해보자

mysql> EXPLAIN ANALYZE SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
+-------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t1.a = t2.a)  (cost=26368857.25 rows=26367169) (actual time=42.485..52.619 rows=5 loops=1)
    -> Table scan on t1  (cost=0.01 rows=16269) (actual time=0.016..10.830 rows=16384 loops=1)
    -> Hash
        -> Table scan on t2  (cost=1644.95 rows=16207) (actual time=0.051..10.163 rows=16384 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

실행계획에 "Inner hash join" 이 출력되어 있고, Hash Join이 실행된 것을 알 수 있다.

# Refer
https://mysqlserverteam.com/hash-join-in-mysql-8/