mysql支持hash join_MySQL支持hash join的使用和优化

博客文章除注明转载外，均为原创。转载请注明出处。本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5826073.html从MySQL 8.0.18开始，MySQL对SQL查询在具有相等连接条件且不使用索引的情况下查询使用哈希连接(Hash join).创建如下表：CREATE TABLE t1 (c1 INT, c2 INT);CREATE TABLE t2 (c1 INT, c2 INT);CREATE TABLE t3 (c1 INT, c2 INT);执行如下语句：SELECT *     FROM t1     JOIN t2         ON t1.c1=t2.c1;查看执行计划：(root@localhost:)[test]> explain SELECT *     ->     FROM t1     ->     JOIN t2     ->         ON t1.c1=t2.c1\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: ALLpossible_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: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where; Using join buffer (Block Nested Loop)2 rows in set, 1 warning (0.01 sec)可以看到上述执行计划使用的是块嵌套循环算法(Block Nested Loop)但是hash join通常比在这种情况下要快，并且在新版本中这种情况下代替MySQL早期版本中使用的块嵌套循环算法。我们可以通过EXPLAIN FORMAT=TREE来查看sql的执行计划(root@localhost:)[test]> 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)1 row in set (0.00 sec)要查看是否将哈希联接用于给定的联接，必须EXPLAIN与 FORMAT=TREE选项一起使用。 EXPLAIN ANALYZE还显示有关使用的哈希联接的信息。可以看到优化器的信息，比如cost和rows等信息。哈希联接也用于涉及多个联接的查询，只要每对表的至少一个联接条件为等联接，如此处所示的查询：SELECT *     FROM t1    JOIN t2         ON (t1.c1 = t2.c1 AND t1.c2     JOIN t3         ON (t2.c1 = t3.c1);看看执行计划：(root@localhost:)[test]> EXPLAIN FORMAT=TREE    -> SELECT *     ->     FROM t1    ->     JOIN t2     ->         ON (t1.c1 = t2.c1 AND t1.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             -> 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         SELECT *              FROM t1             JOIN t2                  ON (t1.c1 = t2.c1)            JOIN t3                  ON (t2.c1 (root@localhost:)[test]> EXPLAIN FORMAT=TREE    ->          SELECT *     ->              FROM t1    ->              JOIN t2     ->                  ON (t1.c1 = t2.c1)    ->             JOIN t3     ->                  ON (t2.c1 *************************** 1. row ***************************EXPLAIN: 其执行计划无法走hash join，只能选择Block Nested Loop(root@localhost:)[test]> EXPLAIN SELECT *               FROM t1              JOIN t2   ON (t1.c1 = t2.c1)   JOIN t3                   ON (t2.c1*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: ALLpossible_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: ALLpossible_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: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where; Using join buffer (Block Nested Loop)3 rows in set, 1 warning (0.01 sec)哈希联接也适用于笛卡尔乘积，即未指定联接条件时，如下所示：EXPLAIN FORMAT=TREE      SELECT *         FROM t1        JOIN t2         WHERE t1.c2 > 50\G(root@localhost:)[test]> 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)1 row in set (0.00 sec)默认情况下，等值条件下MySQL尽可能使用哈希联接。也可以用以下两种方式之一控制是否使用哈希联接：在全局或会话级别上，通过使用 hash_join=on或 hash_join=off作为optimizer_switch 服务器系统变量设置的一部分。默认值为 hash_join=on。通过使用优化器hint提示强制走HASH_JOIN或 NO_HASH_JOIN作为给表连接算法。哈希联接的内存使用情况可以使用join_buffer_size系统变量来控制 ；哈希联接不能使用超过此数量的内存。当散列连接所需的内存超过可用容量时，MySQL需要通过使用磁盘上的文件来处理，这时候性能会受到影响。如果发生这种情况，应该会想到如果哈希联接无法容纳到内存中并且创建的文件数超过设置的数量，联接可能不会成功 open_files_limit。为避免此类问题，请进行以下任一更改：增加join_buffer_size以使哈希联接尽量使用内存中完成，而不是较换到磁盘。增加open_files_limit。--The end！

09-17 13:04