PG vs MySQL 执行计划解读的异同点

原创进击的CJR 2025-03-21

PG的执行计划

基本语法

explain [option] statement

option为可选参数，常见选项如下
选项说明

analyze 执行SQL并且显示实际的运行时间和其他统计信息，会实际执行SQL语句
verbose 显示附加信息
costs 包括每个计划节点的启动成本预估和总成本的消耗，也包括行数和行宽度的预估，默认TRUE
buffers 包括共享块命中、读、脏和写的次数，本地块命中、读、脏和写，临时块读和写的次数。
format 格式：TEXT、XML、JSON 或 YAML，默认 TEXT

postgres=# create table t1(i int);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,10000));
INSERT 0 10000
postgres=# 
postgres=# 
postgres=# explain select * from t1;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on t1  (cost=0.00..159.75 rows=11475 width=4)
(1 row)

postgres=#  explain analyze select * from t1;
                                              QUERY PLAN                                         
     
-------------------------------------------------------------------------------------------------
-----
 Seq Scan on t1  (cost=0.00..145.00 rows=10000 width=4) (actual time=0.010..0.645 rows=10000 loop
s=1)
 Planning Time: 0.073 ms
 Execution Time: 1.007 ms
(3 rows)

postgres=#  explain (analyze on, buffers on) select * from t1;
                                              QUERY PLAN                                         
     
-------------------------------------------------------------------------------------------------
-----
 Seq Scan on t1  (cost=0.00..145.00 rows=10000 width=4) (actual time=0.007..0.657 rows=10000 loop
s=1)
   Buffers: shared hit=45
 Planning Time: 0.029 ms
 Execution Time: 1.062 ms
(4 rows)

postgres=# explain (analyze on, buffers on,verbose on) select * from t1;
                                                 QUERY PLAN                                      
            
-------------------------------------------------------------------------------------------------
------------
 Seq Scan on public.t1  (cost=0.00..145.00 rows=10000 width=4) (actual time=0.007..0.649 rows=100
00 loops=1)
   Output: i
   Buffers: shared hit=45
 Planning Time: 0.031 ms
 Execution Time: 1.068 ms
(5 rows)



复制

Operation（操作）：表示PostgreSQL使用的操作方式，如顺序扫描、索引扫描等。
Rows（行数）：估算该操作返回的数据行数。
Cost（成本）：PostgreSQL估算的执行成本，分为Startup Cost（启动成本）和Total Cost（总成本）。Total Cost考虑了从开始到结束的整体开销。
Actual Time（实际时间）：使用EXPLAIN ANALYZE时返回，显示每个步骤的实际执行时间。

Seq Scan on public.t1：表示对t1表进行顺序扫描（即全表扫描），这意味着PostgreSQL从头到尾扫描了所有数据行，找出满足条件的记录。
Cost=cost=0.00..145.00：这是PostgreSQL对该操作成本的估算，0.00表示开始时的成本，145.00表示完成此操作的总成本。
Rows=10000 ：表示PostgreSQL估算该查询将返回10000行数据。
actual time=0.007..0.649：这是实际的查询执行时间，从0.007ms开始，到0.649ms结束，总共消耗了约0.642ms。

复制

执行计划解读

解读原则
（1）从下往上
（2）从右至左
（3）每一步cost包括上一步cost

执行计划通常会显示以下几个关键要素，它们帮助我们理解每个查询步骤的执行效率，例如

postgres=# explain (analyze on,buffers on) select relname,relnamespace from pg_class join pg_namespace on (pg_class.relnamespace=pg_namespace.oid) where relname='t1';
                                                                   QUERY PLAN                    
                                                
-------------------------------------------------------------------------------------------------
------------------------------------------------
 Nested Loop  (cost=0.27..5.38 rows=1 width=68) (actual time=0.020..0.022 rows=1 loops=1)
   Join Filter: (pg_class.relnamespace = pg_namespace.oid)
   Rows Removed by Join Filter: 2
   Buffers: shared hit=5
   ->  Index Only Scan using pg_class_relname_nsp_index on pg_class  (cost=0.27..4.29 rows=1 widt
h=68) (actual time=0.011..0.012 rows=1 loops=1)
         Index Cond: (relname = 't1'::name)
         Heap Fetches: 1
         Buffers: shared hit=4
   ->  Seq Scan on pg_namespace  (cost=0.00..1.04 rows=4 width=4) (actual time=0.004..0.004 rows=
3 loops=1)
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.196 ms
 Execution Time: 0.040 ms
(14 rows)


复制

