Mysql:Explain关键字

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《Mysql:Explain关键字》，聊聊MySQL，希望可以帮助到正在努力赚钱的你。

Explain：各列的大致含义

id： 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type： SELECT关键字对应的那个查询的类型
table： 表名
partitions： 匹配的分区信息
type： 针对单表的访问方法
possible_keys： 可能用到的索引
key： 实际上使用的索引
key_len： 实际使用到的索引长度
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows： 预估的需要读取的记录条数
filtered： 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra： 一些额外的信息

id详解:

mysql写的查询语句一般都以SELECT关键字开头，比较简单的查询语句里只有一个SELECT关键字，但是下边两
种情况下在一条查询语句中会出现多个SELECT关键字：
        1，查询中包含子查询的情况
        2，查询中包含UNION语句的情况
查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第
一个列。对于连接查询来说，一个SELECT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行
计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的。

注意事项：
    a:id各不相同。对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含子查询的查询语句的
    执行计划中，每个SELECT关键字都会对应一个唯一的id值.查询优化器可能对涉及子查询的查询语句进行重写，从而转换为
    连接查询。所以如果想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了,看id是
    否相同,相同说明把子查询转成了链接查询。
    
    b：id为null。对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是没错的，不过还是有点儿
    特别的东西，比方说下边这个查询：
          explain select * from teacher1 union select * from teacher2;
    UNION会把多个查询的结果集合并起来并对结
    果集中的记录进行去重，怎么去重呢？MySQL使用的是内部的临时表。正如上边的查询计划中所示，UNION子
    句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为的
    临时表（就是执行计划第三条记录的table列的名称），id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。
    跟UNION对比起来，UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，
    所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录。

select_type详解：

只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。下面是可能出现的角色：
A,SIMPLE: 查询语句中不包含UNION,连接查询,子查询的查询都算作是SIMPLE类型。

B,PRIMARY: 对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，
           其中最左边的那个查询select_type值就是PRIMARY

C,UNION: 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，
         其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION

D,UNION RESUULT: MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT

E,SUBQUERY: 非相关子查询，由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍

F,DEPENDENT SUBQUERY: 相关子查询，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

G,MATERIALIZED: 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
该子查询对应的select_type属性就是MATERIALIZED

type：

访问方式,可能存在的值：

A,system: 当新建一个MyISAM表，并为其插入一条记录，此时对表的访问就是SYSTEM。也就是说，当表中只有一条记录
          并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。
          
B,const:  当根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。比如：
     explain select * from person  where p_age = 27;
     
C,eq_ref: 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者
          一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref。
          比如：
     explain select * from teacher tr  join course co  on co.t_id = tr.t_id;
     
D,ref: 当通过普通的二级索引列与常量进行等值匹配时来查询某个表。比如：
     explain select * from teacher where t_age = 27;
     
E,ref_or_null: 当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时。比如：
     explain select * from teacher where t_age = 27 or t_age is null;
     
F,index_merage: 索引合并。比如：
     explain select * from teacher where t_age=27 and t_moblie='180xxxxyyyy';
     
G,unique_subquery: 如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配
                   的话，那么该子查询执行计划的type列的值就是unique_subquery。比如:
     explain select * from t1 where c in (select a from t2 where t1.e = t2.e) or a =1;
     
H,index_subquery: index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引。比如：
     explain select * from t1 where c in (select b from t2 where t1.e = t2.e) or a =1;
     
I,range: 范围查询，比如：
     explain select * from t1 where a > 1;
     
J,index: 当我们可以使用覆盖索引，但需要扫描全部的索引记录时，该表的访问方法就是index。比如：
     explain select b from t1 where c = 1;

K,ALL: 全表扫描

possible_keys和key:

    possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些。
    不过有一点比较特别，就是在使用index访问方法来查询某个表时，possible_keys列是空的，而key列展示的是实际使用到的索引
    注意事项： possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时
    间，所以如果可以的话，尽量删除那些用不到的索引

key_len：

key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

    1，使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定
    字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是
    utf8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。
    
    2，如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。对于变长字段来说，
       都会有2个字节的空间来存储该变长列的实际长度。

ref：

    当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、
    unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的东西是什么，比如只是
    一个常数或者是某个列。

