MySQL学习笔记：慢查询与EXPLAIN

这两天学习了慢查询以及后续的EXPLAIN操作，其中EXPLAIN那一段看了蛮久的，因为它包含的id,select_type,table,partitions,possible_keys,keys,key_len,ref,rows,flitered,extra这几个字段，每个字段代表的含义都会对我们执行优化操作提供有力的帮助，所以不敢大意，用心看了几遍。

接下来是我个人对慢查询及EXPLAIN中所包含的字段的含义进行的一个自我梳理。如果有什么理解上的问题请各位老师帮我指出一下，感激不尽！！！

慢查询与EXPLAIN其实都属于SQL优化的一部分，是优化过程中的两个环节。个人理解而言，慢查询是针对我们平时在表中查询数据时发现等待时间或查询时间过长这类极度影响查询效率的问题时进行的一个自查过程。

首先我们需要现在mysql中打开慢查询，在日常操作数据库过程中，我们一般不会打开这类与优化有关的变量开关，因为它们也会影响到我们平时查询数据的效率，故只有在优化的时候我们才会去打开这个开关。我们需要先查看慢查询这个变量的状态，如下图

可以看到slow_query_log默认的value是off，下面的slow_query_log_file代表的是这个文件所在的位置。

我们先将slow_query_log值修改为on，可以使用临时会话进行修改，也可进入my.cnf进行永久修改（不推荐），修改语句为：set global slow_query_log = on;（slow_query_log是全局变量）

因为之前练习的数据库比较小，所以根据教材的源码搞了一个新的测试数据库

为了测试，我先将long_query_time设置为1s（默认是10s），set long_query _time = 1;

设置完毕后，我们查询一条记录：1.SELECT stuno,name FROM student WHERE age BETWEEN  20 AND 30；

接着我们查询符合慢查询条件（查询时间>1s）的记录条数： show status like 'slow_queries';可以看到我们查询的那条就是慢查询

接着开始使用mysqldumpslow工具（不能在mysql的命令行里操作）： mysqldumpslow -a（不转换字符，原汁原味） -s（按查询方式t排序） t（查询时间）  -t（返回前t条记录） 3（返回前3条） /var/lib/mysql/centos7_3-slow.log

查询结果如下图：

可以看到mysqldumpslow很好的定位了符合慢查询的语句并展示了查询所需时间，记录条数和查询的语句；

接下来我们通过EXPLAIN对该慢查询语句进行详细的查看，举个栗子：

图1

图2

可以看到我在基于一张表的情况下对两条不同的语句进行了explain，也许中间存在些许疏漏之处，请各位老师海涵并指出，感谢！

1. table

table展示的是我们是从哪个表中进行select查询的，如果存在union的形式，我们可以看到它还会生成一个union result的<union1,2>表，只是这个表是临时的；

2.id

id是我们根据select生成的，但我们在图1中看到明明有两个select为何最后id还是等于1？那是因为首先我们用到的这第二个select，本质上也是在student这张表上操作的；其次是有时我们使用子查询语句，子查询所用的表（内表）也确实和外层的外表用的不是同一个表，但最后显示id都为1，是因为优化器对我们的语句进行了重写，结果发现子查询可以转变为多表查询，那么就只有一个select存在了；

3.selec_type

select_type的主要类型有simple，primary，union，union result，dependent union，subquery，dependent subquery，materialized,derived；

下面我们一个个来解析类型的含义：

①simple：simple是最简单的查询方式，不包含任何的union查询、subquery查询；

②primary：primary是union、subquery查询的最外层；

③union：union就是使用union查询中的另一个select类型，打个比方，假设我们selec左表union右表，那么左表的select_type就是primary，右表的select_type就是union；

④union result：union result是一种特殊的select_type，它会以临时（temporary）的形式出现在primary和union的下方，但也仅限于使用union，如果是union all，它就不会出现；因为union 需要去重，而union all不需要去重；

