哈喽,小伙伴们好呀。我是狗哥,今天打算跟大家聊聊一个很基础的 MySQL 命令 —— explain。这个命令相信很多小伙伴都熟悉并且几乎每天都会使用,反正我是这样的。那为什么还要写呢?
事情是这样的,周六有位大一小学弟问起我这个命令是不是很常用?
这个命令其实职场的兄弟们都很熟悉,但是对于一些学生来说,他们可能并不了解。所以就有了这篇文章。
如果你已经工作了就当复个习,我面试就真被问到了 type 的结果有几种?分别是啥意思?如果你还没毕业,那我倒是建议你去了解下,真的很重要。
话不多说了,国际惯例先上张本文的思维导图。PS:文末福利,电子书、大厂面试题以及 IDEA 激活,需要的文末获取~
explain 就是一个分析 sql 执行的命令,主要用于 select 语句(PS:其他语句我还没 explain 过。。。)通过它可以知道自己写的 sql 语句是否足够高效以及还有哪些可以优化的地方。
开篇之前声明下我的 MySQL 版本是:5.6.46,PS:5.6 之前的版本只能对 select 语句进行 explain,之后的版本可以对其他命令。
开始之前我建了三张表并造了一些测试数据:
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2021-07-11 14:39:53'), (2,'b','2021-07-11 10:39:53'), (3,'c','2021-07-11 10:24:39');
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
explain 的使用非常简单,在 sql 前面加 explain 即可:
explain select * from film;
除此以外 mysql 还有以下两个扩展,这个知道即可。
它会在 explain 的基础上额外提供一些查询优化的信息,从下图可以看出。它比 explain 多了一个 filtered 列,是一个百分比值,rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数(前一个表指 explain 中的 id 值比当前表 id 值小的表)
跟 show warnings 两条语句一起执行,MySQL 就会给出优化后的 sql 语句,可以看出 MySQL 帮我们优化了什么。
比 explain 多了个 partitions 字段,如果查询是基于分区表的话,会显示查询将访问的分区。
原因很简单。通过 explain 命令我们可以学习到该条 sql 是如何执行的,随后解析 explain 的结果可以帮助我们使用更好的索引,最终来优化它!
通过 explain 命令我们可以知道以下信息:表的读取顺序,数据读取操作的类型,哪些索引可以使用,哪些索引实际使用了,表之间的引用,每张表有多少行被优化器查询等信息。
从上图我们可以知道:explain 命令输出的结果有 10 列,分别是:id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
接下来我将逐一分析这些结果列代表的含义,希望对你有帮助:
一组数字,表示 sql 语句中 select 的执行顺序,有几个 select 就有几个 id,按照 select 出现的顺序呈现结果。
有几种情况:
见名知义,就是 select 查询的类型,它的结果有好多种,且听我分析,加粗的都是常见的,得知道意思。
举个例子,你就知道它们的的区别了:
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) alias;
举个栗子,帮助你们分清楚区别:
explain select 1 union all select 1;
PS:只需要弄懂前四个即可,其他的知道就好。
表示 explain 的一行访问的表是哪一个
表关联类型或访问类型,重要的一列,是判断查询是否高效的一句:也就是 MySQL 决定如何查找表中的行就看这个列。
结果有很多,性能从最优到最差为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
我就挑几个常见的取值聊聊,没聊到的证明我也没遇到过,哈哈哈:
in (), between ,> ,<, >=
等操作中。使用一个索引来检索给定范围的行。表示 MySQL 执行查询可能会使用那些索引来查找,如果为 null,可考虑在该列加个索引。
表示 MySQL 执行查询实际使用那些索引来查找,如果为 null,则证明没有使用索引。如果想强制使用或者忽略索引可以在查询语句加 force index(想要强制使用的索引名)或者 ignore index(想要忽略的索引名)
PS:如果 possible_keys 有列,而 key 显示 null 的情况,可能是因为表数据不多,加索引作用也不大。
在索引里使用的字节数,当 key 列的值为 NULL 时,则该列也是 NULL
PS:索引的最大长度是 768 字节,字符串过长时,MySQL 会将前半部分提出来做索引,这点大家需要注意。(不过一般我们也不会在这么长的字段上建索引)
那些字段或者常量被用来和 key 列记录的索引配合查找值,常见的有:const(常量),func,NULL,字段名(例:film.id)
这一列是 mysql 估计要读取并检测的行数,注意这个不是结果集里的行数。(只是个预测的数量)
额外信息,也非常重要
其实 explain 并不难,我们需要关注的结果列也只有 select_type、type 以及 extra 列。这是非常基础的一个命令,各位在校生小伙伴非常建议你提前了解下。
微信经常有很多小伙伴,还不知道怎么激活 jetbrains 全家桶的,经常有人来问我咋整?我这边把破解补丁和安装教程都给整理出来了,有需要的直接点击阅读原文~