前言

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

为了生成更优的执行计划，使用 Hint 来干预优化器行为是一种常见且有效的手段。

然而，直接让数据库管理员在业务 SQL 语句中加上 Hint 会面临诸多挑战：

1. 中间件生成的 SQL：许多应用的 SQL 是通过中间件自动生成的，手动添加 Hint 并不实际且繁琐。
2. 发布流程的复杂性：客户需要在应用程序中手动加 Hint，并且每次调整 Hint 都需要发布新的应用版本，这不仅费时费力，还可能带来额外的风险。
3. HINT 管理的复杂性：在应用程序中加了较多 HINT 后增加了管理成本，数据库管理员难以搞清楚整个系统有多少 HINT。

针对上述困境，PolarDB-PostgreSQL (简称PolarDB-PG) 提供对某类 SQL 自动增加 Hint 的能力。我们将这种功能称为执行计划固定（Statement OUTLINE），简称为 OUTLINE。OUTLINE 允许数据库管理员对特定的 SQL 语句手动固定其执行计划，添加和修改这类 SQL 的 Hint。OUTLINE 提供了以下优势：

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

通过 OUTLINE 功能，数据库管理员可以在不影响业务应用程序的前提下，精细控制 SQL 执行计划，高效解决慢 SQL 问题。

针对PostgreSQL设计的OUTLINE

在 PostgreSQL 中，设计和实现简单好用的OUTLINE并不容易，具体来说，会遇到如下问题：

如何标识一类SQL

在 PostgreSQL 中，有精心设计的PlanCache模块，合理使用PlanCache会使得SQL免去解析和生成计划，是TP业务性能的核心组件。 为了更好的利用PlanCache，在使用PostgreSQL中需要配合使用Prepare Statement，将变化的参数标识出来，下面是一段使用PostgreSQL JDBC Prepare Statement的例子

PreparedStatement ps = con.prepareStatement("SELECT userid,orderid FROM sales WHERE userid = ?");
ps.setInt(1, 666);
ps.execute();
ResultSet rs = ps.getResultSet();
... // handle rs to get result

这样的设计保证了良好的性能，但是给标识SQL带来了很多问题，数据库管理员在发现某类SQL出现性能问题的时候，在系统视图、慢日志等监控中往往只能看到是类似 SELECT userid,orderid FROM sales WHERE userid = 666这样的慢SQL。 创建OUTLINE的时候没有时间也很难搞清楚这类SQL在应用侧和驱动侧的细节，比如:

1. 这条SQL应用程序是否使用了Prepare Statement
2. 这条SQL有几个参数，哪些是参数化的，哪些是非参数化的
3. 这条SQL的参数是什么类型
4. 这条SQL的格式是什么样子，有几个空格，应用侧会不会有特殊的字符比如中文注释妨碍SQL的识别匹配

为了解决这类问题，PolarDB-PostgreSQL 创新性的引入了SQL_ID的概念，将上述匹配和驱动协议侧的细节在内核侧进行屏蔽，归一化一类 SQL为相同的SQL_ID，忽略以下因素对 OUTLINE 匹配的影响

1. 空格、换行和注释
2. SQL 中的参数，包括常量和变量 ($1) ——例如 a = 1、a = $1、a = 2 都会被归一化成一样
3. 关键字的大小写 ——例如 SELECT a、select a、Select a 会被归一化成一样
4. 对于连续的参数，会忽略参数个数的影响 ——例如 a IN (1,2,3) 和 a IN (1,2) 会被归一化成一样

有了SQL_ID，数据库管理员就无需有以上担心，识别慢SQL，测试HINT能解决问题后就可以直接创建OUTLINE固定计划，快速解决慢SQL

怎样保证对系统性能的影响最小

引入OUTLINE势必就是在内核侧对任何SQL请求进行识别和匹配，匹配到了OULINE的SQL就自动增加OUTLINE管理的HINT来干预计划。 所以SQL的请求和匹配会带来一定的性能损失，如果性能损失太大，用了OUTLINE 大量的正常请求也会变慢很多，导致系统出现新的瓶颈。 因此PolarDB-PG在设计和实现OUTLINE的时候进行了大量的性能设计和优化，包括：

