MySQL连载：不会explain，简历别写熟悉SQL优化

文字：2003 阅读：6分钟

昨天说了一条SQL执行慢的原因，我们还要会使用explain命令具体分析SQL语句的执行计划，找出问题出在哪里。

在SQL语句前加上explain关键词，MySQL在执行的时候就会返回执行计划的相关信息，我们准备两个最常用的表：user（用户信息表）和user_info（用户信息扩展表），两个表使用user.id和user_info.user_id进行关联。

explain select * from user

返回结果中包含了执行计划的访问类型、访问的表格，使用的索引以及查询的行数等信息。

id

id: 表示查询中select子句或者操作表的顺序，id值越大，代表优先级越高，越先执行。如果id相同，代表优先级相同，从上到下顺序执行。

SQL语句1：
explain 
select * from `user` u 
left join user_info ui on u.id = ui.user_id 
where 
u.id = 1

两表直接关联查询时，id相同都为1，说明可以顺序执行，我们将这个SQL再修改一下，先缩小user_info的范围，再进行关联查询，语句如下：

SQL语顺2：
explain 
select * from `user` u 
left join (
  select user_id from user_info where id < 100
) as ui on u.id = ui.user_id 
where u.id = 1

我们发现子查询的id为2,说明它被最先执行。

select_type

select_type 代表查询的类型，我们可以通过这一列来初步判断查询是简单查询 还是复杂查询 。有以下几种类型：

1 SIMPLE 简单的，就像SQL语句1中使用id=1进行主键查询

2 PRIMARY 在查询语句中包含任何复杂的子查询时，最外层被标记为Primary. 如果SQL语句2返回结果

3 SUBQUERY 当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY

4 DERIVED 表示包含在from子句中的子查询的select，在我们的 from 列表中包含的子查询会被标记为DERIVED

5 UNION 如果union后边又出现的select 语句，则会被标记为UNION

6 UNION RESULT 代表从union的临时表中读取数

举一个例子：

SQL语句3：
explain 
select * from `user` u1  where u1.id = 1 
union 
select * from `user` u2 where u2.id = 2 

从这个语句中我们可以印证一下，第一条为子查询最外层，所以被标记PRIMARY ,第二条在union后面出现了select 所以被标记为UNION ,第三条，我们从临时表中读取数据，被标记为UNION RESULT.

table

table 表示正在访问的表，可能是表的真实名字，也可能是临时表的别名。如SQL语句3

type

这是一个比较重要的列，代表访问类型或关联类型，即MySQL决定如何查找表里的行。是SQL优化中非常重要的指标，面试官如果问到EXPLAIN的相关知识，其实他是很想听到你对这部分的理解。

需要记住一个性能的排序：从好到坏依次是：null > system  > const > eq_ref > ref  > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

我们介绍几个重要的

1. NULL 代表在执行时，不用走索引或是访问表

explain  select min(id) from `user` 
这就好比别人问你一个问题，你连想都不用想。

2 SYSTEM 代表只有一行记录，用于系统表，不需要磁盘I/O，比如我们查看一个配置项

3 CONST代表查询时命中了PRIMARY KEY或是唯一索引，扫描效率极高

explain select * from `user` where id = 1 limit 1 

命中主键

4 EQ_REF 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

5 REF 非唯一性索引扫描，返回匹配某个单独值的所有行

6 ref_or_null 类似ref，但是可以搜索值为NULL的行

7 index_merge 表示使用了索引合并的优化方法

8 range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引,一般就是在你的where语句中出现between、<>、in等的查询。

9 index Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

10 all 将遍历全表以找到匹配的行，性能最差。

extra

extra 这一列表示不适合其它列的内容

1. Using filesort 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为“文件排序”

2. Using temporary 使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by

3. Using index 表示相应的select操作中使用了覆盖索引（Covering Index）,避免回表，效率不错！

4. Using where 使用了where条件

5. Select tables optimized away 没有遍历表和索引，就可以返回数据了，

其它

possible key 表示可能应用在表中的索引，一个或是多个.

key 实际使用的索引，如果为NULL 则没有使用索引

key_len 表示查询用到的索引长度（字节数），原则是越短越好

ref 显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

row 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

面试时，能够回答上面的内容这可能是大家都了解的，其实在explain还有一个隐含的信息：执行计划的成本

explain  FORMAT=JSON select *  from `user`  
#返回如下内容:
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.80"
    },
    "table": {
      "table_name": "user",
      "access_type": "ALL",
      "rows_examined_per_scan": 34,
      "rows_produced_per_join": 34,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "6.80",
        "prefix_cost": "7.80",
        "data_read_per_join": "65K"
      }
    }
  }
}

cost_info 即代表本次执行计划的成本

read_cost 的成本包括两部分，I/O成本和检测 rows × filter条记录的CPU成本.

eval_cost 是检测rows × filter条记录成本

prefix_cost 为read_cost+eval_cost ，即读取一个表的成本

我们在SQL调优时，可以对比不同版本SQL的执行成本。

PS:

在执行explain 后我们还可以执行：show warnings

会返回Level、Code、Message三个字段，当Code 为1003时会给出SQL的优化建议，可以作为sql语句优化的参考。

PS:

各大互联网公司曝出了裁员消息，今年同样也是互联网保险艰难的一年，据统计有200万代理人离职，管理层在监管互联网保险线上业务的同时，又引导部分类型业务转为线下，监管层层加码，或许这只是一个开始。

关注我，带你一起进化！

更多面试资料