请注意：这是一个MySQL的sql hint的问题
MySQL版本是8.0.30
语句
select count(*) from customer a,orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMMENT,1,10);
数据都来源于tpch库，语句来源于这个帖子
需求，不知道怎么在MySQL里面加个hint ，让a表和b表走hash join，按照官方的文档的做法
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-table-level
应该使用BNL或者NO_BNL ，但是试了几次也不知道应该怎么用
我只能用以下的hint 来让优化器使用hash join，麻烦请各位老师指导下，MySQL怎么让优化器强制使用hash join(只是好奇，走index join 肯定性能高)
mysql>explain analyze select /*+ no_index(a PRIMARY) no_index(b indexO_CUSTKEY)*/ count(*) from customer a,orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMME
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0) (cost=2229157716.05 rows=1) (actual time=614.465..614.465 rows=1 loops=1)
-> Inner hash join (b.O_CUSTKEY = a.C_CUSTKEY), (substr(a.C_COMMENT,1,10) = substr(b.O_COMMENT,1,10)) (cost=2229135302.38 rows=224137) (actual time=614.461..614.461 rows=0 loops=1)
-> Table scan on b (cost=16.01 rows=1505768) (actual time=0.060..341.510 rows=1500000 loops=1)
-> Hash
-> Filter: (a.C_PHONE = '25-989-741-2988') (cost=15275.30 rows=14802) (actual time=0.090..47.197 rows=1 loops=1)
-> Table scan on a (cost=15275.30 rows=148023) (actual time=0.088..37.339 rows=150000 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.62 sec)
