mysql高级(四)--explain

1.explain是什么？

使用explain关键字可以模拟优化器执行sql语句，从而知道mysql是如何处理你的sql语句的，然后可以分析语句以及表结构的性能瓶颈。

2怎么用？

使用explain+sql语句即可
例：

explain select * from user;

3.执行计划包含的信息

id：表的读取顺序
select_type：数据读取操作的类型
table：哪张表
type：扫描类型
possible_keys：可能用到的索引
key：实际用到的索引
ken_len：索引长度
ref：表之间的引用
rows：该sql语句大致需要扫描的行数
extra：重要的额外信息

4.字段详解

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

执行顺序：t1>t3>t2

②id不同，执行顺序由大到小

执行顺序：t3>t1>t2

③id相同和不同同时存在
id越大的越先执行，id相同的从上到下执行

执行顺序：t3>derived2(衍生表，也可以说临时表)>t2

2.select_type
查询的类型，主要是用于区别普通查询，联合查询，子查询等复杂查询
①.SIMPLE
简单的select查询，查询中不包含子查询或union查询。

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

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

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

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

⑥UNION RESULT
从union表获取结果的select。

3.table
用于知晓操作的是哪张表

4.type
显示使用的是哪种类型查询
类型从好到坏依次为：
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的特例），几乎不会出现，可以忽略不计;

②const：表示通过索引一次就找到了，一般是通过主键索引或者唯一索引作为where的条件会出现该类型

③eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描

const和eq_ref的区别？
两者仿佛都是通过主键或唯一扫描，const是直接按主键或唯一键读取，eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询，简单的说const是单表情况下会产生，eq_ref是多表情况下会产生。

④ref：非唯一性索引扫描，可能会扫描出多个符合条件的行
首先我们为name这个字段创建一个普通索引，方便我们等会演示

 create index index_name on tb_emp(name);

因为name是非唯一性索引，所以可能通过这条语句能找出好多条叫tom的记录(本表数据只有一条tom)，而上面的const和eq_ref因为是唯一性索引的原因，所以查找的结果不可能超过一条，这是ref与上面const和eq_ref的区别。

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


⑥index：全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。（通俗点说就是只查询建立了索引的列就会出现index的情况）

因为id自带主键索引

刚刚name也建立了索引，所以我们查询name看看

id，name一起查

⑦ALL：全局扫描

5.possible_keys：可能被用到的索引，但是不一定被实际用到

6.key：实际被用到的索引

因为刚刚我们为name建立了索引，所以这里可以看到我们可能用到的索引为null，但是实际上却用到了idx_tb_emp_name这个索引。

7.key_len：表示索引中所使用的字节数

表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。在使用索引查询时，当条件越精确，key_len的长度可能会越长，所以在不影响结果的情况下，key_len的值越短越好。

8.ref：显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

1.tb_emp表为非唯一性索引扫描，实际使用的索引列为idx_name，由于tb_emp.name='rose’为一个常量，所以ref=const。

2.tb_dept为唯一索引扫描，从sql语句可以看出，实际使用了PRIMARY主键索引，ref=db01.tb_emp.deptid表示关联了db01数据库中tb_emp表的deptid字段

9rows：根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。

10：extra：重要的额外信息

①Using filesort

Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
mysql中无法利用索引完成的排序操作称为“文件排序”。出现Using filesort就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。

deptname字段未建索引的情况。

为deptname字段创建索引后

后续我们有一篇会专注研究这个，这里暂时混个眼熟

②Using temporary
使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，“十死无生”，急需优化。将tb_emp中name的索引先删除，出现如下图结果，非常烂，Using filesort和Using temporary，“十死无生”。

③Using index
表明使用了索引来查询，相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。
如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where deptid=1）如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。（出现这个是好事）

覆盖索引：select的数据列只从索引中就能取得数据，不必读取数据行。通过上面的例子理解：创建了（name，deptid）的复合索引，查询的时候也使用复合索引或部分，这就形成了覆盖索引。简记：查询使用复合索引，并且查询的列就是索引列，不能多，个数需对应。

注：Using filesort（九死一生） Using temporary（十死无生），也就说出现这两项表明sql语句是非常烂的，急需优化！！！

5.总结

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

②type，type主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。

③key，实际被使用的索引列。

④Extra，额外信息，使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。

着重关注上述四个字段信息，对日常生产过程中调优十分有用。