每天5分钟PG聊通透第21期,为什么要用绑定变量?
digoal约 8 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
每天5分钟PG聊通透第21期,为什么要用绑定变量?
背景
问题说明(现象、环境) 分析原因 结论和解决办法
链接、驱动、SQL
21、为什么要用绑定变量?
https://www.bilibili.com/video/BV1qL4y1b7xp/
1、SQL的执行过程:
https://www.postgresql.org/developer/backend/
parser rewrite generate paths generate plan execute plan
2、如果使用绑定变量, 那么可以跳过parser, rewrite, generate paths, generate plan. 执行过程变成bind parameter, execute.
有兴趣的同学可以通过perf去观察pgbench压测时采用simple模式和prepared模式的区别.
3、prepare例子
服务端prepare例子:
https://www.postgresql.org/docs/14/sql-prepare.html
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00); PREPARE usrrptplan (int) AS
SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
AND l.date = $2;
EXECUTE usrrptplan(1, current_date);
客户端prepare请参考对应驱动, 例如jdbc:
https://jdbc.postgresql.org/documentation/head/index.html
https://jdbc.postgresql.org/documentation/publicapi/index.html
4、prepare可以减轻数据库SQL解析、rewrite、生成执行计划的开销, 对于高并发、短平快的OLTP类业务, 建议使用.
性能例子
IT-C02YW2EFLVDL:~ digoal$ pgbench -M prepared -n -r -P 1 -c 8 -j 8 -T 120 -S
pgbench (14.1)
progress: 1.0 s, 62676.4 tps, lat 0.126 ms stddev 0.096
progress: 2.0 s, 65493.0 tps, lat 0.122 ms stddev 0.088
progress: 3.0 s, 62853.8 tps, lat 0.127 ms stddev 0.202
progress: 4.0 s, 65102.8 tps, lat 0.122 ms stddev 0.093
progress: 5.0 s, 67121.9 tps, lat 0.119 ms stddev 0.114
progress: 6.0 s, 72911.0 tps, lat 0.109 ms stddev 0.078
progress: 7.0 s, 69617.0 tps, lat 0.114 ms stddev 0.083
progress: 8.0 s, 66310.4 tps, lat 0.120 ms stddev 0.178
progress: 9.0 s, 67849.8 tps, lat 0.117 ms stddev 0.092
progress: 10.0 s, 77373.0 tps, lat 0.103 ms stddev 0.085
progress: 11.0 s, 70912.5 tps, lat 0.112 ms stddev 0.421
progress: 12.0 s, 74559.4 tps, lat 0.107 ms stddev 0.410 IT-C02YW2EFLVDL:~ digoal$ pgbench -M simple -n -r -P 1 -c 8 -j 8 -T 120 -S
pgbench (14.1)
progress: 1.0 s, 49513.3 tps, lat 0.160 ms stddev 0.110
progress: 2.0 s, 49571.4 tps, lat 0.161 ms stddev 0.101
progress: 3.0 s, 47109.3 tps, lat 0.169 ms stddev 0.359
progress: 4.0 s, 45408.0 tps, lat 0.176 ms stddev 0.252
progress: 5.0 s, 47226.3 tps, lat 0.169 ms stddev 0.375
progress: 6.0 s, 48296.1 tps, lat 0.165 ms stddev 0.847
progress: 7.0 s, 44865.8 tps, lat 0.178 ms stddev 0.504
progress: 8.0 s, 40258.4 tps, lat 0.198 ms stddev 0.797
progress: 9.0 s, 42694.7 tps, lat 0.187 ms stddev 0.542
progress: 10.0 s, 44505.2 tps, lat 0.179 ms stddev 0.789
progress: 11.0 s, 47628.3 tps, lat 0.168 ms stddev 0.798
5、同时prepare还有一个好处: 可以规避SQL注入风险.
《PostgreSQL 转义、UNICODE、与SQL注入》
select * from a where id = ? ; 注入:
select * from a where id = 1 or 1=1 ;
使用prepare则不会出现此类风险, 因为?这儿会当成整个value放进去, 而不是拆成另一个or条件. 所以上面这个例子会因为传入值不是int类型而直接报错.
如果我们使用了绑定变量, 那么执行计划是不是永久不变呢? 不是!
最典型的疑问:
SQL相关的表的记录变了(数据发生了新增、更新、删除操作)plan会不会动态变化? 首先要保证统计信息更新及时, 由autovacuum触发autoanalyze来实现. 例如数据内容的变化超过一个比例(autovacuum_analyze_scale_factor)时, 自动触发analyze. SQL的输入条件变了plan会不会变化? 这个会由算法保证, 如果有必要变更执行计划, 则会走到custom plan的流程中. 算法如下:
prepare的执行计划选择算法详解:
《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》 《PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜》
在生成generic plan(缓存的执行计划)之前, 会使用5次custom plan, 这5次的custom plan每次都会经过generate plan的过程, 并且保留2个值:
1、custom plan avg cost 2、custom plan 次数 第5次custom plan之后, 在每次调用prepare时, 在execute前, 在bind parameter后, 需要先使用传入参数, 通过generic plan计算cost. 如果计算得到的 “cost > custom plan avg cost” , 那么就会重新触发custom plan. 同时更新"custom plan avg cost" 以及 "custom plan 次数"
什么情况下不建议用绑定变量呢?
olap场景, 因为olap业务SQL执行时长本身就很长, 执行计划的耗时占比非常低, 使用generic plan极端情况下还是会出现数据倾斜导致错误的执行计划.
如何强制使用绑定变量或者强制不使用绑定变量?
在olap系统中, 经常使用plpgsql这种存储过程处理逻辑, plpgsql里面就会自动使用generic plan, 想用custom plan怎么办?
1、execute 语法, 每次都会使用custom plan
2、设置plan_cache_mode参数
plan_cache_mode = force_custom_plan # auto, force_generic_plan or force_custom_plan
《PostgreSQL 11 preview - 增加强制custom plan GUC开关(plancache_mode)，对付倾斜》
今日荐书
彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: