MySql的Explain用法

explain执行计划

explain执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

➤ 通过EXPLAIN，我们可以分析出以下结果：

➤ 使用方式如下：

EXPLAIN +SQL语句

EXPLAIN SELECT * FROM t1

执行计划包含的信息

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

➤ 执行计划各字段含义

id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。

在这里插入图片描述
id相同，执行顺序从上到下，搭配table列进行观察可知，执行顺序为t1->t3->t2。

在这里插入图片描述
如果是子查询id的序号会递增，id值越大执行优先级越高，搭配table列可知，执行顺序为t3->t1->t2。

在这里插入图片描述
id相同不同，同时存在，id如果相同，可认为是同一组，执行顺序从上到下。在所有组中，id值越大执行优先级越高。所以执行顺序为t3->derived2(衍生表，也可以说临时表)->t2。

总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。

select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

1、SIMPLE

简单的select查询，查询中不包含子查询或union查询。

2、PRIMARY

查询中若包含任何复杂的子部分，最外层查询为PRIMARY，也就是最后加载的就是PRIMARY。

3、SUBQUERY

在select或where列表中包含了子查询，就为被标记为SUBQUERY。

4、DERIVED

在from列表中包含的子查询会被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放在临时表中。

5、UNION

若第二个select出现在union后，则被标记为UNION，若union包含在from子句的子查询中，外层select将被标记为DERIVED。

6、UNION RESULT

table

显示sql操作属于哪张表的。

partitions

官方定义为The matching partitions（匹配的分区），值为NULL表示表未被分区。

type

表示查询所使用的访问类型，type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：

system>const>eq_ref>ref>range>index>ALL

要详细了解type取值的作用，需要用数据说话。创建tb_emp（员工表）和tb_dept（部门表）。

tb_emp表

DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `deptid` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_tb_emp_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tb_emp`(name,deptid) VALUES ('jack', '1'); INSERT INTO `tb_emp`(name,deptid) VALUES ('tom', '1'); INSERT INTO `tb_emp`(name,deptid) VALUES ('tonny', '1'); INSERT INTO `tb_emp`(name,deptid) VALUES ('mary', '2'); INSERT INTO `tb_emp`(name,deptid) VALUES ('rose', '2'); INSERT INTO `tb_emp`(name,deptid) VALUES ('luffy', '3'); INSERT INTO `tb_emp`(name,deptid) VALUES ('outman', '4');

tb_dept表

DROP TABLE IF EXISTS `tb_dept`; CREATE TABLE `tb_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deptname` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tb_dept`(deptname) VALUES ('研发'); INSERT INTO `tb_dept`(deptname) VALUES ('测试'); INSERT INTO `tb_dept`(deptname) VALUES ('运维'); INSERT INTO `tb_dept`(deptname) VALUES ('经理');

system：表只有一行记录（等于系统表），是const的特例类型，平时不会出现，可以忽略不计。

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。

由于是非唯一性索引扫描，所以对tb_emp表的deptid字段创建索引：

create index idx_tb_emp_deptid on tb_emp(deptid);

在这里插入图片描述
range：只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。
这种索引的范围扫描比全表扫描要好，因为索引的开始点和结束点都固定，不用扫描全索引。

虽然我们为deptid字段创建了索引并在where中使用了between等，但在如下情况type仍为ALL。

在这里插入图片描述
对比两图，可以看到使用deptid和id进行操作，其type的值一个是ALL也就是进行了全表扫描，一个是range进行了指定索引范围值检索。可能原因deptid并不是唯一索引。