mysql 查询不到记录取默认值_MySQL优化查询3：explain和show profiles | 数据分析学习历程全记录...

本文包括以下内容：

Explain的使用和理解

Show profiles的使用和理解

1. Explain的使用和理解：

无论是show processlist还是慢查询日志，两者都是需要开始执行后才能知道该查询请求是否效率低下，如果能够在提交请求之前就能知道该查询的效率，将省下很多时间和资源。

‘Explain’就是这样一种方法。

当然，对于show processlist和慢查询日志得到的SQL语句，也可以通过Explain方法来查看MySQL是如何执行该请求的。

比如输入：

EXPLAIN 

得到结果：

接下来我们看一下每个字段的意思。

1.1 Id：

ID是表的一个代号。如果查询语句中涉及表连接，那么涉及的表的id都是1，但是表顺序是根据查询语句排序。

比如：

EXPLAIN

得到的结果为：

可以发现对于a，b两张表其id都为1，但是因为采用了left join，所以a排在b的前面。那么当我们将left join换成right join后：

EXPLAIN 

结果为：

发现table列中b表在a表前。

如果是子查询，则id不同，id越大，则查询越早。

比如：

EXPLAIN

结果为：

发现student表的id为1，score表的id为2，说明score首先被查询。

1.2 Select_type：

Select_type下具有几个不同的值：

1.2.1 Simple

指简单的select查询，不包含子查询或者Union。比如：

EXPLAIN 

1.2.2 Primary

查询中若包含任何复杂的子查询。

1.2.3 Subquery

在select或where列表中使用了子查询。比如：

EXPLAIN

此时student表为primary，score表为subquery，说明student表中包含了子查询，该子查询是score表。

1.2.4 Derived

在From列表中包含的子查询。

但是当我们输入：

EXPLAIN 

发现结果为：

其select_type仍然是simple，而且只有一个表。那么from子句后的衍生表a跑哪里去了？

这是因为在MySQL5.7之后，优化器多了一个derived_merge的优化选项，即子查询合并优化，对于上面的SQL，优化器会将子句合并到外查询，直接变成SELECT * FROM student。

根据官方的使用手册，我们可以在子查询中加入聚合函数、DISTINCT、GROUP BY、HAVING、LIMIT、UNION或UNION ALL等情况时将产生派生表。

或者可以通过下面的语句将该功能关闭：

SET 

再一次操作后得到的结果为：

此时表student的select_type显示为Derivied。同时可以发现table字段中id为1的表显示<derived2>，表示是从id为2的衍生表中进行查询。

1.2.5 Union

若第二个select出现在union之后，则标记为union

1.2.6 Union result

从Union表获取结果的select

输入：

EXPLAIN

输出结果为：

因为在SQL语句中表score出现在union之后，因此被标记为union。

但是在查找的资料中发现理应出现union result，可是这里没有。但是如果将上面的SQL语句中的Union ALL替换成Union后则出现了union result。

输出结果如下：

这一点比较奇怪，目前也没有查询到相应的解答。

1.2.7 Select_type查询效率

查询效率由高至低依次为：Simple -> Primary -> Subquery -> Derived -> Union -> Union result。

1.3 Table

当前查询的数据来源于哪一张表。

1.4 Partition

使用了哪个分区。

所谓分区表就是为了缓解增删改过程中数据库的压力，将一张大表按照某种规则分区存储在不同的物理位置，理论上存储在不同分区的数据依然属于同一张表。而分表则是将一个大表分成几个小表，小表中的数据不再属于大表。

经验上经常按照时间将大表进行分区。

1.5 Type

访问类型，表示以何种类型访问了对应的表。接下来解释每种访问类型及其查询效率。

Type查询效率：

查询效率由高至低依次为：Null -> System -> Const -> Eq_ref -> Ref -> Range -> Index -> All。

1.6 Possible_keys

可能用到的索引，也就是对应表中存在的所有索引。

1.7 Key

在查询过程中实际用到的索引。

1.8 Key_len

索引的长度，表示索引字段可能的最大长度。

1.9 Ref

表示在索引查询中被当作值使用的在其他表里的字段或者常数。

比如采用一个表连接查询，其中where子句使用了一个索引列（studentid）的常量作为过滤条件：

EXPLAIN 

输入结果为：

发现ref字段中对于表b而言为const，即常量，表示将表a的索引列的一个常量作为过滤条件。

此时如果把过滤条件改为：’WHERE a.studentid > 1’或者使用其它列作为过滤条件，我们会发现结果改变了：

1.10 Rows

扫描的行数。

1.11 Filtered

这个字段感觉在网上的解释都不太清楚，其官网解释是：

翻译过来的意思是：filtered字段表示过滤后返回行数与过滤前存储的表的总行数的比值的预测值。

做了几个简单的测试，发现filtered字段的值与where子句的关系比较紧密。但该预测值往往与实际情况相差甚远，因此感觉filtered的参考价值并不大。

# 

1.12 Extra

其他额外的执行计划信息，额外的信息种类非常多，此处展示几个最常出现的额外信息：

2. Show profile

该语句可以查询了解SQL在执行过程中将时间耗费在哪个阶段。

# 

比如依次运行：

SELECT 

得到输出结果：

可以看到Query_ID为118的即我们刚才的请求。

接着我们输入：

SHOW 

可以查询单个查询请求的时间用在了哪里（sending data是指线程开始访问数据行到结果查询出来并返回客户端的整个过程）。

结果为：

因为我们的请求是select sleep(5)，所以可以看到这里user_sleep对应的持续时间为5秒。

那么如果我们采用：

SELECT 

结果为：

发现多出来statistics和preparing，以及较长时间的过程变为executing。

如果输入请求：

SELECT 

结果为：

此时又多出来创建临时表和删除临时表两个步骤。

可以看到show profile只显示运行过的步骤，如果不涉及该步骤则不显示。

通过以下请求可以查看更详细的信息。

# 

欢迎关注专栏：

数据分析学习历程全记录​zhuanlan.zhihu.com

其他关于SQL的文章：

舟晓南：MySQL优化查询1：索引及其使用技巧 | 数据分析学习历程全记录

舟晓南：MySQL优化查询2：如何查找低效率语句 | 数据分析学习历程全记录

舟晓南：MySQL的执行逻辑和表的结构 | 数据分析学习历程全记录

舟晓南：转行数据分析师如何开始学习SQL | 工科生三个月转行数据分析学习心得

其它关于数据分析的文章：

舟晓南：如何转行和学习数据分析 | 工科生三个月成功转行数据分析心得浅谈

舟晓南：求职数据分析师岗位，简历应该如何写？｜工科生三个月成功转行数据分析心得浅谈

需要SQL学习资料可私信~

数据分析学习社群正式启动~

在这里会分享各种数据分析相关资源，技能学习技巧和经验等等~

详情私信，一起进步吧！

写于成都

2020-8-27