PostgreSQL - 一文看懂explain_postgresql explain-优快云博客

本文链接：https://blog.youkuaiyun.com/zou8944/article/details/107729297

explain是SQL优化的前提。但explain的结果，无论是官方手册，还是别人写的博客，看完后点头如捣蒜，但往往看到自己SQL的执行计划时，并不很确定要优化的点在哪里。很大一部分原因是不知道各计划节点的具体含义，更确切地说，是不明白SQL执行的原理。比如，我们并不能很好地回答以下问题

index scan、index only scan、bitmap index scan 有啥区别？

为什么明明有建立索引，但PG就是不用呢？

执行计划中有子查询就一定不好吗？

hash join、merge join、nestloop join有啥区别？

。。。。。。

本文的目的，是让大家看懂SQL执行计划。毕竟，看懂了，才谈得上优化。

只有在了解数据库针对SQL做了哪些优化，才能制定出合理策略，利用优化器本身的特性达到最终优化整个SQL的最终目的，因此我们首先会介绍SQL优化器的原理；而本文的目的是让大家看懂explain，因此会介绍执行计划中最核心的部分——扫描节点和连接节点，大部分的优化思路都来自于此；文章的最后介绍explain命令得到的结果每一部分的具体含义，并介绍方便查看复杂计划的可视化工具。

查询优化器原理

在介绍执行计划前，先了解一下执行计划生成的原理。一条SQL从输入到执行完毕，大致会经历如下三个步骤

语法分析：词法分析、语法分析、语义分析
查询优化：基于规则的优化、基于代价的优化
查询执行、数据存取

explain的输出，是查询优化的最终结果。而查询优化又被分为两个步骤

基于规则的优化：即逻辑优化，通过对关系代数表达式进行逻辑上的等价变换，以获得性能更好的计划，比如谓词下推（将上层的过滤条件下推到下层）。
基于代价的优化：即物理优化，逻辑优化后，实际的查询路径还是有很多种，PG建立了代价计算模型，计算所有可能路径的代价，选出最优路径。

比如select * from users where id = 1，在扫描方式上，有顺序扫描、索引扫描等。在不同的数据量情况下，按顺序扫描和索引扫描的代价可能是不一样的，因此它的执行计划可能会随数据量的变化而变化。

逻辑优化（基于规则的优化）

基于逻辑的等价变换，可对原始的SQL语句进行优化。逻辑优化的基本原则——将复杂逻辑变为简单逻辑。具体做的工作，大致是提升子查询、表达式预处理、having子句条件下推、group by冗余字段消除、谓词（过滤条件）下推、外连接消除等。

子查询提升

子查询可分为如下两类

相关子查询：子查询中引用外层表的列属性，导致外层表的每一条记录，子查询都需要重新执行一次
非相关子查询：子查询是独立的，与外层表没有直接的关联，子查询单独执行一次，外层表可以重复利用其结果

通常来说，相关子查询会被提升，非相关子查询由于其本来就只执行一次，因此没有太大必要提升。

-- 相关子查询举例
explain select *, (select label_id from content_to_label where content_id = content.id) as label_id from content;
HotSpotIntrinsicCandidate
Seq Scan on content  (cost=0.00..3694.61 rows=1151 width=743)
  SubPlan 1
    ->  Seq Scan on content_to_label  (cost=0.00..3.10 rows=3 width=4)
          Filter: (content_id = content.id)

-- 非相关子查询举例
explain select *, (select label_id from content_to_label limit 1) as label_id from content;

Seq Scan on content  (cost=0.02..126.53 rows=1151 width=743)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..0.02 rows=1 width=4)
          ->  Seq Scan on content_to_label  (cost=0.00..2.68 rows=168 width=4)

相关子查询又可以依据子查询出现的位置分为如下两种——子查询和子连接，他们都能够被提升。

子查询语句：出现在FROM关键字后的是子查询语句
子连接语句：出现在WHERE/ON等约束条件或SELECT子句中的是子连接语句

提升子连接

子连接是子查询的一种特殊情况，由于它常出现在条件中，因此通常伴随ANY、EXISTS、NOT EXISTS、IN、NOT IN等关键字，PG会对他们尝试做提升，比如

-- 查询那些打过标签的文章
explain select * from content where exists(select 1 from content_to_label where content_id = content.id);
-- 在不优化的情况下，exists子查询会像上面所示，真的是子查询。但这里优化器将子查询做了提升，提升后变成连接，通过将内表hash化，降低算法复杂度
Hash Join  (cost=4.43..134.62 rows=59 width=739)
  Hash Cond: (content.id = content_to_label.content_id)
  ->  Seq Scan on content  (cost=0.00..126.51 rows=1151 width=739)
  ->  Hash  (cost=3.69..3.69 rows=59 width=4)
        ->  HashAggregate  (cost=3.10..3.69 rows=59 width=4)
              Group Key: content_to_label.content_id
              ->  Seq Scan on content_to_label  (cost=0.00..2.68 rows=168 width=4)

能够被提升还有一个前提条件是子查询必须足够简单，上面同样的SQL，子查询投影改成聚集函数，就无法提升

explain select * from content where exists(select sum(content_id) from content_to_label where content_id = content.id);

Seq Scan on content  (cost=0.00..3714.75 rows=576 width=739)
  Filter: (SubPlan 1)
  SubPlan 1
    ->  Aggregate  (cost=3.11..3.12 rows=1 width=8)
          ->  Seq Scan on content_to_label  (cost=0.00..3.10 rows=3 width=4)
                Filter: (content_id = content.id)

提升子查询

出现在表位置的子查询，也能够提升，如下

explain select * from content left join (select *, 1 from content_to_label) ctl on content.id = ctl.content_id;

Hash Right Join  (cost=140.90..144.02 rows=1151 width=759)
  Hash Cond: (content_to_label.content_id = content.id)
  ->  Seq Scan on content_to_label  (cost=0.00..2.68 rows=168 width=20)
  ->  Hash  (cost=126.51..126.51 rows=1151 width=739)
        ->  Seq Scan on content  (cost=0.00..126.51 rows=1151 width=739)

预处理表达式

即将能够事先处理的表达式处理掉，比如常量结算、约束条件的逻辑简化等

常量简化

即直接计算出SQL中的常量表达式

-- 可以直接计算出101
explain select * from content where id < 1 + 100

Bitmap Heap Scan on content  (cost=5.08..123.47 rows=104 width=739)
  Recheck Cond: (id < 101)
  ->  Bitmap Index Scan on content_pkey