<< 返回文章列表

MogDB 数据库查看执行计划需要注意的要点

2024年7月30日
M
o
g
D
B
,
,
,
D
B
A
,
杨天成
27
使用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;",数据不会发生变化。