精华内容
下载资源
问答
  • 执行计划

    千次阅读 2016-11-27 19:31:32
    一、什么是执行计划(explain plan) 执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述。 二、如何查看执行计划 1: 在PL/SQL下按F5查看执行计划。第三方工具toad等。 很多人以为PL/SQL的执行计划...

    一、什么是执行计划(explain plan)

    执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述。

    二、如何查看执行计划

    1: 在PL/SQL下按F5查看执行计划。第三方工具toad等。

    很多人以为PL/SQL的执行计划只能看到基数、优化器、耗费等基本信息,其实这个可以在PL/SQL工具里面设置的。可以看到很多其它信息,如下所示

    clip_image002

    2: 在SQL*PLUS(PL/SQL的命令窗口和SQL窗口均可)下执行下面步骤

     

     SQL>EXPLAIN PLAN FOR
              SELECT * FROM SCOTT.EMP;  --要解析的SQL脚本
     SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

     

     

    clip_image004

    clip_image006

    3: 在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令:


    SQL>SET TIMING ON             --控制显示执行时间统计数据


    SQL>SET AUTOTRACE ON EXPLAIN       --这样设置包含执行计划、脚本数据输出,没有统计信息


    SQL>执行需要查看执行计划的SQL语句


    SQL>SET AUTOTRACE OFF           --不生成AUTOTRACE报告,这是缺省模式


    SQL> SET AUTOTRACE ON           --这样设置包含执行计划、统计信息、以及脚本数据输出


    SQL>执行需要查看执行计划的SQL语句


    SQL>SET AUTOTRACE OFF


    SQL> SET AUTOTRACE TRACEONLY      --这样设置会有执行计划、统计信息,不会有脚本数据输出


    SQL>执行需要查看执行计划的SQL语句


    SQL>SET AUTOTRACE TRACEONLY STAT     --这样设置只包含有统计信息


    SQL>执行需要查看执行计划的SQL语句

    三、看懂执行计划

    1.执行顺序

    执行顺序的原则是:由上至下,从右向左

    由上至下:在执行计划中一般含有多个节点,相同级别(或并列)的节点,靠上的优先执行,靠下的后执行

    从右向左:在某个节点下还存在多个子节点,先从最靠右的子节点开始执行。

    当然,你在PL/SQL工具中也可以通过它提供的功能来查看执行顺序。如下图所示:

    clip_image014

    2.执行计划中字段解释

    clip_image016

    SQL>

    名词解释:

    recursive calls           递归调用

    db block gets           从buffer cache中读取的block的数量当前请求的块数目,当前模式块意思就是在操作中正好提取的块数目,而不是在一致性读的情况下而产生的正常情况下,一个查询提取的块是在查询查询开始的那个时间点上存在的数据库,当前块是在这个时候存在数据块,而不是这个时间点之前或者之后的的数据块数目。

    consistent gets          从buffer cache中读取的undo数据的block的数量数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块,,这里的概念是在你处理你这个操作的时侯需要在一致性读状态上处理多个块,这些块产生的主要原因是因为你在查询过程中,由于其它会话对数据 块进行操作,而对所要查询的块有了修改,但是由于我们的查询是在这些修改之前调用的,所要需要对回滚 段中的数据块的前映像进行查询,以保证数据的一致性。这样就产生了一致性读。

     

    physical reads           物理读 就是从磁盘上读取数据块的数量。其产生的主要原因是:

                      1:在数据库高速缓存中不存在这些块。

                      2:全表扫描

                      3:磁盘排序

    redo size              DML生成的redo的大小

    sorts (memory)           在内存执行的排序量

    sorts (disk)             在磁盘执行的排序量

    2091 bytes sent via SQL*Net to client     从SQL*Net向客户端发送了2091字节的数据

    416 bytes received via SQL*Net from client  客户端向SQL*Net发送了416字节的数据。

    参考文档:SQLPlus User’s Guide and Reference Release 11.1

    clip_image018

    db block gets 、 consistent gets 、 physical reads这三者的关系可以概括为:逻辑读指的是ORACLE从内存读到的数据块块数量,一般来说是:

    consistent gets + db block gets. 当在内存中找不到所需要的数据块的话,就需要从磁盘中获取,于是就产生了物理读。

    3.具体内容查看

    1> Plan hash Value

    这一行是这一条语句的的hash值,我们知道ORACLE对每一条ORACLE语句产生的执行计划放在SHARE POOL里面,第一次要经过硬解析,产生hash值。下次再执行时比较hash值,如果相同就不会执行硬解析。

    2> COST

     

    COST没有单位,是一个相对值,是SQL以CBO方式解析执行计划时,供ORACLE来评估CBO成本,选择执行计划用的。没有明确的含义,但是在对比是就非常有用。

    公式:COST=(Single Block I/O COST + MultiBlock I/O Cost + CPU Cost)/ Sreadtim

     

    3> 对上面执行计划列字段的解释:

    Id: 执行序列,但不是执行的先后顺序。执行的先后根据Operation缩进来判断(采用最右最上最先执行的原则看层次关系,在同一级如果某个动作没有子ID就最先执行。一般按缩进长度来判断,缩进最大的最先执行,如果有2行缩进一样,那么就先执行上面的。)

        Operation:当前操作的内容。

        Name:操作对象

        Rows:也就是10g版本以前的Cardinality(基数),Oracle估计当前操作的返回结果集行数。

        Bytes:表示执行该步骤后返回的字节数。

        Cost(CPU):表示执行到该步骤的一个执行成本,用于说明SQL执行的代价。

        Time:Oracle 估计当前操作的时间。

    4.谓词说明:

    Predicate Information (identified by operation id):

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

    2 - filter("B"."MGR" IS NOT NULL)

    4 - access("A"."EMPNO" = "B"."MGR")

        Access: 表示这个谓词条件的值将会影响数据的访问路劲(全表扫描还是索引)。

        Filter:表示谓词条件的值不会影响数据的访问路劲,只起过滤的作用。

        在谓词中主要注意access,要考虑谓词的条件,使用的访问路径是否正确。

    5、 动态分析

    如果在执行计划中有如下提示:

    Note

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

    -dynamic sampling used for the statement

    这提示用户CBO当前使用的技术,需要用户在分析计划时考虑到这些因素。 当出现这个提示,说明当前表使用了动态采样。我们从而推断这个表可能没有做过分析。

    这里会出现两种情况:

    (1) 如果表没有做过分析,那么CBO可以通过动态采样的方式来获取分析数据,也可以或者正确的执行计划。

    (2) 如果表分析过,但是分析信息过旧,这时CBO就不会在使用动态采样,而是使用这些旧的分析数据,从而可能导致错误的执行计划。

    四、表访问方式

    1.Full Table Scan (FTS) 全表扫描

     

    2.Index Lookup 索引扫描

    There are 5 methods of index lookup:

    index unique scan --索引唯一扫描

    通过唯一索引查找一个数值经常返回单个ROWID,如果存在UNIQUE或PRIMARY KEY约束(它保证了语句只存取单行的话),ORACLE

    经常实现唯一性扫描

    Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.

    index range scan --索引局部扫描

    Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .

    使用一个索引存取多行数据,在唯一索引上使用索引范围扫描的典型情况是在谓词(WHERE 限制条件)中使用了范围操作符号(如>, < <>, >=, <=,BWTEEN)

    index full scan --索引全局扫描

    Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

    index fast full scan --索引快速全局扫描,不带order by情况下常发生

    Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

    index skip scan --索引跳跃扫描,where条件列是非索引的前提情况下常发生

    Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.

    3.Rowid 物理ID扫描

    This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid扫描是最快的访问数据方式



    作者:潇湘隐者

    本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.

    展开全文
  • MySQL执行计划

    万次阅读 2020-07-29 15:20:09
    MySQL执行计划 在企业的应用场景中,为了知道优化SQL语句的执行,需要查看SQL语句的具体执行过程,以加快SQL语句的执行效率。 可以使用explain+SQL语句来模拟优化器执行SQL查询语句,从而知道mysql是如何处理sql语句...

    MySQL执行计划

    在企业的应用场景中,为了知道优化SQL语句的执行,需要查看SQL语句的具体执行过程,以加快SQL语句的执行效率。

    可以使用explain+SQL语句来模拟优化器执行SQL查询语句,从而知道mysql是如何处理sql语句的。

    官网地址: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

    执行计划中包含的信息如下:

    Column Meaning
    id The SELECT identifier
    select_type The SELECT type
    table The table for the output row
    partitions The matching partitions
    type The join type
    possible_keys The possible indexes to choose
    key The index actually chosen
    key_len The length of the chosen key
    ref The columns compared to the index
    rows Estimate of rows to be examined
    filtered Percentage of rows filtered by table condition
    extra Additional information

    id

    select查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序。

    id号分为三种情况:

    1、如果id相同,那么执行顺序从上到下。

    mysql> explain select * from emp e join dept d on e.deptno = d.deptno;
    +----+-------------+-------+------------+------+---------------+-----------+---------+-----------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key       | key_len | ref             | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+-----------+---------+-----------------+------+----------+-------+
    |  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL      | NULL    | NULL            |    4 |   100.00 | NULL  |
    |  1 | SIMPLE      | e     | NULL       | ref  | fk_deptno     | fk_deptno | 5       | oracle.d.deptno |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+-----------+---------+-----------------+------+----------+-------+
    

    2、如果id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。

    mysql> explain select * from emp e where exists (select d.deptno from dept d where d.dname = 'SALES' and d.deptno=e.deptno);
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
    | id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
    |  1 | PRIMARY            | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL            |   14 |   100.00 | Using where |
    |  2 | DEPENDENT SUBQUERY | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | oracle.e.deptno |    1 |    25.00 | Using where |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
    

    3、id相同和不同的同时存在:相同的可以认为是一组,从上往下顺序执行,在所有组中,id值越大,优先级越高,越先执行。

    mysql> explain select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal  where exists (select d.deptno from dept d where d.dname = 'SALES' and d.deptno=e.deptno);
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
    | id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra                                              |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
    |  1 | PRIMARY            | sg    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL            |    5 |   100.00 | NULL                                               |
    |  1 | PRIMARY            | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL            |   14 |    11.11 | Using where; Using join buffer (Block Nested Loop) |
    |  2 | DEPENDENT SUBQUERY | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | oracle.e.deptno |    1 |    25.00 | Using where                                        |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
    

    select_type

    select_type主要用来分辨查询的类型,是普通查询还是联合查询还是子查询。

    select_type Value Meaning
    SIMPLE Simple SELECT (not using UNION or subqueries)
    PRIMARY Outermost SELECT
    UNION Second or later SELECT statement in a UNION
    DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
    UNION RESULT Result of a UNION.
    SUBQUERY First SELECT in subquery
    DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
    DERIVED Derived table
    UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
    UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

    simple:简单查询,不包含子查询和union。

    mysql> explain select * from emp;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    

    primary:查询中若包含任何复杂的子查询,最外层查询则被标记为Primary(不是主键查询的意思)。

    dependent subquery:子查询要受到外部表查询的影响。

    mysql> explain select * from emp e where exists (select d.deptno from dept d where d.dname = 'SALES' and d.deptno=e.deptno);
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
    | id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
    |  1 | PRIMARY            | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL            |   14 |   100.00 | Using where |
    |  2 | DEPENDENT SUBQUERY | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | oracle.e.deptno |    1 |    25.00 | Using where |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
    

    union:若第二个select出现在union之后,则被标记为union。

    union result:从union表获取结果的select。

    mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;
    +----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
    | id | select_type  | table      | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra           |
    +----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
    |  1 | PRIMARY      | emp        | NULL       | ref  | fk_deptno     | fk_deptno | 5       | const |    3 |   100.00 | NULL            |
    |  2 | UNION        | emp        | NULL       | ALL  | NULL          | NULL      | NULL    | NULL  |   14 |    33.33 | Using where     |
    | NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL      | NULL    | NULL  | NULL |     NULL | Using temporary |
    +----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
    

    dependent union:跟union类似,此处的depentent表示union或union all联合而成的结果会受外部表影响。

    mysql> explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000);
    +----+--------------------+------------+------------+--------+-------------------+---------+---------+------+------+----------+-----------------+
    | id | select_type        | table      | partitions | type   | possible_keys     | key     | key_len | ref  | rows | filtered | Extra           |
    +----+--------------------+------------+------------+--------+-------------------+---------+---------+------+------+----------+-----------------+
    |  1 | PRIMARY            | e          | NULL       | ALL    | NULL              | NULL    | NULL    | NULL |   14 |   100.00 | Using where     |
    |  2 | DEPENDENT SUBQUERY | emp        | NULL       | eq_ref | PRIMARY,fk_deptno | PRIMARY | 4       | func |    1 |    21.43 | Using where     |
    |  3 | DEPENDENT UNION    | emp        | NULL       | eq_ref | PRIMARY           | PRIMARY | 4       | func |    1 |    33.33 | Using where     |
    | NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL              | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
    +----+--------------------+------------+------------+--------+-------------------+---------+---------+------+------+----------+-----------------+
    

    subquery:在select或者where列表中包含子查询。

    mysql> explain select * from emp where sal > (select avg(sal) from emp) ;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | PRIMARY     | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    33.33 | Using where |
    |  2 | SUBQUERY    | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | NULL        |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    

    DERIVED: from子句中出现的子查询,也叫做派生类。

    mysql> explain select * from (select deptno,max(sal) from emp group by deptno) t;
    +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------+
    | id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra |
    +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------+
    |  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL      | NULL    | NULL |   14 |   100.00 | NULL  |
    |  2 | DERIVED     | emp        | NULL       | index | fk_deptno     | fk_deptno | 5       | NULL |   14 |   100.00 | NULL  |
    +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------+
    

    UNCACHEABLE SUBQUERY:表示使用子查询的结果不能被缓存。

    mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
    +----+----------------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------------+
    | id | select_type          | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                          |
    +----+----------------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------------+
    |  1 | PRIMARY              | NULL  | NULL       | NULL | NULL          | NULL      | NULL    | NULL  | NULL |     NULL | no matching row in const table |
    |  2 | UNCACHEABLE SUBQUERY | emp   | NULL       | ref  | fk_deptno     | fk_deptno | 5       | const |    1 |   100.00 | Using index                    |
    +----+----------------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------------+
    

    uncacheable union:表示union的查询结果不能被缓存,sql语句未验证。

    table

    table对应行正在访问哪一个表,表名或者别名,可能是临时表或者union合并结果集。

    • 如果是具体的表名,则表明从实际的物理表中获取数据,当然也可以是表的别名。
    • 表名是derivedN的形式,表示使用了id为N的查询产生的衍生表。
    • 当有union result的时候,表名是union n1,n2等的形式,n1,n2表示参与union的id。

    type

    type显示的是访问类型,访问类型表示我是以何种方式去访问我们的数据,最容易想的是全表扫描,直接暴力的遍历一张表去寻找需要的数据,效率非常低下,访问的类型有很多,效率从最好到最坏依次是:

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    一般情况下,得保证查询至少达到range级别,最好能达到ref。

    all:全表扫描,一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。

    mysql> explain select * from city;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  600 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    

    index:全索引扫描这个比all的效率要好,主要有两种情况,一种是当前的查询时覆盖索引,即我们需要的数据在索引中就可以索取,或者是使用了索引进行排序,这样就避免数据的重排序。

    mysql> explain  select empno from emp;
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | emp   | NULL       | index | NULL          | fk_deptno | 5       | NULL |   14 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    

    range:表示利用索引查询的时候限制了范围,在指定范围内进行查询,这样避免了index的全索引扫描,适用的操作符: =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()。

    mysql> explain select * from emp where empno between 7000 and 7500;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | emp   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    

    index_subquery:利用索引来关联子查询,不再扫描全表。

    unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引。

    index_merge:在查询过程中需要多个索引组合使用,没有模拟出来。

    ref_or_null:对于某个字段即需要关联条件,也需要null值的情况下,查询优化器会选择这种访问方式。

    ref:使用了非唯一性索引进行数据的查找。

    eq_ref :使用唯一性索引进行数据查找。

    const:这个表至多有一个匹配行。

    mysql> explain select * from emp where empno = 7369;
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    

    system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现。

    possible_keys

    显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。

    key

    实际使用的索引,如果为null,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的select字段重叠。

    key_len

    key_len表示索引中使用的字节数,可以通过key_len计算查询中使用的索引长度,在不损失精度的情况下长度越短越好。

    ref

    ref显示索引的哪一列被使用了,如果可能的话,是一个常数。

    rows

    根据表的统计信息及索引使用情况,大致估算出找出所需记录需要读取的行数,此参数很重要,直接反应的sql找了多少数据,在完成目的的情况下越少越好。

    extra

    包含额外的信息。

    using filesort:说明mysql无法利用索引进行排序,只能利用排序算法进行排序,会消耗额外的位置。

    mysql> explain select * from emp order by sal;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using filesort |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    

    using temporary:建立临时表来保存中间结果,查询完成之后把临时表删除。

    mysql> explain select ename,count(*) from emp where deptno = 10 group by ename;
    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                                                  |
    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------------------------------------+
    |  1 | SIMPLE      | emp   | NULL       | ref  | fk_deptno     | fk_deptno | 5       | const |    3 |   100.00 | Using index condition; Using temporary; Using filesort |
    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------------------------------------+
    

    using index:这个表示当前的查询是覆盖索引的,直接从索引中读取数据,而不用访问数据表(回表)。如果同时出现using where表明索引被用来执行索引键值的查找,如果没有,表明索引被用来读取数据,而不是真的查找。

    mysql> explain select deptno,count(*) from emp group by deptno limit 10;
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | emp   | NULL       | index | fk_deptno     | fk_deptno | 5       | NULL |   10 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    

    using where:使用where进行条件过滤。

    mysql> explain select * from emp where empno > 7000;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | emp   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   14 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    

    using join buffer:使用连接缓存。

    mysql> explain select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal  where exists (select d.deptno from dept d where d.dname = 'SALES' and d.deptno=e.deptno);
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
    | id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra                                              |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
    |  1 | PRIMARY            | sg    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL            |    5 |   100.00 | NULL                                               |
    |  1 | PRIMARY            | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL            |   14 |    11.11 | Using where; Using join buffer (Block Nested Loop) |
    |  2 | DEPENDENT SUBQUERY | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | oracle.e.deptno |    1 |    25.00 | Using where                                        |
    +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
    

    no matching row in const table:where语句的结果总是false。

    mysql> explain select * from emp where empno = 7469;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    

    更多精彩内容关注本人公众号:架构师升级之路
    在这里插入图片描述

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

    (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是不会去分析的;
    ③ 收集方式灵活。可针对分区表的某个分区进行,可采用并行机制来收集表和索引的信息;


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

    exec dbms_stats.gather_table_stats(ownname => 'AAA', tabname => 'TEST02',estimate_percent =>
    10,method_opt => 'for all indexed columns');

    --收集索引统计信息

    exec dbms_stats.gather_index_stats(ownname => 'AAA',indname => 'ID_IDX',estimate_percent =>
    10,degree => '4');

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

    exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'TEST02',estimate_percent =>
    10,method_opt => 'for all indexed columns',cascade => true);

    (1)explain plan for

    SQL> show user
         USER 为 "HR"
    SQL> set linesize 1000
    SQL> set pagesize 2000
    SQL> explain plan for
    2 select *
    3 from employees,jobs
    4 where employees.job_id=jobs.job_id
    5 and employees.department_id=50;
    已解释。
    
    SQL> select * from table(dbms_xplan.display());
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------
    Plan hash value: 303035560
    ------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
    | 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
    | 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
    | 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
    |* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
    |* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
    filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
    5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
    已选择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信息

    SQL> set autotrace on
    SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
    --输出结果(略)
    -- ...
    已选择45行。
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 303035560
    ------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
    | 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
    | 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
    | 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
    |* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
    |* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
    filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
    5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
    统计信息
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    13 consistent gets
    0 physical reads
    0 redo size
    5040 bytes sent via SQL*Net to client
    433 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    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'));

    SQL> alter session set statistics_level=all;
    SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
    --输出结果
    --...
    已选择45行。
    
    SQL> set linesize 1000
    SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    -----------
    SQL_ID d8jzhcdwmd9ut, child number 0
    -------------------------------------
    select * from employees,jobs where employees.job_id=jobs.job_id and
    employees.department_id=50
    Plan hash value: 303035560
    ------------------------------------------------------------------------------------------------------------------------
    ----------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
    1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------
    ----------------
    | 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 13 | 8 | |
    | |
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    -------------
    | 1 | MERGE JOIN | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | |
    | |
    | 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | |
    | |
    | 3 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | |
    | |
    |* 4 | SORT JOIN | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 |
    6144 | 6144 (0)|
    |* 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | |
    | |
    ------------------------------------------------------------------------------------------------------------------------
    ----------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    -----
    filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
    5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
    已选择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.如果有多个执行计划,可用以下方法查出:

    select * from table(dbms_xplan.display_cursor('&sql_id',0));
    select * from table(dbms_xplan.display_cursor('&sql_id',1));
    */
    SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID 5hkd01f03y43d, child number 0
    -------------------------------------
    select * from test where table_name = 'LOG$'
    Plan hash value: 2408911181
    --------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | | 2 (100)|
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)|
    |* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - access("TABLE_NAME"='LOG$')
    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


    详细demo可见《附录2:使用awrsqrpt.sql查看执行计划》


     

     

    展开全文
  • Mysql执行计划和Mysql优化

    万次阅读 2020-12-05 10:19:56
    1. 执行计划 1.1. 执行计划概念 执行计划是什么:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。 作用:分析你的查询语句或是表结构的性能瓶颈。 语法:Explain + SQL...

    1. 执行计划

    1.1. 执行计划概念

    执行计划是什么:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。
    作用:分析你的查询语句或是表结构的性能瓶颈。
    语法:Explain + SQL语句

    执行计划输出内容介绍:表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询。

    执行计划包含的信息:
    在这里插入图片描述

    1.2. 执行计划各字段详解

    1.2.1. 执行计划-ID

    select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

    规则:
    id相同,执行顺序由上至下
    id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
    id相同不同,同时存在

    第一种情况 Id相同
    在这里插入图片描述
    第二种情况 Id不同
    在这里插入图片描述
    如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。

    第三种情况 Id相同又不同
    在这里插入图片描述
    id如果相同,可以认为是一组,从上往下顺序执行;
    在所有组中,id值越大,优先级越高,越先执行

    1.2.2. 执行计划-select_type

    查询的类型,主要是用于区别普通查询、联合查询、子查询等复杂查询。

    在这里插入图片描述
    具体描述
    在这里插入图片描述
    例子:

    Simple:简单的 select 查询,查询中不包含子查询或者UNION
    在这里插入图片描述
    PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为
    SUBQUERY:在SELECT或WHERE列表中包含了子查询
    EXPLAIN
    select t1.,(select t2.id from t2 where t2.id = 1 ) from t1
    在这里插入图片描述
    在FROM列表中包含的子查询被标记为DERIVED(衍生)
    MySQL会递归执行这些子查询, 把结果放在临时表里。
    select t1.
    from t1 ,(select t2.* from t2 where t2.id = 1 ) s2 where t1.id = s2.id
    在这里插入图片描述

    1.2.3. 执行计划-table

    显示这一行的数据是关于哪张表的
    在这里插入图片描述

    1.2.4. 执行计划-type

    type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    需要记忆的 system > const > eq_ref > ref > range > index > ALL

    **System:**表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计。

    Const:表示通过索引一次就找到了。
    Const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快
    如将主键置于where列表中,MySQL就能将该查询转换为一个常量
    在这里插入图片描述
    读取本表中和关联表表中的每行组合成的一行。除 了 system 和 const 类型之外, 这是最好的联接类型。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时, 将使用该值。
    eq_ref 可用于使用 = 运算符比较的索引列。比较值可以是常量或使用此表之前读取的表中的列的表达式。
    在这里插入图片描述
    Ref:非唯一性索引扫描(二级索引),返回匹配某个单独值的所有行。
    本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
    在这里插入图片描述
    Range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
    一般就是在你的where语句中出现了between、<、>、in等的查询。
    这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。

    在这里插入图片描述
    Index:当查询的结果全为索引列的时候,虽然也是全部扫描,但是只查询的索引库,而没有去查询数据。
    在这里插入图片描述
    All:Full Table Scan,将遍历全表以找到匹配的行。
    在这里插入图片描述

    1.2.5. 执行计划-possible_keys 与 key

    possible_keys:可能使用的key索引。
    Key:实际使用的索引。如果为NULL,则表示没有使用索引。

    查询中若使用了覆盖索引,则该索引和查询的select字段重叠。

    在这里插入图片描述
    在这里插入图片描述
    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。
    key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

    根据key_len这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段是否都被查询用到。
    char和varchar跟字符编码也有密切的联系,
    latin1占用1个字节,gbk占用2个字节,utf8占用3个字节。(不同字符编码占用的存储空间不同)

    1.2.6. 执行计划-key_len

    在这里插入图片描述
    字符类型
    在这里插入图片描述
    字符类型-索引字段为char类型+不可为Null时
    在这里插入图片描述
    字符类型-索引字段为char类型+允许为Null时
    在这里插入图片描述
    索引字段为varchar类型+不可为Null时
    在这里插入图片描述
    索引字段为varchar类型+允许为Null时
    在这里插入图片描述
    varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

    在这里插入图片描述
    日期和时间
    在这里插入图片描述
    datetime类型在5.6中字段长度是5个字节,
    datetime类型在5.5中字段长度是8个字节

    整数/浮点数/时间类型的索引长度
    NOT NULL=字段本身的字段长度
    NULL=字段本身的字段长度+1(因为需要有是否为空的标记,这个标记需要占用1个字节)

    datetime类型在5.6及其以后版本中字段长度是5个字节,datetime类型在5.5中字段长度是8个字节

    key_len 总结:
    变长字段需要额外的2个字节(VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节),所以VARCAHR索引长度计算时候要加2),固定长度字段不需要额外的字节。

    而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL,因为NULL让统计更加复杂并且需要额外的存储空间。

    复合索引有最左前缀的特性,如果复合索引能全部使用上,则是复合索引字段的索引长度之和,这也可以用来判定复合索引是否部分使用,还是全部使用。

    1.2.7. 执行计划-ref

    Ref:显示索引的哪一列被使用了,如果可能的话,是一个常量。哪些列或常量被用于查找索引列上的值。
    在这里插入图片描述
    由key_len可知t1表的idx_col1_col2被充分使用,col1匹配t2表的col1,col2匹配了一个常量,即 ‘ac’。

    1.2.8. 执行计划-rows

    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。
    在这里插入图片描述

    1.2.9. 执行计划-Extra

    Extra:包含不适合在其他列中显示但十分重要的额外信息
    在这里插入图片描述
    Using filesort
    说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
    MySQL中无法利用索引完成的排序操作称为“文件排序”
    在这里插入图片描述
    在这里插入图片描述
    Using temporary
    使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

    在这里插入图片描述
    在这里插入图片描述
    USING index
    表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
    如果同时出现using where,表明索引被用来执行索引键值的查找;

    在这里插入图片描述
    如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
    在这里插入图片描述
    USING index
    Using where
    表明使用了where过滤

    2. 执行计划

    2.1. sql优化实战

    2.1.1. 尽量全值匹配

    EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
    EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
    EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
    
    

    在这里插入图片描述

    2.1.2. 最佳左前缀法则-最左匹配

    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
    在这里插入图片描述

    2.1.3. 不在索引列上做任何操作

    不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
    在这里插入图片描述

    EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
    

    2.1.4. 范围条件放最后

    存储引擎不能使用索引中范围条件右边的列
    在这里插入图片描述

    2.1.5. 覆盖索引尽量用

    尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
    在这里插入图片描述

    2.1.6. 不等于要甚用

    mysql 在使用不等于(!= 或者<>)的时候有可能无法使用索引会导致全表扫描。
    在这里插入图片描述

    2.1.7. Like查询要当心

    like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。
    在这里插入图片描述

    2.1.8. 字符类型加引号

    字符串不加单引号索引失效。在这里插入图片描述

    2.1.9. OR改UNION效率高

    在这里插入图片描述

    2.2. Mysql架构层面优化

    2.2.1 硬件方面的优化

    mysql服务器端增大并发的连接数。如果服务器端并发连接数不够,可以增加并发连接数的配置。

    show variables like '%max_connections%'
    

    在这里插入图片描述

    2.2.2 集群

    1. 可以做基于主从复制
      在这里插入图片描述
      主从复制+读写分离原理:
      在这里插入图片描述
      在这里插入图片描述

    2. 基于mycat或者shading做都写分离
      基于mycat

    cat schema.xml 
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
            <schema name="BLADEX" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex"/>
            <schema name="BLADEX_FLOW" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex_flow"/>
            <schema name="XXL_JOB" checkSQLschema="true" sqlMaxLimit="100" dataNode="xxl_job"/>
            <dataNode name="bladex" dataHost="bladex" database="bladex" />
            <dataNode name="bladex_flow" dataHost="bladex_flow" database="bladex_flow" />
            <dataNode name="xxl_job" dataHost="xxl_job" database="xxl_job" />
    
            <dataHost name="bladex" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <!-- can have multi write hosts -->
                    <writeHost host="bladexM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladexS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="111.com"/>
                    </writeHost>
                    <writeHost host="bladexM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladexS2" url="jdbc:mysql://10.1.33.27:3306" user="root" password="123.com"/>
                    </writeHost>
            </dataHost>
            <dataHost name="bladex_flow" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <!-- can have multi write hosts -->
                    <writeHost host="bladex_flowM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladex_flowS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="123.com"/>
                    </writeHost>
                    <writeHost host="bladex_flowM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladex_flowS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="123.com"/>
                    </writeHost>
            </dataHost>
            <dataHost name="xxl_job" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <!-- can have multi write hosts -->
                    <writeHost host="xxl_jobM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="1111">
                            <readHost host="xxl_jobS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="1111"/>
                    </writeHost>
                    <writeHost host="xxl_jobM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="1111.com">
                            <readHost host="xxl_jobS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="1111.com"/>
                    </writeHost>
            </dataHost>
    </mycat:schema>
    
    

    或者基于shading也是可以的。

    1. 分库分表
      每个微服务一个库
      单个表达可以分表

    单个数据库或者表数据量过大,那么可以选择分库分表的方式去优化。
    垂直分库
    在这里插入图片描述
    水平方向的的分库分表在这里插入图片描述

    展开全文
  • Greenplum执行计划

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

    千次阅读 2018-07-08 09:51:29
    一:什么是Oracle执行计划执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述 二:怎样查看Oracle执行计划?因为我一直用的PLSQL远程连接的公司数据库,所以这里以PLSQL为例:①:配置执行计划需要显示...
  • oracle查看真实的执行计划oracle查看到的执行计划,偶尔并不是真实的执行计划, 查看是否是真实的执行计划,就是看SQL有没有真正的执行,若没有被执行,那么由于统计信息与实际直接误差,或其他原因,执行计划可能...
  • 如何执行计划

    2018-11-04 16:27:01
    如何执行计划 对于计划的执行,这里借用一个别的行业术语:PDCA 1、P (plan) 计划,包括方针和目标的确定,以及活动规划的制定。 2、D (Do) 执行,根据已知的信息,设计具体的方法、方案和计划布局;再根据设计和...
  • 如何查看MySQL执行计划(Explain)

    万次阅读 2020-06-28 23:25:27
    我们经常会使用Explain去查看执行计划,这个众所周知。但我在面试时问面试者,你用Explain主要是看什么?对方的回答大多是“查看是否有使用到索引”,很显然我对这个回答不太满意。 今天我们就来说一说Explain的详细...
  • Mysql查看执行计划

    万次阅读 多人点赞 2018-08-15 15:26:46
    explain执行计划包含的信息 其中最重要的字段为:id、type、key、rows、Extra 各字段详解 id select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序  三种情况:  1、id相同:执行顺序...
  • 使用plsql执行计划进行sql调优(转载) 一段SQL代码写好以后,可以通过查看SQL的执行计划,初步预测该SQL在运行时的性能好坏,尤其是在发现某个SQL语句的效率较差时,我们可以通过查看执行计划,分析出该SQL代码的...
  • 生产数据库中经常出现SQL语句走错执行计划的情况,如果该sqlid还有其他高效的执行计划,可以通过coe_xfr_sql_profile.sql脚本进行绑定,但是如果sqlid没有高效的执行计划,就需要通过自己手工生成一个执行计划(通过...
  • 绑定执行计划

    千次阅读 2018-08-20 15:29:17
    --这次是个测试,先绑定慢的执行计划,然后恢复成原来的 --需要先绑定一个执行计划得到sql_handle --绑定慢的 declare  l_pls number; begin  l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =&gt...
  • db2执行计划

    千次阅读 2018-08-09 09:26:38
    mysql执行计划比较方便看,explain就好了。。。 db2似乎有些麻烦。 下面这个似乎简单些。 工具用的DbVisualizer 9.0.2 执行 CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,'SYSTOOLS'); SYSTOOLS是...
  • 1 生成执行计划 Oracle执行计划,分为预估执行计划 和实际执行计划。   通过told、PL\SQL developer、sql developer、explain plan for 或 set autotrace traceonly等获取的执行计划都是 预估的执行计划。  ...
  • hive执行计划解析

    千次阅读 2019-04-20 18:48:03
    解析sql执行计划流程2.1 大数据两类sql框架2.2 解析sql执行计划流程详解3.sql执行计划映射MR流程3.1过滤类查询sql3.2分组聚合类查询sql3.3join类查询sql4. 执行计划优化 1.hive执行流程的重要性 1)当sql任务非常慢...
  • Oracle 查看执行计划

    千次阅读 2018-09-22 11:11:33
    定义:用来执行目标SQL语句的这些步骤的组合就被称为执行计划。 oracle查看执行计划的几种方式: 1、explain plan命令(可能不准确,SQL语句没有实际执行是个估计值) --语法:explain plan for 目标sql explain ...
  • 在clickhouse 20.6版本之前要查看SQL语句的执行计划需要设置日志级别为trace才能可以看到,在20.6版本引入了原生的执行计划的语法。 Clickhouse> select version(); SELECT version() ┌─version()───┐ ...
  • 数据库执行计划理解

    千次阅读 2019-09-16 14:48:24
    oracle执行计划 执行计划执行顺序: 缩进最多最先执行,缩进相同最上面先执行 同一级如果某个动作没有子ID就最先执行 表访问方式 TABLE ACCESS FULL :全表扫描 TABLE ACCESS BY ROWID :Oracle中存取单行数据最...
  • 之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》 《一个执行计划异常变更的案例 -...
  • 8.8.1 执行计划

    千次阅读 2016-02-03 08:38:44
    8.8.1 执行计划正在更新内容,请稍后
  • DB2执行计划

    千次阅读 2016-11-30 17:26:02
    执行db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL建立执行计划表 2.db2 set current explain mode explain(在数据库所在用户下) 设置成解释模式,并不真正执行下面将发出的sql命令 3.执行你想要分析的s
  • 执行计划(execution plan,也叫查询计划或者解释计划)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想,我们首先应该...
  • 如果要了解执行计划和执行顺序,必须理解执行计划的父子关系。执行计划是一个树状结构,顶层的STATEMENT是这棵树的根。父子关系按照如下的树状结构组织: PARENT FIRST CHILD SECOND CHILD 在这...
  • 序言本篇主要目的有二:1、看懂t-sql的执行计划,明白执行计划中的一些常识。2、能够分析执行计划,找到优化sql性能的思路或方案。如果你对sql查询优化的理解或常识不是很深入,那么推荐几骗博文给你:SqlServer性能...
  • 图形执行计划很有用,其在于易读。不过,关于运算符的过多数据信息并不立即可以看到,在“工具提示”窗口中显示的信息也有所限制,“属性”窗口则显示完整的数据信息。要是有一种方法一次可以查看所有的数据信息该有...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 51,830
精华内容 20,732
关键字:

执行计划