2. Explain关键字解析

一.  explain 列描述

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

二. 各个列名详细介绍：

　id： 我们写的查询语句一般都以SELECT 关键字开头，比较简单的查询语句里只有一个SELECT 关键字，但是下面两种情况下在一条语句中会出现多个SELECT 关键字：

• 查询中包含子查询的情况
• 查询中包含UNION语句的情况

　　查询语句中每出现一个SELECT关键字，MySQL 就会为它分配一个唯一的id 值。这个 id 值就是 explain 语句的第一个列。对于连接查询来说，一个 select 关键字后边的 from 子句可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的 id 值都是相同的。但当使用 union 合并两个查询的结果时，MySQL 会把合并的结果暂时保存到一个临时表中，再对这个临时表进行去重，此时这个临时表 id 就是 NULL

　select_type：每个 select 关键字代表的小查询都定义了一个 称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。常见的 select_type 有：

• SIMPLE： 查询语句中不包含 UNION 或者子查询的查询都算作是 SEMPLE类型，连接查询也算 SEMPLE 类型
• PRIMARY：对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY
• UNION：对于包含UNION、UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION
• UNION RESULT：MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT。
• DERIVED：针对将派生表物化之后的表进行查询
• SUBQUERY：非相关子查询，由于select_type 为 SUBQUERY 的子查询会被物化，所以只需要执行一遍
• DEPENDENT SUBQUERY：相关子查询，select_type 为 DEPENDENT SUBQUERY 的查询可能会被执行多次
• MATERIALIZED：当查询优化器在执行包含子查询语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED

　table：表名。如果给当前要查询的表添加了别名，那就是别名名称。如果是派生子表，通常命名为 <derived>。如果是 union 合并还未去重的临时表，通常命名为 <union4,5>

　type：访问方法，通常有以下几种类型：

• system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。
• const：当我们根据主键或唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const
• eq_ref：在连接查询时，如果被驱动的表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是 eq_ref
• ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
• ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL 值时，那么对该表的访问方法就可能是 ref_or_null
• index_merge：当通过 or 关键字查询，且 or 的两端使用到了不同的索引，那么对该表的访问方法就可能是 index_merge
• unique_subquery：如果查询优化器决定将 IN 子查询 转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery
• index_subquery：与 unique_subquery 类似，只不过访问子查询中的表时使用的是二级索引。
• range：如果根据主键查找大于/小于或者 根据主键、二级索引使用 IN 查询内都是常量的查询，那么就可能是 range
• index：当我们可以使用覆盖索引，但需要扫描全部索引记录时，该表的访问方法就是 index
• ALL：全表扫描

　possible_keys 和 key：possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key 列表示实际用到的索引有哪些。不过有一点比较特别，就是在使用 index 访问方法来查询某个表时，possible_keys列是空的，而key 列表示的是实际使用到的索引。 注意： possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花更长的时间，所以如果可以的话，可以使用 USE/IGNORE/FORCE INDEX 使用/忽略/强制使用指定的索引

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

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

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

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

　filtered：代表查询优化其预测在这扫描的记录中，有多少条记录满足其余的搜索条件

　Extra：用来说明一些额外的信息，我们可以通过这些额外的信息来更准确的理解MySQL 到底将如何执行盖顶的查询语句。通常会有以下类型：

• No tables used：查询语句没有 from 子句
• Impossible WHERE：查询语句的 where 子句永远为 false 
• No matching min/max row：查询列表处有 MIN 或者 MAX 聚合函数，但是并没有符合 where 子句中的搜索条件的记录
• Using index：查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下展示的信息
• Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引
• Using where：使用全表扫描来执行对某个表的查询，并且该语句的 where 子句中有针对该表的搜索条件
• Using join buffer (Bolck Nested Loop)：连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫 join buffer 的内存块来加快查询速度
• Using filesort：很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，这种在内存或磁盘上进行排序的方式统称为文件排序。
• Using temporary：在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类，比如我们在执行许多包含 DISTINCT、GROUP BY、UNION 等子句的查询过程中，如果不能有效的利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。
• Start temporary、End Temporary：查询优化器会优先尝试将 in 子查询转换成 semi-join，而 semi-join 又有好多种执行策略，当执行策略为 DuplicateWeedout 时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将展示 Start temporary，被驱动表查询执行计划的 Extra 列将显示 End temporary 提示
• FirstMatch：将 In 子查询转为 semi-join时，如果采用的是 FirstMatch 执行策略，则显示 FirstMatch

 

三  总结

　性能按type 排序：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > range > index > ALL

　性能按Extra 排序：

1. Using index： 用了覆盖索引
2. Using index condition：用了条件索引
3. user where：从索引查出来的数据后继续用 where 条件过滤
4. Using join buffer (Bolck Nested Loop)：join 的时候利用了 join buffer（优化策略：去除外连接、增大join buffer 大小）
5. Using filesort：用了文件排序，排序没有用到索引
6. Using temporary：用了临时表
7. Start temporary、End temporary：子查询的时候，可以优化成半连接，但是使用的是通过临时表来去重
8. FirstMatch (tbl_name)：子查询的时候，可以优化成半连接，但是使用的是直接进行数据比较来去重

 

四  常见优化手段

1. SQL语句中 IN 包含的值不应过多，不能超过 200 个，200 个以内查询优化器计算成本时比较精确，超过200 个时估算成本增大，另外建议能用 between 就不要用 in， 这样可以使用 range 索引
2. SELECT 语句务必指明字段名称， SELECT * 增加了很多不必要的消耗；增加了使用覆盖索引的可能性。
3. 当只需要一条数据时，使用limit 1
4. 排序时主义能否利用到索引
5. 使用 or 时如果没用到索引，可以改为 union all 或 union
6. 如果 in 不能用到索引，可以改成 exists 看是否能用到索引
7. 使用合理的分页方式以提高分页效率
8. 不建议使用前缀 % 模糊查询
9. 避免在 where 子句中对字段进行表达式操作
10. 避免隐式类型转换
11. 对于联合索引来说，要遵守最左前缀法则
12. 必要时可以使用 force index 来强制查询走某个索引
13. 对于联合索引来说，如果存在范围查询，比如 betewwn、>、< 等条件时，会造成后面的索引字段失效
14. 尽量使用 inner join，避免 left join，让查询优化器来自动选择小表作为驱动表
15. 必要时可以使用 straight_join 来指定驱动表，前提是条件本身是 inner join