一步步教你MySQL查询优化分析教程

2023-08-09 17:03:04 75

前言

MySQL是关系性数据库中的一种，查询功能强，数据一致性高，数据安全性高，支持二级索引。但性能方面稍逊于非关系性数据库，特别是百万级别以上的数据，很容易出现查询慢的现象。这时候需要分析查询慢的原因，一般情况下是程序员sql写的烂，或者是没有键索引，或者是索引失效等原因导致的。

这时候MySQL提供的EXPLAIN命令就尤其重要,它可以对SELECT语句进行分析,并输出SELECT执行的详细信息,以供开发人员针对性优化.

而且就在查询语句前加上Explain就成:

EXPLAINSELECT*FROMcustomerWHEREid<100;

准备

首先需要建立两个测试用表及数据:

CREATETABLE`customer`(
`id`BIGINT(20)unsignedNOTNULLAUTO_INCREMENT,
`name`VARCHAR(50)NOTNULLDEFAULT'',
`age`INT(11)unsignedDEFAULTNULL,
PRIMARYKEY(`id`),
KEY`name_index`(`name`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4

INSERTINTOcustomer(name,age)VALUES('a',1);
INSERTINTOcustomer(name,age)VALUES('b',2);
INSERTINTOcustomer(name,age)VALUES('c',3);
INSERTINTOcustomer(name,age)VALUES('d',4);
INSERTINTOcustomer(name,age)VALUES('e',5);
INSERTINTOcustomer(name,age)VALUES('f',6);
INSERTINTOcustomer(name,age)VALUES('g',7);
INSERTINTOcustomer(name,age)VALUES('h',8);
INSERTINTOcustomer(name,age)VALUES('i',9);

CREATETABLE`orders`(
`id`BIGINT(20)unsignedNOTNULLAUTO_INCREMENT,
`user_id`BIGINT(20)unsignedNOTNULLDEFAULT0,`product_name`VARCHAR(50)NOTNULLDEFAULT'',
`productor`VARCHAR(30)NOTNULLDEFAULT'',
PRIMARYKEY(`id`),
KEY`user_product_detail_index`(`user_id`,`product_name`,`productor`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4

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

EXPLAIN输出格式

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

mysql>explainselect*fromcustomerwhereid=1\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:customer
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

SIMPLE——简单的select查询，查询中不包含子查询或者UNION
PRIMARY——查询中若包含任何复杂的子查询，最外层查询则被标记为primary
UNION——表示此查询是UNION的第二或随后的查询
DEPENDENTUNION——UNION中的第二个或后面的查询语句,取决于外面的查询
UNIONRESULT——从UNION表获取结果的select结果
DERIVED——在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
SUBQUERY——在select或where列表中包含了子查询
DEPENDENTSUBQUERY——子查询中的第一个SELECT,取决于外面的查询.即子查询依赖于外层查询的结果.

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

mysql>explainselect*fromcustomerwhereid=2\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:customer
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*FROMcustomerWHEREidIN(1,2,3))
->UNION
->(SELECT*FROMcustomerWHEREidIN(3,4,5));
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|1|PRIMARY|customer|NULL|range|PRIMARY|PRIMARY|8|NULL|3|100.00|Usingwhere|
|2|UNION|customer|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*fromcustomerwhereid=2\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:customer
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*FROMcustomer,order_infoWHEREcustomer.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:customer
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*FROMcustomer,order_infoWHEREcustomer.id=order_info.user_idANDorder_info.user_id=5\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:customer
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*FROMcustomerWHEREidBETWEEN2AND8\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:customer
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>EXPLAINSELECTnameFROMcustomer\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:customer
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>EXPLAINSELECTageFROMcustomerWHEREage=20\G***************************1.row***************************
id:1
select_type:SIMPLE
table:customer
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类型的稍快.后面的几种类型都是利用了索引来查询数据,因此可以过滤部分或大部分数据,因此查询效率就比较高了.

对程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。

ALL：（fulltablescan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
index：（fullindexscan）全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。
range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，<等查询。
ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。
eq_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，
const：表示通过索引一次就可以找到，const用于比较primarykey或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可

possible_key

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

key

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

key_len

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

key_len的计算规则如下:

字符串
- char(n):n字节长度
- varchar(n):如果是utf8编码,则是3
  n+2字节;如果是utf8mb4编码,则是4
  n+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_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`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查询优化分析教程

热门推荐

随机推荐