MySQL性能优化神器Explain的基本使用分析

2023-08-17 20:30:04 70

简介

MySQL提供了一个EXPLAIN命令,它可以对SELECT语句进行分析,并输出SELECT执行的详细信息,以供开发人员针对性优化.

EXPLAIN命令用法十分简单,在SELECT语句前加上Explain就可以了,例如:

EXPLAINSELECT*fromuser_infoWHEREid<300;

准备

为了接下来方便演示EXPLAIN的使用,首先我们需要建立两个测试用的表,并添加相应的数据:

CREATETABLE`user_info`(
`id`BIGINT(20)NOTNULLAUTO_INCREMENT,
`name`VARCHAR(50)NOTNULLDEFAULT'',
`age`INT(11)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`name_index`(`name`)
)
ENGINE=InnoDB
DEFAULTCHARSET=utf8

INSERTINTOuser_info(name,age)VALUES('xys',20);
INSERTINTOuser_info(name,age)VALUES('a',21);
INSERTINTOuser_info(name,age)VALUES('b',23);
INSERTINTOuser_info(name,age)VALUES('c',50);
INSERTINTOuser_info(name,age)VALUES('d',15);
INSERTINTOuser_info(name,age)VALUES('e',20);
INSERTINTOuser_info(name,age)VALUES('f',21);
INSERTINTOuser_info(name,age)VALUES('g',23);
INSERTINTOuser_info(name,age)VALUES('h',50);
INSERTINTOuser_info(name,age)VALUES('i',15);

CREATETABLE`order_info`(
`id`BIGINT(20)NOTNULLAUTO_INCREMENT,
`user_id`BIGINT(20)DEFAULTNULL,
`product_name`VARCHAR(50)NOTNULLDEFAULT'',
`productor`VARCHAR(30)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`user_product_detail_index`(`user_id`,`product_name`,`productor`)
)
ENGINE=InnoDB
DEFAULTCHARSET=utf8

INSERTINTOorder_info(user_id,product_name,productor)VALUES(1,'p1','WHH');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(1,'p2','WL');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(1,'p1','DX');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(2,'p1','WHH');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(2,'p5','WL');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(3,'p3','MA');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(4,'p1','WHH');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(6,'p1','WHH');
INSERTINTOorder_info(user_id,product_name,productor)VALUES(9,'p8','TE');

EXPLAIN输出格式

EXPLAIN命令的输出内容大致如下:

mysql>explainselect*fromuser_infowhereid=2\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:const
possible_keys:PRIMARY
key:PRIMARY
key_len:8
ref:const
rows:1
filtered:100.00
Extra:NULL
1rowinset,1warning(0.00sec)

各列的含义如下:

id:SELECT查询的标识符.每个SELECT都会自动分配一个唯一的标识符.
select_type:SELECT查询的类型.
table:查询的是哪个表
partitions:匹配的分区
type:join类型
possible_keys:此次查询中可能选用的索引
key:此次查询中确切使用到的索引.
ref:哪个字段或常数与key一起被使用
rows:显示此查询一共扫描了多少行.这个是一个估计值.
filtered:表示此查询条件所过滤的数据的百分比
extra:额外的信息

接下来我们来重点看一下比较重要的几个字段.

select_type

select_type表示了查询的类型,它的常用取值有:
SIMPLE,表示此查询不包含UNION查询或子查询
PRIMARY,表示此查询是最外层的查询
UNION,表示此查询是UNION的第二或随后的查询
DEPENDENTUNION,UNION中的第二个或后面的查询语句,取决于外面的查询
UNIONRESULT,UNION的结果
SUBQUERY,子查询中的第一个SELECT
DEPENDENTSUBQUERY:子查询中的第一个SELECT,取决于外面的查询.即子查询依赖于外层查询的结果.

最常见的查询类别应该是SIMPLE了,比如当我们的查询没有子查询,也没有UNION查询时,那么通常就是SIMPLE类型,例如:

mysql>explainselect*fromuser_infowhereid=2\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:const
possible_keys:PRIMARY
key:PRIMARY
key_len:8
ref:const
rows:1
filtered:100.00
Extra:NULL
1rowinset,1warning(0.00sec)

如果我们使用了UNION查询,那么EXPLAIN输出的结果类似如下:

mysql>EXPLAIN(SELECT*FROMuser_infoWHEREidIN(1,2,3))
->UNION
->(SELECT*FROMuser_infoWHEREidIN(3,4,5));
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|1|PRIMARY|user_info|NULL|range|PRIMARY|PRIMARY|8|NULL|3|100.00|Usingwhere|
|2|UNION|user_info|NULL|range|PRIMARY|PRIMARY|8|NULL|3|100.00|Usingwhere|
|NULL|UNIONRESULT||NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3rowsinset,1warning(0.00sec)

