执行计划错误使用hashjoin

[h5n1]

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【TiDB 版本】4.0.12

【问题描述】
user表hash 32个分区，trade表trad_id为主键首列，where条件过滤后只有1条数据。执行计划使用Hashjoin对user表分区做rangescan造成执行时间很长，使用/+ TIDB_INLJ(a,b)/提示仍使用hashjoin不能使用索引嵌套循环。

[yilong (yi888long)]

麻烦反馈下，建表语句和 explain analyze sql 的执行计划，多谢。（执行计划太长，可以上传到文件中，多谢。）

[h5n1]

SQL.txt (76.5 KB)
sql1.txt (6.5 KB)
sql1.txt为 和另一张非分区表的关联查询，各表数据量
TF_F_USER_DISCNT：1449358763 TF_B_TRADE：1938136 ，TF_F_USER： 约140800000每分区440万

[spc_monkey (carry@pingcap.com)]

使用 hint 时，是在 mysql 客户端验证的还是程序反馈不好用，如果是客户端验证的，记得在客户端命令加上 --comments 参数，官网有类似提醒，可以看看

[h5n1]

加上comments 后提示仍未生效，另外问题的关键是为什么会选择hashjoin，为什么explain analyze显示rpc时间很长？

把tf_f_user重建为非分区表后，执行计划正常很快出结果

[QBin (Bin)]

1. RPC 时间是该算子 RPC 请求时间的总和，所以显示的时间会很大。
2. 可以先参考一下分区裁剪的规则，以及有哪些情况不适合分区裁剪。
   https://docs.pingcap.com/zh/tidb/stable/partition-pruning#hash-分区表上不能使用分区裁剪的场景
   https://docs.pingcap.com/zh/tidb/stable/partitioned-table#分区裁剪

[h5n1]

a表取出1条数据，然后取和b表的主键字段关联**(主键是单列bigint，PRIMARY KEY (USER_ID))**，就算是没有分区裁剪，就1条数据也不至于执行1分多钟吧，而且改为非分区表后就正常了 ， RPC是请求总和那么这些请求是消耗在什么地方，根据rowid去匹配数据一个分区需要多少请求？
另外表要么全表扫描要么通过索引定位，对于TableRangscan是具体怎样执行的？
不能使用index join 原因应该是分区表不支持内表为分区表？

[spc_monkey (carry@pingcap.com)]

问一下分区健是哪个字段啊

[h5n1]

PARTITION BY HASH( user_id )
PARTITIONS 32 |

[spc_monkey (carry@pingcap.com)]

哦，哪你这个是使用不了分区裁剪的，你的这个SQL 用不了的原因是：

，其他限制想要了解可以参考上面发的链接，分区表在不断优化中，咱们类似的使用场景多么？

[h5n1]

我觉得这个问题不是分区裁剪的事，而是每个分区都扫描都要消耗比较长时间，tablerange scan不可能扫描整个分区吧

[spc_monkey (carry@pingcap.com)]

没太理解你的意思，不过我理解：由于在生成执行计划前，不知道 user-id 的具体范围，所以无法判断可以裁剪哪些分区，只能扫描全部分区，导致成本估算很大，所以采用了 hash join。同时你引出了另一个问题：为什么使用 hint 还没有选择使用 NL 的关联方式，这个我建议你 带上 --comments 的同时，把 hint 改成 ```
/*+ INL_JOIN(a, b) */ 试试

[h5n1]

trade表根据过滤条件只有1数据(评估为3条)，然后根据获得的user_id和 user表的user_id去匹配，由于是执行中获得的值所以不能进行分区裁剪，对user表每个分区进行扫描，使用tableranscane，执行计划中评估user表返回行数每个分区都400多万万，这个行数时如何评估的？根据trade表的trade_id评估返回的行数时3条，然后去和user表的主键列关联，怎么也不应该是每个分区评估出来400万条。。user是分区表时每个分区的tablerange scan的范围是range:[0,+inf],而非分区表时范围是range:decide by[d_order.tf_b_trade.user_id]

[h5n1]

被驱动表改成range分区测试，评估行数依然不正确。

[tiancaiamao (Genius)]

分区表在旧版本里面不能走 index lookup join 是已知问题
master 版本中开始支持了
应该 5.1 是开始 (实验性质 or 正式?) 支持分区表走 index lookup join
@h5n1

[qizheng]

5.1 已经支持了，可以设置 tidb_partition_prune_mode 为 dymanic 测试下性能
https://docs.pingcap.com/zh/tidb/v5.1/system-variables#tidb_partition_prune_mode-从-v51-版本开始引入

[h5n1]

5.1版本 tidb_partition_prune_mode = dymanic 测试确实解决分区 indexjoin问题，感谢！