文档

基于代价的查询变换

更新时间:

本文档主要介绍了PolarDB MySQL版如何通过CBQT组件(Cost Based Query Transformation)实现基于代价的查询变换,从而大幅提升复杂查询的执行效率。

前提条件

  • 查询变换旧版

    集群版本需为PolarDB MySQL版8.0.2版本且Revision version为8.0.2.2.0及以上。

  • 查询变换新版

    集群版本需为PolarDB MySQL版8.0.2版本且Revision version为8.0.2.2.19及以上。

您可以通过查询版本号来确认集群版本。

背景信息

查询变换是指基于等价规则,将一个查询语句改写成语义上等价的另一种形式。如以下查询语句:

SELECT *
FROM d1
	JOIN f1 ON d1.c1 = f1.c1
	LEFT JOIN (
		SELECT d2.c2 AS d2_c2, f2.c3 AS f2_c3
		FROM d2, f2
		WHERE d2.c1 = f2.c1
	) derived
	ON derived.d2_c2 = d1.c2
		AND derived.f2_c3 = f1.c3;

以上查询语句可以通过物化表合并规则将物化表展开,展开后改写为如下查询语句:

SELECT *
FROM d1
	JOIN f1 ON d1.c1 = f1.c1
	LEFT JOIN (d2
		JOIN f2 ON TRUE)
	ON d2.c1 = f2.c1
		AND f2.c3 = f1.c3
		AND d2.c2 = d1.c2;

社区版MySQL目前只支持基于规则的查询变换,语义上能做的变换肯定会做,比如上述变换在社区版MySQL中肯定会进行。但实际上这些变换不一定是更优的,比如上述查询语句,如果d1f1d2f2之间没有索引关系,d1f1连接输出的每一行都会驱动d2f2做一次连接的重复计算,执行效率会大大降低。所以,某些查询语句需要根据执行代价来决定是否需要做查询变换,特别是复杂的变换,其往往会受到Cardinality、Access Method、Join Order等多种因素的影响。基于此,PolarDB MySQL版实现了基于代价的查询变换,能够基于执行代价选择是否做某种变换。

对于复杂查询,CBQT会收集该查询都可以做哪些基于代价的查询变换,这些变换会汇总成一个状态空间。CBQT会在该状态空间中选择生成执行计划代价最低的状态,执行对应的计划。如下图所示,对于输入的SQL语句,CBQT收集到基于代价的查询变换A和B,这两个变换组成的状态空间有:None(均不作变换)、A(只做变换A)、B(只做变换B)、AB(变换A和变换B都做)。这些状态空间分别对应不同的执行计划:Plan1、Plan2、Plan3、Plan4。CBQT会从中选择最优的执行计划,图中选择了Plan2,即查询变换A。同时,那些肯定能带来收益的变换被定义为基于规则的查询变换并被添加在框架中,如果符合变换规则,这些变换肯定会被调用。

image

以上述的查询语句为例,其经过物化表合并变换后形成的执行计划如下:

image

如果不做物化表合并,其执行计划如下:

image

这两种计划哪种更优取决于d2f2表与d1f2表之间是否有索引关联以及连接结果集大小。如果有索引关联关系,则合并计划可能更优。否则,不合并更优。CBQT框架会计算合并与不合并的代价并进行对比,最终选择一个适合当前数据场景下的更优的计划。

名词解释

查询嵌套及嵌套深度

查询嵌套指的是一个查询语块嵌套在另一个查询语句中的情况,前者为子查询或者内查询,后者为父查询或外查询。主要针对子查询和物化表的情况,嵌套深度为查询块嵌套的层数。示例如下:

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE t2.c=t1.b);

其嵌套深度为2,父查询为查询t1表的查询语句,子查询为查询t2表的查询语句。

需要注意的是,union查询是在同一层的SQL语句。示例如下:

SELECT *
FROM t1
WHERE t1.a IN (
	SELECT dt.b
	FROM (
		SELECT b, c
		FROM t2
		UNION
		SELECT b, c
		FROM t3
	) dt
	WHERE dt.c = t1.b
);

以上查询语句的嵌套深度为3,dt表中的子查询是由union组合而成,属于同一层查询。

变换

基于等价规则将查询转换为另一种形式。例如背景信息一节中的物化表合并。

变换对象

等价规则应用的对象。如背景信息一节中物化表合并示例中的derived表。不同的变换规则对应不同的变换对象,例如,物化表合并的变换对象为物化表,而子查询转Semi Join中的变换对象为子查询。

迭代

变换流程循环的次数。CBQT的变换流程会对所有对象上所有的可能变换做尝试,但在某些变换上做完尝试后,在某些查询块上可能会出现新的可以做变换的规则。例如,子查询转为物化表后就可以应用物化表合并规则。因此,需要对新产生的对象继续调用变换流程,即CBQT流程中的迭代。迭代次数是指反复调用变换流程的次数。

使用方法

基于代价的查询变换(CBQT)功能一直在演进,目前已经有了两个版本的CBQT能力,基于代价的查询变换功能的总开关是由参数cbqt_enabled控制。使用哪种版本的查询变换是由参数cbqt_version控制,当参数cbqt_version的值为1时,表示使用查询变换旧版。当参数cbqt_version的值为2时,表示使用查询变换新版。

参数名称

级别

描述

cbqt_enabled

Global、Session

