什么是explain
应用优化器能够模仿优化器执行SQL查问语句，从而晓得MySQL怎么解决你的SQL语句的，剖析你的查问语句和表构造的性能瓶颈。
explain可能干什么
- 读取表的程序
- 哪些索引可能被应用
- 数据读取操作的操作类型
- 哪些索引可能被理论应用
- 表之间的援用
- 每张表有多少行被物理查问
创立一个学习用的数据库
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mydb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mydb`;
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
/*Data for the table `course` */
insert into `course`(`id`,`name`) values
(1,'语文'),(2,'高等数学'),(3,'视听说'),(4,'体育'),(5,'马克思详情'),(6,'民族实践'),(7,'毛中特'),(8,'计算机根底'),(9,'深度学习'),(10,'Java程序设计'),(11,'c语言程序设计'),(12,'操作系统'),(13,'计算机网络'),(14,'计算机组成原理'),(15,'数据结构'),(16,'数据分析'),(17,'大学物理'),(18,'数字逻辑'),(19,'嵌入式开发'),(20,'需要工程');
/*Table structure for table `stu_course` */
DROP TABLE IF EXISTS `stu_course`;
CREATE TABLE `stu_course` (
`sid` int(10) NOT NULL,
`cid` int(10) NOT NULL,
PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `stu_course` */
insert into `stu_course`(`sid`,`cid`) values
(1,2),(1,4),(1,14),(1,16),(1,19),(2,4),(2,8),(2,9),(2,14),(3,13),(3,14),(3,20),(4,5),(4,8),(4,9),(4,11),(4,16),(5,4),(5,8),(5,9),(5,11),(5,12),(5,16),(6,2),(6,14),(6,17),(7,1),(7,8),(7,15),(8,2),(8,3),(8,7),(8,17),(9,1),(9,7),(9,16),(9,20),(10,4),(10,12),(10,14),(10,20),(11,3),(11,9),(11,16),(12,3),(12,7),(12,9),(12,12),(13,1),(13,5),(13,13),(14,1),(14,3),(14,18),(15,1),
(15,9),(15,15),(16,2),(16,7);
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `name_age` (`name`,`age`),
KEY `id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
/*Data for the table `student` */
insert into `student`(`id`,`name`,`age`) values
(25,'乾隆',17),(14,'关羽',43),(13,'刘备',12),(28,'刘永',12),(21,'后嗣',12),(30,'吕子乔',28),(18,'嬴政',76),(22,'孙悟空',21),(4,'安其拉',24),(6,'宋江',22),(26,'康熙',51),(29,'张伟',26),(20,'张郃',12),(12,'张飞',32),(27,'朱元璋',19),(11,'李世民',54),(9,'李逵',12),(8,'林冲',43),(5,'橘右京',43),(24,'沙和尚',25),(23,'猪八戒',22),(15,'王与',21),(19,'王建',23),(10,'王莽',43),(16,'秦叔宝',43),(17,'程咬金',65),(3,'荆轲',21),(2,'诸葛亮',71),(7,'钟馗',23),(1,'鲁班',21);这个数据库实际上的业务是:学生表 – 选课表 – 课程表
如何应用explain
应用而explain很简略就是，在你书写的SQL语句加一个单词 – explain，而后将 explain + SQL执行后会呈现一个表，这个表会通知你MySQL优化器是怎么执行你的SQL的。
就比方执行上面一句语句：
EXPLAIN SELECT * FROM studentMySQL会给你反馈上面一个信息：
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- -------- ------- ------ ------ -------- -------------
1 SIMPLE student (NULL) index (NULL) name_age 68 (NULL) 30 100.00 Using index 具体这些信息是干什么的，会对你有什么帮忙，会在上面通知你。
explain各个字段代表的意思
- id ：select查问的序列号，蕴含一组数字，示意查问中执行select子句或操作表的程序
- select_type ：查问类型 或者是 其余操作类型
- table ：正在拜访哪个表
- partitions ：匹配的分区
- type ：拜访的类型
- possible_keys ：显示可能利用在这张表中的索引，一个或多个，但不肯定理论应用到
- key ：理论应用到的索引，如果为NULL，则没有应用索引
- key_len ：示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度
- ref ：显示索引的哪一列被应用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
- rows ：依据表统计信息及索引选用状况，大抵估算出找到所需的记录所需读取的行数
- filtered ：查问的表行占表的百分比
- Extra ：蕴含不适宜在其它列中显示但非常重要的额定信息
下面介绍了每个字段的意思，能够大体看一下，上面会逐个介绍每个字段示意的啥？该关注什么？
id与table字段
为什么要将id和table放在一起讲呢？因为通过这两个字段能够齐全判断出你的每一条SQL语句的执行程序和表的查问程序。
先看id后看table，id和table在SQL执行判断过程中的关系就像是足球联赛的积分榜，首先一个联赛的球队排名应该先看积分，积分越高的球队排名越靠前，当两支或多只球队的积分一样高怎么办呢？那咱们就看净胜球，净胜球越多的球队，排在后面。而在explain中你能够把id看作是球队积分，table当作是净胜球。
比如说咱们explain一下这一条SQL：
EXPLAIN
SELECT
S.id,S.name,S.age,C.id,C.name
FROM course C JOIN stu_course SC ON C.id = SC.cid
JOIN student S ON S.id = SC.sid后果是这样：
id select_type table partitions type possible_keys key key_len ref
------ ----------- ------ ---------- ------ ------------------- ------- ------- -----------
1 SIMPLE SC (NULL) index PRIMARY PRIMARY 8 (NULL)
1 SIMPLE C (NULL) eq_ref PRIMARY PRIMARY 4 mydb.SC.cid
1 SIMPLE S (NULL) eq_ref PRIMARY,id_name_age PRIMARY 4 mydb.SC.sid 咱们看到id全是1，那就阐明光看id这个值是看不出来每个表的读取程序的，那咱们就来看table这一行，它的读取程序是自上向下的，所以，这三个表的读取程序该当是：SC – C – S。
再来看一条SQL
EXPLAIN
SELECT *
FROM course AS C
WHERE C.`id` = (
SELECT SC.`cid`
FROM stu_course AS SC
WHERE SC.`sid` =
(
SELECT
S.`id`
FROM student AS S
WHERE S.`name` = "安其拉"
) ORDER BY SC.`cid` LIMIT 1
)这条语句是查问后果是：一个叫安其拉的学生选的课外面，课程id最小的一门课的信息，而后来看一下explain的后果吧！
id select_type table partitions type possible_keys key key_len ref
------ ----------- ------ ---------- ------ ------------- ------- ------- ------
1 PRIMARY C (NULL) const PRIMARY PRIMARY 4 const
2 SUBQUERY SC (NULL) ref PRIMARY PRIMARY 4 const
3 SUBQUERY S (NULL) ref name,name_age name 63 const 此时咱们发现id是不雷同的，所以咱们很容易就看出表读取的程序了是吧！C – SC – S
留神！！！！！！你认真看一下最外面的子查问是查问的哪个表，是S这张表，而后里面一层呢？是SC这张表，最里面这一层呢？是C这张表，所以执行程序应该是啥呢？是….是…..难道是S – SC – C吗？是id越大的table读取越在后面吗？是的！这就像方才说的足球联赛积分，分数越高的球队的排序越靠前。
当然还有上面这种状况
EXPLAIN
SELECT *
FROM course AS C
WHERE C.`id` IN (
SELECT SC.`cid`
FROM stu_course AS SC
WHERE SC.`sid` =
(
SELECT
S.`id`
FROM student AS S
WHERE S.`name` = "安其拉"
)
)这个查问是：查问安其拉选课的课程信息
id select_type table partitions type possible_keys key key_len ref
------ ----------- ------ ---------- ------ ------------- ------- ------- -----------
1 PRIMARY SC (NULL) ref PRIMARY PRIMARY 4 const
1 PRIMARY C (NULL) eq_ref PRIMARY PRIMARY 4 mydb.SC.cid
3 SUBQUERY S (NULL) ref name,name_age name 63 const 后果很明确：先看id应该是S表最先被读取，SC和C表id雷同，而后table中SC更靠上，所以第二张读取的表该当是SC，最初读取C。
select_type字段
-
SIMPLE简略查问，不包含子查问和union查问EXPLAIN SELECT * FROM student JOIN stu_course ON student.`id` = sidid select_type table partitions type possible_keys key ------ ----------- ---------- ---------- ------ ------------------- -------- 1 SIMPLE student (NULL) index PRIMARY,id_name_age name_age 1 SIMPLE stu_course (NULL) ref PRIMARY PRIMARY PRIMARY当存在子查问时，最里面的查问被标记为主查问-
SUBQUERY子查问EXPLAIN SELECT SC.`cid` FROM stu_course AS SC WHERE SC.`sid` = ( SELECT S.`id` FROM student AS S WHERE S.`name` = "安其拉" )id select_type table partitions type possible_keys key key_len ref ------ ----------- ------ ---------- ------ ------------- ------- ------- ------ 1 PRIMARY SC (NULL) ref PRIMARY PRIMARY 4 const 2 SUBQUERY S (NULL) ref name,name_age name 63 const UNION当一个查问在UNION关键字之后就会呈现UNION-
UNION RESULT连贯几个表查问后的后果EXPLAIN SELECT * FROM student WHERE id = 1 UNION SELECT * FROM student WHERE id = 2id select_type table partitions type possible_keys key ------ ------------ ---------- ---------- ------ ------------------- ------- 1 PRIMARY student (NULL) const PRIMARY,id_name_age PRIMARY 2 UNION student (NULL) const PRIMARY,id_name_age PRIMARY (NULL) UNION RESULT <union1,2> (NULL) ALL (NULL) (NULL)下面能够看到第三行
table的值是<union1,2> -
DERIVED在FROM列表中蕴含的子查问被标记为DERIVED（衍生），MySQL
会递归执行这些子查问，把后果放在长期表中
MySQL5.7+ 进行优化了，减少了derived_merge（派生合并），默认开启，可放慢查问效率如果你想理解更具体的派生合并请点击这里
当你的MySQL是5.7及以上版本时你要将derived_merge敞开后能力看到
DERIVED状态set session optimizer_switch='derived_merge=off'; set global optimizer_switch='derived_merge=off';EXPLAIN SELECT * FROM ( SELECT * FROM student AS S JOIN stu_course AS SC ON S.`id` = SC.`sid` ) AS SSCid select_type table partitions type possible_keys key ------ ----------- ---------- ---------- ------ ------------------- -------- 1 PRIMARY <derived2> (NULL) ALL (NULL) (NULL) 2 DERIVED S (NULL) index PRIMARY,id_name_age name_age 2 DERIVED SC (NULL) ref PRIMARY PRIMARY下面咱们察看，最外层的主查问的表是<derived2>,而S和SC表的
select_type都是DERIVED,这阐明S和SC都被用来做衍生查问，而这两张表查问的后果组成了名为<derived2>的衍生表，而衍生表的命名就是<select_type + id>。
partitions字段
该列显示的为分区表命中的分区状况。非分区表该字段为空（null）。
type字段
留神！！！留神！！！重点来了！
首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优良）
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL-
NULLMySQL可能在优化阶段合成查问语句，在执行阶段用不着再拜访表或索引有没有这样一种纳闷，不查问索引也不查问表那你的数据是从哪里来的啊？谁说
SELECT语句必须查问某样货色了？EXPLAIN SELECT 5*7id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------ 1 SIMPLE (NULL) (NULL) (NULL) (NULL) (NULL)我就简简单单算个数不好吗？好啊????。。。
然而！！如果只是这样的话咱们还
explain个毛线啊！我很闲吗？存在这样一种状况，大家都晓得索引是将数据在B+Tree中进行排序了，所以你的查问速率才这么高，那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊？既然你都晓得了，那MySQL比你更晓得啊！当你要查问最大值或者最小值时，MySQL会间接到你的索引得分叶子节点上间接拿，所以不必拜访表或者索引。
EXPLAIN SELECT MAX(id) FROM studentid select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------ 1 SIMPLE (NULL) (NULL) (NULL) (NULL) (NULL)然而！你要记住，
NULL的前提是你曾经建设了索引。 SYSTEM表只有一行记录（等于零碎表），这是const类型的特列，平时不大会呈现，能够疏忽。-
const示意通过索引一次就找到了，const用于比拟primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查问转换为一个常量。简略来说，
const是间接按主键或惟一键读取。EXPLAIN SELECT * FROM student AS S WHERE id = 10id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------- 1 SIMPLE S (NULL) const PRIMARY PRIMARY -
eq_ref用于联表查问的状况，按联表的主键或惟一键联结查问。多表
join时，对于来自后面表的每一行，在以后表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或惟一非NULL索引的所有字段都被用作join联接时会应用此类型。EXPLAIN SELECT * FROM student AS S JOIN stu_course AS SC ON S.`id` = SC.`cid`id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------- 1 SIMPLE SC (NULL) index (NULL) PRIMARY 1 SIMPLE S (NULL) eq_ref PRIMARY PRIMARY以下面查问为例，咱们察看
id和table会晓得，先是从SC表中取出一行数据，而后再S表查找匹配的数据，咱们察看，SC中取出cid和S表中的id比拟，毫无疑问因为id是S表中的主键（不反复），所以只能呈现一个id与cid的值雷同。所以！满足条件 S 表的type为eq_ref。 -
ref能够用于单表扫描或者连贯。如果是连贯的话，驱动表的一条记录可能在被驱动表中通过非惟一（主键）属性所在索引中匹配多行数据，或者是在单表查问的时候通过非惟一（主键）属性所在索引中查到一行数据。EXPLAIN SELECT * FROM student AS S JOIN stu_course AS SC ON S.id = SC.`sid`不要在意SQL，以上SQL没有理论查问的意义只是用于表白用例
id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------- 1 SIMPLE S (NULL) ALL PRIMARY (NULL) 1 SIMPLE SC (NULL) ref PRIMARY PRIMARYSC的主键索引是（cid,sid）所以sid列中必定是反复的数据，尽管在前面的
key中显示应用了主键索引。而后，就很明确了S.id一行能在SC表中通过索引查问到多行数据。上面是单表了，写一个例子，然而不细讲了
EXPLAIN SELECT * FROM student AS S WHERE S.`name` = "张飞"id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ---------- 1 SIMPLE S (NULL) ref index_name index_name留神
name字段是有索引的哈！！！ -
ref_or_null相似ref，然而能够搜寻值为NULL的行EXPLAIN SELECT * FROM student AS S WHERE S.`name` = "张飞" OR S.`name` IS NULLid select_type table partitions type possible_keys key ------ ----------- ------ ---------- ----------- ------------- ---------- 1 SIMPLE S (NULL) ref_or_null index_name index_name -
index_merge示意查问应用了两个以上的索引，最初取交加或者并集，常见and，or的条件应用了不同的索引，官网排序这个在ref_or_null之后，然而实际上因为要读取多个索引，性能可能大部分工夫都不如range。EXPLAIN SELECT * FROM student AS S WHERE S.`name` LIKE "张%" OR S.`age` = 30id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ----------- -------------------- -------------------- 1 SIMPLE S (NULL) index_merge index_name,index_age index_name,index_age -
range索引范畴查问，常见于应用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查问中。EXPLAIN SELECT S.`age` FROM student AS S WHERE S.`age` > 30id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ -------------------- ---------- 1 SIMPLE S (NULL) range index_name,index_age index_name -
indexindex只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是尽管all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。EXPLAIN SELECT S.`name` FROM student AS Sid select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ---------- 1 SIMPLE S (NULL) index (NULL) index_name ALL如果一个查问的type是All,并且表的数据量很大，那么请解决它！！！
possible_keys字段
这个表外面存在且可能会被应用的索引，可能会在这个字段上面呈现，然而个别都以key为准。
key字段
理论应用的索引，如果为null,则没有应用索引，否则会显示你应用了哪些索引，查问中若应用了笼罩索引（查问的列刚好是索引），则该索引仅呈现在key列表。
ref字段
显示哪些列被应用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
rows字段和Filter字段
rows是依据表的统计信息和索引的选用状况，优化器大略帮你估算出你执行这行函数所须要查问的行数。
Filter是查问的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。
Extra字段
这一字段蕴含不适宜在其余列显示，然而也十分重要的额定信息。
-
Using filesort示意当SQL中有一个中央须要对一些数据进行排序的时候，优化器找不到可能应用的索引，所以只能应用内部的索引排序，内部排序就一直的在磁盘和内存中替换数据，这样就解脱不了很屡次磁盘IO，以至于SQL执行的效率很低。反之呢？因为索引的底层是B+Tree实现的，他的叶子节点原本就是有序的，这样的查问能不爽吗？EXPLAIN SELECT * FROM course AS C ORDER BY C.`name`type possible_keys key key_len ref rows filtered Extra ------ ------------- ------ ------- ------ ------ -------- ---------------- ALL (NULL) (NULL) (NULL) (NULL) 20 100.00 Using filesort没有给
C.name建设索引，所以在依据C.name排序的时候，他就应用了内部排序 -
Using tempporary示意在对MySQL查问后果进行排序时，应用了长期表,,这样的查问效率是比内部排序更低的，常见于order by和group by。EXPLAIN SELECT C.`name` FROM course AS C GROUP BY C.`name`possible_keys key key_len ref rows filtered Extra ------------- ------ ------- ------ ------ -------- --------------------------------- (NULL) (NULL) (NULL) (NULL) 20 100.00 Using temporary; Using filesort下面这个查问就是同时触发了
Using temporary和Using filesort，堪称是雪上加霜。 Using index示意应用了索引，很优良????。Using where应用了where然而如同没啥用。Using join buffer表明应用了连贯缓存,比如说在查问的时候，多表join的次数十分多，那么将配置文件中的缓冲区的join buffer调大一些。impossible where筛选条件没能筛选出任何货色distinct优化distinct操作，在找到第一匹配的元组后即进行找同样值的动作
发表回复