数据库Explain对比

关系型数据库 Postgres

数据库Explain对比

SQL优化整个流程大概可以分为两个步骤：1. 确认性能差的SQL、2.根据执行计划了解导致性能差的原因，尝试针对做优化

Postgres

确定SQL

安装 pg_stat_statement 扩展，安装此扩展后，后续的查询将会被 postgres 记录到扩展中记录的 pg_stat_statement 中，该记录表会记录 SQL 、查询消耗时间等一系列信息。
使用慢SQL日志，可在配置文件中对 log_min_duration_statement 慢 SQL 判断阈值，超过此阈值作为慢SQL记录下来。
对 pg_stat_activity 表做查询，它的效果是查询当前正在执行的SQL进程，主要用于 KILL 进程，当出现死锁 / 占用巨量资源的不合理SQL进程时，那么就可以利用该表获知 process ID 将目标进程 kill 掉。

优化SQL

postgres 提供了 EXPLAIN 和 EXPLAIN ANALYZE 两种不同的性能分析工具，EXPLAIN 实际上不会对SQL做实际的执行工作，只是单纯的生成执行计划。而 EXPLAIN ANALYZE 不仅仅将 SQL 执行计划生成，并且还会对SQL的执行步骤做耗时记录。则相对 Mysql 来说是相当优秀的功能。

Postgres Explain Demo

EXPLAIN SELECT t.oid,t.*,c.relkind, d.description
FROM pg_catalog.pg_type t
	-- pg_type 实际上没有对查询结果造成影响，执行优化器优化掉了
    LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem 
    LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
    LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL

-- query result
-- 对 下方两个 Hash节点 做关联 (Hash Right Join 和 Hash)，资源消耗 cost 为 54.01..166.03，涉及行数 654，长度为 290
Hash Left Join  (cost=54.01..166.03 rows=654 width=290)
  -- Hash 关联条件是 t.typrelid = c.oid
  Hash Cond: (t.typrelid = c.oid)
  ->  Hash Right Join  (cost=30.71..141.00 rows=654 width=285)
        Hash Cond: (d.objoid = t.oid)
        -- 实际导致对 pg_description 做 Seq Scan 扫描，资源消耗 cost 为 0.00..96.703，涉及行数 5170，行平均宽度为29字节
        ->  Seq Scan on pg_description d  (cost=0.00..96.70 rows=5170 width=29)
        -- 将 pg_type 做 Seq Scan 过滤的结果构造 Hash 节点，并交付上层使用
        ->  Hash  (cost=22.54..22.54 rows=654 width=260)
        	 -- 针对 typename 做 Seq Scan 过滤判断，资源消耗 cost 为 0.00..22.54，涉及行数 654，行平均宽度为260字节
              ->  Seq Scan on pg_type t  (cost=0.00..22.54 rows=654 width=260)
                    Filter: (typname IS NOT NULL)
  -- 将对 pg_class 做 Seq Scan 的结果构造 Hash 节点，并交付上层使用
  ->  Hash  (cost=18.13..18.13 rows=413 width=5)
        ->  Seq Scan on pg_class c  (cost=0.00..18.13 rows=413 width=5)

整个执行计划可以被转义成：

对 pg_class 做全表查询，得到平均行宽度是 5字节，共有413条数据的结果
针对上述 pg_class 查询结果，构造 Hash 节点 A
针对 pg_type 做全表查询，并做过滤，条件为(typename IS NOT NULL)，得到平均宽度为 260 字节，共有 654 条数据的结果
针对上述 pg_type 查询结果，构造 Hash 节点 B
针对 pg_description 做全表查询，得到平均行宽度是 29 字节，共有5170条数据的结果
将 pg_type 查询结构构造的 Hash 节点 B 和 pg_description 的查询结果利用 d.objoid 和 t.oid 做关联，得到平均宽度为 285 字节，共有 654 条数据的结果
将 pg_class 查询结果构造的 Hash 节点 A 和上一步结果做关联，得到平均宽度为 290 直接，共有 654 的执行结果。

