Skip to content

Latest commit

 

History

History
188 lines (126 loc) · 9.58 KB

sql-non-prepared-plan-cache.md

File metadata and controls

188 lines (126 loc) · 9.58 KB
title summary
非 Prepare 语句执行计划缓存
介绍 TiDB 中非 Prepare 语句执行计划缓存的原理、使用方法及示例。

非 Prepare 语句执行计划缓存

对于某些非 PREPARE 语句,TiDB 可以像 Prepare/Execute 语句一样支持执行计划缓存。这可以让这些语句跳过优化器阶段,以提升性能。

开启非 Prepare 语句执行计划缓存可能会带来额外的内存和 CPU 开销,并不一定适用于所有场景。建议参考性能收益内存监控章节,根据具体的使用情况决定是否开启该功能。

原理

Non-Prepared Plan Cache 为会话级别,并且与 Prepared Plan Cache 共用一个缓存。Non-Prepared Plan Cache 功能的基本原理如下:

  1. 开启 Non-Prepared Plan Cache 后,TiDB 首先根据 AST(抽象语法树)对查询进行参数化。例如,将 SELECT * FROM t WHERE b < 10 AND a = 1 参数化为 SELECT * FROM t WHERE b < ? and a = ?
  2. 然后,使用参数化后的查询在 Plan Cache 中查找。
  3. 如果能找到可以直接复用的计划,则直接使用,并跳过整个优化过程。
  4. 否则,继续进行查询优化,并在最后将生成的计划放回到缓存中,以便下次复用。

使用方法

目前,你可以通过 tidb_enable_non_prepared_plan_cache 开启或关闭 Non-Prepared Plan Cache。同时,你还可以通过 tidb_session_plan_cache_size 来控制 Plan Cache 的大小。当缓存的计划数超过 tidb_session_plan_cache_size 时,TiDB 会使用 LRU (Least Recently Used) 策略进行逐出。

从 v7.1.0 开始,你可以通过变量 tidb_plan_cache_max_plan_size 来设置可以缓存的计划的最大大小,默认为 2 MB。超过该值的执行计划将不会被缓存到 Plan Cache 中。

注意:

tidb_session_plan_cache_size 定义的内存会被 Prepared 和 Non-Prepared Plan Cache 共享。如果集群已经开启 Prepared Plan Cache,那么开启 Non-Prepared Plan Cache 可能降低原先 Prepared Plan Cache 的命中率。

示例

下面是一个使用示例:

  1. 创建用于测试的表 t

    CREATE TABLE t (a INT, b INT, KEY(b));
  2. 开启 Non-Prepared Plan Cache:

    SET tidb_enable_non_prepared_plan_cache = ON;
  3. 依次执行以下查询:

    SELECT * FROM t WHERE b < 10 AND a = 1;
    SELECT * FROM t WHERE b < 5 AND a = 2;
  4. 查看第二个查询语句是否命中缓存:

    SELECT @@last_plan_from_cache;

    输出结果中 last_plan_from_cache 的值为 1,表示第二次执行的查询计划来自于缓存:

    +------------------------+
    | @@last_plan_from_cache |
    +------------------------+
    |                      1 |
    +------------------------+
    1 row in set (0.00 sec)

限制

缓存限制

TiDB 对参数化后形式相同的查询,只能缓存一个计划。例如,对于 SELECT * FROM t WHERE a < 1SELECT * FROM t WHERE a < 100000 这两个查询语句,由于参数化后的形式相同,均为 SELECT * FROM t WHERE a < ?,因此它们会共用一个计划。

如果由此产生性能问题,可以使用 ignore_plan_cache() Hint 忽略计划缓存中的计划,让优化器每次重新为 SQL 生成执行计划。如果无法修改 SQL,可以通过创建 binding 来解决,例如 CREATE BINDING FOR SELECT ... USING SELECT /*+ ignore_plan_cache() */ ...

使用限制

