本文共 8737 字，大约阅读时间需要 29 分钟。

如果看不清图片，可以尝试看看图片版本：

 

explain 关键字分析

EXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执行SELECT语句的信息：    ·EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。    ·如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。该节解释EXPLAIN的第2个用法。借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的统计（例如关键字集的势），这样会影响优化器进行的选择。参见13.5.2.1节，“ANALYZE TABLE语法”。还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不只是SELECT开头。EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。

explain 是可以获取到Mysql到底底层是如何执行select语句的，让我们看到他优化的步骤，如果处理select，并且告诉我们多个表之间，是怎么进行连接，并且连接的顺序的。

并且我们使用explain分析工具，可以知道什么时候可以为表增加索引，并且得到一个可以使用索引方式来寻找数据的更优的查询sql。

如果说，由于我们设计的索引，导致了mysql优化器执行效率低下。我们也可以通过这个关键字进行分析。

我们还可以知道mysql优化器是否是用了一个最优的表查询顺序进行表之间的关联查询。因为mysql优化器毕竟是死的，虽然会帮我们分析出最优的方式，但是毕竟业务的千变万化，还是需要我们程序员对其进行分析设计的。

 

explain table_name

示例：

 

如果对表进行分析，那么就是分析整个表的情况，例如上方我就分析了我的日志表，有字段、字段类型、是否可以为null，是否为主键，是否有默认值，包括最后一栏Extra，自增属性。

这个很好理解哈，就是对表结构进行分析。

 

那么我们最关键的是分享sql执行计划

EXPLAIN [EXTENDED] SELECT select_options

示例：

 

我们直接查询整个表，发现有很多属性栏。这些属性栏分别是什么意思？

此处我们先打预习一下基本知识：

1.id（select识别符，这是select的查询序列号，相同序列号为从上往下；不同序列号，为以大到小）

 

 

2.select_type（select类型，为以下几种）

select查询的类型，主要的功能就是区别于普通查询和联合查询、子查询之类的复杂查询

（1）SIMPLE：简单的上select，不使用union或者子查询

（2）PRIMARY：最外层的select

（3）UNION：UNION中的第二个或者后面的SELECT语句

（4）DEPENDENT UNION：UNION中的第二个或者后面的select语句，取决于外面的查询

（5）UNION RESULT：UNION的结果

（6）SUBQUERY：子查询中的第一个SELECT

（7）DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询

（8）DERIVED：导出表的SELECT（FROM子句的子查询）

 

 

3.table（输出的行所引用的表）

 

 

4.type（链接类型，以下列出，排序方式为最优到最差）

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eqref > ref > fulltext > refornull > indexmerge > uniquesubquery > indexsubquery > range > index > ALL 一般来说，得保证查询至少达到range级别，最好能达到ref。

（1）system：表仅有一行（就是系统表）。这是常量const连接类型的特殊连接类型。

 

（2）const：常量连接类型，就是表中最多有一个匹配行，他将在查询开始是被读取。因为只有一行，在这行的列值可被优化器剩余部分认为就是常数。const表很快，因为只会读取一次。

const用于常量值比较主键（primary key）或者说unique（唯一索引）索引的所在部分。举个例子

主键索引：

 

唯一索引：

 

（3）eq_ref：唯一索引扫描；对于每个来自前面表（链表查询）的行组合，从该表中只会读取一行。这就是最好的链表方式，当然，除了const类型。他用在一个索引的所有部分被连接使用，并且索引是unique或者primary key。

eq_ref可以用于使用 = 操作父比较的带有索引的列。比较值可以是常量或者是一个使用在该表前面所读取的表的列的表达式。

举例说明：

 

 

对比上述两条sql，可以很清晰看出两个优势和劣势了，虽然第二条sql不是eq_ref

 

（4）ref：

非唯一性的索引扫描，同eq_ref的区别就是，他可以匹配索引的某一行，但是也有可能是多行，因为他不是唯一索引；对于每个来自前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果链接只使用键的最左前缀，或者说如果键不是 UNIQUE(唯一键) or PRIMARY KEY (主键)（或者连接不是基于关键字选择单个行的情况下），则会使用ref。如果使用的键是少量的数据，那效率还是可以的。

ref可以用于 = 或者 <=> 操作符的带索引的列。举例说明：

 

或者说，链表情况下的查询，当某张A表的某个字段是 B表的 的一个索引字段，那么就是如下效果

 

可以看出，b表是走的常量匹配关联，这里可以对比上面的 eqref，可以知道，当A表字段为主键的时候，我们就可以直接使用eqref操作。

