当前位置: 365bet亚洲官方投注 > MySQL数据库 > 正文

join测量试验_Mysql_脚本之家,新特点之哈希连接

时间:2019-12-22 07:08来源:MySQL数据库
Hash Join MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA版本,带来了一些新特性和增强功能。其中最引人注目的莫过于多表连接查询支持hash join 方式了。我们先来看看官方的描

Hash Join

MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本,带来了一些新特性和增强功能。其中最引人注目的莫过于多表连接查询支持 hash join 方式了。我们先来看看官方的描述:

1. NESTED LOOP

Hash Join 不需要任何索引来执行,并且在大多数情况下比当前的块嵌套循环算法更有效。

MySQL 实现了用于内连接查询的 hash join 方式。例如,从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询:

对于被连接的数据子集较小的情况,nested loop连接是个较好的选择。nested loop就是扫描一个表,每读到一条记录,就根据索引去另一个表里面查找,没有索引一般就不会是 nested loops。一般在nested loop中, 驱动表满足条件结果集不大,被驱动表的连接字段要有索引,这样就走nstedloop。如果驱动表返回记录太多,就不适合nested loops了。如果连接字段没有索引,则适合走hash join,因为不需要索引。

下面通过实例代码给大家介绍Mysql 8.0.18 hash join测试,具体内容如下所示:

SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;

可用ordered提示来改变CBO默认的驱动表,可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

CREATE TABLE COLUMNS_hj as select * from information_schema.`COLUMNS`;INSERT INTO COLUMNS SELECT * FROM COLUMNS; -- 最后一次插入25万行CREATE TABLE COLUMNS_hj2 as select * from information_schema.`COLUMNS`;

explain format=treeSELECT COUNT, SUMFROM COLUMNS_hj c1, COLUMNS_hj2 c2WHERE c1.table_name = c2.table_nameAND c1.column_name = c2.column_nameGROUP BY c1.table_name, c1.column_nameORDER BY c1.table_name, c1.column_name;

Hash join 不需要索引的支持。大多数情况下,hash join 比之前的 Block Nested-Loop 算法在没有索引时的等值连接更加高效。使用以下语句创建三张测试表:

1)对于被连接的数据子集较小的情况,嵌套循环连接是个较好的选择2)使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接3)Nested loop一般用在连接的表中有索引,并且索引选择性较好的时候4)OIN的顺序很重要,驱动表的记录集一定要小,返回结果集的响应时间是最快的。5)Nested loops 工作方式是从一张表中读取数据,访问另一张表来做匹配,nested loops适用的场合是当一个关联表比较小的时候,效率会更高。

必须使用format=tree才能查看hash join的执行计划:

CREATE TABLE t1 ;CREATE TABLE t2 ;CREATE TABLE t3 ;

2. HASH JOIN

-> Sort: .TABLE_NAME, .COLUMN_NAME -> Table scan on  -> Aggregate using temporary table -> Inner hash join (c1.`COLUMN_NAME` = c2.`COLUMN_NAME`), (c1.`TABLE_NAME` = c2.`TABLE_NAME`) (cost=134217298.97 rows=13421218) -> Table scan on c1 (cost=1.60 rows=414619) -> Hash -> Table scan on c2 (cost=347.95 rows=3237)

set join_buffer_size=1048576000;SELECT COUNT, SUMFROM COLUMNS_hj c1, COLUMNS_hj2 c2WHERE c1.table_name = c2.table_nameAND c1.column_name = c2.column_nameGROUP BY c1.table_name, c1.column_nameORDER BY c1.table_name, c1.column_name;

使用EXPLAIN FORMAT=TREE命令可以看到执行计划中的 hash join,例如:

hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表,利用连接键在内存中建立hash表,然后扫描大表,每读到一条记录就探测hash表一次,找出与hash表匹配的行。

再来看BNL,先创建索引。

mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON t1.c1=t2.c1G*************************** 1. row ***************************EXPLAIN: -> Inner hash join   -> Table scan on t2  -> Hash -> Table scan on t1 

当小表可以全部放入内存中,其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。

alter table columns_hj drop index idx_columns_hj;alter table columns_hj2 drop index idx_columns_hj2;create index idx_columns_hj on columns_hj(table_name,column_name);create index idx_columns_hj2 on columns_hj2(table_name,column_name);-> Sort: .TABLE_NAME, .COLUMN_NAME -> Table scan on  -> Aggregate using temporary table -> Nested loop inner join (cost=454325.17 rows=412707) -> Filter: ((c2.`TABLE_NAME` is not null) and (c2.`COLUMN_NAME` is not null)) (cost=347.95 rows=3237) -> Table scan on c2 (cost=347.95 rows=3237) -> Index lookup on c1 using idx_COLUMNS_hj (TABLE_NAME=c2.`TABLE_NAME`, COLUMN_NAME=c2.`COLUMN_NAME`) 

best365娱乐官方登录 ,必须使用 EXPLAIN 命令的 FORMAT=TREE 选项才能看到节点中的 hash join。另外,EXPLAIN ANALYZE命令也可以显示 hash join 的使用信息。这也是该版本新增的一个功能。

至于两个表都进行分区,其好处是可以使用parallel query,就是多个进程同时对不同的分区进行join,然后再合并。但是复杂。

大约4.5秒。可见hash join效果还是杠杠的。

多个表之间使用等值连接的的查询也会进行这种优化。例如以下查询:

使用hash join时,HASH_AREA_SIZE初始化参数必须足够的大,如果是9i,Oracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可。

不得不吐槽下mysql的优化器提示,貌似HASH_JOIN/NO_HASH_JOIN都不生效。

SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON ;

以下条件下hash join可能有优势:1)两个巨大的表之间的连接。2)在一个巨大的表和一个小表之间的连接。

除了hash_join外,mysql 8.0.3引入的SET_VAR优化器提示还是很好用的,可用来设置语句级参数(oracle支持,mariadb记得也支持了的),如下:

在以上示例中,任何其他非等值连接的条件将会在连接操作之后作为过滤器使用。可以通过EXPLAIN FORMAT=TREE命令的输出进行查看:

要点如下:1)散列连接是CBO 做大数据集连接时的常用方式.2)也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接3)Hash join在两个表的数据量差别很大的时候.4)Hash join的工作方式是将一个表做hash运算并存储到hash列表中,从另一个表中抽取记录,做hash运算,到hash 列表中找到相应的值,做匹配。

mysql> select /*+ set_var(optimizer_switch='index_merge=off') set_var */ c_id from customer limit 1;
mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) -> JOIN t3 -> ON G*************************** 1. row ***************************EXPLAIN: -> Inner hash join   -> Table scan on t3  -> Hash -> Filter:   -> Inner hash join   -> Table scan on t2  -> Hash -> Table scan on t1 

可用ordered提示来改变CBO默认的驱动表,可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。

SET_VAR支持的变量列表:

从以上输出同样可以看出,包含多个等值连接条件的查询也可以使用多个 hash join 连接。

3. SORT MERGE JOIN

auto_increment_incrementauto_increment_offsetbig_tablesbulk_insert_buffer_sizedefault_tmp_storage_enginediv_precision_incrementend_markers_in_jsoneq_range_index_dive_limitforeign_key_checksgroup_concat_max_leninsert_idinternal_tmp_mem_storage_enginejoin_buffer_sizelock_wait_timeoutmax_error_countmax_execution_timemax_heap_table_sizemax_join_sizemax_length_for_sort_datamax_points_in_geometrymax_seeks_for_keymax_sort_lengthoptimizer_prune_leveloptimizer_search_depth variablesoptimizer_switchrange_alloc_block_sizerange_optimizer_max_mem_sizeread_buffer_sizeread_rnd_buffer_sizesort_buffer_sizesql_auto_is_nullsql_big_selectssql_buffer_resultsql_modesql_safe_updatessql_select_limittimestamptmp_table_sizeupdatable_views_with_limitunique_checkswindowing_use_high_precision

