Mysql篇-语句实行筹划详解(explain)
概述使用 explain 输出 SELECT 语句实行的具体信息，包罗以下信息：
[*]表的加载顺序
[*]sql 的查询类型
[*]大概用到哪些索引，实际上用到哪些索引
[*]读取的行数
Explain 实行筹划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。
通过explain extended + show warnings可以在原本explain的基础上额外提供一些查询优化的信息，得到优化以后的大概的查询语句（不一定是最终优化的结果）。
测试环境：
CREATE TABLE `blog` (
`blog_id` int NOT NULL AUTO_INCREMENT COMMENT '唯一博文id--主键',
`blog_title` varchar(255) NOT NULL COMMENT '博文标题',
`blog_body` text NOT NULL COMMENT '博文内容',
`blog_time` datetime NOT NULL COMMENT '博文发布时间',
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`blog_state` int NOT NULL COMMENT '博文状态--0 删除 1正常',
`user_id` int NOT NULL COMMENT '用户id',
PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
CREATE TABLE `user` (
`user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户唯一id--主键',
`user_name` varchar(30) NOT NULL COMMENT '用户名--不能重复',
`user_password` varchar(255) NOT NULL COMMENT '用户密码',
PRIMARY KEY (`user_id`),
KEY `name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
CREATE TABLE `discuss` (
`discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论唯一id',
`discuss_body` varchar(255) NOT NULL COMMENT '评论内容',
`discuss_time` datetime NOT NULL COMMENT '评论时间',
`user_id` int NOT NULL COMMENT '用户id',
`blog_id` int NOT NULL COMMENT '博文id',
PRIMARY KEY (`discuss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8id
表示查询中实行select子句或者操纵表的顺序，id的值越大，代表优先级越高，越先实行
explain select discuss_body
from discuss
where blog_id = (
select blog_id from blog where user_id = (
select user_id from user where user_name = 'admin'));三个表依次嵌套，发现最里层的子查询 id最大，最先实行。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827105.png
select_type
表示 select 查询的类型，重要是用于区分各种复杂的查询，比方：平凡查询、团结查询、子查询等。
[*]SIMPLE：表示最简单的 select 查询语句，在查询中不包含子查询或者交并差集等操纵。
[*]PRIMARY：查询中最外层的SELECT（存在子查询的外层的表操纵为PRIMARY）。
[*]SUBQUERY：子查询中首个SELECT。
[*]DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）。
[*]UNION：在SELECT之后使用了UNION
table
查询的表名，并不一定是真实存在的表，有别名显示别名，也大概为临时表。当from子句中有子查询时，table列是的格式，表示当前查询依靠 id为N的查询，会先实行 id为N的查询。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827045.png
partitions
查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区环境。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827067.png
type
查询使用了何种类型，它在 SQL优化中是一个非常重要的指标
访问服从：const > eq_ref > ref > range > index > ALL
system
当表仅有一行记载时（系统表），数据量很少，每每不需要进行磁盘IO，速率非常快。好比，Mysql系统表proxies_priv在Mysql服务启动时候已经加载在内存中，对这个表进行查询不需要进行磁盘 IO。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827081.png
const
单表操纵的时候，查询使用了主键或者唯一索引。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827072.png
eq_ref
多表关联查询的时候，主键和唯一索引作为关联条件。如下图的sql，对于user表（外循环）的每一行，user_role表（内循环）只有一行满足join条件，只要查找到这行记载，就会跳出内循环，继续外循环的下一轮查询。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827088.png
ref
查找条件列使用了索引而且不为主键和唯一索引。虽然使用了索引，但该索引列的值并不唯一，这样即使使用索引查找到了第一条数据，仍然不能制止，要在目标值附近进行小范围扫描。但它的好处是不需要扫全表，由于索引是有序的，即便有重复值，也是在一个非常小的范围内做扫描。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827832.png
ref_or_null
雷同 ref，会额外搜刮包含NULL值的行
index_merge
使用了索引合并优化方法，查询使用了两个以上的索引。新建comment表，id为主键，value_id为非唯一索引，实行explain select content from comment where value_id = 1181000 and id > 1000;，实行结果显示查询同时使用了id和value_id索引，type列的值为index_merge。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827916.png
range
有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。像between、and、>、 explain select blog_id from user_like where user_id = 13;+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+|1 | SIMPLE | user_like | NULL | ref| ul1,ul2 | ul1| 4 | const | 2 | 100.00 | Using index |+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+而下面这个SQL的实行筹划ref值为NULL，由于key为NULL，查询没有效到索引。
mysql> explain select blog_id from user_like where user_id = 13;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|1 | SIMPLE | user_like | NULL | ref| ul1,ul2 | ul1| 4 | const | 2 | 100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+rows
估算要找到所需的记载，需要读取的行数。评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般环境下 rows 值越小越好
filtered
存储引擎返回的数据在经过过滤后，剩下满足条件的记载数量的比例
extra
表示额外的信息阐明。为了方便测试，这里新建两张表。
mysql> explain select user_id from user_like where status = 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1 | SIMPLE | user_like | NULL | ALL| NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+using where
表示在查询过程中使用了WHERE条件进行数据过滤。当一 个查询中包含WHERE条件时，MySQL会根据该条件过滤出满足条件的数据行，然后再进行后续的操纵。这个过程 就被称为"Using Where”。
表示查询的列未被索引覆盖，，且where筛选条件是索引列前导列的一个范围，或者是索引列的非前导列，或者黑白索引列。对存储引擎返回的结果进行过滤（Post-filter，后过滤），一般发生在MySQL服务器，而不是存储引擎层，因此需要回表查询数据。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827731.png
using index
查询的列被索引覆盖，而且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827749.png
Using where&Using index
查询的列被索引覆盖，但无法通过索引查找找到符合条件的数据，不外可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。
包罗两种环境（组合索引为(user_id, orde)）：
where筛选条件不符合最左前缀原则
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827741.png
where筛选条件是索引列前导列的一个范围
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827773.png
null
查询的列未被索引覆盖，而且where筛选条件是索引的前导列，也就是用到了索引，但是部分字段未被索引覆盖，必须回表查询这些字段，Extra中为NULL。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827756.png
using index condition
索引下推（index condition pushdown，ICP），先使用where条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。
对于团结索引（a, b），在实行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在团结索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在团结索引里判断？照旧回主键索引去判断呢？
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在团结索引遍历过程中，对团结索引中包含的字段先做判断，直接过滤掉不满足条件的记载，减少回表次数。
不使用ICP的环境（set optimizer_switch='index_condition_pushdown=off'），如下图，在步骤4中，没有使用where条件过滤索引：
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827903.png
使用ICP的环境（set optimizer_switch='index_condition_pushdown=on'）：
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827708.png
下面的例子使用了ICP：
CREATE TABLE `t_order` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`order_id` int DEFAULT NULL,
`order_status` tinyint DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8
CREATE TABLE `t_orderdetail` (
`id` int NOT NULL AUTO_INCREMENT,
`order_id` int DEFAULT NULL,
`product_name` varchar(100) DEFAULT NULL,
`cnt` int DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_orderid_productname` (`order_id`,`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827706.png
关掉ICP之后（set optimizer_switch='index_condition_pushdown=off'），可以看到extra列为using where，不会使用索引下推。
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827721.png
using temporary
使用了临时表保存中间结果，常见于 order by 和 group by 中。典型的，当group by和order by同时存在，且作用于差异的字段时，就会建立临时表，以便盘算出最终的结果集
filesort
文件排序。表示无法使用索引完成排序操纵，以下环境会导致filesort：
[*]order by 的字段不是索引字段
[*]select 查询字段不满是索引字段
[*]select 查询字段都是索引字段，但是 order by 字段和索引字段的顺序不一致
https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261827717.png
using join buffer
Block Nested Loop，需要进行嵌套循环盘算。两个关联表join，关联字段均未建立索引，就会出现这种环境。好比内层和外层的type均为ALL，rows均为4，需要循环进行4*4次盘算。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环盘算。
面试题专栏
Java面试题专栏已上线，欢迎访问。
[*]假如你不知道简历怎么写，简历项目不知道怎么包装；
[*]假如简历中有些内容你不知道该不应写上去；
[*]假如有些综合性问题你不知道怎么答；
那么可以私信我，我会尽我所能帮助你。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]