自适应执行能力

背景信息

优化器会根据统计信息和部分数据采样信息来做基数和估算选择率，这些信息可能因采样精度、采样方式等原因导致估算偏差较大。同时，还有广泛的场景没有相关的统计信息，此时数据库会根据经验值或者某种数学假设去评估统计信息，这可能会导致评估的值与实际值偏差较大。对于优化器估算与实际不符导致选错执行计划的场景，优化器难以预先收集对应的信息。

自适应执行能力

行列自适应分流

使用列存索引（IMCI）功能时，若您配置的是行列自动分流，执行的查询可能会由于行存执行代价的估算偏低，导致被路由到行存执行，而非列存执行。

针对上述路由错误的问题，之前的解决方案如下：

• 调低路由到列存的代价阈值，此方案容易导致大量的短查询路由到列存，从而导致列存负载太高，阻塞了其他查询。

• 使用计划固化 （Statement Outline）功能，将固定指定模板的查询路由到列存，如通过添加以下示例中的outline可以强制该查询语句路由到列存执行。但该方式会导致运维成本大幅增加。

  CALL dbms_outln.add_optimizer_outline("<Schema_name>", "/*+ SET_VAR(cost_threshold_for_imci=0) SET_VAR(imci_ap_threshold=0) */","<query>");

当前PolarDB MySQL版的自适应执行可以应用在行列路由中，将错误路由到行存的慢查自动切换到列存执行，保证执行效率。如下图所示通过自适应执行，数据库会在优化阶段标记将查询中各个Query block和整体的扫描数据行数加入到监控信息中。如果查询在代价计算后未选择列存，优化器将计算触发自适应执行的阈值。在新版本的集群中（如8.0.1.1.49及以上或 8.0.2.2.28及以上），该阈值基于参数loose_adaptive_cost_threshold_for_imci进行计算；而在旧版本的集群中，则基于参数loose_cost_threshold_for_imci进行计算。在执行阶段，当Query block或整体扫描行数触发自适应执行的阈值，即检查是否切换列存执行。这里扫描行数的监控在核心路径上仅是一个整数比较，并不影响执行性能。同时切换列存前，会保证行存执行的结果集没有返回给客户端。选择切换列存后，会清空已缓存的行存结果集。

有序索引的自适应选择

在数据库查询优化中，prefer_ordering_index 是一个优化选项，用来指示查询优化器优先使用可支持排序操作的索引。然而，在某些情况下，这个优化可能会导致查询性能下降。特别是当排序索引和查询的选择性不匹配时，由于排序索引的低选择性或大量数据扫描回表的情况，选择排序索引可能会导致查询扫描大量无关数据，增加I/O和CPU的负载。

针对上述由于prefer_ordering_index导致的慢查询，解决方案如下：

可以使用计划固化 （Statement Outline）功能固定指定SQL模板的查询关闭prefer_ordering_index。但该方式会导致运维成本大幅增加。同时若在该SQL模板下，有部分查询开启prefer_ordering_index后执行效率更高，则会受到影响。

CALL dbms_outln.add_optimizer_outline("<Schema_name>", "/*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */","<query>");

当前自适应执行可以应用在有序索引的选择纠偏中，当查询因为prefer_ordering_index 优化而执行效率不佳时，会重新调整执行计划，从而将执行计划从有序索引上纠正回来。如果查询因为prefer_ordering_index优化选择了有序索引，优化器会计算触发自适应执行的阈值。在执行阶段，有序索引上执行行数触发阈值条件时，会检查是否切换执行计划重新执行。这里扫描行数的监控在核心路径上仅是一个整数比较，并不影响执行性能。同时切换执行计划前，会保证执行的结果集没有返回给客户端。选择切换计划后，会清空已缓存的行存结果集。

使用前提

• 开启行列自适应分流（对应使用说明的imci_chosen开关）能力的集群要求如下：

  • PolarDB MySQL版8.0.1版本且修订版本为8.0.1.1.39及以上。

  • PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.25及以上。

• 开启有序索引的自适应选择（对应使用说明的ordering_index开关）的集群要求如下：

  • PolarDB MySQL版8.0.1版本且修订版本为8.0.1.1.47及以上。

  • PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.27及以上。

使用说明

开启或关闭自适应执行能力

您可以登录PolarDB控制台。在目标集群的参数配置页面配置自适应执行功能：

• 将参数loose_adaptive_plans_switch的值设置为'imci_chosen=on'来开启行列自适应分流的能力。

• 将参数loose_adaptive_plans_switch的值设置为'ordering_index=on'来开启针对prefer_ordering_index优化选中的有序索引进行自适应调整。

• 将参数loose_adaptive_plans_switch的值设置为'imci_chosen=on,ordering_index=on'同时开启两项自适应能力，或是通过将其中某项的值设置为'off'关闭对应的自适应能力。

设置参数值的具体操作请参见设置集群参数和节点参数，使用自适应执行能力时涉及的具体参数见下表。

查看自适应切换执行计划的次数

您可以在数据库中执行以下SQL语句，来查看从开启自适应执行能力开始，截止到目前的自适应切换执行计划的次数。

SHOW GLOBAL STATUS LIKE 'Adaptive_plan_used';

其中，变量Adaptive_plan_used说明如下：