熟能生巧丨MogDB 数据库查看执行计划需要注意的要点

MogDB 2024-07-30

103

使用EXPLAIN将显示SQL语句的执行计划，可以看到表会采用什么样的扫描方式，如：简单的顺序扫描、索引扫描等。如果引用了多个表，执行计划还会显示用到的JOIN算法。

执行计划的最关键的部分是语句的预计执行开销，这是计划生成器估算执行该语句将花费多长的时间。

若指定了ANALYZE选项，则该语句会被执行，然后根据实际的运行结果显示统计数据，包括每个计划节点内时间总开销（毫秒为单位）和实际返回的总行数。这对于判断计划生成器的估计是否接近现实非常有用。

EXPLAIN用法

显示不同详细程度的执行计划信息，比较常见的有如下几种：

EXPLAIN statement：只生成执行计划，不实际执行。其中statement代表SQL语句。
EXPLAIN ANALYZE statement：生成执行计划，进行执行，并显示执行的概要信息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花掉的总时间（以毫秒计）和它实际返回的行数。
EXPLAIN PERFORMANCE statement：生成执行计划，进行执行，并显示执行期间的全部信息。

为了测量运行时在执行计划中每个节点的开销，EXPLAIN ANALYZE或EXPLAIN PERFORMANCE会在当前查询执行上增加性能分析的开销。在一个查询上运行EXPLAIN ANALYZE或EXPLAIN PERFORMANCE有时会比普通查询明显的花费更多的时间。超支的数量依赖于查询的本质和使用的平台。

因此，当定位SQL运行慢问题时，如果SQL长时间运行未结束，建议通过EXPLAIN命令查看执行计划，进行初步定位。如果SQL可以运行出来，则推荐使用EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看执行计划及其实际的运行信息，以便更精准地定位问题原因。

查看执行计划需要注意

在指定ANALYZE/PERFORMANCE选项时，语句会被执行。如果用户想使用EXPLAIN分析INSERT、UPDATE、DELETE、CREATE TABLE AS或EXECUTE语句，而不想改动数据（执行这些语句会影响数据），请使用如下方法：

START TRANSACTION;
EXPLAIN ANALYZE ...;
ROLLBACK;

下面进行测试，测试情况如下：

准备测试数据