Postgres Explain Analyze Demo

EXPLAIN ANALYZE SELECT t.oid,t.*,c.relkind, d.description
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem 
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL

-- query result
Hash Left Join  (cost=54.01..166.03 rows=654 width=290) (actual time=0.959..1.737 rows=629 loops=1)
  Hash Cond: (t.typrelid = c.oid)
  ->  Hash Right Join  (cost=30.71..141.00 rows=654 width=285) (actual time=0.859..1.569 rows=629 loops=1)
        Hash Cond: (d.objoid = t.oid)
        ->  Seq Scan on pg_description d  (cost=0.00..96.70 rows=5170 width=29) (actual time=0.188..1.119 rows=5221 loops=1)
        ->  Hash  (cost=22.54..22.54 rows=654 width=260) (actual time=0.115..0.116 rows=629 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 124kB
              ->  Seq Scan on pg_type t  (cost=0.00..22.54 rows=654 width=260) (actual time=0.005..0.061 rows=629 loops=1)
                    Filter: (typname IS NOT NULL)
  ->  Hash  (cost=18.13..18.13 rows=413 width=5) (actual time=0.088..0.088 rows=427 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 25kB
        ->  Seq Scan on pg_class c  (cost=0.00..18.13 rows=413 width=5) (actual time=0.008..0.061 rows=427 loops=1)
Planning Time: 0.252 ms
Execution Time: 1.793 ms

由于基本的查询跟普通的 Explain 得到的执行计划上是大概一样的，这里不详细解释每一步具体的效果是什么，我们直接关注不一样的地方：

首先每一步动作下，都会额外的多出一段 (cost=54.01..166.03 rows=654 width=290) (actual time=0.959..1.737 rows=629 loops=1)
第一括号参数和值 (cost=54.01..166.03 rows=654 width=290) 其实是预估的资源占用和行宽度预估值，而第二个括号中的 (actual time=0.959..1.737 rows=629 loops=1) 才是实际执行得到的统计结果。预估的结果如果与实际结果相差很大，那么可以用 ANALYZE 对设计的表格数据做统计更新，往往是统计数据过时导致的
在 Hash 节点 (Hash 表) 构建的记录底下，往往会额外输出类似格式的内容： Buckets: 1024 Batches: 1 Memory Usage: 124kB
该统计只会针对 Hash Join、Hash Aggregate、Sort 这一类将会造成大量内存占用的情况才会产生该统计结果。
分别代表：哈希表的桶位、哈希表插入内存所需要处理的批次、内存使用使用大小

Postgres Explain 解析参数及取值：

参数

cost：一个抽象的、用于估算工作量的统计单位 (它的统计标准是 CPU、I/O 的工作量)。比如上述 demo 中，我们有一个 cost 取值如下：cost=54.01..166.03
他其实是两个取值：分别是 54.01 和 166.03 ，54.01 代表着完成这个工作的第一步 (或者说输出首行) 将需要多少工作量 (startup_cost)，而 166.03 代表着整个步骤走完 (全部输出完) 需要多少工作量 (total_cost)
对于实际计算函数感兴趣，可以参考官方文档 PostgreSQL 17.6 Documentation 19.7. Query Planning
rows：涉及行数
width：平均单行宽度，单位是字节
actual_time：实际运行耗时，它与 cost 的情况非常类似，往往会输出以下格式的值： actual time=0.959..1.737
一样的它的首个取值 0.959 代表着的其实是第一步 (或者说输出首行) 将需要多少时间，而 1.737 代表的是整个步骤完成 (全部输出完) 需要多少时间

Buckets：哈希表桶位，它的取值是 postges 根据表格的统计信息计算来的，取决于 explain 执行计划计算出来的涉及行数，如果该值超过 1024 ，那么 postgres 将会用指数退避 2^n 翻倍 buckets 的大小。在默认的情况下，取值为 1024。

源码地址：PostgreSQL Source Code: src/backend/executor/nodeHash.c File Reference 其中方法：ExecChooseHashTableSize、ExecParallelHashIncreaseNumBatches 的具体实现中都有类似的逻辑

/* ExecChooseHashTableSize 方法 */
    /*
     * Set nbuckets to achieve an average bucket load of NTUP_PER_BUCKET when
     * memory is filled, assuming a single batch; but limit the value so that
     * the pointer arrays we'll try to allocate do not exceed hash_table_bytes
     * nor MaxAllocSize.
     * Note that both nbuckets and nbatch must be powers of 2 to make
     * ExecHashGetBucketAndBatch fast.
     */
    max_pointers = hash_table_bytes / sizeof(HashJoinTuple);
    max_pointers = Min(max_pointers, MaxAllocSize / sizeof(HashJoinTuple));
    /* If max_pointers isn't a power of 2, must round it down to one */
    max_pointers = pg_prevpower2_size_t(max_pointers);
 
    /* Also ensure we avoid integer overflow in nbatch and nbuckets */
    /* (this step is redundant given the current value of MaxAllocSize) */
    max_pointers = Min(max_pointers, INT_MAX / 2 + 1);
 
    dbuckets = ceil(ntuples / NTUP_PER_BUCKET);
    dbuckets = Min(dbuckets, max_pointers);
    nbuckets = (int) dbuckets;
    /* don't let nbuckets be really small, though ... */
    nbuckets = Max(nbuckets, 1024);   // 写死的 1024
    /* ... and force it to be a power of 2. */   // 指数退避
    nbuckets = pg_nextpower2_32(nbuckets);

/* ExecParallelHashIncreaseNumBatches 方法 */
    if (pstate->old_nbatch == 1)
    {
        ······
        /*
        * We need to calculate the maximum number of buckets to
        * stay within the MaxAllocSize boundary.  Round the
        * maximum number to the previous power of 2 given that
        * later we round the number to the next power of 2.
        */
        max_buckets = pg_prevpower2_32((uint32)(MaxAllocSize / sizeof(dsa_pointer_atomic)));
        dbuckets = ceil(dtuples / NTUP_PER_BUCKET);
        dbuckets = Min(dbuckets, max_buckets);
        new_nbuckets = (int) dbuckets;
        new_nbuckets = Max(new_nbuckets, 1024); // 写死的 1024
        ······
        pstate->nbuckets = new_nbuckets;
    }

Batches：Hash 表插入内存所需要进行的插入次数，实际设计插入的数据量越大该值就越大。它的取值等于 Hash 表 / work_mem (配置文件)，如果此值大于 1 ，那么 postgres 就会通过 Hash Spill 做拆分。如果此值在确定正常业务下仍然很大，最好的办法就是调整 work_mem 的大小
loops：涉及循环数，如果是在 filed in 等场景下，此值可能会很大。

查询类型

Seq Scans (Sequential Scan)：全表扫描
Index Scans：索引扫描，经典的索引匹配，然后根据 rowId 回表查询获取目标列数据
Index Only Scan：索引覆盖扫描，跟Index Scans的区别在于它不需要再回表查询
Bitmap Heap Scan：位图堆扫描，当一个SQL查询可以用到多个索引或者某个查询即使使用了索引，仍然返回了大量的行，
使用索引找到所有匹配行的位置(生成一个位图)；然后根据该位图一次性、顺序的从主表之中获取所有必要行；对于多个索引条件组合或者索引选择性不高的场景性能提升很高。
Posgres 的执行器会在评估 Bitmap 的构建是否比普通通过Index查询更优后决定是否使用该查询方式
使用 Bitmap Heap Scan Postgres 会经过以下顺序：
1. Bitmap Index Scan：扫描多个索引，扫描索引不是为了获取目标数据，而是标记满足条件的数据，这一个过程在实际物理数据中表现出来的是构建出位图 Bitmap
2. Bitmap Heap Scan：从第一阶段产生的位图之中，按照实际物理位置按顺序、一次性的将目标行都取出来。
Foreign Scan：外部扫描，Postgres提供扩展扫描Spi，它允许我们直接通过 FDW (Foreign Data Wrapper) 对其他的外部系统做扫描，得到扫描的结果后，可以将这些结果交给 Postgres 处理。除非专门测试，否则几乎见不到

Tid Scan：直接对物理地址做扫描，几乎只有手动对 ctid 做查询才会出现

Tid 其实指代的是 postgres 的实际物理存储单位，每一行的数据其实都作为一个元组 (Tuple) 而存在，TID 指代的是该元组的实际物理地址，TID 由块编号(Blocking Number) 和偏移量(Item Pointer)组成。

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
SELECT ctid, id, name FROM users;
-- query result (ctid 其中 0 就代表着第 0 个块，偏移量分别是 1  2 3)
ctid   id   name
(0,1)	1	Alice
(0,2)	2	Bob
(0,3)	3	Charlie

-- 直接对 ctid 查询输出的执行计划
EXPLAIN ANALYZE
SELECT * FROM users WHERE ctid = '(0,2)';
-- query result
Tid Scan on users  (cost=0.00..4.01 rows=1 width=36) (actual time=0.004..0.005 rows=1 loops=1)
  TID Cond: (ctid = '(0,2)'::tid)
Planning Time: 0.077 ms
Execution Time: 0.020 ms

关联类型

Nested Loop Join：嵌套循环，经典的嵌套循环取笛卡尔积
Hash Join：哈希连接，在内存中构建Hash节点 (Hash 表)，然后根据哈希表来对另外一个大表做匹配。主要针对有一定数据量、无索引的表格。
实际在连表查询时，只要没有索引，几乎都会通过 Hash Json 来实现关联。
Merge Join：归并连接，关联的两个表都已经排序，将其直接合并，主要针对大数据集。

SQL Server

关于SQLServer介绍会比较少，以后可能考虑补充，但不是现在，根本原因是目前手上没有Vm或者服务器正在使用 SQLServer ，没办法拿到完整的测试结果和相关连接信息，但针对确认SQL等部分，可以参考以前写过的 blog ：数据库资源卡顿查询SQL - LeticiaFENG Note

确定SQL

SQLServer 提供了多个存储过程，在存储过程中可以以图表的方式来查看近期性能较差的SQL，他的本质是依托于自身自带的 “Activity Monitor” 和 Dynamic Management Views (DMVs) 实现的，我们的 SQL 查询会被记录到表格中，而在实际数据上将会直接显示到：sys.dm_exec_query_stats 这一视图上

优化SQL

在 SQLServer 之中甚至都不需要写 Explain ，可以直接通过图表化的方式查看执行计划，来知道具体性能差的步骤是哪一步。当然它实际上也有类似的操作符输出：

Cluster Index Scan：本质上就是全表查询，但是在有聚簇索引的情况下SQLServer会显示该类型
Table Scan(Heap)：在没有聚簇索引的情况下，SQLServer 将会显示该值
Index Seek：索引范围，并只对索引做范围扫描
Index Scan：索引扫描，对整个索引做扫描
Key LookUp：回表查询，命中索引但是索引只能提供 RowId，仍然需要依靠RowId回查表
RID LookUp：没有聚簇索引只能 Loop 整个表来找行数据

关联类型

关联类型部分几乎跟 Postgres 一致，只不过名字有所区别：

Nested Loops Join：嵌套循环
Hash Match Join：哈希连接
Merge Join：归并连接

Oracle

关于Oracle介绍也比较少，以后可能考虑补充，但不是现在，根本原因是目前手上没有Vm或者服务器正在使用 Oracle ，没办法拿到完整的测试结果和相关连接信息，但针对确认SQL等部分，可以参考以前写过的 blog ：数据库资源卡顿查询SQL - LeticiaFENG Note

确定SQL

Oracle 毕竟是老牌强大的数据库了，它提供了 AWR (Automatic Workload Repository) 报告，基于 ASH (Active Session History) 对当前活动会话按照每秒做采样的数据进行统计分析，AWR报告实际上按照小时作为单位，生成数据库性能报告，里面提供了一段："SQL ordered by Elapsed Time" 等分析报告，在其中直接列举出了耗时长的SQL。对于运维 / 开发人员来说，只需要从报告中即可直接获知具体原因，但值得一提的是，这个功能是企业版独有的，除非花米购买，不然往往只能考虑使用 “性能视图” 来进行分析。

在免费的版本下，我们也可以通过 VSQLAREA + VSQL 根据视图中的 CPU_TIME + ELAPSED_TIME 来获知具体消耗资源。

优化SQL

Orace的执行计划的输出和其他的数据库脚本有一些不一样，他的命令需要分为两步：

EXPLAIN PLAN FOR SELECT ······;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

而最终打印出来的信息也跟其他的数据库不太一样，在其他数据库来说被称为类型 type 的一栏，在 Oracle 中被实际称为 Operation 和 Access Method。针对 Operation 其实主要包含以下类型：

INDEX UNIQUE SCAN：根据唯一索引做排查
INDEX RANGE SCAN：索引范围扫描
INDEX FAST FULL SCAN：快一些的全索引扫描
INDEX FULL SCAN：全索引扫描
TABLE ACCESS BY INDEX ROW ID：索引后回表扫描
TABLE ACCESS FULL：全表扫描

Mysql

Mysql 在之前的笔记中有非常详细的介绍，这里就不进一步展开介绍了。有需要可以参考：SQL性能优化常见流程 - LeticiaFENG Note 现在回头看这个笔记还是有点问题的，没有将 Explain 、Extra 说明是 Mysql 下的情况存在歧义问题，但基本的描述和信息都是正确的

Type / Operation对比

针对 Type / Operation 我们可以单独做出以下图以供参考

数据库类型	Mysql	Postgres	SqlServer	Oracle
编译时常量化查询	system、const	(无特定标识)	(无特定标识)	(无特定标识)
运行期间物理地址查找 (可能意味着回表)	(无特定标识)	TID Scan	Key Lookup / RID Lookup	TABLE ACCESS BY INDEX ROWID
索引精确查找	eq_ref、ref、ref_or_null、unique_subquery、index_subquery	Index Scan 、Index Only Scan	Index Seek	INDEX UNIQUE SCAN
索引范围查找	range	Index Scan 、Index Only Scan	Index Seek	INDEX RANGE SCAN
多索引合并 / 多索引优化策略	index_merge、fulltext	Bitmap Heap Scan	没有任何标识	没有任何标识
全索引查找	index	Index Scan	Index Scan	INDEX FULL SCAN
全表查询	all	Seq Scan	Clustered Index Scan、Table Scan	TABLE ACCESS FULL

除此以外Mysql还有以外一些 extra 使我们需要注意的：using index、using where、using temporary、using filesort，具体含义可以跳转Mysql相关笔记

如果觉得文章对你有用，请随意赞赏

Postgres 数据库

数据库Explain对比

https://www.leticiafeng.com/archives/1c01fbe0-a2f1-4d34-ba65-ad00b14bba38

作者

LeticiaFENG

发布于

2025-09-20

更新于

2025-09-20

许可协议

CC BY 4.0

数据库Explain对比

数据库Explain对比

Postgres

确定SQL

优化SQL

Postgres Explain Demo

Postgres Explain Analyze Demo

Postgres Explain 解析参数及取值：

参数

查询类型

关联类型

SQL Server

确定SQL

优化SQL

关联类型

Oracle

确定SQL

优化SQL

Mysql

Type / Operation对比

作者

发布于

更新于

许可协议

评论