⑤dependent union：这是我看视频时看了蛮多次的一个字段，所以可能在用自己的理解阐述时会有点误差，请各位老师勘正。结合③中的描述，dependent union从字面意思上来理解就是select_type为union的右表依赖于select_type为primary的左表或是与左表构成了依赖关系；

⑥subquery：字面翻译就是子查询类型，即在外层select语句包含的内层子查询语句不会经优化器转变为‘semi_join’（即以join方式连接）形式且内层表与外层表不相关的非相关子查询；

⑦dependent subquery：这又是另一个让我研究了蛮久的一个字段，看了视频也看了很多帖子，个人理解是dependent sbquery是不会经优化器转变为‘semi_join’形式的相关子查询，但还有一种特殊情况：子查询语句明明为非相关，但最后explain发现是dependent subquery，是因为在执行过程中优化器将外表要查询的值转为具体数值，去与内层的子查询得到的值进行比对，如果值是true，就记录表中，如果false，则继续比对下一条；

⑧materialized：指子查询语句中select与外层查询语句条件相同的字段；

⑨derived：通过在原有表的基础上通过一系列组合或截取其中部分字段等操作形成一个新的表，我们称之为派生表，在此派生表上进行select查询的类型即为derived；

4.partitions（分区）：不咋见到，就略了；

5.type

type也是让我看了很久的重点，按照查询性能来分依次为：system>const>eq_ref>ref>ref_fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

①system：当表中仅有一条数据且存储引擎是与其精准匹配的访问类型；

②const：根据主键及唯一性二级索引对单表中的常数值（注：不是模糊值）进行匹配的访问类型，仅有一条数据；

③eq_ref：在连接查询（union，join，子查询）时，表分为驱动表和被驱动表，当select的值与被驱动表中包含主键或唯一性二级索引（单个索引/联合索引）的值进行等值匹配时，访问类型即为eq_ref，仅有一条数据;

④ref：对普通的二级索引（非主键/非唯一性/非空）访问类型，包含多条数据；

⑤ref_fulltext：对普通的全文索引访问类型；

⑥ref_or_null：对普通的二级索引（包含了NULL值的）访问类型，包含多条数据；

⑦index_merge：使用了多个的单个索引，并在查询中将它们集合了起来，合成的一种访问类型；

⑧unique_subquery：在子查询语句中对具有唯一性的索引/主键进行访问的类型；

⑨index_subquery：与⑧差不多的原理，只是索引为普通的索引，没有唯一性的约束；

⑩range：对给定的某个字段范围的一种访问类型，也可理解为范围索引；

⑪index：对全部的索引的访问类型（即全索引遍历）；

⑫all：对全表数据的访问类型（即全表遍历）；

可以看到后面两种index即all是对所有的表/索引进行的一种遍历行为，这样的查询效率很低。

6.possible_key：显示全部有可能用到的索引；

7.key：实际应用到的索引；

8.key_len：使用到的实际索引的长度，字符集不同，算得的长度也不同，在默认的utf8中，如图1所示，id的数据类型是int，且为主键，所以非空，就是4个字节；假如我们用了表中带二级索引的key3，它的数据类型是varchar（100）那么它的len就是一个字符用3个字节，即100*3 =300，再加上1个null字节（没有指明非空），再加上2个可变长度（varchar）的动态字节，总和为303字节；

9.ref：当使用索引列等值查询时，对应的与其等值匹配的对象信息，有可能是const（常数），也有可能是具体的一个列；

10.rows：对应的select查询出来的语句条数；

11.filtered：命中数，代表查询语句中命中条件的条数百分比；

12.extra：额外信息，会展示是否使用了索引或where过滤条件等重要信息，按照给的提示进行字面意义上的理解即可，因为测试到的不多，比如图1，2中的using where就是用到了where过滤，using temporary就是表明用到了临时表，临时表是数据库临时建的，说明缺少索引，效率不高需要改进。

以上就是我个人的一些理解和想法，希望看到的老师帮我指点指点！十分感谢！