暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL执行计划详解

MySQL执行计划详解

并发编程之美 2019-09-07
237


介绍

我们在工作过程中，需要对慢sql进行优化，那么了解MySQL的执行计划就至关重要了。本文对MySQL执行计划进行总结整理，文中示例以本机MySQL5.7版本为例，并且均是默认配置。


一、MySQL查询优化器

MySQL Query Optimizer是一个专门负责优化SELECT 语句的优化器模块，主要功能是通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。


工作原理

  • 当客户端向MySQL请求一条Query时，命令解析器模块完成请求分类，区别出是select并转发给MySQL Query Optimizer

  • MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值

  • 并对Query中的查询条件进行简化和转换，比如去掉一些无用或显而易见的条件、结构调整等

  • 然后分析Query中的Hint信息，看显示Hint信息是否可以完全确定该Query的执行计划

  • 如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行相应的计算分析，然后再得出最后的执行计划


二、MySQL性能瓶颈

MySQL的性能瓶颈主要体现在以下三个方面：


CPU

CPU在饱和的时候，一般发生在数据装入内存或从磁盘上读取数据的时候。


I/O

上节在MySQL索引原理中，介绍了MySQL之所以选择B+树作为底层数据结构的原理，一方面就是要减少查找过程中的磁盘I/O的存取次数。这里顺便提一下磁盘I/O的瓶颈发生在装入数据远大于内存容量的时候。


服务器硬件性能瓶颈

取决于服务器当前运行的性能状态，可以通过命令top，free，iostat和vmstat等来查看系统的性能状态。


三、MySQL执行计划

