如何使用PolarDB MySQL版的Auto Plan Cache功能缓存SQL语句的执行计划信息

您可以使用PolarDB MySQL版提供的Auto Plan Cache功能，来缓存SQL语句的执行计划信息，以缩短查询优化时间，提升SQL语句的查询性能。本文介绍了Auto Plan Cache功能的背景信息、前提条件、依赖的参数和接口等内容。

背景信息

执行计划的选择需要考虑诸多因素，如统计信息、不同的连接顺序和不同的查询变换等。对于不同的查询语句，其优化时间不同，可能会存在某些SQL语句的查询优化时间在整体执行时间中占比很大的情况。如果这类SQL语句执行的次数较多，就会因为优化时间占比大导致系统负载增加。通过缓存并复用SQL语句的执行计划，可以减少每次执行SQL语句的优化时间，从而提升查询性能，降低数据库负载，提升吞吐能力。

除此之外，还有很多查询语句的查询优化时间占比很小，执行时间受执行计划的影响非常大，SQL语句中不同参数值对应不同的最优执行计划。某些场景下，MySQL会根据参数值从引擎获取实际数据做进一步优化。

上述两类查询一旦固定执行计划，查询响应时间和负载开销不会有明显优化，反而可能使查询性能回退。

为了提升优化时间占比太多的SQL语句的查询性能，降低系统负载，同时避免执行SQL语句时因采用固定执行计划而导致查询性能回退问题。PolarDB MySQL版提供了Auto Plan Cache功能。Auto Plan Cache功能提供了AUTO、DEMAND和ENFORCE三种模式，您可以根据需要将loose_plan_cache_type参数设置为三种模式中的任意一种模式，将SQL语句的执行计划缓存在Plan Cache中，以减少执行查询语句时的优化时间，提升查询性能。当缓存在Plan Cache中的执行计划涉及的表的统计信息发生变化，或对缓存中执行计划引用的表执行了DDL操作时，缓存的执行计划会自动失效。

前提条件

PolarDB集群版本需满足以下条件之一：

PolarDB MySQL版8.0.1版本且修订版本为8.0.1.1.33及以上。
PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.12及以上。

参数说明

您可以在PolarDB控制台上设置下表中的参数，详细操作步骤请参见设置集群参数和节点参数。

参数	说明
loose_plan_cache_type	Auto Plan Cache模式。取值如下： OFF（默认）：关闭Auto Plan Cache功能。 AUTO：自动将满足缓存条件的SQL语句的执行计划缓存在Plan Cache中。说明缓存条件： SQL语句的整体执行时间阈值大于或等于`loose_auto_plan_cache_time_threshold`参数值，且SQL语句的优化时间占整体执行时间阈值的百分比阈值大于或等于`loose_auto_plan_cache_pct_threshold`参数值时，该SQL语句的执行计划会被缓存到Plan Cache中。 DEMAND：将指定SQL语句的执行计划缓存在Plan Cache中。 ENFORCE：强制将所有SQL语句的执行计划缓存在Plan Cache中。
loose_plan_cache_expire_time	Plan Cache中的执行计划超过该时间没有被命中，内存会被回收。单位为秒。取值范围：0~UINT_MAX。默认值为1800。
loose_auto_plan_cache_pct_threshold	优化时间占语句整体执行时间的百分比阈值。取值范围：0~100。默认值为20。
loose_auto_plan_cache_time_threshold	SQL语句整体执行时间阈值。单位为微秒。取值范围：0~ULONG_MAX。默认值为400。
loose_auto_plan_cache_count_threshold	当`loose_plan_cache_type`参数设置为AUTO时，满足缓存条件的SQL语句的执行计划缓存到Plan Cache中的次数阈值。取值范围：0~ULONG_MAX。默认值为512。说明当缓存到Plan Cache中的次数阈值大于或等于`loose_auto_plan_cache_count_threshold`参数值时，缓存中的执行计划才会生效。

接口说明