当前我们直接将a表中的某个属性直接匹配常量的时候，那么就可以匹配b表中的确定数据了

 

（5）refornull：这个连接类型同ref一个道理，就是在带有索引的的字段，使用匹配一个常量同时或匹配一个不为null的条件。就会出现refornull。

 

如果我们只判断该字段 为 null，那么就是回归到ref类型，所以两者差别不大，这里可以看出来

 

（6）indexmerge：该链接类型就是索引合并优化方法。也就是说他同时使用了多个索引进行查询，我们可以在key这一列看出所有使用的索引名称。在keylen栏可以看出使用的所有的最长的关键元素

示例：同时使用两个索引，我们可以使用or

 

 

（7）unique_subquery：看类型拼接就知道，就是一个唯一索引和子查询；

示例：笔者怎么设计索引，都没达到这个效果，按照官方描述来说，就是当a表中的user_id是一个唯一索引就可以了，他怎么都是全表扫描

（有哪位童鞋能够指点一下为啥我这没有成功，留言即可）

 

如果我把其查询字段改为 user_id ，那么a表就会变成index，也就是上升了一个等级

但是他怎么也没达到子查询类型

 

 

（8）indexsubquery：这个同uniquesubquery很类似了，

示例：就是将外层查询的条件为子查询的结果的存在索引匹配。（有哪位童鞋能够指点一下为啥我这没有成功，留言即可）

 

 

（9）range：见文知意，范围查询，也就是我们，我们再使用索引的时候，是一个范围，这里有很多典例：

BETWEEN

 

IN

 

/< /> 大于小于

 

（10）index：这个级别基本上可以理解为和接下来的ALL是一个意思了，只不过，他是全表扫描的时候，是使用的索引，可以理解为是对索引这个树进行全表扫描的，对于索引数据来说，通常都是非聚簇索引，数据量是小很多，且存在有序，那么效率是比ALL快的。

 

（11）all：全表扫描，很差的一种情况，如果出现了这种，如果在一个尚半万、上亿数据量的表中进行查询，那就崩了，数据库的CPU基本上是打满的，这种情况，如果是业务强烈要求，我们也可以将其尽量优化至index。

 

对于上述中all与index的区别很明显，我们在查询条件中使用的精确查询字段，那么他在扫描的时候，虽然是全表扫描，但是是索引数据的扫描，数据量下降，整个查询的IO也会下降的。所以优化方式应该很好理解了。

 

对于上述的扫描类型，很多笔者认为都是一些很常见的行为习惯就可以解决的，当我们思维中形成一个定式，对整个优化方向有一定了解，那么我们在设计查询脚本的时候，那么就会有一定的下意识去避免这些很简单的问题。虽然无法做到写一个sql就是最优的，但是我们在不断的挖坑，填坑的过程中，就会有一定的积累，并且学会总结，那么就可以成长了。

 

5.possiblekeys ：possiblekeys列是告诉程序员，mysql优化器是通过哪个索引去寻找数据。

注意：possiblekeys列是独立于 explain 分析出来的表的查询顺序的，什么意思呢？就是说possiblekeys中的一些 索引 在实际使用中 不能按照 我们 扫描表的顺序去使用的。通俗的来讲，就是该列列出来的索引索引是可能会被使用的，并不是一定会被使用的。

如果possible_keys 这列是null，那么就是没有匹配的索引，那么这个时候就需要去分析一下查询条件中，哪些可以使用上索引，如果没有，那么就需要我们创建一个合适的索引了。

 

6.key：mysql实际上用到的索引，如果possible_keys 为null，那么这个基本上为null。

在这里，我们可以主动让sql强制使用某个索引，也可以强制让某个sql不使用索引

FORCE INDEX、USE INDEX或者IGNORE INDEX

示例：

（1）FORCE INDEX：强制mysql使用一个特定的索引，通常情况来说，只有mysql优化器做出了错误的优化，我们才能使用这个关键字

那我们试着写一下看看，强制索引语法；

先不强制 ，看看

 

看到，mysql优化器分析出由两个可以选择的索引，但是他选择了idx_name1；

我们任性一下，叫他使用idx_name2

 

mysql还是比较听话的；如果说，我们强制使用一个不存在的索引会怎么样呢？

 

是的，我们有点强人所难了。都不存在，还强迫mysql使用

那么我们把查询条件改为age，也就是说，强迫他使用一个毫不相干的索引会怎么样呢？

 

是的，毫不相干，匹配都匹配不了，别说，实际使用了，所以，强制索引需要注意，当你不确定自己的脑回路有用，就别轻易强制索引。

 