由于上述风险以及执行计划缓存只在简单查询上有明显收益(如果查询较为复杂,查询本身执行时间较长,使用执行计划缓存收益不大),TiDB 目前对 Non-Prepared Plan Cache 的生效范围有严格的限制。具体限制如下:

  • Prepared Plan Cache 不支持的查询或者计划,Non-Prepared Plan Cache 也不支持。
  • 不支持包含 WindowHaving 的查询。
  • 不支持包含三张表及以上 Join 或子查询的查询。
  • 不支持 ORDER BY 或者 GROUP BY 后直接带数字或者表达式的查询,如 ORDER BY 1GROUP BY a+1。仅支持 ORDER BY column_nameGROUP BY column_name
  • 不支持过滤条件中包含 JSONENUMSETBIT 类型的列的查询,例如 SELECT * FROM t WHERE json_col = '{}'
  • 不支持过滤条件中出现 NULL 值的查询,例如 SELECT * FROM t WHERE a is NULL
  • 默认不支持参数化后参数个数超过 200 个的查询,例如 SELECT * FROM t WHERE a in (1, 2, 3, ... 201)。从 v7.3.0 开始,你可以通过在 tidb_opt_fix_control 系统变量中设置 44823 这个 Fix 来调整该限制。
  • 不支持访问虚拟列、临时表、视图、或内存表的查询,例如 SELECT * FROM INFORMATION_SCHEMA.COLUMNS,其中 COLUMNS 为 TiDB 内存表。
  • 不支持带有 Hint 或有 Binding 的查询。
  • 默认不支持 DML 语句或包含 FOR UPDATE 的查询语句。若要启用支持,你可以执行 SET tidb_enable_non_prepared_plan_cache_for_dml = ON

开启此功能后,优化器会对查询进行快速判断,如果不满足 Non-Prepared Plan Cache 的支持条件,则会走正常的优化流程。

性能收益

在内部测试中,开启 Non-Prepared Plan Cache 功能在大多数 TP 场景下可以获得显著的性能收益。例如:TPCC 测试中性能提升约 4%,一些 Bank 负载上提升超过 10%,在 Sysbench RangeScan 上提升达到 15%。

但是这个功能本身也会带来额外的 CPU 和内存开销,包括判断查询是否支持、对查询进行参数化、在 Plan Cache 中进行搜索等。如果负载中的多数查询无法被 Cache 命中,开启此功能反而可能影响性能。

此时,你需要观察 Grafana 监控中的 Queries Using Plan Cache OPS 面板中的 non-prepared 指标和 Plan Cache Miss OPS 面板中的 non-prepared-unsupported 指标。如果大多数查询都无法被支持,只有少部分查询能命中 Plan Cache,此时你可以关闭此功能。

non-prepared-unsupported

诊断

开启 Non-Prepared Plan Cache 后,可以使用 EXPLAIN FORMAT='plan_cache' SELECT ... 语句验证查询是否能够命中缓存。对于无法命中缓存的查询,系统会通过 warning 的方式返回无法命中的原因。

需要注意的是,如果不加 FORMAT='plan_cache',则 EXPLAIN 语句永远不会命中缓存。

执行下面 EXPLAIN FORMAT='plan_cache' 语句,查看查询是否能够命中:

EXPLAIN FORMAT='plan_cache' SELECT * FROM (SELECT a+1 FROM t) t;

输出结果示例如下:

3 rows in set, 1 warning (0.00 sec)

通过 SHOW warnings; 查看无法命中缓存的查询信息:

SHOW warnings;

输出结果示例如下:

+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1105 | skip non-prepared plan-cache: queries that have sub-queries are not supported |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到在上述例子中,由于 Non-Prepared Plan Cache 不支持 + 操作,所以无法命中缓存。

监控

开启 Non-Prepared Plan Cache 后,可以在以下几个面板中查看缓存的内存使用情况、缓存中计划的个数、缓存命中的情况等信息。

non-prepared-plan-cache

statements_summary 表和慢查询日志也会体现缓存的命中情况。下面是查看 statements_summary 表中缓存命中情况的例子:

  1. 创建表 t

    CREATE TABLE t (a int);
  2. 打开 Non-Prepared Plan Cache 开关:

    SET @@tidb_enable_non_prepared_plan_cache = ON;
  3. 依次执行以下三个查询:

    SELECT * FROM t WHERE a<1;
    SELECT * FROM t WHERE a<2;
    SELECT * FROM t WHERE a<3;
  4. 查询 statements_summary 表查看查询命中缓存的情况:

    SELECT digest_text, query_sample_text, exec_count, plan_in_cache, plan_cache_hits FROM INFORMATION_SCHEMA.STATEMENTS_SUMMARY WHERE query_sample_text LIKE '%SELECT * FROM %';

    输出结果如下:

    +---------------------------------+------------------------------------------+------------+---------------+-----------------+
    | digest_text                     | query_sample_text                        | exec_count | plan_in_cache | plan_cache_hits |
    +---------------------------------+------------------------------------------+------------+---------------+-----------------+
    | SELECT * FROM `t` WHERE `a` < ? | SELECT * FROM t WHERE a<1                |          3 |             1 |               2 |
    +---------------------------------+------------------------------------------+------------+---------------+-----------------+
    1 row in set (0.01 sec)

    可以看到,查询执行了三次且命中缓存两次。