精华内容
下载资源
问答
  • Sql执行计划,优化sql必备!

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

    SQL执行计划学习

    背景:

            实际项目开发中,由于我们不知道实际查询的时候数据库里发生了什么事情,数据库软件是怎样扫描表、怎样使用索引的,因此,我们能感知到的就只有sql语句运行的时间,在数据规模不大时,查询是瞬间的,因此,在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大,如千万、亿的时候,我们运行同样的sql语句时却发现迟迟没有结果,这个时候才知道数据规模已经限制了我们查询的速度。所以,查询优化和索引也就显得很重要了。

    引出的问题:

            当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢?
    答案是可以的,mysql提供了相应的功能和语法来实现该功能。那就是sql执行计划!

    关键字:explain

    什么是Sql执行计划?

            执行计划,简单的来说,是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。(这里我的理解是,存储引擎在执行sql的时候,把一条sql分解,列出来每一步需要干什么,并按照步骤依次执行,这样我们就能看出来哪个步骤耽误了时间,当然这也是接下来要讲的重点!)

    如何查看sql执行计划?

    1. 查询SELECT * from employee WHERE `name` ='蒋峰1';


    2. 查看上述语句的执行计划 (加关键字explain)

    EXPLAIN SELECT * from employee WHERE `name` ='蒋峰1';


    读懂执行计划

    通过上面,我们知道了什么是执行计划,也看到了执行计划到底是什么东西,现在我们来具体了解一下,MySQL执行计划中,每个属性代表的是什么意思?


    我们一一来介绍,并说明每个属性有哪些可选值,以及每个可选值的意思。

    id

    表示查询中select操作表的顺序,按顺序从大到依次执行(不是表中的自增主键!)
    id值相同执行顺序从上到下。

    id值不同时id值大的先执行。

    select_type

    这一列显示了对应行是简单还是复杂SELECT.取值如下:SIMPLE值意味着查询不包括子查询和UNION。查询有任何复杂的子部分,则最外层标记为PRIMARY.取值如下:


    type 

    该属性表示访问类型,有很多种访问类型。
    最常见的其中包括以下几种: ALL(全表扫描), index(索引扫描),range(范围扫描),ref (非唯一索引扫描),eq_ref(唯一索引扫描,),(const)常数引用, 访问速度依次由慢到快。
    其中 : range(范围)常见于 between and …, 大于 and 小于这种情况。

    提示 : 慢SQL是否走索引,走了什么索引,也就可以通过该属性查看了。


    table 

    输出数据行所在的表的名称


    possible_keys 

    顾名思义,指出MySQL能使用哪些索引来优化查询,查询所涉及的列上的索引都会被列出,但不一定会被使用,算是个提示作用!

    key 

    显示MySQL实际使用的索引,其中就包括主键索引(PRIMARY),或者自建索引的名字。

    如果没有可用的索引,则显示为NULL

    key_len 

    表示索引字段的最大可能长度,KEY_LEN的长度由字段定义计算而来,并非数据的实际长度,

    当 key 字段的值为 null时,索引的长度就是 null。注意,key_len的值可以告诉你在联合索引中 MySQL 会真正使用了哪些索引。

    ref 

    连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值
    表示哪些列或常量被用于查找索引列上的值

    该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引(换句话说,就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时,这就是一个不错的连接类型。 ref还可以用于检索字段使用=操作符来比较的时候。以下的几个例子中,MySQL将使用 ref 来处理ref_table,和eq_ref的区别是-用到的索引是否唯一性

    rows (关键)

    扫描行数,也就是说,需要扫描多少行,才能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高, 也就有大部分SQL优化,都是在减少这个值的大小。

    注意: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)

    Extra 

    这个属性非常重要,该属性中包括执行SQL时的真实情况信息,如上面所属,使用到的是”using where”,表示使用where筛选得到的值,常用的有:

    “Using temporary”: 使用临时表 “using filesort”: 使用文件排序


    对一开始的sql进行改造

    看到这里,我们应该已经发现,在第一步中,我们的这条SQL
    SELECT * from employee WHERE `name` ='蒋峰1';
    是没有走索引的,而且还是全表扫描,在数据量少的情况下,问题还不会特别突出,如果数据量比较大,这可是个会造成生产事故的慢查询哦,现在我们改造一下,将name字段添加上索引,
    # 添加索引
    alter table employee add index idx_name(name);

    看看它的执行计划是怎样的。


    你看,现在已经走idx_name索引了,其type从All(全表扫描)到ref(非唯一索引了),别看就只有这一点点小区别,在大数据量的时候,可是会起大作用的哦。

    再举一个栗子

    课程表
    create table Course(
    c_id int PRIMARY KEY,
    name varchar(10))
    数据100条-------------------------100条(自己模拟)

    学生表:
    create table Student(
    id int PRIMARY KEY,
    name varchar(10))
    数据70000条-----------------------400条

    学生成绩表SC
    CREATE table SC(
        sc_id int PRIMARY KEY,
        s_id int,
        c_id int,
        score int)
    数据70w条--------------------------30w条

    查询目的:

    查找语文考100分的考生

    查询语句:

    select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
    执行时间:30248.271s(29.648s)
    为什么这么慢,先来查看下查询计划:
    EXPLAIN 
    select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

    发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。
    先给sc表的c_id和score建个索引
    CREATE index sc_c_id_index on SC(c_id);

    CREATE index sc_score_index on SC(score);

    再次执行上述查询语句,时间为: 1.054s(2.428s)
    快了3w多倍,大大缩短了查询时间,看来索引能极大程度的提高查询效率,建索引很有必要,很多时候都忘记建
    索引了,数据量小的的时候压根没感觉,大数据量感觉贼爽。

    但是2s的时间还是太长了,还能进行优化吗,仔细看执行计划:


    接下来再次优化:这次我们用连接查询!(先删除索引)
    alter table SC drop index sc_c_id_index;
    alter table SC drop index sc_score_index;
    SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100

    再次执行上述查询语句,时间为: 0.088s

    EXPLAIN SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
    再看执行计划:


    发现一个ALL,extra字段中显示where
    所以我们尝试加索引:
    CREATE index sc_c_id_index on SC(c_id);
    CREATE index sc_score_index on SC(score);

    再次执行上条sql:
    SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
    再次执行上述查询语句,时间为: 0.010s

    再看sql执行计划:


    总结:
    1.mysql嵌套子查询效率确实比较低
    2.可以将其优化成连接查询
    3.连接表时,可以先用where条件对表进行过滤,然后做表连接
    (虽然mysql会对连表语句做优化)
    4.建立合适的索引,必要时建立多列联合索引
    5.当然我们最主要的是要学会分析sql执行计划,mysql会对sql进行优化,所以分析执行计划很重要

    索引优化
    上面讲到子查询的优化,以及如何建立索引,而且在多个字段索引时,分别对字段建立了单个索引
    后面发现其实建立联合索引效率会更高,尤其是在数据量较大,单个列区分度不高的情况下。
    create index sc_c_id_score_index on SC(c_id,score);
    时间为: 0.008s(由于数据量有限,效果不明显,数据大的时候效率更高)

    展开全文
  • 执行计划(execution plan,也叫查询计划或者解释计划)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想,我们首先应该...

    执行计划(execution plan,也叫查询计划或者解释计划)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想,我们首先应该查看它的执行计划。本文主要介绍如何在各种数据库中获取和理解执行计划,并给出进一步深入分析的参考文档。

    现在许多管理和开发工具都提供了查看图形化执行计划的功能,例如 MySQL Workbench、Oracle SQL Developer、SQL Server Management Studio、DBeaver 等;不过我们不打算使用这类工具,而是介绍利用数据库提供的命令查看执行计划。

    我们先给出在各种数据库中查看执行计划的一个简单汇总:

    数据库 执行计划
    MySQL EXPLAIN sql_statement;
    Oracle EXPLAIN PLAN FOR sql_statement;
    SELECT * FROM TABLE(DBMS_XPLAN.display);
    SQL Server SET STATISTICS PROFILE ON;
    sql_statement;
    SET STATISTICS PROFILE OFF;
    PostgreSQL EXPLAIN sql_statement;
    SQLite EXPLAIN QUERY PLAN sql_statement;

    本文使用的示例表和数据可以点击链接《SQL 入门教程》示例数据库

    MySQL 执行计划

    MySQL 中获取执行计划的方法很简单,就是在 SQL 语句的前面加上EXPLAIN关键字:

    EXPLAIN
    SELECT e.first_name,e.last_name,e.salary,d.department_name
      FROM employees e
      JOIN departments d ON (e.department_id = d.department_id)
     WHERE e.salary > 15000;
    

    执行该语句将会返回一个表格形式的执行计划,包含了 12 列信息:

    id|select_type|table|partitions|type  |possible_keys    |key    |key_len|ref                 |rows|filtered|Extra      |
    --|-----------|-----|----------|------|-----------------|-------|-------|--------------------|----|--------|-----------|
     1|SIMPLE     |e    |          |ALL   |emp_department_ix|       |       |                    | 107|   33.33|Using where|
     1|SIMPLE     |d    |          |eq_ref|PRIMARY          |PRIMARY|4      |hrdb.e.department_id|   1|     100|           |
    

    MySQL 中的EXPLAIN支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。

    接下来,我们要做的就是理解执行计划中这些字段的含义。下表列出了 MySQL 执行计划中的各个字段的作用:

    列名 作用
    id 语句中 SELECT 的序号。如果是 UNION 操作的结果,显示为 NULL;此时 table 列显示为 <unionM,N>。
    select_type SELECT 的类型,包括:
    - SIMPLE,不涉及 UNION 或者子查询的简单查询;
    - PRIMARY,最外层 SELECT;
    - UNION,UNION 中第二个或之后的 SELECT;
    - DEPENDENT UNION,UNION 中第二个或之后的 SELECT,该 SELECT 依赖于外部查询;
    - UNION RESULT,UNION 操作的结果;
    - SUBQUERY,子查询中的第一个 SELECT;
    - DEPENDENT SUBQUERY,子查询中的第一个 SELECT,该 SELECT 依赖于外部查询;
    - DERIVED,派生表,即 FROM 中的子查询;
    - DEPENDENT DERIVED,依赖于其他表的派生表;
    - MATERIALIZED,物化子查询;
    - UNCACHEABLE SUBQUERY,无法缓存结果的子查询,对于外部表中的每一行都需要重新查询;
    - UNION 中第二个或之后的 SELECT,该 UNION属于 UNCACHEABLE SUBQUERY。
    table 数据行的来源表,也有可能是以下值之一:
    - <unionM,N>,id 为 M 和 N 的 SELECT 并集运算的结果;
    - <derivedN>,id 为 N 的派生表的结果;
    - <subqueryN>,id 为 N 的物化子查询的结果。
    partitions 对于分区表而言,表示数据行所在的分区;普通表显示为 NULL。
    type 连接类型或者访问类型,性能从好到差依次为:
    - system,表中只有一行数据,这是 const 类型的特殊情况;
    - const,最多返回一条匹配的数据,在查询的最开始读取;
    - eq_ref,对于前面的每一行,从该表中读取一行数据;
    - ref,对于前面的每一行,从该表中读取匹配索引值的所有数据行;
    - fulltext,通过 FULLTEXT 索引查找数据;
    - ref_or_null,与 ref 类似,额外加上 NULL 值查找;
    - index_merge,使用索引合并优化技术,此时 key 列显示使用的所有索引;
    - unique_subquery,替代以下情况时的 eq_ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
    - index_subquery,与 unique_subquery 类似,用于子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    - range,使用索引查找范围值;
    - index,与 ALL 类型相同,只不过扫描的是索引;
    - ALL,全表扫描,通常表示存在性能问题。
    possible_keys 可能用到的索引,实际上不一定使用。
    key 实际使用的索引。
    key_len 实际使用的索引的长度。
    ref 用于和 key 中的索引进行比较的字段或者常量,从而判断是否返回数据行。
    rows 执行查询需要检查的行数,对于 InnoDB 是一个估计值。
    filtered 根据查询条件过滤之后行数百分比,rows × filtered 表示进入下一步处理的行数。
    Extra 包含了额外的信息。例如 Using temporary 表示使用了临时表,Using filesort 表示需要额外的排序操作等。

    对于上面的示例,只有一个 SELECT 子句,id 都为 1;首先对 employees 表执行全表扫描(type = ALL),处理了 107 行数据,使用 WHERE 条件过滤后预计剩下 33.33% 的数据(估计不准确);然后针对这些数据,依次使用 departments 表的主键(key = PRIMARY)查找一行匹配的数据(type = eq_ref、rows = 1)。

    使用 MySQL 8.0 新增的 ANALYZE 选项可以显示实际执行时间等额外的信息:

    EXPLAIN ANALYZE
    SELECT e.first_name,e.last_name,e.salary,d.department_name
      FROM employees e
      JOIN departments d ON (e.department_id = d.department_id)
     WHERE e.salary > 15000;
    -> Nested loop inner join  (cost=23.43 rows=36) (actual time=0.325..1.287 rows=3 loops=1)
        -> Filter: ((e.salary > 15000.00) and (e.department_id is not null))  (cost=10.95 rows=36) (actual time=0.281..1.194 rows=3 loops=1)
            -> Table scan on e  (cost=10.95 rows=107) (actual time=0.266..0.716 rows=107 loops=1)
        -> Single-row index lookup on d using PRIMARY (department_id=e.department_id)  (cost=0.25 rows=1) (actual time=0.013..0.015 rows=1 loops=3)
    

    其中,Nested loop inner join 表示使用嵌套循环连接的方式连接两个表,employees 为驱动表。cost 表示估算的代价,rows 表示估计返回的行数;actual time 显示了返回第一行和所有数据行花费的实际时间,后面的 rows 表示迭代器返回的行数,loops 表示迭代器循环的次数。

    关于 MySQL EXPLAIN 命令的使用和参数,可以参考 MySQL 官方文档 EXPLAIN 语句

    关于 MySQL 执行计划的输出信息,可以参考 MySQL 官方文档理解查询执行计划

    Oracle 执行计划

    Oracle 中提供了多种查看执行计划的方法,本文使用以下方式:

    1. 使用EXPLAIN PLAN FOR命令生成并保存执行计划;
    2. 显示保存的执行计划。

    首先,生成执行计划:

    EXPLAIN PLAN FOR
    SELECT e.first_name,e.last_name,e.salary,d.department_name
      FROM employees e
      JOIN departments d ON (e.department_id = d.department_id)
     WHERE e.salary > 15000;
    

    EXPLAIN PLAN FOR命令不会运行 SQL 语句,因此创建的执行计划不一定与执行该语句时的实际计划相同。

    该命令会将生成的执行计划保存到全局的临时表 PLAN_TABLE 中,然后使用系统包 DBMS_XPLAN 中的存储过程格式化显示该表中的执行计划。以下语句可以查看当前会话中的最后一个执行计划:

    SELECT * FROM TABLE(DBMS_XPLAN.display);
    PLAN_TABLE_OUTPUT                                                                           |
    --------------------------------------------------------------------------------------------|
    Plan hash value: 1343509718                                                                 |
                                                                                                |
    --------------------------------------------------------------------------------------------|
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     ||
    --------------------------------------------------------------------------------------------|
    |   0 | SELECT STATEMENT             |             |    44 |  1672 |     6  (17)| 00:00:01 ||
    |   1 |  MERGE JOIN                  |             |    44 |  1672 |     6  (17)| 00:00:01 ||
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 ||
    |   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 ||
    |*  4 |   SORT JOIN                  |             |    44 |   968 |     4  (25)| 00:00:01 ||
    |*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |    44 |   968 |     3   (0)| 00:00:01 ||
    --------------------------------------------------------------------------------------------|
                                                                                                |
    Predicate Information (identified by operation id):                                         |
    ---------------------------------------------------                                         |
                                                                                                |
       4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      |
           filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      |
       5 - filter("E"."SALARY">15000)                                                           |
    

    Oracle 中的EXPLAIN PLAN FOR支持 SELECT、UPDATE、INSERT 以及 DELETE 语句。

    接下来,我们同样需要理解执行计划中各种信息的含义:

    • Plan hash value 是该语句的哈希值。SQL 语句和执行计划会存储在库缓存中,哈希值相同的语句可以重用已有的执行计划,也就是软解析;
    • Id 是一个序号,但不代表执行的顺序。执行的顺序按照缩进来判断,缩进越多的越先执行,同样缩进的从上至下执行。Id 前面的星号表示使用了谓词判断,参考下面的 Predicate Information;
    • Operation 表示当前的操作,也就是如何访问表的数据、如何实现表的连接、如何进行排序操作等;
    • Name 显示了访问的表名、索引名或者子查询等,前提是当前操作涉及到了这些对象;
    • Rows 是 Oracle 估计的当前操作返回的行数,也叫基数(Cardinality);
    • Bytes 是 Oracle 估计的当前操作涉及的数据量
    • Cost (%CPU) 是 Oracle 计算执行该操作所需的代价;
    • Time 是 Oracle 估计执行该操作所需的时间;
    • Predicate Information 显示与 Id 相关的谓词信息。access 是访问条件,影响到数据的访问方式(扫描表还是通过索引);filter 是过滤条件,获取数据后根据该条件进行过滤。

    在上面的示例中,Id 的执行顺序依次为 3 -> 2 -> 5 -> 4- >1。首先,Id = 3 扫描主键索引 DEPT_ID_PK,Id = 2 按主键 ROWID 访问表 DEPARTMENTS,结果已经排序;其次,Id = 5 全表扫描访问 EMPLOYEES 并且利用 filter 过滤数据,Id = 4 基于部门编号进行排序和过滤;最后 Id = 1 执行合并连接。显然,此处 Oracle 选择了排序合并连接的方式实现两个表的连接。

    关于 Oracle 执行计划和 SQL 调优,可以参考 Oracle 官方文档《SQL Tuning Guide》

    SQL Server 执行计划

    SQL Server Management Studio 提供了查看图形化执行计划的简单方法,这里我们介绍一种通过命令查看的方法:

    SET STATISTICS PROFILE ON
    

    以上命令可以打开 SQL Server 语句的分析功能,打开之后执行的语句会额外返回相应的执行计划:

    SELECT e.first_name,e.last_name,e.salary,d.department_name
      FROM employees e
      JOIN departments d ON (e.department_id = d.department_id)
     WHERE e.salary > 15000;
    
    first_name|last_name|salary  |department_name|
    ----------|---------|--------|---------------|
    Steven    |King     |24000.00|Executive      |
    Neena     |Kochhar  |17000.00|Executive      |
    Lex       |De Haan  |17000.00|Executive      |
    
    Rows|Executes|StmtText                                                                                                                                                                                           |StmtId|NodeId|Parent|PhysicalOp          |LogicalOp           |Argument                                                                                                                                                           |DefinedValues                                                       |EstimateRows|EstimateIO  |EstimateCPU|AvgRowSize|TotalSubtreeCost|OutputList                                                            |Warnings|Type    |Parallel|EstimateExecutions|
    ----|--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------|------|------|--------------------|--------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------|------------|------------|-----------|----------|----------------|----------------------------------------------------------------------|--------|--------|--------|------------------|
       3|       1|SELECT e.first_name,e.last_name,e.salary,d.department_name¶  FROM employees e¶  JOIN departments d ON (e.department_id = d.department_id)WHERE e.salary > 15000                                  |     1|     1|     0|                    |                    |                                                                                                                                                                   |                                                                    |   2.9719627|            |           |          |     0.007803641|                                                                      |        |SELECT  |       0|                  |
       3|       1|  |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[department_id]))                                                                                                                              |     1|     2|     1|Nested Loops        |Inner Join          |OUTER REFERENCES:([e].[department_id])                                                                                                                             |                                                                    |   2.9719627|           0|          0|        57|     0.007803641|[e].[first_name], [e].[last_name], [e].[salary], [d].[department_name]|        |PLAN_ROW|       0|                 1|
       3|       1|       |--Clustered Index Scan(OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00)))                                     |     1|     3|     2|Clustered Index Scan|Clustered Index Scan|OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00))                                     |[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id]|           3|0.0038657407|   2.747E-4|        44|     0.004140441|[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id]  |        |PLAN_ROW|       0|                 1|
       3|       3|       |--Clustered Index Seek(OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD)|     1|     4|     2|Clustered Index Seek|Clustered Index Seek|OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD|[d].[department_name]                                               |           1|    0.003125|   1.581E-4|        26|       0.0035993|[d].[department_name]                                                 |        |PLAN_ROW|       0|                 3|
    

    SQL Server 中的执行计划支持 SELECT、INSERT、UPDATE、DELETE 以及 EXECUTE 语句。

    SQL Server 执行计划各个步骤的执行顺序按照缩进来判断,缩进越多的越先执行,同样缩进的从上至下执行。接下来,我们需要理解执行计划中各种信息的含义:

    • Rows 表示该步骤实际产生的记录数;
    • Executes 表示该步骤实际被执行的次数;
    • StmtText 包含了每个步骤的具体描述,也就是如何访问和过滤表的数据、如何实现表的连接、如何进行排序操作等;
    • StmtId,该语句的编号;
    • NodeId,当前操作步骤的节点号,不代表执行顺序;
    • Parent,当前操作步骤的父节点,先执行子节点,再执行父节点;
    • PhysicalOp,物理操作,例如连接操作的嵌套循环实现;
    • LogicalOp,逻辑操作,例如内连接操作;
    • Argument,操作使用的参数;
    • DefinedValues,定义的变量值;
    • EstimateRows,估计返回的行数;
    • EstimateIO,估计的 IO 成本;
    • EstimateCPU,估计的 CPU 成本;
    • AvgRowSize,平均返回的行大小;
    • TotalSubtreeCost,当前节点累计的成本;
    • OutputList,当前节点输出的字段列表;
    • Warnings,预估得到的警告信息;
    • Type,当前操作步骤的类型;
    • Parallel,是否并行执行;
    • EstimateExecutions,该步骤预计被执行的次数;

    对于上面的语句,节点执行的顺序为 3 -> 4 -> 2 -> 1。首先执行第 3 行,通过聚集索引(主键)扫描 employees 表加过滤的方式返回了 3 行数据,估计的行数(3.0841121673583984)与此非常接近;然后执行第 4 行,循环使用聚集索引的方式查找 departments 表,循环 3 次每次返回 1 行数据;第 2 行是它们的父节点,表示使用 Nested Loops 方式实现 Inner Join,Argument 列(OUTER REFERENCES:([e].[department_id]))说明驱动表为 employees ;第 1 行代表了整个查询,不执行实际操作。

    最后,可以使用以下命令关闭语句的分析功能:

    SET STATISTICS PROFILE OFF
    

    关于 SQL Server 执行计划和 SQL 调优,可以参考 SQL Server 官方文档执行计划

    PostgreSQL 执行计划

    PostgreSQL 中获取执行计划的方法与 MySQL 类似,也就是在 SQL 语句的前面加上EXPLAIN关键字:

    EXPLAIN
    SELECT e.first_name,e.last_name,e.salary,d.department_name
      FROM employees e
      JOIN departments d ON (e.department_id = d.department_id)
     WHERE e.salary > 15000;
    
    QUERY PLAN                                                            |
    ----------------------------------------------------------------------|
    Hash Join  (cost=3.38..4.84 rows=3 width=29)                          |
      Hash Cond: (d.department_id = e.department_id)                      |
      ->  Seq Scan on departments d  (cost=0.00..1.27 rows=27 width=15)   |
      ->  Hash  (cost=3.34..3.34 rows=3 width=22)                         |
            ->  Seq Scan on employees e  (cost=0.00..3.34 rows=3 width=22)|
                  Filter: (salary > '15000'::numeric)                     |
    

    PostgreSQL 中的EXPLAIN支持 SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS 以及 CREATE MATERIALIZED VIEW AS 语句。

    PostgreSQL 执行计划的顺序按照缩进来判断,缩进越多的越先执行,同样缩进的从上至下执行。对于以上示例,首先对 employees 表执行全表扫描(Seq Scan),使用 salary > 15000 作为过滤条件;cost 分别显示了预估的返回第一行的成本(0.00)和返回所有行的成本(3.34);rows 表示预估返回的行数;width 表示预估返回行的大小(单位 Byte)。然后将扫描结果放入到内存哈希表中,两个 cost 都等于 3.34,因为是在扫描完所有数据后一次性计算并存入哈希表。接下来扫描 departments 并且根据 department_id 计算哈希值,然后和前面的哈希表进行匹配(d.department_id = e.department_id)。最上面的一行表明数据库采用的是 Hash Join 实现连接操作。

    PostgreSQL 中的EXPLAIN也可以使用 ANALYZE 选项显示语句的实际运行时间和更多信息:

    EXPLAIN ANALYZE
    SELECT e.first_name,e.last_name,e.salary,d.department_name
      FROM employees e
      JOIN departments d ON (e.department_id = d.department_id)
     WHERE e.salary > 15000;
    
    QUERY PLAN                                                                                                      |
    ----------------------------------------------------------------------------------------------------------------|
    Hash Join  (cost=3.38..4.84 rows=3 width=29) (actual time=0.347..0.382 rows=3 loops=1)                          |
      Hash Cond: (d.department_id = e.department_id)                                                                |
      ->  Seq Scan on departments d  (cost=0.00..1.27 rows=27 width=15) (actual time=0.020..0.037 rows=27 loops=1)  |
      ->  Hash  (cost=3.34..3.34 rows=3 width=22) (actual time=0.291..0.292 rows=3 loops=1)                         |
            Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                            |
            ->  Seq Scan on employees e  (cost=0.00..3.34 rows=3 width=22) (actual time=0.034..0.280 rows=3 loops=1)|
                  Filter: (salary > '15000'::numeric)                                                               |
                  Rows Removed by Filter: 104                                                                       |
    Planning Time: 1.053 ms                                                                                         |
    Execution Time: 0.553 ms                                                                                        |
    

    EXPLAIN ANALYZE通过执行语句获得了更多的信息。其中,actual time 是每次迭代实际花费的平均时间(ms),也分为启动时间和完成时间;loops 表示迭代次数;Hash 操作还会显示桶数(Buckets)、分批数量(Batches)以及占用的内存(Memory Usage),Batches 大于 1 意味着需要使用到磁盘的临时存储;Planning Time 是生成执行计划的时间;Execution Time 是执行语句的实际时间,不包括 Planning Time。

    关于 PostgreSQL 的执行计划和性能优化,可以参考 PostgreSQL 官方文档性能提示

    SQLite 执行计划

    SQLite 也提供了EXPLAIN QUERY PLAN命令,用于获取 SQL 语句的执行计划:

    sqlite> EXPLAIN QUERY PLAN
       ...> SELECT e.first_name,e.last_name,e.salary,d.department_name
       ...>   FROM employees e
       ...>   JOIN departments d ON (e.department_id = d.department_id)
       ...>  WHERE e.salary > 15000;
    QUERY PLAN
    |--SCAN TABLE employees AS e
    `--SEARCH TABLE departments AS d USING INTEGER PRIMARY KEY (rowid=?)
    

    SQLite 中的EXPLAIN QUERY PLAN支持 SELECT、INSERT、UPDATE、DELETE 等语句。

    SQLite 执行计划同样按照缩进来显示,缩进越多的越先执行,同样缩进的从上至下执行。以上示例先扫描 employees 表,然后针对该结果依次通过主键查找 departments 中的数据。SQLite 只支持一种连接实现,也就是 nested loops join。

    另外,SQLite 中的简单EXPLAIN也可以用于显示执行该语句的虚拟机指令序列:

    sqlite> EXPLAIN
       ...> SELECT e.first_name,e.last_name,e.salary,d.department_name
       ...>   FROM employees e
       ...>   JOIN departments d ON (e.department_id = d.department_id)
       ...>  WHERE e.salary > 15000;
    addr  opcode         p1    p2    p3    p4             p5  comment
    ----  -------------  ----  ----  ----  -------------  --  -------------
    0     Init           0     15    0                    00  Start at 15
    1     OpenRead       0     5     0     11             00  root=5 iDb=0; employees
    2     OpenRead       1     2     0     2              00  root=2 iDb=0; departments
    3     Rewind         0     14    0                    00
    4       Column         0     7     1                    00  r[1]=employees.salary
    5       Le             2     13    1     (BINARY)       53  if r[1]<=r[2] goto 13
    6       Column         0     10    3                    00  r[3]=employees.department_id
    7       SeekRowid      1     13    3                    00  intkey=r[3]
    8       Column         0     1     4                    00  r[4]=employees.first_name
    9       Column         0     2     5                    00  r[5]=employees.last_name
    10      Column         0     7     6                    00  r[6]=employees.salary
    11      Column         1     1     7                    00  r[7]=departments.department_name
    12      ResultRow      4     4     0                    00  output=r[4..7]
    13    Next           0     4     0                    01
    14    Halt           0     0     0                    00
    15    Transaction    0     0     8     0              01  usesStmtJournal=0
    16    Integer        15000  2     0                    00  r[2]=15000
    17    Goto           0     1     0                    00
    

    关于 SQLite 的执行计划和优化器相关信息,可以参考 SQLite 官方文档解释查询计划

    欢迎关注❤️、点赞👍、转发📣!

    展开全文
  • 使用PL/SQL执行计划进行sql调优

    千次阅读 2017-03-07 11:11:25
    使用PL/SQL执行计划进行sql调优 1、打开工具:pl/sql developer 在pl/sql中写好一个SQL语句,然后按F5打开执行计划分析窗口 2、查看cost,获得消耗资源的总体印象 一般而言,执行计划的第一行所对应的cost值,反应了...

    使用PL/SQL执行计划进行sql调优

    1、打开工具:pl/sql  developer

    pl/sql中写好一个SQL语句,然后按F5打开执行计划分析窗口

    2、查看cost,获得消耗资源的总体印象

    一般而言,执行计划的第一行所对应的cost值,反应了这段代码的总体成本估计,单看这个没有实际意义,但是拿这个cost和不同计划的执行比较,就可以比对出执行效率的高低。

    3、按照从左到右,从上到下的方法,了解执行计划的执行步骤。

    4、分析表的访问方式:

    表的访问方式有2种:第一种是全表扫描table access full和索引扫描index scan,如果表上存在选择性很好的索引,却走了全表扫描,说明存在问题。

    5、分析表的连接方式和连接顺序

    表的连接顺序:就是以那张表作为驱动表连接其他表的先后访问顺序。

    表的连接方式:简单的说分为三种:嵌套循环、哈希连接和排序-合并连接

     

    网络文本:

    我们常见得是嵌套循环和哈希连接。 

    嵌套循环:最适用也是最简单的连接方式。类似于用两层循环处理两个游标,外层游标称作驱动表,Oracle检索驱动表的数据,一条一条的代入内层游标,查找满足WHERE条件的所有数据,因此内层游标表中可用索引的选择性越好,嵌套循环连接的性能就越高。 

    哈希连接:先将驱动表的数据按照条件字段以散列的方式放入内存,然后在内存中匹配满足条件的行。哈希连接需要有合适的内存,而且必须在CBO优化模式下,连接两表的WHERE条件有等号的情况下才可以使用。哈希连接在表的数据量较大,表中没有合适的索引可用时比嵌套循环的效率要高。


    联系作者:


    加群交流:


    展开全文
  • 数据库SQL执行计划

    千次阅读 2018-10-11 23:40:13
    什么是Mysql的执行计划 要对执行计划有个比较好的理解,需要先对MySQL的基础结构及查询基本原理有简单的了解。 一条SQL如何执行?大概过程 MySQL本身的功能架构分为三个部分,分别是 应用层、逻辑层、物理层,不只是...

    能写sql 只是程序员的基本功,能写出性能优异的SQL是优秀程序员的必备技能

    什么是Mysql的执行计划

    要对执行计划有个比较好的理解,需要先对MySQL的基础结构及查询基本原理有简单的了解。

    一条SQL如何执行?大概过程

    MySQL本身的功能架构分为三个部分,分别是 应用层、逻辑层、物理层,不只是MySQL ,其他大多数数据库产品都是按这种架构来进行划分的。

    • 应用层,主要负责与客户端进行交互,建立链接,记住链接状态,返回数据,响应请求,这一层是和客户端打交道的。

    • 逻辑层,主要负责查询处理、事务管理等其他数据库功能处理,以查询为例。

      • 首先接受到查询sql之后,数据库会立即分配一个线程对其进行处理;
      • 第一步查询处理器会对SQL查询进行优化,优化后会生成执行计划;
      • 然后交由计划执行器来执行。
      • 计划执行器需要访问更底层的事务管理器,存储管理器来操作数据,他们各自的分工各有不同;
      • 最终通过调用物理层的文件获取到查询结构信息,将最终结果响应给应用层。
    • 物理层,实际物理磁盘上存储的文件,主要分为数据文件,日志文件

    通过上面的描述,生成执行计划是执行一条SQL必不可少的步骤,一条SQL性能的好坏,可以通过查看执行计划很直观的看出来,执行计划提供了各种查询类型与级别,方面我们进行查看以及为作为性能分析的依据。

    所谓的执行计划,就是mysql如何执行一条Sql语句,包括sql查询的顺序、是否使用索引、以及使用索引信息的等等。

    如何分析执行计划,来帮助我们写出更好的Sql

    MySQL为我们提供了 explain 关键字来直观的查看一条SQL的执行计划。

    //1. 查询table_name
    select * from table_name where name="explain";
    
    //2. 查看上述语句的执行计划
    explain select * from table_name where name="explain";
    

    执行查看上述2语句后,我们可以得出以下执行计划结果

    id select_type table partitions type possible_keys key key_len ref rows Extra
    1 SIMPLE table_name ALL 1 Using where

    上面这个执行计划给到的信息是: 这个结果通过一个简单的语句全表扫描,共扫描1行,使用where条件在t_base_user表中筛选出的。


    要想看懂执行计划,就要明白每一个参数的含义

    id select_type table partitions type possible_keys key key_len ref rows Extra

    id

    有一组数字组成。表示一个查询中各个子查询的执行顺序;

    • id相同执行顺序由上至下。

    image

    • id不同,id值越大优先级越高,越先被执行。

    image

    • id为null时表示一个结果集,不需要使用它查询,常出现在包含union等查询语句中。

    image

    select_type

    每个子查询的查询类型,一些常见的查询类型。

    id select_type description
    1 SIMPLE 不包含任何子查询或union等查询
    2 PRIMARY 包含子查询最外层查询就显示为 PRIMARY
    3 SUBQUERY 在select或 where字句中包含的查询
    4 DERIVED from字句中包含的查询
    5 UNION 出现在union后的查询语句中
    6 UNION RESULT 从UNION中获取结果集,例如上文的第三个例子

    table

    表示该语句查询的表

    partitions

    表分区、表创建的时候可以指定通过那个列进行表分区。 ex:

    create table tmp (
        id int unsigned not null AUTO_INCREMENT,
        name varchar(255),
        PRIMARY KEY (id)
    ) engine = innodb
    partition by key (id) partitions 5;
    

    image

    type

    访问类型

    • ALL 扫描全表数据
    • index 遍历索引
    • range 索引范围查找
    • index_subquery 在子查询中使用 ref
    • unique_subquery 在子查询中使用 eq_ref
    • ref_or_null对Null进行索引的优化的 ref
    • fulltext 使用全文索引
    • ref 使用非唯一索引查找数据
    • eq_refjoin查询中使用RIMARY KEY or UNIQUE NOT NULL索引关联。
    • const 使用主键或者唯一索引,且匹配的结果只有一条记录。
    • system const 连接类型的特例,查询的表为系统表。

    image

    possible_keys

    查询涉及到的字段上若存在索引,则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。可能使用的索引,注意不一定会使用。

    key

    显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL。

    Tips:查询中若使用了覆盖索引(覆盖索引:索引的数据覆盖了需要查询的所有数据),则该索引仅出现在key列表中

    key_length

    索引长度 char()、varchar()索引长度的计算公式:

    (Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
    

    其他类型索引长度的计算公式: ex:

    CREATE TABLE `student` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(128) NOT NULL DEFAULT '',
      `age` int(11),
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx` (`name`),
      KEY `idx_age` (`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
    
    

    name 索引长度为: 编码为utf8mb4,列长为128,不允许为NULL,字段类型为varchar(128)。key_length = 128 * 4 + 0 + 2 = 514;

    image

    age 索引长度:int类型占4位,允许null,索引长度为5。
    image

    ref

    表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

    rows

    扫描行数,也就是说,需要扫描多少行,采能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高,也就有大部分SQL优化,都是在减少这个值的大小。

    Tips: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)

    extra

    extra的信息非常丰富,常见的有:

    • 1.Using index 使用覆盖索引
    • 2.Using where 使用了用where子句来过滤结果集
    • 3.Using filesort 使用文件排序,使用非索引列进行排序时出现,非常消耗性能,尽量优化。
    • 4.Using temporary 使用了临时表

    参考blog → 彻底读懂Mysql执行计划

    展开全文
  • Oracle 11g开始,提供了一种新的固定执行计划的方法,即SQL plan baseline,中文名SQL执行计划基线(简称基线),可以认为是OUTLINE(大纲)或者SQL PROFILE的改进版本,基本上它的主要作用可以归纳为如下两个: ...
  • SAP HANA SQL执行计划: 方法一:直接写执行语句 explain plan SET STATEMENT_NAME = 'select_emp' FOR select ename,sal,hiredate from emp where hiredate > '1980-12-17' and hiredate ; 方法二: 在SAP HANA ...
  • Oracle 查看 SQL执行计划 SQL性能分析

    万次阅读 2019-01-05 09:41:25
    使用Oracle执行计划分析SQL性能 博客分类:  db oracle执行计划sql性能解释  执行计划可以用来分析SQL的性能   一、查看执行计划的方法 1. 设置autotrace  set autotrace off: 此为默认值,即关闭...
  • DATAGRIP 查看SQL执行计划

    千次阅读 2019-08-17 11:03:55
    如图:选中SQL点击右键
  • 使用spm固定sql执行计划

    千次阅读 2013-09-02 21:11:20
    固定sql执行计划 var n number begin :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value, fixed =>'YES', enabled=>'YES'); end; /   删除固定的执行计划:...
  • mysql的sql执行计划详解

    千次阅读 2018-06-19 09:02:47
    explain执行计划包含的信息其中最重要的字段为:id、type、key、rows、Extra各字段详解idselect查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 三种情况: 1、id相同:执行顺序由上至下 2...
  • sql执行计划看懂sql执行步骤

    千次阅读 2016-08-17 09:18:17
    执行顺序规则:  从上往下,遇到平级就停,上面先执行,下面后执行  同级的上面先执行,然后下面 执行  不同级就一层一层往上 那么执行顺序就是 2 4 6 5 3 1 0 解析: 执行0 要先执行1,执行1 要先执行2、3 ...
  • 本文将以中文版本DB 查询分析器7.01版本为例,详细介绍 新增的强大的周和月的“SQL 执行计划”、执行的多条SQL语句返回的记录集保存到多个文件中、执行结果保存文件时用户自定义导出文件中的字段间分隔符与行结束符...
  • SQL 执行计划的理解

    万次阅读 2017-04-13 19:47:55
     在开头要先说明,第一次看执行计划要注意,SQL Server的执行计划是从右向左看的。  名词解析:  扫描:逐行遍历数据。  先建立一张表,并给大家看看大概是什么样子的。  CREATE TABLE Person( Id int...
  • Oracle SQL执行计划分析器功能的创建3步曲: 1 首先,编译XYG_ALD_SESS_PKG的Package头。 (XYG_ALD_SESS_PKG.sql) 2 接着要建立好下面的4个视图对象。因为XYG_ALD_SESS_PKG包体会用到。(View Create Script v...
  • 使用dbms_shared_pool.purge清除共享池中的SQL执行计划
  • EXPLAIN查看SQL执行计划

    千次阅读 2018-08-15 17:16:03
    我们写完一个sql语句,为了让它高性能地执行,一定要explain一下,查看一下它的执行计划。 查看心法: 1.首先从查询类型type列开始查看,如果出现all关键字,那么不用继续看了,全表扫描了 2.查看key列,看是否...
  • 动态语句exec与sp_executesql执行计划区别
  • DB2 SQL执行计划

    万次阅读 2011-04-22 09:02:00
    在oracle数据库中可以在sqlplus中打开trace选项后查看sql执行计划,在DB2数据库中同样也有类似的功能,DB2提供了一个比较简单的工具db2expln工具来查看SQL执行计划 可以通过输入db2expln来查看其帮助信息。 C:...
  • mysql之查看sql执行计划

    千次阅读 2019-03-12 10:13:52
    下面介绍一下mysql的explain,我们可以通过(explain+执行sql)查看sql执行计划。 1)type列:连接类型。一个好的sql语句至少要达到range级别,杜绝出现all级别。 2)key列:使用到的索引名。如果没有选择索引,...
  • mysql的sql执行计划explain

    千次阅读 2018-04-10 21:29:40
    引言:实际项目开发中,由于我们不知道实际查询的时候数据库里发生了什么事情,数据库软件是怎样扫描表、怎样使用索引的,因此,我们能感知到的就只有sql语句运行的时间,在数据规模不大时,查询是瞬间的,因此,在...
  • 【MySQL】SQL执行计划分析

    万次阅读 2018-01-09 09:15:30
    分析为什么这段SQL执行的效率比较低(分析),3.最后根据第二步分析的结构采取优化措施(解决)。而EXPLAIN命令的作用就是帮助我们分析SQL的执行情况,属于第二步。说的规范一点就是:EXPLAIN命令是查看查询优化器...
  • 用awrsqrpt生成SQL执行计划

    千次阅读 2017-01-06 10:17:11
    查询SQL_ID
  • 【视频教学:性能优化】Maclean Liu的Oracle性能优化讲座第一回《真正读懂Oracle SQL执行计划》 本次的讲座议程包括: 如何查看SQL执行计划 正确的执行计划执行顺序 通过示例来实践正确阅读执行计划的方法 介绍...
  • (2010-08-21)在TOAD中查看SQL执行计划

    千次阅读 2010-08-21 10:13:00
     平时分析SQL执行计划的时候都是用Sql*Plus或者PL/SQL Developer来查看的,这对于大都数人来说都应该比较熟悉的方法。今天突然想用用TOAD工具了,毕竟TOAD装了好久了却很少用,平时贪图方便都是用PL/SQL ...
  • 查看SQL执行计划的方法及优劣

    千次阅读 2018-05-06 00:00:00
    作者 | 胡佳伟:云和恩墨技术工程师,有多年数据库优化经验,在一线执行过多个包括通信、保险等行业的优化项目。在 Oracle 的性能分析中,很多时候需要对 SQL 进行分...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,524,958
精华内容 609,983
关键字:

sql执行计划