执行计划缓存，Prepared Statement 性能跃升的秘密

2023-06-02
广东
本文字数：2587 字
阅读完需：约 8 分钟

本文分享自华为云社区《执行计划缓存，Prepared Statement性能跃升的秘密》，作者： GaussDB 数据库。

引言

在数据库系统中，SQL（Structured Query Language）语句输入到系统后，一般要经历：词法语法解析(parse)、重写（resolve）、优化（optimize）、执行(execute)的过程。词法语法分析，重写和优化，这三个阶段会生成 SQL 语句的执行计划 (plan)。当 SQL 语句存在多种执行计划的时候，优化器会从这许多的执行计划中挑选出一个它认为最优的（通常是占用系统资源最少的，包括 CPU 以及 IO 等）作为最终的执行计划供执行器执行。生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。

图 1：SQL 语句执行

Prepared Statement 是将 SQL 语句中的值用占位符替代，可以视为将 SQL 语句模板化或者说参数化。当执行 PREPARE 语句时，传统 MySQL 将对指定的语句进行词法语法解析和重写，如上图①②。该阶段称为预编译阶段。Prepared Statement 的优势在于一次编译、多次运行，省去了预编译阶段需要的时间。随后发出 EXECUTE 命令时，MySQL 将对编译阶段生成的结构执行优化，即上图的③，生成对应的执行计划并执行，把输出结果返回到客户端。例如：

PREPARE stmt FROM ‘SELECT * FROM t WHERE t.a = ?’;SET @var = 2;EXECUTE stmt USING @var;

复制代码

传统 MySQL 的 Prepared Statement 只会节省 SQL 语句的解析及重写过程需要的时间，但是对于一条 SQL 语句，如文章开头所述，优化 SQL 语句并生成执行计划需要耗费大量的资源以及时间。如果能将该 Prepared Statement 语句对应的最终执行计划进行缓存，当执行 EXECUTE 语句的时候，就可以直接使用已缓存的执行计划，从而就可以跳过 SQL 语句生成执行计划的整个过程，进而可以提高语句的执行性能。为此，GaussDB(for MySQL) 提供了 Prepared Statement 执行计划缓存特性。

接下来一起看一下 GaussDB(for MySQL)是如何对执行计划进行缓存并加速 Prepared Statement 性能的。

执行计划缓存工作原理

GaussDB(for MySQL)对 Prepared Statement 执行计划进行缓存的基本原理和流程如下图所示：

响应 EXECUTE，执行查询。
通过 is_plan_cached 过程来查看当前 Query 的执行计划是否已经被缓存。
如果已经被缓存，优化器将对当前的 Query 缓存的执行计划进行初始化，根据执行计划的上下文还原执行计划，然后利用还原的执行计划继续执行。
如果没有被缓存，在执行完 Query 优化生成执行计划之后，通过 is_query_cachable 过程验证当前执行计划是否可以被缓存。
如果满足缓存条件，执行计划将会被缓存（调用 cache_JOIN_plan），以便以后的 EXECUTE 语句可以利用该缓存的计划进行执行。
如果不能缓存，通过传统的 MySQL 执行流程（优化，生成执行计划然后执行）执行 EXECUTE 语句。

执行计划缓存管理

执行计划缓存功能开关

GaussDB(for MySQL)引入了一个新的系统参数 rds_plan_cache 来开关 Prepared Statement 执行计划缓存功能。

rds_plan_cache：该参数可以设置为 ON/OFF。分别代表开启和关闭执行计划缓存。该参数是 Session/Global 级别的参数。

查看执行计划缓存情况

GaussDB(for MySQL)提供了两个状态变量供用户查看或者验证 Prepared Statement 执行计划是否被缓存，以及在执行时是否命中了缓存的执行计划。

cached_plan_count：显示有多少个 Prepared Statement 缓存了执行计划。这是一个 Global 级别的状态变量。
cached_plan_hits：显示 EXECUTE 执行过程中命中了缓存的执行计划的次数。这是一个 Session/Global 状态。

下面举例来看一下 Prepared Statement 是如何利用了执行计划缓存特性的：

SET @a = 'two';SET @b = 3;PREPARE stmt FROM "SELECT * FROM t1 WHERE b = ? AND c = ?";EXECUTE stmt USING @a,@b;

复制代码

执行结果如下：

a b c6 two 3

复制代码

再次执行 Prepared Statement：

EXECUTE stmt USING @a,@b;a b c6 two 3

复制代码

第三次执行 Prepared Statement：

execute stmt using @a,@b;a b c6 two 3

复制代码

通过 cached_plan_count 和 cached_plan_hits 查看 stmt 执行计划是否被缓存，以及在执行时是否命中了缓存的执行计划。

SHOW SESSION STATUS LIKE "cached_plan%";

复制代码

显示结果如下：

Variable_name ValueCached_plan_count 1Cached_plan_hits 2

复制代码

从显示结果可以看出，第一次执行 EXECUTE 语句的时候，Prepared Statement 对执行计划进行了缓存，即可以看到 Cached_plan_count 为 1；之后执行两次 EXECUTE 语句，都命中了执行计划缓存，所以可以看到 Cached_plan_hits 变成了 2。

缓存的执行计划如何失效

为了保持当前缓存的执行计划是尽可能最优的，GaussDB(for MySQL)定义了如下规则来对当前缓存的计划进行失效，并重新生成执行计划：

执行计划相关表的记录数更改超过总记录数的 20%。
这意味着当前表的记录数如果插入/删除超过 20%的记录，当前缓存计划将失效并在优化后重新缓存。注：记录数是根据统计数据估计的。所以最好先对表进行 Analyze。
表定义进行了更改。
例如，执行计划相关表上进行的 DDL 将导致缓存计划无效，并在优化后重新缓存。
如果系统变量 Optimizer_switch 中影响执行计划生成的选项值进行了更改，则缓存的计划将失效，并在优化后重新缓存。
系统字符集发生变化，与缓存的计划不同时，将导致缓存计划失效，并在优化后重新缓存。

执行计划缓存功能当前的一些限制

GaussDB(for MySQL)的 Prepared Statement 的目的是节约查询的优化时间。对于通过并行查询优化的大查询，也就是数据量相对庞大的查询，这些查询大部分的执行时间是集中在执行计划的执行阶段。对于该类型的查询，优化时间相比执行时间而言可以忽略不计，所以 GaussDB(for MySQL)没有对并行查询计划进行缓存。另外，GaussDB(for MySQL)对于 Prepared statement 缓存执行计划的能力还在逐步增强中，比如当前只支持单表的 SELECT 查询语句，暂时还不支持 UNION 操作。