PostgreSQL — Prepared Statement 预编译

浅浅的花香味﹌ 2023-10-05 11:29 20阅读 0赞

## 目录 ##

#### 文章目录 ####

*  目录
 *  PREPARE 指令
 *  EXECUTE 指令
 *  DEALLOCATE 指令
 *  EXPLAIN 指令
 *  ANALYZE 指令
 *  ERROR,0A000,"cached plan must not change result type"
 *   *  模拟异常
     *  解决方法
 *  参考文档

## PREPARE 指令 ##

PostgreSQL 的 PREPARE 指令用于创建一个预备（Prepare）语句，本质是一段待执行的、且进行了预编译的 SQL 语句，所以也称之为 “预编译语句”。在 RDBMS 中，预备语句常被用于优化性能。

当我们需要在一个 Session 中执行大量近似的 SQL 语句时，使用预备语句可以获得最大的性能提升。如果该语句很复杂（难于规划或重写），例如，如果查询涉及很多表的连接（Join）或者要求应用多个不同的规则，那么性能差异将会特别明显。反之，如果 SQL 语句相对比较容易规划和重写，但是执行起来开销相对较大，那么预备语句的性能优势就不那么显著了。

预备语句进行性能优化的原理就是 “预编译”，即：在执行实际的 SQL 语句之前就规划好了后续的数据写入流程，尤其当 SQL 语句非常近似时，预备语句就可以避免重复性的解析工作（类似于缓存预热），继而提升 SQL 语句执行的效率。

在 PostgreSQL 中执行 PREPARE 指令时，其内含定义的的 SQL 预备语句会被预编译。当后续再发出一个 EXECUTE 指令时，预备语句就可以快速的被执行。

> PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.

创建一个预备语句：

PREPARE name [ ( data_type [, ...] ) ] AS statement

*  **name**：指定预备语句的名称。它在同一个会话中必须保持唯一，也作为后续 EXECUTE（执行）、DEALLOCATE（清除）预备语句的句柄。
 *  **data\_type**：预备语句可以接受参数列表，data\_type 则用于指定参数的数据类型。如果一个特定参数的数据类型没有被指定或者被指定为 unknown 类型，那么该参数的类型将从当前的运行环境中推理得出。
 *  **statement**：即 SQL 语句的定义，可以是任何 SELECT、INSERT、UPDATE、DELETE 或者 VALUES 语句。

示例：

# 创建测试用表。
    create table test(a int, b text, c bool, d numeric);
    
    # 为一个 INSERT 语句创建一个预备语句，然后执行它。
    PREPARE testplan(int, text, bool, numeric) AS INSERT INTO test VALUES($1, $2, $3, $4);
    EXECUTE testplan(1, 'Hunter Valley', 't', 200.00);
    
    # 为一个 SELECT 语句创建一个预备语句，然后执行它。
    PREPARE selectplan(int) AS SELECT * FROM test WHERE a=$1;
    EXECUTE selectplan(1);

注意，PostgreSQL 的预备语句仅在一个 Session 中有效。即：当 Session 终结时，预备语句的定义也会被自动清除，因此在重新使用之前必须重新建立它。这也意味着一个预备语句不能被多个数据库客户端（不同的 Session）同时使用。当然，在同一个 Session 中的预备语句也可以使用 DEALLOCATE 指令手工清除。

简而言之，预备语句在为各个客户端服务的各个子进程之间，无法共享已经分析好的执行计划。

另外。尽管预备语句的主要工作是为了避免对 SQL 语句进行重复解析和规划，但是如果在重复使用同一条预备语句的间隙之间对数据库表结构执行了 DDL 修改，那么就会强制地在后续的预备语句执行中，再次地进行重新分析和规划。同样的，如果 search\_path 值发生变化的话，也将使用新的 search\_path 重新解析并规划该预备语句。

## EXECUTE 指令 ##

EXECUTE 指令用来执行一个之前准备好的预备语句。由于预备语句只在 Session 期间有效，所以该预备语句必须在当前 Session 中更早的执行 PREPARE 指令创建。

格式：

EXECUTE name [ ( parameter [, ...] ) ]

*  **name**：执行要执行的预备语句名称。
 *  **parameter**：传入预备语句的参数值，这必须是一个能得到与该参数数据类型（ 在预备语句创建时决定）兼容的值的表达式。

如果预备语句指定了参数列表，那么 EXECUTE 指令也必须传递一组兼容的参数值，否则会触发错误。注意，与函数不同，预备语句无法基于其参数的类型或者数量重载。

## DEALLOCATE 指令 ##

DEALLOCATE 指令用于显式的释放一个指定的预备语句。如果不显式地释放一个预备语句，那么预备语句也会在 Session 结束时隐式的释放。

格式：

DEALLOCATE [ PREPARE ] { name | ALL }

*  **PREPARE**：这个关键词通过被忽略。
 *  **name**：指定要释放的预备语句名称。
 *  **ALL**：释放所有预备语句。

## EXPLAIN 指令 ##

EXPLAIN 指令查看一个指定预备语句的执行计划。

格式：

EXPLAIN [ ( option [, ...] ) ] statement
    EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
    
    # option 可以是：
    - ANALYZE [ boolean ]
    - VERBOSE [ boolean ]
    - COSTS [ boolean ]
    - BUFFERS [ boolean ]
    - TIMING [ boolean ]
    - FORMAT { TEXT | XML | JSON | YAML }

*  **ANALYZE**：执行命令并显示实际的运行时间和其他统计信息。默认为 FALSE。
 *  **VERBOSE**：显示关于计划的详细信息。特别是：计划树中每个结点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。默认为 FALSE。
 *  **COSTS**：显示包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。默认为 TRUE。
 *  **BUFFERS**：显示包括缓冲区使用的信息。特别是：共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、以及临时块读取和写入的次数。一次命中表示避免了一次读取，因为需要的块已经在缓存中找到了。共享块包含着来自于常规表和索引的数据，本地块包含着来自于临时表和索引的数据，而临时块包含着在排序、哈希、物化计划结点和类似情况中使用的短期工作数据。脏块的数量表示被这个查询改变的之前未被修改块的数量，而写入块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。为一个较高层结点显示的块数包括它的所有子结点所用到的块数。在文本格式中，只会打印非零值。只有当 ANALYZE 也被启用时，这个参数才能使用。默认为 FALSE。
 *  **TIMING**：显示包括实际的启动时间以及在每个结点中花掉的时间。反复读取系统时钟的负荷在某些系统上会显著地拖慢查询，因此在只需要实际的行计数而不是实际时间时，把这个参数设置为 FALSE 可能会有用。即便用这个选项关闭结点层的计时，整个语句的运行时间也总是会被度量。只有当 ANALYZE 也被启用时，这个参数才能使用。默认为 TRUE。
 *  **FORMAT**：指定输出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本输出包含和文本输出格式相同的信息，但是更容易被程序解析。默认为 TEXT。
 *  **boolean**：指定被选中的选项是否应该被打开或关闭。可以写 TRUE、ON 或 1 来启用选项，写 FALSE、OFF 或 0 禁用它。boolean 值也能被忽略，在这种情况下会假定值为 TRUE。
 *  **statement**：你想查看其执行计划的任何 SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS 或者 CREATE MATERIALIZED VIEW AS 语句。

示例：

# 创建查询测试表。
    create table test2(a int);
    insert into test2 select i from generate_series(1, 10000) t(i);
    
    # 有一个具有单个 integer 列和 10000 行的表，要显示在其上的一个简单查询的计划。
    POSTGRES=# EXPLAIN SELECT * FROM test2;
                            QUERY PLAN                         
    -----------------------------------------------------------
     Seq Scan on TEST2  (cost=0.00..145.00 rows=10000 width=4)
    (1 row)
    
    # JSON 格式输出。
    POSTGRES=# EXPLAIN (FORMAT JSON) SELECT * FROM test2;
               QUERY PLAN            
    ---------------------------------
     [                              +
       {                            +
         "Plan": {                  +
           "Node Type": "Seq Scan", +
           "Parallel Aware": false, +
           "Relation Name": "TEST2",+
           "Alias": "TEST2",        +
           "Startup Cost": 0.00,    +
           "Total Cost": 145.00,    +
           "Plan Rows": 10000,      +
           "Plan Width": 4          +
         }                          +
       }                            +
     ]
    (1 row)
    
    # 使用参数 ANALYZE。
    POSTGRES=# EXPLAIN ANALYZE  SELECT * FROM test2 WHERE a=1000;
                                               QUERY PLAN                                            
    -------------------------------------------------------------------------------------------------
     Seq Scan on TEST2  (cost=0.00..170.00 rows=1 width=4) (actual time=0.271..2.354 rows=1 loops=1)
       Filter: (A = 1000)
       Rows Removed by Filter: 9999
     Planning time: 0.045 ms
     Execution time: 2.370 ms
    (5 rows)
    
    # 这里是一个使用 EXPLAIN EXECUTE 显示预备查询的执行计划的例子。
    POSTGRES=# PREPARE test2plan(int) AS SELECT * FROM test2 WHERE a=$1;
    PREPARE
    POSTGRES=# EXPLAIN ANALYZE EXECUTE test2plan(1000);
                                               QUERY PLAN                                            
    -------------------------------------------------------------------------------------------------
     Seq Scan on TEST2  (cost=0.00..170.00 rows=1 width=4) (actual time=0.243..2.359 rows=1 loops=1)
       Filter: (A = 1000)
       Rows Removed by Filter: 9999
     Execution time: 2.400 ms
    (4 rows)

## ANALYZE 指令 ##

ANALYZE 指令用于收集一个数据库中的表的内容的统计信息，并且将结果存储在 sys\_statistic 系统目录中。接下来，查询规划器会使用这些统计信息来帮助确定查询最有效的执行计划。

格式：

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

*  **VERBOSE**：允许显示进度消息。
 *  **table\_name**：指定要分析的表名称（可以是模式限定的）。如果省略，当前数据库中所有常规表（非外部表）都会被分析。
 *  **column\_name**：指定要分析的一个列名称。默认是所有列。

## ERROR,0A000,“cached plan must not change result type” ##

### 模拟异常 ###

1.  创建测试表并插入数据

francs=> create table test_cache (id int4, name character varying(32));
    CREATE TABLE
      
    francs=> insert into test_cache values (1,'a'),(2,'b'),(3,'c');
    INSERT 0 3
      
    francs=> select * from test_cache ;
    id | name
    ----+------
     1 | a
     2 | b
     3 | c
    (3 rows)

创建完表后，接着开启两个会话，顺序按以下操作进行。

1.  会话1：创建 PREPARE SQL

francs=> PREPARE select_1 (character varying) AS
    francs->  select * From test_cache where name=$1;
    PREPARE
      
    francs=> EXECUTE select_1('a');
    id | name
    ----+------
     1 | a
    (1 row)
    备注：正常使用 EXECUTE 语句。

1.  会话2：DDL 更改表结构

1.  再次回到会话1，并执行 EXECUTE 指令：

francs=> EXECUTE select_1('a');
    ERROR: cached plan must not change result type

### 解决方法 ###

由于语句语句的生命周期在一个 Session 周期内，所以最简单的解决方式就是释放 Session 的同时清理预备语句。或者手动的执行 DEALLOCATE 指令进行清除，然后重新生成预备语句。

francs=> DEALLOCATE select_1;
    DEALLOCATE
      
    francs=> PREPARE select_1 (character varying) AS
    francs->  select * From test_cache where name=$1;
    PREPARE
      
    francs=> EXECUTE select_1('a');
    id | name
    ----+------
     1 | a
    (1 row)

## 参考文档 ##

https://postgres.fun/20130724155037.html