ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

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

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

原创 国内数据库 作者：云和恩墨 时间：2024-08-12 15:43:46 0 删除 编辑
使用 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<110;

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<110;

QUERY PLAN                                  

-----------------------------------------------------------------------------

 Index Scan using tctest_pkey on tctest  (cost=0.00..8.44 rows=11 width=116)

   Index Cond: (id <1 10)

(2 rows)

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


MogDB=# explain analyze select * from tctest where id<110;

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 <1 10)

 Total runtime: 0.084 ms

(3 rows)

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


MogDB=# explain performance select * from tctest where id<110;

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 <1 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<110;

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 <1 10)

(3 rows)

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


MogDB=# select * from tctest where id<110;

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<110;

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 <1 10)

 Total runtime: 0.681 ms

(4 rows)

  • analyze 数据发生了变化。


MogDB=# select * from tctest where id<110;

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<110;

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 <1 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<110;

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<110;

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 <1 10)

 Total runtime: 0.260 ms

(4 rows)



MogDB=# select * from tctest where id<110;

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<110;

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;"，数据不会发生变化。


关于作者

杨天成，云和恩墨技术支持工程师，15年数据库运维管理经验，具有丰富的数据库管理经验， 曾服务于金融、保险、运营商等行业的业务关键系统，目前专注于 MogDB 数据库的研究和测试。


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29715045/viewspace-3024805/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 循序渐进丨MogDB 数据库v5.0之闪回DROP/TRUNCATE
下一篇： 循序渐进丨免密登录 openGauss / MogDB 的几种方式
请登录后发表评论 登录
全部评论
云和恩墨

注册时间：2014-06-19

  • 博文量
    183
  • 访问量
    346689

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593