POSTGRESQL执行计划详解分析SQL的基础（30页）.pdf

1、POSTGRESQL执行计划详解分析SQL的基础何敏，曾就职于成都文武、人大金仓，参与开发PG高可用系统、RDS、数据库接口。精通数据库迁移、高可用、系统方案设计，有丰富的开发和运维经验。PG中文社区首届 PG MVP中国首期PG ACE 伙伴中国POSTGRESQL分会官方认证讲师盘古云课堂高级讲师PostgreSQL在执行任何查询时，都会先经过语法、语义解析，生成查询表达式树；然后根据规则系统对查询树进行转换，视图也在这里展开，最终生成逻辑查询树；最后根据系统统计信息、物理存储的情况对查询进行优化，生成物理查询计划；最后根据查询计划进行执行，最终得到结果。因此，选择正确的查询结构和数据属性

2、的规划，对执行效率至关重要。我们可以通过explain命令来查看执行计划，进而对不合理的地方进行调整，提高SQL的执行效率。解析器分析器重写器计划器执行器语法解析：语法解析树语义分析：查询树重写：查询树执行计划树1.执行预处理2.找出代价最小的路径3.创建计划树初初识识执执行行计计划划初初识识执执行行计计划划通过在SQL语句前面加上explain操作，就可以获取到该SQL的执行计划，该SQL并没有实际执行。输出的执行计划有如下特点：1.查询规划是以规划为节点的树形结构。树的最底节点是扫描节点：他返回表中的原数据行。2.不同的表有不同的扫描节点类型：顺序扫描，索引扫描和位图索引扫描。3.也有非表

3、列源，如VALUES子句并设置FROM返回，他们有自己的扫描类型。4.如果查询需要关联，聚合，排序或其他操作，会在扫描节点之上增加节点执行这些操作。5.EXPLAIN的输出是每个树节点显示一行，内容是基本节点类型和执行节点的消耗评估。可能会出现同级别的节点，从汇总行节点缩进显示节点的其他属性。第一行（最上节点的汇总行）是评估执行计划的总消耗，这个值越小越好。初初识识执执行行计计划划因为这个查询没有WHERE子句，所以必须扫描表中的所有行，所以规划器选择使用简单的顺序扫描规划。括号中的数字从左到右依次是：1.评估开始消耗：这是可以开始输出前的时间，比如排序节点的排序的时间。2.评估总消耗：假设查

4、询从执行到结束的时间。有时父节点可能停止这个过程，比如LIMIT子句。3.评估查询节点的输出行数，假设该节点执行结束。4.评估查询节点的输出行的平均字节数。初初识识执执行行计计划划需要知道的是：上级节点的消耗包括其子节点的消耗。这个消耗值只反映规划器关心的内容，一般这个消耗不包括将数据传输到客户端的时间。评估的行数不是执行和扫描查询节点的数量，而是节点返回的数量。它通常会少于扫描数量，因为有WHERE条件会过滤掉一些数据。理想情况顶级行数评估近似于实际返回的数量。这个消耗的计算依赖于规划器的设置参数，这里的例子都是在默认参数下运行。cost描述一个SQL执行的代价是多少，而不是具体的时间。下面

5、是默认情况下，对数据操作的消耗评估基础：初初识识执执行行计计划划回到刚才的例子，表test有10000条数据分布在94个磁盘页，评估时间是（磁盘页*seq_page_cost）+（扫描行*cpu_tuple_cost）。默认seq_page_cost是1.0，cpu_tuple_cost是0.01，所以评估值是(94*1.0)+(10000*0.01)=194。什么时候更新的pg_class、pg_stat_user_tables等统计信息初初识识执执行行计计划划查询节点增加了“filter”条件。这意味着查询节点为扫描的每一行数据增加条件检查，只输入符合条件数据。评估的输出记录数因为wher

6、e子句变少了，但是扫描的数据还是10000条，所以消耗没有减少，反而增加了一点CPU的计算时间。这个查询实际输出的记录数是1000，但是评估是个近似值，多次运行可能略有差别，这中情况可以通过ANALYZE命令改善。几几种种扫扫描描方方式式全全表表扫扫描描：Seq Scan全表扫描，当数据表中没有索引，或者满足条件的数据集较大，索引扫描的成本高于全表扫描，这时规划器会选择使用全表扫描。至于全表扫描的过程和索引扫描的过程在这里不详细说，后续可以再开一个主题。几几种种扫扫描描方方式式索索引引扫扫描描：index scan如果查询的列创建有索引，则直接扫描索引，不再进行全表扫描，耗费时间小于顺序扫描。