mysql 8.0.18 hash join测试（内外网首文）

oracle天杀的，8.0.19版本居然又把hash join优化器提示废弃了，卧槽，再不信你了。不过一定要支持的话，可以通过设置NO_BNL强行走hash join，从8.0.20开始，hash join已经支持半连接、反连接、左右连接，不仅限于内连接。

CREATE TABLE COLUMNS_hj as select * from information_schema.`COLUMNS`;
INSERT INTO COLUMNS_hj SELECT * FROM COLUMNS_hj; -- 最后一次插入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秒左右（相比mariadb的hash join实现BNLH join来说，要稳定的多）。

 

再来看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仅支持普通连接hash join，不支持反连接、半连接、外连接。

2019/11/15更新：各种场景的对比，也可以参考下https://mysqlserverteam.com/hash-join-in-mysql-8/

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

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