PG优化器简单概述

作者:阎书利

对于使用关系型数据库的应用系统而言,SQL的好坏会直接影响系统的性能,一条烂SQL可能拖跨整个应用,甚至会导致数据库服务器失去响应或者使整个数据库Hang住。

而对所有的关系型数据库而言,优化器都是最核心的部分,因为优化器负责解析SQL,数据也都是通过SQL来访问数据库获得的,所以优化器的好坏会直接决定关系型数据库的强弱,且优化器负责解析SQL,所以想做好SQL优化就必须了解优化器。PG 对复杂查询处理能力与Oracle类似,且查询优化器很成熟。 PG的优化器被用来处理所有可能的查询, 它里面包含了大量的分支判断,比如在扫描表的时候需要确定是使用 seqscan还是使用indexscan或者 tidscan,如果使用seqscan,那么优化器也要判断需要多少个worker(自9.6版本引入的并行worker)等等。

优化器概念:

优化器(Optimizer)是数据库里的一个核心子系统,它的目的是按照一定规则来得到它认为的目标SQL在当前情形下的最高效的执行路径,也就是说优化器的目的是为了得到目标SQL的执行计划。

优化器一般分为RBO(Rule-Based Optimizer)基于规则的优化器和CBO(Cost-Based Optimizer)基于代价的优化器,PG里使用的是CBO,各个执行路径的成本和扫描方式, 关联方式, 操作符, 成本因子, 数据集等都有关。

如下为PG数据库里一个SQL的处理过程:

CBO会选择那些消耗系统I/O和CPU资源最少的执行路径作为最优选择,在解析目标时,首先会对SQL完成查询转换,接下来,会计算完成查询转换之后得到的等价改写SQL的各种可能执行的路径的成本,然后从这些执行路径里选择成本值最小的一条来作为原目标SQL的执行计划。然后PG就会根据此执行计划去实际执行该SQL,并将执行结果返回给用户。

代价=IO代价+CPU代价

影响COST计算的几个常见因素:

1.Cardinality:集的势,指定集合所包含的记录数,或者说指定结果集的行数,表示对目标SQL的某个具体步骤的执行结果所包含的记录数的估算。如果某步骤的Cardinality值越大,对应的成本值往往越大,总成本也就越大。

2.Selectivity:可选择率,范围为0~1,值越小表设计选择性越好,在为1时选择性是最差的。

Selectivity=施加指定谓词条件后返回结果集的记录数/未施加任何谓词条件的原始结果集的记录数。

Cardinality和Selectivity的值会直接影响CBO对于相关执行步骤的估算,进而影响CBO对于目标SQL执行计划的选择。

3.成本因子,例如连续或随机扫描单个数据块的成本因子, CPU从HEAP块处理一条记录的成本因子, 从INDEX块处理一条索引记录的成本因子等。

4.数据存储物理顺序和索引顺序的离散度, 影响索引扫描的计算成本。

5.内存大小, 影响索引扫描的计算成本。

6.列统计信息(列、宽、空值比例, 唯一值比例, 高频值及其比例, bucket, 物理顺序和索引顺序的离散度等), 影响选择性, 即结果集行数, 最终影响索引扫描的计算成本。

7.回表:如果回表次数太多,就不应该走索引了,应该走全表扫描;在SQL优化时.必须关注回表次数,要注意回表的物理I/O次数;

8.表与表之间关闭,1:1 1:N N:N。

9.成本计算方面,seq_page_cost,random_page_cost等参数。

主营产品:印后包装