postgres=# explain analyze select relname,relnamespace from pg_class join pg_namespace on (pg_class.relnamespace=pg_namespace.oid) where relname='t1';
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.27..5.38 rows=1 width=68) (actual time=0.017..0.019 rows=1 loops=1)
   Join Filter: (pg_class.relnamespace = pg_namespace.oid)
   Rows Removed by Join Filter: 2
   ->  Index Only Scan using pg_class_relname_nsp_index on pg_class  (cost=0.27..4.29 rows=1 width=68) (actual time=0.009..0.010 rows=1 loops=1)
         Index Cond: (relname = 't1'::name)
         Heap Fetches: 1
   ->  Seq Scan on pg_namespace  (cost=0.00..1.04 rows=4 width=4) (actual time=0.003..0.003 rows=3 loops=1)
 Planning Time: 0.149 ms
 Execution Time: 0.034 ms
(9 rows)
复制

第一步：通过Seq Scan 扫描 pg_namespace
第二步：通过pg_class_relname_nsp_index 索引扫描pg_class 。Index Only Scan不会回表相
第三步：对步骤一和步骤二的扫描结果进行Nestloop Join 连表扫描

(cost=0.27…5.38 rows=1 width=68) (actual time=0.020…0.022 rows=1 loops=1)

cost=0.27…5.38 rows=1 width=68 #cost=0.27 计划启动成本，返回第一行cost值；5382返回所有数据成本；rows=1返回行数；width=68每行平均宽度（字节），代价越低表示查询执行越高效。

actual time=0.020…0.022 rows=1 loops=1 #实际花费时间，loops循环次数

Planning Time: 0.196 ms
Execution Time: 0.040 ms

Planning Time：生成执行计划的时间
Execution Time：执行执行计划的时间
Buffers：数据在缓存中命中块数

常见的扫描方式

Sequential Scan
Index Scan
Index Only Scan
Bitmap Scan
TID Scan
Nested Loop Join
Hash Join
Merge Join

（1）Seq Scan
全表扫描，当数据表中没有索引，或者满足条件的数据集较大，索引扫描的成本高于全表扫描，优化器会选择使用全表扫描。

（2）Index Scan
索引扫描，查询列有索引，则直接扫描索引，不再进行全表扫描，耗费时间小于全表扫描。

（3）Index Only Scan
全索引扫描，当查询的条件都在索引中，也会走该扫描方式，不会读取表文件。

（4）Bitmap Index Scan
位图索引扫描，也是一种走索引的方式，方法是扫描索引，把满足条件的行或者块在内存中建一个位图，扫描完索引后，再跟进位图中记录的指针到表的数据文件读取相应的数据。在or、and、in子句和有多个条件都可以同时走不同的索引时，都可能走Bitmap Index Scan。

（5）TID Scan
查找谓词中有TID。（CTID是一个系统列，用于标识某一元组位于哪个位置，由（block number + 块内的偏移量offset）组成和oracle的rowid十分类似，TID Scan就是采用该方式扫描，直接定位）

（6）Nestloop Join
嵌套循环连接，是在两个表做连接时，从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录JOIN。

这个方式和mysql的SNLJ一样的原理。
NLJ (Nested Loop Join)是一种最简单的连接算法，每条外部关系的记录与每条内部关系的记录相匹配。

（7）Hash Join
散列连接，是优化器做大数据集连接时的常用方式，优化器使用两个表中较小的表（通常是小一点的那个表或数据源）利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到hash列表中，然后扫描较大的表，同样对JOIN KEY进行HASH后探测散列表，找出与散列表匹配的行。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个部分，写入磁盘的临时文件，会多一个写的代价，降低效率。

（8）Merge Join
排序合并连接，是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。

PostgreSQL支持多种与规划器相关的配置，这些配置可用于提示查询优化器不要选择某些特定类型的连接方法。如果优化器选择的连接方法不是最优的，那么可以关闭这些配置参数，以强制查询优化器选择不同类型的连接方法。默认情况下，所有这些配置参数都是开启的。下面是对于特定连接方法的规划器配置参数：

enable nestloop：对应Nested Loop Join
enable hashjoin：对应Hash Join
enable mergejoin：对应Merge Join

MySQL的执行计划

基本语法

{EXPLAIN | DESCRIBE | DESC}FORMAT = {TRADITIONAL | JSON | TREE}explainable_stmtement

默认的格式为 TRADITIONAL，以表格的形式显示输出信息；JSON 选项表示以 JSON 格式显示信息；MySQL 8.0.16 之后支持 TREE 选项，以树形结构输出了比默认格式更加详细的信息，这也是唯一能够显示 hash join 的格式。

