计划固定（STATEMENT OUTLINE）_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

背景信息

在实际运行的数据库环境中，慢SQL问题常常困扰着运维团队和开发人员。慢SQL的成因多种多样，可能源于数据分布不均、统计信息不准确等因素。

为了生成更优的执行计划，使用HINT干预优化器行为是一种常见且有效的手段。然而，直接要求数据库管理员在业务SQL语句中添加HINT会面临诸多挑战：

添加HINT的复杂性：许多应用的SQL是通过中间件自动生成的，手动添加HINT既不现实又十分繁琐。
发布流程的复杂性：在应用程序中手动添加HINT，并且每次调整HINT都需要发布新的应用版本，这不仅耗时耗力，还可能带来额外的风险。
HINT管理的复杂性：在应用程序中添加了大量HINT后，管理成本随之增加，数据库管理员难以清晰了解整个系统中存在的HINT数量。

针对上述困境，PolarDB PostgreSQL版推出对某类SQL自动增加HINT的能力，即执行计划固定（Statement OUTLINE），简称OUTLINE。该功能可在不影响业务应用程序的情况下，精确控制SQL执行计划。

功能介绍

OUTLINE功能允许您手动固定特定SQL语句的执行计划，并对这类SQL的HINT进行添加和修改。OUTLINE提供了以下优势：

独立于应用程序：无需修改应用程序代码，也无需重新发布，仅在数据库层面进行配置即可生效。
快速响应和调整：及时响应并调整执行计划，以有效解决慢SQL问题，提升系统的稳定性与性能。
精细化控制和管理：为不同的SQL提供相应的OUTLINE，可以清晰呈现整个系统的OUTLINE及其状态（例如命中情况、是否开启等）。

在不影响业务应用程序的情况下，您可通过OUTLINE功能精确控制SQL执行计划，从而高效解决慢SQL问题。

基本概念

HINT：优化器提示，通过注释的方式干预优化器生成不同的执行计划。
OUTLINE：指针对某类SQL指定增加特定的HINT，称之为创建了一个OUTLINE。
OUTLINE DDL：指对OUTLINE的修改，包括新增、删除、状态改变。
SQL_ID：SQL语句标识符，由数据库系统自动生成，用于区分不同的SQL。OUTLINE使用SQL_ID区分和匹配目标SQL。

前提条件

支持的PolarDB PostgreSQL版的版本如下：
PostgreSQL 14（内核小版本14.13.28.0及以上）
说明
您可通过如下语句查看PolarDB PostgreSQL版的内核小版本号：
```
SELECT version();
```
如需升级内核小版本，请参见升级版本。
创建pg_hint_plan插件，且插件版本不低于1.4.1，详细插件介绍请参见pg_hint_plan。

注意事项

SQL_ID匹配标准：数据库内核会归一化一类SQL，忽略以下因素对OUTLINE匹配的影响。
- 空格、换行和注释。
- SQL中的参数，包括常量和变量。例如，a = 1、a = $1、a = 2将被归一化。
- 关键字的大小写。例如，SELECT a、select a、Select a将被归一化。
- 对于连续参数，将忽略参数个数的影响。例如，a IN (1,2,3)、a IN (1,2)将被归一化，但是，a IN (1,2,3)与a IN (1)不会执行归一化，原因是IN (1)不是连续的参数。
支持为一类SQL创建多个OUTLINE：多个OUTLINE将相互叠加，且该SQL语句受到多个HINT的影响。
OUTLINE优先级高于SQL语句中自带的HINT：对SQL创建OUTLINE之后，原SQL语句中的HINT将失效，仅受OUTLINE中HINT影响。
OUTLINE和pg_hint_plan的hint_table功能互斥：OUTLINE与hint_table功能类似，开启OUTLINE功能后，hint_table功能默认失效。

性能影响

OUTLINE设计了高并发缓存模块，对性能影响极小。启用并添加OUTLINE后，Sysbench标准压测显示TPS和QPS仅下降1%~2%左右。

开启OUTLINE功能

在需要使用OUTLINE功能的数据库中确认是否已安装pg_hint_plan插件，以及插件版本是否大于等于1.4.1。
```
SELECT extname, extversion >= '1.4.1' AS outline_version_ok FROM pg_extension WHERE extname = 'pg_hint_plan';
```
如返回结果如下则插件安装成功且满足OUTLINE功能使用版本要求。
```
   extname    | outline_version_ok
--------------+--------------------
 pg_hint_plan | t
(1 row)
```
若返回结果不一致，可能存在以下问题。您可通过对应方式进行处理：
说明
请在目标数据库中使用具有相应权限的账号执行以下语句。
- 插件不存在：
```
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;
```
- 已安装插件，但不满足插件版本要求：
```
ALTER EXTENSION pg_hint_plan UPDATE;
```
开启OUTLINE功能。设置参数pg_hint_plan.polar_enable_outline为on，修改该参数不会造成集群重启。通过控制台设置集群参数详细操作，请参见设置集群参数。

使用OUTLINE功能

创建OUTLINE

PolarDB提供hint_plan.create_outline函数，用于创建OUTLINE。输入需要固定的SQL后，将自动提取SQL中的Hints并加入OUTLINE。

示例

CALL hint_plan.create_outline($$ SELECT /*+ Set(enable_bitmapscan off) */ * FROM t WHERE a = 1 $$);

查看OUTLINE

PolarDB PostgreSQL版提供hint_plan.outlines_status视图，用于查看数据库内的OUTLINE。

