Explain

最新推荐文章于 2024-03-26 23:52:22 发布

weixin_55476214

最新推荐文章于 2024-03-26 23:52:22 发布

阅读量39

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_55476214/article/details/131904527

版权

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈.

Explain的作用

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

每张表有多少行被优化器查询

使用Explain+SQL语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wJhfm7Y3-1690202087678)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/4.png)]

EXPLAIN输出项

来源于官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

explain各字段解释

表环境和数据准备

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 
 INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); 
 INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));  
 INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));    
 INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

此处有坑：千万留意

对于type 为DERIVED【派生】在mysql5.7做了优化

针对mysql本身的优化器增加了一个控制优化器的参数叫 derived_merge （派生类合并）。默认是开启状态。

什么意思呢，据mysql5.7官方使用手册的说法：

Merge the derived table into the outer query block
Materialize the derived table to an internal temporary table

-将派生表合并到外部查询块中
-将派生表物化为一个内部临时表

派生表：当SELECT语句的FROM子句中使用独立子查询时，我们将其称为派生表。

类似于这样
select * from (select * from t1) as aa;
通过合并派生表，该查询类似于：
select * from t1;

值得注意的是：派生表必须要有别名。而子查询可以没有。

example:

 select * from t1 where  t1.id=(select t2.id from t2);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ohwJMpZq-1690202087680)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/image-20230720214704376.png)]

 select * from t1 where  t1.id=(select t2.id from t2);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mouZ8hI3-1690202087682)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/image-20230720214945391.png)]

由于MySQL5.7默认是打开的。因此我们将其手动关闭
set session optimizer_switch='derived_merge=off';
set global optimizer_switch='derived_merge=off';

官网地址：

https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html

1. id★

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。

存在三种情况：

id相同，执行顺序由上至下

Explain select * from t1,t2,t3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bGLt6Yae-1690202087683)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/7.png)]

**id不同：**如果是子查询id的序号会递增,id值越大优先级越高,越先被执行

也就是先执行t3,在执行t1，最后执行t2

EXPLAIN SELECT t2.* FROM t2 WHERE t2.id=(SELECT t1.id FROM t1 WHERE t1.id =(SELECT id FROM t3))

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zxEQVFeL-1690202087686)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/8.png)]

id相同不同，同时存在

这种情况在MySQL5.7中必须关闭合并派生表

derived_merge=off

explain select t2.* from (select t3.id from t3 )as a,t2 where a.id =t2.id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iq93F5en-1690202087687)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/25.png)]

id如果相同，可以认为是一组，从上往下顺序执行；

在所有组中，id值越大，优先级越高，越先执行。

结论：

id相同，不同

id如果相同，可以认为是一组，从上往下顺序执行；

id不同，id值越大，优先级越高，越先执行;

关注点：id号每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好。

2. select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

有哪些：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iOUvzZHV-1690202087689)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/10.png)]

SIMPLE

简单的 select 查询,查询中不包含子查询或者UNION

EXPLAIN SELECT * FROM t1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cjZhFPNM-1690202087690)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/12.png)]

2）PRIMARY