基于代价的查询变换控制开关。取值范围如下:

  • ON(默认值):开启基于代价的查询变换。

  • OFF:关闭基于代价的查询变换。

  • REPLICA_ON:在只读节点上开启基于代价的查询变换。

cbqt_version

Global、Session

CBQT版本控制开关。取值范围如下:

  • 1(默认值):使用查询变换旧版。

  • 2:使用查询变换新版。

两个版本的详细参数使用说明如下:

查询变换旧版

由于基于代价的查询变换会增加执行计划的搜索空间,同时会增加一定的优化搜索时间。为了避免对短查询造成影响,您可以通过参数cbqt_cost_threshold来配置基于代价查询变换的代价阈值,只有执行代价超过该阈值的查询语句才会考虑使用基于代价的查询变换。对于PolarDB新加的子查询(MySQL社区没有的)使用GROUP BY解关联变换,该变换完全是基于代价的查询变换。对于MySQL社区已有的变换,您可以通过参数控制其是否基于代价选择查询变换。对于derived merge,您可以通过参数polar_optimizer_switch中的开关derived_merge_cost_based来选择其是否受基于代价的查询变换控制。

参数名称

级别

描述

cbqt_cost_threshold

Global、Session

当查询语句的原执行代价超过该值时,对查询语句使用基于代价的查询变换。

取值范围:0~18446744073709551615。默认值为100000。

cbqt_timeout

Global、Session

为避免搜索合适的查询变换花费太多的优化时间,当优化时间超过该值时,将不再继续搜索。

取值范围:0~18446744073709551615。默认值为200。单位为毫秒。

说明

该值为0时,表示搜索时间无限制。

polar_optimizer_switch

Global、Session

查询优化控制开关。取值范围如下:

  • unnest_use_window_function:利用Window function解关联的功能开关。

    • ON(默认值):开启利用Window function解关联功能。

    • OFF:关闭利用Window function解关联功能。

  • unnest_use_group_by:利用Group by解关联的功能开关,该查询变换受基于代价的查询变换控制。

    • ON(默认值):开启利用Group by解关联功能。

    • OFF:关闭利用Group by解关联功能。

  • derived_merge_cost_based:derived merge功能是否受基于代价的查询变换的控制。

    • OFF(默认值):derived merge功能不受基于代价的查询变换的控制。

    • ON:derived merge功能受基于代价的查询变换的控制。

查询变换新版

基于代价的查询变换需要反复迭代以搜索到最优执行计划,这可能会增加优化时间。因此,您可以通过以下参数来控制搜索的过程。

参数名称

级别

描述

cbqt_iteration_limit

Global

CBQT迭代次数。迭代次数越多选择出最优计划的可能性越大,但优化时间会更长。反之选出最优计划的可能性越小,优化时间更短。

取值范围:1~10。默认值为1。单位为次。

cbqt_max_nested_level

Global、Session

CBQT处理的查询语句最大嵌套深度。如果查询语句的嵌套深度超过该值,将不会对该查询语句使用CBQT。

取值范围:1~64。默认值为5。

cbqt_search_strategy

Global

CBQT搜索最优计划的策略。取值范围如下:

  • auto(默认值):自适应搜索。根据变换对象的情况自适应选择linear或twoPass策略。

  • linear:线性搜索。在该策略下,会依次对每一个对象都对比一下变换应用前后的代价,选择更优的计划。

  • twoPass:两遍搜索。在该策略下,只会对比所有对象都应用一个变换或者都不应用变换的代价,选择更优的计划。

cbqt_rule_switch

Global、Session

查询优化控制开关。该参数控制是否使用某种查询变换。取值范围如下:

  • merge_derived:基于代价的物化表合并规则开关。

    • ON(默认):启用基于代价的物化表合并规则。

    • OFF:禁用基于代价的物化表合并规则。

  • subquery_to_derived:基于代价的子查询转物化表规则开关。

    • ON(默认):启用基于代价的子查询转物化表规则。

    • OFF:禁用基于代价的子查询转物化表规则。

  • unnest_subquery_by_groupby:基于代价的利用Group by解关联规则开关。

    • ON(默认):启用基于代价的利用Group by解关联规则。

    • OFF:禁用基于代价的利用Group by解关联规则。

  • unnest_subquery_by_windows:利用Window function解关联规则开关。

    • ON(默认):启用利用Window function解关联规则。

    • OFF:禁用利用Window function解关联规则。

  • derived_projection_pruning:物化表投影列裁剪规则开关。

    • ON(默认):启用物化表投影列裁剪规则。

    • OFF:禁用物化表投影列裁剪规则。

  • cond_pushdown:条件下推规则开关。

    • ON(默认):启用条件下推规则。

    • OFF:禁用条件下推规则。

  • heuristic_merge_derived:启发式的物化表合并规则开关。

    • ON(默认):启用启发式的物化表合并规则。

    • OFF:禁用启发式的物化表合并规则。

  • subquery_to_semijoin:子查询转semijoin规则开关。

    • ON(默认):启用子查询转semijoin规则。

    • OFF:禁用子查询转semijoin规则。

  • heuristic_coalesce_subquery:启发式的子查询折叠规则开关。

    • ON(默认):启用启发式的子查询折叠规则。

    • OFF:禁用启发式的子查询折叠规则。

  • coalesce_subquery:基于代价的子查询折叠规则开关。

    • ON(默认):启用基于代价的子查询折叠规则。

    • OFF:禁用基于代价的子查询折叠规则。