谈谈MYSQL中的Explain

​

目录

前言

基本介绍 

Explain结果列解读

概述 

key

key_len

type

extra

前言

我们在设计一个系统的时候，有时候通常为了基础业务，写出的查询sql语句并不高效，从而影响到用户使用系统的整体体验感不是很好，我们通常在系统的测试阶段会开启MySQL中的慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，只要SQL执行的时间超过了我们设置的时间就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了，从而就可以对这些语句进行调优优化，使用 Explain来分析 SQL 语句的性能。

默认情况下，MySQL 数据库没有开启慢查询日志 ，需要我们手动来设置这个参数。

如果不需要调优的话，一般 不建议启动该参数，因为开启慢查询日志会或多或少的带来一定的性能影响。慢查询日志支持将日志记录写入到文件中。

开启慢查询语句（永久生效）：

#如果要永久生效，需要修改配置文件 my.cnf；
#[mysqld] 下增加或修改参数，slow_query_log 和 slow_query_log_file，重启即可，如下：

#开启慢查询
slow_query_log=1
#慢查询日志存放的位置
slow_query_log_file=/home/services/mysqllog/mysql-slow.log
#规定慢 SQL 的查询阙值，超过这个值将会被记录到慢查询文件中，单位：秒
long_query_time=3
#慢查询日志以文件的形式输出
log_output=FILE

基本介绍 

Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。（注意，如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。

• Explain结果是基于数据表中现有数据的。
• Explain结果与MySQL版本有很大的关系，不同版本的优化器的优化策略不同。

语法：

explain sql语句

示例：

mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

在查询中的每个”表”会输出一行，这里的“表”的意义非常广泛，不仅仅是数据库表，还可以是子查询、一个union 结果等。 

Explain结果列解读

概述 

下面我们仔细的介绍下几个比较重要的列 

key

这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通 过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有：
ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

• ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
• index: Full Index Scan，index与ALL区别为index类型只遍历索引树
• range:只检索给定范围的行，使用一个索引来选择行
• ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
• const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
• NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

• Using where: 不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
• Using temporary： 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
• Using filesort： 当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
• Using join buffer： 改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
• Impossible where： 这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
• Select tables optimized away： 这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
• No tables used： Query语句中使用from dual 或不含任何from子句

​