查询中若包含任何复杂的子查询部分，最外层查询则被标记为primary
```
EXPLAIN select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eqB1Wz7Z-1690202087691)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/14.png)]

3)SUBQUERY

在SELECT或WHERE列表中包含了子查询

#在where后
EXPLAIN select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5relwrEq-1690202087692)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/15.png)]

#在select后
explain SELECT t1.id, (SELECT t2.id from t2  ) from t1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F4fJlMD4-1690202087693)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/16.png)]

4)DEPENDENT SUBQUERY

在SELECT或WHERE列表中包含了子查询,子查询的条件基于外层。

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HO4Bet4v-1690202087694)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/17.png)]

UNCACHEABLE SUBQUREY【当前子查询】

表示这个subquery的查询要受到外部系统变量的影响

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XM7scN6r-1690202087695)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/18.png)]

思考：此时table为何为null

当table为null时，表示查询语句在执行过程中被优化，不用访问表，直接从索引中得到值。

6)UNION

若第二个SELECT出现在UNION之后，则被标记为UNION；
若UNION被from子句中的子查询包含，那么它的第一个SELECT将被标记为：DERIVED
```
EXPLAIN SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) aa; 
```

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AIF2hh29-1690202087695)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/19.png)]

7)UNION RESULT

从UNION的临时表中检查结果的SELECT会被标记为 UNION RESULT

EXPLAIN SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) aa;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-68ySNZla-1690202087696)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/20.png)]

8)DERIVED【派生表】

子查询在from后面
一定要有别名

explain select *  from (select *  from t1) as a;
或者
explain select *  from (select *  from t1) as a where id=1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LjlpRaFs-1690202087697)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/26.png)]

3. table

显示这一行的数据是关于哪张表的或者该表的别名

explain  select * from t1 as a

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EOk9yo3d-1690202087698)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/image-20230720223128194.png)]

4. partitions

代表分区表中的命中情况，非分区表，该项为null

意思是指将同一表中不同行的记录分配到不同的物理文件中。而说白了几个分区就有几个.idb文件。

#创建一张表
create table t_temp_employees (
    id int not null primary key,
    first_name varchar(10),
    last_name varchar(10))
    partition by range(id)(
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p2 values less than (30),
    partition p3 values less than (40)
    );
		
#随便插入几条数据		
insert into t_temp_employees values(1,'Hu','zhongkui');
insert into employees values(6,'Lei','fengyang');
insert into employees values(11,'Zhou','yang');
insert into employees values(16,'Song','hongkang');
#查询
EXPLAIN SELECT * from employees partition(p0);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cBMqgIua-1690202087699)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/image-20230720224030485.png)]

5. type★

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uDBRCSFw-1690202087700)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/21.png)]

5.1.访问类型排列

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const> eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

5.2 类型介绍

5.2.1.System

表仅有一行记录，必须是系统表,查询起来非常迅速。

explain SELECT * from mysql.proxies_priv WHERE `User`='root';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pIlcHrFm-1690202087700)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/22.png)]

explain SELECT * from mysql.proc where db='sys';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7kj48VdW-1690202087702)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/image/24.png)]

5.2.2.const

该表最多有一个匹配的行，在查询开始时读取。因为只有一行，所以优化器的其他部分可以将这一行中的列的值视为常量。

explain select * from t1 where id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MLQgw9gi-1690202087703)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/23.png)]

表示通过索引一次就找到了,const用于primary key或者unique索引。
因为只匹配一行数据，所以很快.。如将主键或者唯一索引置于where列表中，MySQL就能将该查询转换为一个常量级别的查询。

细节：

system其实就是const类型的特例，当查询的表只有一行的情况下，使用system

5.2.3.eq_ref *

对于每个索引键，表中只有一条记录与之匹配。简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

explain select * from t1,t2 where t1.id = t2.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8LwovGmX-1690202087704)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/24.png)]

细节：

除了 system 和 const 类型之外, 这是最好的联接类型
const是用于单表查询的情况，按照按主键或唯一键读取，eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询。

5.2.4.ref *

非唯一性索引扫描，返回匹配某个值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，但是它可能会找到多个符合条件的行。

举个例子来说明这两个区别：

先说eq_ref:

表t1包含列（id,context）,其中id是主键。表t2具有相同的列（id,content）,其中id是主键.

表A包含以下数据；

1 263

2 857

表B包含以下数据

1 334

2 665

而eq_ref为t1 t2之间的join:

select * from t1,t2 where t1.id=t2.id;

因为对于表t1中扫描的每一行，表t2中只能有一行满足。**因为t2.id是独一无二的。 **

再谈ref:

对于上面一张表t1, 给content建立索引但非UNQIE

1 263

2 263

那么ref为t1 t2之间的join:

select * from t1,t2 where t1.content=t2.content

这个join就没有eq_ref的快，因为对于t1中扫描的的每一行，t2表中有几个可能的行与之匹配，因为t2.content不是唯一的。

alter  table  t2  add index idx_content(content) 

EXPLAIN SELECT * FROM t1, t2 WHERE t1.content = t2.content;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qtuyhun3-1690202087705)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/25.png)]

explain select * from t2 where content='1234'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qXoI5qa1-1690202087706)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/image-20230720232758899.png)]

5.2.5.range *

只检索给定范围的行，使用一个索引来选择指定范围行

explain select * from t2 where id >1 and id <5;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kkJGONMN-1690202087707)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/26.png)]

5.2.6.index

出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引

index与ALL区别为index类型只遍历辅助索引树的叶子节点。

这通常比ALL快，因为索引文件通常比数据文件小。

explain select id from t1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IHdgdt1Q-1690202087708)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/27.png)]

explain select id,content from t2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-URSEuI6W-1690202087709)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/image-20230720232501583.png)]

细节：

index和ref的区别：

虽然两者都是走的索引，ref是带有where条件的，会查找出符合条件的数据，也即会进行二次回表，而index是查询所有的数据，走B+数的叶子结点的指针，一条条往下面遍历，不会进行二次回表。

5.2.7.all

Full Table Scan，将遍历全表以找到匹配的行。也即扫描主键索引（聚簇索引）的所有叶子节点。

explain select * from t1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jfib6PK1-1690202087710)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/28.png)]

6. possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a2yqg7MN-1690202087710)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/29.png)]

7. key

实际使用到的索引，如果为null，则没有使用索引

 explain   select t1.id  from t1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YkgTBxRd-1690202087711)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/30.png)]

8. key_len ★

key_len表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。值越大越好。

先将t_emp表中字段长度修改为以下大小

id—int(11)

name—varchar(20)

deptId—int(11)

empno—int(11)

给mydb库中的t_mp表建立复合索引

create index idx_age_name on t_emp (age,name);

#sql1:
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30 AND t_emp.name LIKE 'ab%'; 
#sql2:
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xHW6NVIE-1690202087712)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/32.png)]

第一组：

key_length=name的字节长度 + age的字节长度=63+5=68

name字节长度=20*3+1+2；

age字节长度=4+1;

第二组:

key_length=name字节的长度=5

常见的类型例子说明：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CzbyOY2L-1690202087713)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/33.png)]

key_len的长度计算公式:

varchr(10):变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

9. ref

显示将哪些列或常量与键列中命名的索引进行比较，以从表中选择行。

说白了一句话 索引被用于哪一列。

explain select * from t1,t2 where t1.id = t2.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DBpAjWdY-1690202087714)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/34.png)]

10. rows

rows列显示MySQL认为它执行查询时必须检查的行数，对于InnoDB，这个数字是一个估计，不一定准确。值越小越好。

11. filtered

这个字段表示存储引擎返回的数据在mysql server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

    explain  select * from t1 where content ='1234'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XYnw2FyX-1690202087716)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/27.png)]

12. Extra

包含不适合在其他列中显示,但十分重要的额外信息

12.1.Using filesort

如果出现Using filesort 说明mysql会对数据使用一个内部进行排序，而不是按照表内的索引顺序进行读取 ，那么MySQL中无法利用索引完成的排序操作称为“文件排序”。

当你发现你的sql在Extra这一列中对应的值是Using filesort的话，就代表mysql进行内部排序也叫做文件排序，所以需要将其优化掉。

另外创建表t_student，并指定索引

		
create table t_student(id int not null PRIMARY key , age int, height int, name varchar(10));

插入10条数据

INSERT into t_student values(1,10,170,'A1');
INSERT into t_student values(2,20,160,'A2');
INSERT into t_student values(3,22,166,'A3');
INSERT into t_student values(4,25,165,'A4');
INSERT into t_student values(5,25,169,'A5');
INSERT into t_student values(6,22,170,'A6');
INSERT into t_student values(7,30,170,'A7');
INSERT into t_student values(8,40,174,'A8');
INSERT into t_student values(9,50,172,'A9');
INSERT into t_student values(10,50,177,'A10');

explain  select height  from  t_student order by height;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3raawC9s-1690202087717)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/28.png)]

如何去掉filesort排序？

优化：

给height字段建立索引

create index idx_height on t_student(height);

explain  select height from  t_student  order by height;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GPbiOAp0-1690202087718)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/29.png)]

完美，去掉using filesort

过程：因为建立了 height 索引，那么也即height是排好序的，因此直接根据在height索引树直接返回数据即可，不需要在进行mysql内部在进行排序了。

12.2.Using temporary

如果出现了Using temporary情况：分组字段没有用上索引。产生临时表。注意：分组操作是需要先排序后分组【组内会有你一个排序操作】。所以，也会出现Using filesort。

先清楚除主键索引以外的其它索引

 explain SELECT age  from t_student  GROUP BY age;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EDJd8mC8-1690202087719)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/30.png)]

如何去掉using temporary?

优化：

给排序字段age建立索引

create  index idx_age on t_student(age);

 explain SELECT age  from t_student  GROUP BY age;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tyvYbmHa-1690202087720)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02.assets/31.png)]

12.3.Using index

表示使用了覆盖索引 [age是一个索引]

explain select age from t_student where age=20;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A1N1WR4Q-1690202087721)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/39.png)]

若给height建立一个索引。那么它也满足

create  index idx_age on t_student(age);

 explain  select height from t_student  where height=170;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R7wmydDo-1690202087722)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/40.png)]

当然还有一种情况 Using index 和 Using where同时存在

当Using where和Using index同时出现的时候，此时Using where只是用来从索引中查找数据。

为了演示效果方便

删除t_student中的idx_age、idx_height索引。

#创建age、height的复合索引
create index idx_age_height on t_student(age,height)

explain select  age,height from t_student where height=170;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gDlbi5zu-1690202087723)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/41.png)]

12.4.Using where

注意：当只出现using where时表示条件是由需要回表查询过滤过滤的，而不是利用索引树直接过滤的。

#删除复合索引
drop  index idx_age_height on t_student
#分别建立单个索引
create index idx_age on t_student(age);
create  index idx_height on t_student(height);

explain  select * from t_student where age=20 and name='A10';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eN7OBEhA-1690202087724)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/42.png)]

分析：因为age是索引字段，name不是索引字段可以看到这里也使用了这个索引。首先在age这颗索引树上找到age=20,然后在根据主键值回表到主键索引树上找到记录然后再次过滤。在这个过程中回表查询记录之后进行的过滤就不是于存储引擎做的了，而是Mysql Server做的过滤。

比如下面这个就用不到using where

explain select * from t_student where id=1 and name='A1'\G;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-09n6dAy7-1690202087725)(D:/Tools/%E6%AF%8F%E6%97%A5%E8%B5%84%E6%96%99/l_mySQL%E9%AB%98%E7%BA%A7/day03/%E8%AF%BE%E4%BB%B6/day02/43.png)]

e height=170;


[外链图片转存中...(img-gDlbi5zu-1690202087723)]



#### 12.4.Using where 

    注意：当只出现using where时 表示条件是由<strong style='color:red'>需要回表查询过滤</strong>过滤的，而不是<strong style='color:red'>利用索引树直接过滤的</strong>。



```sql
#删除复合索引
drop  index idx_age_height on t_student
#分别建立单个索引
create index idx_age on t_student(age);
create  index idx_height on t_student(height);

explain  select * from t_student where age=20 and name='A10';

[外链图片转存中…(img-eN7OBEhA-1690202087724)]

比如下面这个就用不到using where

explain select * from t_student where id=1 and name='A1'\G;

[外链图片转存中…(img-09n6dAy7-1690202087725)]

总结：并不是带了where条件就一定会出现using where，只是说有可能。

weixin_55476214

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Explain

Explain 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈.Explain的作用表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用每张表有多少行被优化器查询使用Explain+SQL语句。
复制链接

扫一扫