您可以通过以下语句查询数据库中的OUTLINE：

SELECT * FROM hint_plan.outlines_status;

返回字段说明如下：

列名	数据类型	描述

列名	数据类型	描述
id	BIGINT	主键，由系统自动生成，用于区分OUTLINE的编号。
sql_id	BIGINT	OUTLINE对应的SQL_ID。
hints	TEXT	OUTLINE对应的HINT。
state	CHARACTER(1)	OUTLINE当前状态，取值如下： Y：开启。 N：关闭。
depends_rels	TEXT[]	使用当前OUTLINE生成的计划中依赖的所有`relation`名称。
query_string	TEXT	创建OUTLINE的SQL。
create_user	TEXT	创建OUTLINE的用户。
create_time	TIMESTAMP WITHOUT TIME ZONE	创建OUTLINE的时间。
total_hints	TEXT	当前SQL_ID需要添加的HINT信息，当多个OUTLINE拥有相同SQL_ID时，按照ID顺序聚合。
calls	BIGINT	当前OUTLINE已被命中且使用的次数。

开启或关闭目标OUTLINE

创建OUTLINE后，可使用以下函数选择是否启用该OUTLINE。

hint_plan.enable_outline：用于开启OUTLINE，输入OUTLINE对应id。
hint_plan.disable_outline：用于关闭OUTLINE，输入OUTLINE对应id。

示例

--- 查询数据库内的OUTLINE id
SELECT * FROM hint_plan.outlines_status;

--- 开启OUTLINE
CALL hint_plan.enable_outline(1);

--- 关闭OUTLINE
CALL hint_plan.disable_outline(1);

删除OUTLINE

对于不再使用的OUTLINE，可以使用hint_plan.del_outline函数来删除目标OUTLINE。在输入对应的OUTLINE ID后，将删除相应的OUTLINE。

示例

--- 查询数据库内的OUTLINE id
SELECT * FROM hint_plan.outlines_status;

--- 删除目标OUTLINE
CALL hint_plan.del_outline(1);

完整示例

开启OUTLINE功能。具体操作，请参见开启OUTLINE功能。

基础测试数据准备。

CREATE TABLE t(a int,b int,PRIMARY KEY(a));
CREATE INDEX ON t(b);
INSERT INTO t SELECT i,i FROM generate_series(1,100000)i;
ANALYZE t;

优化器认为主键索引与b列索引的成本相当，因此其计划具有一定的随机性。

EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

返回结果如下：

          QUERY PLAN
-------------------------------
 Index Scan using t_b_idx on t
   Index Cond: (b = 1)
   Filter: (a = 1)
(3 rows)

使用HINT干预优化器，使执行计划选择主键索引，并希望使用OUTLINE干预计划，使得SQL计划不变。
1. 使用HINT干预优化器，使执行计划选择主键索引。
```
EXPLAIN (costs off) /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;
```
  返回结果如下：
```
          QUERY PLAN
------------------------------
 Index Scan using t_pkey on t
   Index Cond: (a = 1)
   Filter: (b = 1)
(3 rows)
```
2. 为该SQL创建OUTLINE，需要注意SQL文本和应用程序的模板要一致。普通的参数、常量、HINT、空格以及注释不影响匹配，额外的::类型转换、多指定Table的Schema、改变表名大小写、改变列名大小写等将影响匹配，详细的匹配规则请参见SQL_ID匹配标准。
```
CALL hint_plan.create_outline($$/*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;$$);
```
  说明
  如使用DMS连接集群执行以上语句出现类似ERROR: invalid transaction termination报错，可使用其他客户端操作，例如psql等，详情请参见连接数据库集群。
  再次验证目标SQL，将使用期望的主键索引：
```
EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;
```
  返回结果如下：
```
          QUERY PLAN
------------------------------
 Index Scan using t_pkey on t
   Index Cond: (a = 1)
   Filter: (b = 1)
(3 rows)
```
  使用其他参数、多空格、多注释等与模板一致的SQL，不影响OUTLINE的匹配，依旧使用主键索引。
```
EXPLAIN (costs off) SELECT * -- comment
FROM t
WHERE b = 2 AND a = 4;
```
  返回结果如下：
```
          QUERY PLAN
------------------------------
 Index Scan using t_pkey on t
   Index Cond: (a = 4)
   Filter: (b = 2)
(3 rows)
```

通过hint_plan.outlines_status视图查看所有OUTLINE状态。

SELECT * FROM hint_plan.outlines_status;

返回结果如下：

 id |        sql_id        |        hints        | state | depends_rels |                           query_string                           | create_user |        create_time         |     total_hints     | calls
----+----------------------+---------------------+-------+--------------+------------------------------------------------------------------+-------------+----------------------------+---------------------+-------
  1 | -3220256307655713529 | IndexScan(t t_pkey) | Y     | {public.t}   | /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1; | postgres    | 2024-11-11 11:24:44.063143 | IndexScan(t t_pkey) |     2
(1 row)

您可按需关闭或删除不再使用的OUTLINE。

关闭id为1的OUTLINE。
```
CALL hint_plan.disable_outline(1);
```
删除id为1的OUTLINE。
```
CALL hint_plan.del_outline(1);
```

关闭或删除相应OUTLINE后计划将变回原样。

EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

返回结果如下：

          QUERY PLAN
-------------------------------
 Index Scan using t_b_idx on t
   Index Cond: (b = 1)
   Filter: (a = 1)
(3 rows)