开头还是介绍一下群,如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题,有需求都可以加群群内有各大数据库行业大咖,可以解决你的问题。加群请联系 liuaustin3 ,(共2340人左右 1 + 2 + 3 + 4 +5+6 + 7) (1 2 3 4 5 均没有空位了,请不要在问了谢谢)
最近写技术的东西的确有点少了,尤其PostgreSQL. 报应不爽,开发部的一个开发给我提了一个问题,他问我PostgreSQL 为什么不稳定,一会快一会慢。
我回答,这不可能,不会一会快,一会慢! 不是数据库的问题,应该是操作上有什么问题导致的。
了解问题中,具体的问题描述是,在PG13数据库中,定义了一个函数,在函数里面有一个对表的操作,操作的类型是UPDATE,同样的语句,在函数中运行就速度非常的慢大约需要1秒左右,但如果将语句拿出到外部,基本在100毫秒内就完成了操作。
这里需要解释一下dtCreate是变量,是从函数外部传递进来的变量。
这里外面一一排查问题
1 传递进来的值是一个固定值,在执行中变量被替换成一个固定日期,计算式的左边也是一个系统变量和传递变量后进行计算后的值,左右都是变量。
2 函数传递进来的值的类型一定要与左侧计算公式结果的类型相同,否则就产生隐士转换的问题
解决这类问题方案 1
在函数中将要执行的语句进行变量赋值,通过变量赋值中添加 explain来查看具体在函数中,语句执行的具体执行计划。如下方式
CREATE OR REPLACE FUNCTION my_function(var_value INT)
RETURNS void AS
$$
DECLARE
query_text TEXT;
BEGIN
-- 构建带参数的查询语句,使用 $1 作为参数占位符
query_text := 'EXPLAIN SELECT * FROM your_table WHERE column = $1 AND column2 > $2';
-- 查看执行计划
EXECUTE query_text USING var_value, var_value * 2;
END;
$$
LANGUAGE plpgsql;
对比实际的不传递变量的语句执行的方式与上述的方式执行语句的执行计划是否一致,如果明显的不一致,则问题的原因就找到了。
具体类似的问题的解决方案可以通过,将执行的语句通过execute 包裹,并将变量在SQL 语句中拼接的方式来执行如。
execute 'update tablename set age = 25 where pid = '''||变量'''|| and ....'
的方式来执行语句,这样的方式在函数中执行语句,和在函数外部执行语句的方式是一致的,变量在执行语句前已经固话,是一个定值就不会产生执行计划对于值无法进行判断,而无法进行正确的执行计划的给出。
总结:1 在函数内部,尤其是使用变量时,PostgreSQL 需要在每次调用函数时重新解析和规划 SQL 语句。这会导致函数内部的 UPDATE 语句执行速度较慢
2 当使用 EXECUTE 包裹 UPDATE 语句时,PL/pgSQL 会将其视为动态 SQL,并且可以对动态 SQL 进行特定的优化和缓存,从而提高执行速度。
3 使用 EXECUTE 包裹 UPDATE 语句时,会在动态 SQL 解析过程中生成和缓存一个新的执行计划,从而加快后续执行的速度。
4 使用 EXECUTE 包裹 UPDATE 语句时,变量会在解析过程中直接绑定,从而减少了额外的开销。
更多详细的用法参见官方的信息:https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
置顶文章:
PostgreSQL 14 小版本分析,有那个版本不建议使用
SQL SERVER 2022 针对缓存扫描和Query Store 的进步,可以考虑进行版本升级
PostgreSQL 15 16 小版本更新信息小结 版本更新是不是挤牙膏MySQL 的SQL引擎很差吗?由一个同学提出问题引出的实验
临时工访谈:从国产数据库 到 普罗大众的产品 !与在美国创业软件公司老板对话