在dba的日常工作中,时常会收到应用侧的通知“XX帮看看某数据库是不是有问题,SQL跑不动了,好慢之类的等等”。这种一般都是SQL性能类问题,通过执行计划我们将直观的确认SQL性能是否存在隐患,掌握执行计划这项技能就显得尤为重要,下面就一起来学习一下pg的执行计划吧。
首先我们先要搞懂SQL执行计划到底是个什么东东,简单的来讲就是SQL语句在数据库内部从取数据到返回结果集的一个完整过程,通过这个过程,我们可以看到SQL每一步的开销,进而判断SQL响应是否正常.那在pg中执行计划又包含4大类型,如下:
1.控制节点(ControlNode):
Append/Uniq组织多个字表或子查询的执行节点,主要用于union操作.
2.扫描节点(ScanNode):
用于扫描表等对象以获取结果集,比如常见得全表扫描(SeqScan),索引扫描(IndexScan).
3.物化节点(MaterializationNode):
缓存执行结果,等待后续调用,比如用来排序、聚合计算等
4.连接节点(JoinNode):
多表连接时,表连接方式,比如hashjoin,nestedloopjoin ,merge join 等等.
逼逼了这么多,接下来我们还是制作2张表进行实战练习,毕竟实战岀真知。
t_user,用户表,并填充50万行数据信息.
t_cust,cust表,以tid关联t_user表,并填充150万行数据信息.具体如下:
在pg中查看执行计划使用explain命令,语法如下:
EXPLAIN [ ( option[, ...] ) ] statement
EXPLAIN [ ANALYZE ][ VERBOSE ] statement
这里option可选项如下:
ANALYZE [boolean ]
VERBOSE [boolean ]
COSTS [ boolean]
BUFFERS [boolean ]
TIMING [ boolean]
SUMMARY [boolean ]
FORMAT { TEXT |XML | JSON | YAML }
注意EXPLAIN默认不执行语句,只显示估算信息,EXPLAIN ANALYZE会实际执行语句且输出真实消耗信息,当我们诊断DML语句时就要格外小心ANALYZE选项以免影响生产数据。
先来看一个简单查询计划:
这里SeqScan on t_user即上文提及的扫描节点(ScanNode),代表着以全表扫描的方式访问t_user表。圆括号中cost单位毫秒,0.00代表这一步计划的启动开销。12423为这一步计划的总开销.rows为结果行数,width为平均行的字节数。
注意这里都是估算,查看实际执行的信息需要使EXPLAINANALYZE,如下:
其中actual为实际耗时信息,以及最后输出的该语句的总时长。相较于总执行时长60.519ms的开销,主要都消耗在了扫描节点(60.494ms)上,且扫描节点下的Filter和RowsRemoved byFilter输出也提示基本上是99%的过滤性;这一步计划就可以发现存在优化空间,在tid字段上建立索引再来观察执行计划.如下:
可以看到表的扫描节点已经更新成索引扫描(Indexscan using idx_user),sql Execution time从60ms下降到0.08ms,提升明显。
再来看一个带有控制节点的计划:
由于这条语句使用了union操作,所以计划中便出现了Append控制节点(ControlNode),对它的2个扫描子节点进行数据合并.注意这2个子扫描节点由于是同层关系(以->观察),执行顺序将由上至下.
接下来是带有物化节点(MaterializationNode)的计划:
这里的GroupAggregate以及Sort节点便是物化节点。其使用子扫描节点的数据进行排序和分组计算。需要注意由于->存在不同层级,则最里层最先执行,即第一步执行SeqScan返回结果集,第二步处理的是其子项第一步的结果,所以这里就出现了物化节点,第三步同理,处理的是第二步的结果,同样是物化节点。
最后我们在来看2种常见的表连接执行计划:
第一种HashJoin:
既然是表连接,必然会出现连接节点;那图中黄色部分就代表这是一个使用HashJoin算法关联的连接节点。根据由里至外,由上至下规则;则过程如下:
第一步:对t_user进行全表扫描,根据tid< 150000过滤350001行,返回149999行;
第二步:对第一步的结果集进行Hash处理生成HashBuckets;
第三步:对t_cust进行全表扫描,返回结果集1500000行;
第四步:根据HashJoin条件,使用步骤三的结果集与HashBuckets关联取出匹配的行,返回结果集.225044行;
第五步:对第三步hash结果集进行聚合算并最终返回结果集1行。
第二种NestedLoop:
图中黄色部分就代表这是一个使用NestedLoop算法关联的连接节点;具体执行过程如下:
第一步:t_cust.cname字段存在索引,则使用索引扫描返回结果集.rows=3;
第二步:根据Nestedloop算法,使用步骤一的结果集(rows=3),以这3行结果集中的tid(JoinFilter)作为条件循环扫描t_user表3次(单次扫描处理500000行),最终NestedLoop过滤1499999行数据.返回1行结果集(rows=1)在这个计划中可以看到循环体内(Seq Scan on t_user a),扫描的结果集较大,且过滤性非常高,适合在该字段上创建索引。
在t_user.tid字段上创建索引在来观察执行计划:
可以看到仍然是NestedLoop表关联,其中子项扫描节点变化为同层关系;具体过程如下:
第一步:索引扫描t_cust表,返回结果集rows=3;
第二步:根据NestedLoop算法,使用步骤一结果集中的tid循序使用新建的tdx_user_tid索引扫描3次,并最终返回结果集rows=3。
从整个执行时间来看从6271ms缩短到0.151ms,提升非常明显.
从上面2个表关联案例中我们可以发现HashJoin适用于大结果集的表关联,并不关注最终返回结果集,分析型场景较多;而NestedLoop往往最终返回结果集较小,通常都是交易型场景,要求驱动表谓词条件相对精确,缩小结果集以降低循环次数,以及被驱动表的关联条件上需要创建索引,提升循环体内部的扫描效率;总体而言NestedLoop出现问题的情况比较多,需要额外注意。
通过以上几种案例解读,可以发现各种数据库执行计划都大同小异 ,问题的类型以及优化思路也基本类似,本文就到此为止,后续小短文敬请期待。
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/130161.html
摘要:年之前,微信支付业务快速发展,需要一款数据库能够安全高效的支撑微信支付商户系统核心业务,这个重任落在了腾讯数据库团队自研上。由于是用于微信支付的核心数据库,腾讯被定位为安全高效,稳定,可靠的数据库集群。 欢迎大家前往腾讯云+社区,获取更多腾讯海量技术实践干货哦~ 本文由李跃森发表于云+社区专栏李跃森,腾讯云PostgreSQL首席架构师,腾讯数据库团队架构师,负责微信支付商户系统核心数...
摘要:收集有关数据库中表的内容的统计信息。预计的该规划节点的行平均宽度单位字节。其中上层节点的开销将包括其所有子节点的开销。一般而言,顶层的行预计数量会更接近于查询实际返回的行数。 问题场景 有model Account,SQLAlchemy 查询语句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
摘要:收集有关数据库中表的内容的统计信息。预计的该规划节点的行平均宽度单位字节。其中上层节点的开销将包括其所有子节点的开销。一般而言,顶层的行预计数量会更接近于查询实际返回的行数。 问题场景 有model Account,SQLAlchemy 查询语句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
阅读 1235·2023-01-11 13:20
阅读 1542·2023-01-11 13:20
阅读 996·2023-01-11 13:20
阅读 1651·2023-01-11 13:20
阅读 3958·2023-01-11 13:20
阅读 2456·2023-01-11 13:20
阅读 1288·2023-01-11 13:20
阅读 3452·2023-01-11 13:20