【 TiDB 使用环境】测试/ Poc
【 TiDB 版本】7.5.0
想把hashJoin的算子改为indexJoin，使用 explain SELECT /*+ INL_JOIN(table1,table2) */后，查看执行计划还是用的hashJoin，有知道原因的吗
mysql 客户端加个参数 -c 看一下是不是有 warning
有日志:
Warning | 1815 | Optimizer Hint /*+ INL_JOIN(table1, table2) / or /+ TIDB_INLJ(table1, table2) */ is inapplicable
那肯定是有限制了，用不了。
最经典的情况是你没有索引，它是搞不了这个 hint 的。
1 个赞
表名有别名的要用别名。
我看了下连表的字段，一个是主键id，另外一个是普通索引
没用别名哦
常见-hint-不生效问题排查 先看看这个，还有可能是隐式转换了导致不生效
1 个赞
把你原始sql发一下呢
执行计划发一下吧
explain SELECT COUNT(id) amount FROM table1 WHERE b_id=241 AND status=1 AND ((look_limit=1 ) OR user_id=xxx OR (look_limit=3 AND id IN ( SELECT r_id FROM table2 WHERE x_id=241 AND user_id=xx AND status = 1 ) )) AND app=1; 类似这样的sql，做了脱敏
应该是排序规则不兼容，你看下表的COLLATE
show create table table1;
show create table table2;
看了两张表的COLLATE=utf8mb4_bin是一样的
show warnings;报什么
尝试了一下，好像是不行。这个 or 里面带了子查询，建议把 or 改成 union 就可以了。
mysql> explain select * from t1 where (t1.b=0 or t1.a in (select a from t2 where t2.b=2));
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| Projection_8 | 8000.00 | root | | test.t1.a, test.t1.b |
| └─Selection_9 | 8000.00 | root | | or(eq(test.t1.b, 0), Column#7) |
| └─HashJoin_10 | 10000.00 | root | | CARTESIAN left outer semi join, other cond:eq(test.t1.a, test.t2.a) |
| ├─TableReader_15(Build) | 10.00 | root | | data:Selection_14 |
| │ └─Selection_14 | 10.00 | cop[tikv] | | eq(test.t2.b, 2) |
| │ └─TableFullScan_13 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_12(Probe) | 10000.00 | root | | data:TableFullScan_11 |
| └─TableFullScan_11 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> explain select /*+inl_join(t1)*/ * from t1 where (t1.b=0 or t1.a in (select a from t2 where t2.b=2));
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| Projection_8 | 8000.00 | root | | test.t1.a, test.t1.b |
| └─Selection_9 | 8000.00 | root | | or(eq(test.t1.b, 0), Column#7) |
| └─HashJoin_10 | 10000.00 | root | | CARTESIAN left outer semi join, other cond:eq(test.t1.a, test.t2.a) |
| ├─TableReader_15(Build) | 10.00 | root | | data:Selection_14 |
| │ └─Selection_14 | 10.00 | cop[tikv] | | eq(test.t2.b, 2) |
| │ └─TableFullScan_13 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_12(Probe) | 10000.00 | root | | data:TableFullScan_11 |
| └─TableFullScan_11 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> explain select * from t1 where t1.b=0
-> union
-> select /*+inl_join(t1)*/ * from t1 where t1.a in (select a from t2 where t2.b=2);
+--------------------------------------+----------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+----------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------+
| HashAgg_16 | 17.99 | root | | group by:Column#10, Column#11, funcs:firstrow(Column#10)->Column#10, funcs:firstrow(Column#11)->Column#11 |
| └─Union_17 | 19.99 | root | | |
| ├─TableReader_21 | 10.00 | root | | data:Selection_20 |
| │ └─Selection_20 | 10.00 | cop[tikv] | | eq(test.t1.b, 0) |
| │ └─TableFullScan_19 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─IndexJoin_28 | 9.99 | root | | inner join, inner:IndexLookUp_27, outer key:test.t2.a, inner key:test.t1.a, equal cond:eq(test.t2.a, test.t1.a) |
| ├─HashAgg_45(Build) | 7.99 | root | | group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a |
| │ └─TableReader_46 | 7.99 | root | | data:HashAgg_38 |
| │ └─HashAgg_38 | 7.99 | cop[tikv] | | group by:test.t2.a, |
| │ └─Selection_44 | 9.99 | cop[tikv] | | eq(test.t2.b, 2), not(isnull(test.t2.a)) |
| │ └─TableFullScan_43 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─IndexLookUp_27(Probe) | 1.25 | root | | |
| ├─Selection_26(Build) | 1.25 | cop[tikv] | | not(isnull(test.t1.a)) |
| │ └─IndexRangeScan_24 | 1.25 | cop[tikv] | table:t1, index:a(a) | range: decided by [eq(test.t1.a, test.t2.a)], keep order:false, stats:pseudo |
| └─TableRowIDScan_25(Probe) | 1.25 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+--------------------------------------+----------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)
1 个赞
换下连接顺序
感谢，我试试
学习一下
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。