在dba的日常工作中，时常会收到应用侧的通知“XX帮看看某数据库是不是有问题，SQL跑不动了，好慢之类的等等”。这种一般都是SQL性能类问题，通过执行计划我们将直观的确认SQL性能是否存在隐患,掌握执行计划这项技能就显得尤为重要，下面就一起来学习一下pg的执行计划吧。
首先我们先要搞懂SQL执行计划到底是个什么东东,简单的来讲就是SQL语句在数据库内部从取数据到返回结果集的一个完整过程，通过这个过程，我们可以看到SQL每一步的开销，进而判断SQL响应是否正常.
那在pg中执行计划又包含4大类型，如下：
1.控制节点(ControlNode)
2.扫描节点(ScanNode)
3.物化节点(MaterializationNode)
4.连接节点（JoinNode）
t_cust,cust表，以tid关联t_user表，并填充150万行数据信息.
具体如下：
在pg中查看执行计划使用explain命令,语法如下：
EXPLAIN [ ( option[, ...] ) ] statement
EXPLAIN [ ANALYZE ][ VERBOSE ] statementANALYZE [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:
第一步:对t_user进行全表扫描，根据tid< 150000过滤350001行，返回149999行；
第二步:对第一步的结果集进行Hash处理生成HashBuckets；
第三步:对t_cust进行全表扫描,返回结果集1500000行；
第四步:根据HashJoin条件,使用步骤三的结果集与HashBuckets关联取出匹配的行,返回结果集.225044行；
第五步:对第三步hash结果集进行聚合算并最终返回结果集1行。
第二种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字段上创建索引在来观察执行计划：
第一步:索引扫描t_cust表，返回结果集rows=3；
第二步:根据NestedLoop算法，使用步骤一结果集中的tid循序使用新建的tdx_user_tid索引扫描3次,并最终返回结果集rows=3。
从整个执行时间来看从6271ms缩短到0.151ms,提升非常明显.
从上面2个表关联案例中我们可以发现HashJoin适用于大结果集的表关联,并不关注最终返回结果集,分析型场景较多;而NestedLoop往往最终返回结果集较小,通常都是交易型场景,要求驱动表谓词条件相对精确，缩小结果集以降低循环次数，以及被驱动表的关联条件上需要创建索引，提升循环体内部的扫描效率;总体而言NestedLoop出现问题的情况比较多，需要额外注意.
通过以上几种案例解读，可以发现各种数据库执行计划都大同小异 ,问题的类型以及优化思路也基本类似,本文就到此为止.
本文首发于2020年8月31日.
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129262.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(...
阅读 1347·2023-01-11 13:20
阅读 1685·2023-01-11 13:20
阅读 1133·2023-01-11 13:20
阅读 1860·2023-01-11 13:20
阅读 4101·2023-01-11 13:20
阅读 2705·2023-01-11 13:20
阅读 1386·2023-01-11 13:20
阅读 3599·2023-01-11 13:20
