SQL优化（四）：explain命令详解：SQL性能分析利器

矫情吗；* 2022-02-27 01:52 359阅读 0赞

1.  id：执行的序号，这个语句没有子查询，故只有1，值越大越先执行；
2.  select\_type：数据读取类型，这里只是针对单表，也没有UNION操作，故是SIMPLE，其他类型包括：PRIMARY, UNION RESULT, SUBQUERY等；
3.  table：查询涉及的表；
4.  partitions：查询涉及的表的哪些分区；
5.  type：访问类型，指明了MySQL以何种方式查找表中符合条件的行，这个也是需要重点关注的一项指标，包含的类型为：ALL, index, range, ref, eq\_ref, const/system, NULL，性能依次变好，其中：
    
     *  ALL：为全表扫描，性能最差；
     *  index：为全索引扫描，性能通常也是不够理想；
     *  range：为对索引进行范围扫描，然后返回对应的数据行，如SQL包含BETWEEN，>=，IN()等语句时就是range；
     *  ref：为索引查找，返回匹配单个索引值的数据行，如果不是范围查询，则需要至少达到这个级别；
     *  eq\_ref：也是索引查找，不过最多只返回一条记录，通常是在主键或者唯一性索引上，性能较好；
     *  const/system：为表只有最多一个匹配行，直接读取对应的数据行，不需要查找索引再根据索引的结果读取数据行，通常是主键或唯一性索引上有固定值的情形；
     *  NULL：在执行阶段不需要访问表，直接从索引返回需要的值。
6.  possible\_keys：该次查询可以使用的索引；
7.  key：该次查询实际使用的索引；
8.  key\_len：使用索引时，所使用的索引值的最大字节数，当type为NULL时，该值也为NULL；
9.  ref：哪些字段或常量配合key将数据行从表中获取出来；
10. rows：估计查找到所需要的行，大概需要扫描读取多少数据行，这个值越小越好；即存储引擎大概需要读取并返回rows的值这么多行数据给server层；通常与下面的filtered一起分析，即如果rows为5，而实际需要的查询结果为1条记录，则filtered的值为大概为1/5；如下：
    
        mysql> explain SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
        +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
        | id | select_type | table             | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra       |
        +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
        |  1 | SIMPLE      | test			   | NULL       | ref  | idx_ul_type   | idx_id_type | 769     | const,const |    5 |    11.11 | Using where |
        +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
        1 row in set, 1 warning (0.00 sec)
    
    SQL执行情况：这条SQL实际返回了一条记录。
    
        mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
        省略具体内容
        1 row in set (0.00 sec)
    
    如果把price BETWEEN 80 AND 80.999的条件去掉，则返回5条记录：
    
        mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3;
        省略具体内容
        5 rows in set (0.00 sec)
    
    索引情况如下：在id和type的两个列建了一个联合索引，不包含price列，故由于price没有索引，实际存储引擎会读取并返回5条记录给server层，server层在根据 price BETWEEN 80 AND 80.999再过滤最后剩下一条记录。
    
        KEY `idx_id_type` (`id`,`type`) USING BTREE
    
    如果把price加到idx\_id\_type索引中，即`idx_id_type` (`id`,`type`,`price`) ，则执行情况如下：
    
        mysql> explain SELECT type, expire_date, id FROM test WHERE ul='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
        +----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
        | id | select_type | table             | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
        +----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
        |  1 | SIMPLE      | test			   | NULL       | range | idx_id_type   | idx_id_type | 778     | NULL |    1 |   100.00 | Using index condition |
        +----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
        1 row in set, 1 warning (0.00 sec)
11. filtered：此查询条件所过滤的数据的百分比，表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例；越大表示存储引擎返回给server层的都是有用的，故说明效率较高，通常都是100%；
12. Extra：此处执行的额外信息，一次SQL查询的Extra可以包含以下的一条或多条，包括：
    
     *  Using where：表示SQL语句存在WHERE条件，不是获取全表数据，一般使用了WHERE条件都会有这个；
     *  Using index：表示使用覆盖索引返回数据，不需要访问表，通常是指该查询性能较好；
     *  Using index condition：这个是在5.6版本后加入的新特性，主要作用是当WHERE中的某个条件对应的字段，如a，是加了索引的，但是无法使用，如使用了 a like ‘%abc%’ 这种SQL，在没有这个特性之前，存储引擎此时无法使用这个字段对应的索引了，需要回表找到符合WHERE其他条件的数据行，即full row正行数据，然后传给server层，最后在server层处理a like '%abc%'这个条件，即使a这个字段加了索引，这样就存储引擎就需要读取较多的数据行和传给server层较多的数据。拥有这个特性之后，存储引擎层会使用索引来处理a like '%abc%'这个SQL，进一步过滤，从而减少回表查询的次数和传给server层的数据量；详见官方文档：[8.2.1.5 Index Condition Pushdown Optimization  
        ][8.2.1.5 Index Condition Pushdown Optimization]，如下为详细例子：  
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMTM1NzM_size_16_color_FFFFFF_t_70]
     *  Using temporary：表示使用了临时表来进行分组、排序或者多表join，通常表示查询效率不高，需要优化；
     *  Using filesort：表示需要使用一个外部文件索引来对结果进行排序，而不是直接根据内部索引顺序从表中读取数据，这个过程通常需要消耗比较大的CPU资源，需要优化。

[8.2.1.5 Index Condition Pushdown Optimization]: https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMTM1NzM_size_16_color_FFFFFF_t_70]: /images/20220227/21563cfaab5346e3add40013cacc67bf.png