投稿
  1. 首页
  2. 随笔

mysql 8 新特性三 Hash Join联接查询算法之Hash Join (五)

xiaoxue • 2023-3-7 • 随笔 • 阅读 27

mysql 8 新特性三 Hash Join联接查询算法之Hash Join (五),第1张

mysql8以前 的 join 算法只有 nested loop 这一种，在 MySQL8 中推出了一种新的算法 hash join，比 nested loop 更加高效。mysql8中的部分NLJ算法已经取消，hash join 是它的的替代方案。像属于NLJ的BNLJ、SNLJ都会被Hash join替代！不过基于索引的INLJ算法还是存在的，所以实际使用中可以对比下INLJ和Hash Join的查询性能然后做出选择。

个人觉得mysql8这个hash join也只能算是一个锦上添花的功能，顶多是代替了没有加索引时默认走的BNLJ算法，提高了join的性能下限。说白了就是给不懂加索引的mysql新用户提高下join性能。其实也不绝对，不过我有做 INLJ和Hash Join 对比实验，Hash Join 很有可能比需要在内部表建立索引的INLJ算法性能要好！毕竟当INLJ需要回表查的时候性能会大幅度下降，这时候Hash Join绝对值得一试的，当然具体两者之间的选择还请自己实际测试下。

创建user和book表

可以看看下列语句的执行计划，Extra 出现了 Using join buffer (hash join) 说明该语句使用到了hash join。这里还使用了 IGNORE index(index_user_id)禁用索引，不然使用的是INLJ。

那么，使用Hash Join会分为下面2个阶段：

1、build 构建阶段：从参与join的2个表中选一个，选择占空间小的那个表，不是行数少的，这里假设选择了 user 表。对 user表中每行的 join 字段值进行 hash(a.id ) 计算后放入内存中 hash table 的相应位置。所有行都存放到 hash table 之后，构建阶段完成。

溢出到磁盘在构建阶段过程中，如果内存满了，会把表中剩余数据写到磁盘上。不会只写入一个文件，会分成多个块文件。

2、probe 探测阶段：对 book 表中每行中的 join 字段的值进行 hash 计算：hash(b.user_id) 拿着计算结果到内存 hash table 中进行查找匹配，找到一行就发给 client。这样就完成了整个 join *** 作，每个表只扫描一次就可以了，扫描匹配时间也是恒定的，非常高效。

散列连接的内存使用可以使用join_buffer_size系统变量来控制；散列连接使用的内存不能超过这个数量。当散列连接所需的内存超过可用的数量时，MySQL通过使用磁盘上的文件来处理这个问题(溢出到磁盘)。

如果发生这种情况，您应该知道，如果散列连接无法容纳在内存中，并且它创建的文件超过了为open_files_limit设置的数量，则连接可能不会成功。

为避免此类问题，请执行以下任一更改:

1、增加join_buffer_size，以便哈希连接不会溢出到磁盘。

在MySQL 8.0.19及更高版本中， 设置 optimizer_switch 变量值 hash_join=on or hash_join=off 的方式已经失效了

2、增加open_files_limit。若数据量实在太大内存无法申请更大的join_buffer，就只能溢出到磁盘上了。我们可以增加open_files_limit，防止创建的文件超过了为open_files_limit设置的数量而join失败。

必须使用format=tree（8.0.16的新特性）才能查看hash join的执行计划：

创建几张测试表

从MySQL 8.0.18开始，MySQL对每个连接都有一个等连接条件的任何查询都使用散列连接，并且没有可应用于任何连接条件的索引，例如:

在MySQL 8.0.20之前，如果任何一对连接的表没有至少一个等连接条件，就不能使用Hash Join，并且使用了较慢的BNLJ。而 在MySQL 8.0.20和更高版本中，hash join可以用于未包含等值连接条件的查询

甚至是笛卡尔积的join

Semijoin也行

还有 antijoin

0. 目标端必须有同名表，没有则建一个空表；

####################################

1、 源端文件准备

源端： 

   flush tables t for export 

复制 

t.ibd, t.cfg到目标端。 

###############################

flush tables tt7   for export 

cp  tt7*   ../ops

2、 目标端<存在同样的表则>丢弃原来的数据文件

目标端： 

 alter table tt7  discard tablespace

3、 目标端加载新的数据文件 t.ibd

alter table tt7 import tablespace 

4、源端释放锁

源端： 

unlock tables 

过程中主要异常处理：

#####################################################

SELECT  * FROM   ops2.tt7   

SELECT  * FROM   ops.tt7   

import tablespace报错：

mysql>alter table tt7 import tablespace 

ERROR 1812 (HY000): Tablespace is missing for table ops.tt7.

确认再相应的目录存在两个文件

确认属主和权限

#####################################################

过程

[root@qaserver120 ops]# ll

total 80

drwxr-xr-x 2 root  root      36 Dec  2 21:42 000

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

[root@qaserver120 ops]# 

[root@qaserver120 ops]# 

[root@qaserver120 ops]# 

[root@qaserver120 ops]# cp 000

[root@qaserver120 ops]# ll

drwxr-xr-x 2 root  root      36 Dec  2 21:42 000

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 root  root     627 Dec  2 21:45 tt7.cfg