（2）USE INDEX：建议索引；也就是说，我们可以控制 possible_key中的索引内容，这样，mysql只能从我们建议中的索引选择对应的索引，这样mysql就不会选择其他乱系八糟的索引了。

我们试一下

 

和FORCE INDEX()差别不大，因为，我们限制住了只有一个索引，那么就是只能用这个索引，要么就不用，要么就用不了，哈哈，这是变相强制。

那么使用一个不存在的索引和上述强制索引是一样的，也就是说，当某些场景下，我们请强制索引是一样的效果。注意事项也是一样的。

 

（3）IGNORE INDEX：禁止使用索引；通俗来说，就是我们可以设置一个索引，让某个sql，不能使用某些索引，通常场景在于，我们有些索引，还未删除，但是暂时不能直接删除，那么又不想mysql去选择他，这个时候，就可以这样做了

上面可以知道，我们定义的sql默认是选择索引idx_name1，那么我们不想让他选择这个索引，就可以指定排除某个索引

 

那么mysql就会选择其他索引了。

 

7.key_len：显示mysql实际使用的索引的长度。那么如果没有使用索引，那么就是null。

通常来说，我们可以通过这个指标去确定mysql使用的索引的使用情况，精确匹配长度。通常来说，这个数值越大，说明我们对索引的使用精度越高。至于这个数值是如何得出的呢？

此处也简单说一下：

就是我们索引上的字段的类型再加上索引上字段的类型的长度

比如说，

1）.常见的索引类型 varchar 和 int，在字段设置的时候，通常都需要指定长度，int缺省值为11，varchar缺省值为255，那么对应的长度就是 11； 255；

2）.那么如果是varchar或者char等字符串类型长度，按照不同的字符编码需要乘以不同的长度，都知道UTF-8是占3个字节，GBK是占用2个字节。

3）.并且varchar是一种动态变化的长度的字符串类型，要加上2个字节

4）.我们字段上配置该字段允许为空，又要加上1个字节

那么我们举几个实例来说算一下：

例如 ： 一个 utf-8环境 可变字符varchar(30)，可以允许为NULL，这个索引的长度是多少呢？

首先 30位 * 3倍 字节 + 2 + 1 = 93；

例如2：一个 utf-8环境 固定字符 char(30)，允许为NULL，那么这个索引长度？

30*3+1 = 91

例如3：一个 utf-8环境 整形 int(11)， 不允许为NULL。

11

 

上面就是767，有兴趣的同学可以算算是不是这个值，我给你们看看name的结构

255*3+2 == 767

 

8.ref：标识使用哪个列或者尝试与键一起从表中选择行。怎么理解呢?

就是显示索引中哪一列被使用上了，或者说，可能是一个常数。通常可以用来查询哪些列或者常量被用作查询索引列上是数值。

常量

 

如下就是，b表使用的就是a表中的某个索引字段

 

9.rows：行数，这个很好理解，就是mysql认为在查询这张表的时候，需要扫描的行数。

扫描1行，常量匹配，且使用索引，所以就1行

 

扫描2行，因为我就两行数据，这就是全表扫描

 

 

10.Extra：在这个里面，显示了Mysql解决查询的较为详细的分析信息，有很多情况，一一列举，一一分析举例

（1）Distinct：当mysql发现了第一个匹配的数据后，就停止搜索其他数据了。

 

（2）Not exists：表示Mysql对左连接表查询的一个优化，当发现有一个匹配的左连接的行的数据后，就不再为前面的行组合在这个表中再查询更多的行的数据。

通俗的来讲就是：a 表左连接 b 表，链接条件为 各自的 name，并且b表的name是有唯一索引， 然后我们再链接后的过滤条件中加上b 表的name 要等于 “wangji”；那么而这个时候，a表的name也要等于 “wangji”，其实就好优化了，a表也是常量匹配，b表也是常量匹配，并且b表中存在唯一索引，当a表找b表中找到了“wangji”的数据后，就会终止扫描匹配，因为他知道不可能有其他数据也有name=“wangji”了。

 

