MySQL优化之explain

        在日常的MYSQL优化中我们常常看到这样一个关键词:explain,例如这种:

        SELECT * FROM Cloud_Order WHERE money > 10;


        explain是什么呢?使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

        1.表的读取顺序

        2.数据读取操作的操作类型

        3.哪些索引可以使用

        4.哪些索引被实际使用

        5.表之间的引用

        6.每张表有多少行被优化器查询


1、体验explain命令

        首先我们来体验一下explain命令是怎么使用的,以及输出的结果是什么:

explain select * from user_info ;

        输出结果:

        冷暖自知一抹茶ck

        发现很使用起来很简单,只要explain后边跟着SQL语句就完事了(MySQL5.6之前的版本,只允许解释SELECT语句,从 MySQL5.6开始,非SELECT语句也可以被解释了)。


2、为什么需要explain命令

        我们很多时候编写完一条SQL语句,往往想知道这条SQL语句执行是否高效。或者说,我们建立好的索引在这条SQL语句中是否使用到了,就可以使用explain命令来分析一下!

        简单来说:通过explain命令我们可以学习到该条SQL是如何执行的,随后解析explain的结果可以帮助我们使用更好的索引,最终来优化它!

        通过explain命令我们可以知道以下信息:表的读取顺序,数据读取操作的类型,哪些索引可以使用,哪些索引实际使用了,表之间的引用,每张表有多少行被优化器查询等信息。


3、查看explain命令结果

        explain命令输出的结果有10列:id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra


        1)、id

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

        在id列上也会有几种情况:

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

                如果id不相同,id的序号会递增,id值越大优先级越高,越先被执行。

                (一般有子查询的SQL语句id就会不同)

        explain一下拥有子查询的SQL

  冷暖自知一抹茶ck


        2)、select_type

表示select查询的类型

        select_type属性下有好几种类型:

                SIMPLLE:简单查询,(不使用UNION或子查询)

                PRIMARY:如果查询包含UNION 或子查询,则最外层的查询被标识为PRIMARY(最外面的SELECT)

                UNION:表示此查询是 UNION 中的第二个或者随后的查询

                DEPENDENT:UNION 满足 UNION 中的第二个或者随后的查询,其次取决于外面的查询

                UNION RESULT:UNION 的结果

                SUBQUERY:子查询中的第一个select语句(该子查询不在from子句中)

                DEPENDENT SUBQUERY:子查询中的 第一个 select,同时取决于外面的查询

                DERIVED:包含在from子句中子查询(也称为派生表)   【导出表的SELECT(FROM子句的子查询)】

                UNCACHEABLE SUBQUERY:满足是子查询中的第一个 select 语句,同时意味着 select 中的某些特性阻止结果被缓存于一个Item_cache 中

                UNCACHEABLE UNION:满足此查询是 UNION 中的第二个或者随后的查询,同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中


        3)、table

该列显示了对应行正在访问哪个表(有别名就显示别名)。

        当from子句中有子查询时,table列是 <derivenN>格式,表示当前查询依赖 id=N的查询,于是先执行 id=N 的查询

        

        4)、type

该列称为关联类型或者访问类型,它指明了MySQL决定如何查找表中符合条件的行,同时是我们判断查询是否高效的重要依据。

        从最好到最差的连接类型为:system >> const >> eq_reg >> ref >> range >> index >> ALL

    

以下为常见的取值:

 const、system:可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.

        该表至多有一个匹配行,在查询开始时读取,或者该表是系统表,只有一行匹配。其中 const 用于在和 primary key 或 unique 索引中有固定值比较的情形。

eq_ref:访问索引,返回某单一行的数据.(通常在联接时出现,查询使用的索引为主键或惟一键)

        使用这种索引查找,最多只返回一条符合条件的记录。在使用唯一性索引或主键查找时会出现该值,非常高效。

ref:访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生

        一种索引访问,也称索引查找,它返回所有匹配某个单个值的行。此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询。

 range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西,并且该字段上建有索引时发生的情况(注:不一定好于index)

        范围扫描,就是一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行。这个类型通常出现在 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() 的操作中,key 列显示使用了哪个索引,当 type 为该值时,则输出的 ref 列为 NULL,并且 key_len 列是此次查询中使用到的索引最长的那个。

index:以索引的顺序进行全表扫描,优点是不用排序,缺点是还要全表扫描

        全索引扫描,和 ALL 类型类似,只不过 ALL 类型是全表扫描,而 index 类型是扫描全部的索引,主要优点是避免了排序,但是开销仍然非常大。如果在 Extra 列看到 Using index,说明正在使用覆盖索引,只扫描索引的数据,它比按索引次序全表扫描的开销要少很多。

ALL:全表扫描,应该尽量避免

        全表扫描,这个类型是性能最差的查询之一。通常来说,我们的查询不应该出现 ALL 类型,因为这样的查询,在数据量最大的情况下,对数据库的性能是巨大的灾难。

NULL:在执行阶段不需要访问表。


        5)、possible_keys

显示可能应用在这张表中的索引。如果为空,没有可能的索引。

        6)、key

这一列显示MySQL实际决定使用的索引。如果没有选择索引,键是NULL。
MYSQL很少会选择优化不足的索引,此时可以在SELECT语句中使用USE INDEX(index)来强制使用一个索引或者用

        7)、key_len

这一列显示了在索引里使用的字节数,当key列的值为NULL时,则该列也是 NULL
使用的索引的长度。在不损失精确性的情况下,长度越短越好

        8)、ref

这一列显示了哪些字段或者常量被用来和key配合从表中查询记录出来。
显示索引的哪一列被使用了,如果可能的话,是一个常数

        9)、rows

这一列显示了估计要找到所需的行而要读取的行数,这个值是个估计值,原则上值越小越好。

        10)、filtered

显示了通过条件过滤出的行数的百分比估计值。

        11)、extra

其他的信息

        常见的取值如下:

                Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。

                Using index:使用覆盖索引,表示查询索引就可查到所需数据,不用扫描表数据文件,往往说明性能不错(只用到索引,可以避免访问表.)

                Using Where:在存储引擎检索行后再进行过滤,使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。

                Using temporary:在查询结果排序时会使用一个临时表,一般出现于排序、分组和多表 join 的情况,查询效率不高,建议优化。

                Using filesort:对结果使用一个外部索引排序,而不是按索引次序从表里读取行,一般有出现该值,都建议优化去掉,因为这样的查询 CPU 资源消耗大。



        通过explain,我们可以对原有的sql做进行不同的分析,可以分析出有没有使用到索引,可以知道这是一条快SQL还是慢SQL,EXPLAIN的作用就是帮我们显示出SQL的性能瓶颈和各项可能的结果,这对于我们去优化MSQL的查询有着重要的意义,根据EXPALIN字段返回不同的结果,我们就能对原有的慢SQL进行改造了。






冷暖自知一抹茶ck
请先登录后发表评论
  • 最新评论
  • 总共0条评论