但是,如果任何连接语句中没有使用等值连接条件,将不会采用 hash join 连接方式。例如:

a)对连接的每个表做table access full;b)对table access full的结果进行排序;c)进行merge join对排序结果进行合并。

总结

mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON  -> JOIN t3 -> ON G*************************** 1. row ***************************EXPLAIN: 

sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下,9i开始已经很少出现,因为其排序成本高,大多为hash join替代。 通常情况下hash join的效果都比sort merge join要好,但是,如果行源已经被排过序,在执行sort merge join时不需要再排序,这时sort merge join的性能会优于hash join。当全表扫描比“索引范围扫描后再通过rowid进行表访问”更可取的情况下,sort merge join会比nested loops性能更佳。

以上所述是小编给大家介绍的Mysql 8.0.18 hash join测试,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

此时,将会采用性能更慢的 block nested loop连接算法。这与 MySQL 8.0.18 之前版本中没有索引时的情况一样:

1)使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接.2)Sort Merge join 用在没有索引,并且数据已经排序的情况.3)连接步骤:将两个表排序,然后将两个表合并。4)通常情况下,只有在以下情况发生时,才会使用此种JOIN方式:a)RBO模式b)不等价关联bHASH_JOIN_ENABLED=falsed)数据源已排序e)Merge Join 是先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配,因为merge join需要做更多的排序,所以消耗的资源更多。

mysql> EXPLAIN -> SELECT * -> FROM t1 -> JOIN t2 -> ON  -> JOIN t3 -> ON 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 *************************** 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 

f) like ,not like 通常来讲,能够使用merge join的地方,hash join都可以发挥更好的性能

Hash join 连接同样适用于不指定查询条件时的笛卡尔积,例如:

可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。

mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> WHERE t1.c2 > 50G*************************** 1. row ***************************EXPLAIN: -> Inner hash join  -> Table scan on t2  -> Hash -> Filter:   -> Table scan on t1 

默认配置时,MySQL 所有可能的情况下都会使用 hash join。同时提供了两种控制是否使用 hash join 的方法:

在全局或者会话级别设置服务器系统变量optimizer_switch中的 hash_join=on或者 hash_join=off选项。默认为 hash_join=on

在语句级别为特定的连接指定优化器提示 HASH_JOIN 或者 NO_HASH_JOIN。

可以通过系统变量join_buffer_size控制 hash join 允许使用的内存数量;hash join 不会使用超过该变量设置的内存数量。如果 hash join 所需的内存超过该阈值,MySQL 将会在磁盘中执行操作。需要注意的是,如果 hash join 无法在内存中完成,并且打开的文件数量超过系统变量 open_files_limit的值,连接操作可能会失败。为了解决这个问题,可以使用以下方法之一:

增加 join_buffer_size的值,确保 hash join可以在内存中完成。

增加 open_files_limit的值。

接下来他们比较一下 hash joinblock nested loop的性能,首先分别为 t1、t2 和 t3 生成 1000000 条记录:

set join_buffer_size=2097152000;SET @@cte_max_recursion_depth = 99999999;INSERT INTO t1-- INSERT INTO t2-- INSERT INTO t3WITH RECURSIVE t AS ( SELECT 1 AS c1, 1 AS c2 UNION ALL SELECT t.c1 + 1, t.c1 * 2 FROM t WHERE t.c1 < 1000000)SELECT * FROM t;

没有索引情况下的 hash join:

