点击蓝字 关注我们
一、前言
相信对于有一定开发经验的工程师来说，平时进行sql调优的时候，explain是逃不掉的，可以说explain算是sql调优的基本操作了，且在面试的时候，也是一个高频考点。因此，笔者尝试进行归纳总结，让各位同学一览其貌。
二、用法
语法：explain+ SQL语句，例如explain select * from user，会生成类似如下的分析结果，下面详细对每个字段进行讲解
三、id
概念：表示查询中执行select子句或操作表的顺序。
主要有以下三种情况
-
id相同，执行顺序由上至下。
-
id不同，id值越大优先级越高，越先被执行。
-
id相同和不同的情况同时存在，遵循1和2规则。也即是先判断哪个id大，id大的先执行，然后id相同的，由上至下执行。
举个例子：：
id select_type table partitions type possible_keys key key_len ref------ ----------- ------ ---------- ------ ------------- ------- ------- -----------1 PRIMARY table1 (NULL) ref PRIMARY PRIMARY 4 const1 PRIMARY table2 (NULL) eq_ref PRIMARY PRIMARY 4 mydb.SC.cid3 SUBQUERY table3 (NULL) ref name,name_age name 63 const
结果很明确：先看id应该是table3最先被读取，table1和table2的id相同，但是table1靠前，所以第二张读取的表应当是table1，最后是table2。
四、select_type
SIMPLE：简单查询，不包括子查询和union查询。例如：
EXPLAINSELECT * FROM student JOIN course ON student.`id` = sid
id select_type table partitions type possible_keys key------ ----------- ---------- ---------- ------ ------------------- --------1 SIMPLE student (NULL) index PRIMARY,id_name_age name_age1 SIMPLE course (NULL) ref PRIMARY PRIMARY
-
PRIMARY：当存在子查询是，最外面的查询被标记为主查询。
-
SUBQUERY：子查询
举个例子：
EXPLAINSELECT C.`cid`FROM course AS CWHERE C.`sid` =(SELECTS.`id`FROM student AS SWHERE S.`name` = "李华")
id select_type table partitions type possible_keys key key_len ref------ ----------- ------ ---------- ------ ------------- ------- ------- ------1 PRIMARY C (NULL) ref PRIMARY PRIMARY 4 const2 SUBQUERY S (NULL) ref name,name_age name 63 const
-
UNION：当一个查询在UNION关键字之后就会出现UNION。
-
UNION RESULT：连接几个表查询后的结果
举个例子：
EXPLAIN SELECT * FROM student WHERE id = 123456UNION SELECT * FROM student WHERE id = 654321
id select_type table partitions type possible_keys key------ ------------ ---------- ---------- ------ ------------------- -------1 PRIMARY student (NULL) const PRIMARY,id_name_age PRIMARY2 UNION student (NULL) const PRIMARY,id_name_age PRIMARY(NULL) UNION RESULT <union1,2> (NULL) ALL (NULL) (NULL)
可以看到最后一行的select_type是UNION RESULT
-
DERIVED:在FROM列表中包含的子查询被标记为DERIVED（衍生）
MySQL5.7+ 版本进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率。
如果你的mysql版本是5.7+，想看到DERIVED，需要关闭derived_merge，命令如下：
set session optimizer_switch='derived_merge=off';set global optimizer_switch='derived_merge=off';
举个例子：
EXPLAINSELECT * FROM(SELECT *FROM student AS S JOIN course AS CON S.`id` = C.`sid`) AS temp
id 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_age2 DERIVED C (NULL) ref PRIMARY PRIMARY
可以看到最外层的主查询的表是,S和C都被用来做衍生查询，所以S和C表的select_type都是DERIVED，而这两张表查询的结果组成了名为的衍生表。
五、table和partitions
table：正在访问哪个表。
partitions：这个是表示命中的分区表的情况，不是分区表的话这个字段为空。
六、type
重点记住一下这个排序：
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL越靠左边越好，表示效率越高效。
-
NULL：表示mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
举个例子：
EXPLAIN SELECT MIN(id) FROM studentid select_type table partitions type possible_keys key------ ----------- ------ ---------- ------ ------------- ------1 SIMPLE (NULL) (NULL) (NULL) (NULL) (NULL)
因为主键索引是在B+tree中已经排好序了，所以最大值和最小值就是B+树两边的叶子节点，因此mysql可以直接到索引的叶子节点获取数据，因此不用访问表或者索引。
-
SYSTEM：表中只有一行记录，这是const类型的特例。
-
const：表示通过索引一次就能找到，const常常用于primary key或者uique索引，因为只是匹配一行数据，效率很快。
举个例子：
EXPLAIN SELECT * FROM student AS S WHERE id = 123456id select_type table partitions type possible_keys key------ ----------- ------ ---------- ------ ------------- -------1 SIMPLE S (NULL) const PRIMARY PRIMARY
-
eq_ref：用于联表查询的情况，按联表的主键或唯一键联合查询。
连表查询join的时候，对于前表中的每一行，在当前表中只能找到唯一一行，也常常出现primary和非null的uique索引。
举个例子：
EXPLAIN SELECT * FROM student AS S JOIN course AS C ON S.`id` = C.`sid`id select_type table partitions type possible_keys key------ ----------- ------ ---------- ------ ------------- -------1 SIMPLE C (NULL) index (NULL) PRIMARY1 SIMPLE S (NULL) eq_ref PRIMARY PRIMARY
先从C表中取出数据，然后再和S表进行匹配，C中取出的数据和S中的id匹配，因为S表中id是主键，因此S.id是不重复的且是唯一的，因此只能出现一个id和sid相等的情况，因此S表中的type是eq_ref。
-
ref：表示单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一属性（主键）所在的索引查到一行数据。
举个例子：
假如course表中的索引说（cid，sid）
EXPLAINSELECT * FROM student AS S JOIN course AS C ON S.id = C.`sid`
id select_type table partitions type possible_keys key------ ----------- ------ ---------- ------ ------------- -------1 SIMPLE S (NULL) ALL PRIMARY (NULL)1 SIMPLE C (NULL) ref PRIMARY PRIMARY
S.id能在C表中通过索引查询到多行数据。
单表的举个简单的例子如下：
假设name字段有索引
EXPLAINSELECT * 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
-
ref_or_null 类似ref，但是可以搜索值为NULL的行。
举个例子：
EXPLAINSELECT * FROM student AS S WHERE S.`name` = "李华" OR S.`name` IS NULL
id select_type table partitions type possible_keys key------ ----------- ------ ---------- ----------- ------------- ----------1 SIMPLE S (NULL) ref_or_null index_name index_name
-
index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引。
举个例子:
EXPLAINSELECT * FROM student AS S WHERE S.`name` LIKE "李%" OR S.`age` = 40
id 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等运算符的查询中。
举个例子：
EXPLAINSELECT S.`age` FROM student AS S WHERE S.`age` > 40
id select_type table partitions type possible_keys key------ ----------- ------ ---------- ------ -------------------- ----------1 SIMPLE S (NULL) range index_name,index_age index_name
-
index ：只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
举个例子：
EXPLAINSELECT S.`name` FROM student AS S
id select_type table partitions type possible_keys key------ ----------- ------ ---------- ------ ------------- ----------1 SIMPLE S (NULL) index (NULL) index_name
-
ALL 如果一个查询的type是All,并且表的数据量很大。
七、possible_keys
显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到。
八、key
实际使用到的索引，如果为NULL，则没有使用索引。
九、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
十、ref
显示哪些列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
十一、rows和filtered
rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。
filtered是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。
十二、extra
下面逐个解析一下：
-
Using filesort 表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。反过来，如果建立了索引，由于索引的底层是B+Tree实现的，他的叶子节点本来就是有序的。
举个例子：
假如在列name上没有建立索引，执行如下的语句：
EXPLAINSELECT * 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
因此可以看到由于没有在name上建立索引，所以根据name排序的时候，使用了filesort。
-
Using tempporary 表示在对MySQL查询结果进行排序时，使用了临时表,,这样的查询效率是比外部排序更低的，常见于order by和group by。
举个例子：
EXPLAINSELECT 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 index 表示使用了索引，效率高。
Using where 使用了where但是好像没啥用。
Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
impossible where 筛选条件没能筛选出任何东西。
distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
重点！重点！重点！听说题主开通了公众号：有理唔理。欢迎关注一起探讨问题。
点击蓝字 关注我们