使用Explain查看select语句的执行计划，Explain可以告诉我们这个select查询在数据库中是以一个怎样的执行计划来实现的。测试数据如下：

    create table t1(
        id bigint(20) not null auto_increment comment 'ID',
        col_1 varchar(64) DEFAULT NULL COMMENT 'col_1',
        col_2 varchar(64) DEFAULT NULL COMMENT 'col_2',
        col_3 varchar(64) DEFAULT NULL COMMENT 'col_3',
        primary key(id),
        key inx_col(col_1)
    )engine=InnoDB auto_increment=1 default charset=utf8mb4 COMMENT 't1表';
      
    insert into t1 (col_1,col_2,col_3) values ('col_1','col_2','col_3');


    create table t2(
        id bigint(20) not null auto_increment comment 'ID',
        col_1 int(11) not null DEFAULT 0 COMMENT 'col_1',
        col_2 int(11) not null DEFAULT 0 COMMENT 'col_2',
        col_3 varchar(64) DEFAULT NULL COMMENT 'col_3',
        col_4 varchar(64) DEFAULT NULL COMMENT 'col_4',
        primary key(id),
        key inx_col3_col4(col_3,col_4)
    )engine=InnoDB auto_increment=1 default charset=utf8mb4 COMMENT 't2表';
      
    insert into t2 (col_1,col_2,col_3,col_4) values (1,10,'col_31','col_41');
    insert into t2 (col_1,col_2,col_3,col_4) values (2,20,'col_32','col_42');
    insert into t2 (col_1,col_2,col_3,col_4) values (3,30,'col_33','col_43');


    create table t3(
        id bigint(20) not null auto_increment comment 'ID',
        col_1 varchar(64) DEFAULT NULL COMMENT 'col_1',
        col_2 varchar(64) DEFAULT NULL COMMENT 'col_2',
        col_3 varchar(64) DEFAULT NULL COMMENT 'col_3',
        primary key(id),
        key inx_col(col_1)
    )engine=InnoDB auto_increment=1 default charset=utf8mb4 COMMENT 't3表';
      
    insert into t3 (col_1,col_2,col_3) values ('col_11','col_21','col_31');
    insert into t3 (col_1,col_2,col_3) values ('col_12','col_22','col_32');
    insert into t3 (col_1,col_2,col_3) values ('col_13','col_23','col_33');

    之所以建这样的数据，是希望大家不要关注表的内容，而关注查询的类型。


    1. id

    表示select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。id分为三种情况：

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

      explain select t2.* from t1,t2,t3
      where t3.id = t2.id and t3.id = t1.id
      and t3.col_1 = 'col_11';

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

        explain select t2.* from t2
        where id = (select id from t1 
          where id = (select t3.id from t3 
            where t3.col_1 = 'col_11'));

        • id有相同，也有不同。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

          explain select t2.* from t1,t2
          where t1.id = t2.id 
          and t1.id = (select t3.id from t3 
              where t3.col_1 = 'col_11');


          2. select_type

          表示数据查询的类型，主要用于区分普通查询、联合查询、子查询等复杂查询。包含的值有：

          • SIMPLE

          简单的select查询，查询中不包含子查询或者union。

          • PRIMARY

          查询中若包含任何复杂的子部分，最外层查询被标记为该值，最后执行。

            explain select col_1 from t2 
            where col_1 = (select max(id) from t3);


            • SUBQUERY

            在select或where列表中包含了子查询。

            • DERIVED

            在from列表中包含的子查询被标记为衍生表，MySQL会递归执行这些子查询，把结果放在临时表里（衍生表意味着在查询过程中会在内存或磁盘上创建临时表，临时表是不具有索引的，因此临时表在与其他表关联时性能会比较差。在MySQL较低版本（5.5以下）中，比较容易构造出一个派生表查询，但是在MySQL5.6之后版本，根据optimizer_switch中参数derived_merge是否为开启，from子句中的子查询可以与外部查询综合起来优化）。


            • UNION

            若第二个select出现在union之后，则被标记为union；

            若union包含在from子句的子查询中，外层select将被标记为DERIVED。

            • UNION RESULT

            从union表获取结果的select。

              explain select * from t1 a left join t2 b on a.id = b.id
              union
              select * from t1 a right join t2 b on a.id = b.id;


              3. table

              表示显示这一行的数据是关于哪张表的。包含的值有：

              • NULL：表示select一些与数据库表无关的内容，如select now()

              • <unionM,N>：表示由UNION操作产生的临时表，其中M和N表示产生临时表的源表

              • <derivedM> ：表示是由id为M的表衍生而来的临时表

              • <subqueryM> 表示是由id为M的子查询物化而来的临时表

              MySQL引入了Materialization（物化）这一关键特性用于子查询（比如在IN/NOT IN子查询以及 FROM 子查询）优化。 具体实现方式是：在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。 与此同时，优化器还具有延迟物化子查询的能力，先通过其它条件判断子查询是否真的需要执行。物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。 与之相对的执行方式是对外表的每一行都对子查询进行调用，其执行计划中的查询类型为“DEPENDENT SUBQUERY”。


              4. type

              表示显示查询使用了何种类型。全部值：

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

                一般来说，得保证查询至少达到range级别，最好能达到ref。

                • system

                表示表只有一行记录（等于系统表），这是const类型的特列。实际上，这种类型只出现在MyISAM/Memory存储引擎,InnoDB并不存在这种连接类型。

                  create table t4 (a int primary key) engine myisam;
                  insert into t4 select 1;
                  explain select * from t4;
                  alter table t4 engine innodb;
                  explain select * from t4;


                  • const

                  表示表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，它会再查询刚开始的时候被读取，之后会被优化器当作常量。

                    explain select * from t1 where id = 1;


                    • eq_ref

                    表示唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。

                      explain select * from t1,t2 where t1.id = t2.id;


                      • ref

                      表示非唯一性索引扫描，返回匹配某个单独值的所有行。属于查找和扫描的混合体。

                        explain select * from t1 where col_1 = 'col_1';


                        • fulltext

                        在MySQL5.5及以下版本只有MyISAM存储引擎支持全文索引。


                        • ref_or_null

                        ref_or_null连接类型和ref差不多，只是有个or null的选项。


                        • index_merge

                        索引合并就是对于查询条件比较复杂的情况，MySQL会将条件拆分，使用不同的索引，再将结果进行交、并、去重等操作。

                          create table t5(a int,b int,key(a),key(b));
                          insert into t5 select 1,2;
                          insert into t5 select * from t5;
                          ...
                          explain select * from t5 where a < 1 or b > 3;

                          在建表后，需要人为地再插入一些数据以避免在小数据量的情况下，MySQL直接扫表方式来完成查询。在实际应用中，即便优化器选项中开启index_merge，也未必会使用到，如果确信索引合并效率比较高的话，可以用index hint来指引MySQL使用index_merge。索引合并的不足在于：

                          • 它需要读取多个索引，这就增加了磁盘的I/O，效率不如读取单个索引

                          • 对于复杂的AND/OR，很多时候没办法正确优化，或者即便可以用索引合并，MySQL在执行的时候也未必会用

                          • 索引合并需要对部分结果进行交、并、去重，这本身也是有一定开销的


                          • unique_subquery

                          与eq_ref不同的地方在于,当至多只会有一条结果的select出现在where中的in条件时，执行计划的type会显示为unique_subquery。在高版本MySQL（5.6及以上）由于对in子查询有了很多优化，比较难看见这种类型。


                          • index_subquery

                          与unique_subquery不同点在于：unique_subquery保证了in列表中的值不会是重复的。而index_subquery有点类似于ref，它会利用索引对in子句中的查询结果去重。


                          • range

                          表示只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，结束于另一个点，不用扫描全部索引。

                            explain select * from t2 where id in (1,2);


                            • index

                            表示Full Index Scan，index与ALL区别为index类型只遍历索引树。通常在如下两种情况下可能会出现index连接方式：

                            • 某个索引的列覆盖了查询条件，即可以使用索引覆盖避免访问聚集索引，这时extra列会显示"using index"

                            • 可以利用索引的顺序来遍历记录，这种情况下extra列不会显示using index

                              explain select id from t1;


                              • ALL

                              表示Full Table Scan 全表扫描。


                              5. possible_keys

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


                              6. key

                              实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，possible_keys为NULL。

                                explain select col_3, col_4 from t2;


                                7. key_len

                                表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。长度越短越好。key-len显示的值为索引字段的最大可能长度，并非实际使用长度，即key-len是根据表定义计算而得，不是通过表内检索出的。

                                  desc t1;
                                  explain select * from t1 where col_1 = 'col_1';
                                  explain select * from t1 where col_2 = 'col_2';
                                  explain select * from t1 where col_3 = 'col_3';


                                  8. ref

                                  表示显示索引的哪一列被使用了，如果可能的话，最好是一个常数。如果是常数的话ref列会显示const。对于这个列比较需要关注的是出现func的情况，这表示与索引进行比较的项是经过某种函数运算的。

                                    explain select * from t2 
                                    where col_3 = 'col_32' and col_4 = 'col_42';


                                    9. rows

                                    表示根据表统计信息及索引选用情况，大致估算（对于对于InnoDB表）出找到所需的记录所需要读取的行数。


                                    10. Extra

                                    表示额外信息。包含的值有：

                                    • Using filesort

                                    表示mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为文件排序。

                                      explain select col_1 from t2 where col_1 = 2 order by col_4;


                                      • using temporary

                                      表示使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。仅仅靠explain，通常无法断定临时表是在内存中创建的还是在磁盘中创建的。官方文档上提及常见的情况是group by与order by用了不同的索引。但是实际上并不是说extra里没显示Using temporary就代表执行过程中没有创建临时表。以下情况都是通常会创建临时表的情况：

                                      • from语句带了子查询，MySQL把这个叫做派生derived，实际上也是临时表

                                      • count(distinct col)并且无法使用索引时，会创建临时表

                                      • union/union all会用临时表来合并结果

                                      • 无法使用索引的排序

                                        explain select col_1 from t2 
                                        where col_1 in (1,2) group by col_1,col_4;


                                        • using index

                                        表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行。

                                        如果同时出现using where，表明索引被用来执行索引键值的查找。

                                        如果没有同时出现using where，表明索引用来读取数据而非执行查找动作（从索引上获取了数据）。

                                          explain select col_3,col_4 from t2;
                                          explain select col_4 from t2 where col_3 = 'col_32';


                                          • using where

                                          表示使用了where过滤。对于用户编写的带有where语句的SQL，MySQL有三种方式处理，从好到坏依次是。在存储引擎层用索引来过滤where条件中不匹配的记录;在MySQL服务器层用索引覆盖(extra列会出现Using index)返回记录;从数据表中返回数据后，过滤where中不匹配的条件(extra会出现Using where)这也是MySQL服务器层完成的。


                                          • using join buffer

                                          表示使用了链接缓存。对于多表连接查询，如果被驱动表（下一个待join的表）在连接条件上没有高效的索引(连接类型为all/index/range)的话，通常会使用BNL算法来进行表之间的join。Batched Key Access是MySQL5.6开始出现的一种连接算法。对于出现连接缓冲的extra信息，可以检查下MySQL选择的连接顺序，以及被连接表上的索引情况。一般来说优化器都足以选择最优的连接顺序，如果需要人为指定的话，尽量遵循以下几点：

                                          • 临时表和普通表join 这种情况用临时表作驱动表

                                          • 临时表和临时表join 用小表作驱动表

                                          • 普通表和普通表join 看索引和表大小，都有索引或者都没索引，小表作驱动表。其余情况的话尽量保证被驱动表上连接字段有索引


                                          • impossible where

                                          表示where子句的值总是false，不能用来获取任何元素。


                                          • select tables optimized away

                                          表示在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。


                                          • Only index

                                          表示信息只用索引树中的信息检索出的。


                                          • Const row not found

                                          出现这种情况，排查一下筛选条件值是否错了，或者表数据是否少了。


                                          • No tables used

                                          对于select 1 from dual或者其他不带表的查询，extra信息中会显示此列。


                                          • distinct

                                          表示优化distinct操作，在找到第一匹配的元素后即停止找同样值的动作。


                                          结语

                                          本篇总结了MySQL执行计划，详细介绍了explain中的字段含义以及相关的查询类型。了解了这些，我们才能分析如何进行sql优化。


                                          推荐阅读

                                          MySQL事务隔离级别

                                          MySQL索引原理


                                          看完本文有收获？请转发分享给更多人

                                          关注「并发编程之美」，一起交流Java学习心得


                                          mysql
                                          文章转载自并发编程之美，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                                          评论