MogDB=# create table tctest(
MogDB(# id int ,
MogDB(# info varchar(500),
MogDB(# val_int int,
MogDB(# val_float decimal(12,2),
MogDB(# crt_date date,
MogDB(# crt_time timestamp,
MogDB(# remark char(50)
MogDB(# )
MogDB-# WITH (autovacuum_enabled = off, toast.autovacuum_enabled = off,compression=no);
CREATE TABLE
MogDB=# 
MogDB=# 
MogDB=# alter table tctest add constraint tctest_pkey primary key (id);
NOTICE:  ALTER TABLE  ADD PRIMARY KEY will create implicit index "tctest_pkey" for table "tctest"
ALTER TABLE
MogDB=# create index idx1_tctest on  tctest (crt_date);
CREATE INDEX
MogDB=# create index idx2_tctest on  tctest (info);
CREATE INDEX
MogDB=# 
MogDB=# 
MogDB=# insert into tctest(id,info,val_int,val_float,crt_date,crt_time,remark)
MogDB-# select
MogDB-# generate_series(1,1999999999999999) id,
MogDB-# md5(random()::text) info,
MogDB-# generate_series(100,1999999999999999) val_int,
MogDB-# random()*(10^5) val_float,
MogDB-# sysdate crt_date,
MogDB-# now() crt_time,
MogDB-# 'first'||(random()*(10^3))::integer remark
MogDB-# limit 1500000;
INSERT 0 1500000
MogDB=# 
MogDB=# select * from tctest where id<10;
 id |               info               | val_int | val_float |      crt_date       |          crt_time          |                       remark                       
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
  1 | 477323c19c9d523e012bbe0282dfc8be |     100 |  18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739                                          
  2 | cf025a0ff205f620948cfbf8e510e3a1 |     101 |  31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323                                          
  3 | 2f155efb19290c3d5821cd35655f0ec6 |     102 |  14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42                                           
  4 | 279f92370d522fe69debd07ced9fa1d6 |     103 |  22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506                                          
  5 | c0606cc2c0ce4fdea05597669e386fb2 |     104 |   2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239                                          
  6 | a0d552ef5232d6f36d66058b393db78f |     105 |  44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403                                          
  7 | 91e7303fa8d9bf50c7131aa183059456 |     106 |  50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804                                          
  8 | 37b1d048d5b4c4f4a798145cedbdbf0e |     107 |  89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141                                          
  9 | a7a98ce07ffa883d77c017e6f8ff8f0e |     108 |  94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912                                          
(9 rows)

MogDB=#

测试数据准备完成。

查看执行计划

查看select语句的执行计划，对数据没有影响。

只生成执行计划，不实际执行

MogDB=# explain  select * from tctest where id<10;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Scan using tctest_pkey on tctest  (cost=0.00..8.44 rows=11 width=116)
   Index Cond: (id < 10)
(2 rows)

生成执行计划，且实际执行

MogDB=# explain analyze select * from tctest where id<10;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using tctest_pkey on tctest  (cost=0.00..8.44 rows=11 width=116) (actual time=0.007..0.011 rows=9 loops=1)
   Index Cond: (id < 10)
 Total runtime: 0.084 ms
(3 rows)

生成执行计划，且实际执行

MogDB=# explain performance select * from tctest where id<10;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tctest_pkey on public.tctest  (cost=0.00..8.44 rows=11 width=116) (actual time=0.007..0.009 rows=9 loops=1)
   Output: id, info, val_int, val_float, crt_date, crt_time, remark
   Index Cond: (tctest.id < 10)
   (Buffers: shared hit=4)
   (CPU: ex c/r=4043733957297190, ex row=9, ex cyc=36393605615674712, inc cyc=36393605615674712)
 Total runtime: 0.072 ms
(6 rows)

DML语句查看执行计划，如果不需要数据变化，注意在查看执行计划之前需要加上begin,查看完成后再执行rollback.

仅显示执行计划，数据没有发生变化。

MogDB=# explain  update  tctest set val_int=100 where id<10;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Update on tctest  (cost=0.00..8.44 rows=11 width=118)
   ->  Index Scan using tctest_pkey on tctest  (cost=0.00..8.44 rows=11 width=118)
         Index Cond: (id < 10)
(3 rows)

查看执行计划的数据，数据没有发生变化。

MogDB=# select * from tctest where id<10;
 id |               info               | val_int | val_float |      crt_date       |          crt_time          |                       remark                       
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
  1 | 477323c19c9d523e012bbe0282dfc8be |     100 |  18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739                                          
  2 | cf025a0ff205f620948cfbf8e510e3a1 |     101 |  31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323                                          
  3 | 2f155efb19290c3d5821cd35655f0ec6 |     102 |  14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42                                           
  4 | 279f92370d522fe69debd07ced9fa1d6 |     103 |  22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506                                          
  5 | c0606cc2c0ce4fdea05597669e386fb2 |     104 |   2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239                                          
  6 | a0d552ef5232d6f36d66058b393db78f |     105 |  44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403                                          
  7 | 91e7303fa8d9bf50c7131aa183059456 |     106 |  50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804                                          
  8 | 37b1d048d5b4c4f4a798145cedbdbf0e |     107 |  89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141                                          
  9 | a7a98ce07ffa883d77c017e6f8ff8f0e |     108 |  94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912                                          
(9 rows)

查看执行计划，实际执行了该语句

MogDB=# explain analyze update  tctest set val_int=100 where id<10;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Update on tctest  (cost=0.00..8.44 rows=11 width=118) (actual time=0.251..0.571 rows=9 loops=1)
   ->  Index Scan using tctest_pkey on tctest  (cost=0.00..8.44 rows=11 width=118) (actual time=0.011..0.027 rows=9 loops=1)
         Index Cond: (id < 10)
 Total runtime: 0.681 ms
(4 rows)

analyze 数据发生了变化。

MogDB=# select * from tctest where id<10;
 id |               info               | val_int | val_float |      crt_date       |          crt_time          |                       remark                       
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
  1 | 477323c19c9d523e012bbe0282dfc8be |     100 |  18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739                                          
  2 | cf025a0ff205f620948cfbf8e510e3a1 |     100 |  31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323                                          
  3 | 2f155efb19290c3d5821cd35655f0ec6 |     100 |  14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42                                           
  4 | 279f92370d522fe69debd07ced9fa1d6 |     100 |  22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506                                          
  5 | c0606cc2c0ce4fdea05597669e386fb2 |     100 |   2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239                                          
  6 | a0d552ef5232d6f36d66058b393db78f |     100 |  44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403                                          
  7 | 91e7303fa8d9bf50c7131aa183059456 |     100 |  50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804                                          
  8 | 37b1d048d5b4c4f4a798145cedbdbf0e |     100 |  89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141                                          
  9 | a7a98ce07ffa883d77c017e6f8ff8f0e |     100 |  94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912                                          
(9 rows)

查看执行计划，实际执行了该语句

MogDB=# explain performance update  tctest set val_int=200 where id<10;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Update on public.tctest  (cost=0.00..8.44 rows=11 width=118) (actual time=0.081..0.180 rows=9 loops=1)
   (Buffers: shared hit=30 dirtied=3)
   (CPU: ex c/r=-3639725859365662, ex row=9, ex cyc=-32757532734290960, inc cyc=3639725859461305)
   ->  Index Scan using tctest_pkey on public.tctest  (cost=0.00..8.44 rows=11 width=118) (actual time=0.013..0.068 rows=9 loops=1)
         Output: id, info, 200, val_float, crt_date, crt_time, remark, ctid
         Index Cond: (tctest.id < 10)
         (Buffers: shared hit=21 dirtied=2)
         (CPU: ex c/r=4044139843750251, ex row=9, ex cyc=36397258593752264, inc cyc=36397258593752264)
 Total runtime: 0.298 ms
(9 rows)

performance 数据发生了变化

MogDB=# select * from tctest where id<10;
 id |               info               | val_int | val_float |      crt_date       |          crt_time          |                       remark                       
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
  1 | 477323c19c9d523e012bbe0282dfc8be |     200 |  18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739                                          
  2 | cf025a0ff205f620948cfbf8e510e3a1 |     200 |  31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323                                          
  3 | 2f155efb19290c3d5821cd35655f0ec6 |     200 |  14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42                                           
  4 | 279f92370d522fe69debd07ced9fa1d6 |     200 |  22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506                                          
  5 | c0606cc2c0ce4fdea05597669e386fb2 |     200 |   2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239                                          
  6 | a0d552ef5232d6f36d66058b393db78f |     200 |  44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403                                          
  7 | 91e7303fa8d9bf50c7131aa183059456 |     200 |  50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804                                          
  8 | 37b1d048d5b4c4f4a798145cedbdbf0e |     200 |  89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141                                          
  9 | a7a98ce07ffa883d77c017e6f8ff8f0e |     200 |  94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912                                          
(9 rows)

如果不需要数据发生变化，查看执行计划之前需要加begin，查看完成后再执行rollback，数据不会发生变化。

MogDB=# 
MogDB=# begin;
BEGIN
MogDB=# explain analyze update  tctest set val_int=10000 where id<10;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Update on tctest  (cost=0.00..8.44 rows=11 width=118) (actual time=0.074..0.122 rows=9 loops=1)
   ->  Index Scan using tctest_pkey on tctest  (cost=0.00..8.44 rows=11 width=118) (actual time=0.012..0.021 rows=9 loops=1)
         Index Cond: (id < 10)
 Total runtime: 0.260 ms
(4 rows)

MogDB=# select * from tctest where id<10;
 id |               info               | val_int | val_float |      crt_date       |          crt_time          |                       remark                       
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
  1 | 477323c19c9d523e012bbe0282dfc8be |   10000 |  18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739                                          
  2 | cf025a0ff205f620948cfbf8e510e3a1 |   10000 |  31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323                                          
  3 | 2f155efb19290c3d5821cd35655f0ec6 |   10000 |  14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42                                           
  4 | 279f92370d522fe69debd07ced9fa1d6 |   10000 |  22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506                                          
  5 | c0606cc2c0ce4fdea05597669e386fb2 |   10000 |   2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239                                          
  6 | a0d552ef5232d6f36d66058b393db78f |   10000 |  44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403                                          
  7 | 91e7303fa8d9bf50c7131aa183059456 |   10000 |  50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804                                          
  8 | 37b1d048d5b4c4f4a798145cedbdbf0e |   10000 |  89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141                                          
  9 | a7a98ce07ffa883d77c017e6f8ff8f0e |   10000 |  94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912                                          
(9 rows)

MogDB=# 
MogDB=# rollback;
ROLLBACK
MogDB=# 
MogDB=# select * from tctest where id<10;
 id |               info               | val_int | val_float |      crt_date       |          crt_time          |                       remark                       
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
  1 | 477323c19c9d523e012bbe0282dfc8be |     200 |  18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739                                          
  2 | cf025a0ff205f620948cfbf8e510e3a1 |     200 |  31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323                                          
  3 | 2f155efb19290c3d5821cd35655f0ec6 |     200 |  14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42                                           
  4 | 279f92370d522fe69debd07ced9fa1d6 |     200 |  22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506                                          
  5 | c0606cc2c0ce4fdea05597669e386fb2 |     200 |   2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239                                          
  6 | a0d552ef5232d6f36d66058b393db78f |     200 |  44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403                                          
  7 | 91e7303fa8d9bf50c7131aa183059456 |     200 |  50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804                                          
  8 | 37b1d048d5b4c4f4a798145cedbdbf0e |     200 |  89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141                                          
  9 | a7a98ce07ffa883d77c017e6f8ff8f0e |     200 |  94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912                                          
(9 rows)

小结

该测试我们直观看到了，查看执行计划时指定ANALYZE或PERFORMANCE选项时，语句会被执行，DML语句会导致数据发生变化，不加ANALYZE或PERFORMANCE选项时，仅显示执行计划，DML语句不会导致数据变化。如果不需要数据发生变化，查看执行计划之前需要加"begin;"或者"start transaction;"，查看完成后再执行"rollback;"，数据不会发生变化。