MySQL支持HashJoin咯

  • Z时代
  • 2024-01-10
  • 分类：综合

database

8.0.20 对非等值条件(执行Hash Join再进行过滤(Filter))，笛卡尔积都能使用Hash Join。

CREATE TABLE `t4` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  `c4` int DEFAULT NULL,
  KEY `c1` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t5` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
delimiter ;;
create procedure load_t5()
    begin
    declare i int;
    set i=0;
    while(i<1000)
    do
       insert into t4(c1,c2,c3,c4) values(i,i,1000-i,i);
       insert into t5(c1,c2) values(i,i);
       set i=i+1;
    end while;
    end;;
delimiter ;
call load_t5();
# 执行Join 查询，对驱动表不能使用索引的查询，8.0开始支持使用Hash Join
root [t37](17:55 | DB102_8.0.20) >desc select t4.c4 from t4,t5 where t4.c3=t5.c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t4    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t5    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
root [t37](18:01 | DB102_8.0.20) >desc select t4.c4 from t4,t5 where t4.c3=t5.c1 and t4.c1 < 100;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t4    | NULL       | range | c1            | c1   | 5       | NULL |    8 |   100.00 | Using index condition                      |
|  1 | SIMPLE      | t5    | NULL       | ALL   | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

内存大小由参数join_buffer_size控制，超过容量将使用磁盘文件，适当调大join_buffer_size和open_files_limit参数，避免失败。从8.0.20开始，内存大小是按需分配的，不会一开始就分配参数配置的大小，避免浪费。

官档：

8.2.1.4 Hash Join Optimization

以上是 MySQL支持HashJoin咯 的全部内容， 来源链接： utcz.com/z/534422.html

回到顶部