Mysql 8.0.18 hash join测试(推荐)_Mysql

Hash Join

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

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

									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=tree

									SELECT

									 COUNT(c1. PRIVILEGES),

									 SUM(c1.ordinal_position)

									FROM

									 COLUMNS_hj c1,

									 COLUMNS_hj2 c2

									WHERE

									 c1.table_name = c2.table_name

									AND c1.column_name = c2.column_name

									GROUP BY

									 c1.table_name,

									 c1.column_name

									ORDER BY

									 c1.table_name,

									 c1.column_name;

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

									-> Sort: <temporary>.TABLE_NAME, <temporary>.COLUMN_NAME

									 -> Table scan on <temporary>

									  -> 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(c1. PRIVILEGES),

									 SUM(c1.ordinal_position)

									FROM

									 COLUMNS_hj c1,

									 COLUMNS_hj2 c2

									WHERE

									 c1.table_name = c2.table_name

									AND c1.column_name = c2.column_name

									GROUP BY

									 c1.table_name,

									 c1.column_name

									ORDER BY

									 c1.table_name,

									 c1.column_name;

1.5秒左右。

再来看BNL，先创建索引（分别优化了，再对比效果才公平）。

									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: <temporary>.TABLE_NAME, <temporary>.COLUMN_NAME

									 -> Table scan on <temporary>

									  -> 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`) (cost=127.50 rows=127)

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

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

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

1	`mysql>` `select` `/+ set_var(optimizer_switch='index_merge=off') set_var(join_buffer_size=4M) / c_id` `from` `customer limit 1;`

SET_VAR支持的变量列表：

									auto_increment_increment

									auto_increment_offset

									big_tables

									bulk_insert_buffer_size

									default_tmp_storage_engine

									div_precision_increment

									end_markers_in_json

									eq_range_index_dive_limit

									foreign_key_checks

									group_concat_max_len

									insert_id

									internal_tmp_mem_storage_engine

									join_buffer_size

									lock_wait_timeout

									max_error_count

									max_execution_time

									max_heap_table_size

									max_join_size

									max_length_for_sort_data

									max_points_in_geometry

									max_seeks_for_key

									max_sort_length

									optimizer_prune_level

									optimizer_search_depth variables

									optimizer_switch

									range_alloc_block_size

									range_optimizer_max_mem_size

									read_buffer_size

									read_rnd_buffer_size

									sort_buffer_size

									sql_auto_is_null

									sql_big_selects

									sql_buffer_result

									sql_mode

									sql_safe_updates

									sql_select_limit

									timestamp

									tmp_table_size

									updatable_views_with_limit

									unique_checks

									windowing_use_high_precision