MySQL查询优化explain

一、介绍

#1、是什么
    简称查看执行计划，使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的

#2、用法
    explain + SQL语句

#3、能干吗
    1、表的读取顺序
    2、数据读取操作的操作类型
    3、哪些索引可以使用
    4、哪些索引实际使用
    5、表之间的引用
    6、每张表有多少行被优化器查询
 
#4、执行计划表头包含的信息
    id    select_type    table    type    possible_keys    key    key_len    ref    rows   Extra

 

 

二、各部分详解

1、id

三种情况：
    id相同，执行顺序（表顺序）由上至下。
    id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    id相同和id不同同时存在
表的读取数据

如图

 

 

 

 

 

2、select_type

#1、作用
    查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询


#2、都有哪些类型
#SIMPLE    
    简单的select查询，查询中不包含子查询或者UNION

#PRIMARY    
    查询中若包含任何复杂的子查询，最外层标记  （鸡蛋壳，最后查询）  

#SUBQUERY    
    在select或where列表中包含的子查询

#DERIVED    
    在from列表中包含的子查询被标记为DERIVED(衍生)MySQL会递增执行这些子查询，把结果放到临时表中

#UNION
    若第二个select出现在UNION之后，则被标记为UNION，
    若UNION包含在from子句的子查询中，外层select将被标记为 DERIVED

#UNION RESULT
    从union表获取结果的select。（两种union表的合并）
View Code

 

 

 

3、type

#type显示的是访问类型
#1、type有哪些
    从最好到最差依次是：
        #system>const>eq_ref>ref<range>index>all
    一般来说，保证查询至少达到range级别，最好能达到ref

#详解
#system：
    表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。

#const：
    mysql就能将该查询转换为一个常量，表示通过索引一次就找到了，const用于比较primary key或者unique索引。

#eq_ref：
    唯一性索引扫描，对于每个索引键，表中都只有一条记录与之匹配。常见于主键或唯一索引扫描。（公司CEO）

#ref：
    非唯一性索引扫描，返回匹配某个单独值的所有行。（程序员）

#range：
    只检索给定范围的行，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好

#index：
    index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从磁盘中读取的）。

#all：
    将遍历全表
View Code

 

 

 

4、possible_keys、key和key_len

#possible_keys：
    显示可能应用在这张表中的索引，一个或多个,不一定实际使用

#key：
    实际使用的索引，若为null，则没有使用到索引。
    两种可能，①没建立索引。②建立索引，但索引失效）。
    #查询中若使用了覆盖索引，则该索引仅出现在key列表中。理论上没有，却用到了索引

#key_len
    表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确型的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据定义计算而得，不是通过表内检索出的。
View Code

 

 

 

5、ref

#ref
    显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或者常量被用于查找索引列上的值。只有当type为ref的时候，ref这列才会有值        

#rows
    根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数，所以越小越好。可以用来查询sql的读取行数。
View Code

 

 

6、rows

# rows
    根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数，所以越小越好。可以用来查询sql的读取行数。

 

 

 

 

7、extra

 #extra：
    包含不适合在其他列中显示但十分重要的额外信息


#几种类型
#常用3种
#Using filesort：  （非常影响性能，应避免）
    说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序操作称为“文件排序”。

#Using temporary：（非常影响性能，应避免）
    使用了临时表来保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。

#USING index：
    表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如没有同时出现useing where，表明索引用来读取数据而非执行查找动作。

#覆盖索引（Covering Index）：一般说为索引覆盖。
    就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
    注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。

----------------------------
#不常用的几种
#using where
    表示使用了where

#using join buffer
    使用了连接缓存

#impossible where
    where子句中的值总是false

#select tables optimized away

#distinct
View Code

 

 

练习：看下图执行顺序

 

 

 

posted @ 2019-08-12 22:27  pdun  阅读(1806)  评论(1)  编辑  收藏  举报