-rw-r----- 1 root  root  114688 Dec  2 21:45 tt7.ibd

[root@qaserver120 ops]# chown mysql.mysql tt7*

[root@qaserver120 ops]# 

[root@qaserver120 ops]# ll

drwxr-xr-x 2 root  root      36 Dec  2 21:42 000

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql    627 Dec  2 21:45 tt7.cfg

-rw-r----- 1 mysql mysql 114688 Dec  2 21:46 tt7.ibd

[root@qaserver120 ops]# 

#####################################################

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'

mysql>alter table tt7 import tablespace 

ERROR 1812 (HY000): Tablespace is missing for table ops.tt7.

mysql> 

mysql>alter table tt7 import tablespace

Query OK, 0 rows affected (0.08 sec)

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>

###############################################

################################################

mysql>mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.00 sec)

mysql> 

mysql>use ops

Database changed

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

+---------------+

1 row in set (0.00 sec)

mysql> 

mysql>use ops2

Database changed

mysql>select * from tt7

+--------+------+

| x      | y    |

+--------+------+

| BBBBBB | NULL |

+--------+------+

1 row in set (0.00 sec)

mysql> 

mysql> 

mysql>insert into tt7 select * from tt3

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql>insert into tt7 select * from tt3

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql> 

mysql>commit

Query OK, 0 rows affected (0.00 sec)

mysql> 

mysql>exit

Bye

[root@qaserver120 pkg]# cd /data/mysql/ops2

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# 

[root@qaserver120 ops2]# 

[root@qaserver120 ops2]# 

[root@qaserver120 ops2]# mysql -u'root'  -p'fgxkB9Zq40^MFQUi$PJ'        -A

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with or \g.

Your MySQL connection id is 56

Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help' or '\h' for help. Type '\c' to clear the current input statement.

mysql>use ops2

Database changed

mysql> 

mysql>flush tables tt7   for export 

Query OK, 0 rows affected (0.00 sec)

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.01 sec)

mysql>exit

Bye

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# pwd

/data/mysql/ops2

[root@qaserver120 ops2]# cd  cd /data/mysql 

-bash: cd: cd: No such file or directory

[root@qaserver120 ops2]#  cd /data/mysql/ops2

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# ll -al

total 244

drwxr-x---  2 mysql mysql     51 Dec  2 21:38 .

drwxr-xr-x 12 mysql mysql   4096 Dec  2 21:17 ..

-rw-r-----  1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r-----  1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r-----  1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# pwd

/data/mysql/ops2

[root@qaserver120 ops2]# mysql -u'root'  -p'fgxkB9Zq40^MFQUi$PJ'        -A

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with or \g.

Your MySQL connection id is 57

Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help' or '\h' for help. Type '\c' to clear the current input statement.

mysql>use ops2

Database changed

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.00 sec)

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>flush tables tt7   for export 

Query OK, 0 rows affected (0.00 sec)

mysql>use ops

Database changed

mysql>ll

    ->

ERROR 1064 (42000): You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'll' at line 1

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

+---------------+

1 row in set (0.00 sec)

mysql> 

mysql> 

mysql>alter table tt7 import tablespace 

ERROR 1100 (HY000): Table 'tt7' was not locked with LOCK TABLES

mysql> 

mysql> 

mysql>use ops2

Database changed

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.00 sec)

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>unlock tables

Query OK, 0 rows affected (0.00 sec)

mysql>show create table  tt7

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                         |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| tt7   | CREATE TABLE `tt7` (

  `x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT NULL,

  `y` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>use ops

Database changed

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

+---------------+

1 row in set (0.01 sec)

mysql>  CREATE TABLE `tt7` (

    ->   `x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT NULL,

    ->   `y` int(11) DEFAULT NULL

    ->) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> 

mysql> 

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

Empty set (0.00 sec)

mysql> 

mysql>alter table tt7  discard tablesapce 

ERROR 1064 (42000): You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'tablesapce' at line 1

mysql>alter table tt7  discard tablespace

Query OK, 0 rows affected (0.03 sec)

mysql> 

mysql> 

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'

mysql> 

mysql> 

mysql> 

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'

mysql> 

mysql> 

mysql>alter table tt7 import tablespace 

ERROR 1812 (HY000): Tablespace is missing for table `ops`.`tt7`.

mysql> 

mysql> 

mysql> 

mysql>alter table tt7 import tablespace

Query OK, 0 rows affected (0.08 sec)

mysql> 

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>SELECT  * FROM   ops2.tt7   

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>SELECT  * FROM   ops.tt7   

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql> 

mysql> 

mysql> 

mysql>unlock tables 

Query OK, 0 rows affected (0.00 sec)

mysql>unlock tables 

Query OK, 0 rows affected (0.00 sec)

mysql>use ops

Database changed

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql> 

mysql> 

mysql> 

mysql>use ops2

Database changed

mysql> 

mysql> 

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.01 sec)

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql> 

mysql>use ops

Database changed

mysql> 

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/5906756.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
xiaoxue xiaoxue 一级用户组
0 0
上一篇 2023-03-07
下一篇 2023-03-07

发表评论

登录后才能评论

评论列表（0条）

保存