MySQL Hash Join Optimization

출처 : https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.4 Hash Join Optimization

 

MySQL 8.0.18 부터 MySQL은 각 조인이 동일 조인 조건을 가지며 인덱스를 사용하지 않는 모든 쿼리에 해시 조인을 사용합니다.

SELECT *      FROM t1      JOIN t2          ON t1.c1=t2.c1;

해시 조인은 일반적으로 빠르며 이전 버전의 MySQL에서 사용 된 block nested loop 알고리즘 대신 사용됩니다.

CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT); CREATE TABLE t3 (c1 INT, c2 INT);

다음 EXPLAIN FORMAT=TREE과 같이 사용하여 해시 조인이 사용되고 있음을 알 수 있습니다 .

mysql> EXPLAIN FORMAT=TREE     -> SELECT *      ->     FROM t1      ->     JOIN t2      ->         ON t1.c1=t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)     -> Table scan on t2  (cost=0.35 rows=1)     -> Hash         -> Table scan on t1  (cost=0.35 rows=1)

주어진 조인에 해시 조인이 사용되고 있는지 확인하려면 옵션 EXPLAIN과 함께 사용해야합니다 FORMAT=TREE. EXPLAIN ANALYZE 사용하면 해시 조인에 대한 정보도 표시합니다.

해시 조인은 각 테이블 쌍에 대해 하나 이상의 조인 조건이 여기에 표시된 쿼리와 같은 동일 조인 인 경우 여러 조인과 관련된 쿼리에도 사용됩니다.

SELECT *      FROM t1     JOIN t2          ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)     JOIN t3          ON (t2.c1 = t3.c1);

방금 표시된 것과 같은 경우 조인이 실행 된 후 동일 조인이 아닌 추가 조건이 필터로 적용됩니다. 

mysql> EXPLAIN FORMAT=TREE     -> SELECT *      ->     FROM t1     ->     JOIN t2      ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)     ->     JOIN t3      ->         ON (t2.c1 = t3.c1)\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)     -> Table scan on t3  (cost=0.35 rows=1)     -> Hash         -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)             -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)                 -> Table scan on t2  (cost=0.35 rows=1)                 -> Hash                     -> Table scan on t1  (cost=0.35 rows=1)

방금 표시된 출력에서 ​​볼 수 있듯이 여러 해시 조인은 여러 등일 조인 조건을 가진 조인에 사용될 수 있습니다.


조인 테이블과 동일 조인 조건이 쌍을 이룰 수 없는 경우 해시 조인을 사용할 수 없습니다.

mysql> EXPLAIN FORMAT=TREE     ->     SELECT *      ->         FROM t1     ->         JOIN t2      ->             ON (t1.c1 = t2.c1)     ->         JOIN t3      ->             ON (t2.c1 < t3.c1)\G *************************** 1. row *************************** EXPLAIN: not executable by iterator executor

이 경우 인덱스를 사용할 수없는 MySQL 8.0.18 이전 버전에서와 같이 느린 블록 중첩 루프 알고리즘이 사용됩니다.

mysql> EXPLAIN     ->     SELECT *      ->         FROM t1     ->         JOIN t2      ->             ON (t1.c1 = t2.c1)     ->         JOIN t3      ->             ON (t2.c1 < t3.c1)\G              *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: t1    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 1      filtered: 100.00         Extra: NULL *************************** 2. row ***************************            id: 1   select_type: SIMPLE         table: t2    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 1      filtered: 100.00         Extra: Using where; Using join buffer (Block Nested Loop) *************************** 3. row ***************************            id: 1   select_type: SIMPLE         table: t3    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 1      filtered: 100.00         Extra: Using where; Using join buffer (Block Nested Loop)

해시 조인은 Cartesian product에도 적용됩니다.

mysql> EXPLAIN FORMAT=TREE     -> SELECT *     ->     FROM t1     ->     JOIN t2     ->     WHERE t1.c2 > 50\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join  (cost=0.70 rows=1)     -> Table scan on t2  (cost=0.35 rows=1)     -> Hash         -> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)             -> Table scan on t1  (cost=0.35 rows=1)

기본적으로 MySQL은 가능할 때마다 해시 조인을 사용합니다. 해시 조인을 두 가지 방법 중 하나로 사용할지 여부를 제어 할 수 있습니다.

optimizer_switch 서버 시스템 변수를 global or session level에서 hash_join=on또는 hash_join=off에 대한 설정을 변경 합니다. 
기본값은 hash_join=on입니다.

경우에 따라 옵티마이저 파라미터를 사용 하거나 HASH_JOIN, NO_HASH_JOIN 힌트를 사용합니다.

해시 조인에 의한 메모리 사용량은 join_buffer_size 시스템 변수를 사용하여 제어 할 수 있습니다 . 
해시 조인은 join_buffer_size 보다 많은 메모리를 사용할 수 없습니다. 
해시 조인에 필요한 메모리가 사용 가능한 양을 초과하면 MySQL은 디스크의 파일을 사용하여 이를 처리합니다. 
이 경우 해시 조인이 open_files_limit에 설정된 것보다 많은 파일을 만들면 조인에 실패 할 수 있습니다.