文档

计划缓存 (Auto Plan Cache)

更新时间:

您可以使用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

示例

  1. 数据准备。

    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);
  2. 将Auto Plan Cache模式设置为DEMAND

    您可以通过以下两种方式来设置Auto Plan Cache模式。

    • PolarDB控制台参数配置页面将loose_plan_cache_type参数设置为DEMAND。设置完成后,断开当前连接并重新连接数据库。

    • 在当前数据库连接中执行以下命令,将当前会话中的plan_cache_type参数设置为demand

      SET plan_cache_type=demand;
  3. 执行以下命令,将指定SQL语句的执行计划缓存到Plan Cache中。

    CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
  4. 执行查询语句。

    SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10;
  5. 查询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参数配置为OFFAUTOENFORCE时的性能。测试结果如下:

  • PS协议下的性能测试结果如下:PS协议下的查询性能

  • 非PS协议下的性能测试结果如下:非PS协议下的查询性能

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