Mysql(一)Explain详解

概述

概念：通过explain命令获取select语句的执行计划。explain主要用于分析查询语句或表结构的性能瓶颈。

核心：

• 表的读取顺序。（id）
• 数据读取操作的类型。（select_type）
• 哪些索引可以使用，实际使用了哪些索引。（possible_keys）
• 表之间的引用。（ref）
• 每张表有多少行被优化器查询。（rows）

id

概念：id列的编号是select的序号列。有几个select就有几个id。

select查询分类：简单查询和复杂查询。

复杂查询：

• 简单子查询
• 派生表（from语句中的子查询）
• union查询

执行逻辑：

• id相同，执行顺序由上往下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

select_type

概念：表示对应行应该是简单或者是复杂的查询。

查询分类：

• simple：简单查询，不含有子查询和union

• primary：复杂查询中最外层的select

• subquery：包含在select中的子查询（不在from中的子查询）

• deriverd：包含在from子句中的子查询，结果存放在一个临时表中，也称为派生表（derived的英文含义）

• union：在union中的第二个和随后的select

• union result：从union临时表检索结果的select

table

概念：表示explain的一行正在访问哪个表。

type

概念：表示查询所使用的访问类型，即Mysql决定如何查找表中的行。

type的分类，性能从好到坏如下：

• system
• const
• eq_ref
• ref
• range
• index
• ALL

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

# sql建表语句见文章最后
EXPLAIN SELECT * FROM (SELECT * FROM tb_emp WHERE id=1) emp;

const

概念：表示通过一次索引就找到了结果，常出现于primary key或unique索引。因为只匹配一行数据，所以查询非常快。如将主键置于where条件中，MySQL就能将查询转换为一个常量。

EXPLAIN SELECT * FROM tb_emp WHERE id=1

结论：对于system和const可能实际意义并不是很大，因为单表单行查询本来就快，意义不大。

eq_ref

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

primay key或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录；

简单的select查询不会出现这种type。

EXPLAIN SELECT * FROM tb_emp,tb_dept WHERE tb_dept.id=tb_emp.deptid AND tb_emp.name='outman'

ref

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

相比eq_ref，不适用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

range

概念：只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。

#这种索引的范围扫描比全表扫描要好，因为索引的开始点和结束点都固定，不用扫描全索引。
EXPLAIN SELECT * FROM tb_emp WHERE deptid>2;

# 得到的是range操作
EXPLAIN SELECT * FROM tb_emp WHERE id BETWEEN 1 AND 3
# 得到的是ALL操作，因为deptid不是唯一索引。
EXPLAIN SELECT * FROM tb_emp WHERE deptid BETWEEN 1 AND 3

index

概念：全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。

EXPLAIN SELECT deptid FROM tb_emp;

ALL

概念：全表扫描

EXPLAIN SELECT * FROM tb_dept;

总结：一般而言，需要保证查询至少达到range级别，最好能达到ref级别。

possible_keys&key&key_len

概念：

• possible_keys：显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。
• key：实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。
• key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

CREATE INDEX idx_name ON tb_emp(NAME);
EXPLAIN SELECT * FROM tb_emp WHERE deptid=2
EXPLAIN SELECT * FROM tb_emp WHERE deptid=2 AND NAME='rose'
# 在使用索引查询时，当条件越精确，key_len的长度可能会越长，所以在不影响结果的情况下，key_len的值越短越好。

ref

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

EXPLAIN SELECT * FROM tb_emp,tb_dept WHERE tb_emp.deptid=tb_dept.id AND tb_emp.name='rose'
#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字段。

rows

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

filtered

概念：百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

Extra

概念：显示一些重要的额外信息

• Using fileSort

概念：Using fileSort表示mysql会对数据使用一个外部的索引排序。而不是按照表内的索引顺序进行读取。mysql无法利用索引完成的排序操作称为：文件排序。

结论：出现Using fileSort必须解决，尽快优化语句

# deptname字段未建索引的情况。
EXPLAIN SELECT * FROM tb_dept ORDER BY deptname
# 为deptname字段创建索引后。
CREATE INDEX idx_deptname ON tb_dept(deptname);
EXPLAIN SELECT * FROM tb_dept ORDER BY deptname

• Using temporary

概念：使用了临时表保存中间结果，常见于排序order by和分组查询group by。

结论：必须要优化这个sql。

# 将tb_emp中name的索引先删除，出现如下图结果，非常烂，Using filesort和Using temporary
EXPLAIN SELECT * FROM tb_emp GROUP BY NAME;
# 为name字段创建索引后。
CREATE INDEX idx_name ON tb_emp(NAME);
EXPLAIN SELECT * FROM tb_emp GROUP BY NAME;

• Using index

概念：表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。

如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where deptid=1）

如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

删除tb_emp表中name和deptid字段的单独索引，创建复合索引。

CREATE INDEX idx_name ON tb_emp(NAME,deptid)
EXPLAIN SELECT NAME FROM tb_emp WHERE deptid=1;

覆盖索引的定义：select的数据列只从索引中就能取得数据，不必读取数据行。通过上面的例子理解：创建了（name，deptid）的复合索引，查询的时候也使用复合索引或部分，这就形成了覆盖索引
结论：优先级Using index>Using filesort>Using temporary

附录：

Mysql测试数据

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');

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 ('经理');