rows:

    如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，
    如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。

filtered:

    代表查询优化器预测在这扫描的记录中，有多少条记录满足其余的搜索条件。对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值

Extra:

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。这些情况分为：

    A,No tables used: 
        当查询语句的没有FROM子句时将会提示该额外信息。比如：
       explain select 1;
    
    B,impossible where: 
        查询语句的WHERE子句永远为FALSE时将会提示该额外信息。比如：
       explain select b from t1 where 1=0;
    
    C,No matching min/max row: 
        当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息。比如：
       explain select max(a) from t1 where a=100;
       
    D,Using index: 
        当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列
        将会提示该额外信息。比如：
       explain select d from t1 where b =1;
     
    E,Using index condition: 
     查找使用了索引，但是需要回表查询数据

    F,Using where: 
        当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列
        中会提示上述额外信息。比如：
       explain select * from t1 where e = 1;
    
    G,Using join buffer(Block Nested Loop): 
        在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问
        速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度。比如：
       explain select * from t1 join t2 on t1.e = t2.e;
        执行结果为：
            Using where; Using join buffer (Block Nested Loop) 
        分析: 
            可以在对t2表的执行计划的Extra列显示了两个提示：
                1，Using join buffer (Block Nested Loop)：这是因为对表t2的访问不能有效利用索引，只好退而求其
                次，使用join buffer来减少对t2表的访问次数，从而提高性能。
               ,2，Using where：可以看到查询语句中有一个t1.e = t2.e条件，因为t1是驱动表，t2是被驱动表，所以
                在访问t2表时，t1.e的值已经确定下来了，所以实际上查询t2表的条件就是t2.e = 一个常数，所以
                提示了Using where额外信息。
        
    H,Using filesort: 
        很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行
        排序，这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。如果某个查询需要使用文件
        排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示。
    
    I,Using temporary: 
        在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行
        许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很
        有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显
        示Using temporary提示。
    
    J,Start temporary,End temporary:
        查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，当执行策略为
        DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划
        的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示
    
    K,FirstMatch(表名）：
        在将In子查询转为semi-join时，如果采用的是FirstMatch执行策略，则在被驱动表执行计划的Extra列就是显示
        FirstMatch(tbl_name)提示

依据这些信息发挥的作用：

   A，性能按照type排序：
        system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery >
        index_subquery > range >index > ALL
    
  B,性能按照Extra排序：
    Using index：用了覆盖索引
    Using index condition：用了条件索引（索引下推）
    Using where：从索引查出来数据后继续用where条件过滤
    Using join buffer (Block Nested Loop)：join的时候利用了join buffer（优化策略：去除外连接、增
    大join buffer大小）
    Using filesort：用了文件排序，排序的时候没有用到索引
    Using temporary：用了临时表（优化策略：增加条件以减少结果集、增加索引，思路就是要么减少待排序的数量，要么就提前排好序）
    Start temporary, End temporary：子查询的时候，可以优化成半连接，但是使用的是通过临时表
    来去重
    FirstMatch(tbl_name)：子查询的时候，可以优化成半连接，但是使用的是直接进行数据比较来去重

根据explain的优化手段：

    1. SQL语句中IN包含的值不应过多，不能超过200个，200个以内查询优化器计算成本时比较精准，超过200
    个是估算的成本，另外建议能用between就不要用in，这样就可以使用range索引了。
    2. SELECT语句务必指明字段名称：SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加
    了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段
    名。
    3. 当只需要一条数据的时候，使用limit 1
    4. 排序时注意是否能用到索引
    5. 使用or时如果没有用到索引，可以改为union all 或者union
    6. 如果in不能用到索引，可以改成exists看是否能用到索引
    7. 使用合理的分页方式以提高分页的效率
    8. 不建议使用%前缀模糊查询
    9. 避免在where子句中对字段进行表达式操作
    10. 避免隐式类型转换
    11. 对于联合索引来说，要遵守最左前缀法则
    12. 必要时可以使用force index来强制查询走某个索引
    13. 对于联合索引来说，如果存在范围查询，比如between,>,

理论要掌握，实操不能落！以上关于《Mysql:Explain关键字》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！