table

表示查询涉及的表或衍生表

type

type字段比较重要,它提供了判断查询是否高效的重要依据依据.通过type字段,我们判断此次查询是全表扫描还是索引扫描等.

type常用类型

type常用的取值有:

system:表中只有一条数据.这个类型是特殊的const类型.
const:针对主键或唯一索引的等值查询扫描,最多只返回一行数据.const查询速度非常快,因为它仅仅读取一次即可.
例如下面的这个查询,它使用了主键索引,因此type就是const类型的.

mysql>explainselect*fromuser_infowhereid=2\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:const
possible_keys:PRIMARY
key:PRIMARY
key_len:8
ref:const
rows:1
filtered:100.00
Extra:NULL
1rowinset,1warning(0.00sec)

eq_ref:此类型通常出现在多表的join查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果.并且查询的比较操作通常是=,查询效率较高.例如:

mysql>EXPLAINSELECT*FROMuser_info,order_infoWHEREuser_info.id=order_info.user_id\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:order_info
partitions:NULL
type:index
possible_keys:user_product_detail_index
key:user_product_detail_index
key_len:314
ref:NULL
rows:9
filtered:100.00
Extra:Usingwhere;Usingindex
***************************2.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:eq_ref
possible_keys:PRIMARY
key:PRIMARY
key_len:8
ref:test.order_info.user_id
rows:1
filtered:100.00
Extra:NULL
2rowsinset,1warning(0.00sec)

ref:此类型通常出现在多表的join查询,针对于非唯一或非主键索引,或者是使用了最左前缀规则索引的查询.

例如下面这个例子中,就使用到了ref类型的查询:

mysql>EXPLAINSELECT*FROMuser_info,order_infoWHEREuser_info.id=order_info.user_idANDorder_info.user_id=5\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:const
possible_keys:PRIMARY
key:PRIMARY
key_len:8
ref:const
rows:1
filtered:100.00
Extra:NULL
***************************2.row***************************
id:1
select_type:SIMPLE
table:order_info
partitions:NULL
type:ref
possible_keys:user_product_detail_index
key:user_product_detail_index
key_len:9
ref:const
rows:1
filtered:100.00
Extra:Usingindex
2rowsinset,1warning(0.01sec)

range:表示使用索引范围查询,通过索引字段范围获取表中部分数据记录.这个类型通常出现在=,<>,>,>=,<,<=,ISNULL,<=>,BETWEEN,IN()操作中.

当type是range时,那么EXPLAIN输出的ref字段为NULL,并且key_len字段是此次查询中使用到的索引的最长的那个.

例如下面的例子就是一个范围查询:

mysql>EXPLAINSELECT*
->FROMuser_info
->WHEREidBETWEEN2AND8\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:range
possible_keys:PRIMARY
key:PRIMARY
key_len:8
ref:NULL
rows:7
filtered:100.00
Extra:Usingwhere
1rowinset,1warning(0.00sec)

index:表示全索引扫描(fullindexscan),和ALL类型类似,只不过ALL类型是全表扫描,而index类型则仅仅扫描所有的索引,而不扫描数据.

index类型通常出现在:所要查询的数据直接在索引树中就可以获取到,而不需要扫描数据.当是这种情况时,Extra字段会显示Usingindex.

例如:

mysql>EXPLAINSELECTnameFROMuser_info\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:index
possible_keys:NULL
key:name_index
key_len:152
ref:NULL
rows:10
filtered:100.00
Extra:Usingindex
1rowinset,1warning(0.00sec)

上面的例子中,我们查询的name字段恰好是一个索引,因此我们直接从索引中获取数据就可以满足查询的需求了,而不需要查询表中的数据.因此这样的情况下,type的值是index,并且Extra的值是Usingindex.

ALL:表示全表扫描,这个类型的查询是性能最差的查询之一.通常来说,我们的查询不应该出现ALL类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难.如一个查询是ALL类型查询,那么一般来说可以对相应的字段添加索引来避免.

下面是一个全表扫描的例子,可以看到,在全表扫描时,possible_keys和key字段都是NULL,表示没有使用到索引,并且rows十分巨大,因此整个查询效率是十分低下的.

mysql>EXPLAINSELECTageFROMuser_infoWHEREage=20\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:user_info
partitions:NULL
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:10
filtered:10.00
Extra:Usingwhere
1rowinset,1warning(0.00sec)

type类型的性能比较

通常来说,不同的type类型的性能关系如下:

ALL

ALL类型因为是全表扫描,因此在相同的查询条件下,它是速度最慢的.

而index类型的查询虽然不是全表扫描,但是它扫描了所有的索引,因此比ALL类型的稍快.

后面的几种类型都是利用了索引来查询数据,因此可以过滤部分或大部分数据,因此查询效率就比较高了.

