Hint中表连接顺序LEADING应支持多表关联优先级和兼容物理优化阶段Join算法的选择

🌞 建议反馈 产品需求
1

【需求涉及的问题场景】多表join过程中leading不方便使用以及不支持对物理优化阶段Join算法的选择

【期望的需求行为】支持leading多表优先级，对选择 join 算法（ 即 MERGE_JOIN 、INL_JOIN 、INL_HASH_JOIN 、HASH_JOIN）兼容。

1、 希望leading支持多表关联优先级

目前tidb的join reorder能力还有待进一步加强，平时也遇到较多需要手工指定join顺序来优化语句的情况，举例：

都是因为join reorder算法不足导致没有选择较好的连接顺序，因此我们需要自己人工指定顺序，但是在使用leading的时候发现leading功能不支持优先级，比如：
select * from a,b,c where a.id=b.id and a.id=c.id 表的连接顺序有：
(a,b),c
c,(a,b)
(a,c),b
b,(a,c)
但是leading语法中并不支持优先级的选择，因此是否可以增强leading支持如下形式：
leading(a,(b,c))
leading((a,b),(c,d))
可以让leading支持多表关联的优先级？

2、 希望leading兼容对选择 join 算法（ 即 MERGE_JOIN 、INL_JOIN 、INL_HASH_JOIN 、HASH_JOIN）的支持

因为leading发生在逻辑优化阶段我理解这并不与物理优化（join算法的选择）冲突，为何一直不兼容join算法的选择呢。

https://docs.pingcap.com/zh/tidb/stable/optimizer-hints

1 个赞
2

11111
111111021×268 12.3 KB

今天听了毛老师的讲解，感觉你这个提议确实非常使用，在确定leading不支持优先级以后，我想起是否可以用STRAIGHT_JOIN+from 后面表的顺序来完成类似的功能。至少STRAIGHT_JOIN没有不能指定join算法的限制。

我试了一下如上图，好像可以部分完成你要求的这两点。因为我没有tpch的环境，所以不确定在tpch的情况下是否会有好的表现。

1 个赞
3

有没有可能提个 github issue 来记录并反馈这个问题

1 个赞
4

oceanbase 支持这种多表关联的写法

5

感谢答复，leading属于半自动挡，straight_join属于完全手动挡。在较为复杂的查询场景下，只是局部顺序存在问题，那么通过leading可以让局部手动调整，在整体上还是优化器自动调整，因此会减少复杂度。straight_join对于复杂情况尤其较多张表关联，那么全部指定join顺序对DBA来说也是较为困难的。而且对于(a,b),(c,d) 这种连表顺序不修改SQL很难达到想要的结果，即使用了straight_join（很多复杂SQL可能是拼装生成的，不允许修改SQL顺序，这就导致straight_join受限）。
另外，关于这块表达能力最好的我认为当属DB2的hint，采用了xml的表达方式,可以表达出来几乎所有的优化器能表达的场景，针对我们这里简单的(a,b),(c,d)关联方式可以对标如下：

<HSJOIN>
	<JOIN>
	    <ACCESS TABLE='a'/>
	    <LPREFETCH TABLE='b' INDEX='b_idx'/>
	</JOIN>

	<NLJOIN>
	  <ACCESS TABLE='c' FIRST='TURE'/>
	  <IXSCAN TABLE='d'/>
	</NLJOIN>
</HSJOIN>

解释：

  1. a,b进行关联，关键字是让优化器自行选择物理优化阶段的join方式（hashjoin、nljoin等），且a,b谁是外表由优化器自己选择，对于b表指定让其走b_idx索引且通过LPREFETCH高效取出所有索引RID回表方式（这里LPREFETCH指定了数据请求模式）。

  2. c,d进行关联，关键字是强制走NLJOIN算法，其中c表是作为外表先执行（first=true），然后告诉d表走索引，但是让优化器自己选择一个高效索引。

  3. 最后(a,b) hash join (c,d) 是告诉优化器整体上走hash join,自己选择内外表。

  4. 通过上述hint可以让一个复杂语句中a,b,c,d这四张表按照我们指定的顺序和算法甚至数据请求方式来走执行计划。

这只是最简单通用场景的一种，xml表达能力非常强，DB2对于优化器Hint这块提供了完备的XML Schema定义，非常强大。
MySQL的hint表达能力太弱，只兼容MySQL较为受限。TiDB也可以适当借鉴DB2这种样式，定义自己专有的一套表达机制。

1 个赞