MYSQL大数据量 join操作的性能调优：欺骗查询计划器以命中HASH JOIN

笔者当前所在mysql版本为8.0.29；mysql在8.0.20,8.0.23逐步演进并完善了部分hash join的功能。

hash join我理解的算法基础即基于hash的桶算法。本质相当于创建一个hashMap，将连接条件作为key直接存入，本质算法复杂度为O(2n)（查询计划会并发扫表实际为n）,而循环算法复杂度为O(n²),索引命中的算法下，假设索引分支性能优化优化数量为10倍，也仅仅是O(n²/100),是完全不可同日而语的。但mysql的join语法任然以索引命中优先，并且由于优化器优化顺序的问题，极难命中hash join。本文介绍一种几乎可以稳定命中hash join的方式。

hash join在当前版本中无法命中最基础的原因是链接条件中存在like操作与or链接。在实测过程中部分or操作也会导致hash不命中的问题。尽可能视情况使用union语句处理(主要是union带来的多次扫表的性能取舍)。

hash命中的优化思路主要是使用子sql分别扫表，有了临时表就可以直接用临时表进行hash过程了。

现有如下原始sql：

SELECT
	a.*,
	b.* 
FROM
	test2 a
	LEFT JOIN test1 b 
    ON a.test21 = b.test11 AND b.test12 = '1' AND test13 = '2'
	WHERE a.test22 = '1'
    

先将a表全表与b表ON操作后再where是非常低效的。初步优化为

SELECT
	a.*,
	b.* 
FROM
	( SELECT * FROM test2 WHERE test22 = '1' ) a
	LEFT JOIN ( SELECT * FROM test1 WHERE test12 = '1' AND test13 = '2' ) b 
    ON a.test21 = b.test11

此时获取到了先缩小结果集后join的性能优化。并且语义上已经符合了hash join的触发标准，即没有索引列存在（因为是临时表）。但实际的执行过程中，查询优化器首先忽视你的子sql结构，将它直接展开为普通sql从而获取到正常的查询计划仍然直接对test1，test2物理表进行join操作，从而获取到了物理表字段上的索引，从而使用其他的循环join算法（真的非常蛋疼）。验证这一步可以复杂化表sql后查看执行计划：

SELECT
	a.*,
	b.* 
FROM
	( SELECT * FROM test2 WHERE test22 = '1' ) a
	LEFT JOIN ( 
        SELECT * FROM ( SELECT * FROM test1 WHERE test12 = '1' ) b1 WHERE test13 = '2' 
    ) b ON a.test21 = b.test11

使用EXPLAIN或EXPLAIN FORMAT = tree查看查询计划，会发现第二条和第三条sql查询计划是一样的，从而判断mysql查询计划对子sql产生了破坏性优化。

我用了一种愚蠢又粗暴的做法干预查询器，对每个join的子表做一次全字段无意义的group by操作：

SELECT
	a.*,
	b.* 
FROM
	( SELECT test21, test22, test23 FROM test2 
		WHERE test22 = '1' 
		GROUP BY test21, test22, test23 ) a
	LEFT JOIN ( 
	SELECT 
		test11, test12, test13 
		FROM test1 
		WHERE test13 = '2' AND test12 = '1' 
		GROUP BY test11, test12, test13 
	) b ON a.test21 = b.test11

果然骗过了查询计划器，查询计划器不会对group by操作破坏性优化，EXPLAIN FORMAT = tree查看查询计划发现已经变成hash join。这种方式挺稳定的，没有发现满足hash条件时不可触发的情况。group by不影响扫表次数，浪费一些CPU资源微乎其微。

在我的生产环境测试过程中，三张二十万数据的子sql结果集相互join的过程只花了0.5秒，sql时长几乎等同于最慢的一张表的扫表速度，join过程微乎其微。但桶计数的方式对内存压力较大，默认的16M临时表大小不够祸祸，可以另寻教程调配此参数（我直接设成了4个G，不知道会不会有什么隐患，32G的机器，配置后生产环境机器内存最多能吃到百分之七十，但现在来看稳定性可靠。）