Chinaunix首页 | 论坛 | 博客
  • 博客访问: 519303
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1172
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(100)

文章存档

2019年(21)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2019-10-17 16:57:41

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址: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: 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)
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 < t2.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 < 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)
从上面执行计划的输出中还可以看到,多个散列连接可以(具有)用于具有多个等连接条件的连接。但是,如果任何一对联接表都没有至少一个等联接条件,则不能使用哈希联接,如下所示:
EXPLAIN FORMAT=TREE
         SELECT * 
             FROM t1
             JOIN t2 
                 ON (t1.c1 = t2.c1)
            JOIN t3 
                 ON (t2.c1 < t3.c1)\G


(root@localhost:)[test]> EXPLAIN FORMAT=TREE
    ->          SELECT * 
    ->              FROM t1
    ->              JOIN t2 
    ->                  ON (t1.c1 = t2.c1)
    ->             JOIN t3 
    ->                  ON (t2.c1 < t3.c1)\G
*************************** 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: 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)
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!
阅读(3574) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~