MySQL 8.0 hash join有重大缺陷？(1)

徐春阳老师发文爆MySQL 8.0 hash join有重大缺陷。

文章核心观点如下：多表（比如3个个表）join时，只会简单的把表数据量小的放在前面作为驱动表，大表放在最后面，从而导致可能产生极大结果集的笛卡尔积，甚至耗尽CPU和磁盘空间。

就此现象，我也做了个测试。

1. 利用TPC-H工具准备测试环境

TPC-H工具在这里下载

http://www.tpc.org/tpch/default5.asp

。默认并不支持MySQL，需要自己手动做些调整，参见

https://imysql.com/2012/12/21/tpch-for-mysql-manual.html

。

在本案中，我指定的 Scale Factor 参数是10，即：

[[email protected] dbgen]# ./dbgen -s 10 && ls -l *tbl
-rw-r--r-- 1 root root  244847642 Apr 14 09:52 customer.tbl
-rw-r--r-- 1 root root 7775727688 Apr 14 09:52 lineitem.tbl
-rw-r--r-- 1 root root       2224 Apr 14 09:52 nation.tbl
-rw-r--r-- 1 root root 1749195031 Apr 14 09:52 orders.tbl
-rw-r--r-- 1 root root  243336157 Apr 14 09:52 part.tbl
-rw-r--r-- 1 root root 1204850769 Apr 14 09:52 partsupp.tbl
-rw-r--r-- 1 root root        389 Apr 14 09:52 region.tbl
-rw-r--r-- 1 root root   14176368 Apr 14 09:52 supplier.tbl      

2. 创建测试表，导入测试数据。

查看几个表的数据量分别是：

+----------+------------+----------+----------------+-------------+--------------+
| Name     | Row_format | Rows     | Avg_row_length | Data_length | Index_length |
+----------+------------+----------+----------------+-------------+--------------+
| customer | Dynamic    |  1476605 |            197 |   291258368 |            0 |
| lineitem | Dynamic    | 59431418 |            152 |  9035579392 |            0 |
| nation   | Dynamic    |       25 |            655 |       16384 |            0 |
| orders   | Dynamic    | 14442405 |            137 |  1992294400 |            0 |
| part     | Dynamic    |  1980917 |            165 |   327991296 |            0 |
| partsupp | Dynamic    |  9464104 |            199 |  1885339648 |            0 |
| region   | Dynamic    |        5 |           3276 |       16384 |            0 |
| supplier | Dynamic    |    99517 |            184 |    18366464 |            0 |
+----------+------------+----------+----------------+-------------+--------------+      

提醒：几个测试表都不要加任何索引，包括主键，上表中 Index_length的值均为0。

3. 运行测试SQL

本案选用的MySQL版本是8.0.19：

[[email protected]]> \s
...
Server version:         8.0.19-commercial MySQL Enterprise Server - Commercial
...      

徐老师是在用TPC-H中的Q5时遇到的问题，本案也同样选择这个SQL。

不过，本案主要测试Hash Join，因此去掉了其中的GROUP BY和ORDER BY子句。

先看下执行计划吧，都是全表扫描，好可怕...

[[email protected]]> desc select count(*)
-> from
->     customer,
->     orders,
->     lineitem,
->     supplier,
->     nation,
->     region
-> where
->     c_custkey = o_custkey
->     and l_orderkey = o_orderkey
->     and l_suppkey = s_suppkey
->     and c_nationkey = s_nationkey
->     and s_nationkey = n_nationkey
->     and n_regionkey = r_regionkey
->     and r_name = 'AMERICA'
->     and o_orderdate >= date '1993-01-01'
->     and o_orderdate < date '1993-01-01' + interval '1' year;
+----------+------+----------+----------+----------------------------------------------------+
| table    | type | rows     | filtered | Extra                                              |
+----------+------+----------+----------+----------------------------------------------------+
| region   | ALL  |        5 |    20.00 | Using where                                        |
| nation   | ALL  |       25 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
| supplier | ALL  |    98705 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
| customer | ALL  |  1485216 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
| orders   | ALL  | 14932433 |     1.11 | Using where; Using join buffer (Block Nested Loop) |
| lineitem | ALL  | 59386314 |     1.00 | Using where; Using join buffer (Block Nested Loop) |
+----------+------+----------+----------+----------------------------------------------------+      

加上 format=tree 再看下（真壮观啊。。。）

*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
-> Inner hash join (lineitem.L_SUPPKEY = supplier.S_SUPPKEY), (lineitem.L_ORDERKEY = orders.O_ORDERKEY)  (cost=40107736685515472896.00 rows=4010763818487343104)
    -> Table scan on lineitem  (cost=0.07 rows=59386314)
    -> Hash
        -> Inner hash join (orders.O_CUSTKEY = customer.C_CUSTKEY)  (cost=60799566599072.12 rows=6753683238538)
            -> Filter: ((orders.O_ORDERDATE >= DATE'1993-01-01') and (orders.O_ORDERDATE < <cache>((DATE'1993-01-01' + interval '1' year))))  (cost=0.16 rows=165883)
                -> Table scan on orders  (cost=0.16 rows=14932433)
            -> Hash
                -> Inner hash join (customer.C_NATIONKEY = nation.N_NATIONKEY)  (cost=3664985889.79 rows=3664956624)
                    -> Table scan on customer  (cost=0.79 rows=1485216)
                    -> Hash
                        -> Inner hash join (supplier.S_NATIONKEY = nation.N_NATIONKEY)  (cost=24976.50 rows=24676)
                            -> Table scan on supplier  (cost=513.52 rows=98705)
                            -> Hash
                                -> Inner hash join (nation.N_REGIONKEY = region.R_REGIONKEY)  (cost=3.50 rows=3)
                                    -> Table scan on nation  (cost=0.50 rows=25)
                                    -> Hash
                                        -> Filter: (region.R_NAME = 'AMERICA')  (cost=0.75 rows=1)
                                            -> Table scan on region  (cost=0.75 rows=5)      

看起来的确是把最小的表放在最前面，把最大的放在最后面。

在开始跑之前，我们先看一眼手册中关于Hash Join的描述，其中有一段是这样的：

Memory usage by hash joins can be controlled using the join_buffer_size
system variable; a hash join cannot use more memory than this amount. 
When the memory required for a hash join exceeds the amount available, 
MySQL handles this by using files on disk. If thishappens, you should 
be aware that the join may not succeed if a hash join cannot fit into 
memory and it creates more files than set for open_files_limit. To avoid 
such problems, make either of the following changes:

- Increase join_buffer_size so that the hash join does not spill over to disk.
- Increase open_files_limit.      

简言之，当 join_buffer_size 不够时，会在hash join的过程中转储大量的磁盘表（把一个hash表切分成多个小文件放在磁盘上，再逐个读入内存进行hash join），因此建议加大 join_buffer_size，或者加大 open_files_limit 上限。

所以，正式开跑前，我先把join_buffer_size调大到1GB，并顺便看下其他几个参数值：

[[email protected]]> select @@join_buffer_size,  @@tmp_table_size,  @@innodb_buffer_pool_size;
+--------------------+------------------+---------------------------+
| @@join_buffer_size | @@tmp_table_size | @@innodb_buffer_pool_size |
+--------------------+------------------+---------------------------+
|         1073741824 |         16777216 |               10737418240 |
+--------------------+------------------+---------------------------+
      

并且为了保险起见，在执行SQL时也用 SET_VAR（8.0新特性） 设置了 join_bufer_size，走起。