（3）range checked for each record (index map: #)

翻译过来就是：检查每个记录的范围

就是当mysql实在没有发现有什么合适的索引可以匹配了，那么mysql发现你的组合查询条件中有部分是可以使用范围查询来使用索引的，那么这个时候，就会进行范围的查询，这是一种下下策，毕竟效率肯定比没有索引要快。

 

（4）Using filesort

看到这个，我们需要提防了；因为这是一个文件排序现象，什么叫文件排序？我们的索引结构是有顺序的，当查询sql所需要的排序并不是我们索引设计的排序规则，没有匹配成功的时候，就会失效，然后会对这批所查询出来的数据进行一个外部的索引排序。这是比较消耗性能的，那么我们需要对其进行优化。

模拟一个文件排序的现象

 

上述就是索引是 文件排序，我们看看索引

 

那么我们优化一下索引

 

再次执行sql

 

变成index scan了，很完美，至于什么是index scan，我们后面会说，

所以我们要明确一点，当排序的时候也使用到了索引，那么将是对效率有很大的提升的。对排序效率而言。

 

（5）Using index

Using index代表表示相应的查询语句中使用了覆盖索引（Covering index）,没有直接去访问数据的树，是一个很高效的结果。当然我们也会碰到同时出现了using where，这就是代表我们所用到的索引是用作索引键值的查询；如果没有出现using where呢？那么就是说明索引只是用来读取数据，而不是理由索引进行查询操作，这两者怎么分别呢？

这么说，当我们不需要查询到具体数据相关的查询时，那么这个就是最优的，因为我们所需要的结果，完全可以从索引树上就可以得到结果了。

相反，如果我们需要一些不止索引树上的数据，那么就需要用到using where，也就是需要回表查询。

对于我们自己定义出来的聚簇索引，也是直接可以操作，因为在聚簇索引中的叶子节点上是存在具体是数据的，那么将不需要回表操作。

 

这个关键字的意思是：使用了临时表用于保存中间的一个结果。常见于聚合查询和排序查询中。我们也举例说明一下；

索引原本如下

 

执行聚合查询

 

我们分析一下，mysql到底怎么想的：先从cm_test中找到 name等于1的所有数据，因为name不是唯一键，那么可能有多条，然后再将查询出的结果组成一个新的临时表，然后对临时表中的数据进行分组。按照age进行分组。再输出结果。是不是这样呢？

我们看看show profile

创建临时表，再存放数据，然后后面又删除了。。

那么我们怎么优化呢？对于聚合查询，我们可以将聚合字段与查询条件组合，形成复合索引。

设计过的索引

 

新的执行分析

 

 

（7）Using where

 

上面说了Using Index，那么Using where是必须也要说的。

例如下方的Using where; Using index;

我们查询的条件都在索引中，所以使用了索引，但是是索引范围查询，因为我们的查询条件并不是user_id最左前缀原则。虽然使用了索引，但是扫描文档数还是全表。

例如这个，就是最左前缀原则，实现了索引查询，

那么还有一种最差的情况，就是直接在真实数据的表中全表查询，没有索引。

 

 

（8）Using sort_union(...), Using union(...), Using intersect(...)

Using sort_union：出现在使用 and 和 or 查询数据量大的时候，先查询主键，然后再进行排序合并，然后将数据进行返回，这个其实和我们的归并排序很类似的算法。

Using union：出现在使用 or 连接各个使用索引的条件的时候，然后将各个查询出来的结果进行合并返回。

Using intersect：这个表示使用and条件，将各个索引查询出的结果进行合并返回。可以理解为是两个结果后的交集

以上都为索引合并结果方法。通俗的来描述就是，两次索引查询的结果进行合并，上面有三种，都是合并的意思，但是细分，例如union，就是两个结果集都要；sort_union就是合并后进行排序，类似归并排序；第三种就是去并集，取两者相同的部分。

其实还有很多，此处只列出了三种。

 

（9）Using index for group-by

表示mysql优化器发现了一个索引，可以用于分组查询和去重查询所有列，不需要访问聚簇索引（含有数据集的表）。是一种高效的查询。

 

（10）Using index condition

在MySQL 5.6版本后加入的新特性（Index Condition Pushdown）;会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

使用了索引，但是是先通过索引进行过滤，然后过滤完成后，在对剩下的数据进行条件过滤。

也称之为“索引下推” ，说明此方法，出现了回表操作

 

为什么出现了回表操作呢？可以理解为，查询的结果，没有返回我们所需要的所有数据，比如上方的home字段，我们试着去掉看看。

 

效率提升，上升到了 Using where; Using index;优化成功

 

 

总结：

上述说了很多分析说明，不难发现，很多细节；例如，不是使用了索引，就是最优的。

我们需要关注很多信息，才能更精确的发现很多指标，更好的定位sql的瓶颈，例如，索引使用的长度，索引所扫描的行数等等，更多的我们在Extra中还发现很多关键字，都是一种描述性的关键字，可以更直接的知道当前sql做了什么。上述所简单分享的某些 explain 的指标，我们也只能作为参考性，并不绝对作为实际执行情况，比如rows；这也是mysql说的“来自Mysql优化器的 “教育猜测” ”；

 

转载地址：https://changemax.blog.csdn.net/article/details/113651943 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！