possible_keys

possible_keys表示MySQL在查询时,能够使用到的索引.注意,即使有些索引在possible_keys中出现,但是并不表示此索引会真正地被MySQL使用到.MySQL在查询时具体使用了哪些索引,由key字段决定.

key

此字段是MySQL在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数.这个字段可以评估组合索引是否完全被使用,或只有最左部分字段被使用到.
key_len的计算规则如下:

字符串

char(n):n字节长度
varchar(n):如果是utf8编码,则是3n+2字节;如果是utf8mb4编码,则是4n+2字节.

数值类型:

TINYINT:1字节
SMALLINT:2字节
MEDIUMINT:3字节
INT:4字节
BIGINT:8字节

时间类型

DATE:3字节
TIMESTAMP:4字节
DATETIME:8字节

字段属性:NULL属性占用一个字节.如果一个字段是NOTNULL的,则没有此属性.

我们来举两个简单的栗子:

mysql>EXPLAINSELECT*FROMorder_infoWHEREuser_id<3ANDproduct_name='p1'ANDproductor='WHH'\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:order_info
partitions:NULL
type:range
possible_keys:user_product_detail_index
key:user_product_detail_index
key_len:9
ref:NULL
rows:5
filtered:11.11
Extra:Usingwhere;Usingindex
1rowinset,1warning(0.00sec)

上面的例子是从表order_info中查询指定的内容,而我们从此表的建表语句中可以知道,表order_info有一个联合索引:

KEY`user_product_detail_index`(`user_id`,`product_name`,`productor`)

不过此查询语句WHEREuser_id<3ANDproduct_name='p1'ANDproductor='WHH'中,因为先进行user_id的范围查询,而根据最左前缀匹配原则,当遇到范围查询时,就停止索引的匹配,因此实际上我们使用到的索引的字段只有user_id,因此在EXPLAIN中,显示的key_len为9.因为user_id字段是BIGINT,占用8字节,而NULL属性占用一个字节,因此总共是9个字节.若我们将user_id字段改为BIGINT(20)NOTNULLDEFAULT'0',则key_length应该是8.

上面因为最左前缀匹配原则,我们的查询仅仅使用到了联合索引的user_id字段,因此效率不算高.

接下来我们来看一下下一个例子:

mysql>EXPLAINSELECT*FROMorder_infoWHEREuser_id=1ANDproduct_name='p1'\G;
***************************1.row***************************
id:1
select_type:SIMPLE
table:order_info
partitions:NULL
type:ref
possible_keys:user_product_detail_index
key:user_product_detail_index
key_len:161
ref:const,const
rows:2
filtered:100.00
Extra:Usingindex
1rowinset,1warning(0.00sec)

这次的查询中,我们没有使用到范围查询,key_len的值为161.为什么呢?因为我们的查询条件WHEREuser_id=1ANDproduct_name='p1'中,仅仅使用到了联合索引中的前两个字段,因此keyLen(user_id)+keyLen(product_name)=9+50*3+2=161

rows

rows也是一个重要的字段.MySQL查询优化器根据统计信息,估算SQL要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示SQL的效率好坏,原则上rows越少越好.

Extra

EXplain中的很多额外的信息会在Extra字段显示,常见的有以下几种内容:

Usingfilesort

当Extra中有Usingfilesort时,表示MySQL需额外的排序操作,不能通过索引顺序达到排序效果.一般有Usingfilesort,都建议优化去掉,因为这样的查询CPU资源消耗大.

例如下面的例子:

mysql>EXPLAINSELECT*FROMorder_infoORDERBYproduct_name\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:order_info
partitions:NULL
type:index
possible_keys:NULL
key:user_product_detail_index
key_len:253
ref:NULL
rows:9
filtered:100.00
Extra:Usingindex;Usingfilesort
1rowinset,1warning(0.00sec)

我们的索引是

KEY`user_product_detail_index`(`user_id`,`product_name`,`productor`)

但是上面的查询中根据product_name来排序,因此不能使用索引进行优化,进而会产生Usingfilesort.

如果我们将排序依据改为ORDERBYuser_id,product_name,那么就不会出现Usingfilesort了.例如:

mysql>EXPLAINSELECT*FROMorder_infoORDERBYuser_id,product_name\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:order_info
partitions:NULL
type:index
possible_keys:NULL
key:user_product_detail_index
key_len:253
ref:NULL
rows:9
filtered:100.00
Extra:Usingindex
1rowinset,1warning(0.00sec)

Usingindex

"覆盖索引扫描",表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往往说明性能不错

Usingtemporary

查询有使用临时表,一般出现于排序,分组和多表join的情况,查询效率不高,建议优化.

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL性能优化神器Explain的基本使用分析

热门推荐

随机推荐