mysql> EXPLAIN ANALYZE -> SELECT COUNT -> FROM t1 -> JOIN t2 -> ON  -> JOIN t3 -> ON G*************************** 1. row ***************************EXPLAIN: -> Aggregate: count (actual time=22993.098..22993.099 rows=1 loops=1) -> Inner hash join  (cost=9952535443663536.00 rows=9952435908880402) (actual time=14489.176..21737.032 rows=1000000 loops=1) -> Table scan on t3 (cost=0.00 rows=998412) (actual time=0.103..3973.892 rows=1000000 loops=1) -> Hash -> Inner hash join  (cost=99682753413.67 rows=99682653660) (actual time=5663.592..12236.984 rows=1000000 loops=1) -> Table scan on t2 (cost=0.01 rows=998412) (actual time=0.067..3364.105 rows=1000000 loops=1) -> Hash -> Table scan on t1 (cost=100539.40 rows=998412) (actual time=0.133..3395.799 rows=1000000 loops=1)1 row in set mysql> SELECT COUNT -> FROM t1 -> JOIN t2 -> ON  -> JOIN t3 -> ON ;+----------+| COUNT |+----------+| 1000000 |+----------+1 row in set 

实际运行花费了 12.98 秒。这个时候如果使用 block nested loop:

mysql> EXPLAIN FORMAT=TREE -> SELECT /*+ NO_HASH_JOIN */ COUNT -> FROM t1 -> JOIN t2 -> ON  -> JOIN t3 -> ON G*************************** 1. row ***************************EXPLAIN: 1 row in set SELECT /*+ NO_HASH_JOIN */ COUNT FROM t1 JOIN t2 ON  JOIN t3 ON ;

EXPLAIN 显示无法使用 hash join。查询跑了几十分钟也没有出结果,其中一个 CPU 使用率到了 100%;因为一直在执行嵌套循环。

再看有索引时的 block nested loop 方法,增加索引:

mysql> CREATE index idx1 ON t1;Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0mysql> CREATE index idx2 ON t2;Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0mysql> CREATE index idx3 ON t3;Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0

查看执行计划并运行相同的查询语句:

mysql> EXPLAIN ANALYZE -> SELECT COUNT -> FROM t1 -> JOIN t2 -> ON  -> JOIN t3 -> ON G*************************** 1. row ***************************EXPLAIN: -> Aggregate: count (actual time=47684.034..47684.035 rows=1 loops=1) -> Nested loop inner join (cost=2295573.22 rows=998412) (actual time=0.116..46363.599 rows=1000000 loops=1) -> Nested loop inner join (cost=1198056.31 rows=998412) (actual time=0.087..25788.696 rows=1000000 loops=1) -> Filter:  (cost=100539.40 rows=998412) (actual time=0.050..5557.847 rows=1000000 loops=1) -> Index scan on t1 using idx1 (cost=100539.40 rows=998412) (actual time=0.043..3253.769 rows=1000000 loops=1) -> Index lookup on t2 using idx2   (actual time=0.012..0.015 rows=1 loops=1000000) -> Index lookup on t3 using idx3   (actual time=0.012..0.015 rows=1 loops=1000000)1 row in set mysql> SELECT COUNT -> FROM t1 -> JOIN t2 -> ON  -> JOIN t3 -> ON ;+----------+| COUNT |+----------+| 1000000 |+----------+1 row in set 

实际运行花费了 19.56 秒。所以在我们这个场景中的测试结果如下:

Hash Join

Block Nested Loop

Block Nested Loop

12.98 s 未返回 19.56 s

再增加一个 Oracle 12c 中无索引时 hash join 结果:1.282 s。

再增加一个 PostgreSQL 11.5 中无索引时 hash join 结果:6.234 s。

再增加一个 SQL 2017 中无索引时 hash join 结果:5.207 s。

总结

以上所述是小编给大家介绍的MySQL 8.0 新特性之哈希连接,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

编辑:MySQL数据库 本文来源:join测量试验_Mysql_脚本之家,新特点之哈希连接

关键词: