PostgreSQL绑定变量

原创 chirpyli All China Database Union 2025-01-08

261

前言

数据库中查询优化是至关重要的，往往对查询语句的执行效率起着决定性的作用。对此，数据库实现了多种查询优化技术，其中一种就是查询重用。即尽可能的利用先前的执行结果，以达到节约查询计算全过程的时间并减少资源消耗的目的。目前查询重用技术主要在如下两个方面：

查询结果重用。在缓存区中分配一块缓冲块，存放该SQL语句文本和最后的结果集，当遇到同样的SQL输入时，可直接把结果返回。节省了执行计划生成时间和查询执行时间，同时减少了查询执行全过程的资源消耗。
执行计划重用。缓存一条查询语句的执行计划及其相应语法树结构。减少了执行计划生成时间和资源消耗。

在OLTP场景中，业务上会遇到非常多SQL语句重复高频执行，且没有数据倾斜，其生成执行计划的时间接近或者高于执行时间的情况。针对这种场景优化器通过执行计划重用技术能够有效避免对同一条SQL语句执行完整优化（在商业数据库Oracle中被称为硬解析），有效降低系统硬解析的数量。从而提高整体的执行效率。目前主流数据库优化器都已实现了执行计划重用技术，作为目前功能最强大的开源数据库PostgreSQL绑定变量实现了这一技术。下面我们就深入PostgreSQL绑定变量进行分析。

执行计划重用优化的思考

我们知道，绑定变量实现优化的核心思想是重用执行计划，节省执行计划生成的时间和资源消耗。这就需要我们将指定SQL语句的执行计划缓存起来，然后再遇到重复的SQL时，可以直接从执行计划缓存中去匹配，匹配到后直接应用缓存的执行计划，就不用再耗费时间和资源去重新生成执行计划。可行的一个思路是将SQL字符串进行Hash映射为一个key，同时生成该SQL的执行计划Plan，插入到执行计划缓存中，这里我们可以用哈希表来进行缓存，表示为：HashTable<sqlkey, Plan>。每当输入一个SQL，就先去执行计划缓存中查找，看有无已缓存的执行计划，有则匹配，应用缓存的执行计划，没有则进入执行计划生成流程。这样设计理论上是没有问题的，但是从整体来讲对实际业务应用意义有限，因为这样设计业务上实际能被优化的SQL语句十分有限（即必须是完全相同的SQL才能被优化）。那么更优的设计是什么呢？

我们需要从重用缓存的执行计划这一点去思考什么类型的语句有可能复用另一个SQL语句的执行计划？也就是说什么样的SQL语句能生成同一执行计划？答案是同一类型的SQL语句，比如只存在某些谓词（条件表达式）不同的SQL语句。举个实际的例子：

select * from test01 where id = 1;
select * from test01 where id = 2;

这两条SQL语句其他部分都相同，只有where子句的条件表达式不同，在没有数据倾斜的情况下，这两条SQL语句极大概率生成的是相同的执行计划，这种情况第2条SQL语句其实就没有必要重新生成执行计划，直接复用第1条SQL的执行计划即可。我们将上述两条SQL语句抽象成select * from test01 where id = $1的形式，把条件表达式中不同的部分抽象出来作为变量，表示为$1，意为位置引用参数。通过这种方式，就可以表示同一类SQL语句。后续如果有大量的同一类型的SQL语句，比如其$1为（3,4,5…）时，都可以复用执行计划，从而节省了大量生成执行计划的时间和资源。

PostgreSQL中可用PREPARE <plan_name> [(args, ...)] AS <query>语法为同一类SQL语句创建执行计划缓存（或者称为通用执行计划general plan），具体的SQL执行语法为EXECUTE <plan_name> [(params, ...)]。比如的上面例句就可应用绑定变量实现重用执行计划，减少硬解析。示例如下：

prepare cacheplan(int) as select * from test01 where id = $1;  -- 生成通用执行计划，缓存执行计划
execute cacheplan(1);  -- 解析为select * from test01 where id = 1;
execute cacheplan(2);  -- 解析为select * from test02 where id = 2;

postgres@postgres=# prepare cacheplan(int) as select * from test01 where id = $1;
PREPARE

postgres@postgres=# select * from pg_prepared_statements;
-[ RECORD 1 ]---+--------------------------------------------------------------
name            | cacheplan
statement       | prepare cacheplan(int) as select * from test01 where id = $1;
prepare_time    | 2022-10-24 14:22:59.380774+08
parameter_types | {integer}
from_sql        | t

gram.y中定义

/*******************************************************************
 *
 *		QUERY:
 *				PREPARE <plan_name> [(args, ...)] AS <query>
 *
 *****************************************************************/

PrepareStmt: PREPARE name prep_type_clause AS PreparableStmt
				{
					PrepareStmt *n = makeNode(PrepareStmt);
					n->name = $2;
					n->argtypes = $3;
					n->query = $5;
					$$ = (Node *) n;
				}
		;

prep_type_clause: '(' type_list ')'			{ $$ = $2; }
				| /* EMPTY */				{ $$ = NIL; }
		;

PreparableStmt:
			SelectStmt
			| InsertStmt
			| UpdateStmt
			| DeleteStmt					/* by default all are $$=$1 */
			| MergeStmt
		;

/******************************************************************
 *
 * EXECUTE <plan_name> [(params, ...)]
 *
 *****************************************************************/
ExecuteStmt: EXECUTE name execute_param_clause
				{
					ExecuteStmt *n = makeNode(ExecuteStmt);
					n->name = $2;
					n->params = $3;
					$$ = (Node *) n;
				}

execute_param_clause: '(' expr_list ')'				{ $$ = $2; }
					| /* EMPTY */					{ $$ = NIL; }
					;

prepare cacheplan(int) as select * from test01 where a = $1;
Prepare语句主流程

main
--> PostmasterMain
    --> ServerLoop
        --> BackendStartup
            --> BackendRun
                --> PostgresMain
                    --> exec_simple_query

exec_simple_query
--> pg_parse_query
--> pg_analyze_and_rewrite
--> pg_plan_queries
--> PortalStart
--> PortalRun
    --> PortalRunUtiliey
        --> standard_ProcessUtility
            --> PrepareQuery
            --> ExecuteQuery
            --> DeallocateQuery

PrepareQuery
--> CreateCachedPlan  // 创建CachedPlanSource
--> parse_analyze_varparams  // 语义分析
    --> parse_variable_parameters
    --> transformTopLevelStmt
        --> transformStmt
            --> transformSelectStmt
                --> transformWhereClause
                    --> transformExpr
                        --> transformParamRef
    --> check_variable_parameters
--> QueryRewrite    // 查询重写
--> CompleteCachedPlan   // 创建plan cache entry
--> StorePreparedStatement // 存到哈希表中
    --> InitQueryHashTable /* Initialize the hash table, if necessary */
        --> hash_create    
    --> hash_search  /* Add entry to hash table */
    --> SaveCachedPlan // save a cached plan permanently
        --> ReleaseGenericPlan
        --> dlist_push_tail

execute cacheplan(1);
Execute主流程

ExecuteQuery
--> FetchPreparedStatement // 哈希表中查找是否有已缓存的执行计划
    --> hash_search
--> CreateExecutorState
--> EvaluateParams   // 获取绑定变量参数，返回ParamListInfo
    --> ExecPrepareExprList
        --> ExecPrepareExpr
            --> expression_planner
                --> eval_const_expressions
            --> ExecInitExpr
    --> makeParamList
--> CreateNewPortal
    --> CreatePortal
--> GetCachedPlan
    --> RevalidateCachedQuery
    --> choose_custom_plan  // choose whether to use custom or generic plan
    --> if (!customplan) // 走generic plan
        --> if (CheckCachedPlan)
                // 直接获取已有的有效generic plan
            else
            --> BuildCachedPlan
                --> pg_plan_queries 
            --> cached_plan_cost
    --> if (customplan)  // 走custom plan
        --> BuildCachedPlan
            --> pg_plan_queries
                --> pg_plan_query
                    --> planner
                        --> standard_planner
                            --> subquery_planner
                                --> preprocess_qual_conditions
                                    --> eval_const_expressions
                            --> create_plan

--> PortalDefineQuery
--> PortalStart
    --> ExecutorStart
        --> InitPlan
--> PortalRun
    --> ExecutorRun
        --> standard_ExecutorRun
            --> ExecutePlan
                --> ExecSeqScan
                    --> ExecScan
                        --> ExecScanFetch
                        --> ExecQual
                            --> ExecInterExpr
                                --> ExecEvalParamExtern
                        --> ExecProject
--> PortalDrop
    --> ExecutorEnd
        --> standard_ExecutorEnd
            --> ExecEndPlan
    --> PortalReleaseCachedPlan  /* drop cached plan reference, if any */

针对数据倾斜导致选择的通用执行计划执行效率低的问题，笔者认为有改进空间，

PostgreSQL绑定变量使用

PostgreSQL & Oracle绑定变量窥视对比

总结

优点：

缺点：
当使用绑定变量时，查询优化器会忽略其具体值，因此其预估的准确性远不如直接使用数值真实。当表的列上存在数据倾斜（表上的数据非均匀分布）时，可能会提供低效的执行计划。

需要注意的是，目标SQL中的绑定变量个数不宜太多，否则可能会导致目标SQL总的执行时间大幅度增加。其增加的时间主要耗费在执行目标SQL时对每一个绑定变量都用其实际的值来替换（这个过程就是所谓的绑定变量值替换），目标SQL的SQL文本中的绑定变量的个数越多，这个替换过程所耗费的时间就越长，总执行时间越长。

Postgres数据库绑定变量

从PostgreSQL12开始新增了plan_cache_mode参数，用来配置优化器是否选择通用执行计划。

plan_cache_mode = auto                 # auto, force_generic_plan or
                                       # force_custom_plan

有3个参数可供选择，分别是auto, force_generic_plan, force_custom_plan。其中默认是auto，即安装算法优化器自己决定采用那个执行计划，而force_generic_plan选项则指定选择通用执行计划，force_custom_plan则不选择通用执行计划，每次新生成执行计划。
plan_cache_mode = auto auto, force_generic_plan, force_custom_plan 默认是auto
具体的，可根据场景需求进行配置，可针对不同的user和database来设置该参数，比如：

alter user lucy set plan_cache_mode to force_generic_plan;
alter database juice set plan_cache_mode to force_custom_plan;

实践总结：

对于OLAP场景，每条SQL执行计划生成时间占总耗时时间占比不大，且每一次请求输入的条件评估选择率时可能差异较大，选择通用执行计划往往不是最优执行计划，此时选择每次生成执行计划往往效率最高，建议配置选项选择force_custom_plan。
对于OLTP场景，建议使用auto。

我们知道业务场景是复杂多变的，而且业务数据又在动态变化，同一SQL前一时刻高效的执行计划，在业务数据发生动态变化后，在后一时刻同样的执行计划并不一定是高效的，这就给数据库优化带了困难，同时也是数据库优化器存在的最大意义。优化器不是尽善尽美的，优化器的实现是作为通用解决方案，针对具体的业务场景，优化器不一定能给出最高效的最优的解决方案，这时候往往需要业务开发人员给与数据库优化器更多的信息，比如更改配置，选择更优的策略或者参数来进行干预，同时开发人员要根据数据库优化器的特点写出针对性的高效SQL。

最后修改时间：2025-05-13 15:41:44

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

PostgreSQL绑定变量

前言

执行计划重用优化的思考

PostgreSQL绑定变量使用

总结

评论