1. 对SQL_ID引入缓存，同样的SQL只需要在第一次收到请求的时候计算一次SQL_ID
2. 引入全局共享内存hashtable保存OUTLINE的状态，大大加速长短连接的OUTLINE匹配和搜索性能
3. 引入本地内存的状态缓存，在OUTLINE没有DDL变更的情况下进一步加速OUTLINE的搜索性能

同时，上述的缓存在OUTLINE变化的时候会自动淘汰和失效，对用户侧无感。 经过上述极致的性能设计下，在启用并添加 OUTLINE 后，sysbench 标准压测表明 TPS 和 QPS 仅下降 1% - 2% 左右。 基本可以忽略使用OUTLINE造成的性能影响。

使用方法

使用前提

1. 将内核升级到 v14.13.27.0 版本以上，并在需要使用到 OUTLINE 的数据库内执行以下 SQL，将插件 pg_hint_plan 同步升级到 1.4.1 版本之后：

CREATE EXTENSION IF NOT EXISTS pg_hint_plan;
ALTER EXTENSION pg_hint_plan UPDATE;
select extname, extversion >= '1.4.1' as outline_version_ok from pg_extension where extname = 'pg_hint_plan';
   extname    | outline_version_ok
--------------+--------------------
 pg_hint_plan | t
(1 row)

1. 在控制台上将pg_hint_plan.polar_enable_outline修改为on，该修改不会重启实例

OUTLINE 相关参数

OUTLINE DDL 接口

OUTLINE 监控视图属性

监控视图名称：hint_plan.outlines_status

注意事项

1. SQL_ID 匹配标准——数据库内核会归一化一类 SQL，忽略以下因素对 OUTLINE 匹配的影响

   • a. 空格、换行和注释
   • b. SQL 中的参数，包括常量和变量 ($1) ——例如 a = 1、a = $1、a = 2 都会被归一化成一样
   • c. 关键字的大小写 ——例如 SELECT a、select a、Select a 会被归一化成一样
   • d. 对于连续的参数，会忽略参数个数的影响 ——例如 a IN (1,2,3) 和 a IN (1,2) 会被归一化成一样，但是 a IN (1,2,3) 和 a IN (1) 不一样，因为 IN (1) 不是连续的参数

2. 可以为一类 SQL 创建多个 OUTLINE——它们彼此之间会互相叠加，让这个 SQL 受到多个 hint 的影响。

3. OUTLINE 的优先级比 SQL 文本中自带的 HINT 的优先级更高——也就是对 SQL 创建 OUTLINE 之后，原本 SQL 中带的 hint 会失效，只会受到 OUTLINE 中有的 hint 影响。

4. OUTLINE 和 pg_hint_plan 的 hint_table 功能互斥——OUTLINE 和 hint_table 功能类似，因此当打开 OUTLINE 功能的时候，hint_table 功能会默认失效。

使用示例

在需要创建 OUTLINE 的 DB 中创建并升级插件 pg_hint_plan，确认插件版本升级到 1.4.1 版本之后:

CREATE EXTENSION IF NOT EXISTS pg_hint_plan;
ALTER EXTENSION pg_hint_plan UPDATE;
select extname, extversion >= '1.4.1' as outline_version_ok from pg_extension where extname = 'pg_hint_plan';
   extname    | outline_version_ok
--------------+--------------------
 pg_hint_plan | t
(1 row)

在控制台将 OUTLINE 功能开启（修改 pg_hint_plan.polar_enable_outline 为 on，该修改不会重启实例）,show 参数确认 OUTLINE 为开启状态

show pg_hint_plan.polar_enable_outline;
 pg_hint_plan.polar_enable_outline
-----------------------------------
 on
(1 row)

数据准备

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 计划不变

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)

为该 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;$$);

再次验证 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)

可以查看所有 OUTLINE 对应的状态，注意到calls值展示了OUTLINE的命中情况，发生变化说明OUTLINE已经干预了对应的SQL的计划

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 时候可以把 OUTLINE 删除或者关闭

call hint_plan.disable_outline(1);-- 关闭id为1的OUTLINE
call hint_plan.del_outline(1);-- 删除id为1的OUTLINE

此时计划变回原样

explain (costs off) select * -- comment
from t
where b = 2 and a = 4;
          QUERY PLAN
-------------------------------
 Index Scan using t_b_idx on t
   Index Cond: (b = 2)
   Filter: (a = 4)
(3 rows)

相关资料

Statement OUTLINE用户文档 https://help.aliyun.com/zh/polardb/polardb-for-postgresql/statement-outline