dbms_sql.add_plan_cache(schema, query)：将指定SQL语句的执行计划缓存到Plan Cache中。
当loose_plan_cache_type参数配置为DEMAND时，您可以通过该内置存储过程将指定SQL语句的执行计划缓存到Plan Cache中。示例如下：
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```
上述语句执行完成后，当您执行的SQL语句满足SELECT * FROM t_for_plan WHERE c1 > ？ AND c1 < ？模板时，该SQL语句的执行计划会被缓存到Plan Cache中。
dbms_sql.display_plan_cache_table()：查看当前Plan Cache中引用的表信息。示例如下：
```
CALL dbms_sql.display_plan_cache_table()\G
```
执行结果如下：
```
*************************** 1. row ***************************
 SCHEMA_NAME: test
  TABLE_NAME: t_for_plan
   REF_COUNT: 1
     VERSION: 0
VERSION_TIME: 2023-03-10 17:21:35.605264
```
对应的参数说明如下：
- SCHEMA_NAME：引用的表所在的SCHEMA名称。
- TABLE_NAME：引用的表名。
- REF_COUNT：引用的表在Plan Cache中被引用的数量。
- VERSION：引用的表在Plan Cache中的版本。
- VERSION_TIME：引用当前版本的表的时间。
dbms_sql.delete_sharing_by_rowid(row_id)：删除指定SQL语句的执行计划。
row_id为存储在mysql.sql_sharing表中的执行计划的行ID值。
示例
1. 执行以下命令，查看缓存中的执行计划信息。
```
SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\G
```
  查询结果如下：
```
*************************** 1. row ***************************
         Id: 1
Schema_name: test
       Type: PLAN_CACHE
Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
```
  从以上查询结果可以看出，row_id值为1。
2. 删除上述查询中的执行计划。
```
CALL dbms_sql.delete_sharing_by_rowid(1);
```

获取Plan Cache中的缓存信息

SQL语句的执行计划存储在SQL Sharing模块中，您可以通过以下SQL语句在INFORMATION_SCHEMA.SQL_SHARING表中查询Plan Cache中的缓存信息。

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

示例

数据准备。

CREATE TABLE t_for_plan AS WITH RECURSIVE t(c1, c2, c3) AS (SELECT 1, 1, 1 UNION ALL SELECT c1+1, c1 % 50, c1 %200 FROM t WHERE c1 < 1000) SELECT c1, c2, c3 FROM t;
CREATE INDEX i_c1_c2 on t_for_plan(c1, c2);

将Auto Plan Cache模式设置为DEMAND。
您可以通过以下两种方式来设置Auto Plan Cache模式。
- 在PolarDB控制台的参数配置页面将loose_plan_cache_type参数设置为DEMAND。设置完成后，断开当前连接并重新连接数据库。
- 在当前数据库连接中执行以下命令，将当前会话中的plan_cache_type参数设置为demand。
```
SET plan_cache_type=demand;
```

执行以下命令，将指定SQL语句的执行计划缓存到Plan Cache中。

CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");

执行查询语句。

SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10;

查询Plan Cache中的缓存信息。

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

查询结果如下：

*************************** 1. row ***************************
       TYPE: SQL
     REF_BY: ["PLAN_CACHE(DEMAND)"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
    PLAN_ID: NULL
       PLAN: NULL
 PLAN_EXTRA: NULL
      EXTRA: {"TRACE_ROW_ID":1}
*************************** 2. row ***************************
       TYPE: PLAN
     REF_BY: ["PLAN_CACHE"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: NULL
    PLAN_ID: 08xftakma6pm6
       PLAN: /*+ INDEX(`t_for_plan`@`select#1` `i_c1_c2`) */
 PLAN_EXTRA: {"access_type":["`t_for_plan`:range"]}
      EXTRA: {"PLAN_CACHE_INFO":{"tables":[`test`.`t_for_plan`], "versions":[0], "hits": 0}}

其中，EXTRA字段的PLAN_CACHE_INFO中会展示引用的表、引用的表的版本和执行计划命中次数。

查询性能

在集群规格为8核32 GB，数据库中已创建25张表，单张表存储400万行数据的场景下进行压测。压测使用的SQL语句为：SELECT id FROM sbtestN WHERE k IN(...)，其中，IN LIST的长度为20。在PS协议和非PS协议下，测试loose_plan_cache_type参数配置为OFF、AUTO和ENFORCE时的性能。测试结果如下：

PS协议下的性能测试结果如下：
非PS协议下的性能测试结果如下：

从以上测试结果可以看出：Auto Plan Cache功能在PS协议和非PS协议下都能够获得50%以上的性能提升。