精华内容
下载资源
问答
  • Oracle执行计划——查看执行计划的方法
    千次阅读
    2019-07-25 19:28:26

    文章转自:https://blog.csdn.net/sinat_28472983/article/details/82382239

     

    (1)什么是执行计划
    SQL是一种傻瓜式语言,每一个条件就是一个需求,访问的顺序不同就形成了不同的执行计划。Oracle必须做出选择,一次只能有一种访问路径。执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述


    (2)执行计划的选择

    通常一条SQL有多个执行计划,那我们如何选择?那种执行开销更低,就意味着性能更好,速度更快,我们就选哪一种,这个过程叫做Oracle的解析过程,然后Oracle会把更好的执行计划放到SGA的Shared Pool里,后续再执行同样的SQL只需在Shared Pool里获取就行了,不需要再去分析


    (3)执行计划选定依据

    根据统计信息来选择执行计划。


    (4)统计信息
    什么是统计信息: 记录数、块数等,具体查看dba_tables / dba_indexes


    (5)动态采样

    Oracle正常情况下会在每天的某段时间收集统计信息,对于新建的表,Oracl如何收集统计信息?采用动态采样。
    set autotrace on
    set linesize 1000
    --执行SQL语句
    --会出现dynamic sampling used for this statement(level=2)关键


     

    (一)六种执行计划

    Oracle提供了6种执行计划获取方法,各种方法侧重点不同:

    选择时一般遵循以下规则:
    1.如果sql执行很长时间才出结果或返回不了结果,用方法1:explain plan for
    2.跟踪某条sql最简单的方法是方法1:explain plan for,其次是方法2:set autotrace on
    3.如果相关察某个sql多个执行计划的情况,只能用方法4:dbms_xplan.display_cursor或方法6:awrsqrpt.sql
    4.如果sql中含有函数,函数中有含有sql,即存在多层调用,想准确分析只能用方法5:10046追踪
    5.想法看到真实的执行计划,不能用方法1:explain plan for和方法2:set autotrace on
    6.想要获取表被访问的次数,只能用方法3:statistics_level = all

    获取方法优点缺点

    [explain plan for] plsql按F5

     

    explain plan for select * from dual;

    select * from table(dbms_xplan.display());

    无需真正执行,快捷方便1.没有输出相关统计信息,例如产生了多少逻辑读,多少次物理读,多少次递归调用的情况;
    2.无法判断处理了多少行;
    3.无法判断表执行了多少次

    [set autotrace on]-sql*plus

     

    set autotrace on

    select * from dual;

    1.可以输出运行时的相关统计信息(产生多少逻辑读、多少次递归调用、多少次物理读等);

    2.虽然要等语句执行完才能输出执行计划,但是可以有traceonly开关来控制返回结果不打屏输出

    1.必须要等SQL语句执行完,才出结果

    2.无法看到表被访问了多少次;

    [statistics_level=all]

     

    alter session set statistics_level=all;

    select * from dual;

    select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,null,'allstats last'));

    1.可以清晰的从starts得出表被访问多少次;

    2.可以从E-Rows和A-Rows得到预测的行数和真实的行数,从而可以准确判断Oracle评估是否准确;

    3.虽然没有准确的输出运行时的相关统计信息,但是执行计划中的Buffers就是真实的逻辑读的数值;

    1.必须要等执行完后才能输出结果;

    2.无法控制结果打屏输出,不像autotrace可以设置traceonly保证不输出结果;

    3.看不出递归调用,看不出物理读的数值

    [dbms_xplan.display_cursor]

     

    select * from table( dbms_xplan.display_cursor('&sql_id') );

    1.知道sql_id即可得到执行计划,与explain plan for一样无需执行;

    2.可得到真实的执行计划

    1.没有输出运行的统计相关信息;

    2.无法判断处理了多少行;

    3.无法判断表被访问了多少次;

    [事件10046 trace]

     

    步骤1:alter session set events '10046 trace name context forever,level 12'; --开启追踪
    步骤2:执行sql语句;
    步骤3:alter session set events '10046 trace name context off'; --关闭追踪
    步骤4:找到跟踪后产生的文件(开启10046前先用‘ls -lrt’看一下文件,执行结束后再看哪个是多出来的文件即可)
    步骤5:tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

    1.可以看出sql语句对应的等待事件;

    2.如果函数中有sql调用,函数中有包含sql,将会被列出,无处遁形;

    3.可以方便的看处理的行数,产生的逻辑物理读;

    4.可以方便的看解析时间和执行时间;

    5.可以跟踪整个程序包

    1.步骤繁琐;

    2.无法判断表被访问了多少次;

    3.执行计划中的谓词部分不能清晰的展现出来

     

     

     


    附录:


    (0)Oracle如何收集统计信息

    ① Oracle会选择在一个特定的时间段收集表和索引的统计信息(默认周一至周五:22:00,周六周日:06:00),用户可自行调整,主要为了避开高峰期;
    ② 表与索引的分析有阈值限制,超过阈值才会自动进行分析。如果数据变化量不大,Oracle是不会去分析的;
    ③ 收集方式灵活。可针对分区表的某个分区进行,可采用并行机制来收集表和索引的信息;


    如何收集统计信息
    --收集表统计信息

     
    1. exec dbms_stats.gather_table_stats(ownname => 'AAA', tabname => 'TEST02',estimate_percent =>

    2. 10,method_opt => 'for all indexed columns');

    --收集索引统计信息

     
    1. exec dbms_stats.gather_index_stats(ownname => 'AAA',indname => 'ID_IDX',estimate_percent =>

    2. 10,degree => '4');

    --收集表与索引的统计信息

     
    1. exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'TEST02',estimate_percent =>

    2. 10,method_opt => 'for all indexed columns',cascade => true);


    (1)explain plan for

     
    1. SQL> show user

    2. USER 为 "HR"

    3. SQL> set linesize 1000

    4. SQL> set pagesize 2000

    5. SQL> explain plan for

    6. 2 select *

    7. 3 from employees,jobs

    8. 4 where employees.job_id=jobs.job_id

    9. 5 and employees.department_id=50;

    10. 已解释。

    11.  
    12. SQL> select * from table(dbms_xplan.display());

    13.  
    14. PLAN_TABLE_OUTPUT

    15. ------------------------------------------------------------------------------------------------------------------------

    16. ----------------------------------------------------

    17. Plan hash value: 303035560

    18. ------------------------------------------------------------------------------------------

    19. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    20. ------------------------------------------------------------------------------------------

    21. | 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |

    22. | 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |

    23. | 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |

    24. | 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |

    25. |* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |

    26. |* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |

    27. ------------------------------------------------------------------------------------------

    28. Predicate Information (identified by operation id):

    29. ---------------------------------------------------

    30. 4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")

    31. filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")

    32. 5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)

    33. 已选择19行。

    优点:无需真正执行,快捷方便
    缺点:1.没有输出相关统计信息,例如产生了多少逻辑读,多少次物理读,多少次递归调用的情况;
    2.无法判断处理了多少行;
    3.无法判断表执行了多少次
     


    (2)set autotrace on

    用法:
    命令作用
    SET AUTOT[RACE] OFF 停止AutoTrace
    SET AUTOT[RACE] ON 开启AutoTrace,显示AUTOTRACE信息和SQL执行结果
    SET AUTOT[RACE] TRACEONLY 开启AutoTrace,仅显示AUTOTRACE信息
    SET AUTOT[RACE] ON EXPLAIN 开启AutoTrace,仅显示AUTOTRACE的EXPLAIN信息
    SET AUTOT[RACE] ON STATISTICS 开启AutoTrace,仅显示AUTOTRACE的STATISTICS信息

     
    1. SQL> set autotrace on

    2. SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;

    3. --输出结果(略)

    4. -- ...

    5. 已选择45行。

    6.  
    7. 执行计划

    8. ----------------------------------------------------------

    9. Plan hash value: 303035560

    10. ------------------------------------------------------------------------------------------

    11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    12. ------------------------------------------------------------------------------------------

    13. | 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |

    14. | 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |

    15. | 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |

    16. | 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |

    17. |* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |

    18. |* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |

    19. ------------------------------------------------------------------------------------------

    20. Predicate Information (identified by operation id):

    21. ---------------------------------------------------

    22. 4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")

    23. filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")

    24. 5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)

    25. 统计信息

    26. ----------------------------------------------------------

    27. 0 recursive calls

    28. 0 db block gets

    29. 13 consistent gets

    30. 0 physical reads

    31. 0 redo size

    32. 5040 bytes sent via SQL*Net to client

    33. 433 bytes received via SQL*Net from client

    34. 4 SQL*Net roundtrips to/from client

    35. 1 sorts (memory)

    36. 0 sorts (disk)

    37. 45 rows processed

    优点:1.可以输出运行时的相关统计信息(产生多少逻辑读、多少次递归调用、多少次物理读等);
                2.虽然要等语句执行完才能输出执行计划,但是可以有traceonly开关来控制返回结果不打屏输出
    缺点:1.必须要等SQL语句执行完,才出结果;
                2.无法看到表被访问了多少次;


    (3)statistics_level=all

    步骤一:ALTER SESSION SET STATISTICS_LEVEL=ALL;
    步骤二:执行待分析的SQL
    步骤三:select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,null,'allstats last'));

     
    1. SQL> alter session set statistics_level=all;

    2. SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;

    3. --输出结果

    4. --...

    5. 已选择45行。

    6.  
    7. SQL> set linesize 1000

    8. SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

    9.  
    10. PLAN_TABLE_OUTPUT

    11. ------------------------------------------------------------------------------------------------------------------------

    12. -----------

    13. SQL_ID d8jzhcdwmd9ut, child number 0

    14. -------------------------------------

    15. select * from employees,jobs where employees.job_id=jobs.job_id and

    16. employees.department_id=50

    17. Plan hash value: 303035560

    18. ------------------------------------------------------------------------------------------------------------------------

    19. ----------------

    20. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |

    21. 1Mem | Used-Mem |

    22. ------------------------------------------------------------------------------------------------------------------------

    23. ----------------

    24. | 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 13 | 8 | |

    25. | |

    26. PLAN_TABLE_OUTPUT

    27. ------------------------------------------------------------------------------------------------------------------------

    28. -------------

    29. | 1 | MERGE JOIN | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | |

    30. | |

    31. | 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | |

    32. | |

    33. | 3 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | |

    34. | |

    35. |* 4 | SORT JOIN | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 |

    36. 6144 | 6144 (0)|

    37. |* 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | |

    38. | |

    39. ------------------------------------------------------------------------------------------------------------------------

    40. ----------------

    41. Predicate Information (identified by operation id):

    42. ---------------------------------------------------

    43. 4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")

    44. PLAN_TABLE_OUTPUT

    45. ------------------------------------------------------------------------------------------------------------------------

    46. -----

    47. filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")

    48. 5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)

    49. 已选择25行。

    关键字解读:
    1.starts:SQL执行的次数;
    2.E-Rows:执行计划预计返回的行数;
    3.R-Rows:执行计划实际返回的行数;
    4.A-Time:每一步执行的时间(HH:MM:SS.FF),根据这一行可知SQL耗时在哪些地方;
    5.Buffers:每一步实际执行的逻辑读或一致性读;
    6.Reads:物理读;

    优点:1.可以清晰的从starts得出表被访问多少次
                2.可以从E-Rows和A-Rows得到预测的行数和真实的行数,从而可以准确判断Oracle评估是否准确
                3.虽然没有准确的输出运行时的相关统计信息,但是执行计划中的Buffers就是真实的逻辑读的数值
    缺点:1.必须要等执行完后才能输出结果;
                2.无法控制结果打屏输出,不像autotrace可以设置traceonly保证不输出结果;
                3.看不出递归调用,看不出物理读的数值


    (4)dbms_xplan.display_cursor获取


    步骤1:select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到
    注释:
    1.还有1种方法,select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取
    2.如果有多个执行计划,可用以下方法查出:

     
    1. select * from table(dbms_xplan.display_cursor('&sql_id',0));

    2. select * from table(dbms_xplan.display_cursor('&sql_id',1));

    3. */

    4. SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));

    5. PLAN_TABLE_OUTPUT

    6. --------------------------------------------------------------------------------

    7. SQL_ID 5hkd01f03y43d, child number 0

    8. -------------------------------------

    9. select * from test where table_name = 'LOG$'

    10. Plan hash value: 2408911181

    11. --------------------------------------------------------------------------------

    12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

    13. --------------------------------------------------------------------------------

    14. | 0 | SELECT STATEMENT | | | | 2 (100)|

    15. | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)|

    16. |* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|

    17. --------------------------------------------------------------------------------

    18. Predicate Information (identified by operation id):

    19. ---------------------------------------------------

    20. 2 - access("TABLE_NAME"='LOG$')

    21. 19 rows selected

    注释:如何查看1个sql语句的sql_id,可直接查看v$sql

    优点:1.知道sql_id即可得到执行计划,与explain plan for一样无需执行
                2.可得到真实的执行计划
    缺点:1.没有输出运行的统计相关信息;
                2.无法判断处理了多少行;
                3.无法判断表被访问了多少次;
     


    (5)事件10046 trace跟踪


    步骤1:alter session set events '10046 trace name context forever,level 12'; --开启追踪
    步骤2:执行sql语句;
    步骤3:alter session set events '10046 trace name context off'; --关闭追踪
    步骤4:找到跟踪后产生的文件(开启10046前先用‘ls -lrt’看一下文件,执行结束后再看哪个是多出来的文件即可)
    步骤5:tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

    详细demo可见《附录1:10046追踪demo》

    优点:1.可以看出sql语句对应的等待事件;
                2.如果函数中有sql调用,函数中有包含sql,将会被列出,无处遁形;
                3.可以方便的看处理的行数,产生的逻辑物理读;
                4.可以方便的看解析时间和执行时间;
                5.可以跟踪整个程序包
    缺点:1.步骤繁琐;
                2.无法判断表被访问了多少次;
                3.执行计划中的谓词部分不能清晰的展现出来
     


    (6)awrsqrpt.sql


    步骤1:@?/rdbms/admin/awrsqrpt.sql
    步骤2:选择你要的断点(begin snap和end snap)
    步骤3:输入要查看的sql_id

    更多相关内容
  • MySQL——执行计划

    千次阅读 2021-01-18 19:45:54
    项目开发中,性能是我们比较关注的问题,特别是数据库的性能;作为一个开发,经常和SQL语句打交道...下面我们以MySQL5.7为例了解一下执行计划:注:文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划,...

    项目开发中,性能是我们比较关注的问题,特别是数据库的性能;作为一个开发,经常和SQL语句打交道,想要写出合格的SQL语句,我们需要了解SQL语句在数据库中是如何扫描表、如何使用索引的;

    MySQL提供explain/desc命令输出执行计划,我们通过执行计划优化SQL语句。

    下面我们以MySQL5.7为例了解一下执行计划:

    注:文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划,有不合理之处勿喷

    explain/desc 用法

    只需要在我们的查询语句前加 explain/desc即可

    准备数据表

    1 --创建user表

    2 create table user(3 id int,4 name varchar(20),5 role_id int,6 primary key(id)7 )engine=innodb default charset=utf8;8 --创建role表

    9 create tablerole(10 id int,11 name varchar(20),12 primary key(id)13 )engine=innodb default charset=utf8;

    查询,执行计划

    1 explain select * from user;

    11ea57bca755606a4427104ec35b9375.png

    执行计划输出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra,这些内容有什么意义,下面简单介绍一下

    explain/desc 输出详解

    一、id ,select 查询序列号

    1 id相同,从上往下一次执行;

    1 --左关联

    2 explain select * from user a left join user b on a.id=b.id;3 --右关联

    4 explain select * from user a right join user b on a.id=b.id;

    1808fa90bde37b8ce92cf44201a8f0da.png

    通过left join 和 right join 验证;id一样(注意执行计划的table列),left join 先扫描a表,再扫描b表;right join 先扫描b表,再扫描a表

    2 id不同,id越大优先级越高,越先被执行

    1 desc select * from user where role_id=(select id from role where name='开发');

    9cf15158f7e66c8924ab5edb7bb0e52f.png

    我们编写查询角色为开发的用户;可以知道先查询角色name为开发角色id,查询序列号为2;再根据角色id查询用户,查询序列号为1;

    二、select_type,查询语句类型

    (1) SIMPLE(简单SELECT,不使用UNION或子查询等)

    1 explain select * from user;

    11ea57bca755606a4427104ec35b9375.png

    (2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

    1 desc select * from user where role_id=(select id from role where name='开发');

    9cf15158f7e66c8924ab5edb7bb0e52f.png

    (3) UNION(UNION中的第二个或后面的SELECT语句)

    1 desc select * from user where name='Java' union select * from user where role_id=1;

    00576a524e492838c7f5b58ddf134799.png

    (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)

    1 desc select * from usera2 where id in(3 select b.id from user b where b.id=a.id union

    4 select c.id from role c where c.id=a.role_id5 );

    2fadfdd1950142780b7e250000f6a96a.png

    (5) UNION RESULT(UNION的结果)

    1 desc select * from user where name='Java' union select * from user where role_id=1;

    3217ad14b2942637b84eeec3a1094ca5.png

    (6) SUBQUERY(子查询中的第一个SELECT)

    1 desc select * from user where role_id=(select id from role where name='开发');

    9cf15158f7e66c8924ab5edb7bb0e52f.png

    (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)

    1 desc select * from user where role_id = ( select id from role where id=user.id );

    eba34c4df3e0abf2875685fb893988ce.png

    (8) DERIVED(派生/衍生表的SELECT, FROM子句的子查询)

    1 desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;

    d06fe6fde14f801fd087488ba9bbd84e.png

    (9) MATERIALIZED(物化子查询) 在SQL执行过程中,第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ,后续对子查询结果集的访问将直接通过临时表获得。

    (10) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

    51f74f88365506ffe5a1793363dbc1fd.png

    (11) UNCACHEABLE UNION(UNION查询的结果不能被缓存)

    15772caf82adaa1911322e576965c91e.png

    三、table,查询涉及的表或衍生表

    9cf15158f7e66c8924ab5edb7bb0e52f.png

    table分别user、role表

    四、partitions查询涉及到的分区

    创建分区表,

    1 --创建分区表,

    2 --按照id分区,id<100 p0分区,其他p1分区

    3 create table user_partitions (id intauto_increment,4 name varchar(12),primary key(id))5 partition byrange(id)(6 partition p0 values less than(100),7 partition p1 valuesless than maxvalue8 );

    64926b3a7b2409e6ad003d870625d18f.png

    1 desc select * from user_partitions where id>200;

    查询id大于200(200>100,p1分区)的记录,查看执行计划,partitions是p1,符合我们的分区规则

    38fac79f849654449ed1e3cafa556d7d.png

    五、type 提供了判断查询是否高效的重要依据依据

    通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等,下面简单介绍一下常用的type;

    (1)system: 表中只有一条数据,相当于系统表; 这个类型是特殊的 const 类型;

    (2)const: 主键或者唯一索引的常量查询,表格最多只有1行记录符合查询,通常const使用到主键或者唯一索引进行定值查询。

    主键

    1 --创建user表

    2 create table user(id int primary key, name varchar(20), role_id int);3 --插入一条记录

    4 insert into user values (1, 'a', 1);5 --按id查询

    6 desc select * from user where id=1;7 --按role_id查询

    8 desc select * from user where role_id=1;

    分别查看按id和按role_id查询的执行计划;发现按主键id查询,执行计划type为const

    4054e7ca6a97b5a00820f591fac2a1a7.png

    将主键设置为id和role_id

    1 --删除主键

    2 alter table user drop primary key;3 --设置主键id,role_id

    4 alter table user add primary key(id,role_id);5 --按照部分主键查询

    6 desc select * from user where id=1;7 --按照部分主键查询

    8 desc select * from user where role_id=1;9 --按照全部主键查询

    10 desc select * from user where id=1 and role_id=1;

    发现只有按照全部主键查询,执行计划type为const

    ae022afe9b65e3acfb9ff7d254c56d39.png

    唯一索引

    1 --删除主键

    2 alter table user drop primary key;3 --设置主键

    4 alter table user add primary key(id);5 --设置role_id为唯一索引

    6 alter table user add unique keyuk_role(role_id);7 --按照唯一索引查询

    8 desc select * from user where role_id=1;

    发现按role_id唯一索引查询;执行计划type为const

    7075f5cfb32425a322959d2545c25fc2.png

    普通索引

    1 --将role_id设置成普通索引

    2 --删除唯一索引

    3 alter table user drop indexuk_role;4 --设置普通索引

    5 alter table user add indexindex_role(role_id);6 --按照普通索引查询

    7 desc select * from user where role_id=1;

    发现按role_id普通索引查询;执行计划type为ref

    6e8bf27925a8ec7cfd9e6e256ee0cd7f.png

    const用于主键或唯一索引查询;将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时使用;与索引类型有关。

    (3)eq_ref: 除了system和const类型之外,效率最高的连接类型;唯一索引扫描,对于每个索引键,表中只有一条记录与之对应;常用于主键或唯一索引扫描

    准备数据

    1 --创建teacher表

    2 create table teacher( id int primary key, name varchar(20), tc_id int);3 --插入3条数据

    4 insert into teacher values (1,'a',1),(2,'b',2),(3,'c',3);5 --创建teacher_card表

    6 create table teacher_card( id int primary key, remark varchar(20) );7 --插入2条数据

    8 insert into teacher_card values (1,'aa'),(2,'bb');9 --关联查询,执行计划

    10 desc select * from teacher t join teacher_card tc on t.tc_id=tc.id where t.name='a';

    执行计划

    b879d4783578a3eaf2a4a33bbccfd53a.png

    根据上面的知识;可知id相同,由上至下依次执行,分析结果可知:

    先查询t表就是teacher表中name字段为a的记录,由于name字段没有索引,所以全表扫描(type:ALL),一共有3条记录,扫描了3行(rows:3),1条符合条件(filtered:33.33 1/3);

    再查询tc即teacher_card表使用主键和之前的t.tc_id关联;由于是关联查询,并且是通过唯一索引(主键)进行查询,仅能返回1或0条记录,所以type为eq_ref。

    1 --删除teacher_card主键

    2 alter table teacher_card drop primary key;3 --这是teacher_card.id为唯一索引

    4 alter table teacher_card add unique keyui_id(id);5 --关联查询,执行计划

    6 desc select * from teacher t join teacher_card tc on t.tc_id=tc.id where t.name='a';

    85a73fb16cba702fff6ba021101baec8.png

    分析结果,将teacher_card的id设置为唯一索引,type为eq_ref;满足仅能返回1或0条记录。

    1 --删除teacher_card唯一索引

    2 alter table teacher_card drop indexui_id;3 --设置teacher_card.id为普通索引

    4 alter table teacher_card add indexindex_id(id);5 --关联查询,执行计划

    6 desc select * from teacher t join teacher_card tc on t.tc_id=tc.id where t.name='a';

    32456fe63e3aeae95fb26406cf249109.png

    分析结果,将teacher_card的id设置为普通索引,type为ref;不满足仅能返回1或0条记录。

    equ_ref用于唯一索引查询,对每个索引键,表中只有一条或零条记录与之匹配。

    (4)ref:此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询(换句话说,连接不能基于键值选择单行,可能是多行)。

    1 --teacher.tc_id无索引,执行计划

    2 desc select * from teacher t join teacher_card tc on t.tc_id=tc.id where tc.remark='aa';3 --设置teacher.tc_id为普通索引

    4 alter table teacher add indexindex_tcid(tc_id);5 --teacher.tc_id有索引,执行计划

    6 desc select * from teacher t join teacher_card tc on t.tc_id=tc.id where tc.remark='aa';

    2bd6d006f17ac48a41ac93358b664801.png

    先查询tc表就是teacher_card表中remark字段为aa的记录,由于remark字段没有索引,所以全表扫描(type:ALL),一共有2条记录,扫描了2行(rows:2),1条符合条件(filtered:50,1/2);

    tc_id无索引 再查询t即teacher表使用tc_id和之前的tc.id关联;由于是关联查询,不是索引,全表扫描,所以type为ALL。

    tc_id有索引 再查询t即teacher表使用tc_id和之前的tc.id关联;由于是关联查询,索引扫描,能返回0或1或多条记录,所以type为ref。

    (5)range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, , BETWEEN, IN() 操作中。

    1 desc select * from teacher where id>2;2 desc select * from teacher where id in (1,2,3);

    ad012f92198327d436d6a01d0c619c5f.png

    (6)index: 扫描索引树

    如果索引是复合索引,并且复合索引列满足select所需的所有数据,则仅扫描索引树。在这种情况下,Extra为Using index。仅索引扫描通常比ALL索引的大小通常小于表数据更快。

    索引列不满足select所需的所有数据,此时需要回表扫描;按索引顺序查找数据行。Uses index没有出现在Extra列中。

    1 --查看teacher表索引

    2 show index fromteacher;3 --查询tc_id,执行计划

    4 desc select tc_id fromteacher;5 --按tc_id索引分组,执行计划

    6 desc select name from teacher group by tc_id;

    7985d19bcaf65cfff93cec06c5fc8ab1.png

    查询tc_id,扫描索引树,type为index,Extra为 Using index;

    按tc_id分组,全表扫描,以按索引顺序查找数据行。

    (7)ALL: 全表扫描,没有任何索引可以使用时。这是最差的情况,应该避免。

    1 --查看teacher表索引

    2 show index fromteacher;3 desc select * from teacher where name='a';

    9ab283bb3982f0580b91ac2fe207bc33.png

    由于name字段不存在索引,type:ALL全表扫描;可通过对name字段设置索引进行优化。

    六、possible_keys:指示MySQL可以从中选择查找此表中的行的索引。

    七、key:MySQL查询实际使用到的索引。

    1 --创建course表

    2 create table course(id int primary key,name varchar(20),t_id int,key index_name(name),keyindex_tid(t_id));3 --插入数据

    4 insert into course values (1,'Java',1), (2,'Python',2);5 --查询1

    6 desc select * from course where name='Java' or t_id=1;7 --查询2

    8 desc select * from course where name='Java';

    查看执行计划

    6f6c3117a923dbc1d9a929286f07caa4.png

    查询1,查询name为Java或t_id为1的记录;可能用到的索引possible_keys为index_name,index_tid;实际用到的索引key为NULL

    查询2,查询name为Java;可能用到的索引possible_keys为index_name;实际用到的索引key为index_name

    八、key_len:表示索引中使用的字节数(只计算利用索引作为index key的索引长度,不包括用于group by/order by的索引长度)

    一般地,key_len 等于索引列类型字节长度,例如int类型为4 bytes,bigint为8 bytes;

    如果是字符串类型,还需要同时考虑字符集因素,例如utf8字符集1个字符占3个字节,gbk字符集1个字符占2个字节

    若该列类型定义时允许NULL,其key_len还需要再加 1 bytes

    若该列类型为变长类型,例如 VARCHAR(TEXT\BLOB不允许整列创建索引,如果创建部分索引也被视为动态列类型),其key_len还需要再加 2 bytes

    字符集会影响索引长度、数据的存储空间,为列选择合适的字符集;变长字段需要额外的2个字节,固定长度字段不需要额外的字节。而null都需要1个字节的额外空间,所以以前有个说法:索引字段最好不要为NULL,因为NULL让统计更加复杂,并且需要额外一个字节的存储空间。

    1 --key_len的长度计算公式:

    2

    3 --varchar(len)变长字段且允许NULL : len*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

    4 --varchar(len)变长字段且不允许NULL : len*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)

    5

    6 --char(len)固定字段且允许NULL : len*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)

    7 --char(len)固定字段且不允许NULL : len*(Character Set:utf8=3,gbk=2,latin1=1)

    下面验证一下结论:

    1 --创建user_info表

    2 create table user_info( id int comment '主键',3 name varchar(10) character set utf8 not null comment '姓名',4 alias_name varchar(10) character set utf8 comment '姓名',5 role_id int comment '角色id',6 remark varchar(10) character set gbk not null comment '备注',7 primary key(id),8 keyindex_name(name),9 keyindex_alias(alias_name),10 keyindex_role(role_id),11 keyindex_remark(remark)12 )engine=innodb;13 --插入数据

    14 insert into user_info values (1,'a','aa',1,'aaa');15 --按主键查询

    16 desc select * from user_info where id=1;17 --按索引role_id查询

    18 desc select * from user_info where role_id=1;

    8f85f34614b97b5655d87c407d48aea3.png

    按照主键id查询possible_keys为primary,实际用到的索引key为primary,key_len为4;

    按照索引role_id查询possible_keys为index_role,实际用到的索引key为index_role,key_len为5;

    分析结果:按照role_id比按照id(均为int类型)的key_len大5-4=1,因为role_id可以为null,需要一个标志位;

    1 --按照name查询 varchar(10) not null utf8 一个字符占3个字节 10*3+2(变长)=32

    2 desc select * from user_info where name='a';3 --按照alias_name查询 varchar(10) utf8 一个字符占3个字节 10*3+2(变长)+1(null标志位)=33

    4 desc select * from user_info where alias_name='aa';

    0b7ddd5a5ac11b4bf60c2d4b73c6c81b.png

    按照name查询possible_keys为index_name,实际用到的索引key为index_name,key_len为32=10*3+2(变长);

    按照alias_name查询possible_keys为index_alias,实际用到的索引key为index_alias,key_len为33=10*3+2(变长)+1(null标志位);

    分析结果:name与remark均为变长且字符集一致,remark可以为null,33-32=1多占一个标志位;

    1 --按照name查询 varchar(10) not null utf8 一个字符占3个字节 10*3+2(变长)=32

    2 desc select * from user_info where name='a';3 --按照remark查询 varchar(10) not null gbk 一个字符占2个字节 10*2+2(变长)=22

    4 desc select * from user_info where remark='aaa';

    9a0945b24921426e42be7dfb599d666c.png

    按照name查询possible_keys为index_name,实际用到的索引key为index_name,key_len为32=10*3(utf8一个字符3个字节)+2(变长);

    按照remark查询possible_keys为index_remark,实际用到的索引key为index_remark,key_len为22=10*2(gbk一个字符2个字节)+2(变长);

    分析结果:name与remark均为变长但字符集不一致,分别为utf8与gbk;符合公式;

    1 --将name修改为char(10) 定长 character set utf8 not null

    2 alter table user_info modify name char(10) character set utf8 not null;3 --按照name查询 varchar(10) not null utf8 一个字符占3个字节 10*3=30

    4 desc select * from user_info where name='a';

    a61a20710d4b41b1a58d55bc43eb2369.png

    按照name查询possible_keys为index_name,实际用到的索引key为index_name,key_len为30;

    因为将name修改为char(10) 定长 character set utf8 not null,10*3=30;符合公式

    九、ref:显示该表的索引字段关联了哪张表的哪个字段

    1 desc select * from user,role where user.role_id=role.id;

    ca5f54b10938d78207ab6e3eb3350460.png

    通过执行计划可知,role表执行计划ref为study.user.role_id;说明role.id关联user.role_id;

    十、rows:根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好

    十一、filtered:返回结果的行数占读取行数的百分比,值越大越好

    --查看teacher数据

    select * fromteacher;--查看teacher_card数据

    select * fromteacher_card;--查询语句

    select * from teacher t join teacher_card tc on t.tc_id=tc.id where t.name='a';--执行计划

    desc select * from teacher t join teacher_card tc on t.tc_id=tc.id where t.name='a';

    a7e8a4da831f47278ad98fe0d8cdef78.png

    根据上面的知识;可知id相同,由上至下依次执行,分析结果可知:

    先查询t表就是teacher表中name字段为a的记录,由于name字段没有索引,所以全表扫描(type:ALL),一共有3条记录,扫描了3行(rows:3),1条符合条件(filtered:33.33 1/3);

    再查询tc即teacher_card表使用主键和之前的t.tc_id关联;扫描索引(type:ref),返回1条记录,最终返回1条记录,(filtered:100 1/1)。

    十二、extra:包含不适合在其他列中显示但十分重要的额外信息。常见的值如下

    use filesort:MySQL会对数据使用非索引列进行排序,而不是按照索引顺序进行读取;若出现改值,应优化索引

    1 --查看user索引

    2 show index from user;3 --查询name并排序

    4 desc select name from user order byname;5 --为name列设置索引,优化

    6 alter table user add indexindex_name(name);7 --查询name并排序

    8 desc select name from user order by name;

    4be81f8a6487f48f01ccf62796a566f6.png

    use temporary:使用临时表保存中间结果,比如,MySQL在对查询结果排序时使用临时表,常见于order by和group by;若出现改值,应优化索引

    use index:表示select操作使用了索引覆盖,避免回表访问数据行,效率不错

    use where:where子句用于限制哪一行

    1 --创建student表

    2 create tablestudent(3 id int,4 first_name varchar(10),5 last_name varchar(10),6 primary key(id),7 keyindex_first(first_name)8 )engine=innodb default charset=utf8;9 --插入数据

    10 insert into student values (1,'a','b');11 --按照first_name查找

    12 desc select first_name,last_name from student where first_name='a';13 --设置first_name,last_name复合索引

    14 alter table student drop indexindex_first;15 alter table student add indexindex_name(first_name,last_name);16 --按照first_name查找

    17 desc select first_name,last_name from student where first_name='a';

    b8a2f996ccb1bff7d35ed08ca6fc0f07.png

    分析结果:

    当设置first_name为普通索引(单列索引),按照first_name查询;type:ref、possible_keys:indes_first、key:indes_first、extra:null,用到索引;

    当设置first_name,last_name为复合索引(联合索引),按照first_name查询;type:ref、possible_keys:indes_name、key:indes_name、extra:Using index;type:ref用到索引,因为是复合索引不需要回表扫描,extra:Using index索引覆盖;注意此时key_len为33=10*3(utf8)+2(变长)+1(null标志位),用到了复合索引的一部分即first_name

    当设置first_name,last_name为复合索引(联合索引),按照last_name查询;type:index、possible_keys:null、key:indes_name、extra:Using where,Using index;type:index而不是ref,扫描索引树,复合索引的最左原则;此时key_len为66=10*3(utf8)+2(变长)+1(null)+10*3(utf8)+2(变长)+1(null标志位);Using where应where子句进行限制

    小结:

    根据MySQL执行计划的输出,分析索引使用情况、扫描的行数可以预估查询效率;进而可以重构SQL语句、调整索引,提升查询效率。

    本文只是简单介绍一下MySQL执行计划,想全面深入了解MySQL,可优先阅读MySQL官方手册。

    展开全文
  • PostgreSQL执行计划

    千次阅读 2019-06-14 19:15:35
    在PostgreSQL的执行计划中,是自上而下阅读的,通常执行计划会有相关的索引来表示不同的计划节点,其中计划节点类型分为四类:控制节点(Control Node),扫描节点(Scan Node),物化节点(Materialization Node)...

    简介

    PostgreSQL是“世界上最先进的开源关系型数据库”。因为出现较晚,所以客户人群基数较MySQL少,但是发展势头很猛,最大优势是完全开源。

    MySQL是“世界上最流行的开源关系型数据库”。当前客户基数大,随着被Oracle收购,开源程度减小,尤其是近期单独拉了免费的MariaDB分支,更表明MySQL有闭源的倾向;

    至于两者孰优孰劣,不是本文要讨论的重点,在一般的使用中,没什么大的差别,下面我们只讨论PG中执行计划。

    执行计划

    pg在查询规划路径过程中,查询请求的不同执行方案是通过建立不同的路径来表达的,在生成较多符合条件的路径之后,要从中选择出代价最小的路径,把它转化为一个执行计划,传递给执行器执行。那么如何生成最小代价的计划呢?基于统计信息估计计划中各个节点的成本,其中与之相关的参数如下所示:

    计算代价:

    # 估算代价:
    total_cost = seq_page_cost * relpages + cpu_tuple_cost *  reltuples
    
    
    # 有时我们不想用系统默认的执行计划,这时可以通过禁止/开启某种运算的语法来强制控制执行计划:
    enable_bitmapscan = on
    enable_hashagg = on
    enable_hashjoin = on
    enable_indexscan = on #索引扫描
    enable_indexonlyscan = on #只读索引扫描
    enable_material = on #物化视图
    enable_mergejoin = on
    enable_nestloop = on
    enable_seqscan = on
    enable_sort = on
    enable_tidscan = on
    
    # 按照上面扫描方式并过滤代价:
    Cost = seq_page_cost * relpages + cpu_tuple_cost *  reltuples + cpu_operation_cost * reltuples

    每个SQL语句都会有自己的执行计划,我们可以使用explain指令获取执行计划,语法如下:

    nsc=# \h explain;
    Command:     EXPLAIN
    Description: show the execution plan of a statement
    Syntax:
    EXPLAIN [ ( option [, ...] ) ] statement
    EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
    
    where option can be one of:
    
        ANALYZE [ boolean ]  -- 是否真正执行,默认false
        VERBOSE [ boolean ]  -- 是否显示详细信息,默认false
        COSTS [ boolean ]    -- 是否显示代价信息,默认true
        BUFFERS [ boolean ]  -- 是否显示缓存信息,默认false,前置事件是analyze
        TIMING [ boolean ]   -- 是否显示时间信息
        FORMAT { TEXT | XML | JSON | YAML }  -- 输格式,默认为text

    如下图所示,cost是比较重要的指标,cost=1000.00..1205.30,执行sql代价,分为两个部分,前一部分表示启动时间(startup)是1000ms,执行到返回第一行时需要的cost值,后一部分表示总时间(total)是1205.30ms,执行整个SQL的cost。rows表示预测的行数,与实际的记录数可能有出入,数据库经常vacuum或analyze,该值越接近实际值。width表示查询结果的所有字段的总宽度为285个字节。

    可以在explain后添加analyze关键字来通过执行这个SQL获得真实的执行计划和执行时间,actual time中的第一个数字表示返回第一行需要的时间,第二个数字表示执行整个sql花费的时间。loops为该节点循环次数,当loops大于1时,总成本为:actual time * loops

    执行计划节点类型

    在PostgreSQL的执行计划中,是自上而下阅读的,通常执行计划会有相关的索引来表示不同的计划节点,其中计划节点类型分为四类:控制节点(Control Node),扫描节点(Scan Node),物化节点(Materialization Node),连接节点(Join Node)。

    控制节点:append,组织多个字表或子查询的执行节点,主要用于union操作。

    扫描节点:用于扫描表等对象以获取元组

       Seq Scan(全表扫描):把表的所有数据块从头到尾读一遍,筛选出符合条件的数据块;

       Index Scan(索引扫描):为了加快查询速度,在索引中找到需要的数据行的物理位置,再到表数据块中把对应数据读出来,如B树,GiST,GIN,BRIN,HASH

       Bitmap Index/Heap Scan(位图索引/结果扫描):把满足条件的行或块在内存中建一个位图,扫描完索引后,再根据位图列表的数据文件把对应的数据读出来,先通过Bitmap Index Scan在索引中找到符合条件的行,在内存中建立位图,之后再到表中扫描Bitmap Heap Scan。

    物化节点:能够缓存执行结果到缓存中,即第一次被执行时生成的结果元组缓存,等待上层节点使用,例如,sort节点能够获取下层节点返回的所有元组并根据指定的属性排序,并将排序结果缓存,每次上层节点取元组时就从缓存中按需读取。

       Materialize:对下层节点返回的元组进行缓存(如连接表时)

       Sort:对下层返回的节点进行排序(如果内存超过iwork_mem参数指定大小,则节点工作空间切换到临时文件,性能急剧下降)

       Group:对下层排序元组进行分组操作

       Agg:执行聚集函数(sum/max/min/avg)

    条件过滤,一般在where后加上过滤条件,当扫描数据行时,会找出满足过滤条件的行,条件过滤在执行计划里面显示Filter,如果条件的列上面有索引,可能会走索引,不会走过滤。

    连接节点:对应于关系代数中的连接操作,可以实现多种连接方式(条件连接/左连接/右连接/全连接/自然连接)

       Nestedloop Join(嵌套连接): 内表被外表驱动,外表返回的每一行都要在内表中检索找到与它匹配的行,因此整个查询返回的结果集不能太大,要把返回子集较小的表作为外表,且内表的连接字段上要有索引。 执行过程为,确定一个驱动表(outer table),另一个表为inner table,驱动表中每一行与inner table中的相应记录关联;

       Hash Join(哈希连接):优化器使用两个比较的表,并利用连接属性在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行;

       Merge Join(合并连接):通常hash连接的性能要比merge连接好,但如果源数据上有索引,或结果已经被排过序,这时merge连接性能会优于hash连接;

    运算类型(explain)

    运算类型操作说明是否有启动时间
    Seq Scan顺序扫描表无启动时间
    Index Scan索引扫描无启动时间
    Bitmap Index Scan索引扫描有启动时间
    Bitmap Heap Scan索引扫描有启动时间
    Subquery Scan子查询无启动时间
    Tid Scan行号检索无启动时间
    Function Scan函数扫描无启动时间
    Nested Loop Join嵌套连接无启动时间
    Merge Join合并连接有启动时间
    Hash Join哈希连接有启动时间
    Sort排序(order by)有启动时间
    Hash哈希运算有启动时间
    Result函数扫描,和具体的表无关无启动时间
    Uniquedistinct/union有启动时间
    Limitlimit/offset有启动时间
    Aggregatecount, sum,avg等聚集函数有启动时间
    Groupgroup by有启动时间
    Appendunion操作无启动时间
    Materialize子查询有启动时间
    SetOpintersect/except有启动时间

    示例讲解

    慢sql如下:

    SELECT
    	te.event_type,
    	sum(tett.feat_bytes) AS traffic
    FROM t_event te
    LEFT JOIN t_event_traffic_total tett
    ON tett.event_id = te.event_id
    WHERE
    	((te.event_type >= 1 AND te.event_type <= 17) OR (te.event_type >= 23 AND te.event_type <= 26) OR (te.event_type >= 129 AND te.event_type <= 256))
    AND te.end_time >= '2017-10-01 09:39:41+08:00'
    AND te.begin_time <= '2018-01-01 09:39:41+08:00'
    AND tett.stat_time >= '2017-10-01 09:39:41+08:00'
    AND tett.stat_time < '2018-01-01 09:39:41+08:00'
    GROUP BY te.event_type
    ORDER BY total_count DESC
    LIMIT 10

    耗时:约4s

    作用:事件表和事件流量表关联,查出一段时间内按照总流量大小排列的TOP10事件类型

    记录数:

    select count(1) from t_event;  -- 535881条
    select count(1) from t_event_traffic_total; -- 2123235条

    结果:

    event_type    traffic
    17	2.26441505638877E17
    2	2.25307250128674E17
    7	1.20629298837E15
    26	285103860959500
    1	169208970599500
    13	47640495350000
    6	15576058500000
    3	12671721671000
    15	1351423772000
    11	699609230000

    执行计划:

    Limit  (cost=5723930.01..5723930.04 rows=10 width=12) (actual time=3762.383..3762.384 rows=10 loops=1)
      Output: te.event_type, (sum(tett.feat_bytes))
      Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
      ->  Sort  (cost=5723930.01..5723930.51 rows=200 width=12) (actual time=3762.382..3762.382 rows=10 loops=1)
            Output: te.event_type, (sum(tett.feat_bytes))
            Sort Key: (sum(tett.feat_bytes))
            Sort Method: quicksort  Memory: 25kB
            Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
            ->  HashAggregate  (cost=5723923.69..5723925.69 rows=200 width=12) (actual time=3762.360..3762.363 rows=18 loops=1)
                  Output: te.event_type, sum(tett.feat_bytes)
                  Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
                  ->  Merge Join  (cost=384982.63..4390546.88 rows=266675361 width=12) (actual time=2310.395..3119.886 rows=2031023 loops=1)
                        Output: te.event_type, tett.feat_bytes
                        Merge Cond: (te.event_id = tett.event_id)
                        Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
                        ->  Sort  (cost=3284.60..3347.40 rows=25119 width=12) (actual time=21.509..27.978 rows=26225 loops=1)
                              Output: te.event_type, te.event_id
                              Sort Key: te.event_id
                              Sort Method: external merge  Disk: 664kB
                              Buffers: shared hit=652, temp read=84 written=84
                              ->  Append  (cost=0.00..1448.84 rows=25119 width=12) (actual time=0.027..7.975 rows=26225 loops=1)
                                    Buffers: shared hit=652
                                    ->  Seq Scan on public.t_event te  (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)
                                          Output: te.event_type, te.event_id
                                          Filter: ((te.end_time >= '2017-10-01 09:39:41+08'::timestamp with time zone) AND (te.begin_time <= '2018-01-01 09:39:41+08'::timestamp with time zone) AND (((te.event_type >= 1) AND (te.event_type <= 17)) OR ((te.event_type >= 23) AND (te.event_type <= 26)) OR ((te.event_type >= 129) AND (te.event_type <= 256))))
                                    ->  扫描子表过程,省略...
                        ->  Materialize  (cost=381698.04..392314.52 rows=2123296 width=16) (actual time=2288.881..2858.256 rows=2123235 loops=1)
                              Output: tett.feat_bytes, tett.event_id
                              Buffers: shared hit=1247 read=16463, temp read=21469 written=21469
                              ->  Sort  (cost=381698.04..387006.28 rows=2123296 width=16) (actual time=2288.877..2720.994 rows=2123235 loops=1)
                                    Output: tett.feat_bytes, tett.event_id
                                    Sort Key: tett.event_id
                                    Sort Method: external merge  Disk: 53952kB
                                    Buffers: shared hit=1247 read=16463, temp read=21469 written=21469
                                    ->  Append  (cost=0.00..49698.20 rows=2123296 width=16) (actual time=0.026..470.610 rows=2123235 loops=1)
                                          Buffers: shared hit=1247 read=16463
                                          ->  Seq Scan on public.t_event_traffic_total tett  (cost=0.00..0.00 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
                                                Output: tett.feat_bytes, tett.event_id
                                                Filter: ((tett.stat_time >= '2017-10-01 09:39:41+08'::timestamp with time zone) AND (tett.stat_time < '2018-01-01 09:39:41+08'::timestamp with time zone))
                                          ->  扫描子表过程,省略...
    Total runtime: 3771.346 ms

    执行计划解读:

    第40->30行:通过结束时间上创建的索引,顺序扫描t_event_traffic_total表,根据时间跨度三个月过滤出符合条件的数据,共2123235条记录;

    第26->21行:根据时间过滤出t_event表中符合条件的记录,共26225条记录;

    第30->27行:根据流量大小排序,执行sort操作;

    第12->09行:两个表执行join操作,执行完记录200条;

    第08->04行:对最终的200条记录按照大小排序;

    第01行:执行limit取10条记录。

    整个执行计划中花时间最长的是根据时间条件过滤t_event_traffic_total表,因为字表较多,记录较多,导致花费2.8s之多,所以我们优化的思路就比较简单了,直接根据actual time,花费较多的子表去查看表中是否有索引,以及记录是不是很多,有没有优化的空间,而经过排查,发现一个子表中的数据量达到1531147条。

    展开全文
  • SQL Server查询执行计划–查看计划

    千次阅读 2020-07-16 04:48:20
    In this article, we will focus on the methods for opening the plans, both actual and estimated ones 在“ SQL Server查询执行计划-基础知识”中 ,我们描述了SQL Server中的查询执行计划以及为什么它们对性能...

    In the SQL Server query execution plans – Basics, we described the query execution plans in SQL Server and why they are important for performance analysis. In this article, we will focus on the methods for opening the plans, both actual and estimated ones

    在“ SQL Server查询执行计划-基础知识”中 ,我们描述了SQL Server中的查询执行计划以及为什么它们对性能分析很重要。 在本文中,我们将重点介绍开放计划的方法,包括实际的和估计的方法。

    If you look at the Query item in the SQL Server Management Studio menu, you’ll see two options related to query plans – Display Estimated Execution plan and Include Actual Execution plan

    如果您查看SQL Server Management Studio菜单中的“ 查询”项,您将看到与查询计划相关的两个选项-“ 显示估计的执行计划”和“ 包括实际的执行计划”

    An estimated execution plan is a SQL Server query plan that is generated without actually running the query (or stored procedure) the plan is created for. It’s based on estimation of expected behavior. It’s useful for analyzing how a query would behave, without actually running it. This is very useful for testing purposes in environments where performance should not be affected by running actual code (e.g. running a SELECT statement with complex joins against huge tables), or when running code is not possible due to data changes it makes (e.g. executing an UPDATE). Its downside is that it might be inaccurate in some scenarios

    估计执行计划是一种SQL Server查询计划,该计划是在不实际运行为其创建查询的查询(或存储过程)的情况下生成的。 它基于对预期行为的估计。 这对于分析查询的行为很有用,而无需实际运行它。 这对于在以下环境中进行测试的目的非常有用:在这些环境中,运行实际代码不会影响性能(例如,对大型表运行带有复杂联接的SELECT语句),或者由于数据更改而无法运行代码(例如,执行更新)。 它的缺点是在某些情况下可能不准确

    An actual execution plan is the SQL Server query plan that is generated after a query was executed. It’s more reliable, as it’s based on the actual execution, not estimates. It also provides more information and statistics, therefore it’s much useful in troubleshooting

    实际的执行计划是在执行查询后生成SQL Server查询计划。 它更可靠,因为它基于实际执行,而不是估计。 它还提供更多的信息和统计信息,因此在故障排除中非常有用

    There are several methods available for viewing a query plan in SQL Server

    有几种方法可用于查看SQL Server中的查询计划

    使用ApexSQL计划查看实际执行计划 (View Actual execution plan using ApexSQL Plan)

    The Actual execution plan option is available in the in the Home tab of ApexSQL Plan

    ApexSQL Plan的“主页”选项卡中的“ 实际执行计划”选项可用

    1. Query in ApexSQL Plan查询
    2. Actual execution plan option实际执行计划选项


    SELECT *
      FROM person.PersonPhone
      WHERE PhoneNumber LIKE '%697%'
    

    If multiple SQL queries are executed, their plans will be listed in the same tab, one below another separated as statements.

    如果执行了多个SQL查询,则它们的计划将列在同一选项卡中,一个在另一个选项中以语句分开。

    Each item in the query plan shows a tooltip with additional information

    查询计划中的每个项目都会显示一个工具提示,其中包含其他信息

    Dialog showing a tooltip with additional information for each item in the query plan

    SQL Server execution plans can be saved as SQL or sqlplan files for later analysis.

    可以将SQL Server执行计划另存为SQL或sqlplan文件,以供以后分析。

    The steps are similar for using the Estimated execution Plan option, except the query doesn’t have to be executed.

    步骤与使用“ 估计执行计划”选项相似,不同之处在于不必执行查询。

    SQL Server query plans can also be shown in Query Editor using some of the following options:

    还可以使用以下某些选项在查询编辑器中显示SQL Server查询计划:

    使用ApexSQL计划查看估计的执行计划 (View Estimated execution plan using ApexSQL Plan)

    The Estimated execution plan option is also available in the in the Home ribbon bar of ApexSQL Plan

    ApexSQL Plan的主页功能区栏中的“ 估计执行计划”选项也可用

    1. Query in ApexSQL Plan查询
    2. Estimated execution plan option估计执行计划”选项

    SHOWPLAN_XML (SHOWPLAN_XML)

    The SHOWPLAN option in SQL Server Management Studio has to be set using T-SQL and it shows the estimated execution plan. This is the same plan as shown when the Estimated Execution Plan option is selected, when the query is not actually executed

    必须使用T-SQL设置SQL Server Management Studio中的SHOWPLAN选项,它显示估计的执行计划。 与实际上未执行查询时选择“ 估计执行计划”选项时显示的计划相同。

    1. Execute

      执行

      SET SHOWPLAN_XML ON            
      

      Note that this is the only statement in the batch that can be executed

      请注意,这是批处理中唯一可以执行的语句

    2. Execute a query. The Results tab will show a link to the query plan. Note that the query results are not shown, as the query is not really executed

      执行查询。 结果选项卡将显示查询计划的链接。 请注意,由于未真正执行查询,因此未显示查询结果

      Dialog showing the Results tab with a link to the query plan

    3. Click the link is the grid

      单击链接是网格

      A new query tab will be opened showing the query plan

      将打开一个新的查询选项卡,显示查询计划

      Dialog showing a new query tab with the query plan in it

    4. To stop the query plan from showing, run

      要停止显示查询计划,请运行

      SET SHOWPLAN_XML OFF
      

    使用查询缓存 (Use the query cache)

    As mentioned in the SQL Server query execution plans – Basics article, query plans in SQL Server are saved in the query plan cache, so they can be reused later in order to execute queries faster. One of the options to see query plans is to query the content of the plan cache using Dynamic Management Views (DMVs)

    如“ SQL Server查询执行计划-基础”一文中提到的,SQL Server中的查询计划保存在查询计划缓存中,因此以后可以重用它们以更快地执行查询。 查看查询计划的选项之一是使用动态管理视图(DMV)查询计划缓存的内容

    The sys.dm_exec_cached_plans view shows one row for every query plan stored in the plan cache. The view shows query text, memory used, and how many times the plan was reused

    sys.dm_exec_cached_plans视图为计划缓存中存储的每个查询计划显示一行。 该视图显示查询文本,已使用的内存以及计划被重用了多少次

    The sys.dm_exec_sql_text view shows the SQL batch text, identified by sql_handle

    sys.dm_exec_sql_tex t视图显示SQL批处理文本,由sql_handle标识

    To see the plans for ad hoc queries cached in the plan cache:

    要查看计划缓存中缓存的临时查询的计划,请执行以下操作:

    SELECT qp.query_plan, 
           CP.usecounts, 
           cp.cacheobjtype, 
           cp.size_in_bytes, 
           cp.usecounts, 
           SQLText.text
      FROM sys.dm_exec_cached_plans AS CP
      CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
      CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
      WHERE objtype = 'Adhoc' and cp.cacheobjtype = 'Compiled Plan'
    

    Dialog showing one row for every query plan stored in the plan cache

    To open a plan, click the link in the query_plan results column and the plan will be shown in the new Query window

    要打开计划,请单击query_plan结果列中的链接,该计划将显示在新的“查询”窗口中

    使用STATISTICS和SHOWPLAN选项 (Use the STATISTICS and SHOWPLAN options)

    The STATISTICS XML option shows the same query plan as shown when the Include Actual Execution Plan option is selected. Unlike with the SHOWPLAN options that don’t actually execute the queries, the STATISTICS options execute it and show the results

    STATISTICS XML选项显示的查询计划与选择“ 包括实际执行计划”选项时显示的查询计划相同。 与不实际执行查询的SHOWPLAN选项不同,STATISTICS选项执行它并显示结果

    SET STATISTICS XML ON
    

    Note that besides the link to the query plan, the query results are also shown

    请注意,除了链接到查询计划之外,还显示了查询结果

    Dialog showing the Results tab using the STATISTICS XML option

    To turn the option off, execute:

    要关闭该选项,请执行:

    SET STATISTICS XML OFF
    

    Other useful options are:

    其他有用的选项是:

    SHOWPLAN_XML – doesn’t execute the query, so no results are shown. Shows the link the same as the STATISTICS XML option

    SHOWPLAN_XML –不执行查询,因此不显示结果。 显示链接与STATISTICS XML选项相同

    SHOWPLAN_TEXT – doesn’t execute the query, shows the text of the estimated query plan

    SHOWPLAN_TEXT –不执行查询,显示估计的查询计划的文本

    SHOWPLAN_TEXT results – shows the text of the estimated query plan

    SHOWPLAN_ALL – doesn’t execute the query, shows the text of the estimated query plan along with the cost estimation

    SHOWPLAN_ALL –不执行查询,显示估算查询计划的文本以及成本估算

    SHOWPLAN_ALL results - shows the text of the estimated query plan along with the cost estimation

    STATISTICS PROFILE – executes the query, shows the results and text of the actual query plan

    统计资料-执行查询,显示结果和实际查询计划的文本

    STATISTICS PROFILE results – shows the results and text of the actual query plan

    使用SQL Server Profiler (Use SQL Server Profiler)

    A query execution plan can also be captured in a SQL Server trace and opened in SQL Server Profiler

    查询执行计划也可以在SQL Server跟踪中捕获并在SQL Server Profiler中打开

    1. Start SQL Server Profiler

      启动SQL Server Profiler
    2. File menu, select 文件”菜单中,选择“ New Trace新建跟踪”
    3. Events Section tab, check 事件部分”选项卡中,选中“ Show all events显示所有事件”
    4. Performance node性能节点
    5. Select Showplan XML

      选择Showplan XML

      Selecting the Showplan XML using SQL Server Profiler

    6. Execute the query you want to see the query plan for

      执行要查看其查询计划的查询
    7. Stop the trace. This is recommended due to practical reasons – in busy databases, it’s difficult to filter by the event you want to trace

      停止跟踪。 由于实际原因,建议使用此方法–在繁忙的数据库中,很难根据要跟踪的事件进行过滤
    8. Select the query plan in the grid

      在网格中选择查询计划

      The SQL Server query plan is shown in the lower pane. It’s the same plan as shown when the Include Actual Execution Plan option is selected. You can see its details in the tooltip that appears on mouse over or save the whole trace as an XML file for later analysis

      SQL Server查询计划显示在下部窗格中。 与选择“ 包括实际执行计划”选项时显示的计划相同。 您可以在鼠标悬停在其上的工具提示中查看其详细信息,或将整个跟踪保存为XML文件以供以后分析

      Dialog showing details for the SQL Server query plan in the tooltip that appears on mouse over

    This method is not recommended due to several downsides. SQL Server Profiler adds some overhead that affects query performance. Another reason is that filtering the events and finding the specific one among thousands of records in not easy in SQL Server Profiler

    由于存在多个缺点,因此不建议使用此方法。 SQL Server Profiler增加了一些影响查询性能的开销。 另一个原因是在SQL Server Profiler中过滤事件并在数千条记录中查找特定事件并不容易

    In this article, we showed how to open a SQL Server query execution plan using various methods. In the next article, we will show how to read the plans, what the objects presented with icons represent, and how to use these plans in performance analysis and troubleshooting

    在本文中,我们展示了如何使用各种方法打开SQL Server查询执行计划。 在下一篇文章中,我们将展示如何阅读计划,带有图标的对象代表什么以及如何在性能分析和故障排除中使用这些计划。

    翻译自: https://www.sqlshack.com/sql-server-query-execution-plans-viewing-plans/

    展开全文
  • sqlserver 执行计划

    千次阅读 2019-05-31 16:24:28
    一个很好的手册分享,执行计划里的属性解释官方文档:...想复杂的事情简单说,在看执行计划的其他文章的时候,发现直接上很复杂的DDL脚本来讲解,这样...
  • Oracle SQL调优系列之看懂执行计划explain

    千次阅读 多人点赞 2020-06-18 10:49:02
    Oracle调优之看懂Oracle执行计划1、写作前言2、什么是执行计划? 1、写作前言 之前曾经拜读过《收获,不止sql调优》一书,此书是国内DBA写的一本很不错的调优类型的书,是一些很不错的调优经验的分享。虽然读了一遍...
  • 摘要:本文描述了11g的新特性之一:执行计划管理,介绍了引入该新特性的原因,以及该新特性的相关特点、工作原理等。最后通过引入一个测试案例来介绍如何使用该新特性。 分析了如何固定优化过的执行计划,避免被...
  • Oracle执行计划

    千次阅读 2021-09-21 00:39:56
    如何查看执行计划 set autotrace on set autotrace on explain set autotrace on statistics select * from jobs; 执行计划 统计信息 explain plan for select * from jobs; select * from table(dbms_xplan....
  • 执行计划(execution plan,也叫查询计划或者解释计划)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想,我们首先应该...
  • SqlServer执行计划

    千次阅读 2018-11-15 09:26:47
    1、看懂t-sql的执行计划,明白执行计划中的一些常识。 2、能够分析执行计划,找到优化sql性能的思路或方案。 如果你对sql查询优化的理解或常识不是很深入,那么推荐几骗博文给你:SqlServer性能检测和优化工具使用...
  • 查看Oracle执行计划的几种方法

    千次阅读 2021-05-08 01:34:22
    查看Oracle执行计划的几种方法一、通过PL/SQL Dev工具1、直接File->New->Explain Plan Window,在窗口中执行sql可以查看计划结果。其中,Cost表示cpu的消耗,单位为n%,Cardinality表示执行的行数,等价Rows。...
  • Mysql 执行计划

    千次阅读 2020-05-19 09:03:37
    当客户端发送给mysql 服务器一条查询语句后,经过sql的优化器,会产生一个执行计划执行计划 使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MYSQL 是如何处理你的 sql 语句的。分析你的查询语句...
  • Spark执行计划分析与研究

    万次阅读 多人点赞 2018-09-18 11:24:30
    在学习、使用和研究spark的过程中,逐渐会发现:单纯看官方文档对spark参数调优只能解决一小部分的问题,要想进一步的学习spark,进一步调优甚至在spark源码的基础上二次开发,我觉得收益最高的应该是学习执行计划了...
  • Doris SQL执行计划

    千次阅读 2022-04-07 18:52:31
    SQL解析 sql过程包括以下四个步骤:词法分析,语法分析,生成逻辑计划,生成物理计划。 词法分析–识别Token 词法分析主要负责将字符串形式的sql识别成一个个token,为语法分析做准备。 .SQL 的 Token 可以分为如下...
  • ORACLE数据库查看执行计划

    千次阅读 2018-09-25 10:12:50
    基于ORACLE的应用系统很多性能问题,是由应用系统SQL性能低劣引起的,所以,SQL的性能优化很重要,分析与优化SQL的性能我们一般通过查看该SQL的执行计划,本文就如何看懂执行计划,以及如何通过分
  • 文章目录官方文档某些SQL查询为什么慢MySQL处理SQL请求的过程查询缓存对SQL性能的影响SQL预处理及生成执行计划造成MySQL生成错误的执行计划的原因 官方文档 https://dev.mysql.com/doc/ 如果英文不好的话,可以...
  • Spark SQL 执行计划详解

    千次阅读 2022-03-03 08:57:45
    弱鸡了吧?背各种SparkSQL调优参数?这个东西才是SparkSQL必须要懂的 - 知乎 (zhihu.com)spark-sql explain 执行计划详解 - 掘金 (juejin.cn) ...背各种SparkSQL调优参数...spark-sql explain 执行计划详解 - 掘金 (juej
  • MySql执行计划的查看

    千次阅读 2019-06-20 11:47:20
    什么是数据库执行计划: 利用一个SQL语句, 你可能要Server取出所有News表中的信息. 当Server收到的这条SQL的时候, 第一件事情并不是解析它. 如果这条SQL没有语法错误, Server才会继续工作. Server会决定最好的计算...
  • 达梦SQL优化及执行计划解读

    千次阅读 2020-05-21 11:20:38
    无论是什么数据库,一般SQL优化我们都需要去查看SQL的执行计划,了解SQL具体是慢在哪里,才知道从哪里开始优化。 那么什么是执行计划呢? 执行计划是SQL语句的执行方式,由查询优化器为语句设计的执行方式,交给执行...
  • MySQL数据库:explain执行计划详解

    万次阅读 2018-11-22 15:22:01
    执行计划执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的。 使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句...
  • 详解GaussDB(DWS) explain分布式执行计划

    千次阅读 2020-10-20 10:46:44
    摘要:本文主要介绍如何详细解读GaussDB(DWS)产生的分布式执行计划,从计划中发现性能调优点。 前言 执行计划(又称解释计划)是数据库执行SQL语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的...
  • (二十四) PLSQL Developer执行计划

    千次阅读 2019-08-03 11:17:34
    文章目录执行计划什么是执行计划配置执行计划需要显示的项执行计划的常用列字段解释使用执行计划查看执行顺序表访问的几种方式(非全部):table access full 全表扫描table access by index rowid 用过rowid的表存取...
  • Sql执行计划,优化sql必备!

    万次阅读 多人点赞 2018-06-22 22:22:29
    SQL执行计划学习背景: 实际项目开发中,由于我们不知道实际查询的时候数据库里发生了什么事情,数据库软件是怎样扫描表、怎样使用索引的,因此,我们能感知到的就只有sql语句运行的时间,在数据规模不大时,查询是...
  • 教你怎么查看 hive 的执行计划

    千次阅读 多人点赞 2020-03-06 16:21:37
    我们在总结Hive架构的时候,我们知道Hive中有一个Compiler组件,这个组件主要是解析字符串的SQL,然后生成执行计划,我们介绍文章主要是详细讲解Compiler中的细节以及生成的执行计划 我们从下面的HiveSQL语句开始: ...
  • 详解sqlserver 执行计划

    千次阅读 2018-12-21 15:40:22
      对于SQL Server的优化来...由于数据库的优化,本身也是一个涉及面比较的广的话题, 因此本文只谈优化查询时如何看懂SQL Server查询计划。毕竟我对SQL Server的认识有限,如有错误,也恳请您在发现后及时批评指...
  • Greenplum执行计划

    千次阅读 2020-01-20 14:47:53
    Greenplum执行计划和pg中的类似,但是由于gp是分布式的shared nothing架构,所以执行计划必然和pg还是有些区别。 gp中查看SQL的执行计划也是通过explain语句,语法如下: Command: EXPLAIN Description: show the ...
  • sql server 数据库优化--显示执行计划

    千次阅读 2019-07-15 15:30:28
    刚开始用SQL Server的时候,我没有用显示执行计划来对查询进行分析。我曾经一直认为我递交的SQL查询都是最优的,而忽略了查询性能究竟如何,从而对“执行计划”重视不够。在我职业初期,我只要能获取数据就很开心,...
  • mongodb基础篇--explain执行计划

    千次阅读 2019-12-07 17:54:47
    我们可以通过执行计划来判断查询语句的效率,根据实际情况进行调整,然后提高查询效率。 可以使用如下方法: db.collection.find().explain(<verbose>) verbose 参数表示执行计划的输出模式。有三种:...
  • Oracle 执行计划详解(预估 + 真实)

    万次阅读 多人点赞 2020-05-19 16:43:35
    文章目录1 概述1.1 思维导图1.2 概念2 执行计划2.1 预估的2.2 真实的3 示例4 备选命令 1 概述 什么是 Oracle 的执行计划执行计划是一条查询语句在 Oracle 中的执行过程或访问路径的描述 简单一点说,就是: ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 647,663
精华内容 259,065
关键字:

执行计划

友情链接: tc26xb_ds_v1.1.pdf.zip