explain format=json statement
JSON格式提供了以JSON格式显示的详细执行计划，这个格式适合于被程序调用，例如图形工具
workbench显示的图形化的执行计划就是调用了JSON格式的接口。

explain format=tree statement
树形格式是从MySQL 8.0.18开始引入格式，它提供的执行计划比传统的执行计划更详细，输出格
式是树形的，和PG几乎是一致的。

mysql> explain format=tree select city from city where country_id=(select country_id from 
    -> country where country='China') \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (city.country_id = (select #2))  (cost=7.55 rows=53)
    -> Index lookup on city using idx_fk_country_id (country_id=(select #2))  (cost=7.55 rows=53)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: (country.country = 'China')  (cost=11.15 rows=11)
            -> Table scan on country  (cost=11.15 rows=109)

1 row in set (0.00 sec)

复制

EXPLAIN ANALYZE实际上是树形的执行计划的扩展，它不但提供了执行计划，还检测并执行了
SQL语句，提供了执行过程中的实际度量。

mysql>  EXPLAIN ANALYZE select city from city where country_id=(select country_id from 
    -> country where country='China') \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (city.country_id = (select #2))  (cost=7.55 rows=53) (actual time=0.099..0.110 rows=53 loops=1)
    -> Index lookup on city using idx_fk_country_id (country_id=(select #2))  (cost=7.55 rows=53) (actual time=0.097..0.103 rows=53 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: (country.country = 'China')  (cost=11.15 rows=11) (actual time=0.044..0.063 rows=1 loops=1)
            -> Table scan on country  (cost=11.15 rows=109) (actual time=0.036..0.050 rows=109 loops=1)


复制

注意：对与非select语句的支持
抑制输出，并不真正执行SQL语句。
mysql> explain analyze update actor set first_name=‘a’ where first_name=‘a’;

explain for connection
在实际工作中，如果发现一个正在执行的SQL语句耗时很长，这时想查询它的执行计划，通常的做法是使用EXPLAIN生成这个SQL语句的执行计划，但因为统计信息等原因，生成的执行计划和正在执行的执行计划可能不完全相同，更好的做法是使用explain for connection查询当前正在使用的执行计划。

下面的SQL查询出当前的会话号：
mysql> select connection_id();
 +-----------------+
 | connection_id() |
 +-----------------+
 |              17 |
 +-----------------+
 1 row in set (0.00 sec)
或者使用show processlist查询会话号。在当前的会话中执行一个慢SQL语句：
mysql> select sleep(60), city from city where country_id=(select country_id from country where country='China') \G
根据会话号在其他会话里查询正在执行的SQL语句的执行计划：
mysql> explain for connection  17\G
复制

执行计划解读

type 字段

type 被称为连接类型（join type）或者访问类型（access type），它显示了 MySQL 如何访问表中的数据。

访问类型会直接影响到查询语句的性能，性能从好到差依次为：

system，表中只有一行数据（系统表），这是 const 类型的特殊情况；

const，最多返回一条匹配的数据，在查询的最开始读取；

eq_ref，对于前面的每一行，从该表中读取一行数据；

ref，对于前面的每一行，从该表中读取匹配索引值的所有数据行；

fulltext，通过 FULLTEXT 索引查找数据；

ref_or_null，与 ref 类似，额外加上 NULL 值查找；

index_merge，使用索引合并优化技术，此时 key 列显示使用的所有索引；

unique_subquery，替代以下情况时的 eq_ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)；

index_subquery，与 unique_subquery 类似，用于子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)；

range，使用索引查找范围值；

index，与 ALL 类型相同，只不过扫描的是索引；

ALL，全表扫描，通常表示存在性能问题。
复制

Extra 字段

执行计划输出中的 Extra 字段通常会显示更多的信息，可以帮助我们发现性能问题的所在。上文中我们已经介绍了一些 Extra 字段的信息，需要重点关注的输出内容包括：

Using where，表示将经过 WHERE 条件过滤后的数据传递给下个数据表或者返回客户端。如果访问类型为 ALL 或者 index，而 Extra 字段不是 Using where，意味着查询语句可能存在问题（除非就是想要获取全部数据）。

Using index condition，表示通过索引访问表之前，基于查询条件中的索引字段进行一次过滤，只返回必要的索引项。这也就是索引条件下推优化。

Using index，表示直接通过索引即可返回所需的字段信息（index-only scan），不需要访问表。对于 InnoDB，如果通过主键获取数据，不会显示 Using index，但是仍然是 index-only scan。此时，访问类型为 index，key 字段显示为 PRIMARY。

Using filesort，意味着需要执行额外的排序操作，通常需要占用大量的内存或者磁盘。
复制

索引通常可以用于优化排序操作.

Using temporary，意味着需要创建临时表保存中间结果。同样可以通过增加索引进行优化。
复制

解读

mysql>  EXPLAIN ANALYZE select city from city where country_id=(select country_id from 
    -> country where country='China') \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (city.country_id = (select #2))  (cost=7.55 rows=53) (actual time=0.099..0.110 rows=53 loops=1)
    -> Index lookup on city using idx_fk_country_id (country_id=(select #2))  (cost=7.55 rows=53) (actual time=0.097..0.103 rows=53 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: (country.country = 'China')  (cost=11.15 rows=11) (actual time=0.044..0.063 rows=1 loops=1)
            -> Table scan on country  (cost=11.15 rows=109) (actual time=0.036..0.050 rows=109 loops=1)


复制

对于每个迭代器，EXPLAIN ANALYZE 输出了以下信息：
估计执行成本，某些迭代器不计入成本模型；
估计返回行数；
返回第一行的实际时间（ms）；
返回所有行的实际时间（ms），如果存在多次循环，显示平均时间；
实际返回行数；
循环次数。
在输出结果中的每个节点包含了下面所有节点的汇总信息.

举例
->Table scan on country (cost=11.15 rows=109) (actual time=0.036…0.050 rows=109 loops=1)

全表扫描；估计成本为 11.15，估计返回 109 行数据；实际返回第一行数据的时间为 0.036ms，实际返回所有数据的平均时间为 0.050 ms，实际返回了 109 行数据，嵌套循环操作执行了 1 次。

循环的实现过程是首先通过主键扫描 employee 表并且应用过滤迭代器：

-> Filter: (e.emp_id in (1,2))  (cost=0.91 rows=2) (actual time=0.218..0.233 rows=2 loops=1)        -> Index range scan on e using PRIMARY  (cost=0.91 rows=2) (actual time=0.214..0.228 rows=2 loops=1)
复制

其中，应用过滤迭代器返回第一行数据的时间为 0.218 ms，包括索引扫描的 0.214 ms；返回所有数据的平均时间为 0.233 ms，包括索引扫描的 0.228 ms；绝大部分时间都消耗在了索引扫描，总共返回了 2 条数据。

常见的扫描方式

常见的和PG类似，MySQL也有全表扫描，索引扫描，和PG的index_only_scan对应的是MySQL的覆盖索引扫描。基于表存储方式的不同（堆表和聚簇索引表），都有各自的不同扫描实现方式。

在连接查询方式上也有差异，MySQL连接查询在8.0之前有这四种
（1）NLJ
Index Nested-Loop Join
通过连接条件确定可用的索引，在 Inner Loop 中扫描索引而不去扫描数据本身，从而提高 Inner Loop 的效率。

（2）SNLJ
Simple Nested-Loop Join
外表（驱动表）中的每一条记录与内表（被驱动表）中的记录进行比较判断，对于两表联接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，被驱动表的具体访问次数取决于对驱动表执行单表查询后的结果集中的记录条数。

（3）BNL
Block Nested-Loop Join
在 SNLJ 的基础上使用了 join buffer，会提前读取 Inner Loop 所需要的记录到 buffer 中，以提高 Inner Loop 的效率。

（4）BKA
Batched Key Access
将一批主键或索引键一次性发送给存储引擎来查找匹配的行，而不是逐行处理。这种方式可以有效利用数据库的缓存和减少 I/O 开销。

（5）Hash Join
8.0.18 版本增加了Hash Join算法。这个就和PG的hash join 对应了。

PG vs MySQL

可以看到 PG和MySQL 执行计划，在MySQL8.0之后执行计划的TREE模式和PG的执行计划在阅读和解读上几乎一致了。同样依靠统计信息和成本计算的方式来生成执行计划。
基本输出内容都会显示查询的执行计划树，包括各种操作节点，如表扫描（Seq Scan、Table Scan）、索引扫描（Index Scan、Index Only Scan）、连接操作（Nested Loop、Hash Join、Merge Join）等，以及每个节点的相关信息，如成本估计（cost）、预计返回行数（rows）等。

输出细节差异
MySQL在 EXPLAIN ANALYZE 的输出中，会提供一些 MySQL 特有的度量信息，如获取第一行的实际时间（以毫秒为单位）、获取所有行的实际时间、实际读取的行数、实际循环数等，这些信息有助于更精确地了解查询在各个阶段的资源消耗情况。
PostgreSQL除了常规的执行计划节点信息外，还会显示一些与 PostgreSQL 系统相关的统计信息，如共享缓冲区命中次数（shared hit）、读取磁盘块的次数（read）等，这些信息对于分析 I/O 性能和内存使用情况非常有用。