Mysql 8.0.18 hash join Test (recommended)

Hash Join

Hash Join does not require any indexes to execute and is in most cases more efficient than the current block nesting loop algorithm.

The following is an introduction to the Mysql 8.0.18 hash join test through the example code, as shown below:

CREATE TABLE COLUMNS_hj as select * from information_ schema.`COLUMNS`; INSERT INTO COLUMNS SELECT * FROM COLUMNS;-insert 250000 rows of CREATE TABLE COLUMNS_hj2 as select * from information_ schema.`COLUMNS` for the last time; explain format=treeSELECT COUNT (C1. PRIVILEGES), SUM (c1.ordinal_position) FROM 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

You must use format=tree (a new feature of 8.0.16) to view the execution plan of 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 (C1. PRIVILEGES), SUM (c1.ordinal_position) FROM 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

About 1.5 seconds.

Let's take a look at BNL, which creates the index first (optimized separately, and then the comparison effect is fair).

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

About 4.5 seconds. It can be seen that the hash join effect is still leveraged.

I have to complain about the optimizer hint of mysql, it seems that HASH_JOIN/NO_HASH_JOIN doesn't work.

In addition to hash_join, the SET_VAR optimizer hint introduced by mysql 8.0.3 is easy to use to set statement-level parameters (supported by oracle and also supported by mariadb), as follows:

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

List of variables supported by SET_VAR:

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

Summary

The above is the Mysql 8.0.18 hash join test introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!